Data Cleaning Log
Builds on Topic 7.
What you'll produce
A Data Cleaning Log for the raw FreshBox extract and the messy support-ticket CSV: a transparent, row-by-row record of every issue you found — duplicates, inconsistent country and plan formatting, missing cancellation dates, numbers stored as text, impossible values, outliers — paired with the decision you made, the reason for it, and the row count before and after. It's the artifact that makes the rest of the investigation trustworthy: when leadership asks "are you sure churn is worst in the UK?", this log is your proof that the UK number isn't an artifact of "U.K." and "United Kingdom" being counted as two countries. This proves the Topic 7 skills: spotting the five classic data problems, making a defensible judgment call on each (never a silent deletion), and documenting it so the whole pipeline is reproducible. It is the unglamorous 80% of the job, done in a way you can hand to a skeptic.
Instructions
- Profile the raw data before you touch it. For each table and the CSV, write down row count, column list, and the min/max/blank-count of every column you'll use. This snapshot is your "before" — you cannot prove what you cleaned without it. Capture it as a short "Source profile" block at the top.
- Hunt for each of the five problem types deliberately, one pass per type, so you don't miss any: (a) duplicates — exact row dupes and business-key dupes (same
customer_idtwice); (b) inconsistent formatting — country, plan name, casing, stray whitespace, date formats; (c) missing values — especiallycancellation_datefor rows marked churned; (d) wrong types — numbers stored as text ("$49.00","49"with a trailing space) that won't sum; (e) outliers / impossible values — negative tenure, MRR of 0 or 99999, signup dates in the future. - Log every issue as its own numbered row with six fields: Issue · Where (table.column) · How found · Rows affected · Decision · Why. One issue per row. No issue is too small to log.
- Make a defensible decision per issue, and never delete silently. For each, choose one of: standardize (map variants to one canonical value), fill/impute (and say with what, and flag the imputed rows), exclude (quarantine the rows into a "dropped_rows" set, never
DELETE), or keep & flag (real but extreme — keep it, mark it, decide later). Write the why in business terms. - Treat outliers as questions, not errors. Investigate before excluding: a 4,000-unit order might be a real corporate account. State what you checked and why you kept or cut it. This is the Topic 7 "investigate rather than assume" rule.
- Re-run your sanity checks and record the "after." Re-count rows, re-check that churned rows now all have dates, that MRR sums to a believable total, and that country/plan have the expected handful of distinct values. Note any total that changed and confirm the change is explained by your log — nothing should move unexplained.
- Write a 3–5 line reproducibility note so a teammate could regenerate your clean dataset: source files + dates, the order of operations, and where the quarantined rows live. State your one riskiest cleaning assumption (the call most likely to change a headline number if you're wrong).
Worked example
(FreshBox investigation — cleaning the warehouse extract freshbox_export_2026-05-31.csv, joined from the four warehouse tables, plus the support export support_tickets_raw.csv)
Source profile (the "before")
| Source | Rows | Key columns checked | First-glance flags |
|---|---|---|---|
| Warehouse extract (customers ⋈ subscriptions ⋈ orders ⋈ cancellations) | 40,213 | customer_id, country, plan_tier, billing_cadence, mrr, signup_date, status, cancellation_date, tenure_months | 213 more rows than the ~40,000 active subs leadership quoted — suspicious |
| Support tickets CSV | 8,946 | ticket_id, customer_id, category, created_at, csat | Mixed date formats visible on scroll; blank category cells; csat like "4", "N/A", 4.0 |
Sanity check that triggered the cleaning: SELECT COUNT(*) = 40,213 active rows, but COUNT(DISTINCT customer_id) = 39,980. That 233-row gap is the first thing to explain before any churn number is believable.
Cleaning log
| # | Issue | Where (table.column) | How found | Rows affected | Decision | Why |
|---|---|---|---|---|---|---|
| 1 | Duplicate customer rows — same customer_id appears twice, once with status='active' and once status='churned', from a re-subscribe being loaded as two rows | extract.customer_id | COUNT(*) vs COUNT(DISTINCT customer_id) gap of 233; grouped to inspect | 233 customers (466 rows) | Keep both, re-label — these are genuine re-subscribers. Kept the churned row in the churn analysis and the active row in the active base; added an is_resubscriber flag. Did not drop either. | Dropping one row would erase a real churn-then-return event, which is exactly the retention story we're investigating. Counting them as 2 separate customers would inflate the base. |
| 2 | Exact duplicate orders — identical order_id, customer, date, amount | orders.order_id | GROUP BY order_id HAVING COUNT(*)>1 | 184 rows | Exclude the duplicate copy (kept 1 of each), quarantined the 184 dropped rows to dropped_rows.csv | A double-loaded order silently inflates revenue and order counts. Quarantined, not deleted, so the drop is auditable. |
| 3 | Inconsistent country formatting — "UK", "U.K.", "United Kingdom", "gb" all present; also "USA"/"United States"/"us " (trailing space) | extract.country | SELECT DISTINCT country returned 14 values for what should be ~6 markets | 7,711 rows touched | Standardize to ISO-style canonical names (United Kingdom, United States, …) via an explicit mapping table, trimmed whitespace, fixed casing | Churn-by-country is a core segment in the brief. Un-standardized, the UK would be split across 4 buckets and its churn rate would look artificially low in each. |
| 4 | Inconsistent plan-tier naming — "Basic", "basic", "BASIC plan", "2-person" and "Two Person" (both legacy labels for Basic) | subscriptions.plan_tier | SELECT DISTINCT plan_tier → 9 values for 3 real tiers | 40,029 rows | Standardize to the 3 canonical plan tiers the brief locked: Basic, Family, Premium via an explicit mapping (2-person/Two Person → Basic) | Plan tier is a primary churn segment; three tiers must be three buckets, not nine. This is the product axis — kept strictly separate from billing cadence (Issue 4b). |
| 4b | Inconsistent billing-cadence naming — "Monthly", "monthly", "mo", "M" vs "Annual", "annual", "yearly", "yr" | subscriptions.billing_cadence | SELECT DISTINCT billing_cadence → 8 values for 2 real cadences | 40,029 rows | Standardize to the 2 canonical cadences the brief locked: Monthly, Annual via mapping | Billing cadence is the second, orthogonal dimension the brief defined and the one D4–D6 segment churn by. It must never be folded into plan_tier — Monthly is a cadence, not a tier — or the downstream churn cuts (Monthly mid-term cancel vs. Annual renewal lapse) become uncomputable. |
| 5 | MRR stored as text with currency symbols — values like "$49.00", " 49", "49.0" won't sum | subscriptions.mrr | SUM(mrr) errored / returned text concatenation; typeof check | 40,029 rows | Cast to numeric after stripping $ and whitespace; verified SUM now returns a number — $1.71M across the 39,418 active rows | MRR trend and dollar-impact (Deliverable 6) are impossible if MRR is text. Logged because the cast is a transformation, not a no-op. Currency note: every value carried a $; this is USD, the canonical currency for the whole investigation — D2's ARPU check, the D5 dashboard, and the D6 readout all read in dollars. |
| 6 | Missing cancellation dates on churned rows — status='churned' but cancellation_date is blank | cancellations.cancellation_date | WHERE status='churned' AND cancellation_date IS NULL → 412 rows | 412 rows | Keep & flag, impute conservatively — set cancellation_date = last order date + 30 days (one billing cycle), marked cancel_date_imputed=TRUE. Did not drop them. | These are real churn events; dropping them would understate churn. Imputing from last activity is defensible and the flag lets us exclude them from any date-precise cut. This is the riskiest assumption (see below). |
| 7 | Impossible negative tenure — tenure_months = -3 | extract.tenure_months | MIN(tenure_months) = -3; sorted ascending | 7 rows | Exclude, quarantined — traced to signup dates loaded after the order date (a pipeline bug, not real customers) | Negative tenure is physically impossible and corrupts any tenure-based segment. 7 rows, quarantined with the reason, not silently dropped. |
| 8 | MRR outliers — 3 accounts at mrr = $1,470 vs the normal $35–$99 plan range | subscriptions.mrr | mrr sorted descending; values >10× the 99th percentile | 3 rows | Keep & flag as is_enterprise — investigated and confirmed they are real bulk corporate accounts (one office ordering ~30 boxes at $49 each) | A real, large customer is not an error. Keeping them is correct, but flagging lets us report median MRR (Topic 8) so they don't distort the average — and it explains why ARPU ($43.40) sits a touch above the $35–$55 plan midpoint without breaching it. |
| 9 | Future signup dates — signup_date = 2027-01-15 | customers.signup_date | WHERE signup_date > '2026-05-31' (extract date) | 19 rows | Exclude, quarantined — month/day likely swapped on import but unrecoverable with confidence | A signup in the future is impossible; without a trustworthy correction, including it would pollute cohort cuts. Quarantined, not deleted. |
| 10 | Mixed date formats in CSV — 01/02/2026 (US), 2026-02-01 (ISO), Feb 1, 2026 in one column | support.created_at | Visual scroll + parse failures on load | 8,946 rows | Standardize to ISO YYYY-MM-DD, parsing each format explicitly; 0 rows failed to parse after mapping | Ticket timing must align with cancellation dates to test the "support pain drives churn" hypothesis; mixed formats break that join. |
| 11 | Blank category in support tickets | support.category | category IS NULL OR category='' → 1,107 rows | 1,107 rows | Fill with 'Uncategorized' and flag, rather than drop | Dropping 12% of tickets would bias the "what do churned customers complain about?" cut. Keeping them visible as Uncategorized is honest. |
| 12 | csat mixed text/number — "4", 4.0, "N/A", blank | support.csat | DISTINCT csat; type check | 8,946 rows | Cast to numeric; map "N/A"/blank → NULL (a true missing rating, not zero) | Coding a missing rating as 0 would fabricate dissatisfaction and skew average CSAT downward. NULL is the honest representation. |
Re-run sanity checks (the "after")
- Row count: 40,213 → 39,983 active-base rows (184 dup orders excluded, 7 negative-tenure + 19 future-signup quarantined; the 233 re-subscribers retained as flagged).
COUNT(DISTINCT customer_id)now reconciles to the active base. Every removed row is accounted for in the log and sits indropped_rows.csv(210 rows). - MRR: summed over the 39,418 rows that meet the brief's strict active definition (status
activeand no cancellation row — the subset of the 39,983 cleaned active-base rows after the 565 stale-status rows are excluded from the revenue cut),SUM(mrr)now returns $1.71M monthly — within 2% of Finance's reported book-of-business MRR ($1.68M), so the cast and de-dup are believable. Two reconciliation checks I ran before trusting it: (1) ARPU back-of-envelope — $1.71M ÷ 39,418 active subs = $43.40/sub, which sits squarely inside the real plan-price band of $35–$55 (Basic/Family/Premium). This is the check that catches a currency or units error: had the sum come back near $1.04M, ARPU would be $26 — below the cheapest plan, which is arithmetically impossible and would mean I'd summed a partial/wrong column, not the active base. (2) Direction-of-discrepancy — Finance reports gross book MRR; my net figure (after the brief's discount netting) should sit slightly under theirs, and it does. A figure above Finance would have flagged that my de-dup (Issue #1/#2) didn't actually remove the double-loaded rows. Both checks pass, so $1.71M is the canonical MRR every downstream deliverable must tie to. - Country: 14 → 6 distinct values. Plan tier: 9 → 3 (
Basic/Family/Premium). Billing cadence: 8 → 2 (Monthly/Annual). - Churned rows: 100% now have a
cancellation_date(412 imputed and flagged); none blank. - Nothing moved unexplained: the only totals that changed are the ones this log explains.
Reproducibility note
Sources: freshbox_export_2026-05-31.csv (warehouse join as of 2026-05-31) and support_tickets_raw.csv (pulled 2026-05-31). Order of operations: de-duplicate → standardize country / plan tier / billing cadence → cast MRR/CSAT to numeric → handle missing cancellation/category → quarantine impossible values → re-run sanity checks. Quarantined rows live in dropped_rows.csv with a drop_reason column; imputed rows carry cancel_date_imputed/flags. Canonical figures this log locks (so every later deliverable reconciles to the same numbers): active base 39,418; total active MRR = $1.71M USD (ARPU $43.40); currency USD throughout — no £, no second MRR figure. If a downstream tile (the D5 dashboard, the D6 readout) shows a different MRR, it is a reconciliation failure to chase here, not a new number to accept: divide it by 39,418 and confirm ARPU still lands in $35–$55 before believing it. Riskiest assumption (Issue #6): imputing 412 missing cancellation dates as "last order + 30 days." If those churns actually happened months earlier, monthly churn timing — and the exact month the MRR trend rolled over — could shift. Mitigation: re-run the MRR-trend cut with those 412 rows excluded; if the $1.71M total and the rollover month hold both ways, the assumption is safe to present.
Rubric
The app's AI scores the learner's submission against these criteria and gives feedback. Levels: Needs work (1) / Solid (2) / Excellent (3). Passing = every criterion at Solid or above.
- Coverage of the five problem types — 1: catches only the obvious dupes, misses formatting/type/missing/outlier issues · 2: finds at least one real instance of each of the five types in the FreshBox data · 3: finds them all, distinguishes exact vs business-key duplicates and impossible-vs-real outliers, and catches subtle ones (trailing whitespace, text-stored MRR).
- Decision quality and business reasoning — 1: decisions are arbitrary or unexplained ("removed bad rows") · 2: every issue has a clear decision and a plausible reason · 3: decisions are defensible in business terms, tied to the downstream churn/MRR question, and pick the right action (standardize vs impute vs exclude vs keep-&-flag) for each case.
- No silent deletions / auditability — 1: rows are deleted with no record, or counts don't reconcile · 2: every change is logged with rows-affected and dropped rows are kept, not destroyed · 3: full before/after row reconciliation where every removed row is explained and quarantined, and totals only move where the log accounts for it.
- Outliers investigated, not assumed — 1: outliers auto-deleted or ignored · 2: outliers are flagged and a keep/cut decision is stated · 3: each outlier is investigated (what was checked), real-but-extreme values are kept & flagged for median reporting, and only confirmed errors are excluded.
- Sanity checks and reproducibility — 1: no verification that cleaning worked · 2: re-runs key checks (row count, MRR sum, distinct counts) and states a reproducibility note · 3: reconciles the MRR total against an external reference (Finance MRR) and against an internal one (ARPU = MRR ÷ active base must land inside the real plan-price range, in one stated currency) — so a wrong total is caught, not just reported — then names the single riskiest cleaning assumption and a concrete way to test whether it changes the headline.
- Coherence with the investigation — 1: a generic cleaning checklist disconnected from FreshBox · 2: uses the real FreshBox tables, segments, and metrics from the brief · 3: cleaning choices visibly protect the specific segments (country, plan tier, billing cadence) and metrics (churn, MRR) the analysis depends on, keeping plan tier and billing cadence as two distinct standardized columns, so the next deliverable can trust this data.