The Monday morning you realize the number is wrong
Your VP asks how many contacts you have. You pull the dashboard: 5,847. Then someone from sales mentions they called a prospect last week who said, politely, that three different people from your company had already reached out. You search the name. Three records. Three owners. Three sets of notes that each tell a third of the story.
So you dig. Roughly 400 records look like duplicates. About 200 have email addresses that stopped working in 2019. There's a contact named Test Testerson who has been in the system since before you started, complete with a closed-won deal attached to him.
The mess isn't the scary part. The scary part is fixing it. Every RevOps lead has heard the story of the bulk edit that wiped out activity history, or the merge that pointed a live opportunity at the wrong account. That fear is why most messy CRMs stay messy for years.
This is the playbook that removes the fear. Not a listicle of tips, but a full CRM data cleansing workflow: what to do before you touch anything, what order to do the work in, what to never automate, and how to keep the database clean once it is. Work through it in sequence and nothing breaks.
What dirty CRM data actually costs: the four failure modes
Dirty data doesn't announce itself. Reports still run, emails still send, the CRM still loads. The damage shows up sideways, and it usually falls into one of four failure modes. Naming them helps, because each one needs a different fix.
Failure mode one: duplication drag. The same person exists more than once, so they get the nurture sequence twice, get called by two reps, and count twice in your funnel. Your conversion rate is quietly wrong. Your sender reputation takes hits from people marking the third copy as spam. Worst case, someone submits a deletion request and you honor it on one of three records, which is a compliance problem you won't discover until it matters.
Failure mode two: format drift. Same information, different shapes. Phone numbers stored as (555) 123-4567, 555.123.4567, and +15551234567. States recorded as 'CA', 'Calif', 'California', and one brave 'Cali'. Nothing is technically wrong, but every filter, list, and report built on those fields under-reports. Your territory assignment misses accounts. Your regional revenue roll-up is off by whatever percentage of records spelled it differently.
Failure mode three: silent gaps. Blank fields aren't visibly broken, so they don't get fixed. No industry means no segmentation. No company name on a contact with a corporate email means that contact never lands in an account-based campaign. Missing fields don't cause errors, they cause absence, and absence is invisible in a dashboard.
Failure mode four: decay. Data that was accurate eighteen months ago describes a world that no longer exists. People change jobs, companies get acquired, domains lapse. Roughly a quarter of B2B contact data goes stale each year in most databases, and nothing in your CRM flags it. The record looks fine. It just isn't true anymore.
Notice that these compound. A stale record gets re-entered by a rep who can't find it because the name was formatted differently, which creates a duplicate, which splits the history in half. That's how 5,000 records become unusable in three years.
- Duplication drag: inflated counts, repeated outreach, split history, deletion requests honored on only one copy.
- Format drift: filters and reports miss records that are technically present.
- Silent gaps: missing fields quietly exclude records from segments and campaigns.
- Decay: accurate data expires and nothing marks it as expired.
Pre-flight: backup, field inventory, and record ownership
Do not start with duplicates. Start with three unglamorous tasks that make everything after them reversible.
Take a complete export first. Every object, every field, every record. Contacts, companies, deals, activities, custom objects, and the association tables that link them. Store it outside the CRM entirely: not the recycle bin, not a connected drive that syncs back. If you can't rebuild a single record from your export alone, the export isn't complete. Date the file. You'll want to know exactly what the database looked like at hour zero.
Then build a field inventory. Export your field list and put four columns next to it: fill rate, who writes to it, what reads it, and whether anyone would notice if it disappeared. Most inherited CRMs have between 40 and 200 custom fields, and typically a third of them are dead. Fields created for a campaign in 2021. Two fields that mean the same thing because two teams each built their own. Fields an old integration wrote to and nothing has updated since. You cannot standardize a field you can't explain, so mark the dead ones for archive before you clean anything.
Third, map record ownership. Who owns each record, and is that owner still at the company? Orphaned records assigned to departed reps are the single most common source of 'nobody followed up' incidents. Build a list of records owned by inactive users now, because merge order in the next step depends on knowing which record a live human is actually working.
Last pre-flight step: clean 100 records by hand. Pick them at random, not the easy ones. Fixing a hundred records manually teaches you what your specific mess is made of, and that knowledge sets your match rules. It takes an hour and saves a week. If you're doing this in HubSpot, Salesforce, or Klaviyo, our platform resource pages walk through where each of these exports lives and which fields tend to hide the problems.
- Full export of every object and association, stored outside the CRM, dated.
- Field inventory with fill rate, writers, readers, and a keep or archive decision.
- Ownership map, including every record assigned to an inactive user.
- One hundred records cleaned by hand to learn the shape of your mess.
Dedupe: match rules, merge order, and what to never auto-merge
Duplicates are the highest-value fix and the highest-risk one. Exact matches are trivial. The hard cases are the near-matches, where two records describe one person in two different ways, and the dangerous cases, where two records look identical and describe two different people.
Set your match rules in tiers, and give each tier a different level of trust. Tier one is a verified email match: same email, different records. Merge those with confidence. Tier two is same last name plus same company domain plus a first-name variant, which is how 'Robert Smith' and 'Bob Smith' at acme.com get reunited. Review those in batches, but you can move fast. Tier three is everything looser: same name at different domains, same phone with different names, same company recorded as 'IBM' and 'International Business Machines Corporation'. Tier three is a review queue, never an automatic action.
This is where plain string matching fails. Comparing characters catches typos and misses meaning. SmartMatch compares records semantically, so it understands that Jon and John are the same first name, that Acme Corp and Acme Corporation are one company, and that a shared verified email outweighs two different spellings of a name. It returns a confidence level with each match, which is what lets you draw the line between auto-merge and review.
Merge order matters as much as match accuracy. Decide, before you start, which record survives. The usual best answer: keep the oldest record as the surviving ID, because it carries the most associated history and the most inbound links from other systems, then pull the freshest field values from the newer records into it. Preserve every activity, note, and attachment from both sides. If your CRM lets you choose the surviving record, never let it default to 'most recently modified', because the most recent record is often the accidental one a rep created five minutes ago.
Some things should never be merged automatically, no matter how strong the match looks.
- Records with open deals or opportunities on both sides. A human decides which pipeline survives.
- Parent and child companies, franchises, and locations. 'Acme (Denver)' and 'Acme (Austin)' are not duplicates.
- Shared inboxes: info@, sales@, accounts@. Three people can sit behind one address.
- Common names with no shared domain. Two John Smiths at different companies are two John Smiths.
- Records with conflicting consent or subscription status. Merging an unsubscribed record into a subscribed one can re-enroll someone who opted out.
- Anything where the two records have different billing or contract data. That's a finance conversation, not a merge.
Standardize: names, phones, addresses, and picklists
With duplicates resolved, formatting is the fastest win in the whole project. It's tedious, which is why it gets skipped, and it's also what makes every future report, filter, and export behave.
Names need real capitalization rules, not blunt title case. 'john smith' becomes John Smith and 'JANE DOE' becomes Jane Doe, but 'MCDONALD' should end up McDonald and 'van der Berg' should keep its lowercase particles. Strip trailing whitespace, strip the credentials that ended up in the last name field, and pull job titles out of name fields where someone pasted a signature block. AutoFormat handles the casing edge cases as a rule set rather than a one-time find and replace, which means the fix survives the next import.
Phones need one format, chosen once. E.164 (+15551234567) is the right default if you have any international contacts, because it's the only format that stores country context reliably and it's what most calling and messaging tools expect. If you're strictly US domestic, (555) 123-4567 is fine. What isn't fine is three formats in one column, because then no dialer, no dedupe rule, and no phone-based match works consistently. Store extensions in their own field.
Addresses standardize in a specific order: country first, then state or region, then city, then postal code, then street. Country first, because 'CA' means California in the US and nothing in Canada. Pick full names or abbreviations for states and apply that choice everywhere. Verify postal code against city rather than the reverse, since postal codes are the more reliable of the two.
Picklists are where standardization pays off most and gets attention least. Industry is the usual disaster: a free-text field with 340 distinct values, including 'SaaS', 'Saas', 'software', 'Software/Tech', and 'B2B SaaS'. Collapse it to a real list, ideally 12 to 20 values that map to how your team actually segments. Same for lifecycle stage, lead source, and company size bands. Once the list is fixed, change the field type so free text can't come back. Lead source deserves particular care, because it feeds attribution, and an attribution report built on 340 values isn't a report.
Fill gaps: when to impute, when to flag, and when to leave it blank
A blank field is a decision waiting to be made, and there are only three right answers: infer it, flag it, or accept it.
Infer when the pattern in your own data is strong and the field is low-stakes. If 95% of contacts on the acme.com domain have Acme Corporation as their company, filling that in for the remaining 5% is safe. If every record in a postal code shares one city, fill the city. If a company's country is blank but its state is Ontario, that's Canada. SmartFill does this against your existing data and attaches a confidence score to each suggestion, which is the part that matters. Accept high-confidence fills in bulk. Route anything middling to review.
Flag when the field drives money or messaging. Deal value, contract dates, account tier, billing address, industry on your enterprise accounts. Guessing there is worse than a blank, because a blank prompts someone to look it up while a wrong value gets trusted. Flagging turns a silent gap into visible work, which is the entire goal.
Leave it blank when nobody reads the field. This is the payoff from your field inventory. If a field has a 4% fill rate and no owner and no consumer, don't spend a single hour filling it. Archive it. The most efficient gap-filling decision is often deleting the gap.
One more rule: never impute consent. Subscription status, marketing permission, and legal basis are recorded facts, not predictions. If it's blank, it's blank, and the record doesn't get emailed until someone confirms otherwise. That applies whether the data lives in your CRM or in the clean list you export and load into Klaviyo or Mailchimp, where a wrong assumption reaches an inbox in minutes.
- Impute: derivable, low-stakes fields with strong internal patterns and a confidence score attached.
- Flag: revenue, contract, and segmentation fields where a wrong value gets trusted.
- Leave blank: fields with no owner and no reader. Archive them instead.
- Never impute: consent, subscription status, and anything with a legal basis.
Anomalies and validation: the records that need a human
Some records aren't dirty in a way software can fix. They're wrong in a way that needs judgment, and the job is to surface them, not resolve them automatically.
Anomalies are values that break the rules of their own field. A seven-digit phone number. An email with no @ symbol, or one with two. A founded date in 2047. A contract that ends before it starts. A deal worth $4,000,000 in a database where the median deal is $8,000. A company with 2 employees and $90M in revenue. LogicGuard collects these into a review queue rather than guessing, because the correct interpretation genuinely varies: that odd phone number might be a valid international format you haven't seen, and that huge deal might be real.
Validation is the narrower, deterministic sibling. Does the email domain resolve? Is the postal code real? Does the state exist in the country listed? Are required fields present? Validation produces yes or no answers and can run automatically. Anomaly review produces questions and needs a person.
Work the anomaly queue in short sessions, sorted by record value. Top accounts first, then open deals, then everything else. This is cognitively expensive work and quality drops fast after about forty minutes, so treat 30 records as a good session rather than trying to clear 800 in an afternoon. Set a rule that the queue gets worked weekly and never exceeds a size you could clear in an hour, because a queue nobody clears is just a list of known problems.
Staying clean: intake rules and a monthly cadence
A clean CRM decays back to its old state in about nine months without intake rules. The cleanup is the easy half. The maintenance is what makes it stick.
Fix intake at every door. List which sources write to your CRM: web forms, imports, sales manual entry, support, product events, and every connected tool. For each one, decide what's required, what's validated, and what's standardized on the way in. Make industry a picklist, not free text. Validate email format at the form, not after. Standardize phone format on write. Require country before state. The best CRM data cleansing is the cleaning you never have to do, and the second best is cleaning that happens at the moment of entry.
Kill the duplicate at creation. Turn on duplicate warnings for reps, and make search the default path to creating a record. Most duplicates exist because someone searched 'Bob', found nothing, and typed a new record. A search that also matches Robert prevents that.
Then set a real cadence and put it on a calendar with a named owner. If data quality belongs to everyone, it belongs to nobody.
Track one number over time so you can tell whether the practice is working. That's what the Clarity Score is for: a single measure of completeness, consistency, uniqueness, and validity across your database. Watching it move is more motivating than any report on record counts, and it turns 'the CRM feels better' into something you can show leadership.
If you want a starting point rather than a project plan, export your CRM and run a free Clarity Score on it. You'll get duplicate clusters, fill rates by field, format inconsistencies, and flagged anomalies in one view, before you change a single record. Or walk through the self-serve product demo first and watch SmartMatch, AutoFormat, SmartFill, and LogicGuard run on a sample database. Every change is logged, every change is reversible, and you see exactly what will happen before it happens. Then take our CRM Data Cleaning Checklist and work the sections in order. It was built for a migration, but the audit, dedupe, and standardization passes are the same work.
- Weekly: clear the anomaly and low-confidence review queue. Thirty minutes.
- Monthly: run duplicate detection, check fill rates on your five most important fields, review records owned by inactive users, and log the Clarity Score.
- Quarterly: audit picklist values for drift, review new fields created since last quarter, and check every integration for what it's writing.
- Annually: archive dead fields, re-verify contact data on your top accounts, and re-read your intake rules against reality.
The one-page brief for getting the cleanup approved
Most of the resistance to this work isn't technical. 'Clean the CRM' sounds like maintenance, so it loses to anything with a revenue number attached. The argument that does land right now is the AI one: every AI feature in the revenue stack scores, personalizes, and forecasts from records exactly as it finds them. Duplicates become double-counted pipeline. Blanks become confident guesses. The errors arrive polished, in full sentences, which is why they go unquestioned for weeks.
We put that case on a single page for RevOps: the four ways dirty data breaks AI, what one missed match costs downstream, a working definition of 'AI-ready data', the two-week plan in order, and a twenty-minute baseline exercise you can run before you ask anyone for anything. Print it, or send it ahead of the meeting.
Frequently Asked Questions
How often should I run CRM data cleansing?
Monthly if multiple sources write to your CRM, quarterly if you have one or two clean entry points and low volume. The bigger factor is consistency: a thirty-minute monthly pass is far easier and safer than one annual marathon, because small batches let you catch a bad rule before it touches ten thousand records.
What is the difference between data cleansing and data validation?
Validation is a yes or no check applied when data arrives: is this a real email format, does this postal code exist, is this required field present. Cleansing is the corrective work you do on data already in the system: merging duplicates, standardizing formats, filling gaps, and reviewing anomalies. You need both, because validation stops new mess and cleansing clears the mess you inherited.
Is it safe to auto-merge duplicate records in a CRM?
Yes, for one specific case: records that share a verified email address, where neither has an open deal and both have the same consent status. Everything looser than that belongs in a review queue, and parent or child companies, shared inboxes, and common names with different domains should never be auto-merged at all. Always keep a dated full export and use a tool that logs and reverses every merge.