Revenue
The source has one amount column. Summing it gives a number that looks like revenue and is not, because the same column carries cancellations, postage, bank fees and bad-debt adjustments.
The source has one amount column and one quantity column. Summing them gives a number that looks like revenue but isn't, because the same two columns also carry cancellations, postage, bank fees and bad-debt adjustments.
All seventeen combinations in the extract, not just the common few. The rare rows are the ones that cause trouble:
Three of those distinctions change an answer materially:
- Postage sits in the same column as the goods. £433,411 of it, so any per-unit or per-product figure that includes those rows is partly measuring delivery charges.
- There is a third invoice prefix. Six
Arows worth -£147,614 record bad-debt adjustments, and they hold the only negative prices in the file. Descriptions of this dataset generally mention invoices and cancellations only, which leaves these quietly folded into the totals. - Negative quantities are not all returns. 3,393 lines carry a negative quantity on an ordinary sale invoice. All of them are priced at zero and have no customer attached, and the descriptions give them away as inventory write-offs: damage, stock counts, one row labelled
check. Treating them as returns would raise the return count by about a fifth while leaving the returned value unchanged, so the money would still reconcile and the error would survive review.
So what. These are not edge cases to filter out at the end. They decide whether a revenue figure means anything, so each one is classified in the model itself and tested there. A caveat in a README would not have stopped anyone summing the column.
Three currencies, and a shop that trades on Sundays
The fact table carries every amount in three currencies, converted at the daily ECB fixing for the transaction date. Doing that against a transaction log turned up something the exchange-rate series had never shown by itself.
In total 136,933 lines, 13.1% of the file, convert on a rate the ECB published on an earlier day, and every one of them falls on a weekend, almost all of them on a Sunday. The business trades on Sundays and barely at all on Saturdays: 139,256 lines against 402. It also closes on the same holidays the euro system does, so no weekday closure ever coincides with an order.
The carry-forward rule and its 7-day cap were written against the FX series alone, where they filled a gap nothing was querying. With a transaction fact on top, 13% of the rows depend on them.
Both lines track the same trading, so the distance between them is sterling's exchange rate and nothing the business did. The electricity price makes the same point at country grain; here it applies to amounts somebody has to book.