0

In Salesforce Pardot we have dynamic list which behaves as below:

When the criteria matches, Pardot adds them to the list for you. When the criteria no longer matches, Pardot takes them off the list for you.

Similar thing I want to implement in salesforce marketing cloud.

Means a dynamic suppression list which will work with email frequency capping.

A subscriber should not get more than 2 email in a week.

Used below sql for automation

    select tde.SubscriberKey,
tde.EmailAddress,
'False' as FrequencyCappingFlag
from FrequecyCappingTargetedDE tde inner join
_sent sdv on tde.SubscriberKey = sdv.SubscriberKey
where sdv.EventDate > DateAdd(day, -(convert(int, (select value from Configs cs where cs.[key] = 'DaysForCapping'))) , getdate())
Group by 
tde.SubscriberKey,
tde.EmailAddress
having count(tde.EmailAddress) > convert(int, (select value from Configs cs where cs.[key] = 'EmailFrequencyCapping'))
2
  • AFAIK there is no standard way for frequence capping, it is part of the April release of einstein inside the marketing cloud.You have to create a own dataextension with own sql automations that get data from the sendlog (most likely) and then create an exclusion scriptwith the functionality that subscribers with 2 or more rows of that dataextension do get pushed out.Atleast that is what i would have done. I am curious if anybody has a better idea though. Most likely Gortonigton (or similar) will answer – Johannes Schapdick Mar 29 '19 at 9:36
  • Hi @JohannesSchapdick thanks for your view on this question. I have already done with setting up a flag in Targetted DE for checking valid or not for capping. But problem comes into picture is frequency of automation.Though we set this to run every hour but in one hour if system sends more than 3 emails(From Salesforce using Triggered email send) that time our logic breaks. So I am looking for Dynamic list like feature. – Dhananjay Patil Mar 29 '19 at 10:10
0

This is just my opinion, but this sounds like a very edge case, especially if you are looking at a cap of 2 emails per week. How often could there possibly be that inside an hour period 3+ emails are sent to the same email address? I feel like the hourly automation should more than handle your needs around keeping the suppression list updated and the below solution will likely be a large cost (hours and processing usage) with very little returned value.

Opinion aside though, the following should help to meet your needs:

Create 4 automations running in 15 minute intervals to run your query (on hourly schedule). This will basically update it every 15 minutes, which should be equal to the dynamic lists in Pardot which are updated 'a few times per hour'. Now this means that you would need to make sure your query can run in < 12 minutes or it could very likely cause errors or other issues. I definitely would not recommend this when targeting a DataView (like _Sent) as they are notoriously slow. As your volume and frequency increases, the slower and more process heavy the query will become.

To combat this, I would create a send log with a 7 day data retention policy and use that for your query instead. This will keep it performant and efficient. You then just have a daily query take your Current send log and push it to a 'storage' DE or export it to store for your own records. This way you will retain the send log information beyond the 7 day retention period.

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.