Business Data-Cleaning Checklist

Imagine a coworking space operator whose member list has been maintained across a booking app, a WhatsApp group and a manual entry desk register for two years, with the same member sometimes appearing under a personal email in one and a company email in another. Before changing a single record, the useful first step is profiling what's actually there: how many rows, how many are missing required fields, how many look like likely duplicates, what date range the data spans. This step is easy to skip because it feels like delay before the "real" work, but it's what turns cleaning from guesswork into a measurable project with a before-and-after comparison.

Equally important, and equally easy to skip under time pressure: take a full, dated, authorised backup of the data before touching it, and get sign-off from whoever owns the data that this is the correct starting point. Data cleaning that isn't reversible is a risk few businesses should accept, however confident the person doing the cleanup feels.

Standardising comes before duplicate detection, not after, because inconsistent formatting is often what hides duplicates in the first place — "Delhi" and "New Delhi" won't match as the same value until formats are aligned. Standardise dates, phone numbers, addresses and category labels into one consistent format across every record.

Field validation checks each column against a rule it should satisfy — a phone number should be ten digits, a GSTIN should match a known pattern, a date shouldn't fall in the future. Only once formats and fields are consistent does duplicate identification become reliable — matching on a shared identifier like a phone number or GSTIN, or a reviewed fuzzy match on name and address where no identifier exists.

When two records are confirmed as duplicates, the merge needs a rule for which value survives when the two disagree — usually the most recently updated field, or the one from the system designated authoritative for that field. Merging without a defined rule leaves the outcome to whichever record happened to be processed last, which is not a decision anyone actually made.

The merge rule should be written down before the first merge happens, not decided case by case as duplicates turn up — otherwise similar conflicts get resolved inconsistently depending on who's doing the cleanup that day. A short set of rules, applied the same way every time, is what makes the result defensible later.

Once cleaning is done, compare key totals — record counts, sum of order values, customer counts — between the original backup and the cleaned dataset. A drop in record count should exactly match the number of confirmed duplicate merges; if it doesn't, records were lost somewhere in the process that weren't accounted for.

This reconciliation step is what separates a defensible cleanup from one that quietly loses or alters data no one can explain later. It should be done and reviewed by someone other than whoever performed the cleaning, as a basic check against a single person's blind spots.

A one-time cleanup addresses the symptom. The recurrence usually comes from the same entry point — free-text fields instead of dropdowns, no duplicate-check warning when creating a new customer, no required-field validation at the point of entry — that let the original mess accumulate.

Closing that entry point, even with something as simple as a mandatory field or a warning when a near-identical record already exists, is what makes a cleanup last. Without it, the same data-cleaning project tends to repeat on a cycle of a year or two, at the same cost, for the same reasons.