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 firstname.lastname@example.org abc email@example.com zyx firstname.lastname@example.org
It's easy to find these - I just used
SELECT SubscriberKey, EmailAddress FROM _Subscribers WHERE EmailAddress = 'email@example.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?