Inconsistent category labels

• The field was free text when it should have been a fixed list.
• The allowed values changed over time and old rows were never updated.
• Typing errors, different capitalization, and regional spellings accumulate.

• Group-by reports show one row per spelling, so no single line gives the true total.
• Filters miss rows: selecting active leaves out Active and activee.
• Charts grow a long tail of tiny categories that are really the big ones misspelled.

• Count distinct values. A column with five real categories and thirty distinct values has this problem.
• Sort the distinct values by frequency. The rare ones are usually variants of the common ones.
• Lowercase and trim the column, then count again to see how much is only formatting.

• Write down the canonical list first. Without it there is nothing to map to.
• Map each variant to its canonical value, and keep the mapping so it can be reused.
• Send values that do not clearly belong to any category to a person, instead of forcing them into the nearest one.

• Name variantsThe same person or organization appears under several spellings, orders, and abbreviations.
• Disguised missing valuesA cell is not empty, but what it holds means there is no value: N/A, a dash, 999, or the word none.
• Stray whitespace and invisible charactersTwo values look identical on screen but differ by a trailing space, a non-breaking space, or a hidden character.
• Case example: Academic researchA survey whose result depended on how blanks were coded
• Case example: Marketing analyticsFive clients, five definitions of a campaign
• Case example: UtilitiesEquipment records that could not be tied to what was spent on them
• Case example: E-commerceA catalog where the same product was listed four times

TermsCanonical value, Cardinality, Standardization, Validation