Updating email address in All Subscribers

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:

1. 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
2. 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:

1. The Email Address value on the All Subscribers list is sent to, regardless of the Email Address value present in the data extension
2. 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
subscriber_key	Text(50)	true	true
identified	Date	true	true
old_email	EmailAddress	false	false
new_email	EmailAddress	false	false
to_be_updated	Boolean	false	false	True

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:

<script language=javascript runat=server> 
      Platform.Load("core", "1") 
        var b1 = DataExtension.Init("Updated_Emails"); 
        var data = b1.Rows.Lookup(["to_be_updated"], ["True"]); 
        if(data){ 
          for (var i = 0; i < data.length; i++){ 
            var status; 
            var OldEmailAddress = data[i]["old_email"]; 
            var NewEmailAddress = data[i]["new_email"];  
            var SubscriberKey = data[i]["subscriber_key"];  
            if(SubscriberKey && SubscriberKey != ""){ 
              var results = Subscriber.Retrieve({ 
                Property:"SubscriberKey",SimpleOperator:"equals",Value:SubscriberKey} 
                                               ); 
              if(results && results[0] && results[0].SubscriberKey == SubscriberKey){ 
                var subObj = Subscriber.Init(SubscriberKey); 
                var updSubscriber = { 
                  "EmailAddress": NewEmailAddress, 
                  "Status": "Active"
                }; 
                status = subObj.Upsert(updSubscriber); 
                status = status=="OK"? status + '. Updated.' : status; 
              } 
             
            } 
            else{ 
              status = "Blank subscriber key"; 
            } 
          } 
        }
    </script> 

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.