Cleaning and Normalizing a CRM Export

You export your contacts from Salesforce, HubSpot, or whatever CRM your company uses. The CSV lands in your Downloads folder. You open it and immediately see the problems.

Sixty columns, most of which you don't need. One company shows up as "TechWave Solutions", "TECHWAVE SOLUTIONS", "techwave solutions", and "TechWave Solutions " (with a trailing space). Contact names are in mixed case. Half the phone number fields are blank. And there are duplicate contacts - the same person entered twice because someone created a new record instead of updating the existing one.

This is the kind of mess that makes people open a spreadsheet and start manually fixing cells. Don't do that. Build a pipeline in ExploreMyData that fixes everything in the right order and can be reapplied next time you export.

Step 1: Strip down to the columns you need

Start with the biggest win: getting rid of noise. Click the green + in the Pipeline panel and select Select Columns from the Columns group. In a typical CRM export, you might keep 10-15 columns out of 60:

• contact_id
• first_name, last_name
• email
• phone
• company_name
• job_title
• city, state
• created_date, last_activity_date

Uncheck everything else. The first pipeline card appears, and your table goes from impossibly wide to something you can actually read.

Do this first. Every subsequent operation runs faster on 11 columns than on 60, and you won't accidentally clean data in a column you're about to throw away.

Step 2: Trim whitespace from all text columns

Trailing spaces are invisible enemies. "TechWave Solutions" and "TechWave Solutions " look identical on screen but are treated as different values by every grouping and matching operation.

Click the green + in the Pipeline panel and select Text Transform from the Transform group. Choose a text column and apply "trim". This strips leading and trailing whitespace. You'll want to do this for first_name, last_name, email, company_name, city, and job_title.

Text Transform works on one column at a time, so each column gets its own pipeline card. Under the hood, ExploreMyData writes TRIM("column_name") and updates the column in place. Six columns, six cards, and each one runs instantly.

Step 3: Capitalize names consistently

Your contacts have "john smith", "JOHN SMITH", and "John Smith". They're all the same person, but your CRM doesn't enforce formatting.

Use Text Transform again. Select first_name and apply "capitalize". Do the same for last_name. Because first and last names live in separate columns here, john becomes John and NGUYEN becomes Nguyen, which is exactly what you want.

Read the small print on "capitalize" before you reach for it anywhere else. It upper-cases the first character of the cell and lower-cases everything after it. It is not per-word title case. A single-word name comes out perfect. A two-word cell does not: MARY JANE in one column becomes Mary jane, not Mary Jane. That matters in Step 4.

For email, apply "lowercase" instead. Emails should always be lowercase for matching purposes.

Step 4: Normalize company name variations

This is the hardest problem in CRM data. "TechWave Solutions", "TECHWAVE SOLUTIONS", "Techwave", "TechWave Solutions Inc.", "TechWave Solutions, Inc" are all the same company.

Start by applying Text Transform with "capitalize" on company_name. That collapses every case variation into one predictable form. Be clear about which form: TECHWAVE SOLUTIONS comes out as Techwave solutions, with a lowercase "s". There is no per-word title case in the app. Text Transform has uppercase, lowercase, trim, ltrim, rtrim, capitalize, reverse and length, and none of them do it. Neither does the SQL Query step, because the DuckDB build that ships in the browser has no INITCAP function. Ask for it and you get a catalog error.

My advice is to live with Techwave solutions. You are normalizing this column so grouping and matching work, not so it looks good on a slide, and one consistent form does that job. If a handful of names really do need their house capitalization back, fix those by hand in the Find & Replace card below with the "Entire cell" box ticked: find Techwave solutions, replace with TechWave Solutions. That is fine for ten accounts and miserable for a thousand.

Now the remaining variants. Click the green + in the Pipeline panel and select Find & Replace from the Transform group. Choose company_name, then add one pair per variation. The values you type have to match what capitalize left behind, which means lowercase tails:

• find Techwave solutions inc., replace Techwave solutions
• find Techwave solutions, inc, replace Techwave solutions
• find Techwave, replace Techwave solutions, with "Entire cell" ticked so it only hits the bare one-word rows

"Add pair" stacks all three inside a single card, so you get one pipeline step with every find/replace visible on it rather than three separate cards to scroll past.

Yes, this is manual. But it's documented. When you export again in three months and there's a new variation, you add one more pair. The old ones still work.

There is a quiet upside to capitalize flattening the tail of the string. Trailing suffixes now have exactly one spelling each, so a pair that finds inc. and replaces it with nothing sweeps up every "Inc.", "INC." and "inc." in the file at once. Same for llc and ltd. That is three pairs for hundreds of companies. Leave "Entire cell" unticked for these, since you're matching a fragment.

Step 5: Fill missing phone numbers

Some contacts have phone numbers; some don't. If multiple contacts exist at the same company, there might be a shared office number on some records but not others.

Click the green + in the Pipeline panel and select Fill Missing from the Data group. Choose phone as the column. For many cases, "forward" works well. It carries the nearest earlier non-null value down, filling a whole run of blanks rather than a single row.

For this to work well, set Sort by (for forward/backward) to company_name on the same panel so contacts at the same company are walked together. That field is part of the Fill Missing card, not a separate sort step. Then forward fill propagates a known phone number to the blank rows below it within the same company block.

One trap worth knowing: Fill Missing only touches real NULLs, because it works through COALESCE. A cell holding an empty string looks blank in the grid but sails straight past it. If nothing gets filled, that's usually why. Fix those with Find & Replace, "Entire cell" ticked, find left empty, replace with whatever you want.

If you'd rather mark unknowns explicitly, use "literal" mode and fill with "No phone on file". Either way, you've eliminated the NULLs.

Step 6: Remove duplicate contacts

Finally, deduplicate. Click the green + in the Pipeline panel and select Remove Duplicates from the Filter & Sort group, then choose email as the column to check. Since email addresses are unique per person (and you already lowercased them), this catches duplicates even when names are slightly different.

This generates SELECT DISTINCT ON ("email") *. One row survives per email address and the rest are dropped, and every other column on the surviving row comes from whichever copy was kept. Which copy that is, is arbitrary: there's no "keep the newest" setting. So if the two records disagree on something you care about, reconcile it before this step rather than after. If you had 8,000 contacts and 500 were duplicates, you're down to 7,500 rows.

The pipeline you built

Scroll through the pipeline sidebar. Fourteen cards, grouped like this:

1. Select Columns (trim to essentials)
2. Trim whitespace (six Text Transform cards, one per column)
3. Capitalize first_name and last_name
4. Lowercase emails
5. Capitalize company names
6. Find & Replace company variations (one card, several pairs)
7. Fill Missing on phone, method "forward", sorted by company_name
8. Remove duplicates by email

Note what is not in that list: a sort step. There isn't one to add. Ordering only shows up where an operation needs it, like the "Sort by" field inside Fill Missing, or the sort options on Limit Rows and Top / Bottom Rows. Clicking a grid column header reorders what you're looking at and nothing else.

Every step is visible, editable, and deletable. If you realize you also need to normalize job titles ("VP of Sales" vs "Vice President of Sales"), add another Find & Replace card and it runs on top of everything before it. Cards execute in the order they were added, so when order matters, build the pipeline in the order you want it to run.

The real value is next quarter. Export from the CRM again, load the file, and the same pipeline cleans it the same way. Five minutes instead of an afternoon.

Clean your CRM export →