Ninety days of sessions, conversions and spend by channel and device. The head of marketing wants to cut the worst performing channel. It is 810 rows, you can rank them in one line, and the ranking will be arithmetically correct and point at the wrong channel.
That is what makes this file worth an hour. Nothing here is dirty in the way the ecommerce file is dirty. There are no broken encodings and no unparseable numbers. Every fault is a way of being wrong while doing the arithmetic perfectly.
If you would rather try it first, the download includes a brief with the task and nothing else.
What you are given
Eight columns: session date, spend date, channel, device, sessions, conversions, spend, and the currency the spend is in. Two devices, ninety days, and six values in the channel column. Only three of those six are channels.
1. Three entries that are not channels
Before any analysis, look at the distinct values of the column you are about to group by.
| Channel | Sessions | Conversion rate |
|---|---|---|
| fellrun.co.uk | 12,903 | 21.01% |
| checkout.stripe.com | 12,164 | 20.96% |
| paypal.com | 12,082 | 21.16% |
| the three real channels | 379,721 | 4.09% |
Your own domain, Stripe and PayPal are not traffic sources. These are sessions where somebody left for a payment gateway and came back, and the return was logged as a new visit from a new source. They convert at five times everything else because they are mostly people who had already decided to buy.
Leave them in and the payment gateway is your best performing channel, and somebody will ask how to buy more of it. The conversions those sessions claim were stolen from whichever channel actually earned them.
The fix is referral exclusions in the analytics configuration, and filtering them out of any historical analysis you run in the meantime.
2. Simpson’s paradox
Now rank the three real channels.
| Channel | Overall conversion rate |
|---|---|
| 4.76% | |
| organic | 3.99% |
| paid_social | 3.51% |
Paid social is worst. Cut it. Now split the same numbers by device.
| Channel | Desktop | Mobile |
|---|---|---|
| paid_social | 6.06% | 2.97% |
| organic | 5.31% | 3.04% |
| 5.59% | 2.39% |
Paid social is top of the desktop table and second on mobile by seven hundredths of a point. It beats email on both devices and loses to it overall. What it is not, on either device, is worst.
The reason is the mix. Paid social is 82% mobile. Email is 26% mobile. Mobile converts worse for everyone, so paid social is being judged on the device split its audience happens to sit in rather than on what it does with that audience.
real = df[df["channel"].isin(["paid_social", "email", "organic"])]g = real.groupby("channel")[["sessions", "conversions"]].sum()g["cr"] = g.conversions / g.sessions * 100d = real.groupby(["channel", "device"])[["sessions", "conversions"]].sum()d["cr"] = d.conversions / d.sessions * 100
The habit is short enough to remember. Before comparing two groups on a rate, check whether they have the same mix on anything that affects that rate. If they do not, the overall comparison is a mix comparison wearing a performance costume.
The fix is to compare within device, or to standardise both channels to a common device mix. What you never do is cut a channel on an unadjusted overall rate.
3. Two currencies in one spend column
Paid social is billed in euros. Everything else is in pounds. There is no rate anywhere in the file.
This is where the dataset stops being a puzzle and starts being unfair, because it pushes the same channel in the same direction a second time.
| Channel | Spend | Conversions | Cost per acquisition |
|---|---|---|---|
| £44,530 | 6,025 | £7.39 | |
| organic | £45,283 | 5,005 | £9.05 |
| paid_social | €45,646 | 4,488 | 10.17 |
Read that table without noticing the currency column and paid social is worst on cost too, which appears to confirm the conversion rate ranking. Convert the euros at 0.85 and paid social comes in at £8.65, ahead of organic.
Two independent faults, both landing on one channel, both pushing the same way. That is how a wrong answer survives a second look: the confirmation felt like evidence.
You cannot answer the cost per acquisition question properly from this file. You need a rate table and a decision about whether to use the daily rate or a fixed one, and you should say which you used.
4. Spend and sessions are a day apart
The spend date differs from the session date on 180 rows, always by exactly one day.
It is tempting to call this an ad platform timezone. The shift hits all three real channels equally, sixty rows and a third of the days each, including organic, which has no ad platform. So it is the export or the join that built the file, not any one source.
Join spend to sessions on the date and a third of your spend lands on the wrong day. Daily cost per acquisition turns into noise. The monthly total reconciles perfectly, which is exactly why nobody notices.
What to tell the head of marketing
Do not cut paid social. It is not the worst channel on either device, it beats email on both, and the ranking that said otherwise was measuring the device mix of its audience.
Three of the six values in the channel column are payment gateways and need excluding before any ranking is run. Cost per acquisition cannot be compared at all until the euro spend is converted, and once it is, paid social moves ahead of organic on that metric too.
What this dataset teaches
None of these four faults produce an error, a null, or an outlier. A data quality tool passes this file. Every one of them is caught by the same unglamorous step: look at the distinct values of every column you are about to group by, and check whether the groups you are comparing are alike in the ways that matter before you compare them on a rate.
The most useful thing here is the coincidence. Two separate faults happened to push the same channel in the same direction, and that agreement is what would have made the recommendation feel safe. When two independent numbers agree, check whether they are actually independent.
Have fun.
