Is your data ready to automate?
Probably less than you think, and that is normal. Before anything is built, pull 30 real records from the system the automation will depend on and check each one for five red flags: data split across places, written different ways, duplicated, missing, or out of date. If all 30 are clean, build on it. If 21 to 29 are, fix the gaps as part of the build. If 20 or fewer are, sort the data out first.
On this page
Key takeaways
- AI can spot the duplicates, the blanks and the odd formats across a whole export, and deciding what to do about them is still a person's job, with its own rules, testing and budget line from the start.
- A person reading messy data fixes it in their head without noticing. An automation only follows the rules it was given, so it stops or gets it wrong quietly, and AI gives a confident answer from whatever it is handed.
- The 30-record test takes about an hour: 30 real records at random, five red flags, and a count of how many records have none. The count tells you whether to build, fix as you build, or sort the data out first.
Most of us think our data is in better shape than it is, and expect AI to figure it out or fix whatever problems we have, which is what we are sold and what everyone seems to post about. AI can help you standardise, clean up and enrich your data, and it can do a lot of the validation too, spotting the duplicates, the blanks and the odd formats across a whole export. Deciding what to do about them is still a person's job though. Someone has to acknowledge the gap, think about how it can and should be fixed, and account for it as a core part of the automation or system from the start, with its own rules, testing and checks, the same as any other part of the build.
Before I scope a project I usually ask for a small data export from each system involved, and in my experience that export says a lot about how the project will go, as data is at the core of every automation. I almost always see one or more red flags that need to be deliberately accounted for and addressed, like one customer saved three different ways, dates typed differently depending on who entered them, a status nobody has updated in months, or the one field the automation depends on left blank.
A person working through that data fixes those things in their head without noticing, but an automation only follows the rules it is given, so it either stops or gets it wrong without anyone seeing, and AI will give you a confident answer from whatever it is handed, so the same problems still happen, just faster and at a bigger scale. Sorting the data out is the part businesses tend to feel they don't have time for, so it often ends up as an assumption or an afterthought, and I think it is the single most important thing to get right, because everything you build on top of it can only be as reliable as the data underneath. This guide covers what “ready” means, the 30-record test you can run on your own data in about an hour, and what to do with the result.
5 flags Split across places, written different ways, duplicated, missing, or out of date. Any one of them means a record needs fixing first.
30 records Enough to catch a problem that affects one record in ten about 96% of the time, and small enough to check by hand.
1 hour About how long the test takes, whether the system holds 80 contacts or 120,000.
Why check the data before you automate?
You can build on the data as it is, and you will find the same gaps either way. Checking first means you find them before the build, while they are still cheap to fix, and the fixes go into the scope and the quote instead of turning up as surprises once people are relying on the system.
Building on the data as it is
- The rules are written for how the data should look
- Records fail, or go to the wrong place
- People start fixing things by hand around the system
- People stop trusting it
Checking it first My default
- Gaps show up before anything is built
- Fixes go into the scope and the quote
- The rules are written for the real data
- It runs the way people expect
This is also why I ask for the export before the first call rather than after it, and the first-call checklist lists it among the things worth sending. The same thinking sits behind the MVP scoping worksheet, where the data structure is the one part of a first version that has to be right from the start.
What does “ready” mean? Five red flags to look for
A record is ready when it shows none of these. If it shows any of them, it needs fixing before an automation can rely on it, and in my experience the fix is nearly always a pattern across many records rather than a one-off.
Data is split across places
Each record should live in one system the automation can read. If part of it sits in a spreadsheet, a shared inbox or someone's notes, the automation only has part of the information.
For exampleCustomer details are in the CRM, and contract dates are in a spreadsheet the account manager keeps.
Data is written different ways
Dates, names, categories and statuses should follow one format. A person reads “Lisbon”, “lisboa” and “LIS” as the same city, and an automation treats them as three.
For exampleA status column has “Done”, “done”, “Complete” and “Closed”, and they all mean the same thing.
Data is duplicated
Each record should appear once, with something unique to match it on, like an ID or an email address. This matters most when the automation moves data between two systems.
For exampleThe same organisation is saved twice under slightly different names, so survey responses get attached to the wrong one.
Data is missing
The fields the automation depends on should be filled in. Other fields can have gaps, so it helps to start by listing which fields the automation needs.
For exampleInvoices are meant to go to the billing contact, and the billing contact field is empty on older accounts.
Data is out of date
The data should reflect what is true now, with a named person or team keeping it that way. If nobody owns it, it tends to get out of date again a few months after the build.
For examplePeople who left last year are still listed as the owner of open requests, so new requests get assigned to them.
A person working through that data fixes those things in their head without noticing, but an automation only follows the rules it is given.
Klara Condac
How do you run the 30-record test?
Four steps, about an hour, and nothing more than an export and somewhere to make notes. The point is to look at real records, including the old and awkward ones, rather than the handful someone tidied up for a demo.
- Work out which data will power the automation, and where it lives. Name the system, the table or list, and the fields the automation will read. Fields it never touches can stay as they are for now.
- Pull 30 real records from it at random. Include a few older ones, and avoid picking the tidy ones. If the system has fewer than 30 records, check all of them.
- Look for the five red flags in each record. Note which ones you find, because the pattern matters more than the total. Three records with the same blank field is one fix, and three records with three different problems is three.
- Count how many records have no red flags. That count, out of 30, is your result, and the three outcomes below tell you what to do with it.
- All 30 are clean: build on it. The data can go straight into the build.
- 21 to 29 are clean: fix the gaps as part of the build. The gaps are usually patterns, so a few rules or a clean-up step can handle them, and a review step catches the rest.
- 20 or fewer are clean: sort the data out first. Agree on formats, merge duplicates and name an owner before anything gets built, or things will just go wrong faster.
Why 30?
A sample's reliability depends mostly on how many records you check, and very little on what share of the total that is, so the same number works for a list of 80 contacts or a CRM of 120,000. Thirty records will catch a problem that affects one record in ten about 96% of the time, against 65% with ten records, and it is still small enough to check by hand in about an hour. If your system has fewer than 30 records, check all of them.
What should you do with the result?
Type in how many of your 30 records had no red flags, and the matching outcome is marked below. The number stays in your browser, and nothing about your data is sent anywhere.
Ready to build
The data can go straight into the build. It is still worth naming who keeps it that way, because the fifth red flag is the one that comes back a few months after the build.
Fix the gaps as part of the build
The gaps are usually patterns, so a few rules or a clean-up step can handle them, and a review step catches the rest. Those rules and that step belong in the scope and the quote, built and tested like any other part of the system.
Sort the data out first
Agree on formats, merge duplicates and name an owner before anything gets built, or things will just go wrong faster and at a bigger scale. What you learn doing it usually changes the scope of the build itself.
Whichever outcome you land on, the step where a person checks what the system did is worth designing in from the start, and the guide to what stays human covers how to decide where that step goes.
Can AI run the check for you?
It can help. AI is good at spotting duplicates, blanks and inconsistent formats across a large export, and it can suggest fixes. Deciding which fixes are right, who owns them and how they will be handled once the system is live is still a person's job, and that thinking needs to go into the scope and the budget from the start, the same as any other part of the build.
What AI does with messy data
An automation that meets a record it was not written for either stops or sends it somewhere wrong, and somebody usually notices eventually. AI gives you a confident answer from whatever it is handed, so the same problems still happen, just faster and at a bigger scale, and with less chance of anyone seeing.
An everyday example: survey invitations
Every new CRM contact gets a survey automatically
A team wants every new contact in the CRM to get a survey automatically, with the responses written back to the contact record. The automation depends on the contact's email address, on each person existing once, and on the fields the survey will be filtered by later.
What the test turned up
- Two contacts with no email address
- The same person saved twice, once with a work email and once with a personal one
- Country written three different ways
What to fix first Before the build
- Make email a required field for new contacts
- Merge the duplicates and agree which email is the main one
- Turn country into a dropdown
How to measure it. The share of new contacts who get the survey without anyone stepping in.
Two projects where the data underneath was most of the job:
For an Australian nonprofit, contacts are created and surveys sent automatically, which saved 500 hours of manual work, removed a two-week wait for data and made the CRM the single source of truth.
Read the case study Case study 170,000+ clean records in under a weekA custom research platform for a US research organisation, covering about 9,000 US towns, with the data transformation built in so the records came out clean.
Read the case studyQuestions people ask
How do I know if my data is ready for automation?
Pull 30 real records at random from the system the automation will depend on and check each one for five red flags: data split across places, written different ways, duplicated, missing, or out of date. If all 30 are clean, the data can go straight into the build. If 21 to 29 are, fix the gaps as part of the build, and if 20 or fewer are, sort the data out before anything is built.
What are the most common data problems before an automation?
One customer saved three different ways, dates typed differently depending on who entered them, a status nobody has updated in months, and the one field the automation depends on left blank. They fall into five groups: data split across places, written different ways, duplicated, missing, and out of date.
How many records do I need to check?
Thirty. A sample's reliability depends mostly on how many records you check, and very little on what share of the total that is, so the same number works for a list of 80 contacts or a CRM of 120,000. Thirty records will catch a problem that affects one record in ten about 96% of the time, against 65% with ten records. If the system has fewer than 30 records, check all of them.
Can AI clean my data before I automate?
It can help. AI is good at spotting duplicates, blanks and inconsistent formats across a large export, and it can suggest fixes. Deciding which fixes are right, who owns them and how they will be handled once the system is live is still a person's job, and that thinking needs to go into the scope and the budget from the start.
What if 20 or fewer of my 30 records are clean?
Sort the data out first. Agree on formats, merge duplicates and name an owner before anything gets built. Building on the data as it is means the rules get written for how the data should look, records fail or go to the wrong place, people start fixing things by hand around the system, and then they stop trusting it.
More resources
A note from me
Run the test and want a second opinion?
Most of the projects I work on start with a small export, and it is usually where the most useful conversations happen. If you have run the test and would like a second opinion on what to do with the results, feel free to send me a message.