Clean Your Customer Data Before AI Breaks It

Validity's CRM data health reports describe something specific and strange: organizations rate their own customer data as "good" while simultaneously reporting severe decay, duplicated records, and direct revenue loss from those conditions. Leaders frequently fail to notice the problem. This is not a story about resource constraints. It is a story about people who believe they have already solved something they have not looked at.

The OECD and SAS research adds a number worth sitting with: 23.5% of respondents name unprepared data as a top barrier to AI adoption, ahead of concerns about cost or trust in the technology. If you are waiting to deploy an AI tool until you can afford better software, you are solving the wrong problem.

The counterargument worth taking seriously

A reasonable founder pushes back here. They know their data is scattered across a spreadsheet from three years ago, a CRM added later, and an email platform pulling from neither. They have looked at this situation and concluded, correctly, that untangling it takes time they are spending on customers and payroll. The OECD and SAS research does frame the barriers as a cluster: weak data quality, siloed access, and limited governance are named as distinct problems. Awareness alone does not dissolve structural fragmentation.

This counterargument holds for founders who have looked at their data and found problems. The Validity evidence describes a much larger group: organizations that have not looked, believe they do not need to, and are losing revenue as a result. For that group, the capacity constraint feels acceptable because the diagnostic failure makes it feel acceptable. A founder who believes the data is in reasonable shape will not allocate time to fix it regardless of how much time they have.

What the seven steps actually do

Google Sheets and OpenRefine documentation shows that structured deduplication, field validation, faceting, and bulk transformation are executable without specialist staff. The capacity constraint is real, but it is not as steep as the counterargument implies.

Start in Google Sheets. Use COUNTIF to surface duplicate email addresses across your contact list. A formula like =COUNTIF(B:B,B2)>1 flags every row where the email appears more than once. Delete or merge the duplicates before moving forward.

Second, validate email formats with a regex check. =REGEXMATCH(B2,"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$") returns FALSE on anything malformed. Addresses returning FALSE will bounce in any email platform and corrupt any AI segmentation built on top of them.

Third, audit missing fields. Use =COUNTBLANK(A2:A500) on each column you rely on for outreach or analysis. A column with more than a handful of blanks in a field like "company name" or "last purchase date" is not a minor gap. It is a broken input for any downstream model.

Fourth, import the cleaned sheet into OpenRefine. Run a text facet on your "contact type" column. OpenRefine clusters near-identical values automatically: "customer," "Customer," "CUSTOMER," and "cust." collapse into one. This step alone removes a category of error AI tools cannot self-correct.

Fifth, use OpenRefine's clustering on name fields to catch "Jon Smith" and "John Smith" living as separate records. The fingerprint and n-gram clustering algorithms in OpenRefine surface these without manual review.

Sixth, check phone number formats. Standardize to a single format using OpenRefine's transform function with a GREL expression. Inconsistent formats across records are invisible to a human skimming a list and fatal to any tool doing phone-based matching.

Seventh, export and version the cleaned file with a date stamp. Do not overwrite the original. You need a before-and-after to measure what changed and to recover if a transformation misfired.

What clean data actually enables

The 23.5% figure from OECD and SAS research names unprepared data as the top barrier to AI adoption. Not cost. Not skepticism. Not technical complexity. The seven steps above do not require a data team, a paid tool, or more than a few hours on a file you already have.

Run the COUNTIF check on your email column first. The number it returns will tell you whether the rest of this is urgent or optional.