0

I have a case where I am being requested that any record that has received an email in the last 3 days needs to be suppressed from additional emails. This includes single mass sends, and any journey the record might currently be in.

What I have drawn out right now is an automation that would run every night to take care of most of this. First there is the SQL Query that finds my records that have received an email in the last 3 days. Then do a data extract, file transfer, and finally import into a suppression list.

That would solve my case for mass sends, since the data extension with all of the SubscriberKeys the SQL Query picked up could be added to the suppression bucket.

Now where I am having some issue is with the individual emails in a Journey. Those need to utilize a suppression list, and the way it is set up now it would just continually add records to the suppression list without removing the ones that have not received an email in less than 3 days.

How would I clear the suppression list on a daily basis without deleting the record from All Subscribers. I don't want to delete any subscriber data, just have a record added to the suppression list and cleared every day.

The SQL query to pull the records that have received an email in the last 3 days is:

SELECT count(*) AS TotalSent, SubscriberKey
FROM _Sent
WHERE EventDate BETWEEN DATEADD(day, -3, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP
GROUP BY SubscriberKey
HAVING COUNT(*) > 0
  • You use case sounds like using Auto-Suppression Lists would be more suitable than standard Suppression Lists help.salesforce.com/…. These can suppress sends to an Email Address from specific Send Classifications or Sender Profiles in one or more Business Units, irrespective or whether the send is a batch or trigger. The workflow to populate these is similar to what you have mentioned, only you'll need to import into the Auto-Suppression List, rather than a Suppression List, and use the "Overwrite" option. – Macca Jul 14 at 0:07
  • @Macca this might work as needed. How would I pull the email address from the Sent data view help.salesforce.com/…? As of right now I don't see how I can pull the email address from there in order to import into the auto-suppression For my case, the Subscriber Key is not the email address. – RunRabbit Jul 14 at 20:19
0

Auto-Suppression Lists are likely going to be a better match for your use case than regular Suppressions Lists as they apply to the email address of the subscriber, rather than the SubscriberKey and automatically wash out subscribers from your sends, irrespective of the method of send - batch sends and triggered sends (Journey Builder). Here is an outline of setting up an Auto-Suppression List that applies rate-capping for Commercial Sends in the manner you've outlined.

  1. Create a new Auto-Suppression list in "Email -> Admin -> Send Management -> Auto-Suppression Configuration" and apply it to the Commercial CAN-SPAM Classification Type:

enter image description here

  1. Create a new Data Extension to stage your data before updating the Auto-Suppression List similar to this one:

enter image description here

  1. Create a new Automation with the following steps

  2. Step 1: Add a new Query Activity that overwrites your staging Data Extension created above

    SELECT DISTINCT su.EmailAddress AS [Email Address], CURRENT_TIMESTAMP AS [Date Added] FROM _Sent sn INNER JOIN _Subscribers su ON sn.SubscriberID = su.SubscriberID WHERE sn.EventDate BETWEEN DATEADD(day, -3, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP

  3. Step 2: Create a Data Extract Activity to export the content of your staging table

  4. Step 3: Create a File Transfer Activity to move your extracted file from the Safehouse to your Enhanced FTP's Import directory

  5. Step 4: Create an Import Activity to import the file in the Import directory to your Auto-Suppression List

  6. Schedule your Automation to run every hour, or so

Note: If you're proficient with the API, the above Automation Steps can be simplified by creating your Query Activity programatically and setting its target as the Auto-Suppression list directly and the extract/transfer/import are no longer needed.

It's worth pointing out that the Automation above is running once per hour and so does not stop a subscriber being sent to more than once in a single hour. If this is a concern, consider adding this script to your emails/templates:

%%[UpsertDE("MyAutoSuppressionListName",1,"Email Address",emailaddr,"Date Added",Now())]%%
| improve this answer | |
  • Thank you, this is exactly what I am looking to do. I just tried this new Query Activity and it doesn't seem to pull any records in. I know there have been records that have received an email in the last 3 days. I even made an adjustment to the query and changed -3 to -10 to be sure and it is still producing 0 records in the data extension. – RunRabbit Jul 15 at 18:56
  • If you're operating in a Multi-Business Unit account, try interrogating the "ENT" schema to get your Subscriber records from the Enterprise version of the _Subscribers data view, "ENT._Subscribers" – Macca Jul 15 at 23:45
  • That did the trick. Thank you very much for all your help with this! – RunRabbit Jul 16 at 4:03
0

You can use SOAP API routes of SFMC to interact with Suppression list. Here are the reference

https://developer.salesforce.com/docs/atlas.en-us.noversion.mc-apis.meta/mc-apis/removing_a_subscriber_from_a_suppression_list.htm

Here is another one, you can try SSJS as well

https://help.salesforce.com/articleView?id=000352294&type=1&mode=1

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.