Use Multiple Fields as the Unique Field in Deduplication
This page explains how to deduplicate the records of a Data Extension including multiple fields as the unique field.
This scenario uses the feature Apply any function (SQL) Custom Value, which is only available on Unaric Segment Advanced.
Overview
Assume you have a Data Extension called Orders, with the following records:
Person | Product | Quote | Version | Date |
|---|---|---|---|---|
A | Car | 1 | 1 | 5/1/2021 |
A | Car | 1 | 2 | 5/2/2021 |
A | Car | 2 | 1 | 5/3/2021 |
A | Car | 2 | 2 | 5/4/2021 |
A | Fire | 1 | 1 | 5/5/2021 |
You would like to keep only unique combinations of person-product pairs. Ideally, the one with the highest quote, the newest version, and the latest Date. In this case, you would like to keep the last two rows.
Setup
Input
Drag and drop your Orders Data Extension to your Selected Data Extensions.
Create your Target Data Extension
You need to create a field to use in deduplication. Since you need unique combinations of Person and Product you will create a custom value that combines these two fields.
- In the Output step, click the Create Data Extension button.
- Provide the name Deduplicated DE on multiple fields and click Save.
- Click Add new value inside the Custom Values area.
- Set the name to Id.
- Select Apply formula to a field.
- Select Apply any function.
- Under the Insert field section, select Orders Data Extension and Person field.
- Click the Insert field button.
- In the formula section, add a white space, a plus icon (+), and one more white space.
- Under the Insert field section, select Orders Data Extension and Product field.
- Click on the Insert field button. Your formula should now look as follows:
- Click the Save button.
- Add the Id custom value to your Target Data Extension's fields.
- Click the Add All Fields button of your Orders Data Extension, under the Available Fields section.
Set deduplication rules
After creating your Target Data Extension, you can set the deduplication rules, and deduplicate using the custom value you created as the unique field. Then, you can proceed with defining the priority criteria, as you would normally do.
- Click Save Data Extension and click the Create button on the pop-up.
- Click the gear icon on the right and select Prio Deduplication.
- Select Id as the unique field and click Next.
- Select Quote from the dropdown and sort all values from highest to lowest.
- Click Add Sorting Option. Select Version from the dropdown and sort all values from highest to lowest.
- Click Add Sorting Option.
- Select Date from the dropdown and sort all values from highest to lowest. Your deduplication logic should look similar to the image below:
- Click Confirm.
Preview
Click the Run Preview button. You should see the following results:
When you want to remove duplicates using a combination of fields, you can use the Custom Values feature. With Custom Values, you can combine the fields into a single value, to use later on in Prio Deduplication.