Business Case:

This is a follow-up to the previous blog here, which discussed about an approach to have the email address updated on All subscriber list when a change is done on CRM on either lead or contact record. The previous solution was a very code-heavy solution, but since those changes are not something that is frequently done the one-time code to have it fire only when needed was a solution that we discussed. Although, not every solution will be a fit for every scenario as every solution has some pro’s and con’s . This one take a very traditional approach of using SFMC automation studio and almost is a code free plug and play solution. So without further ado, let’s jump into this.

Solution Approach:

All subscriber list can be updated using Import activity on automation studio, so this solution utilized that approach. Though, since automation is not real time, we can have this automation run every 24 hours or so in this way the automation doesn’t update the data in the All subscriber list more than 24 hours. To update the Subscribers’ data in the Marketing Cloud Data Extension and get a list of failed attempts often caused by the emails being invalid, we’ll need to follow these steps:

1. Create Data Extension- This DE will hold data from the Lead and/or contact for the records where change is found.

2. Automation: Create Query Activity and get records from SFDC (Synchronized DE)

3. Automation: Create Query Activity to compare SFDC data with All subscriber list.

4. Automation: Create verification activity and to verify the record count is greater than zero, if not the automation stops here to avoid unnecessary runs.

5. Automation: Extract All Subscribers from the Data extension

6. Automation: Transfer All Subscribers into FTP

7. Automation: Update All Subscribers list through import

Also, to get started, we need to create an FTP Account (file transfer protocol account) for transferring files with a host computer using FTP services, which we will use in the Automation process. To do this, go to Setup > Administration > Data Management > FTP Accounts. Then, in FTP Accounts, click on ‘Create User’. Let’s go through all these steps one by one

Solution:

Step 1: Create Target Data Extension for SQL Query Activity

Let’s create a Data Extension with the necessary fields and name it ‘All Subscribers Data’ (or anything you want). For example, we can create a Data Extension with two fields:

• Id

• Email(subscriber email)

We will store our subscriber’s lead/contact data coming from query activity, including their email address and SubscriberKey from Sales Cloud.

Step 2: Automation: Get Record From SFDC

Next, navigate to the Automation Studio in Journey Builder and create a new automation named ‘Update All Subscribers’ or anything you want.

Step 3: Drag and drop “Schedule” on the canvas and set the schedule of the automation based on your use case.

Step 4: SQL Query Activity to get Contact data from SFDC

Now, Choose the ‘SQL Query’ activity and drag it to the right on Canvas. In the Choose SQL Query Activity section, click ‘Create New Query Activity’. Enter the name of the Query Activity as ‘Get Records from SFDC’. In the Folder Location, select the folder where the query will be saved. Then, click ‘Next’. In the QUERY step, enter the following SQL query to retrieve saved data about subscribers from Sales Cloud in Data Extension. In Data Extensions, select ‘All Subscribers Data’, which we have created. In the Data Action choose ‘Overwrite’ to ensure the data is overwritten to always get fresh data in the target data extension.

Query Explanation:

Select Email, Id
From ent.Contact_Salesforce_6
Where LastModifiedDate>=dateadd(hh,-24,getdate())

In this query, we are querying Synchronized DE for Contact so update the name with what you have in your Org. Also, I have added a criteria that ensures we fetch records that are modified in the last 24 hours, this is done to ensure we are not getting all the data from CRM but only specific data (Saving the Query run time and reducing the volume of data we have to deal with).

Step 5:SQL Query Activity to get Lead data from SFDC

Same process as above for lead data, if you are not using Leads in your system you can ignore this. Here in the Data Action choose ‘Append’ to ensure the previous data isn’t overwritten and add new lead data.

Query Explanation:

Select Email, Id
From ent.Lead_Salesforce_6
Where LastModifiedDate>=dateadd(hh,-24,getdate())

In this query, we are querying Synchronized DE for Lead so update the name with what you have in your Org. Also, I have added a criteria that ensure we fetch records that are modified in the last 24 hours, this is done to ensure we are not getting all the data from CRM but only specific data (Saving the Query run time and reducing the volume of data we have to deal with)

Step 6: SQL Query Activity to compare SFDC data with All subscriber list

Here we are comparing the data between the All subscriber list and the target data extension retrieving those records that are common and transferring them into a new Data extension

For this please create one final data extension to store the records. I have included this step to again filter the records that require a change in the All subscriber list since their email address have been changed.

Query Explanation:

Select a.Email, a.Id 
from ent._Subscribers s
INNER JOIN AutomationToFetchQueryData a ON a.Id = s.SubscriberKey

In this query, we are querying the AllSubscriber list and Target Data Extension by comparing subscriberkey and ID. And transfer the common data into the Final Data extension. Here in the Data Action choose ‘Update’ to ensure the previous data isn’t overwritten and is constantly added and updated.

Step 7: Verification Activity to verify the record count is greater than zero

Now we are creating a verification activity to check the count of records in the Final data extension. In this step, if the count is equal to zero the automation will stop at this stage.

Step 8: Extract All Subscribers

Select the ‘Data Extract’ activity and drag it to the right on the Canvas. After clicking ‘Create New Data Extract Activity’, click ‘Choose’ in Data Extract. In Properties, enter ‘Extract All Subscribers’ in the ‘Name’ field. For the ‘File Naming Pattern’, provide a file name, for example, ‘ImportAllSub’. In the ‘Extract Type’ field, select ‘Data Extension Extract’. Then, click ‘Next’.

In the Configuration section, select ‘Has Column Headers’ and enter a ‘,’ (comma) in the Column Delimiter field. For the DECustomer Key, enter the External Key Data Extension we created earlier.

Step 9: Transfer All Subscribers to FTP

Select the ‘File Transfer’ activity and drag it to the right on the Canvas. After clicking ‘Create New File Transfer Activity’, click ‘Choose’ in File Transfer. In the Properties section, fill in the Name field with ‘Export All Subscribers’. Choose ‘Move a File From Safehouse’ as the File Action, then click ‘Next’.

In the Configuration section, input ‘ImportAllSub’ into the File Naming Pattern field, which refers to the same file created during the ‘Data Extract’ step. For the ‘Destination’, select ‘ExactTarget Enhanced FTP’, and then proceed by clicking ‘Next’.

Step 10: Import and update All Subscribers list

Select the ‘Import File’ activity and drag it to the right on the Canvas. Click ‘Choose’ in the Import File, after clicking ‘Create New Import Definition’. In the Properties section, enter ‘Update All Subscribers’ in the ‘Name’ field. If you want to receive emails with this data in .txt format, select ‘Send notification email to:’ and provide your email address. In the Import File section, select ‘ExactTarget Enhanced FTP’ as the File Location. Enter ‘ImportAllSub’ in the File Naming Pattern field. On the right-hand side, in the ‘Delimiter’ section, choose ‘Comma’. Make sure to unselect ‘Respect double quotes(“) as a text delimiter’. In the Destination section, choose ‘Subscriber List’. Then, click on the folder named ‘my subscribers’ and select ‘All Subscribers’. Finally, click ‘Next’. In the Mapping section, select ‘Add and Update’ and click ‘Next’.

Conclusion

After completing the previous steps, you can schedule this automation to run every 24 hours or whatever your business case requires. This ensures that the data in Marketing Cloud is continuously updated with Sales Cloud.

The automation should ultimately look like this: