0

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     |123@hotmail.com|
1111         |Mary      |Spouse      |123@hotmail.com|
1111         |Dave      |Child       |123@hotmail.com|
1111         |Wendy     |Child       |123@hotmail.com|

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!

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) 
  1. 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 123@hotmail.com
3
  • Thanks, i'll give this a go!
    – rom29
    Jul 22 at 21:39
  • If it helped you, do not forget to choose this answer as accepted one :)
    – Niko
    Jul 26 at 17:44
  • it did, thank you!
    – rom29
    Aug 6 at 15:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.