Synchronize and update email address changes from Sales/Service Cloud to Marketing Cloud using WSProxy

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 example@test.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:

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 held or bounced that we have an updated email address for:

SELECT
contact.Id as SubscriberKey,
contact.Email as EmailAddress,
CASE
WHEN allsub.Status = 'held' THEN 'active'
WHEN allsub.Status = 'bounced' THEN 'active'
ELSE allsub.Status
END as Status
FROM
Contact_Salesforce as contact
RIGHT JOIN
_subscribers as allsub
ON contact.Id = allsub.SubscriberKey
WHERE contact.Email != allsub.EmailAddress
UNION
SELECT
lead.Id as SubscriberKey,
lead.Email as EmailAddress,
CASE
WHEN allsub.Status = 'held' THEN 'active'
WHEN allsub.Status = 'bounced' THEN 'active'
ELSE allsub.Status
END as Status
FROM
Lead_Salesforce as lead
RIGHT JOIN
_subscribers as allsub
ON lead.Id = allsub.SubscriberKey
WHERE lead.Email != allsub.EmailAddress

Create this as the first step in the automation and save results to the dedicated Data Extension with the overwrite option.

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:

<script runat="server">
Platform.Load("core", "1.1.1");
try {
var deKey = "xxxxx-xxxxxx-xxxx-xxxxx-xx-xxxxxxx", // replace with the external key of the DE holding the query results
prox = new Script.Util.WSProxy(),
objectType = "DataExtensionObject[" + deKey + "]",
cols = ["subscriberkey", "emailaddress", "status"],
moreData = true,
reqID = null,
numItems = 0;
while (moreData) {
moreData = false;
var data = reqID == null ?
prox.retrieve(objectType, cols) :
prox.getNextBatch(objectType, reqID);
if (data != null) {
moreData = data.HasMoreRows;
reqID = data.RequestID;
if (data && data.Results) {
for (var i = 0; i < data.Results.length; i++) {
var subkey = data.Results[i].Properties[0].Value,
email = data.Results[i].Properties[1].Value,
status = data.Results[i].Properties[2].Value;
var sub = {
SubscriberKey: subkey,
EmailAddress: email,
Status: status,
Lists: [{
ID: 'List ID of All Subscribers List' // input the ID of the All Subscribers list in your account
}]
};
var options = {
SaveOptions: [{
PropertyName: "*",
SaveAction: "UpdateAdd"
}]
};
var resp = prox.createItem("Subscriber", sub, options);
numItems++;
}
}
}
}
} catch (e) {
//Write(Stringify(e));
}
</script>

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:

SELECT
null as SubscriberKey,
null as EmailAddress,
null as Status
where 0 = 1
view raw celar-de.sql hosted with ❤ by GitHub

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:

Summary

To summarize, here is the final Automation flow that you should have as a result:


Questions? Comments?

Leave a comment below or email me at zuzanna@sfmarketing.cloud.

6 thoughts on “Synchronize and update email address changes from Sales/Service Cloud to Marketing Cloud using WSProxy

      1. flpdiaz

        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.

        Like

  1. SFMCdude

    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

    Like

      1. SFMCdude

        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.

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s