Can i automate the process of filters?

Question

I have a data extension in which I have 2 columns:

• Email
• CategoryType

The CategoryType column gets data like this:

Category A, Category B, Category C

So for email te[email protected], I can get this - Category A, Category B, Category C

For another email, I can get this - Category D, Category E, Category A

As you can see, I can get different categories for different customers and the data is inserted in my data extension on daily basis.

Hence, category type can easily multiply.

I have to send emails based on audience for each category. So Email 1 to CategoryType A, Email 2 to CategoryType B and so on...

This requires me to create manual data filter for each category and there should be hundreds of different category types. I also won't know if any daily import includes new category type or not.

Is there any way to automate the process of filter creation based on all categories available in the data? OR is there any way to send emails based on specific category only without creating filters?

Answer 1

You could use a) SQL to automate your split b) Use ampscript in the email, if the email is tied to a certain category, you just split the comma separated values into a rowset, check if the email category is in the rowset; if it's not abort the send using RaiseError function.

For solution b) put this ampscript in your email:

%%[
Var @mailCategory,@customerCategories,@categoryPresent,@customerCategories,@category
Set @emailCategory = "Category A"
Set @customerCategories = BuildRowsetFromString([CategoryType],',')

Set @categoryPresent = false
for @i = 1 to Rowcount(@customerCategories) do
    Set @row = Row(@customerCategories, @i)
    Set @category = Trim(Field(@row,1))

    if @category == @emailCategory Then
        Set @categoryPresent = true
    Endif
next @i

If Not @categoryPresent Then
    RaiseError('Skip customer, email category not in customers categories', true)
Endif
]%%