You ask for a dashboard and the first thing we do is not build a dashboard. We open your data, and we find what's always there: the same customer entered four ways, dates in three formats, and a column called "Status2" that nobody can explain. Then we tell you the thing every analytics shop tells every client eventually: before the charts, there's cleanup. This post explains why that's not padding the invoice, what the mess actually looks like, and how to fix it so it stays fixed.
What dirty data actually looks like
Not corrupted files or hacked systems. Just ordinary entropy from years of humans typing under deadline. The greatest hits:
- Duplicate customers. "Smith Plumbing," "Smith Plumbing LLC," "SMITH PLBG," and "Smith Plumbing (new)" are one company and four records. Your systems count four customers, split their order history four ways, and your "top 20 customers" report quietly demotes one of your actual top five.
- Three date formats in one column. 03/04/2025, 2025-03-04, and "March 4" in the same field, usually because the data passed through Excel, an export, and a copy-paste at some point. Is 03/04 March or April? Any tool that charts by date will either error out or, worse, guess.
- Mystery columns. "Status2," "Flag," "OLD_amount," a column of Xs. The person who added it left in 2022. Nobody dares delete it, nobody trusts it, and every report has to decide what to do about it.
- Free-text category fields. A "type" field that was never a dropdown, so it holds "Repair," "repair," "Repairs," "rpr," and "repair + parts." That's five categories to a computer and one to a human.
- Meaning drift. Until 2023, "Revenue" included shipping. After the new system, it doesn't. The column looks continuous. The trend line across the boundary is fiction.
None of this shows up day to day, because humans auto-correct while reading. Computers don't. Aggregation is where dirty data goes from invisible to load-bearing: every SUM and COUNT and GROUP BY takes the mess at face value.
Why cleaning is 80% of the work
Ask anyone who does analytics for a living and they'll give you roughly the same split: most of the hours in a data project go to acquiring, cleaning, and reshaping data, and a small slice goes to the analysis and charts everyone pictured when the project started. Our experience matches. On a typical dashboard build, the visuals take a day or two. Making the underlying data mean what it claims takes the rest.
Which is why we insist on doing it, and why the order can't flip. A dashboard on dirty data is worse than no dashboard, because it launders the mess: numbers on a chart look authoritative in a way that a messy spreadsheet never did. The first time the owner spots a number they know is wrong (and they always spot it, it's their business), trust in the whole thing dies, and it doesn't come back with a patch. Cleanup isn't a delay before the real project. It is the real project. The dashboard is the trophy on top.
How we systematize it so it stays clean
Here's the trap most cleanup efforts fall into: someone spends a heroic week fixing the spreadsheet, and six months later it's dirty again, because the faucet that produced the mess is still running. One-time cleanup is mopping with the tap open. The fix has three parts, and the first one is not cleaning.
Stop new mess at the door. Every dirty field traces back to an entry point with no guardrails. So: dropdowns instead of free-text for categories, required fields and date pickers on forms, duplicate detection at customer creation ("did you mean Smith Plumbing LLC?"). Most business systems support all of this and almost nobody configures it. An hour of validation settings prevents years of typing variance.
Make the cleanup a script, not a session. Instead of hand-fixing cells, we write the rules down as repeatable steps: in Power Query, in SQL, or in a small Python script, depending on the setup. Normalize the dates, apply the category mappings, merge the known duplicates, flag anything new that looks off. Because it's a script, it reruns on next month's data in seconds, and the cleaning logic is documented by existing instead of living in someone's head.
Give the leftovers an owner. Rules catch most things. The remainder (a genuinely new duplicate, a weird record only a human can judge) goes on an exceptions list that one named person reviews on a schedule, fifteen minutes a week. Data quality with nobody's name on it degrades. Same rule as dashboards.
How to know it's done right
Four checks. Counts survive scrutiny: when the owner asks "why does this say 340 customers," there's an answer, not an excuse. The mystery columns are gone or documented: every field in the reporting layer has a known meaning, and the "Status2"s are either explained or retired. Last month's data flowed through cleaning without a heroic manual effort. And the reports agree with each other, because they all draw from the same cleaned layer instead of five people's five exports.
If you're planning a dashboard, a forecast, or any project with the word "analytics" in it, budget for cleanup up front, because you'll pay for it either way: on purpose at the start, or with interest when the numbers turn out wrong. We'd rather find the four Smith Plumbings in week one. If you want to know how bad yours is, we can usually tell you after a day with the data, and the answer is almost always "fixable."
Stuck on this, or want it done for you? That's the job.
Email us →