Finding changes in Subscriber status

Updated: Jun 8

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: AllSubsSync_ExportToStrategic.zip

  • 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: AllSubsSync_ExportToStrategic.zip

  • 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: AllSubsSync_ExportToStrategic.zip

  • 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 t1.*

FROM (

SELECT DISTINCT t1.[SubscriberKey]

,t1.[EmailAddress]

,t1.[Status]

,t2.[Status] as [PreviousStatus]

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

) t1

WHERE NOT EXISTS (

SELECT *

FROM [AllSubsSync DE - Log] t2

WHERE (

t1.[SubscriberKey] = t2.[SubscriberKey]

OR (

t1.[SubscriberKey] IS NULL

AND t2.[SubscriberKey] IS NULL

AND (

t1.[EmailAddress] = t2.[EmailAddress]

OR (

t1.[EmailAddress] IS NULL

AND t2.[EmailAddress] IS NULL

AND (

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


Conclusion

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.


contact

© 2019 by Christopher Carswell

Photography by Christopher Carswell

  • Github
  • LinkedIn

email: info@christophercarswell.com

Auckland, New Zealand