Remove duplicates in Journey Builder

Question

I was wondering if there is a way to remove duplicates in Journey Builder?

I'm looking at creating a welcome campaign via Journey Builder. Currently I have within my welcome email AMPscript which groups families together (based on family id) and displays their details, like the below within the email. The data I get supplied, each record has an email address. Same email for the same family.

Family ID    |First Name|Relationship|emailaddress   |
1111         |Steve     |Primary     |[email protected]|
1111         |Mary      |Spouse      |[email protected]|
1111         |Dave      |Child       |[email protected]|
1111         |Wendy     |Child       |[email protected]|

At the moment when I send it as a standalone campaign via the email studio, it just takes the top contact (primary) and send just the one email. When I use Journey Builder each contact gets the email.

i was hoping there is a function of line of code which can be used to dedupe the emails before being sent in Journey Builder. Any ideas would be greatly appreciated.

Thanks!

Answer 1

AMPscript is not a recommended tool to use when it comes to the segmentation of the audience. It is better to prepare your contacts in the Data Extension in advance and once an email is sent to add an additional layer of simple calculations and of course personalization by AMPscript.

You can use SQL Query in the Automation Studio to prepare the Data Extension that is going to be used later on the Journey as an Entry Source -

1. This approach requires Primary Key:

/* Selects subscribers with their first record of email if they have many */
SELECT
SubscriberKey,
[Family ID],
[First Name],
Relationship,
emailaddress
FROM [Test_DE] a 
WHERE a.SubscriberKey IN 
    (SELECT SubscriberKey= 

        /* Chooses first subscriber who has duplicates of email */
        (SELECT TOP 1 b.SubscriberKey
        FROM [Test_DE] b 
        WHERE b.emailaddress = c.emailaddress)

    /* SELECTs only unique emails */
    FROM (SELECT DISTINCT emailaddress FROM [Test_DE]) c) 

2. There is also a possibility to do the same with the PARTITION BY function and no constraint by Primary Key -

SELECT
[Family ID],
[First Name],
Relationship,
emailaddress
FROM
    (SELECT
    [Family ID],
    [First Name],
    Relationship,
    emailaddress,
    ROW_NUMBER() OVER (PARTITION BY emailaddress ORDER BY emailaddress ASC) AS RowNumber
    FROM Test_DE) w
WHERE RowNumber < 2 AND emailaddress IS NOT NULL

Both SQLs would result with:

Family ID	First Name	Relationship	emailaddress
1111	Steve	Primary	[email protected]