The Sales Forecast Built on a Broken Join
An analyst is asked to forecast next quarter's revenue for a small online retailer. They combine three files: an orders file (order ID, customer ID, date, amount), a customers file (customer ID, postcode, signup date), and a marketing spend file (month, channel, spend). The orders file has 48,000 rows but only 41,200 distinct order IDs. Postcodes in the customers file appear in three formats ('3000', 'VIC 3000', '3,000'). The marketing file uses month names ('March') while orders use ISO dates. The analyst joins orders to customers on customer ID, drops the 6,800 non-matching rows as 'bad data', and reports that revenue grew 23% quarter on quarter. A director asks whether the growth is real.
1. Diagnose the data preparation errors and explain how each could distort the 23% growth figure.
Read the worked answer
Duplicate order IDs (48,000 rows, 41,200 IDs). Roughly 6,800 rows are duplicates. If duplicates are genuine line items, summing amount double-counts revenue; if they are export errors, the total is inflated. Either way the revenue base is wrong before any comparison is made.
Dropping 6,800 non-matching rows. The analyst removed unmatched orders rather than investigating them. Unmatched orders may be guest checkouts, deleted customers, or a customer-ID format change partway through the period. If the format changed mid-quarter, the drop is uneven across time - which directly distorts a quarter-on-quarter comparison. This is the most dangerous error because it silently changes the population.
Inconsistent postcodes. Three formats mean any postcode-based grouping or join fails or fragments. If postcode is used to segment revenue, segments are understated inconsistently.
Date format mismatch. 'March' cannot be joined to ISO dates without standardisation; if the join silently fails, marketing spend may be misallocated to months, breaking any spend-to-revenue comparison.
No documented cleaning log. Without recording what was changed, the analyst cannot distinguish real growth from artefacts of cleaning. The 23% is not trustworthy until the duplicates are resolved, unmatched rows explained, and formats standardised - then recomputed on a consistent population.
2. Describe the cleaning and validation steps you would perform, and how you would test whether the growth survives.
Read the worked answer
Cleaning (in order):
- Profile each file: row counts, distinct keys, null rates, date ranges.
- Resolve duplicates: determine whether repeated order IDs are line items (aggregate to order level) or errors (keep one, log the rest). Record the rule.
- Standardise postcodes to a single format (strip spaces and commas, validate against a known postcode list).
- Standardise dates to ISO and map month names to the same period key.
- Investigate unmatched orders before dropping: check whether customer IDs changed format by date. If so, fix the key rather than delete rows.
- Keep a cleaning log with counts at each step.
Validation:
- Range checks (amount > 0), referential integrity (every order has a valid customer or an explicit 'guest' flag), and completeness checks on date and amount.
Testing the growth:
- Recompute revenue on the cleaned, consistent population for both quarters.
- Run a sensitivity analysis: does growth hold if unmatched rows are included? If duplicates are aggregated rather than dropped?
- Compare order counts and average order value separately; growth driven only by one component is worth questioning.
- Plot monthly revenue to see whether growth is a step change (possibly a system change) or gradual.
Report growth with the cleaning decisions disclosed, and state the range across scenarios rather than a single 23%.