SFMC Automation: Log Changes to the All Subscribers Status Field

The _Subscribers and _ListSubscribersData Views in Salesforce Marketing Cloud have a wealth of information that can be used to uncover when a subscriber was added to the system, when they unsubscribe and their current status (active, unsubscribed, bounced or held).

Amongst the most valuable information nested in these views is the subscriber status which signifies whether a subscriber can be contacted or not. Unsubscribes cannot be emailed commercial messages due to explicit or implicit opt-outs, and subscribers in held status also cannot be contacted due to either 1 hard bounce or 3 consecutive e-mails being rejected (this protects your deliverability by ensuring you are not sending to inactive addresses).

Subscriber Status in Marketing Cloud

Subscriber Status | Training | Salesforce

However, these Data Views are lacking a DateModified field which would actually make it super convenient to know exactly when the subscriber status changed, what it changed from and what it changed to. Lucky for us there is a Data Extract activity and Automation we can set up to capture this information.

Subscriber Change of Status Audit Logging

The automation we will be setting up will create a zipped Data Extract containing a csv file, which is then uploaded to the FTP, unzipped and re-imported into a Marketing Cloud data extension. Finally, we augment the data we just imported with SQL Activities to make it more useable and add additional context. The automation steps are pictured below.

Automation workflow activities

Before we begin, we need to create a couple of data extensions that will house our data that we’ll be importing into SFMC from the data extract csv file uploaded to the FTP.

DE: StatusChangeAuditLog_DE

DE: StatusChangeSummary

Automation Setup

Step 1: Data Extract

Create a Data Extract activity and choose Tracking Extract as the Extract Type. The Data Extract activity will produced a zipped file. Therefore my File Naming Pattern is: StatusChangeAuditLog_%%Year%%%%Month%%%%Day%%.zip

Data Extract Properties

In the configuration window, untick all the pre-filled checkboxes and ensure just the Extract Status Changes is ticked. Complete the rest of the configuration.

• Account IDs: your specific Business Unit MID
• Character Encoding: UTF-8
• Column Delimiter: Comma
• Format: CSV

Data Extract Configuration

Normally you will use a Rolling Range of 1 Day. This would reflect a daily push all the prior days’ data from Salesforce.

Once this is setup, it will be necessary to create a file transfer activity as next step to transfer the file out to a FTP, based on the file pattern chosen during the data extract configuration.

Step 2: File Transfer — Move File from Safehouse

In this step you need to define the naming pattern for the delivery that is the same as the name used in the data extract activity.

StatusChangeAuditLog_%%Year%%%%Month%%%%Day%%.zip

Step 3: File Transfer — Manage File (unzip)

Add another File Transfer activity to the workflow, but this time choose to Manage File. This activity is able to extract the contents out of a zip file found in the Marketing Cloud’s Enhanced FTP directory. Be sure to check ‘Unzip compressed file’ in the File Management Actions.

Step 4: Import File

Create the new import definition with the below settings. Its important to note that the file name will be different from the zip file. The CSV file name will be called StatusChange.csv . Think of it like you have a file inside a folder on your computer. The file can be named differently to your folder name.

• File Location: ExactTarget Enhanced FTP Import Directory
• File Naming Pattern: StatusChange.csv
• Date Format: English (United States)
• Delimiter: Comma
• Bad Data Behavior: Skip rows with bad data
• Import Options: Respect double quotes ( “ ) as a text delimiter
• Mapping: Map by Header Row
• Destination: StatusChangeAuditLog_DE

Step 5: SQL Activity

This activity will add RowNumber to each record, ordered by oldest to newest. If will help us to very easily see the order in which changes to the subscriber status occurred over time.

SQL to add SortOrder column to the data

Step 6: SQL Activity

This query creates a delimited string that combines all the Status changes in the order that they occurred into a single string.

SQL to create the delimited string

Automation Output

After the automation has completed successfully, you’ll see in the StatusChangeSummary DE that Subscriber’s have a unique row with all the OldStatus and NewStatus fields listed in pairs separated by a comma.

For example, the first record below transitioned from: Deleted → Normal → Held. Their current status is Held and this was updated on August 24, 2020.

Check the Salesforce documentation for this Data Extract. OldStatus and NewStatus fields from the DX Activity can have these values: Normal, Held, Unsub, and Deleted. Note that the status of ‘Normal’ in this DataExtract coincides with two All Subscribers statuses: both ‘active’ and ‘bounced’ .

Finally, you can schedule this automation to run Daily. Adjust the rolling date range in the data extract activity to 1 (previous) day and ensure the import activity’s data action is set to ‘Add Only’. The first SQL activity should be set to update and the second one set to overwrite. In this manner, the previous days data will be imported into SFMC and and the queries will update the DE’s with the correct sorting order after the new data has been ingested.

Try it out and let me know how you go!