Back up a full export first, then clean in this order: merge duplicates, standardise names, emails and phone numbers, record marketing permission with its source and date, mark lapsed and do-not-contact customers, and fix the status fields your AI will act on. For 1,000 to 3,000 records, expect one to two days, mostly spent on duplicates.
The reason to do this before adding AI rather than after is scale. A person sending a campaign by hand notices the customer who complained last month; an AI tool writing to 2,000 people at once doesn't. Every flaw in the records becomes a message. The fix is four passes through the records in a set order, backed by matching rules for duplicates, a merge log, and a monthly check that keeps the list clean once AI is working on it.
What messy records do once AI starts acting on them
These are the failures that make owners wish they'd spent the extra day:
- Duplicates become double messages. Someone in the list twice gets two "we miss you" emails, one addressed to their maiden name.
- Stale status becomes the wrong message. A customer who cancelled after a complaint gets a cheerful loyalty offer.
- Missing permission becomes a compliance problem. An AI campaign tool treats a blank permission field as "fine to email".
- Bad formatting breaks personalisation. "Dear JANE SMITH" and "Dear jane" in the same campaign look careless, and emails with a trailing space fail to send.
Which fields matter depends on the AI job
You don't need every field perfect. You need the fields your first AI job will act on to be right, and the others merely tidy. Decide the job first, then spend your effort accordingly:
| First AI job | Fields that must be right | Fields that can wait |
|---|---|---|
| Win-back or re-engagement emails | Status, marketing permission, email, preferred name, do-not-contact | Phone, date of birth, full address |
| A chat assistant that recognises existing customers | Email and phone format, current bookings, open complaints | Old marketing history |
| Churn or retention analysis | Sign-up date, visit dates, membership changes | Names and contact details (the analysis can run on IDs) |
| Personalised offers | Purchase or class history, categories, permission | Anything not used to choose the offer |
All four passes below still apply, but the table tells you where to be meticulous and where "good enough" is good enough.
Before you change anything: back up and start a merge log
Export the full customer list from every system that holds one, save the exports in a dated folder, and don't touch them again. Work on a copy. Then create a merge log, a separate sheet where every change is recorded:
MERGE LOG
Date | Kept record ID | Removed record ID | Matched on | Fields changed | By
-----|----------------|-------------------|------------|----------------|----
| | | | |After its first morning, the yoga studio worked through below had log rows like these (illustrative):
Date | Kept | Removed | Matched on | Fields changed | By
-----------|-------|---------|---------------------|--------------------------------|----
2026-09-14 | C0118 | C2207 | Email (certain) | Phone updated from C2207; | AM
| | | | 14 marketplace bookings moved |
2026-09-14 | C0342 | C1985 | Phone (probable) | Email updated; preferred name | AM
| | | | "Jo" added |
2026-09-14 | C0671 | - | Phone (probable) | NOT MERGED: household, linked | AM
| | | | to C1420 |The third row matters as much as the other two. Logging a decision not to merge stops someone else "fixing" the same pair next month.
It feels like overhead. It's what lets you undo a bad merge three weeks later when a customer asks why their class history has vanished.
Pass 1: find and merge duplicates
Duplicates in small businesses come from customers signing up twice (once online, once at the desk), bookings arriving through class-booking marketplace apps, changes of email address, and couples or families sharing an address. Match them in three grades:
| Grade | Matching rule | What to do |
|---|---|---|
| Certain | Same email address, once both are lower case and trimmed | Merge |
| Probable | Same phone number after formatting, different email | Merge after a quick look at names |
| Possible | Similar name plus one other match (date of birth, phone, or same first booking date) | Review by hand; merge only if sure |
Watch for false matches. Two members of the same family often share a phone number or even an email address, and they are two customers, not one. If a "probable" match has different first names and different dates of birth, it's a household: link the records if your system allows it, but don't merge them.
A typical case: two records share a mobile number. One is a member who joined in 2022, date of birth in the 1970s, attending twice a week. The other was created in 2025 with a different first name and a date of birth that makes them 16, booked by the same card. That's a parent booking for a teenager, and merging them would move the teenager's classes onto the parent's record and send the parent's renewal reminders to both. Link, don't merge.
When you merge, keep the oldest record's ID (so history links survive), take the most recent contact details, combine the booking and payment history, and log the change.
A word on built-in tools. In Google Sheets, Data, Data cleanup, Remove duplicates deletes repeated rows, and Google's help page notes that it treats cells with the same value but different letter case as duplicates. Excel's Remove Duplicates button on the Data tab works in a similar way. Both are fine for a list of email addresses; on customer records they delete a row rather than merging its history, so use them only on a helper column you've built for matching. If your records live in HubSpot, its duplicate management tool needs a Professional or Enterprise subscription. Cleaning up a messy CRM with AI covers the CRM route in detail.
Pass 2: standardise the fields AI will read
Once duplicates are merged, make every record look the same:
- Names: first name and last name in separate columns, in normal capitalisation. Keep a "preferred name" column if customers use one; AI personalisation should use it.
- Emails: lower case, no spaces. In Google Sheets, Data, Data cleanup, Trim whitespace removes stray spaces, though Google notes it doesn't remove non-breaking spaces, which often arrive in data copied from web pages.
- Phone numbers: one international format throughout: a plus sign, the country code and the number, with no spaces. Messaging tools and automations expect it.
- Dates: year-month-day for sign-up, last visit and date of birth.
- Free-text "notes" fields: move anything that's really a status ("DO NOT BOOK", "moved away") into a proper field. AI tools may not act on a warning buried in a note.
One record from the studio's old spreadsheet, before and after this pass (illustrative):
| Field | Before | After |
|---|---|---|
| Name | JANE smith (two spaces, one column) | first_name: Jane; last_name: Smith; preferred_name: Janey |
| Jane.Smith@Example.com followed by a stray space | jane.smith@example.com | |
| Phone | Mobile typed with spaces and a leading zero | Plus sign, country code and number, no spaces |
| Joined | 12/03/24 | 2024-03-12, checked against her first booking |
| Notes | "Janey. DO NOT BOOK til she pays for 2 classes" | payment_hold: Y (dated 2026-08-30); note kept for the detail |
The notes row is the one that protects you. Left in free text, "DO NOT BOOK" is invisible to a campaign tool choosing who gets a "come back" offer; as a field, it can be excluded with one rule.
Pass 3: permission and contact status
This is the pass with legal weight. For every record, you want three things recorded: whether the person agreed to marketing, where they agreed (online form, in person, booking app) and when. Where the systems disagree, the most restrictive answer wins. If your email tool shows someone unsubscribed, that overrides a "yes" in the booking system, however old.
A conflict resolved that way looks like this in the record: marketing_ok: N, permission_source: email tool (unsubscribed), permission_date: 2025-06-02, with the older "yes" from the booking system noted as superseded rather than deleted. If the customer later ticks the box again at the front desk, the new tick, its source and its date replace the lot. Keeping the source and date is what lets you answer "why did I get this email?" in one sentence.
Add two more fields while you're here: a do-not-contact flag for anyone who's asked not to be contacted at all, and a complaint flag for recent unresolved complaints. Both should be checked by any AI campaign or outreach job before it writes to anyone. What counts as valid permission depends on data-protection law such as the GDPR and on where your customers are; what a small business must do under the GDPR when using AI tools sets out the practical side, and your data-protection adviser can confirm the rest.
Pass 4: set status rules from data, not memory
AI jobs usually target a segment: active members, lapsed members, one-off visitors. If "lapsed" means whatever the person doing the export thinks it means, the AI will act on a guess. Write the rules down and calculate the field from the data:
STATUS RULES (calculated from last_visit and membership fields)
Active Attended at least once in the last 60 days
Lapsing Last attended 61-120 days ago
Lapsed Last attended 121-365 days ago
Former No visit in 365+ days, or membership cancelled
Never Signed up or enquired, never attended
Overrides do_not_contact = Y -> exclude from every campaign
complaint_open = Y -> exclude, and alert the ownerPick thresholds that fit how often your customers normally come; a studio with weekly regulars needs shorter windows than a business people visit twice a year. An illustrative hair salon, where regulars book every six to eight weeks, set its rules like this:
Active Visited in the last 90 days, or has a future booking
Lapsing Last visit 91-150 days ago, no future booking
Lapsed Last visit 151-365 days ago
Former No visit in 365+ days
Overrides do_not_contact = Y -> exclude from every campaign
on_hold = Y -> exclude (maternity, long travel,
illness; set by staff with a date)The "future booking" clause and the on_hold field both came from mistakes. Without the first, clients who had booked three months ahead for a wedding were flagged as lapsing. Without the second, a regular on a planned six-month break got a "we miss you" discount she hadn't asked for, and asked why she was being chased. Any rule based only on last visit will misclassify people like that, so give staff a way to mark them.
These same fields are what make later jobs possible, such as a churn analysis of why customers cancel.
Using AI during the clean-up without handing it your list
AI is genuinely useful here, as long as the customer list itself doesn't go into a chatbot. Three safe ways to use it:
- Formulas. Describe your columns and ask for the formulas: a helper column that lower-cases and trims emails, a count of how many times each phone number appears, a status calculated from the last-visit date.
- Fuzzy-match suggestions on stripped data. Give it names and IDs only, with nothing else, and ask which pairs might be the same person. You then check the suggestions against the full records yourself.
- Rules drafting. Describe your business and ask it to suggest status thresholds and edge cases you've missed.
Below is a list of customer IDs and names only. Identify pairs that may
be the same person (spelling variants, nicknames, maiden/married names,
swapped first and last names). For each pair, give both IDs, your reason,
and a confidence of high/medium/low. Don't merge anything; just list.A typical reply on a few hundred names looks like this (illustrative, first names only here):
C0212 / C1877 "Katherine" and "Kate", same surname
Reason: common short form. Confidence: high
C0455 / C2093 Same first name; surnames differ by one letter
Reason: likely typo. Confidence: medium
C0598 / C2011 "Chris" and "Christine", same surname
Reason: possible short form. Confidence: mediumChecked against the full records, the first pair was one person with an old and a new email address. The second was a typo made at the front desk. The third was two people: a father and daughter, with different dates of birth and both still attending. The AI had no way to know that, which is exactly why the prompt says "don't merge anything". Expect roughly this mix, and treat every medium-confidence pair as a question rather than an answer.
Use a company AI account with model training off, even for names only. If you'd rather strip the data further before it goes anywhere, there are several ways to anonymise client data before it reaches an AI tool.
A yoga studio's 2,300 records, pass by pass
Say an illustrative yoga studio is preparing to use AI for a win-back campaign. Its records came from four places: its booking system, bookings from two class-booking marketplace apps, an old spreadsheet from before the booking system, and its email marketing tool. Combined: 2,300 rows.
- Pass 1 found 520 duplicates, mostly marketplace bookings that had created a second record for existing members. After merging: 1,780 customers. This took most of the first day.
- Pass 2 took two hours with helper-column formulas. Forty-one email addresses turned out to be invalid (typos like a missing full stop), which the studio would never have found otherwise.
- Pass 3 found 310 records with no permission recorded, which were treated as no, and 64 people who had unsubscribed in the email tool but still showed "yes" in the booking system.
- Pass 4 gave 610 active, 240 lapsing, 480 lapsed and 450 former or never-attended customers.
Total time: about a day and a half. The win-back campaign went to lapsed members with permission, just under 300 people, instead of the 900-odd rows the uncleaned list would have produced, duplicates and all. Every one of the 64 unsubscribed people would have been emailed without Pass 3.
Keeping records clean once AI is running
Clean-up decays. Three habits hold it:
- One way in. New customers are created in one system only, and the other channels feed into it.
- Validation at entry. Required email and phone fields, drop-downs for anything categorical, and a permission tick-box with the date recorded automatically.
- A 15-minute monthly check. Count possible duplicates created this month, records with blank permission, and customers marked active with no visit in 60 days. If any count is rising, find the entry point causing it.
The yoga studio's first two monthly checks show why the counts matter more than the clean-up itself (illustrative). In the first month: 3 possible duplicates, 5 blank permissions, 12 "active" members with no visit in 60 days. In the second: 11 possible duplicates, 5 blank permissions, 9 active-but-absent. The permission and status figures were steady; the duplicates had nearly quadrupled. Every new one had arrived through a second marketplace app the studio had connected that month, which created a fresh record for each booking instead of matching on email. The fix was a setting in the booking system's integration, not another afternoon of merging. The nine active-but-absent members were a reminder that a status column is only as fresh as its last recalculation: re-running the rules moved them to lapsing before the next campaign could treat them as regulars.
With that in place, the records are ready for the job you cleaned them for, and for the next one. Preparing your business data for AI covers the wider steps when that next job needs more than customer records.
Questions that come up mid-clean-up
Should I delete old customers or keep them?
Keep what you have a reason and a lawful basis to keep, and delete the rest on a schedule you've written down. Many businesses keep financial records for a set period and remove marketing data for people who've been inactive for years. Mark former customers clearly either way, so no AI job treats them as current. Your accountant or data-protection adviser can confirm the retention periods that apply to you.
What if two records disagree and I can't tell which is right?
Keep both values in the merged record: the more recent one in the main field and the other in a note, with the date and source of each. Don't guess. The next time the customer books or replies, confirm the detail with them and update the record. For marketing permission, if the two records disagree, treat it as no.
Is it worth paying for a de-duplication tool?
For a few thousand records, usually not. Spreadsheet sorting, a helper column and a careful afternoon will find most duplicates. Paid tools, or a CRM tier that includes duplicate management, start to earn their cost when you have tens of thousands of records, several people creating them daily, or duplicates reappearing faster than you can merge them.
Further reads
- What Is an AI CRM and Which Features Actually Save Time? — What an AI CRM can do once the records are clean.
- Business Data Backup Checklist Before You Connect AI Tools — The backup routine to run before any tool can edit records.
- How Pilates Studios Use AI to Turn Intro Offers Into Memberships — A studio's first AI job on freshly cleaned records.
- What Is AI Churn Prediction and Can a Small Gym Use It? — Why lifecycle status fields matter for predicting cancellations.
- How to Classify Business Data Before Using AI Tools — Decide which customer fields any AI tool may see.
- How to Keep Customer Data Private When Your Team Uses AI — Everyday rules once the team starts using the records with AI.
- What Business Data Should You Start Collecting Now for AI? — Seven datasets worth capturing from today (enquiries, quotes, job actuals, questions, complaints, prices, feedback), with the fields that make them usable.
- Do You Need a Lot of Data to Use AI in Your Business? — How much data each common AI task needs, from three example emails to two years of sales history, and why scattered data is the bigger problem.
- How Salons Use AI to Rebook Clients and Fill Gaps in the Diary — Return intervals from your own history, a chair-side rebooking habit, and a way to match each empty slot to the five clients most likely to take it.
- How Barbers Can Win Back Lapsed Clients With Automated Messages — A lapse rule based on each client's own visit rhythm, three short texts with timings, and how to handle chair renters and barbers who've left.
- How to Automate Buyer and Vendor Follow-Up With AI — Map every follow-up moment, capture viewing feedback in a minute, let AI draft buyer nudges and vendor updates, and keep offers and bad news with a negotiator.
- How to Automate Insurance Renewals With AI and Keep More Clients — Automate the renewal calendar, pre-fill last year's answers, score which clients might leave, and spend broker time on the calls that keep them.
- Donor Thank-You Letters With AI That Still Feel Personal — Personal means specific, not a first-name merge: feed AI each gift's details and true impact stories, tier the response, and keep a real signature.
- Can AI Help a Charity Forecast Demand for Its Services? — Use Excel's Forecast Sheet or a chat assistant to turn two years of service counts into a winter range, then add what the model can't see.
- Set Up Conversion Tracking Before You Let AI Spend Your Ad Budget — Build a conversion acceptance check so automated bidding learns from real business outcomes and avoids duplicate or failed actions.
- How to Segment Your Email List With AI, Even a Small One — Sort a small email list into useful groups using recorded interests, clear rules and a human check of every uncertain label.
- AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.
Sources: Google Docs Editors Help on removing duplicates and trimming whitespace in Sheets; HubSpot Knowledge Base on managing duplicate records. Checked September 2026.