top of page
  • Writer's pictureChristopher Carswell

Finding changes in Subscriber status

Updated: Jun 8, 2020

Some projects require the comparision of All Subscriber Statuses, for the purpose of synchronisation with 3rd party systems, or perhaps even other Enterprises (as was the case for me).

When dealing with a large enterprise account with 10 million subscribers, the data view _Subscribers could be used to query against, but I've found it will return inconsistent results.

This might not seem apparent at first, since all other data views can be trusted, but when checking the subscribers status of a person in _subscribers compared to the All Subscribers Status, discrepancies will be found, and when creating a solution to utilize this data, even one discrepancy will be enough to mistrust the source.

Marketing Cloud offers another viable solution, which involves using a Data Extract of the Tracking Extract type to extract the data to FTP, and re-import into a Data Extension, which can then be queried against a Log Data Extension to return the "deltas".

Automation Steps Summary

  1. Data Extract Activity - Exporting the tracking out

  2. File Transfer Activity - Unzipping the tracking file

  3. File Transfer Activity - Placing tracking file into FTP folder

  4. Import Activity - Importing from FTP folder

  5. Query Activity - Comparing the deltas with the Logging Data Extension

  6. Query Activity - Adding the results to the Logging Data Extension

  7. Data Extract Activity - Extracting the Delta Data Extension

  8. File Transfer Activity - Placing file into FTP folder

Automation Steps Detail

Step 1 - Data Extract Activity

  • Description: Extracts the All Subscribers from the backend

  • File Naming Pattern:

  • Extract Type - Tracking Extract

  • Rolling Range: 90 Days

  • UTF-8

  • [Comma] delimited

  • Extract Fields - False

  • Extract Subscribers - True

  • Include All Subscribers - True

Step 2 - File Transfer Activity

  • Description: Transfers the file from the Safehouse to FTP Folder

  • File Naming Pattern:

  • File Action: Move a File From Safehouse

  • Destination: ExactTarget Enhanced FTP

Step 3 - File Transfer Activity

  • Description: Unzips the file in the FTP Folder

  • Details: Unzip Compressed file - True

  • File Naming Pattern:

  • Source File Location: ExactTarget Enhanced FTP

Step 4 - Import Activity

  • Description: Imports the All Subscribers file back into a Data Extension

  • Data Extension Name: AllSubsSync_AllSubscribersImport

  • Fields:

  • ClientID (Number)

  • EmailAddress (Text)

  • SubscriberID (Number)

  • Status (Text)

  • DateHeld (Date)

  • DateCreated (Date)

  • DateUnsubscribed (Date)

  • SubscriberKey (Text)

  • None of these fields are "Required" or "Primary keys"

  • Data Action: Overwrite

  • File Mapping: Map by Header Row

Step 5 - Query Activity

  • Description: Finds the differences in status changes compared to the Log Data Extension

  • Query Text:



SELECT DISTINCT t1.[SubscriberKey]



,t2.[Status] as [PreviousStatus]

FROM [AllSubsSync_AllSubscribersImport] t1 LEFT JOIN [AllSubsSync DE - Log] t3 ON t1.SubscriberKey = t3.SubscriberKey

) t1



FROM [AllSubsSync DE - Log] t2


t1.[SubscriberKey] = t2.[SubscriberKey]

OR (

t1.[SubscriberKey] IS NULL

AND t2.[SubscriberKey] IS NULL


t1.[EmailAddress] = t2.[EmailAddress]

OR (

t1.[EmailAddress] IS NULL

AND t2.[EmailAddress] IS NULL


t1.[Status] = t2.[Status]

OR (

t1.[Status] IS NULL

AND t2.[Status] IS NULL

AND t1.Status IN ('Unsub','Undeliverable')

  • Target Data Extension: AllSubsSync DE - Merge

  • Data Extension Fields:

  • SubscriberKey (Text) Primary Key

  • EmailAddress (EmailAddress)

  • Status (Text)

  • PreviousStatus (Number)

  • None are required

  • Data Action: Overwrite

Step 6 - Query Activity

  • Description: Adds the resultant of the previous query to the Log Data Extension

  • Query Text:

SELECT * FROM [AllSubsSync DE - Merge]

  • Target Data Extension: AllSubsSync DE - Log

  • Data Extension Fields:

  • SubscriberKey (Text) (Primary Key)

  • EmailAddress (EmailAddress)

  • Status (Text)

  • TimeStamp (Date - Current Date)

  • None are required

  • Data Action: Overwrite

Step 7 - Data Extract Activity

  • Description: Exports the results of the previous query

  • File Naming Pattern

  • AllSubsSync_Export_Delta_%%Year%%%%Month%%%%Day%%.csv

  • Extract Type - Data Extension Extract

  • [Comma] delimited

  • DECustomer Key [ Same as Step 5 Data Extension]

  • Has Column headers: True

  • Text Qualified: True

  • Uses Line Feed: false

Step 8 - File Transfer Activity

  • Description: Transfers the file to the FTP for further import

  • File Action: Move a File From Safehouse

  • AllSubsSync_Export_Delta_%%Year%%%%Month%%%%Day%%.csv

  • Destination: Enhanced FTP Export


Running this automation will only output the changes in the All Subscribers statuses and is ready for exporting to another system as required

automated logging

Combine with my Log Emailer solution to automatically emails the results of this Automation to yourself every day, allowing you to easily keep track of how many deltas were created.

61 views0 comments

Recent Posts

See All


bottom of page