[SalesForce] Auto Suppression List – Overwrite but maintain date added field with original posting value

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:

  1. 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.

  2. Data extract of query results from data extension

  3. File transfer activity to take the data extract file and place in import folder on FTP

  4. Wait activity for a few minutes to allow time for the large file to move and settle onto FTP

  5. 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!

Best Answer

You can certainly order rows by date and select the oldest using a partition. You could also join the results to another data extension to compare dates. Something like this:

select
  y.emailaddress
, y.subscriberkey
, y.insertDate
from (
  select
    x.emailaddress
  , x.subscriberkey
  , x.insertDate
  from (
    select
      d.emailaddress
    , d.subscriberkey
    , d.insertDate
    , row_number() over (partition by d.subscriberkey order by d.insertDate asc) ranking
    from DataExtension d
  ) x
  where x.ranking = 1
) y
inner join DataExtension2 d2 on (d2.subscriberkey = y.subscriberkey)
where y.insertDate > d2.insertDate

I'd also like to throw out the possibility of using a Server-Side JavaScript Script Activity in your Automation to delete the subscriber from the list.

You can retrieve rows in a Data Extension and iterate through them. Then for each subscriber to remove, retrieve their Lists and remove the unsub list from the array. From there you can just Update the Subscriber object with the new list object. I've done this quite a bit in custom preference centers that manage publication list membership. The only difference here is that you're iterating through a data extension to get the subscribers.

If Script Activities aren't enabled in your account, SFMC Support can enable them for you.

Related Topic