Shibu Abraham

How often does your marketing team send out communications to a subscriber? Are the subscribers in your database getting too many emails in a week? Does this lead to many unsubscribes and deliverability problems? Are you able to control the emails that are sent to a subscriber for a specific duration? We can help resolve this through the technique of Frequency Filtering.

There are multiple ways you can achieve Frequency Filtering – and the solutions can vary for each organization depending on their processes. In this article, we will look at a simple approach on how to implement Frequency Filtering in your Salesforce Marketing Cloud account using the following steps:

• Pre-Configuration: Using Profile Attributes
• Send Data Filtering: Send Log and Filter Activity OR Data Views and Query Activity
• Subscriber Exclusion: Using Exclusion Script

Now, let’s look at the detailed steps on how to set this up in your instance. For this solution, we will assume that you wish to restrict emails being sent more than once per day to a subscriber – i.e.

• Frequency Duration = Daily
• Max email sends allowed for a subscriber for the Frequency Duration = 1

You can tweak these values on the solution as needed – for e.g. some organizations may need the Frequency Duration to be Weekly – i.e. subscribers should receive an email only once a week.

Pre-Configuration

The Pre-Configuration step involves creation of a Profile Attribute to capture the number of maximum Email Sends per duration that should be allowed for a subscriber. You may keep this attribute as hidden if your marketing team would like to control the send frequency. However, if you wish to give the subscribers the option to control the frequency of with which they may receive emails, then this field can be exposed on the Profile Center.

1. Go to Email Studio > Email > Subscribers > Profile Management
2. Click on "Create" button to add a new **Profile Attribute** with the following properties:
1. General Tab
1. Name: Max Daily Email Send Count
2. Hidden: Checked (if to be controlled internally, else can leave it unchecked)
2. Data Tab
1. Data Type: Numeric
2. Default Value: 1 (you may change this default value as decided by your marketing team’s process – if you leave it at 1, then this means the send to this subscriber will be restricted to once per frequency duration)
3. Max Length: 3 (you may even keep this as 1 if you are sure you won’t need to send more than 9 times to a subscriber during a frequency duration)

Once the MaxDailyEmailSendCnt profile attribute has been saved, we can move to the next step – which is where we filter the Email Send data based on the desired Frequency Duration.

Send Data Filtering

Option 1: SendLog and Filter Activity

In the case that your organization is using Send Logging, then you may use the SendLog Data Extension to retrieve the Email Send information.

1. If you do not have Send Logging enabled, then go ahead and create the SendLog Data Extension.
1. Go to "Contact Builder" > "Data Extensions", create a new "Standard Data Extension"
2. Select "Create" from Template option – and choose the SendLog template as shown below
3. Save the Data Extension – it will show several pre-populated fields as part of the template

2. Now add 2 more custom fields to the SendLog DE (if you have an existing SendLog DE, do check and add if these fields do not exist):
1. Email Address – (type = EmailAddress) – this should match with the Profile Attribute “Email Address” on the Subscriber so that the email id of the subscriber gets populated in the SendLog DE during send time.
2. Send Date – (type = Date, Default Value = Current Date) – this will get populated with the date on which the send happens for each record being entered into the SendLog

The SendLog DE would now store all Email Send details for the Account / Business Unit (depends on how it has been previously configured). We can filter this Email Send Data for a specific duration and then use the same for the final Exclusion step.

3. Create a Filter to segment subscribers from the SendLog DE with email id who were sent one or more emails for current day
1. Go to Email Studio > Subscribers > Data Filters
2. Create a new Filter with following properties:
1. Name: SubscribersSentEmailToday
2. Source: SendLog Data Extension
3. Filter Criteria: SendDate is equal to Today

This Filter would segment and provide all subscribers in the SendLog DE that has a SendDate value of the current date – which means, all subscribers who were sent emails today.

Now if you want a different Frequency Duration – let’s say 7 days instead of daily, then all you need to do is change the Filter criteria in this step accordingly. For e.g. you can check for the “SendDate to be after Today – 7 Days”.

Sometimes, you may have a business scenario where you do not want Triggered Sends to be counted towards the Frequency Filtering as it may be used for sending transactional emails. In this case, you can add the Filter criteria to include only the records that have an empty TriggeredSendID. The resulting records in the Filtered Data Extension would not have any subscribers that were the recipients of a Triggered Send – however if they also did receive a non-Triggered Send email, then their record would populate in the data extension.

4. The third step would be to create a Filter Activity and schedule this through an Automation Workflow
1. Go to "Journey Builder" > then go to "Automation Studio" > Activities > Filter
2. Create a new "Filter Activity" with the following properties:
1. Name: Refresh Todays Email Sends Info
2. Filter Definition: SubscribersSentEmailToday (select this Filter that we created in step 3)
3. Target Data Extension Name: TodaysSendDE
3. Save the Filter Activity
4. Go to the Overview Tab and click on New Automation
5. For Starting Source, pull in Schedule
1. Configure the Schedule based on how often you wish to run this Filter Activity Refresh
6. Under Step 1 of the workflow – pull in Filter Activity – and select “Refresh Todays Email Sends Info” that we created in 4b
7. Save the Automation Workflow – you may run this manually using the Run Once option or schedule it to run every few hours or daily depending on your requirement.

Please do note that a Filter Activity overwrites the data in the target Data Extension (in our case TodaysSendDE) – so as long as the Filter criteria is met, the data will be populated in the target DE. You will need to choose the appropriate times when this Activity should run daily (probably in between multiple Email Sends that you have) in order to have the updated data available for Exclusion.

1. Once the Automation has run, go to Contact Builder > Data Extensions
2. Select the TodaysSendDE – verify that the appropriate Send records have been populated based on the criteria in our Filter

Option 2: Data views and Query Activity

In case your organization is not using Send Logging, or you are unable to get all Email Send info in the SendLog DE, then we have another option to retrieve the Subscriber Send information. This time, we will use the System DataViews and a Query Activity.

1. As a first step, we need to create the target Data Extension that we would be using for the Exclusion step
1. Go to Contact Builder > Data Extensions
2. Create a new Data Extension
1. Name:TodaysSendDE
2. Data Retention: Keep an appropriate retention policy (we will use the Overwrite option from the Query Activity so that old records will be removed automatically during the Query update)
3. Leave Sendable as un-checked as we will not be using this Data Extension for Email Sends
4. Create the fields in the DE as shown below and save

2. Once the Data Extension is ready, let’s build the Query Activity to fetch the Email Send Data.
1. Go to Journey Builder > Automation Studio > Activities
2. Create a new Query Activity with following properties:
1. Name:Retrieve Todays Email Send Data
2. Target Data Extension:TodaysSendDE (that we created in Step 1)
3. SQL Query: As shown in the code snippet below

3. Save the Query Activity and now let’s use this in an Automation Workflow.
1. Go to the "Overview Tab" and click on "New Automation".
2. For "Starting Source", pull in "Schedule"
3. Configure the Schedule based on how often you wish to run this Query Activity
4. Under Step 1 of the workflow – pull in Query Activity – and select “Retrieve Todays Email Send Data” that we created in 2b.
5. Save the Automation Workflow – you may run this manually using the Run Once option or schedule it to run every few hours or daily depending on your requirement.

4. Once the Automation has run, go to Contact Builder > Data Extensions
1. Select the TodaysSendDE – verify that the appropriate Send records have been populated based on the criteria in our Filter

Subscriber Exclusion

Now that we have the Email Send information for the desired Frequency Duration in the TodaysSendDE Data Extension, let’s see how we can use this for excluding subscribers from being sent emails beyond the limit that we have set in the Profile Attributes (as part of Pre-Configuration).

1. For excluding Subscribers during each Email Send, we can use an Exclusion Script that checks the number of records a Subscriber has in the TodaysSendDE and then compares it with the value of that Subscriber’s profile attributeMaxDailyEmailSendCnt.

In the desired Email Send definition(s), under Target Audience, include the Exclusion Script as shown below:

This Exclusion Script uses AMPscript functions – if the condition in an Exclusion Script evaluates to True, then the Subscriber is excluded from the current Send. The script is evaluated for each individual Subscriber that is in the Target Data Extension of the Send.

Script Demystified

The Exclusion Script uses the following functions:

1. LookupRows – This AMPscript function returns a set of rows from a Data Extension based on a lookup Column and lookup Value. In the above solution, we use the Subscribers emailaddr value (which is a system attribute – hence not in double quotes) and check it with all the values in the “Email Address” column of the TodaysSendDE Data Extension – and then return the rows that have a match.
2. RowCount – This AMPscript function returns the count of rows of a Rowset – when used with the LookupRows function, we can identify the number of rows returned from the Data Extension that satisfies the lookup condition. In our solution, this should give us the total number of records the current Subscriber has in the TodaysSendDE.
3. AttributeValue – This AMPscript function returns the value of an attribute based on the context of the Subscriber. In our solution, we use this function to return the value of the MaxDailyEmailSendCntprofile attribute for the current Subscriber.

In summary, the Exclusion Script checks for the number of rows a Subscriber has in the TodaysSendDE (which has the list of all subscribers that was sent emails in the desired Frequency Duration – e.g. Daily, Weekly, etc.) and if this is greater than the value that was set in the Profile AttributeMaxDailyEmailSendCntto control Send Frequency, then the condition will return true and the Subscriber will be excluded from the Send.

Let’s say we have a Subscriber A that hasMaxDailyEmailSendCnt = 1and was already sent an email today. If we are going with a Daily Frequency Duration, then we can run the automation workflow and have his data added to the TodaysSendDE. Subsequently, if the Exclusion Script is added for the next Send today, then the condition would return TRUE as (1 >= 1) for this Subscriber A – and hence he would be excluded for this and all future sends today.

Conclusion

We have now seen how we can set up a Frequency Filtering mechanism in our Salesforce Marketing Cloud account. In order to change the limit for the Frequency Duration, teams can just change the profile attribute value. In the above solution, we have used Email Address for the lookup condition – this means that if we have multiple subscribers with the same email id (for e.g. say members of a household), then all of them would be restricted from the subsequent sends based on Frequency Filtering.

In case you wish to use the Subscriber Key instead of the Email Address, then you can use the _subscriberkey system attribute in the Exclusion Script as shown below:

Overall, a marketing team or the ones formulating the strategy whether they are internal resources or agency partners must take into account the number of records in the SendLog or the DataView Query before implementing this solution.

In scenarios where the size of the audience is too large and it may not be feasible to run Refresh and query activities multiple times, then this solution may not be feasible. This is because the effort involved for larger projects may be too complex at times that leaves it open to human error without sufficient time for quality assurance to occur.