Update All Subscriber List in Marketing Cloud

The All Subscribers list in Marketing Cloud is considered the master list of records for the Marketing Cloud Email Studio account.

Every subscriber in your account appears in the All Subscribers list. Subscribers added to a subscriber list join the All Subscribers list at the same time. This also happens with the standard connector and the Synchronised Data Extensions from Salesforce.

So far so good, now how about if you want to update any of the attributes (fields values) on that list… well that’s not that simple. Hence writing this article, allow us to shed some light!

The Theory

Generally you will use Data Extensions (think of them like objects) to store attributes (fields values). A data extension in Marketing Cloud Email Studio is a table within the application database that contains your data.

You can import data into data extensions with the application interface. You can create, update, and delete rows in a data extension with the SOAP API. Data kept in a Data Extension relates to a subscriber but does not fit in the subscriber profile and preference attributes.

The Practice

This is for the subscriber profile attributes, the one that you may need to update the All Subscribers list.

There’s one reason why you’ll want to make sure that list updates with the latest information from Salesforce, and that is the subscriber email address. For some mystery reason the master list doesn’t update with the connector synchronisation 🤷‍♀️

Also to make it even simpler and more user friendly you can pull to Marketing Cloud some key contact details and update them into the All Subscribers list. Right let’s get to it then!

The How

If beside the email address you want to update any other attribute you need to create that from Email Studio > Subscribers > Profile Management

Then to automate the data update we will use Automation Studio (Journey Builder > Automation Studio). Just before navigating there create a Data Extension to use for the automation, to allow the same values you want to update as well as to the SubscriberKey and I recommend also adding the Status.

The automation we are to create is going to perform four functions:

• Retrievs information from Salesforce
• Extracts information into the Marketing Cloud sFTP
• Transfers the file from the sFTP to update the master list
• Refreshes All Subscribers List with the most recent attributes from Salesforce

1) The first step queries the Account synchronized data extension for updated email addresses and in our example we also want to bring a custom field value “BusinessType”.

In our automation we select SQL Query activity for this. Also select the target Data Extension we created earlier as a placeholder, and we mark the data action as ‘Overwrite’.

SELECT
    sfdc.ContactId as SubscriberKey,
    sfdc.Email as EmailAddress,
    sfdc.BusinessType__c as BusinessType,
	allsub.status as status
FROM
    [Contact_Salesforce] as sfdc with (nolock)
inner JOIN
	 [_Subscribers] as allsub with (nolock)
ON sfdc.ContactId = allsub.SubscriberKey
	inner join [_EnterpriseAttribute] as ent with (nolock)
	On allsub.SubscriberID = ent._SubscriberID

Lets translate that:

Go and find the ContactID and call it SubscriberKey, the email and name it EmailAddress, the BusinessType and call it BusinessType, also the status (this one is in allsub. meaning the famous All Subscribers List).

Then we instruct the query to select from the Contact object (which should be set up as the connector Synchronised Data Extension) we link it with the All Subscribers List and perform the mapping with the ContactID and the SubscriberID.

(Note to future-self this query was running in parent business unit, the ent. prefix helps on child business unit to look to parent)

---

2) The second step extracts records from the temporary Data Extension we created earlier. Here we use a Data Extract activity type.

I’ll give you the settings in a visual here as will make more sense I think, for me the key here was to make sure the ‘DECustomer Key’ match exactly to the temporary Data Extension ID, also having the column headers so it can map easily later AND Text Qualified = False.

(this last one gave me a bit of a fight, as I was adding “” on each value on the first row of the csv file)

It would look something like this:

---

3) The third step transfers and stores the temporary Data Extension data extract on the FTP server. For this step we use a File Transfer type.

As per the last step you have a csv with a very specific File Naming Pattern, you need to make sure it’s the same for this step.

Select option ‘move the file from Safehouse’ (thats from your sFTP)

---

4) The fourth step imports the imports the updated data into the Marketing Cloud subscriber record in the All Subscribers List. Hurray! For this we use Import File activity.

We are selecting to extract from FTP, again the File Naming Pattern must match to the previous, coma delimiter (or the same delimiter you set when you create the file)

Set the destination for the All Subscribers (under ‘my subscribers folder’), then map the attributes and cvs column header.

And the very last thing you need to do is schedule your automation! Tada!

Hope this comes useful and tell us which automations have you have done or are there any interesting finds in Marketing Cloud you can share?