Country field normalisation and the two letter codes that end the argument

The international share of the audience is a number that goes on the first slide of the post-show report, and on a lot of shows it is quietly wrong. The reason is visible the moment you group the registration file by the country column: UK, U.K., United Kingdom, England, Britain, GB and a blank all appear, and every one of them is the same place. Country field normalisation is the work of turning that pile into a single code before anybody computes a percentage from it.

It is a small job. It is also the one that decides whether two shows in the same portfolio can be compared at all.

Why does one country arrive in five spellings?

Each spelling has an origin, and knowing the origin tells you where to fix it.

A free text box on a registration form produces whatever the attendee types, including their nation rather than their state. That is where England, Scotland and Wales come from, and no amount of downstream cleverness stops it recurring.

A dropdown produces a clean value from whichever list that system shipped with, and no two systems ship the same list. One offers United Kingdom, another offers United Kingdom of Great Britain and Northern Ireland, a third offers Great Britain.

An import from a partner or a media house arrives with codes rather than names, and the codes follow that organisation's own convention. Eurostat, in its published glossary of country codes, uses UK for the United Kingdom and EL for Greece, both of which differ from the ISO codes, and a file from a European statistical source will happily carry those.

A billing system produces a country from an address that somebody typed for a courier, which is a different question from where the attendee is based.

Those four sources need one target, and the target should be a code.

Map to ISO 3166-1 alpha-2 on ingest

ISO 3166-1 reached its fourth edition in August 2020, and its scope is deliberately narrow: the document "specifies basic guidelines for the implementation and maintenance of country codes", intended "for use in any application requiring the expression of current country names in coded form". It defines three code types for each country: an alpha-2, an alpha-3 and a three digit numeric code. Its own terms section notes that the alpha-2 form is generally recommended for representing country names, that the alpha-3 exists where a specific need has been identified, and that the numeric code is for cases where language independence is needed.

For an event business the alpha-2 is the right default, and the reason is practical rather than principled. It is two characters, so it survives a CSV round trip, a spreadsheet that helpfully strips leading zeros, and a badge printer's field limit. The numeric code does not survive the spreadsheet.

The 2020 edition also made something explicit that matters for a mapping table. Every alpha-2 code element carries a status, which the standard defines as information on whether the element is assigned, unassigned, or reserved transitionally, exceptionally, or for an indeterminate period. A code you see in a file is not automatically a current country code, and the status is what tells you which kind it is.

Settling the UK argument with a published list

Somebody on the team will insist that UK is the code. It is worth settling this once with the document that decides it.

The ISO 3166 Maintenance Agency publishes a list of reserved code elements, and in the edition dated 28 July 2003 the United Kingdom appears under exceptional reservations with the stated justification "Lest the code element UK be used for any other country". Exceptional reservations, the same document explains, are made at the request of national member bodies, governments and international organisations to support a particular application, and any further use needs the agency's approval.

So UK is real, it is reserved, and it is not the assigned country code. GB is. The operational consequence is small and specific: put UK in your alias table pointing at GB, accept it on the way in, and never write it into the code column. Do the same for EL pointing at GR, because the file from the European statistical source will contain it.

That pattern generalises. Your mapping table has two columns that matter, the raw value as it was seen and the alpha-2 it resolves to, plus a third recording where the alias came from so a future colleague can tell a deliberate decision from a guess.

What do you do with a country name that no longer exists?

A portfolio with five years of history has files older than some of the codes in them, and an acquisition can hand you files older than that.

ISO 3166-3 reached its third edition in August 2020 and covers exactly this case: codes representing non-current country names, which the standard defines as the names deleted from ISO 3166 since its first publication in 1974. The 2020 introduction to the series sets out the division of labour plainly, with part 1 holding current country names, part 2 holding the principal administrative divisions of those countries, and part 3 holding the names that have gone.

The practical use is narrow and real. When a 2014 file offers a country value that resolves to nothing in the current list, part 3 tells you whether you are looking at a country that stopped existing or at a typo, and those two findings lead to different actions. One gets an alias to the successor state and a note on the record. The other goes in the queue.

Count the values that will not map, and publish the count

Normalisation is only useful if somebody can see how much of the file it failed on.

Take a registration file of 38,400 rows for one edition. After the alias table has run, 611 rows have a country value that resolved to nothing, which is 1.6 per cent. That number on its own is mildly interesting. What makes it actionable is sorting the 611 by raw value and looking at the top of the list: if the five most common unmapped values account for 470 of them, adding five aliases takes the unmapped count to 141, or 0.37 per cent, in about twenty minutes.

Report both numbers, before and after, and report them per source system. A single source producing most of your unmapped values is a form problem with a form fix, and it will keep producing them next edition unless somebody changes the form.

Blanks deserve their own line in that report, separate from values that were present and unrecognised. A blank means the form did not require the field or the import dropped it, and the remedy is upstream. An unrecognised value means somebody typed something your table has not seen, and the remedy is an alias. Rolling the two together into one completeness figure hides which of the two you have, and on most shows the balance between them is lopsided enough that the combined number tells you almost nothing. If 611 rows failed to map and 2,100 rows were blank, those are two different projects with two different owners.

One more habit worth building. Every time you add an alias, add it with the date and the file that prompted it. Six months later somebody will ask why Britain resolves to GB and Great Britain resolves to GB but Britannia does not, and the answer will be that nobody has seen Britannia yet, which is a perfectly good answer as long as the table can prove it.

The same discipline applies one level down, where subdivision codes carry their own country prefix and K13 shows why that prefix stops a whole class of collision, and one level further out, where a postcode pattern is chosen by country code in K14. The country code is the key that makes both of those checks possible, which is a good argument for doing this first.

Keep the original, for the same reason as always

Write the code into a new column. Leave the typed value where it is.

A geographic breakdown that somebody disputes is settled in about a minute if you can show the raw string, the alias that matched it and the code it produced. It becomes an afternoon of forensics if the original is gone. The rule holds across the whole unified data record, and country is the field where it gets broken most often, because the mapping feels too obvious to be worth auditing.

Where this stops

A country code is a clean answer to a question the form asked badly.

Ask an attendee for their country and some of them give you their employer's headquarters, some give the office they work from, and some give the country they are flying in from, and all three are defensible readings. Normalisation makes those three answers comparable as strings while leaving them incomparable as facts, which is a real limit and one worth writing in the methodology note beside the international share.

There is a second limit that bites on portfolios. Codes are stable, and the entities behind them are not. A country can split, and when it does your five year trend has a genuine discontinuity that no mapping table should paper over. The honest treatment is to keep the historic codes as they were recorded, map them forward for current reporting, and show both when the comparison spans the change.

This week, group your last registration file by the raw country column, sort descending by count, and read the first fifty rows. You will know within ten minutes whether your international share is built on a normalised field or on a text box, and the fifty values in front of you are the alias table you need to write.