This is a common problem - reconciling the database of record (DBOR) to the All Subscribers. This problem also occurs for any manual unsubscribes in the All Subscribers.
My solution would be to run a Query against the All Subscribers to determine which new unsubscribes have occurred in the previous day.
Once you know who these people are, you would want to perform an API call to Salesforce CRM updating these subscribers to Email Opt Out = true.
Since Script Activities don't allow AMPscript, rather run this function as AMPscript in an email looping through all the records and updating Salesforce CRM accordingly. The email should be part of an automation and send can be sent to a "blackhole" email address if need be ([email protected]).
Automation Studio Steps:
- Schedule - daily
- Run a "Tracking" Data Extract, ticking the "All Subscribers" box (I don't trust the
_Subscribers data view for queries, I have found strange results previously)
- Use a File Transfer Activity to extract the zipped file to the FTP
- Import Activity to import the file into a Data Extension
- Query Activity to segment by "unsubscribe within the previous day" (can also filter by the "feedback loop" unsubscribe method)
Send email to a blackhole subscriber (in a list) initiating the "UpdateSingleSalesforceObject()" function against the results of step 5. This would need to use an AMPscript loop:
%%[
SET @DE = "yourDEName"
SET @rows = LookupRows(@DE,"Constant",1) /* Data Extension has a numeric value of 1 as a default on column "Constant" */
IF RowCount(@rows) > 0 THEN
FOR @i = 1 to RowCount(@rows) DO
SET @row = Row(@rows,@i)
SET @subKey = Field(@row,"SubscriberKey") /* Adjust this field to your Data Extension field */
/* Send SF API call to each of them */
SET @updateSFresult = UpdateSingleSalesforceObject("Contact",@subKey,"Email Opt Out","True") /* Double check the actual field name in SF */
NEXT @i
ENDIF
]%%
