Home / Sales and leads / KA-003

Clean a CRM or contact spreadsheet export and keep every record

Find duplicate contacts, standardize names, phones and addresses, and flag missing fields in a CRM export, with every merge or delete reviewed first.

Who it suits

Small business owner, office manager, sales coordinator, freelance operations assistant

Time saved

About 3 to 6 hours for a list of 2,000 to 5,000 contacts (estimate)

Level

Medium

Updated

Oct 13, 2026

Why this task

Contact lists collect duplicates, mixed phone formats, names in capitals and half filled addresses. Sales follow up suffers when the same person appears three times with different details. An agent can propose a clean version, find likely duplicates and flag gaps. Nothing is merged or deleted until a person reviews each proposed change.

You help clean a contact list exported from [CRM NAME] for [BUSINESS NAME].

Format rules:
- Names: [RULE, e.g. first letter capital, rest lower case, keep prefixes like Mc and O' correct].
- Phones: [RULE, e.g. +1 555 123 4567].
- Addresses: [RULE, e.g. street, town, state code, postal code in separate columns].
- Emails: lower case, trimmed.
- Country: [DEFAULT COUNTRY].

Contacts (CSV with header row, record ID in the first column):
[PASTE CSV ROWS]

Tasks:
1. Create a cleaned version of each row using the rules above. Never invent a value. If a value cannot be cleaned with confidence, leave it as it was and add a note.
2. Find likely duplicates. Group rows that share an email, a phone number, or a close name match with the same town. Give each group an ID and a confidence of HIGH, MEDIUM or LOW, with the reason.
3. For each duplicate group, suggest which record to keep and which fields to carry over. Mark it PROPOSED. Do not merge or remove anything.
4. Flag rows missing any of these required fields: [REQUIRED FIELDS, e.g. email or phone, town].
5. Flag rows that look like test entries, spam or placeholder data, but keep them in the output.

Return three tables:
A. Cleaned rows: record ID, each cleaned field, change note.
B. Duplicate groups: group ID, record IDs, confidence, reason, proposed record to keep.
C. Missing or suspect rows: record ID, issue.

Every input row must appear in table A. Do not delete any row.

Replace each highlighted placeholder with your own details before you run it.

Steps

1. Export the full contact list from your Customer Relationship Management (CRM) system as a comma separated values (CSV) file. Save an untouched copy with the date in the file name.
2. Agree the format rules first: name case, phone format with country code, address layout, state or county abbreviations and email in lower case.
3. Decide which columns the AI tool needs. Leave out notes, payment details and any field the task does not require.
4. For small lists, paste batches into the prompt below. For large lists, ask the AI tool for spreadsheet formulas or a script, and run them on a copy.
5. Review the proposed duplicate groups. For each group, a person picks the record to keep and which fields to carry over.
6. Check the missing fields report. Decide which gaps to fill from your own records and which to leave blank.
7. Compare a random sample of 30 cleaned rows with the original. Fix any rule that changed data it should not have.
8. Import only approved changes back into the CRM, using its own merge or update tools. Keep the original export and the change log.
9. Agent mode: a first clean is a one-off chat or project. A monthly or quarterly scheduled run that flags new duplicates and gaps can suit an agent, if your tools support that. The agent proposes changes only, and a person approves them.

What you get

A cleaned table where every original record is still present and every change has a note. A list of duplicate groups with confidence and reasons, plus a list of rows with missing or suspect data. A person can approve each merge before anything changes in the CRM.

What can go wrong, and the check for each

Related playbooks