1

I have a Data Extension which has email address. I want to store all distinct domains and counts of these emails in another DE For eg: I have someth@som.com, test@test.com, 123@test.com. So my o/p should be: som.com 1 test.com 2

Thanks

1 Answer 1

3

Not being an SQL guru, I can't tell you whether this is the most efficient way of fetching and counting all the unique domains, but it at least does the job:

SELECT RIGHT(Email, LEN(Email) - CHARINDEX('@', email)) as emaildomain ,
COUNT(Email) as EmailCount
FROM   [Your data extension]
WHERE  LEN(Email) > 0
GROUP BY RIGHT(Email, LEN(Email) - CHARINDEX('@', email))
ORDER BY EmailCount DESC

Which will give you this output (in a bad example, with only gmail.com addresses in the DE): enter image description here

2
  • 1
    Just an addition: When your questions aims towards the system dataviews of salesforce: they most include an own column that is named domain. atleast for open click unsub bounce and so on Apr 2, 2020 at 9:58
  • It's not the system data view. Infact I want to see the domain count of the contacts who don't receive an email. It's a custom DE. Thanks for the pointing it though :) Apr 2, 2020 at 11:46

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.