Customer records (messy)

An export of 700 customer records from the point-of-sale system of a fictional homeware retailer, Rimal Living, deliberately left messy for data-cleaning practice: exact duplicates, inconsistent city spellings, placeholder text, impossible ages, negative spends and a unit mix-up (some spends in halalas instead of riyals). Every planted problem is counted below; the cleaned counterpart is customers_clean.

Open in the lab Download CSV

Rows: 700
Columns: 8
One row is: one customer record (some customers appear twice)
Version: v1

Data dictionary

Column	Type	Unit	Summary	Description
`customer_id` Customer	id			Customer identifier (repeated in exact duplicate records).
`signup_date` Signup date	date			Date the customer account was opened.
`city` City	category		19 levels; 7 missing	City of residence as typed by staff — inconsistent capitalisation, aliases and stray spaces.Missing: Null where the city was not recorded.
`segment` Segment	category		2 levels: Individual, Small business	Customer segment.
`age` Age	integer	years	mean 37.21, median 35, SD 15.56, range −1–230; 18 missing	Age in years as recorded (contains impossible values).Missing: Null where not recorded.
`annual_spend_sar` Annual spend	number	SAR	mean 12130, median 3511, SD 83150, range −12150–1,762,072; 12 missing	Spend over the last 12 months as recorded (a few entries are in halalas, a few are negative).Missing: Null where not recorded.
`referral_source` Referral source	text			How the customer heard about the shop, free text (contains placeholder text such as 'N/A' and stray spaces).
`email_opt_in` Email opt-in	boolean		2 levels	True if the customer agreed to receive marketing emails.

What it is good for

Profile a dataset to find duplicates, inconsistent categories, placeholders, impossible values and unit errors.
Write explicit, reproducible cleaning rules and count how many records each rule changes.
Decide when to correct a value, set it to missing, or drop a record — and document the decision.
Compare summaries before and after cleaning (e.g. mean spend with and without halala entries).

Methods: data profiling, frequency tables, duplicate detection, string standardisation, range checks, outlier detection, missing-data handling.

Data quality notes

Exact duplicate records: 21 (700 rows = 679 unique customers + 21 copies).
Inconsistent city spellings: 45 records (case variants such as "RIYADH"/"riyadh", leading/trailing spaces such as "Riyadh ", and aliases "Al Khobar"/"al-khobar" for Khobar).
Missing values (null): age 18, city 7, annual_spend_sar 12.
Placeholder text in referral_source: "N/A" 14, "unknown" 9, "-" 5 (28 in total) — missing values disguised as text.
Leading/trailing whitespace in referral_source: 8 records.
Impossible ages: 3 records with −1 and 2 with 230.
Negative annual spend: 4 records (refunds netted incorrectly).
Unit error: 6 spends recorded in halalas (×100), all above 100 000.
Each problem affects a different set of records, and duplicated records carry no other problem, so the counts above hold for the whole table.

Provenance and licence

679 true customer records are simulated (city, segment, age ~ 18 + gamma, spend log-normal by segment, referral source, email opt-in). Problems are then planted in disjoint, randomly chosen records with a separate seeded stream, and 21 records without other problems are duplicated exactly and inserted at random later positions.

Synthetic data · CC0-1.0 (synthetic data generated by Statistics Quest) · generated deterministically (seed 20241025). It does not describe real people or organisations; any resemblance is coincidental.

Preview

Dataset

Customer records (messy)

700 rows · one row = one customer record (some customers appear twice) · synthetic data

About this dataset Open in the lab

Customer	Signup date	City	Segment	Age (years)	Annual spend (SAR)	Referral source	Email opt-in
C0001	2024-02-28	Riyadh	Small business	31	571670	Advertisement	true
C0002	2021-07-15	Jeddah	Individual	20	4679.64	Friend	true
C0003	2024-08-12	Dammam	Small business	30	3027.49	Advertisement	false
C0004	2022-11-14	Riyadh	Individual	34	5440.72	Search	false
C0005	2022-12-01	Riyadh	Individual	47	1382.89	Social media	false
C0006	2023-11-01	Khobar	Individual	37	4162.73	Store visit	false
C0007	2022-12-06	Jeddah	Individual	36	4146.99	Friend	false
C0008	2023-10-20	Riyadh	Individual	24	2789.46	Search	false
C0009	2024-08-30	Khobar	Individual	missing	2828.16	Search	false
C0010	2022-12-19	Jeddah	Individual	37	1536.23	Search	true
C0011	2021-10-24	Jeddah	Individual	32	10629.29	Search	false
C0012	2022-09-06	Dammam	Small business	71	8058.07	Social media	true

Used in

Importing data · Practical Data Analysis
Setting variable types · Practical Data Analysis
Finding and handling missing values · Practical Data Analysis
Duplicates and invalid values · Practical Data Analysis
Making and documenting cleaning decisions · Practical Data Analysis
Reproducible analysis · Practical Data Analysis
Full statistical investigation: from a messy export to a decision · Capstone