How to Clean Up Your Business Data (Before You Automate Anything)

You clean up business data by repairing what corrupts it, not by scrubbing the report at month end. That is four fixes in a fixed order: name one system of record for each kind of record, kill the re-keying between tools that do not talk, fix the exports that mangle what they touch, and give every record an ID that survives its whole life. In any other order, you will clean the same file again next quarter.

Why do the numbers not tie out?

Because the same customer exists in two places under two identities, and nothing reconciles them except a person. It is almost never a formula problem.

At one residential field services company, the operations lead rebuilds the reporting pipeline about every six weeks. The CRM regenerates a record's ID when a lead becomes a job, so nobody could follow a customer from first call to paid invoice until he invented his own match key. Any marketing change snaps it: a renamed campaign, a new tracking number. Then he re-audits six weeks of history he can no longer trust.

The owner had no idea. From his chair, the dashboards simply appeared.

A report nobody trusts is not information, it is a rumor with a decimal point.

Two phrases run through these conversations: you cannot trust the data, and the numbers do not tie out. Both describe a business where the record has no single home.

Before you clean up business data, name the four ways it goes bad

Data does not rot on its own. Four mechanics break it, each with its own repair, which is why a general cleanup never holds.

None of the four is a data problem. Each is a process problem that leaves its damage in the data, which is why deleting duplicates on a Friday buys one quiet quarter.

Pick the system of record for every kind of record

A system of record is the one place a given kind of record officially lives, so that when two systems disagree there is no argument about which is right. You choose it record by record, not tool by tool.

1. List your record types. Customer, lead, job, employee, invoice, contract, schedule.
2. Name one home for each. Not the tool you paid the most for, the tool the work happens in.
3. Label every copy as a copy. Reports and shared sheets are downstream, and downstream is read-only.
4. Give each record type an owner. A named person, not a department.

Skip this and the system of record picks itself, badly. One company we mapped ran on twenty-plus group chat threads, and those threads held the real record of who worked which job and whether it closed. Two managers each re-read hundreds of messages a week to rebuild facts the business already owned, which is the working meaning of a single source of truth.

If two systems can both claim the same record, neither is the truth, and somebody on payroll is reconciling them by hand.

Kill the re-keying before you clean anything

Cleaning data while people are still retyping it is mopping the floor with the pipe still leaking. Fix the leak first: delete the second system, demote it to read-only, or connect the two, in that order of preference, because an integration fails silently.

The usual defense of double entry is that it takes a few minutes. One multi-store pet retail franchise typed animal records in by hand from breeder PDFs across sixteen locations, at roughly 50 staff hours a week. The worse cost is that every retype could produce a record that did not match the original, and no report will tell you which ones did.

Double entry is not a bad habit, it is a second system nobody agreed to buy.

Counting the stack itself is a separate exercise, worth running once you see what happens when a tool stack becomes the bottleneck. Never clean a field that is still being re-entered by hand.

Fix the exports that corrupt what they touch

Some of the worst data in a small business was clean until the software touched it. In one field services business, the export to a spreadsheet turned unique job codes into plain numbers. Not truncated: converted. So a second report existed to carry the codes, and a weekly merge to undo the export, one of the quieter reasons spreadsheets stop scaling.

When a departing bookkeeper walked her weekly commission process on a screen share, it took 85 minutes just to describe: two reports joined by lookup formula, chat screenshots to find the helper, receipts hunted through a login she did not have. Her owner, watching his own process for the first time in years, said quietly that this is three days every week.

1. Find every manual merge. A recurring step that joins two files is a repair job for something upstream.
2. Compare the export to the source. Ten records, field by field. Identifiers, leading zeros, and dates break first.
3. Repair it at the source. Change the export format, ask the vendor, or use the API instead of the download button.
4. Automate the repair as a last resort. Then write it down, so the fix does not live in one head.

The commission run was not slow because the plan was complicated. It was slow because of a download button.

Give every record one ID that survives its whole life

One identifier, created with the record, unchanged until the record dies. Carry it downstream, never reuse a retired one, and never let a stage change mint a new one.

That sounds obvious until you watch a CRM issue a fresh ID when a lead becomes a job. Now there are two records, joined only by a hand-built match key, and a key like that snaps every time marketing renames a campaign. If a record changes its identity when it changes stage, you do not have one record, you have two.

If your software insists on doing it, mint your own ID and write it into both systems as a plain field. Ugly, and it works. It is cheap to build in and painful to retrofit, so it belongs in what you hand a developer before a custom build, not in version two.

Who is allowed to create a record?

One role per record type, and any status change that money depends on needs evidence rather than an opinion. In one field services operation, card payments hit the CRM the second they clear. Cash and checks do not, because anyone can mark a job paid cash with nothing in the bank. Pollution runs the other way too: stray names on jobs, and jobs never flipped to done, so techs go unpaid.

1. Name one creator per record type. Everyone else requests.
2. Replace free text with a list wherever a wrong value costs money.
3. Require evidence for the status that pays people. A deposit, an invoice, a signature.
4. Give duplicates an owner and a weekly slot. Fifteen minutes a week beats a four-hour purge twice a year.

Rules like these are the raw material for automation, which is why documentation comes before automation. Software enforces the rules you give it and invents none of its own.

One owner took over payables for a few weeks after a key employee left and found 500 to 800 dollars leaking weekly through skipped audits and missed part deductions. His own math: up to 10,000 dollars a month, from a process nobody followed.

Clean data is part of what a buyer pays for

Profit sets the baseline for what a business sells for. Four things decide the multiple that profit gets sold at: transferable systems, low owner and key-person dependence, proprietary assets, and clean, trustworthy data. Three of those take years. The fourth is mostly the work above.

Diligence is a stranger trying to tie your numbers out with no institutional memory and no patience for a match key living in one employee's head. Reports that reconcile without a human interpreter are much of how an owned system raises your multiple.

When we map a client's process at The Systems Effect, we mark every point where a record gets created, copied, or renamed, because that map is the cleanup plan.

Do not start with the whole database. Pick the report your team argues about, take one number off it, and trace it back to the moment somebody created it. You will hit the first broken thing before the end.

Frequently Asked Questions

Why can you not trust your business reports?

Usually because the same real-world thing exists as two records in two systems and no rule says which is correct. A person reconciles them by hand, slightly differently each time. The report is not lying: it faithfully reports a record that was wrong when created.

What causes duplicate data entry?

Two systems that hold the same record and do not talk, plus a person paid to keep them in sync. It starts as a workaround and becomes permanent, because the workaround works. The tell is any recurring task where somebody exports from one tool and types into another.

What is a system of record?

A system of record is the single place a given type of record officially lives, so that when two systems disagree there is no argument about which wins. You pick one per record type, not one for the company: customers in the CRM, employees in payroll, jobs in the field app. Everything else is a copy, and copies are read-only.

How do you fix data quality problems in a small business?

Repair the four mechanics in order: name the system of record, stop the manual re-keying, fix the exports that corrupt identifiers, and give every record one ID that never changes. Then set the rule for who may create a record. Clean the existing file last, because everything before it decides whether the cleanup survives.

Does clean data affect what a business sells for?

Yes. Clean, trustworthy data is one of four things that decide the multiple a buyer applies to profit, alongside transferable systems, low owner and key-person dependence, and proprietary assets. A buyer who cannot reconcile your reports without an employee translating will discount the price or slow the deal.