Cleaning an Email Marketing List Before a Campaign

Marketing just merged three different email lists into one spreadsheet. There are 5,000 rows. Some came from a webinar sign-up form, some from a trade show scanner, and some from a purchased list that looked fine in the preview. Before anyone hits "send," you need to clean it. Duplicates will inflate your costs and annoy recipients. Bad formatting means bounces. Test addresses and no-reply aliases will tank your deliverability score. And ideally, you want to segment by company versus personal email so the sales team can prioritize.

Here's a six-step pipeline in ExploreMyData that takes a messy email list and outputs a clean, segmented file ready for the campaign.

Step 1: Normalize email formatting

Click the green + in the Pipeline panel and select Text Transform from the Transform group. Choose the email column and apply "trim" to strip leading and trailing whitespace. Then add the operation again with "lowercase."

This matters more than you'd think. The trade show scanner captured " John.Smith@Acme.com " (with spaces and mixed case). The webinar form captured "john.smith@acme.com". Without normalization, the deduplication step won't catch these as the same address. Email addresses are case-insensitive by spec, but your CSV doesn't know that.

Two pipeline steps for one column, but they're cheap and they prevent false duplicates downstream. Text Transform updates the column in place, so the first card runs TRIM("email") and the second runs LOWER("email") over the first card's output. Each step is its own view, which is why you see two cards rather than one combined expression.

Step 2: Remove empty and NULL emails

Click the green + in the Pipeline panel and select Filter from the Filter & Sort group, then set the condition to "email" IS NOT NULL AND "email" != ''. You need both halves: a blank cell in a CSV usually arrives as an empty string, not a NULL, so an IS NOT NULL check on its own lets the blanks straight through.

Merged lists almost always have blank rows. Maybe someone filled in a name at the trade show but the scanner didn't capture the email. Maybe the webinar form allowed empty submissions. These rows are useless for an email campaign. Get rid of them early so they don't skew your count.

Step 3: Filter out test and system addresses

Add another Filter step. This time, exclude the addresses that are obviously not real recipients:

"email" NOT LIKE 'test@%' AND "email" NOT LIKE 'demo@%' AND "email" NOT LIKE 'noreply@%' AND "email" NOT LIKE 'no-reply@%' AND "email" NOT LIKE '%@example.com' AND "email" NOT LIKE '%@test.com'

This catches the common patterns. Test accounts that someone used during form setup. System addresses that got pulled into the list by mistake. Example.com addresses from the RFC 2606 reserved domain. You'd be surprised how many of these end up in a merged list.

If your company uses specific test patterns (like internal QA aliases), add those to the filter too. Better to be aggressive here. Sending to a noreply@ address doesn't just waste money - it's a hard bounce that hurts your sender reputation.

Step 4: Deduplicate

Click the green + in the Pipeline panel and select Remove Duplicates from the Filter & Sort group. Choose email as the column to check.

This runs SELECT DISTINCT ON ("email") *. One row survives per address and the rest are dropped. Because you already normalized to lowercase and trimmed whitespace in step 1, "John.Smith@Acme.com" and "john.smith@acme.com " are now the same value and get properly deduplicated.

Which copy survives is arbitrary, and the rest of that row's columns come along with it. In our sample that means the source value on John's surviving row could be "trade show" or "webinar" and you don't get to pick. If a column like that matters to you, resolve it before this step rather than after.

Check the row count in the pipeline sidebar. If you started with 5,000 and you're down to 3,800, that means 1,200 were empty, test addresses, or duplicates. That's 24% of the list that would have been wasted sends.

Step 5: Extract the domain

Click the green + in the Pipeline panel and select Extract Text from the Transform group. Choose the email column, open the Extraction method dropdown and pick after_delimiter. Set the delimiter to @, then use "Apply results into" to send the output to a new column called domain.

That method emits a small CASE expression rather than a one-liner:

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

In plain terms: everything after the first "@" in the cell, and an empty string if there is no "@" at all. So john.smith@acme.com gives you acme.com. Two things follow from "first". A malformed address like a@b@acme.com yields b@acme.com, not acme.com, and there is no "occurrence" setting to change that. And a row with no "@" gets an empty string, not NULL, which matters if you later reach for Fill Missing and wonder why it does nothing.

Now you have a domain column. Useful on its own: group by it and you can see how many contacts you have at each company. But the real payoff is the next step.

Step 6: Segment by company vs. personal

Click the green + in the Pipeline panel and select Add Column from the Columns group. Set the new column name to email_type and put this in the Expression box:

CASE WHEN "domain" IN ('gmail.com', 'yahoo.com', 'hotmail.com', 'outlook.com', 'aol.com', 'icloud.com', 'protonmail.com', 'mail.com', 'zoho.com', 'yandex.com') THEN 'personal' ELSE 'company' END

This is a simple heuristic. If the domain is a major free email provider, it's a personal address. Everything else gets tagged as "company." It's not perfect - someone might use a custom domain for personal email - but it's good enough for segmentation. The sales team can filter to "company" emails and prioritize those for outreach. The marketing team can use "personal" emails for a different messaging track.

You can extend the list with regional providers (gmx.de, mail.ru, qq.com) if your list is international.

The full pipeline

1. Text Transform (trim + lowercase the email column)
2. Filter (remove NULL and empty emails)
3. Filter (remove test@, demo@, noreply@, and @example.com patterns)
4. Remove Duplicates (DISTINCT ON email)
5. Extract Text (after_delimiter on "@" into domain)
6. Add Column (email_type: company vs. personal)

From 5,000 messy rows to a clean, deduplicated, segmented list. Export the result as CSV and load it into your email platform. You've reduced bounce risk, avoided duplicate sends, and given the team a useful segmentation they didn't have before.

Keep the pipeline around. The next conference batch or form export gets the same treatment: load the file, watch the seven cards replay, export. Every operation is visible in the sidebar, so when someone invents a new internal QA alias you widen the test-address filter, and when the list goes international you add the regional providers to the domain list.

Clean your email list now →