1

My company is very soon transitioning to the use of a proprietary subscriber key - we currently use the email address as a subscriber key. I have to do some preliminary investigation into how many people will be affected by this and start reconciling any duplicate accounts.

I would like to see a list of any email addresses that appear more than once, and what their subscriber key is. For example, I am in there several times as a test account, e.g. the following:

SubscriberKey     EmailAddress
xyz                x@yz.com
abc                x@yz.com
zyx                x@yz.com

It's easy to find these - I just used

SELECT SubscriberKey, EmailAddress
FROM _Subscribers
WHERE EmailAddress = 'x@yz.com'

And it shows me all the different Subscribers with this email address. However, I want a list of all Subscriber Keys with multiple email addresses. I tried this:

SELECT [SubscriberKey], COUNT([EmailAddress]) AS EmailAddress
FROM _Subscribers
GROUP BY [EmailAddress], [SubscriberKey], 
HAVING COUNT([EmailAddress]) > 1

And a couple of variations, but for some reason it returns nothing. Can anyone assist?

4

I think you need to try something like that:

SELECT SubscriberKey, count(EmailAddress) FROM _Subscribers GROUP BY SubscriberKey HAVING count(EmailAddress) > 1

Just for example, I tried with the following Query and it's worked:

SELECT Name, count(Email) FROM Contact Group By Name HAVING count(Email) > 1

Also, if anything of this doesn't work, you can try to: 1) Make a report with them. 2) Export with DataLoader and put some filters in the newly exported file.

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.