← Back to blog

Data Hygiene: What It Means and How to Test Yours

data hygieneprocess hygienedata qualitydata cleansingchargebackscampaign attributionrebate payouts

In January 2023 a retailer took $1,428.18 off a payment to Cinderhaven Provisions, a specialty food brand, as a label fine. Cinderhaven is a fictional company, and its figures come from a synthetic dataset I built to show cost patterns that are real. The product's barcode number ended in a check digit that does not compute, so the number on file was not a valid barcode number. Over the next three years the same fine arrived 138 more times, from all six of Cinderhaven's retailers, and every one landed on the same twelve products. The numbers on file are still wrong.

I have worked in operational data for about 25 years, and this is the pattern I see most. Nothing breaks and no system goes down. A record is wrong, the cost turns up later as a fine or a short payment, the fine gets paid, and the record stays wrong. It is not peculiar to food. The same thing happens to advertising budgets and to rebate payments, and both appear below. The condition underneath has a name, and the name is data hygiene.

What data hygiene means

Data hygiene is the state of your records at rest. Set aside, for a moment, how the records got there and who touches them next. Open the tables as they sit today and ask whether they can be trusted. That question breaks into four tests, and they are the same four whatever the data is.

  1. Fields mean what they say. A column called ship date holds the day the truck left, on every row, and not the day the order was keyed on some of them.
  2. Systems agree. When the same fact lives in two places, both places hold the same value.
  3. Transactions reconcile. What was ordered ties to what shipped, what was invoiced ties to what was paid, and any difference has an explanation.
  4. Records carry their own provenance. A row can tell you where it came from, when it last changed, and who or what changed it.

None of these needs special software. Each can be run with a query or a spreadsheet and some patience. What they do need is someone willing to compare things that are usually looked at one at a time.

Here is what failing them looks like in the three kinds of data I work with. Each example uses a fictional company and a synthetic dataset I built for the purpose, so the figures can be published and checked.

In CPG: one product record, six sets of rules

Cinderhaven Provisions is a specialty food brand with 50 SKUs across five product lines, selling to six contracted retailers. It keeps one product master, and each retailer checks that record against its own list of required fields. One retailer requires case dimensions and does not ask for a country of origin. Another asks for the reverse. The same record can be good enough for one customer and deficient for the next.

When I audited the master, the defects were plain to see. Twelve of the 50 SKUs carry a GTIN-14 and a UPC whose check digits don't compute, which means the barcode number on file is not a valid barcode number. Nineteen are missing at least one case dimension. Three have no case weight, and one has no country of origin. None of this stopped an order. Pass rates on the retailers' required-field validation ran from 46% to 76%, and the products shipped anyway.

The cost arrived later, as chargebacks. Over 37 months, 275 of them trace back to one of those five fields. The 139 label fines from the opening are the barcode ones, about $132,000 in all. The rest trace to the blank dimensions, weights and country of origin. Annualized, the 275 come to about $93,000 a year, against an estimated 15 hours of data entry to correct the records. These are the first and third tests failing: fields that don't hold what they claim to hold, and charges that nobody tied back to their cause. The Product Data Health Audit walks through the full example.

In campaign analytics: the platforms and the analytics disagree

Quellwood Provisions, the fictional brand in my campaign dataset, ran 12 paid campaigns in one quarter and spent $72,621 on them. Ask what that spend earned and the answer depends on where you look. The ad platforms reported $255,586 of revenue for those campaigns. The analytics credited the same campaigns with $213,082 on a last-click basis, which is $42,504 less. Some of that gap is by design, since each platform counts a sale its own way. The rest is not. The platforms billed for 105,742 clicks, and the analytics could match only 98,965 sessions to them. Nor does the analytics agree with itself: its raw export holds 5,708 purchase events for 5,556 orders, because 152 purchases were sent twice. The gaps are made of tracking defects.

A link goes out without UTM tags, so an email click lands in the analytics as direct traffic and a paid-social click is counted as organic social. A browser expires its cookie before the shopper comes back, so a returning customer looks like a new one and the ad that first brought them in loses the credit. A checkout hands the shopper to a payment page and back, and the sale is recorded as a referral from the payment processor's domain. The Quellwood dataset has nine defect classes of this kind seeded into it.

Add them up and $5,064 of the $72,621 paid for clicks that never show up as a session in the analytics. A further 694 of the 5,556 orders reach no campaign and no traceable source. Most of those arrived as direct visits, and about a quarter carry one of the tracking defects. The uncertainty spreads from there. Nine of the 12 campaigns change rank by two or more places depending on which attribution rule is applied, and one campaign's return on spend runs anywhere from 0.69x to 1.88x. Whoever moves budget on those rankings is partly responding to the tracking defects. This is the second and fourth tests failing: systems that don't agree about the same campaigns, and sessions that have lost the record of where they came from. Campaign Math shows the whole quarter.

In rebates: a wrong field becomes a wrong payment

Galewick Climate Systems is a fictional HVAC manufacturer. It pays contractors a bonus for each installation and pays distributors a rebate on volume, both under written rules: tiers, claim windows, eligible products, and one bonus per serial number. Two sets of records decide who gets paid. The rulebook and the enrollment records say what qualifies. The claims file and the rebate statements say what was approved and paid. When the two disagree, money moves, because in a rebate program a field is an instruction to pay.

In one program year Galewick approved $521,804 in payouts. Of that, $17,597 was paid against the program's own rules. A further $7,336 was earned under those same rules and never paid. I keep the two figures separate and never net them, because one is money the sponsor should not have spent and the other is money a contractor or distributor is still owed.

Behind those totals sit 129 wrong decisions, each of which can be re-derived by hand from the rulebook. Most of them, 61.2%, started with something a member submitted, and those errors averaged $98. The sponsor's own calculation errors averaged $344. I have worked in incentive fulfillment since 2007, and that split is the one I would expect: members make many small mistakes, and the sponsor's own math makes fewer, larger ones. It can be drawn only because each claim record keeps what was submitted beside what was decided: the model claimed, the model on the invoice, the date received, the amount paid. Here the fourth test passes, and it is what makes the failure fixable. The failure is the third test: payments that don't reconcile to the rules that were supposed to produce them. Payout Math has the claim-level detail.

The four tests, side by side

Test Where it failed What it cost
Fields mean what they say Cinderhaven: 12 SKUs whose barcodes fail their own check digit, 19 SKUs with a blank case dimension About $93,000 a year in chargebacks
Systems agree Quellwood: the ad platforms report $255,586 for campaigns the analytics credits with $213,082 A $42,504 gap in reported revenue
Transactions reconcile Galewick: payouts checked against the program rules $17,597 paid in error, $7,336 owed and unpaid
Records carry their own provenance Quellwood: paid clicks that arrive with no trail $5,064 of spend that cannot be traced

Galewick passes the fourth test, which is why its 129 wrong decisions can each be found and corrected.

What data hygiene is not

It is not a cleanup project. A cleanup fixes the records you have today. Hygiene is the condition you measure, and you can measure it again next quarter to see whether it held.

It is not another word for data quality or data cleansing, though the three are often used as if they were. Data quality is the broad discipline: every way of judging whether data is fit for its use, including whether it arrives on time and whether it covers what you need. Data cleansing is an activity, the work of correcting records. Data hygiene, as I use the term, is narrower than the first and is not an activity at all. It is the condition of the records you already hold, judged by the four tests.

It is not a BI tool. A dashboard displays whatever it is fed. Each of Quellwood's systems was doing what it was built to do, and they still disagreed.

It is not the same as process hygiene. Data hygiene is about the records. Process hygiene is about the routines that create, move and change them, and it is the reason clean records drift. I have written about that side in Process Hygiene: Why Clean Data Never Stays Clean, so I will leave it there.

How to check your own

Pick one dataset. The best choice is the one behind the number people argue about. Then run the four tests in order.

  1. Fields. Choose the five fields that money depends on, write down what each is supposed to hold, and test every row against it. Cinderhaven's version was a check-digit calculation on each barcode. Twelve SKUs failed arithmetic that nobody had run.
  2. Systems. Count the same thing in two systems. Quellwood's ad platforms billed for 105,742 clicks and its analytics matched 98,965 sessions to them. A count is enough to show the gap before anyone argues about revenue.
  3. Transactions. Take a sample of payments and re-derive each one from the written rule that should have produced it, the way Galewick's claims are checked against its rulebook. Do the same for deductions taken against you, and trace each one to the field that caused it.
  4. Provenance. Pull ten rows and try to say where each came from and when it last changed. If you can't, you have found Quellwood's problem: money spent on something the record can no longer explain.

Whatever fails, put a dollar figure next to it if you can. The dollars decide what to fix first.

If you would rather hand the job to someone else, that is what the Data Health Snapshot is for. You send one dataset, and seven business days later I send back your three most expensive data problems, stated in dollars, with a fix plan. The details are on the Snapshot page. The four tests are the same either way, and an afternoon with them will tell you a good deal on your own.

Or run the tests yourself and tell me which one failed. I'll write back with where I would look first. No call required.

Where to look next

Next step

Get the number for your own data. Seven business days.

Send the export you trust least; get back your three most expensive data problems, in dollars, with a fix plan. Or start with the written intake: a few minutes by form, no call.

Private, expiring upload, never email. Mutual NDA before anything moves. Files destroyed within 30 days of delivery, with a certificate. Methods published, tools open source.