Clean a messy customer list in 20 minutes

What you need

• An export of your customer list, as a copy
• A spreadsheet with its built-in AI and import-and-clean steps
• Any AI chat assistant, used only with sample rows or made-up rows of the same shape
• Your online store's customer import

1. Work on a copy and keep personal data in the spreadsheet

   Export the list and save a dated copy. Show an AI tool only the column names and a few made-up rows of the same shape, unless your agreement with that tool covers personal data. Whether your use is allowed under data protection rules: check with a professional.

2. Let AI name the problems

   Ask for a list of the kinds of problems in the columns: mixed date formats, names in capitals, phone numbers with and without country code, empty emails, extra spaces. You get a list of problems, not a rewritten file.

   Weak

   Clean my customer list.

   Better

   Here are my column names and five made-up rows in the same shape as my real data. List every kind of problem you see per column and the fix for each, as a formula or a clean-up step.

3. Save the fixes as a recipe

   Build the fixes as import-and-clean steps in your spreadsheet: trim spaces, fix capitals, split full names, one date format. Next month you load the new export and the same steps run again.

   Example

   Step 1 trim spaces in all text columns. Step 2 proper case for first and last name. Step 3 phone: remove spaces, add country code where missing. Step 4 email: lower case.

4. Find near-duplicates and decide by hand

   Add a match key, for example the email in lower case or the phone without spaces, and flag rows that share it. Look at each pair yourself before you merge. Two people can share a surname and a town.

   Example

   =LOWER(TRIM(C2)) in a key column, then =COUNTIF(K:K,K2)>1 to flag rows that share the key.

5. Import back with a test first

   Import five rows into your store first, check they landed in the right fields, then import the rest. Keep the dated copy until you are sure nothing broke, and respect every customer's email consent as it was.

Copy it into any AI chat assistant and change only the first line.

Change only this line: DESTINATION = [my online store's customer import]

You help me clean my customer list before it goes into my DESTINATION, without seeing the real data.

I paste my column names and five made-up rows in the same shape as my data.

Do four things:
1. List every kind of problem per column (formats, spaces, capitals, empty fields, mixed country codes).
2. For each problem, give the fix as a spreadsheet formula or a clean-up step I can save and rerun.
3. Suggest a match key to find near-duplicates and say which pairs I must check by hand.
4. Write a short test plan for importing five rows before the rest.

Do not ask me for real customer data.

Start with the list of problems.

Paste your material below this line:

Copy the instruction

A clean customer list, a saved recipe that cleans next month's export in minutes, near-duplicates decided by a person and a tested import.

• Pasting the full customer list into a tool that has no agreement for personal data.
• Merging near-duplicates automatically without looking at each pair.
• Cleaning by hand each month instead of saving the steps once.