My previous post hopefully helped you with some of the common real-time segment building issues faced working with multi-hop data queries, custom tables, N:N and 1:N relationships. This post will show you how to build segments using Dataverse activities such as Emails and Appointments, which you can then easily ruse for other ones such as custom activities, Tasks and Phone Calls.

What are ‘Activities’?

Activities in Dynamics 365 are special type of table that has some very specific/different behaviours to that or a User/Team or Organisation owned table. Out of the box activities are things like Email, Task, Fax, Phone Call, and Letter. You can also create custom activities. They show up in the timeline on a record and have common attribute such as start/end time, subject, description and one or more participants.

For example: an Email has a date that it was sent ‘Actual End’ it has To/CC/BCC recipients, a sender as well as ‘Regarding’. A Lead or Contact could be any one of these participants to an email, and sometimes more than one of them!

Another example is Appointments: Have a Start Time it has Required and Optional attendees, as well as ‘Regarding’. A Lead or Contact could be any one of these participants to an appointment, and sometimes more than one of them!

Just to be clear, by ‘Email’ we do not mean any kind of marketing email, these are direct emails sent from a mailbox via some kind of email provider such as Outlook. They can also be sent from within Dynamics 365 if your mailbox and email profile is correctly configured to do so. Activities in this context is completely separate (in terms of data structure/location) to any marketing based interactions/activity.

Common Use Cases for Activities in Segments

A common scenario I come across is the need to exclude contacts/leads from nurture journeys. Nurture content is often posed as personalised ‘direct’ communications sharing educational content, and a call to action for the potential customer to get in touch to do something such as book an appointment or to fill in a form to request a quote. If the customer has already reached out or is already having communications with a member of the business or sales team this kind of content usually comes across as desperate, oblivious and pushy - which is not a common brand reputation to aim for!

The tips I show here are inspired by my previous post but with a specific, and even more complex data hunt in mind. Lots of screenshots alert!

Exclude all contacts from a journey who have been involved with (To/From/CC/BCC) an email in the last 30 days

Lets start at the end - this is the query we need to build. Appearance wise it looks pretty simple, but building it was kind of traumatic. You will mostly likely find that the pathway ‘Email > Activity > Activity Party > Contact‘ you see in the image below just will not show up for you. Step by step instructions below.

Step by step

Create a ‘Contact’ based segment

Add ‘Activity’ (activitypointer) as a ‘Related table’

Create a generic condition to make the relationship ‘stick’ in the segment builder so in this example, select ‘Activity’, select the pathway Activity -> Contact set the condition to ‘Contains data’.

Add Activity Party (activityparty) as a ‘Related table’

Repeat the same steps for Activity Party (activityparty) - add as a ‘Related table’.

Then add an irrelevant condition for Activity Party in a ‘New Group’, in this example, select ‘Activity’, select the pathway Activity Party -> Contact set the condition to ‘Contains data’.

Your segment should look something like this so far, I know it doesn’t make sense but trust the process!

Add Email Messages (email) as a ‘Related table’

Finally, repeat the same steps for Email Messages (email) - add as a ‘Related table’.

Add an irrelevant condition for Email in a ‘New Group’, in this example, select ‘Actual End’, select the pathway Email -> Contact set the condition to ‘Is not empty’.

Save the segment and reload the page, it should look like this

Edit the Email Messages (email) pathway

Select ‘Edit’ in the Group 3 email pathway, Select ‘Back’ to get to the pathway selection screen and if you scroll enough you should be able to find the pathway ‘Email > Activity > Activity Party > Contact‘ - YAY! Select the pathway and configure the attributes as seen below (there is probably only a single option for each). And choose ‘Set Path’.

Update the filter query to say ‘Actual End’ is in the last 30 days rather than ‘Is not empty’ as seen below.

Save again (just to be safe) and then you can delete Group 1 and Group 2 shown below leaving just the Email filter with the pathway ‘Email > Activity > Activity Party > Contact‘.

A cherry on the cake, add a filter to show only active contacts, or any other contact based filtering you need.

Aaaaaand, done!

Exclude all leads from a journey who have an appointment scheduled in the next 7 days

Lets start at the end again, if you have already created the segment above, you might be able to skips some of the pointless query parts along the way - this is the query we need to build with a pathway ‘Appointment > Activity > Activity Party > Lead‘ you see in the image below just will not show up for you. Step by step instructions below.

Step by step

Create a Lead based segment.

The process follows a very similar process to the steps above to start with to add Activity and Activity Party as related tables from above but with Leads instead.

Add Activity (activitypointer) as a ‘Related table’

Add an irrelevant condition for Activity. In this example, select ‘Activity’, select the pathway Activity -> Lead set the condition to ‘Contains data’.

Add Activity Party (activityparty) as a ‘Related table’

Repeat the same steps for Activity Party (activityparty)

Add an irrelevant condition for Activity Party. In this example, select ‘Activity’, select the pathway Activity Party -> Lead set the condition to ‘Contains data’.

Add Appointment (appointment) as a ‘Related table’

Add an irrelevant condition for Appoinment. In this example, select ‘Start Time’, select the pathway Appointment -> Lead set the condition to ‘Is not empty’.

Checkpoint

At this stage you should have three pointless condition groups in your segment one each for Activity, Activity Party and Appointment. At this point you need to save and reload the page.

Edit the Appointment (appointment) pathway

Select ‘Edit’ in the Group 3 email pathway, Select ‘Back’ to get to the pathway selection screen and if you scroll enough you should be able to find the pathway ‘Appointment> Activity > Activity Party > Lead‘ - YAY! Select the pathway and configure the attributes as seen below (there is probably only a single option for each). And choose ‘Set Path’.

Clear out the trash

Save your segment, then delete Group 1 and Group 2 which are now officially useless.

Refine the Appointment filter queries

Now we can update the queries for the appointment to make sure we are only capturing leads with appointments in the next 7 days. Starting with the existing ‘Is not empty’ condition and update it to be ‘Is on of after’ a relative date of Today (aka 0 days from now).

Add a new Appointment Start Time condition into the Existing Group. You might need to replumb the same pathway again for Appointment> Activity > Activity Party > Lead. You are most welcome to roll your eyes whilst doing this.

Update the condition to be ‘Is on of before’ a relative date of 7 days from today.

Finally, add a new Activity Party ‘Participation Type’ condition into the Existing Group. And configure the pathway Activity Party -> Lead.

Update the condition to be ‘Is in’, and select ‘Required attendee’ and ‘Optional attendee’. The reason we add the ‘Participation Type’ here is that we want to make sure the lead is actually invited to the appointment i.e. required/optional, otherwise it could possibly be ‘Regarding’ the lead but not actually with them.

Aaaaaand, done!

The email segment example above did not filter to a specific participating type such as ‘Contacts who have received an email in the last 30 days’ - see below for how this could be added also.

That’s enough data wrangling for one day

Questions, suggestions and helpful additions welcome. Share your segment struggles below, I’m more than happy to help (and use your issues as blog post inspiration)!