Standardizing categories and names

Your region chart has eight real markets and forty-seven labels. US, usa, United States, U.S., a few trailing spaces, and a lonely Americas that someone typed because the form allowed free text. The pivot table looks sophisticated. It is mostly a spelling bee.

Category chaos is a consistency problem with a simple cure that people keep reinventing in one-off notebooks: a mapping table from raw values to canonical values, plus the discipline to stop inventing synonyms in the dark. The failure mode is “Other hell,” where everything awkward falls into a bucket so large it stops meaning anything.

This is Part 4 of Data quality for people who ship numbers. After dimensions, profiling, and careful deduping, we standardize names and categories so groupbys tell the truth. Hands-on, not master data theater with a twelve-month steering committee.

What you’ll learn

• What a canonical value is and why alias lists beat heroic CASE statements
• How to design a mapping table analysts will actually maintain
• How to detect “Other hell” before it eats your dashboard
• SQL and Python patterns for apply-map, unmapped rates, and safe defaults
• A worked example turning messy channels into something leadership can read

Canonical values: the short official list

A canonical value is the agreed spelling and grain for a category in analytics: United States, not six cousins; Paid Search, not every UTM creative string. Canonical does not mean “the only string that will ever appear in source systems.” Sources stay messy. Your presentation and mart layers speak one dialect.

Choose grain deliberately. Is “Paid Search” enough, or do you need “Paid Search – Brand” vs “Paid Search – Non-brand”? Finer grain means more mapping work and cleaner cuts. Coarser grain means simpler charts and hidden structure. Pick from the decision, the same habit as Analytics foundations.

Write the allowed set somewhere visible: a seed CSV in the repo, a small warehouse table, a documented sheet with an owner. If the list lives only in one person’s head, you do not have standards. You have folklore.

Mapping tables beat giant CASE statements

A mapping table is a two-sided dictionary: raw (or normalized raw) goes in, canonical comes out. Optional columns help a lot: source system, effective dates, who approved the map, notes.

Why not a 200-line CASE in every query? Because CASE copies multiply. Someone updates the ads dashboard and forgets the finance one. Mapping tables centralize the alias list. Queries join once. When a new raw value appears, you add a row instead of hunting SQL.

This is the same “put rules in data” instinct as keeping structural truth out of one-off spreadsheet edits in From spreadsheets to real data.

Normalize before you map

Before matching, reduce avoidable variance:

• Trim whitespace
• Collapse internal repeated spaces
• Lowercase for matching (store display labels separately if you need pretty title case)
• Unify obvious punctuation (U.S. vs US) with deliberate rules
• Decide whether empty string maps to null or to a canonical Unknown

Normalization is not the full standard. It only makes the alias list shorter. You still need human judgment for EMEA vs Europe if those are different grains in your company.

import re
import pandas as pd

def norm_label(s: str) -> str:
    if s is None or (isinstance(s, float) and pd.isna(s)):
        return ""
    s = str(s).strip().lower()
    s = re.sub(r"\s+", " ", s)
    s = s.replace("u.s.", "us").replace("u.s", "us")
    return s

df = pd.DataFrame({"region_raw": ["US", " usa", "U.S.", "EMEA", "Europe", ""]})
df["region_norm"] = df["region_raw"].map(norm_label)
print(df)

Example:

Apply the map in SQL and Python

Imagine map_channel:

SQL apply pattern (normalize in the join key):

SELECT
  e.event_id,
  e.utm_source AS channel_raw,
  COALESCE(m.canonical_value, 'Unmapped') AS channel,
  CASE WHEN m.canonical_value IS NULL THEN 1 ELSE 0 END AS is_unmapped
FROM web_events e
LEFT JOIN map_channel m
  ON lower(trim(e.utm_source)) = lower(trim(m.raw_value));

Example output:

Python apply pattern:

import pandas as pd

events = pd.DataFrame(
    {
        "event_id": [1, 2, 3, 4, 5],
        "utm_source": ["google_cpc", "Google / CPC", "tiktok_ads", "newsletter", None],
    }
)
mapping = pd.DataFrame(
    {
        "raw_value": ["google_cpc", "google / cpc", "fb_ads", "newsletter", "none"],
        "canonical_value": ["Paid Search", "Paid Search", "Paid Social", "Email", "Direct"],
    }
)

events["raw_norm"] = events["utm_source"].fillna("").str.lower().str.strip()
mapping["raw_norm"] = mapping["raw_value"].str.lower().str.strip()

out = events.merge(mapping[["raw_norm", "canonical_value"]], on="raw_norm", how="left")
out["channel"] = out["canonical_value"].fillna("Unmapped")
print(out[["event_id", "utm_source", "channel"]])

Notice tiktok_ads becomes Unmapped, not silently Other. That is intentional. Unmapped is an alarm. Other is often a graveyard.

Other hell (and Unknown limbo)

Other hell is what happens when the catch-all bucket becomes the largest slice of the pie. It usually means free text inputs, incomplete mapping, or a taxonomy that no longer matches how the business sells. Unknown limbo is similar when nulls and blanks are labeled Unknown and then ignored forever.

Catch-alls are allowed. They are tools. They fail when they stop being temporary.

Operational rules that keep you honest:

• Track percent unmapped and percent Other as quality metrics, same family as null rates from Part 2.
• Set a threshold (example: investigate when Other exceeds 5% of rows or 10% of revenue).
• Review the top unmapped raw values weekly. Promote frequent ones into the map.
• Split Other only when a segment is large enough to change decisions.
• Never map everything to Other just to make a chart’s legend short.

SELECT
  channel,
  COUNT(*) AS n,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct
FROM (
  SELECT COALESCE(m.canonical_value, 'Unmapped') AS channel
  FROM web_events e
  LEFT JOIN map_channel m
    ON lower(trim(e.utm_source)) = lower(trim(m.raw_value))
) s
GROUP BY channel
ORDER BY n DESC;

Example output:

If Unmapped or Other leads the chart, you do not have a visualization problem. You have a taxonomy problem.

Worked example: campaign channels for a weekly growth review

Raw weekly extract (toy data):

Without mapping, a value_counts style report treats Google twice, Facebook twice, and Acme twice. Paid Social looks weak. Partner revenue looks fragmented. Someone will “fix” it by hand in the slide and the next extract will undo the heroics.

Extend the map:

After apply, the growth table becomes discussable:

SQL that produces the revenue rollup:

WITH cleaned AS (
  SELECT
    s.signup_id,
    s.revenue,
    lower(trim(coalesce(s.utm_source, ''))) AS raw_norm
  FROM signups s
),
mapped AS (
  SELECT
    c.signup_id,
    c.revenue,
    COALESCE(m.canonical_value, 'Unmapped') AS channel
  FROM cleaned c
  LEFT JOIN map_channel m
    ON c.raw_norm = lower(trim(m.raw_value))
)
SELECT
  channel,
  COUNT(*) AS signups,
  SUM(revenue) AS revenue
FROM mapped
GROUP BY channel
ORDER BY revenue DESC;

pandas twin for the same rollup (handy when you live in notebooks from the Python for analytics path):

signups = pd.DataFrame(
    {
        "signup_id": range(1, 11),
        "utm_source": [
            "google_cpc", "Google / CPC", "fb_ads", "facebook", "tiktok_ads",
            "newsletter", None, "partner_acme", "Partner_Acme", "referral",
        ],
        "revenue": [120, 80, 60, 40, 90, 30, 50, 200, 150, 20],
    }
)
signups["raw_norm"] = signups["utm_source"].fillna("").str.lower().str.strip()
mapping["raw_norm"] = mapping["raw_value"].str.lower().str.strip()
# mapping must include tiktok_ads, partner_acme, referral, and empty string rows as above
m = signups.merge(mapping[["raw_norm", "canonical_value"]], on="raw_norm", how="left")
m["channel"] = m["canonical_value"].fillna("Unmapped")
print(m.groupby("channel", as_index=False).agg(signups=("signup_id", "count"), revenue=("revenue", "sum"))
      .sort_values("revenue", ascending=False))
print("unmapped_rate", (m["channel"] == "Unmapped").mean())

Alias lists and ownership

An alias list is simply all raw forms that point to one canonical value. Treat it as a living document:

• One owner (or a small rotation), not “everyone edits whenever”
• Pull requests or a lightweight approval for high-revenue categories
• A scheduled job that lists raw values seen in the last N days with no map row
• Versioning in git when the map is a CSV; warehouse table history when it is SQL

When marketing launches a new partner code, the map update is part of the launch checklist. When the map lags, Unmapped rises. That is a better failure than silent mis-bucketing into Other.

Names of people and companies (a careful note)

Category standardization (region, channel, status) is usually safer than “standardizing” personal names. People names have culture, punctuation, and legal spellings. Company names have legal entities and DBAs. Prefer:

• External stable ids when you have them
• Display the source name; standardize only attributes you truly need to group
• Use the identity maps from Part 3 rather than renaming humans into a “canonical name” fantasy

If you must clean names for matching, keep the original columns. Always.

Common mistakes

• Mapping only in the BI tool. Next extract or next tool reintroduces chaos.
• Over-granular taxonomies nobody maintains. Forty channels with three events each is noise.
• Hiding unmapped as Other. You lose the alarm signal.
• Case-sensitive joins. Partner_Acme and partner_acme deserve the same fate.
• Changing canonical labels without a migration note. Time series break when “Paid Social” becomes “Social Paid” mid-year.
• Standardizing in place on the raw landing table. Keep raw; map downstream so you can reprocess.
• Forgetting revenue-weighted reviews. A rare code with huge revenue matters more than a common code with pennies.

How to practice this week

• Pick one high-chatter field (channel, region, status, plan). Dump distinct values and counts.
• Propose a canonical list of 5 to 15 values. Argue grain with one stakeholder.
• Build a mapping table (SQL, CSV, or sheet) and join it in one report only as a pilot.
• Publish unmapped rate next to the chart for two weeks. Watch how fast aliases appear.
• If joins and groupbys need a refresh, use the SQL series or the paths on Learn, then come back for dates and time zones in Part 5.

Quick recap

• Canonical values are the short official list; aliases are everything sources actually emit.
• Mapping tables centralize standardization better than copy-pasted CASE blocks.
• Normalize lightly, map deliberately, keep raw columns.
• Treat Unmapped as an alarm; treat Other as a temporary bucket with a measured size.
• Next up in the series: dates, time zones, and fiscal calendars, the silent dashboard killers.

Sources

• pandas documentation, DataFrame.merge: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.merge.html
• pandas documentation, working with text data (string methods for normalization): https://pandas.pydata.org/docs/user_guide/text.html
• PostgreSQL string functions (lower, trim): https://www.postgresql.org/docs/current/functions-string.html
• Collibra overview of data quality dimensions (consistency context): https://www.collibra.com/blog/the-6-dimensions-of-data-quality
• DAMA International DMBOK overview: https://www.dama.org/cpages/body-of-knowledge

Merch on Etsy

Open store →

Boxplot Mouse Pad, Data Science Mouse Pad, Analytics Mouse Pad, Box Plot, Whisker Plot View on Etsy → Mute Mouse Pad, Data Science Mouse Pad, Analytics Mouse Pad, You're On Mute View on Etsy → Good Cheap Fast White Ceramic Coffee Mug, Data Science Mug, Data Mug, Analytics Mug, Statistics Mug, Programming Mug, Gift Mug, Coffee Mug View on Etsy → You're On Mute White Ceramic Coffee Mug, Data Science Mug, Data Mug, Analytics Mug, Statistics Mug, Programming Mug, Gift Mug, Coffee Mug View on Etsy →