Tip: Domain Counts within a Data Extension (ET/SFMC)

Here's a quick tip for those who work within the ExactTarget (Salesforce Marketing Cloud) platform.

If you've got a large Data Extension and you want to know the counts by domain, do this:

(1) Create a new Data Extension called "DomainCounts" with two fields:
a. Domain (text, 254 characters, nullable)
b. DomainCount (number, nullable)

(2) Create the following query:

SELECT TOP 100000000 SUBSTRING(e.EmailAddress, (CHARINDEX('@',e.EmailAddress)+1),LEN(e.EmailAddress) - CHARINDEX ('@', e.EmailAddress)) AS Domain, COUNT(*) as DomainCount
FROM [20150702_WidgetSale] e
GROUP BY SUBSTRING(e.EmailAddress, (CHARINDEX('@',e.EmailAddress)+1),LEN(e.EmailAddress) - CHARINDEX ('@', e.EmailAddress))
ORDER by DomainCount DESC


Replace "20150702_WidgetSale" with the name of the Data Extension you want to analyze.  Point the results at "DomainCounts" as Overwrite.

(This assumes that your Data Extension has a field called "EmailAddress".)

Save and run your query.

You will end up with a data extension containing the top 200 domains.  If you export the data extension, you'll have all of the domains.