Where we start
The running situation is still Daybreak, the coffee subscription company, and the file is still the promotion data: rows that are customer-weeks either side of the autumn offer. The file is open, the question is written down, and the temptation is to calculate. Resist it for twenty minutes. Those twenty minutes are the highest-return time in the whole job, because the problems that quietly break every later number are visible here, plainly, in the rows, and they are invisible once you start aggregating. An average does not tell you it was computed over two populations. A total does not tell you a week was loaded twice. You only ever find out by looking first.
★ What you walk out with tonight. A written twenty-minute report on your own file: how many rows it has, what one row is, what is missing, what is duplicated, what is impossible, and one thing you did not expect. Six lines, kept beside the analysis, and the last line is the one that pays for the other five.
Where this sits on the shelf. Which chart to draw, and whether the chart is honest, is learn-dataviz-with-phoebe. Distributions, outlier tests and the formal treatment of missingness are learn-statistics-with-phoebe. Here the subject is the plain look that comes before either of those, done with nothing more than sorting and counting. In the standard process model for this work, CRISP-DM, published by a cross-industry consortium in August 2000, this is the second of six phases and it sits between understanding the business question and preparing the data. Its own description of the phase is to get familiar with the data, identify data quality problems and discover first insights, which is exactly tonight.
Five things to look at, in twenty minutes
6 min liveFive checks, four minutes each, in this order. None of them requires a tool you do not already have; a spreadsheet that can sort and count runs all five. What makes them worth doing is not that they are clever, it is that each one catches a specific defect that will otherwise travel silently into every number you produce for the rest of the project.
LiveThe five checks, and what each one saves you from4 min▶
How many rows. Say the number you expect out loud before you look, then count. An order of magnitude off means you are not looking at what you think you are looking at, usually because a join multiplied rows or a filter you forgot is still applied. Skip this and every total, every count and every denominator underneath every rate is computed on the wrong file.
What is one row. Confirm it, do not assume it. A row here is one customer-week, not one customer and not one order, and the difference decides what "average" even means. Get this wrong and the average order value you report is an average of something that is not an order.
What is missing. List the columns that have blanks and roughly what share of the rows are blank in each. Blanks in the outcome column change what you are measuring; blanks in a grouping column change who is being compared, which is worse and quieter.
What is duplicated. Check whether the thing that should be unique actually is. One week of orders loaded twice after a re-import inflates the count, the total, and the denominator of every rate in the same instant, and all three still look perfectly plausible.
What is impossible. Sort each column and read the extremes. Negative prices, delivery dates before order dates, subscription starts before the company existed, ages of 200. Each one is a small number of rows and each one sits in the tail, which is exactly the part of a distribution that any maximum, any median delivery time and any "worst case" figure is made of.
Self-studyClustered missingness is the dangerous kind4 min▶
Not all blanks are equal, and the share of blanks tells you almost nothing on its own. Blanks spread evenly across the file are an annoyance: you lose a bit of data, the rows you lose look like the rows you keep, and the shape of the answer survives.
Blanks concentrated in one group, one time period or one channel are a different thing entirely. They do not reduce your data, they remove a population from every later comparison, and nothing in the output says so. The answer you produce then describes only who remained, but it is phrased as though it described everybody.
The Daybreak-shaped version: suppose the channel column is blank for most of the earliest weeks, because channel tagging was switched on partway through the period. Now every before-and-after comparison split by channel is quietly comparing two different populations. The "before" side is whatever subset happened to be tagged, the "after" side is everybody, and the difference between them contains a real effect and a change in who is included, mixed together with no way to separate them after the fact.
So the check is not "how many blanks". The check is: sort by the blank column and look at what else those rows have in common. If the blanks share a date range, a channel, a region or a source system, write that down in capital letters, because it constrains every comparison you are about to make. The formal treatment of missingness, the named mechanisms and what you are allowed to do about each, belongs to learn-statistics-with-phoebe; what belongs here is noticing that the blanks have a pattern at all.
Averages hide, rows show
5 min liveHere is the same data twice. On the left, one number that is arithmetically correct and would survive any meeting. On the right, the rows it was computed from. Everything that is wrong with the file is on the right and none of it is on the left, and that asymmetry is permanent: you can always go from rows to a summary, and you can never go back.
| What you see in the summary | What the rows would have shown | The number it breaks |
|---|---|---|
| Average order value: GBP 28 | Two clusters, single bags and bulk boxes, with almost nothing in between | Any plan built on one typical order, because no order is typical |
| Orders this quarter: up 4 percent | A re-import that recorded one week of orders twice | Every count and total, and the denominator under every rate |
| Conversion by channel, before against after | Channel is blank for most of the earliest weeks | The comparison itself, which is now about two different populations |
| Median delivery time: 3 days | Rows where the delivery date sits before the order date | The tail, which is the part customers actually complain about |
LiveLook at the rows, then the summary, never the other way round4 min▶
A summary is a lossy compression of the rows. That is not a criticism, it is the entire point of a summary, and it is why they are useful. What matters is the consequence: you cannot tell from the summary what it lost. Twenty-eight pounds is compatible with a file where every order is twenty-eight pounds, and equally compatible with a file that is half nine-pound bags and half forty-seven-pound boxes with nothing in the middle. The number is identical. The business is not.
Three specific things hide inside a summary, and all three change the summary itself once you find them. Clusters: two populations averaged into a middle that describes neither, so the "typical customer" you plan around does not exist. Impossible values: a handful of negatives or absurd dates that drag a mean, and that live in the tail where maximums and percentiles are made. Duplicates: rows counted twice, which move the count, the total and the average all at once.
Remove any of the three and the summary moves. That is the proof that the summary was never independent of the defects; it was quietly made out of them. Which is why the order is rows first, summary second, and never the reverse. Looking at the summary first tells you what to expect, and once you expect something you will find it in the rows, and you will stop looking.
Self-studyWhat to write down while looking3 min▶
A short written record beats a remembered one, and it beats it by a wide margin, because what you remember three weeks later is the conclusion and not the caveats. Six lines is enough:
- Row count, and what you expected before you counted.
- What one row is, stated as a sentence, and how you confirmed it.
- Columns with blanks, roughly what share, and whether the blanks cluster.
- Duplicate check, what you checked for uniqueness and what came back.
- Anything impossible, how many rows, and which column.
- One surprise. Something you did not expect to see.
Keep it with the analysis, in the same folder or the same notebook, not in a message thread. This is the thing you will be asked for when somebody challenges a number three months later, and by then the file will have been refreshed, the blanks may have been backfilled, and you will have no way of reconstructing what the data looked like on the day you used it.
The most common version of being challenged is not an accusation, it is somebody rerunning your query six months on and getting a different number. Without the report you have no way to tell whether the business changed, the data changed, or you made a mistake. With it, that is usually a five-minute conversation.
When twenty minutes is enough, and when it is not
4 min liveThe five checks have exactly two outcomes and you need to have decided in advance what you do about each. If they pass, you proceed, and the report goes in the folder. If one fails, you have three honest replies available and one dishonest one, and the dishonest one is the default that everybody drifts into because it requires no decision at all.
LiveThree responses to a failed check, and all three are respectable4 min▶
Fix it. The duplicate week gets removed, the negative prices get corrected against the source system, the date typo gets repaired. This is the right answer when you know what the correct value is and can say where you got it. It is not the right answer when you are guessing, and the line between the two is whether you can name your source in one sentence.
Exclude it. Drop the rows, and state the exclusion as part of the result rather than as a footnote: "computed on 1,140 of 1,186 rows, excluding 46 with no channel recorded". An exclusion said out loud is a limitation. The same exclusion unsaid is a silent edit to the population, and the reader believes they are looking at everybody.
Stop. Sometimes the failed check is not a defect at all, it is the gap list from session 2 arriving late. If the column you need is blank for the half of the period you care about, no fix and no exclusion rescues it, and the honest output is a sentence saying what is missing and what would be needed. That sentence is worth more than a number computed on the half that survived.
What is not respectable is the fourth option, which is to proceed silently. It requires no decision, it costs nothing today, and it produces a number that carries a defect nobody downstream can see, including the version of you who quotes it in six months.
Self-studyRecording the fix is the part people skip3 min▶
Every fix is a decision, and every decision changes the answer. Removing forty-six blank-channel rows changes the denominator. Repairing nine impossible dates changes the median. Dropping a duplicated week changes the total and the growth rate computed from it. None of those changes announce themselves in the output, so the record of them is the only thing that makes the analysis reproducible by anybody, and the person most often defeated by its absence is you.
The format does not matter and the discipline does: one line per fix, in the same place as the twenty-minute report. What you changed, how many rows, and why. "Dropped week 11, duplicated by the 4 Oct re-import, 46 rows" is a complete entry and takes fifteen seconds to write.
The test of whether the record is good enough is simple. Hand the file and the record to a colleague and ask whether they could reproduce your number without asking you a question. If the answer is no, the missing piece is almost always a fix you made without noticing you were making it.
Twenty minutes on your own file
★ 22 min · the file you chose in session 2The file you brought, whatever tool it opens in. A spreadsheet is entirely sufficient for all five checks and there is no advantage tonight to anything more powerful. Four minutes each, in order, and write as you go rather than at the end.
Count the rows. Write down the number you expect first, then count. Note both, and note the gap. If you are an order of magnitude out, stop and find out why before doing anything else, because nothing after this point means anything until that is resolved.
Confirm one row. Write the sentence: "one row is one ___". Then prove it: find two rows that should be the same thing, the same customer in the same week, the same order, and check that they really are one row and not two. If they are two, your sentence is wrong and you have just saved yourself a week.
Find the blanks. Sort each column and the blanks collect at one end where you can count them. For every column with blanks, sort by that column and look at what else those rows share: a date range, a channel, a source system. A shared pattern goes in the report in capital letters.
Find the duplicates. Decide what should be unique, the order id, or the customer plus the week, then check that it is. In a spreadsheet, sort by that combination and look for neighbours that match, or count distinct values and compare with the row count. Equal means clean. Fewer means duplicates.
Find the impossible. Sort each column ascending and read the top twenty rows, then descending and read the top twenty again. Negatives where nothing can be negative, dates before the business existed or after today, quantities of zero, text in a number column. Four minutes finds almost all of it.
Write as you go, not at the end. The surprise you noticed in check two is gone by check five. A line written in the moment costs ten seconds; reconstructing it later costs the whole twenty minutes again.
Sorting is the whole technique. Sorting a column puts the blanks at one end and the impossible values at the other, which is why four of the five checks are just a sort and a look. You do not need a tool that does anything cleverer.
Say what one row is before you look at any column. Everything downstream, every average, every count, every rate, is defined relative to that sentence, and getting it wrong invalidates the lot silently.
Count distinct and compare with the row count. One number against another is the fastest duplicate check there is, and it works in every tool including the spreadsheet you already have open.
If a check fails, pick one of the three replies now. Fix, exclude or stop, and write the line before you carry on. Deciding later is how proceeding silently happens to careful people.
Keep the report with the file. Same folder, same notebook, not a message thread. Session 4 calculates on this data, and the first question anybody asks about a surprising number is what the data looked like.
The surprise line is the one that pays for the twenty minutes. The other five lines confirm things you mostly expected, and that has value, but it is quiet value. The surprise is where the twenty minutes returns something you could not have got any other way: a column that means something different from its name, a population you did not know was in the file, a period where the collection changed. Nobody has ever run these five checks on a real file and found nothing.
Before session 4
25 min- Finish the twenty-minute report on your file and keep it with the analysis, in the same folder, not in a chat window. Six lines, including the surprise, and one line per fix if you made any.
- Run the same five checks on a dataset somebody else prepared and note what you find. Data handed to you as finished is the data you are least likely to look at and most likely to trust, which is an unfortunate combination.
- Find one number in circulation in your organisation that would change if duplicates were removed, and find out whether anybody has ever checked. A dashboard tile fed by a re-imported table is the usual candidate.
- Bring a laptop, because session 4 is a bench and everybody drives. Bring one before-and-after figure from your own work as well, whatever it measures.
Three questions
Clear all three and session 3 gets stamped in your passport.
1 · Two files each have ten percent of their channel column blank. In one the blanks are scattered evenly; in the other they are concentrated in the first six weeks. Why is the second more dangerous?
Scattered blanks cost you data but leave the shape of the answer intact, because the rows you lose resemble the rows you keep. Clustered blanks remove a population, and nothing in the output says so. The check is therefore not how many blanks but what the blank rows have in common when you sort by them.
2 · Why look at the rows before the summary rather than the other way round?
The same average is compatible with a file where every value is the average and with a file that is two clusters and nothing in between; the number is identical and the business is not. Because removing any of the three hidden defects moves the summary, the summary was never independent of them. And reading the summary first tells you what to expect, after which you will find it and stop looking.
3 · A check fails: one week of orders appears twice after a re-import. Which responses are legitimate?
All three named responses are respectable and which one is right depends on whether you know the correct value, whether the rows can be dropped without changing who is being described, and whether the problem is a defect or a missing column. Proceeding silently requires no decision and costs nothing today, which is exactly why it is the default drift. It ships a defect that nobody downstream can see.
What this session teaches, and where it came from
Every fact on this page, and its verification tier, is recorded in the course's source map.
Session 3 cheat sheet · pin this
The data is now understood, which means it is finally safe to calculate something with it. Session 4 is a bench: one dataset, three questions people actually asked about the Daybreak promotion, and each one answered four different ways. Two of the three get answers. One of the three headlines turns out to be the exact opposite of the truth, and both numbers are arithmetically correct. Bring a laptop and a before-and-after figure of your own.