The 30-minute rule: what you can and can't learn
Someone in the Monday meeting says the CRM is a mess. Everyone nods. Then your VP asks how bad, exactly, and the room goes quiet, because nobody has a number. The best anyone offers is that last month's send bounced "a lot" and two reps called the same account on the same day.
That gap between everyone knowing and nobody proving is the whole problem. A CRM data audit fixes it. Not a three-week project with a steering committee. Thirty minutes, one export, seven numbers.
Be clear about the limits. In half an hour you can measure the shape of the mess: how much is duplicated, how much is blank, how much is stale, how much is formatted four different ways. You can't verify whether a specific job title is correct, confirm a company still exists, or find records that are wrong in ways that look right. A phone number with the correct number of digits belonging to a person who left in 2021 will pass every check below.
That's fine. You are not trying to fix the data today. You are trying to size it. A number that is roughly right beats a feeling that is definitely strong, and a number is the only thing that survives contact with a budget conversation. If you want the theory behind what you're measuring, our framework post on how to measure data quality covers it. This is the doing version.
Minutes 0 to 5: pull the export
Export your contacts or leads to CSV. One object, not all of them. If you run Salesforce or HubSpot, use the standard report export rather than a custom view someone built in 2022, because custom views hide rows and hidden rows wreck your denominators.
Take everything, not a sample. If your list is over 200,000 rows and your spreadsheet gets unhappy, take the most recent 50,000 by created date and note that you did. A consistent slice is better than a struggling full set.
Make sure these columns come along. If one is missing from your system, skip its check and score the remaining six.
Then two housekeeping steps before you start counting. Freeze the top row, and add a column that lowercases and trims the email address, because "Dana@acme.com " and "dana@acme.com" are the same person and your formulas won't know that unless you tell them. Write your total row count somewhere visible. Every number below is a percentage of it.
- Record ID
- First name, last name
- Company or account name
- Phone
- State or country
- Lead source (or equivalent picklist)
- Owner
- Created date
- Last activity or last modified date
Minutes 5 to 15: checks one through four
Check one, duplicate rate. Count the distinct values in your cleaned email column. Subtract that from your total rows, divide by total rows. That's your floor, not your ceiling, because it misses the same human recorded twice under a work address and a personal one. For a second pass, build a key from last name plus company name and count distinct values again. Use the higher of the two rates. Most B2B lists that have never been deduplicated land between 8% and 22%.
Check two, blank-field rate. Pick the five fields your team actually uses to do work. For a sales org that's usually company, phone, owner, lead source, and state. Count blanks in each column, divide by total rows, then average the five. Do not include fields nobody looks at. Measuring the blank rate on a legacy field inflates the problem and makes your whole audit easier to dismiss.
Check three, format variance. Take one field with an obvious correct form. State is the classic. Build a quick pivot of distinct values and look at what comes back: "CA", "Calif", "California", "California ", and one brave "Cali". Count the rows that are not in your preferred format, divide by the rows that have any value at all. Phone numbers work too if you'd rather measure those: count anything that isn't a consistent digit pattern.
Check four, invalid email share. Two sources here. First, the export itself: count addresses missing an @, containing spaces, ending in a broken domain, or using obvious typo domains like gmial.com. Second, and better, open Mailchimp or Klaviyo and pull the hard bounce rate from your last full send. Use the higher number. Hard bounces are the most persuasive statistic in this entire audit, because they cost money in a way finance already understands.
Minutes 15 to 25: checks five through seven
Check five, stale-record share. Count records whose last activity date is more than 12 months old, or blank. Divide by total rows. Blank counts as stale, and people will argue about that. Hold the line: if there's no evidence anyone has touched a record in a year, it is not an active record, it's a row.
Expect this one to hurt. Lists that have been accumulating since the last CRM change frequently come back at 40% or higher. That number alone reframes the pipeline conversation, because half of what your team thinks it owns is archaeology.
Check six, picklist sprawl. Pick your most-used picklist, usually lead source or industry. Count the distinct values in the export. Compare that against the number of values that are supposed to exist. If the documented list has 8 options and the export has 41, you have sprawl: free-text entries, old campaign names, a value that is just a hyphen, and three spellings of "Webinar". Score this one as documented values divided by actual values, times 100. Eight over 41 gives you 20.
Check seven, orphan rate. These are records missing the link that makes them usable. Contacts with no company. Contacts with no owner. Accounts with no contact attached. Count rows that fail any one of those, divide by total. Orphans are what generates the "who owns this?" Slack thread, and they're usually a small percentage doing a large amount of damage.
You should now have seven numbers written down. Total elapsed time, if you didn't stop to investigate anything, about 25 minutes. The urge to investigate is strong. Resist it until the scorecard is done.
Minutes 25 to 30: turn seven numbers into one score
Seven percentages are hard to present. One number out of 100 is easy. Group your checks into the four dimensions the Clarity Score uses: Completeness, Consistency, Uniqueness, and Validity.
Convert each rate into a score by subtracting it from 100, so a 14% duplicate rate becomes a Uniqueness score of 86. Picklist sprawl is already a score, so use it as is. Then average within each group and average the four groups for your overall number.
A worked example. Blanks 22%, so Completeness is 78. Format variance 31% and picklist sprawl score 20, so Consistency is 45. Duplicates 14%, so Uniqueness is 86. Bounces 9%, stale 44%, orphans 6%, so Validity is 80. Overall: 72.
This is a hand-built approximation, and you should say so when you present it. CleanSmart's Clarity Score weights the same four dimensions across your full record set rather than averaging four numbers evenly, so the automated figure will differ from your spreadsheet version. The direction and the shape will match, which is what matters. In the example above, nobody needs a weighting debate to see that Consistency is the fire.
- Completeness: blank-field rate
- Consistency: format variance and picklist sprawl
- Uniqueness: duplicate rate
- Validity: invalid email share, stale-record share, orphan rate
What each score band means
Bands give your number context, which is what turns it from trivia into a decision. Here's how to read the result.
90 to 100. Genuinely clean. Your remaining problems are individual records, not systemic ones. Set a quarterly recheck and go work on something else.
75 to 89. Functional with friction. Reps work around known gaps, reporting is directionally right but nobody fully trusts it, and marketing pads its list sizes to compensate. This is where most well-run teams sit, and it is also where scores quietly slide without anyone noticing.
60 to 74. Actively costing you. Duplicate outreach happens. Territory and attribution reporting is wrong often enough that people have stopped citing it. Deliverability is starting to slip because bounce rates are drifting up. Fixing this is a defensible quarter's work with a visible return.
Below 60. The CRM is a liability. Decisions made on this data are guesses wearing a suit. If you scored here, stop the audit and don't apologise for the number. You didn't cause it. Years of imports, campaigns, a system change or two, and a hundred well-meant manual entries did.
Whatever you scored, write the date next to it. The single most useful thing about this audit is that it's repeatable. Run it again in 90 days and the delta is your actual proof of progress.
What to fix first
Fix in the order that stops new mess from arriving, not in the order of worst score.
Start with Consistency, because it's upstream of everything. Standardize your state, country, and phone formats, then collapse your picklists to the documented list and turn off free-text entry on those fields. AutoFormat handles the standardizing in bulk. This is also the fastest visible win, since inconsistent values are the thing everyone in the room can see.
Then Uniqueness. Deduplicate once Consistency is handled, never before, because messy formatting hides matches. SmartMatch finds the records that belong together, including the ones that don't share an email address, and shows you each merge before you accept it.
Then Validity: suppress the hard bounces, retire or archive the stale records, and route the orphans to an owner. Completeness comes last, because filling blanks in records you're about to merge or archive is wasted effort. SmartFill closes the remaining gaps, and LogicGuard flags the values that look plausible but aren't, like a close date in 2041.
If the spreadsheet work sounds like a bad way to spend your Thursday, it is. CleanSmart does this part for you: DataBridge reads your records from Mailchimp, Shopify, Klaviyo, HubSpot, or Salesforce, you clean and review, and you get your results as a CSV or JSON export plus a change log to bring back into your platform on your own terms. Your Clarity Score is recalculated after each cleaning run, which takes a few minutes, so you can watch the number move instead of rebuilding your pivot tables. Try the self-serve product demo and get your Clarity Score automatically instead.
Frequently Asked Questions
What if my CRM export is too big to open in a spreadsheet?
Take the most recent 50,000 records by created date and note that your figures cover a slice rather than the full set. The percentages stay valid as long as you apply every check to the same slice.
Should I do a CRM data audit before or after cleaning?
Both. The first run gives you the baseline that justifies the work, and a second run 90 days later gives you the delta that proves it happened.
Will my hand-built score match CleanSmart's Clarity Score?
Not exactly, because the Clarity Score weights Completeness, Consistency, Uniqueness, and Validity across your full record set rather than averaging them evenly. The weak spots it identifies will be the same ones your spreadsheet found.