How to map ZIP codes to DMA, metro area and region: the one table to join to your orders

The underlying problem is that ZIP codes are mail routes, not areas, so they don't nest inside anything. ~29% of ZIPs touch more than one county (~31% of the population lives in one), so HUD's crosswalk has ~54.6k rows for ~39.5k ZIPs. ~15% of ZIPs have no ZCTA at all, mostly PO boxes and single-business ZIPs, so Census has no population or income for them. Census switched Connecticut from counties to planning regions in 2022, so any older county list fails to join for the whole state. Each of these produces a table that looks complete and is quietly wrong somewhere, which is why most brands either don't have one or have one nobody fully trusts.

The rule that keeps it consistent

Pick one county per ZIP, and derive everything county-level from that county. HUD gives the share of each ZIP's residential addresses in each county, so take the county with the largest share and break ties on total addresses (business-only ZIPs have 0 residential addresses everywhere, so without a tie-break the pick is arbitrary). Then look up the metro, combined area, urban/rural code and county name from that one county.

That way the table always rolls up, ZIP to county to metro, and a ZIP can never sit in a county that belongs to a different metro than the one in its metro column. It loses a little for split ZIPs, but less than you'd expect, since only ~7% of ZIPs have no county holding 80%+ of their residential addresses. If an analysis is sensitive to those, allocate their orders across counties using the HUD ratios.

What to do about it

1. Build the master table once, as a governed dimension in your warehouse, with one row per ZIP and every geography as a column, so nobody rebuilds the joins for each analysis.

2. Clean the shipping ZIP before joining: first 5 characters, stored as text, leading zeros kept. Checkout data carries ZIP+4, stray spaces and the odd Canadian postal code, and spreadsheets drop the zero from New Jersey and New England ZIPs.

3. Pick one county per ZIP and derive the rest from it, with the tie-break on total addresses.

4. Key state, region and division on the USPS mailing state, since that's what customers type at checkout, and keep the county's state as its own column for the ~20 ZIPs where they differ.

5. Give each decision its column: division for trend reads, DMA for media and test markets, metro for stores and wholesale, urbanicity for shipping and product mix, income for pricing, and state for tax and compliance.

6. Test it before trusting it. 11201 should come back as Kings County, New York, in the New York-Newark-Jersey City metro and the New York DMA, and 90210 as Los Angeles County. Assert one row per ZIP, and that every ZIP's county is a member of its metro.

7. Refresh on a calendar: HUD quarterly or annually, ACS each December, metro definitions when OMB revises them (the current set is July 2023).

If you'd rather have this built, tested and refreshed inside your own warehouse, it's usually a 1-2 week piece of work for us. Happy to walk through how we'd approach it for your setup.

Common questions

How do I map ZIP codes to DMA?

Nielsen owns the DMA definitions and licenses its ZIP-to-DMA list, so there's no free official file. Most brands get it through Nielsen directly, a media agency, or a data vendor that resells it. The free ZIP-to-DMA files online are usually old Nielsen extracts, so check the license before using one commercially. Join on the 5-digit ZIP stored as text, and expect rural Alaska and Puerto Rico to have no DMA.

What's the difference between a DMA and an MSA or CBSA?

A DMA is a TV market, defined by Nielsen from which stations people watch, and there are 210 of them. A CBSA (the umbrella term for Metropolitan and Micropolitan Statistical Areas, so an MSA is a type of CBSA) is defined by OMB from commuting patterns between counties, and there are ~925 in the 50 states and DC. Use DMA for media and CBSA for anything physical, like stores, wholesale and delivery.

Where can I get a free ZIP to county crosswalk?

HUD publishes the HUD-USPS ZIP Code Crosswalk Files quarterly. They're free, and you just need a HUD User account to download them (or a free API token). The ZIP-County file gives the share of each ZIP's residential, business and total addresses in each county, which is what you need to pick one county per ZIP.

Why is my county or state name wrong for lots of ZIPs, mostly in big metros?

Usually a field was looked up with a key that matches many rows. For example, a county name looked up by metro code from a file with one row per county returns whichever county is listed first in that metro. VLOOKUP, INDEX/MATCH and XLOOKUP all return the first match without a warning, and so does a SQL join someone "fixed" with LIMIT 1 or MIN(). Look up each field with the key at its own grain, so county fields come from county FIPS and metro fields from the metro code.

Why did revenue go up after I joined in geography?

You probably joined HUD's crosswalk straight onto orders. It has one row per ZIP-county pair, ~54.6k rows for ~39.5k ZIPs, so every order from the ~29% of ZIPs that touch more than one county gets duplicated. Reduce it to one row per ZIP before joining (or allocate across counties on purpose, using the ratios), and compare row counts and revenue before and after any geo join.

Why aren't my New Jersey or New England ZIPs joining?

Leading zeros. ~8% of US ZIPs start with 0, covering all of New Jersey, New England and Puerto Rico, and Excel or a CSV import turns 02139 into 2139. Store ZIPs as 5-character text everywhere, and clean order ZIPs to the first 5 digits.

Why are all my Connecticut ZIPs blank?

In 2022 the Census Bureau switched Connecticut from its 8 old counties to 9 planning regions as official county equivalents (FIPS 09110 to 09190). Current HUD, Census and USDA files all use the new codes, so a county list from before then fails to join for the whole state. Keep every source on the same county vintage.

Why does my lookup return a plausible but wrong value, only for some rows?

Usually an approximate match. MATCH without a final 0 and VLOOKUP without FALSE default to approximate matching, which only works on a sorted lookup column and otherwise returns a nearby row with no error. It can also work by luck until a refresh changes the sort order. Always use exact match for geo lookups.

Why do some ZIPs have no population, income or lat/long?

They have no ZCTA, which is true for ~15% of ZIPs, mostly PO boxes and single-business ZIPs. Census reports population, income and coordinates for ZCTAs, so those ZIPs have nothing to join to. That's expected, so fall back to county or metro figures for them rather than dropping their orders. Also watch for ACS income values of 250,001, which means "250k or more", and large negative numbers, which mean the value was suppressed.

Why does a DC ZIP show up in Virginia?

HUD maps some federal ZIPs, like the Pentagon's, to the county where the building physically sits, so the USPS mailing state and the county's state disagree for ~20 ZIPs. Decide which one your state column means (we use the mailing state, since it matches the shipping address) and keep the other as its own column.

Which urban/rural definition should I use?

USDA's RUCA codes (by ZIP, based on commuting) describe a customer's immediate area, and the NCHS scheme (by county, based on metro size) describes the market around them. They give very different answers, ~74% of Americans in "Urban Core" ZIPs against ~31% in "Large central metro" counties, so pick the one that fits the question and label which one a chart uses.