Cleaning a Multi-Region Signup Extract
An e-commerce team receives a 25,000-row customer signup CSV assembled from three regional forms. Columns are email, signup_date, region, age, and marketing_opt_in. Profiling shows four date formats (YYYY-MM-DD, DD/MM/YYYY, MM/DD/YYYY, and Unix seconds), 8% missing region, age values of -1 and 999 alongside plausible ages 13-105, 1,200 future signup dates, duplicate emails that differ only by case or surrounding spaces, and opt-in values of Yes, no, TRUE, 1, and blank. The team wants a cleaned table for conversion-rate analysis by region and age band. A junior analyst proposes deleting every row with any missing or invalid field and imputing missing ages with the column mean. The analysis is due tomorrow, and stakeholders want a single clean file. You must review the proposal and define a defensible validation and cleaning plan.
1. Assess the junior analyst's drop-all-and-mean-impute plan. What biases and data loss would it introduce for region-by-age conversion analysis?
Read the worked answer
Deleting every row with any invalid field is not a cleaning plan; it is a selection bias. Missing region alone is 8% of 25,000 = 2,000 rows; adding future dates, sentinel ages, and ambiguous fields could remove 10-20%. If missingness varies by region, the remaining regions overrepresent certain behaviors, so conversion rates by region are biased.
Mean-imputing age compresses variance and creates impossible precision; it shifts age-band counts and can move people across bands, especially if missing age is related to channel. Sentinel -1 and 999 should become null, not real ages. Duplicate emails inflate denominators if counted as separate customers. Blank opt-in is not 'no'; collapsing it hides consent status.
A single clean file can be produced, but only with explicit flags, a quarantine file, and a missing-data category rather than silent deletion.
2. Specify validation rules, parsing choices, and handling for ambiguous dates, duplicates, missing region, sentinel ages, and future dates. State what you would report to stakeholders.
Read the worked answer
Validate at ingestion and preserve raw values. Parse dates with explicit formats; route DD/MM versus MM/DD ambiguity to review unless the source form is known. Convert Unix seconds with UTC and record the timezone. Flag signup_date > current date; if a known timezone offset explains it, correct and log; otherwise quarantine.
Normalize email with trim and lowercase, then define customer key; deduplicate by keeping earliest or latest signup according to a documented rule, and report duplicate counts. Map opt_in to true/false/unknown; do not coerce blank to false. Convert age -1/999 to null and validate plausible range; keep 'Unknown' as an age band rather than mean-impute. Region missing becomes 'Unknown' and is reported; region comparisons should show coverage or run a sensitivity analysis excluding Unknown.
Produce a validation report with counts per rule, a quarantined rows file, and a data dictionary. Tell stakeholders that region and age-band rates have different denominators and that Unknown categories are not dropped silently.