This post is intended for individuals who are unsure about how to create a subset of another Data Extension based on the drag and drop filter criteria. This post and video also touch briefly on de-duplication. For more about using a de-duplication query, check out "Deduplication by Email Address".
Note
The data shown in the video and within any screenshots is mock data used for demonstration purposes which was created through the Mockaroo service. Mockaroo is extremely helpful when testing out logic as it allows you to create realistic scenarios in your test data. While the addresses used are fictional, you should avoid sending to this data and use it instead to test out filters, queries, etc. ahead of loading primary data extensions with real data. The data referenced in the video is also intentionally modified to introduce common challenges such as multiple records who share different subscriber keys but the same email address or duplicate rows in general per email address.
Step 1 — Create the Filter Definition in Email Studio
Create a Filter Definition in Email Studio > Data Filters and select the option to base it upon an existing "Data Extension." Locate the data extension and add desired criteria. In our example, we'll specify that the Favorite color should be "Maroon."
Last, give the filter definition a name that makes sense for the criteria used. If you plan to use the same filter with modifications to the criteria over time, name it a bit more generically.
Important
DO NOT SELECT the "CREATE DATA EXTENSION" button that appears in the top right. Choosing this option will make it challenging to automate a refresh of the filter. You should instead switch to Automation Studio and select the option to Create an Activity.
Step 2 — Create a Filter Activity in Automation Studio
Access the "Activities" tab in the top left and then click the "Create Activity" button. Select the "Filter" option.
Give your Filter Activity the same name as used for the Filter Definition and then navigate to select the Filter Definition that you had created. Place the Filter Activity within the folder of your choice.
Last, choose a resulting data extension name that matches with the same name you have used for the Filter Definition and Filter Activity. The consistency will simplify things later.
Step 3 — Create a New Automation and Add Your Filter Activity
Select the "Overview" tab and then create a new Automation in Automation Studio. Choose the starting source option of "Schedule" and set the desired cadence and/or start date as desired.
Next, add a Filter option to the first step in the canvas and locate the Filter Activity that you created in Step 2. You now have an automation that can be used to refresh the filter on a schedule. If you do not need to de-duplicate the results of the filter, you are all set. If you do wish to de-duplicate, read on.
Step 4 — De-Duplicate the Filtered Data Extension
Create a new activity and select the SQL Query option. Use the query below as a basis for this query but adjust the field names on the first and 3rd lines of the query to match up with the Data Extension fields used in your filtered results data extension.
The query below is looking up the rows in the filtered results data extension and then adding a temporary field to count the number of times that an email address is used in the case of duplicates. So if Bob had 3 rows with the same email address for [email protected], there would be an additional virtual column with a value of 1, 2, and 3. There is an outer query then that looks at the results that have the extra RowNum field and limits the overall result to only include those with a RowNum of "1". So if Bob had 3 rows, the rows that were temporarily numbered Row 2 and Row 3 would be left out.
This query is numbering the rows in order by the value of the Subscriber Key (ID) but this can also be modified or replaced with NewId().
Select Id, Email, Field1, Field2 FROM (SELECT Id, Email, Field1, Field2, ROW_NUMBER() OVER (PARTITION BY email ORDER BY Id ASC) as RowNum FROM [DE_Name] /*Replace DE_NAME with the name of your filter results DE*/ ) innerqry where RowNum = 1
The final automation can be used separately to refresh a filter on the schedule of your choice or you can add a step to send an email from Automation Studio by adding an Email Send activity after the query. This automation can also be used as a basis for the audience injected into a Journey. Watch for an upcoming blog post that shows how to accomplish the journey option.
Need Help Building Automated Segments?
Light Your Fires can help you design and automate your segmentation strategy in Salesforce Marketing Cloud — from filter definitions to de-duplication queries.
Let's Talk