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
Data Extract Activity - Exporting the tracking out
File Transfer Activity - Unzipping the tracking file
File Transfer Activity - Placing tracking file into FTP folder
Import Activity - Importing from FTP folder
Query Activity - Comparing the deltas with the Logging Data Extension
Query Activity - Adding the results to the Logging Data Extension
Data Extract Activity - Extracting the Delta Data Extension
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.