Before & after: a customer contact list cleanup

What was wrong with the original list

This is the kind of contact list you get when a handful of different people (the owner, a part-time helper, maybe an old subcontractor) have all typed entries into a spreadsheet over a few years, with no rules about how to format anything.

• Duplicate customers. Twenty rows were flat-out duplicates of someone already in the list, often under slightly different names, phone formats, or email typos.
• Inconsistent name formatting. Some names in ALL CAPS, some lowercase, some with stray leading or trailing spaces.
• Phone numbers in six different formats. Dashes, dots, parentheses, no punctuation, and a few with a stray "1-" in front.
• Dates in five different formats in the same column, including some that could be quietly misread backwards (day/month versus month/day).
• Missing information. About 10% of rows were missing an email, a similar share missing a phone number, and a handful missing a name entirely.
• Bad emails. A few entries missing the @ symbol, missing the ".com," or with a typo that would bounce.
• Nonsense values, like a negative number in the "services per year" field.
• Inconsistent category values, such as "referral," "Referral," and "REFERRAL" all meaning the same thing.

None of this is unusual. It's what almost any small business list looks like after a few years of real-world use with no data entry standards.

A look at the data (5 rows out of 200)

Same customers, before and after. Full files run 200 rows before, 180 after.

Before

After

How it was fixed

1. Standardize formatting first. Every name, phone number, date, and category value rewritten into one consistent format.
2. Validate the fields that matter most. Every email checked against a real email pattern. Anything that couldn't possibly be a working address was flagged rather than guessed at or silently deleted.
3. Find and remove duplicates. Records matched by email first, then by name plus phone number, so near-duplicates from typos or reformatting were still caught.
4. Flag, don't fabricate. Missing or clearly wrong information (a negative quantity, an invalid email, a date that couldn't be parsed) got marked in a "needs review" column instead of a made-up value.
5. Sanity-check the result. The final count, duplicate count, and flagged-row count were reviewed by hand before calling it done.

The result is a list ready to load into a CRM, mail merge tool, or spreadsheet without the import choking on bad phone numbers or double-counting the same customer twice.

The honest numbers