Did you read my post about using multiple email addresses for a single subscriber? Here I explained how important it is to ensure, that the email address stored in All Subscribers list is identical to the email address in your sendable data extension, when working with Salesforce Marketing Cloud:
When a send to a data extension occurs, the following process occurs at sendtime, under normal circumstances:
- The data extension field related to the Subscriber Key value on the All Subscribers list is identified
- The value for each record in the data extension is looked up on the All Subscribers list as Subscriber Key
If the value does not exist, a new record is created:
- If there is no field with an Email Address datatype, nothing is added to the All Subscribers list, and subscribers who do not exist will not be sent to
- If there is a field with an Email Address data type, a new subscriber is created on the All Subscribers list with that email address
The related Subscriber Key field value is set as Subscriber Key on the All Subscribers list and the value in the field set as an Email Address datatype will be set as Email Address on the All Subscribers list
If the value does exist:
- The Email Address value on the All Subscribers list is sent to, regardless of the Email Address value present in the data extension
- The Email Address value on the All Subscriber list is not updated
If you are importing your subscribers from an external source, it is important to ensure that you update your All Subscribers list, with the most recent email address being imported.
You can do this using a combination of data extensions, queries and SSJS.
Assuming you receive updates in CSV files on the SFTP, and import these into Customer_Import data extension. This will be the first step in your automation:
Next, you should compare the email addresses just imported, with those in your all subscribers list. This can be done using following query activity:
Containing this SQL:
select imp.subscriber_key, imp.Email_Address as new_email, ls.emailaddress as old_email, getdate() as identified from customer_import imp inner join _listsubscribers ls on ls.subscriberkey = imp.subscriber_key where ls.emailaddress <> imp.email_address
You can then output the result to a data extension, called Updated_Emails:
|Name||Type||Not NULL||PK||Default Value|
Next, you will have two activities in the following step:
This query will archive the recently updated emails in a “logging” data extension (with same data model as above), so you will have the overview of which updated emails have been identified and at which date (you can never log too much).
While the next step, being a script activity, does the actual update of email addresses in all subcribers list:
Using following code:
You have now successfully ensured your email addresses being consistent across your sendable data extensions and all subscribers list.
Do remember, that the script needs to be executed in your ENT (root) business unit.