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:

• How to sync email address changes from Sales/Service Cloud to All Subscribers list in Salesforce Marketing Cloud

• Re-activate subscribers after email change

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

view raw update-all-subscribers-sql.sql hosted with ❤ by GitHub

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>

view raw update-all-subscribers-wsproxy.html hosted with ❤ by GitHub

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.

---