I'm trying to create a data extension that extracts subscriber key and clicks based on specific criteria. Below is the script:
select c.[JobID] , c.[SubscriberKey] , c.[URL] , j.[EmailName] , c.[LinkName] from [_Click] c INNER JOIN [_Job] j ON j.[JobID] = c.[JobID] WHERE j.[EmailName] like '%Onboarding email%' OR j.[EmailName] IN ('Onboarding3', 'Onboarding4') AND c.[URL] like '%review%' OR c.[URL] like '%action%' OR c.[LinkName] like '%B&I%'
This gets me the data that I need which is great. But, I want to add an additional column to my data extension named: Category
And I want it to give a category based on the above filters (two different columns), i.e., if URL has 'review', then the category is Review. If URL has 'action', then the category is Action. And if the Link name has 'B&I', then the category is 'B&I'.
Can someone give me tips on how to go about this? Thank you