Delaying emails to certain individuals based on their time zone

For the second time running the free Flow Online Conference was on September 10, 2019. I was lucky enough to be included among the awesome line up of speakers. My model-driven app was Star Wars themed too 😁

I presented a use case where emails needed to be delayed for certain individuals based on their time zone. To be more specific as an example, delay sending emails to only primary contacts based on their local time zone. This use case came about because previous clients who I have worked with had customers globally or nationally and wanted emails to be delayed to a group of customers.

But we can already delay emails in Outlook? 🤔

Outlook will send the emails based on the single time selected which is also in the context of the person who is sending those delayed emails. Same applies to any email marketing software out there, delaying emails will be based on the person who is sending those delayed emails and the date and time is based on their time zone - not the recipients' time zone.

Furthermore in Outlook the functionality to query contacts and have those resulting contacts in an email where they don't see each other's names is not there.

As demonstrated in previous WTF episodes, through the power of Flow the art of sending emails based on local time zones is 100% possible.

Watch my Flow Online Conference session in the below YouTube video. I present at 2:30.

The remainder of this blog post will outline what was presented.

User Story

The following is the user story from my presentation.

As a customer service technician,

I want to send delayed emails to primary contacts based on their time zone,

so that they can receive the email in their local time.

1.1 - The trigger

The trigger can be a recurrence or a Flow button. It is entirely up to how the Flow will be triggered.

1.2 - Retrieve Accounts

The email addresses of the individuals are stored in CDS in the contact entity. Since we are only sending the email to primary contacts of an organisation the CDS List Records action is used to retrieve the organisations from the account entity.

For the purpose of this Flow I did not enter a filter query. In the scenario where you need to email primary contacts of organisations that meet criteria such as primary contacts of VIP organisations, you can apply a filter query in the CDS List Records action against the account entity.

1.3 - Apply to Each

For every account returned the primary contact will need to be identified. The Apply to Each action is therefore used.

1.4 - Primary Contacts

The next step is to identify the primary contact of an organisation in CDS. The account entity has a single primary contact which is represented by a lookup field. The Apply to Each action is used alongside the CDS List Records action to retreive the contact entity with a filter query of

contactid eq @{items('1.3_Identify_Primary_Contact')?['_primarycontactid_value']}

This filter query will only return contacts based on the GUID in the primary contact field associated to the organisation. The dynamic content value is the Primary Contact field from the previous action that returns accounts.

1.5 - Apply to Each

For every identified primary contact from the previous action, a bunch of actions will be executed in order to send the delayed email to the primary contact. To ensure these actions are executed against each primary contact an Apply to Each action is used.

1.6 - Get location by address

As seen in a previous WTF episode there is a Bing Maps action "Get location by address" that allows the location latitude and longitude to be identified based on address information. The Address 1 fields in the contact record of the primary contact will be used. The assumption I made here is that the primary contact inherits the address of the account it is associated to.

1.7 - Identify local time zone of Contact

There are additional Bing Maps Time Zone APIs available which you can read from this Bing Maps blog post. These APIs are not available as Bing Maps action in Flow however the HTTP action can be used to call the APIs.

In order to find out the time in the primary contact's time zone, the "Given location coordinates, find the time zone of the place" Bing Maps API can be used through a HTTP action.

Simply reference the latitude and longitude outputs from the previous step followed by inserting a Bing Maps key.

https://dev.virtualearth.net/REST/v1/TimeZone/latitude, longitude?key=<bingmaps-key>

1.8 - Retrieve localTime property

To ensure the primary contact receives the email in their local time including the date such as September 21 at 4.30pm, we need to know what is their date and time in their local time zone.

To achieve this the localTime property in the response of the previous HTTP action can be used. Below is a screenshot of the response.

If you attempt to select the localTime as a dynamic content in a subsequent action you're out of luck as it won't be displayed. You will only see "Body" since the action used is HTTP as Flow only is aware of the Body output rather than the JSON properties.

There are two methods that can be applied to get around this.

1. Use the Parse JSON action which will allow you to view and select the properties as dynamic content values.
2. Use a Compose action which allows you to reference the property without using the Parse JSON action but simply an expression instead.

Both of these methods have been used and talked about in previous WTF episodes.

The Compose action with an expression is used in this Flow.

body('1.7_Local_time_of_Contact').resourceSets[0].resources[0].timeZone.convertedTime.localTime

Below is a screenshot to help you understand the expression used.

My way of explaining this expression is to work backwards:

From the HTTP action, get the localTime property from the convertedTime object, whose parent object is timeZone which is in the resources array whose parent object is resourceSets.

If you have a better way of explaining this to a non-technical soul, you can tweet to me 😁

1.8.1 - Split the date string

The format of the localTime property will be in UTC format.

The date is only required, not the time to understand the date in the primary contacts' local time zone.

To retrieve only the date string we need to separate the date from the time in the string value of localTime. the split function can be used in an expression within a Compose action where T is used to separate the two.

split(outputs('1.8_Retrieve_LocalTime'), 'T')

1.8.2 - Retrieve date only string

The output of the previous action will return the following,

This results in an array where the date and time is separated. The first row is the date and the second row is the time.

Another compose action is used to retrieve the first array in order to reference only the date of 2019-09-21.

outputs('1.8.1_Split_the_date_string')?[0]

1.9 - Retrieve windowsTimeZoneID

The same technique is used from 1.8 where the windowsTimeZoneID is referenced in a Compose action.

body('1.7_Local_time_of_Contact').resourceSets[0].resources[0].timeZone.windowsTimeZoneId

The two actions 1.8 and 1.9 are within a parallel branch as they are not dependent on each other. Both actions can be performed simultaneously.

1.10 - Convert time zone

The next step is important as the Convert Time Zone action is used to ensure the email is delayed based on the local time of the primary contact.

Flow uses UTC which means the intended local time of the primary contact (for example 9am) needs to be converted to UTC. I've covered this in a previous WTF episode which can be referred to for more learning.

For example in order to send the email at 4.30pm in the local time zone of a primary contact who is based in Wellington, it has to be converted to UTC which is equal to 4.30am. Below is a screenshot from Savvy Time to illustrate the equivalent time in UTC of the Wellington time zone.

1.11 - Delay until

The Delay Until action is used where the date and time is the output from the previous 1.10 Convert time zone action. The email will be sent when the date and time is met which is equal to the local time of the primary contact in their time zone.

1.12 - Send an email

The final action used is the Outlook Send an email (V2) (Preview) action where the content of the message is entered and the recipient is the Primary Contact from the 1.4 Primary Contacts action.

Show time!

When you run the Flow it will go ahead and do its magic.

I'll use a primary contact in Wellington, New Zealand from the Flow run history as an example. If we look at the convert time zone output, we can see that the UTC time has correctly been reflected as 4.30am. 

As seen in the earlier screenshot, 4.30pm in Wellington is equal to 4.30am UTC. Therefore the email is delayed until 4.30pm in the Wellington time zone. The Primary Contact will receive the email at 4.30pm in their time zone.

Summary

Delayed emails based on a individuals time zone can be achieved through the power of Flow. To go one step further you can also perform queries as well in the business scenario where only selected individuals need to receive the email.

Although after reading one of the reviews of my presentation there is one step that is missing in my original Flow. Maybe I was low in energy that day to recognize the gap. I used the date from the localTime which is great. However what should also be in the expression is to add 1 day so that the email is sent the following day.

My Flow is available for download from the TDG Power Platform Bank.

I also want to take the time to say big thanks to Jon Levesque and Gabriel for their massive effort in organizing and putting together a free Flow Online Conference for everyone. It was really great and I can't wait to see the next one with a new line up of speakers.