A revenue figure I quoted to somebody last year was about a third too high.
The file was fine. The arithmetic was fine. I had joined a customer table onto an invoice table, which quietly turned one row per customer into one row per customer per invoice, and then summed a monthly fee column that now repeated once per invoice. Anyone with three invoices got counted three times.
Nothing in the file recorded that this had happened. A join does not leave a note behind, and the customer columns sit there afterwards looking exactly as they did before.
That is the shape of the whole problem. You almost never lose the data. The file opens, the columns are full, the row count is what it always was. What you lose is the meaning, and the meaning was never inside the file. It was in your head, and your head has moved on.
What follows is the six lines I now write in a plain text file next to any dataset I might want again. It takes about ten minutes.
Line one: what a single row is
Ask what one row stands for before you ask anything else. It sounds too simple to be worth saying out loud, which is exactly why it gets skipped.
I rent out android phones by the day. An export from the device panel could plausibly be one row per phone, one row per phone per day, one row per rental, or one row per rental per invoice line. All four are reasonable designs, all four produce different totals from the same business, and the column headers do nothing to tell them apart.
The answer surprises people more often than it should, and the usual culprits are joins and group by clauses. Both change what a row means halfway through your work without announcing it. After a group by, the columns you did not aggregate sit there still populated, now describing a thing that no longer exists.
So write the sentence out. One row is one phone on one day. If you cannot finish that sentence, you do not understand the file well enough to analyse it, never mind archive it.
Line two: which system, and the day you pressed the button
Name the source, and date the retrieval.
The date does more work than people expect. A usage export taken on the third of the month and the same export taken on the thirtieth are different objects, because in one the month has not finished. Put them side by side a year later and a carrier appears to have fallen off a cliff.
Sources also drift under you without saying anything. A panel adds a column, renames one, or starts including cancelled lines in its default view after an update nobody mentioned. When the note says which panel and which day, you can reconstruct what changed. Without it you have two files that disagree and no way to referee them.
Write the filter down here too, because the export will not remember it. Singtel only. Active only. Last 90 days. I have burned twenty minutes on a discrepancy between two files that turned out to be a carrier filter left set from an hour earlier.
Line three: a sentence for every column
One sentence each, in plain words. This is the tedious part and it is the part that pays.
The columns that need it most are the ones you would swear are self explanatory:
- Identifier columns. Is that ID mine or theirs? Two systems will both call it customer id, they will not mean the same thing, and one of them changes when the customer edits his email address.
- Anything holding a code. Status, type, tier, state. List every value it can take and what each one means. Include the empty one, especially the empty one.
- Dates. Is this when the thing happened, when it was recorded, or when the row was last touched? Those are three different columns in most systems and they get exported with names that do nothing to distinguish them.
Line four: what is not in the file
Almost nobody writes this one down, and it is the line that saves you from confidently reading a file backwards.
Every dataset is a survivor. Something upstream decided which rows got in: a default date window, a permission you did not have, a page of results you never scrolled to, a filter you set and forgot.
I exclude my own test lines from my usage numbers. Sensible, and invisible afterwards, because no row in the output says two lines were removed. Twelve months on it looks like a file containing everything.
So name the exclusions in plain words. Test accounts removed. Refunded orders removed. Anything before the migration removed. None of it can be inferred from what is in front of the reader, and the reader is you.
Line five: what the numbers are actually in
Data volume is easy to get wrong because panels disagree about what a gigabyte is. One counts in a thousand megabytes, another in 1024, and a third reports a billing cycle rather than a calendar month while labelling the column the same way.
Money needs three facts and usually carries none of them: the currency, whether tax is already inside the number, and whether refunds have been taken out. A price column with no currency attached is a number shaped hole.
Clocks have cost me more hours than either. My servers log in UTC and I live eight hours ahead, so a day boundary in the raw file is not a day boundary in my life and eight hours of activity gets filed under the wrong date. A daily series that is eight hours out does not look broken. It looks like a habit your customers have.
Line six: whose definition of the word
This is the one that quietly ruins year on year comparisons.
Take month. It can mean a calendar month, a billing cycle that starts on whatever day the customer signed up, or a rolling window of 30 days. Three defensible answers, three different numbers, one word.
Or churn. Cancellations this month over the customers you had at the start, over the customers you had at the end, or over only those actually up for renewal. All three appear in real dashboards, they disagree with each other, and none of them is wrong.
So the note does not just say what the word means. It says whose word it is. Active, as the billing system defines it. Four extra words, and they are the difference between comparing two things and comparing two things you assumed were the same thing.
The filename is not the place for opinions
You know the folder. One file called final. One called final 2. One called final real. One called use this one.
The rule that fixed it for me is narrow: never encode a judgement in a filename. Encode the date and what the thing is, year first, then month, then day, so the folder sorts itself into chronological order without being asked. Good is an opinion you held on a Wednesday, and by March it is an opinion with no argument attached to it.
One more that people get backwards. A file you derived from an export should carry the date of the export rather than the date you built it. Otherwise three files stamped this week were all made from data three months old, and nothing in the names says so.
A script cannot drift from what you actually did
Whatever you did to get from the raw export to the analysed file should be a script rather than a sequence of things you did with your hands.
A script is the only documentation that cannot drift away from what happened, because it is what happened. Every other kind is a description written afterwards by somebody with an interest in believing he was careful.
Think about what a manual pass leaves behind. You sorted. You deleted the rows that looked wrong. You dragged a formula down and it stopped two rows short of the bottom. None of that leaves a trace, and the file looks identical whether you were careful or whether it was late.
The test is repeatability. Delete the output, run the thing again, and see whether the same file comes back. If it does, your steps are documented whether or not you ever wrote them up.
I still hand edit two spreadsheets. I know they are the weak point and I have not fixed them.
The file I can no longer read
I have a usage export from fourteen months ago. One row per port, a gigabyte figure, a date, and a column called status holding three values: live, ok, and blank. I used it to decide that a batch of lines was not earning its keep, and I retired them.
I can guess that live meant in service. I cannot recover what blank meant. It might mean the port was free. It might mean the export did not know, because the panel had lost contact with the modem that morning. Those two readings push the decision in opposite directions: one says the lines were idle and I was right to pull them, the other says my measurement was broken on the day I happened to look.
The money is the small part. It was one batch and the call was probably fine. What I actually lost is the comparison. The number I most want this year is that same number from last year, it is sitting right there on the disk, and I cannot use it.
There is no note beside that file because at the time it was obvious. That is the trap in one sentence. The moment you understand a dataset perfectly is the exact moment writing it down feels like a waste of an afternoon.
Why almost nobody does this
Here is the honest limit. This is unpaid work today in exchange for a payoff on a date you cannot name. It might be six weeks away. It might never arrive. Everything about running a one person business points the other way: you do the thing in front of you, and last year’s file is not in front of you.
So I do not do it for everything. Roughly one file in ten. The raw exports I take on a schedule, any number I quote to another person, anything feeding a decision I know I will want to revisit.
And none of this makes the data correct. A beautifully documented wrong number is still a wrong number. What the note buys you is the ability to find out that it was wrong, which is more than you have today.
More plain English walkthroughs on data quality and the numbers a one person business actually runs on are at Data Research Analysis Collection.
Get new guides and videos first — join the Telegram channel.