0

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

2

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

| improve this answer | |
  • 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 – Johannes Schapdick Apr 2 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 :) – Swati Mishra Apr 2 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.