Marketing to Leads and Contacts without duplicate sends in Customer Insights - Journeys
Alt title: Exclude or include segment members with a list of email addresses
Sometimes coming up with a concise title for a blog post is hard and I don’t want to over sell my work around but you are here now. I have been asked this question by multiple customers in the last few weeks:
How can I send a marketing email to a segment of Leads and Contacts without sending an email to the same email address twice?
There is a setting that allows journeys to ‘Prevent duplicate emails to shared email addresses’ but this only works in the scope of a single email within a journey. And a journey must be Lead OR Contact based. Segments have the same issue so you cannot exclude via segmenting on the interaction data of a Contact in a Lead based Segment (and vice versa).
Official Microsoft Solution: 💰💰💰💰Customer Insights - Data Unification
A not always feasible solution: Use a frequency cap set to 1 (since you can only have one frequency cap per business unit, this then means you can only use the frequency cap for this reason and not for it’s actual intended purpose)
Amey’s Creative Work Around: 🔨🔨🔨🔨Exclude from a Segment with a list of email addresses (who have received the email)
The Problem: I want to send a newsletter to a segment of subscribed Leads, and a segment of subscribed Contacts, but a Lead can have the same email as a Contact
You have two segments, one for Leads who are ‘Opted In’ to your newsletter Purpose/Topic and the other for Contacts. you will probably have some extra filtering here but for examples sake lets keep the segment conditions simple.
Send the email to the Lead based segment
You could also start with Contact send and exclude for Lead, its up to you as to whether analytics on Leads is more important than Contacts or vice versa. Nothing complicated here just your usual segment - journey - email combo.
Export the ‘Sent’ analytics from the Lead Newsletter
Or alternatively the members for the ‘Lead Newsletter’ segment. Basically, you need a list of email addresses to exclude form the Contact segment.
Format the email addresses to exclude
We need to do a bit of spreadsheet/text manipulation wizardry. There is a probably a million ways to achieve this and how to get to the desired format is totally up to you. The end goal is to convert the email addresses into a line of comma separated and single quote wrapped values.
Here is how I did it.
1) Update your exported file to remove all columns except the email address
2) Copy the column of email addresses, transpose into a single row
3) Copy the row paste into a text editor (don’t use a word/document editor because the apostrophes go funky and break the code)
4) In ‘Find and Replace’, find apostrophe ‘ and replace with \’
5) Replace All
6) Copy the space between the email addresses
7) In ‘Find and Replace’, paste the space into the ‘Find what’ section
8) Replace with ‘,’
9) Select ‘Replace All’
10) Add a single quote at the very start of the line of emails
11) Add a single quote at the very end of the line of emails
Add a ‘but not’ filter to the Contact segment
Check your segment is ‘Draft’ or Stopped’ (not ‘Live, editing’). Update your ‘Contact Newsletter’ segment to add a new group for ‘Email’ Is In and add a query for Email is in ‘a’ (press enter/return to lock in the value). Set the condition between group 1 and group 2 to be ‘but not’. Save your changes.
Open the MQL editor
Open the developer console (CTRL+ALT+J) or right click -> Inspect -> Select ‘Console’ tab. Enter the snippet below and press enter to open the query editor box. If the Query editor doesn’t open straight away try to reload the page or resize the window.
Add the email addresses to the ‘Is in’ query
In the query editor replace ‘a’ with the string of email addresses from the text editor. Select ‘Apply’.
The segment should be updated with the email addresses as an ‘Is In’ query - magic! Mark ‘Ready to use’ to check membership count and don’t send duplicate emails across Contacts and Leads yay!
