4

I would like to use an Exclusion Script when sending an email from Content Builder where if the Subscriber has received an email in the past week, then the email should be suppressed from sending.

As you can't include variables or AMPscript blocks in emails, I need to create this expression in a single line.

I've tested this logic by creating the following email:

%%[
var @test, @result
set @test = DateDiff(iif(RowCount(LookupOrderedRows('_Sent',1,'EventDate desc','SubscriberKey',_subscriberKey))==1,Field(Row(LookupOrderedRows('_Sent',1,'EventDate desc','SubscriberKey',_subscriberKey),1),'EventDate'),'1/1/2000'),SystemDateToLocalDate(Now()),'D')

if @test < 7 then
   set @result = 'true'
else
   set @result = 'false'
endif
]%%

result: %%=v(@result)=%%

Here's the logic that I'm using in my @test variable:

  1. LookupOrderedRows returns the sent date of last email (from the _Sent Data View)
  2. If RowCount from step 1 == 1, then retrieve the EventDate field from the the _Sent Data View
  3. If RowCount from step 1 != 1 (e.g. no rows are returned), then set the date as 1/1/2000 (as we need to use a date for a date comparison in the next step)
  4. Use DateDiff function to compare last sent date with today's date

This works as expected, if the Subscriber has been sent an email in the past 7 days, then @result has a value of true, and if they haven't been sent an email or don't exist in the Sent Data View, then the value is false.

Now in theory, I can put this all together and use the following logical test in an Exclusion Script:

DateDiff(iif(RowCount(LookupOrderedRows('_Sent',1,'EventDate desc','SubscriberKey',_subscriberKey))==1,Field(Row(LookupOrderedRows('_Sent',1,'EventDate desc','SubscriberKey',_subscriberKey),1),'EventDate'),'1/1/2000'),SystemDateToLocalDate(Now()),'D')<7

However, when I send the email, the job sits as 'In Progress' for several minutes and then eventually appears as 'Canceled'.

Can someone explain why this does not work? I believe it should, but perhaps I'm missing something.

  • I believe this is a performance issue due to how large the data can end up overtime. I have ran multiple tests on your code via 'CloudPages' each time taking anywhere between 1 - 3 minutes or even sometimes timing out and producing a Script Error but not all the time. Let me know if you can produce the same result. This may indicate to us that it is not the ideal solution to LookUpOrderedRows on data views via AMPScript. I guess that also depends on how large the sends are on each MC account. – thechrishaddad Aug 16 '17 at 0:04
  • 1
    There are only 3 sends returned for this Subscriber in the Data View and when I use the AMPscript code in an email, it previews almost immediately, so I don't think it's a timeout issue. Perhaps it's just too complex for an Exclusion Script to parse? FYI, you will get the same behaviour the script can't be parsed (e.g. include AMPscript blocks in the Exclusion Script). – Eliot Harper Aug 16 '17 at 0:09
  • Hmm odd, I mean your code is only producing one return value and that is either true or false, so not too sure why else it would fail. I initially thought too many function calls to be handled in an Exclusion Script as it has to run of every single send but the Exclusion Script is not complex at all, simply just a few nested function calls with a date comparison. – thechrishaddad Aug 16 '17 at 0:13
  • 1
    Hi Adam, you could use subscriberId or subscriberKey. I'm using SystemDateToLocalDate to compare the date sent (which is stored as local time in the Sent Data View) with the current local date. If I use Now then this returns US Mountain Time, but I need to compare local time (Australian Eastern Standard Time) as the AEST time difference is one day ahead. – Eliot Harper Aug 16 '17 at 22:40
  • 1
    Have you tried using Send Log instead of the _Sent data view? – Adam Spriggs Aug 17 '17 at 13:53
2

Again, my approach should work, but it doesn't. I was able to workaround this by using the same AMPscript code, but in a Content Block (as the email is in Content Builder). My Content Block looks like this:

%%[
var @test
set @test = DateDiff(iif(RowCount(LookupOrderedRows('_Sent',1,'EventDate desc','SubscriberKey',_subscriberKey))==1,Field(Row(LookupOrderedRows('_Sent',1,'EventDate desc','SubscriberKey',_subscriberKey),1),'EventDate'),'1/1/2000'),SystemDateToLocalDate(Now()),'D')
]%%%%=v(@test)=%%

And then I set my Exclusion Script to test the result of the Content Block (obviously you can do the same using a Content Area if you are using Classic Content):

TreatAsContent(ContentBlockbyID('5381')) < 7

That works fine; the email is only sent to Subscribers who haven't been sent an email in the past week.

Go figure!

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.