De-Duplication by Email Address

When joining data extensions (and objects) through querying, the potential for generating duplicate rows increases — but if a query can get you into a mess, it can surely get you out.

The Scenario

Let's say that you wanted to send out an email at the end of each week to thank any customer who ordered at least one Watermelon in the prior week. To determine who ordered the watermelons, you might join Contacts, Orders, and OrderItems data extensions.

SELECT cont.ContactId, cont.Email
FROM Contacts cont
INNER JOIN Orders ordr ON cont.ContactId=ordr.ContactId
INNER JOIN OrderItems itm ON ordr.orderid=itm.orderid
WHERE itm.ItemName = 'watermelon'

The Problem

The problem is that if one customer places an order for watermelons 3 times during the week, they would have 3 rows in the resulting data extension and thus could potentially receive 3 messages. You would like to de-dup by email address but are sending through a journey. You could potentially adjust journey settings to accommodate but — there is actually a much cleaner solution.

The Solution

With credit to Adam Spriggs, you can accomplish this through a partition and a masterful query within a query. The inner query joins and queries for all potential combinations which meet the criteria (itemname = 'watermelon') and then numbers each row returned sequentially which has the same email address. You have some control over the ordering of the rows by selecting one or more fields in the ORDER BY statement, or you may alternatively randomly select the "best" #1 using NewId(). I prefer using the most recently created SubscriberKey (ContactId) when de-duplicating by Email.

As a final step, you add the outer query that limits the result to only those rows with a RowNum of "1" and includes the same fields referenced in the inner query.

SELECT ContactId, Email

FROM

(SELECT cont.ContactId, cont.Email,
Row_Number() over (Partition By Email ORDER by ContactId DESC) AS ROWNUM

FROM Contacts cont
INNER JOIN Orders ordr ON cont.ContactId=ordr.ContactId
INNER JOIN OrderItems itm ON ordr.orderid=itm.orderid

WHERE itm.ItemName ='watermelon') innerqry

WHERE RowNum = 1

The final result is one unique row per unique email address and one "thank you for buying our watermelons" message per person.

Getting Creative with the Partition

Note that you can also get creative with the query by adding additional fields to the Partition statement. For example, if you had 2 contacts with the same email address but associated with two different companies and thus both deserving of that email — you might want to adjust the partition to account for it.

Row_Number() over (Partition By Email, Company ORDER BY ContactId DESC) AS ROWNUM

Need to create an email that specifies the date that each watermelon was ordered but still sends to a unique individual? Watch for my next post!