Extracting Email Domains to Identify Company Accounts

You have a list of 10,000 users. Each row has a name and an email address. Your boss asks: "Which companies have the most accounts?" There's no company column. There never is.

But the information is right there, hiding after the @ symbol. sarah@techwave.io works at TechWave. mike@dataflow.com works at Dataflow. jenny@gmail.com is using a personal account.

You just need to extract it. Here's how to do that in ExploreMyData without writing any code, and what's happening under the hood.

The starting data

Imagine a CSV exported from your signup database. It looks something like this:

Notice the problem right away: Alex's email is ANovak@TechWave.IO while Sarah's is sarah.chen@techwave.io. Same company, different casing. We'll need to handle that.

Step 1: Extract the domain

Click the green + in the Pipeline panel and select Extract Text from the Transform group. Choose the email column, set the method to "after delimiter", and type @ as the delimiter. Name the new column domain.

Under the hood, ExploreMyData generates:

CASE WHEN POSITION('@' IN email) > 0 THEN SUBSTRING(email, POSITION('@' IN email) + LENGTH('@')) ELSE '' END AS domain

Wordier than the SPLIT_PART you might have written by hand, and deliberately so. "after delimiter" finds the position of the @ and takes everything past it, which means a second @ in the address stays in the result instead of being silently dropped. The ELSE '' branch is what happens when there is no @ at all. More on that in a moment. ("before delimiter", the sibling method, really does use SPLIT_PART(email, '@', 1), which is how you'd pull the local part.)

Your table now has a new domain column with values like techwave.io, Dataflow.com, and TechWave.IO.

Step 2: Normalize the casing

The extracted domains have inconsistent casing. TechWave.IO and techwave.io need to be the same value, otherwise they'll show up as separate groups.

Select Text Transform from the Transform group. Choose the domain column and pick "lowercase".

The SQL:

LOWER(domain)

Now every domain is lowercase. techwave.io, dataflow.com, gmail.com. Clean and consistent.

Three things that will bite you

Rows with no @ come out as an empty string, not NULL. Blank cells, a stray "n/a", a row where someone typed a phone number into the email field: none of them contain an @, so the CASE falls to ELSE '' and you get ''. That distinction matters when you go looking for them. The Nulls stat won't count them because they aren't null. Instead add a Filter on domain with the operator is Empty, which is defined as NULL or empty string and catches both. Filter action "Remove matching rows" drops them; keep them if you want to see how bad the email field is.

Subdomains split your counts. mail.techwave.io and techwave.io are two different strings, so one company shows up as two rows in the breakdown. If that's happening, add a Regex Capture step on domain with the pattern ([^.]+\.[^.]+)$ and capture group 1, which keeps the last two labels. That gives you techwave.io from both. It also gives you co.uk from acme.co.uk, which is wrong, so use it only if your data is mostly single-label TLDs, and check the result.

Plus-addressing doesn't touch the domain, but it will skew user counts. sarah+news@techwave.io and sarah@techwave.io both extract to techwave.io, so the domain analysis is fine. Counting distinct users by email is not: those are one person filed as two. If you need a deduplicated user key, extract the local part with "before delimiter", strip everything from the + onward with another "before delimiter" step, then recombine.

Step 3: See the distribution

Now for the payoff. Click on the domain column header to open Column Explorer. Since domain is a text column, ExploreMyData shows a categorical breakdown: the most frequent values, their counts, and their percentage of the total.

You can immediately see that techwave.io has 847 users, dataflow.com has 312, and gmail.com has 2,341. That last number tells you something important: most of your users are on personal accounts, not company ones.

What you can do with this

Once you have a clean domain column, the analysis possibilities open up:

• Filter out personal email domains (gmail.com, yahoo.com, hotmail.com, outlook.com) to focus only on business accounts.
• Group by domain to find your biggest company customers by user count.
• Combine with signup date to see when companies started adopting your product - did TechWave sign up all at once, or trickle in over months?
• Export the enriched data with the new domain column included for use in your CRM or reporting tool.

Two steps for the whole job

The core operation is two pipeline steps: extract and lowercase. No terminal, no spreadsheet formula bar, and no regex unless subdomains forced your hand. The SQL is there in the pipeline if you want to read it, copy it, or lift it into an ETL job, but you didn't have to write it.

Try it with your own data →