exclusion script for a matrix based on criteria type

Question

I'm trying to write an exclusion script based on multiple criteria. Here's my general setup:

In my Sending-DE, I have a column called "criteria-type" - in my Exclusion-DE, I have 5 criteria columns (news, invite, survey, article, announcement).

Using the example above, I want the exclusion script to find column "invite" and exclude any contact that has "TRUE" in the column. (False or blank will get the email). In this case, subscriber 789 would not get the email, but everyone else would.

I have this exclusion script:

ROWCOUNT(LOOKUPROWS("Exclusion-DE", "subKey", _subscriberkey, [criteria-type], 'True'))>0

But it errors whenever I validate the journey:

Exclusion filter validation failed: Exclusion filter is invalid. Error 1 Invalid Function Call The specified attribute or custom object field name was not found for this client. Function Call: LOOKUPROWS("Exclusion-DE", "subKey", _subscriberkey, [criteria-type], 'True') Attribute or Field Name: criteria-type Invalid Content >>> ROWCOUNT(LOOKUPROWS("Exclusion-DE", "subKey", _subscriberkey, [criteria-type], 'True'))

Any ideas for this one? Any help is appreciated in advance!

Answer 1

You can't specify a field name dynamically like that. I would normalize your Exclusion DE schema, that way you can do a simple lookup for your exclusion.

subKey	criteriaType	exclude
123	invite	True
456	invite	False
456	announcement	True

ROWCOUNT(LOOKUPROWS("Exclusion-DE", "subKey", AttributeValue("_subscriberkey"), "criteriaType", "invite", "exclude", "true")) > 0

I like to temporarily test exclusion scripts in the body of the email before adding it to the send definition:

%%[

set @debug = 1

if @debug == 1 then

  if ROWCOUNT(LOOKUPROWS("Exclusion-DE", "subKey", AttributeValue("_subscriberkey"), "criteriaType", "invite", "exclude", "true")) > 0 then
    output(concat("<br>exclude"))
  else
    output(concat("<br>send"))
  endif

endif

]%%

When you're satisfied it's working, just set @debug to 0.

Answer 2

I did a bit more sleuthing on this - the solution you proposed works, but I also went a little deeper on the matrix I had originally had issues with and found I could make it work by inserting ampScript right into the body of the email - I could also include a domain exclusion and it worked in my testing:

%%[

/*Set domains to exclude here, separate with a comma without whitespace*/
SET @domainExclusions = "gmail"

SET @isExcluded = false

IF NOT EMPTY(AttributeValue("criteriaType")) THEN
    IF ROWCOUNT(
        LOOKUPROWS(
            "Exclusion-DE",
            "subKey",
            _subscriberkey,
            AttributeValue("criteriaType"),
            'true'
        )
    ) > 0 THEN
        SET @isExcluded = true
    ELSE
        SET @domainList = BuildRowsetFromString(@domainExclusions, ",")
        SET @domainCount = Rowcount(@domainList)

        FOR @i = 1 TO @domainCount DO
            SET @domain = Field(Row(@domainList, @i), 1)

            IF IndexOf(emailaddr, @domain) > 0 THEN
                SET @isExcluded = true
                BREAK
            ENDIF
        NEXT @i
    ENDIF
ELSE
    SET @domainList = BuildRowsetFromString(@domainExclusions, ",")
    SET @domainCount = Rowcount(@domainList)

    FOR @i = 1 TO @domainCount DO
        SET @domain = Field(Row(@domainList, @i), 1)

        IF IndexOf(emailaddr, @domain) > 0 THEN
            SET @isExcluded = true
            BREAK
        ENDIF
    NEXT @i    
ENDIF

IF @isExcluded THEN
    RaiseError("excluded.", true)
ENDIF
]%%

In this case, it looks to see if criteriaType is empty - if NOT, it excludes any user that say "true" in the specified type and then excludes any domain listed (in this case, gmail). If criteriaType IS empty, it will only exclude domains listed. It then tells me during preview if anyone is excluded with the raiseError function.

(I'd love to make the raiseError function also tell me if someone is unsubscribed or held, as well, in case anyone knows how to do that, hehe)

What do you think? Is there an inherent issue with doing it this way?