0

Hello all I want to write an exclusion script where I omit people who were sent emails yesterday. Below is my code but it is not working and I think its because my entry source DE needs an eventdate field - BUT I just want to check if the record in the send log has an email sent yesterday or today

ROWCOUNT(LOOKUPROWS("sendlog_exclusion","EmailAddr", EMAILADDR,"eventdate", DateAdd(eventdate, -1, "d"))) 

I feel like I am almost there but there is a piece of a puzzle im missing.

Basically how can I evaluate this to true when a user has received an email yesterday?

---New code targeting people who received more than 2 emails before today--

   %%[

  var @ex_exclude
  var @ex_rows
  var @ex_rowcount
  
  set @ex_exclude = 0

  /* retrieve the most recent send for this subscriber */
  set @ex_rows = LookupOrderedRows("exclude_sendlog", 0, "eventdate Desc", "emailaddr", AttributeValue("emailaddr"))
  set @ex_rowcount = rowcount(@ex_rows)
 set @date1 = AttributeValue('eventdate')

   if @ex_rowcount >= 2 then
    
        var @ex_row
     var @ex_eventDate
     var @ex_yesterday
     
     set @ex_row = row(@ex_rows, 1)

     set @ex_eventDate = field(@ex_row,"eventDate")
     set @ex_yesterday = dateadd(now(),'-1','D')

     if @ex_eventDate > @ex_yesterday then 
       set @ex_exclude = 1
     endif

  endif

]%%


excluded: %%=v(@ex_exclude)=%% <br>entries in DE:
%%=v(@ex_rowcount)=%%<br>
ed: %%=v(@ex_eventdate)=%% <br>
yd: %%=v(@ex_yesterday)=%% <br>
edv2: %%=v(@date1)=%%
1
  • Lookups use AND conditions for criteria. You're assuming "event date is after yesterday", which is not an AND condition. You'll need to do something a little more sophisticated. Commented Nov 1, 2022 at 17:49

1 Answer 1

1

You'll need to create a code-snippet Content Block for this. One with an external key like "Sendlog_exclusion":

In this Content Block include your criteria:

%%[

  var @ex_exclude
  var @ex_rows
  var @ex_rowcount

  set @ex_exclude = 0

  /* retrieve the most recent send for this subscriber */
  set @ex_rows = LookupOrderedRows("Sendlog_exclusion", 1, "eventdate desc", "emailaddr", AttributeValue("emailaddr"))
  set @ex_rowcount = rowcount(@ex_rows)

  if @ex_rowcount > 0 then

     var @ex_row
     var @ex_eventDate
     var @ex_yesterday
     
     set @ex_row = row(@ex_rows, 1)

     set @ex_eventDate = field(@ex_row,"eventDate")
     set @ex_yesterday = dateadd(now(),'-1','D')

     if @ex_eventDate > @ex_yesterday then 
       set @ex_exclude = 1
     endif

  endif

]%%%%=v(@ex_exclude)=%%

Then in the your Exclusion Script box:

TreatAsContent(ContentBlockByKey("Tr_Exclusion")) == 1

Also, it's easier to test Exclusion Scripts in the body of your email. Just copy this to your email code and cycle through some qualified and disqualified subscribers in your audience to see if the flag is set properly. If everything looks good, add the exclusion script one-liner to the send definition.

4
  • Hi Adam, first off: thanks so much for your help seems to work! I wanted to check if a person has received more than 2 emails BEFORE today. You can see my edit in my original comment. I modified your code to read as : if ex_eventDate > ex_yesterday AND ex_rowcount >= 2 then I also changed the lookupordered rows to 0 to return up to 2000 I believe rows so basically this will check if the person has more than 2 records in the send log de - AND I tested it seems to work but is there a better way? Thanks again!
    – GS65
    Commented Nov 2, 2022 at 11:53
  • If you need to count emails, you'll need to loop through the rowset and count the results. The row(@ex_rows,1) is only considering the first row. There's a loop example here: ampscript.guide/lookuporderedrows Commented Nov 2, 2022 at 12:20
  • I'd suggest testing your exclusion script with output in your email code instead of in the send definition -- something like this <br>Excluded: %%=TreatAsContent(ContentBlockByKey("Trademax_Exclusion"))=%%. If you've identified separate emails that are sent and excluded, you can verify it in Send Preview. Commented Nov 15, 2022 at 15:36
  • Thanks Adam I actually did do that in an email and it output 1 so :)! Thanks!
    – GS65
    Commented Nov 16, 2022 at 15:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .