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.
7 thoughts on “Synchronize and update email address changes from Sales/Service Cloud to Marketing Cloud using WSProxy”
Awesome article! Will script work from a child BU?
I would run it for all BUs from the Parent. If in child BUs you have different synched DEs, eg. Contact_Salesforce_1, Contact_Salesforce_2, etc – you can query them all from parent and run updates from there.
LikeLiked by 1 person
Your article is mainly for Sales/Service cloud but this synchronization issue is also for Automation studio, where it will not update email address at Sent time like Journey Builder does. The problem that I am facing is that all my automations live in a child business unit and all starting at different times. A bigger problem is that I have around one hundred + automations and I want to avoid any modifications (as much as possible).
A few approaches that I am trying.
1. Adding a SOAP API Script from a child BU.
2. Creating a new Automation in the Parent BU that will update the emails. Change time time of the original BU in the child BU to run 30 minutes later.
Thanks for the tutorial on this! I can get the SQL automation for step 2 to work while working in a child BU if I use “ent._subscribers” for instance, where it puts all of the “correct” syncronized emails into a DE, but the WS proxy script I think is not working for me, where in all subscribers it still is showing the out of sync email address for the subkey. I think it’s doing this because it can’t correctly locate the subscriber key and email address to update in the all subscribers list like with the “ent.” that I had to amend for the SQL script…. any idea how I can amend the WS proxy script to update that? Any help would be greatly appreciated
I would try running it from Parent BU instead od child
Thanks Zuzanna, that did it! I ran the WSproxy script on the parent BU and it’s now reflecting correctly for the synced DE into the subscriber records now. FYI for anyone else who stumbles across this, it has to be on the parent BU to reflect the all subscriber emails for each subsrcriberkey.
Great Article!! I wonder how to include the Salesforce members don’t exist in Subscriber List because of no existing email value. When an email address is provided on the sales cloud how I can create the same member in the subscriber list with the email address provided.
This would be an addition to the update email query you provided 🙂