Normalize Territory Codes Across ISO, IFTA, and Platform-Specific Formats

Territory codes look like a solved problem until you try to push the same avails file to three platforms. ISO says GB. Amazon wants UK. Some older IFTA-based contracts say United Kingdom. Netflix accepts either the code or the country name depending on the endpoint. If your source-of-truth is inconsistent, the third platform always rejects the file.

The fix is a normalized internal representation plus a platform-specific export mapping. Store ISO α-2 internally; translate at the boundary when you send. This tutorial builds the lookup sheet that does the translation.

It’s also the prerequisite for the Territory Group Exceptions workflow — you can’t expand “EMEA” into member territories if the members aren’t using consistent codes.

Step 1: Pick a Canonical Code System

ISO 3166-1 alpha-2 is the right default. It’s two letters, covers every territory, and every platform can translate from it. The exception: a handful of older IFTA contracts use full names. Convert those at ingest.

Step 2: Build the Lookup Sheet

Create a sheet called Territory Lookup with these columns:

A: ISO α-2 | B: IFTA Name | C: Amazon Code | D: Netflix Name | E: Apple Territory | F: Notes

Seed rows with the top 30 territories you deal with. Example rows:

ISO	IFTA	Amazon	Netflix	Apple
US	United States	US	United States	USA
GB	United Kingdom	UK	United Kingdom	GBR
FR	France	FR	France	FRA
DE	Germany	DE	Germany	DEU
JP	Japan	JP	Japan	JPN

The goal: one row per territory, every platform’s variant in one glance.

Step 3: Add VLOOKUP Translations to Your Avails

In your avails sheet, add columns for each platform you push to:

=VLOOKUP(B2, 'Territory Lookup'!A:C, 3, FALSE)   // Amazon code
=VLOOKUP(B2, 'Territory Lookup'!A:D, 4, FALSE)   // Netflix name

Where B2 is the ISO α-2 code. Wrap in IFERROR(..., "MAPPING MISSING") so you catch territories that aren’t in the lookup yet.

Step 4: Sweep for Ambiguous Source Codes

If your existing avails have UK instead of GB, you need to fix the source. In the avails Territory column:

=IF(COUNTIF('Territory Lookup'!A:A, B2) > 0, "OK",
   IF(COUNTIF('Territory Lookup'!B:B, B2) > 0, "FIX: Name not ISO",
   IF(COUNTIF('Territory Lookup'!C:C, B2) > 0, "FIX: Amazon code not ISO",
   "UNKNOWN")))

Anything not flagged OK goes on the cleanup pile. Typical patterns: UK → GB, USA → US, Germany → DE.

Step 5: Handle Region Codes

Some contracts use EMEA, APAC, LATAM. These aren’t territories — they’re groups. Create a separate sheet Region Members that maps each region name to its member ISO codes, one row per member. When expanding, JOIN or FILTER the region members for the grant.

Common Issues

• Deprecated codes. ISO deprecates codes occasionally (e.g., CS for Serbia and Montenegro). Keep a “Deprecated” sheet mapping old → new so you can normalize historical contracts.
• Overseas territories. Is Réunion RE or part of FR? Depends on the platform. French overseas territories are a classic edge case — the lookup must encode which platforms treat them separately.
• Disputed territories. Taiwan, Kosovo, Hong Kong — different platforms have different policies. Flag disputed-territory rows with a Notes entry so the right person reviews before you ship.

Pair With

• Validate an Avails Sheet Against IFTA + EMA Rules — validator uses the ISO codes you just normalized.
• Territory Group Exceptions for Windowing — expands the regions you just mapped.

Download the Template

The Avails Data Quality Scorecard includes a Territory Lookup tab seeded with the top 50 territories. Extend it to your needs.