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