0

I am trying write a query that will result in duplicate SFMC contacts in a SFMC list.

More specifically, I have 3 brands and each has a shared publication list then there is the All subscriber List. Let's say I have a subscriber with the email address email@mail.com who is in the lists as follows.(note that each subscriberKey is unique)

EmailAddress ListName SubscriberKey email@email.com BrandA 0031500001dpVxEAAU email@email.com BrandA 0031500001dpVyjAAE email@email.com BrandA 0031500001dpVy7AAE email@email.com BrandB 0031500001dpVxUAAU email@email.com BrandC 0031500001dpVxNAAU email@email.com All Subscribers 0031500001dpVxNAAU

I need a query that can identify that this emailAddress exists on List BrandA 3 times and will give me all but 1 of those subscriber keys.

(The goal is that I will delete the results of the query from the contacts keeping only one contact per brand list or all subscriber list if they are not in any brand list)

Any suggestions?

This is an example of what does NOT give me what I need:

SELECT 
  EmailAddress
  , SubscriberKey
  , ListName
FROM _ListSubscribers 
WHERE EmailAddress IN (
    SELECT EmailAddress 
    FROM _ListSubscribers 
    GROUP BY EmailAddress 
    HAVING COUNT(*) > 1
) 
3

Row_Number() is one of the more useful functions for everything relating to deduping.

This will number your duplicates and from the result of the numbering, select all those that do not have the number 1, i.e. are duplicates.

Code is untested, but extrapolated from a working example. I hope it gives you a starting point.

SELECT a.EmailAddress,a.SubscriberKey
FROM (
  SELECT EmailAddress,SubscriberKey, ROW_NUMBER() OVER(Partition by EmailAddress, ListName ORDER BY EmailAddress DESC) AS Row_Number   
  FROM _ListSubscribers) a
WHERE Row_Number != 1
| improve this answer | |
  • Just a note, this would need to be Partitioned by EmailAddress and ListName (Partition by EmailAddress, ListName) in order to get the results he wants. Your example would return empty as SubscriberKey is a unique field and there can be no duplicates. – Gortonington Jun 18 at 12:55
  • 1
    true, fixed this above. Thanks! – Jonas Lamberty Jun 18 at 12:57
  • thank you, I"ll test this out now. great suggestions! – JulieHurtz Jun 18 at 13:03
  • I just had to name the subquery so the where statement would not fail syntax and its running so let's see what it gives me! – JulieHurtz Jun 18 at 13:10
  • also true. I edited in an alias "a" into the code. let us know – Jonas Lamberty Jun 18 at 13:30

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.