Lesson Seven missions
Your sales total is 592 times too big and nothing warned you
A hands-on lesson about the data mistakes that do not throw errors. You get four small files from a fictional bakery chain, and you will get the wrong answer at least twice before you learn to catch it.
Before you start
The data you are working with
Thornbury Bakehouse runs 40 shops. Like most businesses, its information sits in separate systems that were never designed to talk to each other.
- thornbury_stores.csv40 rows · the shops
- thornbury_orders.csv547 rows · what was sold
- thornbury_campaigns.csv559 rows · marketing activity
- thornbury_feedback.csv1,346 rows · complaints and praise
All four share one column, store_id. That single column is what lets you connect them, and it is also what will break your numbers in Mission 1.
Here is one real row from the orders file:
Each mission gives you a goal and a task. Attempt the task before opening the reveal. Getting it wrong first is how the lesson sticks. You can work in a spreadsheet, in Python, in SQL, or in any dashboard tool. The traps are identical in all of them.
Mission 1
The number that was wrong
You want one table holding orders, marketing and feedback together, so you can ask questions that span all three. The obvious move is to join them on store_id.
Your task
Join all three files on store_id, then add up order_value.
Write down the number you get. Then write down what you expected total sales for a 40-shop bakery to be.
What you should have got
This is not a bug. It is arithmetic doing exactly what you asked.
Why it happens
A join matches every row on the left with every matching row on the right. If a shop has 18 orders, 96 marketing touches and 60 pieces of feedback, the join produces every possible combination of those three.
That one shop now accounts for about a third of the entire joined table. Every one of its 18 order values has been copied thousands of times, and your sum counts each copy.
Try it yourself
Move the sliders. This is one shop, and the join multiplies rather than adds.
1. Build each page or report from its own single file. Only the one or two charts that genuinely need two systems at once should use the joined table.
2. On joined data, count unique things, not rows. COUNT(DISTINCT order_id), never COUNT(*).
3. On joined data, divide each value by the number of copies it was split into, then add up.
Count your rows before the join and after it. If the number grew, every total you calculate is now suspect until you prove otherwise.
Mission 2
Trust the file, not the description
Every dataset arrives with a description of what it contains. Here is what Thornbury's says about the feedback file:
Your task
Test all four of those claims against the actual file.
Count the unique IDs. Count the blanks in each column. Check whether store_tier is the same for every row belonging to one shop.
What you should have found
| The description says | The file actually has |
|---|---|
| feedback_id is unique | 1,339 unique across 1,346 rows. Seven repeat |
| sentiment has no blanks | 41 blanks |
| Implies the blanks line up | Zero overlap. 43 rows missing a resolved time, a different 41 missing sentiment. 84 rows affected, not 43 |
| store_tier is the shop's size | It changes row by row. All 40 shops carry more than one tier across their own feedback |
That last one is the most dangerous. If you filter to "Flagship shops" using this column you will get a different set of shops every time, because the column does not describe the shop at all.
Duplicate IDs mean you cannot use that column to remove duplicates.
Blanks that do not overlap mean your data is twice as incomplete as you thought.
A column that varies per row cannot be used as a filter for the thing it appears to describe.
Documentation is a claim, not a fact. Spend ten minutes testing it. The errors you find are findings in their own right, and they change the calculations you are about to build.
Mission 3
Two settings that silently destroy a finding
Your task
Work out the average hours to resolve, by priority. Then work out the average order value.
Load raised_at and resolved_at, subtract one from the other, and group by priority. Separately, average the order_value column.
Trap one: the disappearing clock
The timestamps look like 2024-08-15 14:23:07. If your tool reads that column as a date rather than a date and time, the time is thrown away and every timestamp becomes midnight.
Your resolution times become whole numbers of days, and the finding in this data vanishes completely. Nothing errors.
Check: after loading, look at one value. If you cannot see the time, fix the column type before going further.
Trap two: the zero that is not a zero
100 of the 547 orders were refunded, and refunded orders are stored with order_value of 0.00. That zero does not mean "this order was worth nothing". It means "this order does not count".
Nothing errors, and £48.09 looks like a perfectly reasonable average order value for a bakery. That is what makes it dangerous.
The finding you were looking for
Once the clock survives, here is what the data says about resolution times:
Urgent feedback is handled barely faster than the lowest priority. The priority field is not changing what happens. That is a real operational problem, and it is only visible because the hours survived.
Instead of remembering to exclude refunds every time, build a field that has the rule inside it:SUM(CASE WHEN order_status = 'Completed' THEN order_value ELSE 0 END)
Now you cannot forget.
Mission 4
Pick the chart that answers the question
Your task
For each channel, calculate the response rate and the total spend. Decide what chart to use.
What the data says
| Channel | Response rate | Total spend | Sales per touch |
|---|---|---|---|
| Loyalty App | 51.7% | £3,770 | £77 |
| 28.0% | £2,442 | £44 | |
| SMS | 26.7% | £4,322 | £40 |
| Local Radio | 8.5% | £32,239 | £12 |
| Poster | 8.5% | £21,027 | £14 |
The finding writes itself. The two worst-performing channels take £53,266 of the £63,800 budget. The best channel takes £3,770.
Choosing the form
| The question | The chart | Why |
|---|---|---|
| How do channels compare? | Bar, sorted by value | Length is the easiest thing for an eye to compare accurately |
| How has this changed over months? | Line | Slope reads as direction without effort |
| One headline number? | Big number | A chart of one value is just a decorated number |
| Exact figures per row? | Table | When people need to read values, let them read values |
| Spend and response together? | Two charts | Never two scales on one chart. It makes any relationship you want look true |
Putting two different measures on one chart with two different vertical scales. You can slide either scale until the two lines appear to move together, which means the chart proves nothing and suggests everything. Use two charts side by side.
Cover the title. Can somebody who has never seen it still say what it shows? If not, it is decoration.
Mission 5
When the assistant is confidently wrong
Most tools now let you ask questions in plain English. This is genuinely useful, and it fails in patterns worth knowing by name.
Pattern 1 · Right number, invented reason
You ask how many feedback records are missing a sentiment. It answers 41, which is correct. Then it adds, helpfully: "this is likely the same set as the 43 records with no resolution time."
The two sets have zero overlap. 84 rows are affected, not 43. The count was right. The explanation attached to it was invented.
Catch it: ask about the relationship directly. It will usually compute it correctly when asked. It just did not volunteer to check.
Pattern 2 · Quietly using the wrong table
You ask for average order value. It answers £48.09. Your own report says £58.85.
It included refunds. You excluded them. Neither of you said which.
Catch it: when an answer disagrees with your own numbers, recalculate it the other way. If it matches, you have found the difference, and it is usually a filter.
Pattern 3 · The out-of-date citation
You ask about an industry benchmark. It gives you a real figure from a real organisation, correctly attributed. The figure was replaced last quarter by that same organisation.
This is a staleness problem, not a lying problem, so every rule about "do not invent figures" misses it completely.
Catch it: for anything forward-looking, check the publisher's most recent release yourself.
Pattern 4 · Answering a different question
You ask for "average sales for shops with more than 20 pieces of feedback". It can read that two ways: average per shop, or average per order. It picks one and does not say which.
They give very different numbers and can support opposite conclusions.
Catch it: name the denominator in your question. "Average per order" and "average per shop" are different questions.
Your task
Ask your own assistant three questions about this data where you already know the answer.
Then ask three where you do not, and verify each one against the file. Note which pattern each failure matches.
An assistant that says "that is not in this data" is more useful than one that produces something plausible. If yours never says it cannot answer, that is a warning sign rather than a feature.
Mission 6
Checking, when you cannot check everything
You have five claims to put in a report and time to verify two. Which two?
| # | Claim | Where it came from |
|---|---|---|
| A | Total sales were £26,305 | Your own calculation from the orders file |
| B | Loyalty App converts at 51.7% | Your own calculation from the campaigns file |
| C | Marketing spend was £63,800, more than total sales | Your own calculation, but the comparison is odd |
| D | Industry average bakery margin is 9% | An AI assistant quoted a trade body |
| E | Urgent feedback resolves in 49.4 hours | Your own calculation, depends on the timestamp fix |
Your task
Choose two to verify. Write down why you picked those and not the others.
A reasonable answer
Check C and D.
C fails a sanity test. The company apparently spent £63,800 on marketing to produce £26,305 of sales. That is possible but unlikely, so something is probably being compared that should not be. Perhaps the spend covers a longer period, or counts things the sales figure excludes. Either way, publishing it as a return-on-investment number would be wrong.
D is the only claim you cannot recalculate. A, B and E all come from a file on your own machine. If you doubt them, you re-run the calculation. D comes from outside and was relayed by a tool, so it carries two risks: the figure may be misquoted, and it may be out of date.
The principle: spend your checking time on claims you cannot cheaply recompute, and on any number that fails a common-sense test. Figures from your own files are the cheapest to verify and the least likely to be wrong.
Nobody expects you to verify everything. A report that says "internal figures recalculated from source, external figures spot-checked on the two that carry the most weight" is more trustworthy than one that says nothing, because it tells the reader where the risk sits.
Mission 7
Writing it for someone who decides
The person reading your work does not want your process. They want to know what is happening, whether to believe it, and what to do.
The four parts of any finding
Your task
Write up the marketing channel finding from Mission 4 using all four parts.
Keep it under 100 words. No jargon. Someone who has never seen your data should understand it.
One way to write it
We are spending the most on the two channels that work least.
Local radio and posters take £53,266 of our £63,800 marketing budget and get an 8.5% response. The loyalty app takes £3,770 and gets 51.7%.
Move a quarter of the radio budget into the loyalty app for one quarter and measure the response. Cap it as a test rather than moving everything, because the app currently reaches fewer people and its rate may drop at larger volume.
Confidence: medium to high. The response rates come from our own records and are reliable. What we do not know is whether people who respond on the app would have bought anyway.
A report that is entirely bad news reads as an attack, and people argue with it instead of acting on it. One genuine positive makes the criticism credible.
Name two things a reader might wrongly take away. For this data: do not conclude the loyalty app causes higher spending, because app users may simply be the regulars already. Naming the limit is what makes the rest trustworthy.
Recap
Everything in one table
All of these produce a wrong answer with no error message.
| The trap | What you see | How to catch it |
|---|---|---|
| Join multiplication | Totals hundreds of times too big | Count rows before and after. Recheck one known total |
| Date without time | Durations become whole days | Look at one value after loading |
| Zero meaning "not applicable" | Averages quietly drop | Know which columns use 0 as a placeholder |
| Duplicate "unique" IDs | Deduplication removes real records | Count distinct against total |
| Column that varies per row | Filters return different sets each time | Check it is constant within each entity |
| AI invents a reason | Right number, made-up explanation | Ask about the relationship directly |
| AI uses a different filter | Answers disagree with your own | Recalculate the other way and compare |
| Out-of-date citation | A real source, quoted after it changed | Check the publisher's latest release |
| Numbers that do not compare | A ratio that seems impossible | Check a total cannot exceed what it came from |
Check yourself
Your join turns 547 rows into 320,852. Is something broken?
No. That is normal when each shop has many rows in each file. What is broken is any total you calculate on it without correcting first. Verify by recomputing a number you already know.
Average resolution time comes back as 2. What do you check first?
The column type on your timestamp. If it loaded as a date rather than a date and time, every duration is now a whole number of days.
Why did the average order value drop by 18% without any error?
Because 100 refunded orders are stored as £0. They are not sales worth nothing, they are not sales at all. Filter them out, or build the filter into the calculation.
The assistant gives a correct number then explains it. Trust the explanation?
Less than the number. Treat anything it volunteers without being asked as unverified. Ask about the relationship directly and it will usually compute it properly.
If you remember one thing
Before any number goes into a document or a meeting, ask where it came from and whether you checked it. Every mistake in this lesson looked completely normal on screen.
Thornbury Bakehouse is invented. The four data files are generated and contain deliberately planted problems, so each mission has something real to find. The traps themselves are real and occur in every analysis tool, from spreadsheets to SQL to dashboards.