Our Salesforce CRM has multiple versions of contacts that are merged under a "master" record. The master record is the one where the email opt out preference lives and therefore what works with the connector into the Marketing Cloud. However, in order to prevent accidentally emails sent to the "non-master" versions of the people that opted out, I have an automation that feeds the "non-master" versions of unsubscribed contacts into our global auto suppression list, running daily. The date added field is generated using the
GETDATE() function as default value on the data extension that the query feeds into. That way, we can see when people are added to the auto suppression list. The query and import activities run as an update (not overwrite) so that the records can be imported into the auto suppression list.
Here's the tricky part. So the above scenario works perfectly. However, in the event that a subscriber that had previously unsubscribed, decides they want to be subscribed and we need to clear the opt out checkbox, the above automation will not remove them from the auto suppression list. Therefore, they would not receive email even after opting back in.
In order to remove those folks from auto suppression list systematically, I believe the only solution is to change from an update to overwrite. However, now the "date added" functionality will break down and will always be the most current date of when the automation ran and will no longer represent the "first time" each record showed up in the auto suppression list.
I'm wondering if a way to get around this may be some SQL syntax that can be used to look at the data extension that it would be populating and compare the existing value in the date added field to
GETDATE(), and then return the oldest value. Then I could use the overwrite functionality on the query and import activities and maintain the goal of keeping the date added field accurate. If it is not possible to have SQL look at the data extension it is about to populate, is another solution maybe adding another SQL activity into the automation that could query the existing data extension and then populate results into yet another data extension? And then a third SQL activity to take results of the second data extension to go back and overwrite the first data extension so that it becomes the starting point the next time the automation runs?
Below is a schematic of the steps of my current automation:
SQL query of non-master versions of unsubscribed contacts....populated into data extension using update. Default value in data extension as GETDATE() for date added field.
Data extract of query results from data extension
File transfer activity to take the data extract file and place in import folder on FTP
Wait activity for a few minutes to allow time for the large file to move and settle onto FTP
Import activity to pick up the csv file from import FTP folder and pump into the auto suppression list (using add and update)
Appreciate any insight or recommendations!