Using Exclusion Script with _Sent Data View

Question

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.

Answer 1

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!