markus.codes

When using journey builder and sending to contacts from Sales/Service Cloud for example, email addresses that have been changed there aren’t used as the receipient address, if you don’t have this information in the entry source and specifically tell journey builder to use this address only. To ensure the correct email address is used everywhere in Marketing Cloud, you need to update All Subscribers list. And in this article you can find out how..

EDIT (October 9, 2019): If you also like to reset the ‘Held’ and ‘Bounce’ status, please check out my blogpost “Re-activate subscribers after email change - UPDATE of ‘How to sync email address changes from Sales/Service Cloud to Marketing Cloud?’”.

It might sound strange at first, but this currently is the best way to update the All Subscribers list with data from Sales/Service Cloud on a regular basis: Create a scheduled automation containing these four activities:

SQL Query Activity

This activity checks for changed email addresses in your synchronized data extensions and writes them to a temporary data extension. So first you need to create this temporary data extension (with Contact Builder or Email Studio) with three fields:

• SubscriberKey (Primary Key, Type: Text with length of 18 characters - as it contains the Salesforce Contact Id)
• EmailAddress (Type: EmailAddress)
• Status (Type: Text with length of - to be safe - 50 characters)

When configuring this activity, select your just created target data extension and set the data action to “Overwrite” and add this SQL-snippet:

SELECT
    sfdc.Id as SubscriberKey,
    sfdc.Email as EmailAddress,
    allsub.Status
FROM
    Contact_Salesforce as sfdc
RIGHT JOIN
    _subscribers as allsub
ON sfdc.Id = allsub.SubscriberKey
WHERE sfdc.Email != allsub.EmailAddress

Data Extract Activity

This activity extracts the data from your temporary data extension and writes it to a CSV-file, which is placed in your account’s Enhanced FTP. When configuring this activity select a delimiter (a semicolon for example) as well as a filename and be sure to note down that name as we need it for the following activity.

File Transfer Activity

This moves the CSV-file from activity 2 to the Import-folder of your Enhanced FTP, so Marketing Cloud can process it. Select “Move a File From Safehouse”, add the filename to the file naming pattern and select your “ExactTarget Enhanced FTP” as file destination.

Import File Activity

Imports the data from the CSV-file to the All Subscribers list. For configuration add the file naming pattern, the delimiter of the CSV-file and select “All Subscribers” list in the folder “my subscribers” as destination for the import. Under “Mapping” select the data action “Update” as we only wan’t to update current subscriber’s email addresses. If you already ran the SQL query activity you can use the generated file as source for the mapping.

Conclusion

The automation should ultimately look like this:

Important: Please note that this is only reasonable if you write the updates, which are made to email addresses via your Profile and Preference Center, to your Sales/Service Cloud as otherwise these changes would be overridden by the data stored in Sales/Service Cloud.