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 [email protected], [email protected], [email protected]. So my o/p should be: som.com 1 test.com 2

Thanks

Improve this question

1 Answer 1

Reset to default
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

Improve this answer
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
    – Johannes Schapdick
    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 :)
    – Swati Mishra
    Apr 2, 2020 at 11:46

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .