One of the most common pitfalls of using Marketing Cloud Connect is believing that once MCC is in place, all data will magically flow and synch between Sales/Service Cloud and Marketing Cloud. Unfortunately, this is not the case for email addresses.
If an email address for a Lead or Contact Object is updated in Sales Cloud or Service Cloud, the corresponding email address is not updated in the All Subscribers list.Eliot Harper, THE DATA HANDBOOK
There are ways to get around this, for example, if you’re only sending from Journey Builder, you can configure it to always take the latest email address which is available in the journey’s Data Extension, but for all other Journey Builder or Email and Automation Studio sends, the emails sent to the subscribers will always go out to the email address related to their record on the All Subscribers list.
The email address on the All Subscribers list will be saved there whenever you send for the first time to a subscriber and won’t be updated unless you create a dedicated process for that. For example, if you send to a Salesforce Lead with the Id
00Qabcdef123456EAA and email address
email@example.com, this email address will be added along with their Id to the All Subscribers list. Now whenever you send to that Lead, even if you include a new email address in a Data Extension for Id
00Qabcdef123456EAA, it will still look up the email address from the All Subscribers list and use it for sending. Additionally, if you update the email address for Lead Id
00Qabcdef123456EAA in Sales/Service Cloud, it also won’t be updated anywhere in Marketing Cloud unless you create a process for that.
Markus Dang (formerly Slabina) has written a couple of articles on his blog outlining a process that you can set up in Automation Studio to not only correctly synch and update the email address changes of your Salesforce Contacts, but also to reactivate them in case they were held due to bounces, but their email address changed since then:
- How to sync email address changes from Sales/Service Cloud to All Subscribers list in Salesforce Marketing Cloud
This article is a spin-off of Markus’ original articles, to show you how to make updates to the All Subscribers list programmatically and avoid having to export and re-import data through the FTP server.
Select records that need updating
The first activity in the process, which is selecting the records that actually need to be updated in Marketing Cloud, is exactly the same as described in Marus’ article. You will need a Data Extension and an SQL query to find records in the All Subscribers list that have a different email address than the corresponding record in the Synchronized Data Extension. The Data Extension will need the following fields:
subscriberkey, Text, 18
emailaddress, EmailAddress, 254
status, Text, 50
Below is an enhanced version of the SQL query from Markus’ blog, to account for both Salesforce Contacts and Leads. It will also find subscribers in statuses
bounced that we have an updated email address for:
Create this as the first step in the automation and save results to the dedicated Data Extension with the
Update All Subscribers list using WSProxy
Now that we know which records need to be updated, we can use WSProxy to update them directly in the All Subscribers list. In the below script, you will need to update two variables:
deKey– this should be set to the External Key of the Data Extension that holds SQL query resulsts,
Lists.ID– this should be hardcoded with the ID of the All Subscribers list in your account (Email Studio > Subscribers > All Subscribers > Properties > List ID)
Once you have the updated script, you can include it in a Script Activity as the second step of your automation:
Additional logic to validate the Automation
So far we have created an Automation that will query and update data and it can be run on a weekly, daily, or even an hourly basis, as long as there are records in there that require being updated. So let’s add a couple of additional steps in the Automation flow to make sure that we only run the script in case there are records that need updating.
At the beginning of the Automation, add a simple SQL query activity to clear the Data Extension used in this automation. Create the following SQL query with
overwrite action pointing to the same Data Extension used for the second query in this automation:
And now, add a Verification activity right before the script activity, to verify how many records are present in the Data Extension. If the count is
0, the Automation should stop:
To summarize, here is the final Automation flow that you should have as a result:
Leave a comment below or email me at firstname.lastname@example.org.