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:
LookupOrderedRows
returns the sent date of last email (from the _Sent Data View)- If
RowCount
from step 1 == 1, then retrieve theEventDate
field from the the _Sent Data View - 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) - 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.
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 toLookUpOrderedRows
on data views via AMPScript. I guess that also depends on how large the sends are on each MC account.true
orfalse
, 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.subscriberId
orsubscriberKey
. I'm usingSystemDateToLocalDate
to compare the date sent (which is stored as local time in the Sent Data View) with the current local date. If I useNow
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.