Cleaning a device column that has been free text for years

Every system that has been collecting device identifiers by hand for a few years has the same column: labelled "IMEI", validated by nothing, and containing a considerably wider variety of things than its name suggests.

The instinct is to throw the whole column at a lookup API and see what comes back. That is the expensive way round, and it produces a bill for questions that could have been answered locally.

What is actually in there

Valid identifiers, badly formatted identifiers, MEIDs from CDMA-era stock, serial numbers entered by someone who read the wrong label, numbers that fail the checksum because a digit was mistyped, and free text like "n/a" from people obliged to put something in a required field.

The useful observation is that these fall into two groups: the ones that can be recovered without asking anybody anything, and the ones that cannot be recovered at all.

Four passes, in order of cost

Normalise first. Strip spaces, dashes, quotes and stray whitespace. This is free and recovers a larger share than anyone expects, because a great deal of what looks like bad data is good data with formatting on it.

Classify second. Fifteen digits with no letters is an IMEI. Fourteen characters including A–F is an MEID. Anything else goes in a third pile to be looked at by a person, or not at all.

Validate third, still locally. The checksum removes invented numbers and most single-digit typos, and it costs nothing per row.

Only then resolve — and deduplicate on the first eight digits before you do. A million rows of stock are rarely a million models; the TAC repeats heavily, and one lookup per distinct TAC rather than per row is often two orders of magnitude fewer calls.

What to do with what does not survive

Resist the urge to delete. A row whose identifier is unusable is still a record of something that happened, and removing it makes the history wrong in a different way.

1. Keep the original value untouched in its own column. Whatever you do next, do not overwrite the evidence.
2. Add a status alongside it: resolved, unresolvable, wrong format, empty. Now the column can be reasoned about.
3. Fix the intake form before backfilling. Cleaning a column that is still being filled badly is work you will repeat.
4. Backfill what you can, accept what you cannot, and record the date you drew the line.

The last point matters more than it sounds. A cleaned database with no record of when or how it was cleaned invites the same exercise again in two years, by someone who cannot tell which rows were already examined and found hopeless.