State and province codes are the quiet failure in geographic reporting

A regional breakdown for a North American show came back with California at an implausible share of the audience, and the show director wanted to know what had gone right. Nothing had. The registration file held state and province codes in one column, the Canadian records carried CA for Canada in that column because a partner's export had put the country there, and the report had counted every one of them as California.

Nobody noticed for two editions. The number was large, plausible and on brand for a show with a strong West Coast base, which is exactly the kind of error that survives review.

What a bare state code actually tells you

A two letter subdivision code with no country attached is an incomplete fact, and the incompleteness is invisible because the value looks well formed.

CA is California in a United States address and Canada in an ISO country field. IN is Indiana and India. DE is Delaware and Germany. GA is Georgia the state and Gabon. MT is Montana and Malta. PA is Pennsylvania and Panama. Every one of those pairs will appear in the registration file of a show with an international exhibitor base, and every one of them passes a not-null check, a length check and a lookup against a list of United States state abbreviations, because it is a valid member of that list.

The failure mode is not a rejected record. It is a record that sails through and lands in the wrong bucket, which is the reason this is worth a specific control rather than general vigilance.

Store the subdivision code with its country in it

ISO 3166-2 reached its fourth edition in August 2020, alongside the other two parts of the series, and its scope covers guidelines for implementing and maintaining country subdivision codes for any application needing current subdivision names in coded form.

The definition worth memorising is its own. The standard describes a country subdivision code as composed of a maximum length of six characters, starting with the alpha-2 country code, followed by a hyphen, and up to three characters from A to Z and 0 to 9 as a code within the country.

That single design decision removes the whole class of error above. US-CA and CA-ON cannot be confused with each other, because the country is carried inside the value rather than assumed by whoever wrote the query. A subdivision code is self describing, and a bare abbreviation is not, and on a portfolio where files arrive from different countries in different formats that difference is worth the migration.

The introduction to the 2020 series states the division cleanly: part 1 holds current country names, part 2 holds the principal administrative divisions of the countries in part 1, and part 3 holds names that have gone. Your subdivision column depends on your country column being right first, which is the normalisation job K12 covers, and running them in the other order produces subdivision codes prefixed with a country you have not yet agreed on.

Why does the postal abbreviation not do this job?

Because it was designed for a different purpose and does that purpose well.

The United States Postal Service publishes Publication 28, Postal Addressing Standards, in a revision dated October 2024, and its appendix of two letter abbreviations opens with the instruction to use them when addressing mail. The appendix covers states, territories and possessions, including AS for American Samoa, FM for the Federated States of Micronesia and MH for the Marshall Islands. The abbreviations exist so that a city name, a state and a ZIP code fit inside the character budget of a printed last line.

That is a real constraint and a good standard for the job it does. It carries no country, because a domestic mail stream does not need one. Borrowing it as a warehouse key imports an assumption that every record is American, and the day that assumption breaks is the day a partner sends you a file that is not.

Keep the postal abbreviation as the raw value, because you will need it again for labels and for matching against address files. Derive the subdivision code beside it and point the reporting layer at the derived column.

Validate the prefix against the country field

The check is one line of logic and it catches the whole family of problems: the first two characters of the subdivision code must equal the record's country code.

Run it on a file of 12,900 United States registrations that carry a subdivision value. Suppose 214 fail, which is 1.7 per cent. That number by itself is a curiosity. Group the 214 by source system and it becomes a task: if 168 of them arrived from one partner feed, you have a mapping error in one integration rather than a general data quality problem, and one conversation fixes 168 of the 214, which is 78.5 per cent of the failures.

Report the mismatch count per source every load, and keep the failing rows queryable rather than discarding them. A steward looking at 168 rows from one feed can usually tell in five minutes whether the partner is sending Canadian provinces in a state column or sending country codes in it, and those two diagnoses lead to different requests.

There is a second validation worth adding once the first is clean. Check that the subdivision code exists in the list for that country, not merely that the prefix matches. US-XQ has a correct prefix and is not a state, and a typo generates exactly that shape.

How do you build the codes from what you already have?

Nobody starts with a clean subdivision column, so the first pass is a derivation from the columns you do have.

Three inputs carry most of the load. The country code, which you should already have from normalisation. The raw state or province string, which is usually a postal abbreviation but is sometimes a full name and occasionally both in the same column. The postcode, which in several countries determines the subdivision on its own.

Run them in that order and count what each stage recovers. On a file of 12,900 United States registrations, suppose the postal abbreviation resolves cleanly for 11,700 rows, the full name lookup picks up another 640 where somebody typed California instead of CA, and a postcode based fallback recovers 310 more from rows where the state box was blank but the ZIP code was not. That leaves 250 rows, or 1.9 per cent, with no subdivision at all.

Publishing those four numbers separately is more useful than publishing the coverage rate on its own, because each stage points at a different fix. A large full name bucket means the form should offer a dropdown. A large postcode fallback means the state field is optional when it should not be. A large residue means neither, and those rows go to a person.

Keep the derivation deterministic and rerunnable. If a steward corrects a row by hand, mark it as a manual override so the next run does not overwrite the correction, which is the single most common way this kind of derived column loses the work somebody put into it.

What about the records that never had a subdivision?

A large share of any international registration file will have no subdivision at all, and forcing one is worse than leaving it empty.

Many countries have subdivisions that no attendee thinks of as their address. Someone in London writes London, which is a city, and the subdivision underneath it is a set of boroughs that means nothing in a sales conversation. Someone in Singapore has no subdivision to give. A form that demands a state from every registrant collects a lot of fiction, and the fiction is harder to detect than a blank.

The workable position is to require a subdivision only where the country's addressing conventions use one and your reporting actually needs it, which for most shows means the United States, Canada, Australia and a handful of others. Everywhere else, the country code carries the geography and the postcode pattern for that country that K14 covers gives you a second, finer signal where it exists.

Where this stops

Subdivision codes fix the collision and leave two problems standing.

Subdivisions change. Countries reorganise their administrative divisions, and the code list changes with them, which means a five year series can contain codes that are no longer current and codes that have been reassigned. Anyone building a regional trend across editions needs to decide whether to restate history to the current list or to report each edition on the list in force at the time. Both are defensible. Silently mixing them is not, and mixing them is the default behaviour of a lookup table that gets refreshed without a version.

The bigger limit is that an administrative division is often the wrong unit for the question being asked. A show director asking about the Midwest, or about a two hour drive time from the venue, is asking about a region that no standard defines. Subdivision codes are the right storage for the underlying fact and the wrong answer to that question, and the mapping from codes to commercial regions is a business decision your team owns, writes down and keeps in the unified data record beside everything else.

Start this week by running one query on your last edition: count records where the country field says one thing and the first two characters of the state or province field say another, grouped by source system. If your subdivision column holds bare abbreviations and the query cannot be written, that answer is more useful than the count would have been.