Consolidate the country/region names to match the Legal recommended data source

[data-sync-user]

In alignment with the guidance stated in this Wiki, please consolidate the field names and values for the country/region names we are using.

Requirements to consider

• The field name ‘Region’ should be switched to ‘Continent’ (to capture values like Asia, North America, Antarctica, etc.)
• The field name ‘Country’ should be switched to ‘Country or Region’
• Certain 'country or region' names in the Legal-recommended (see the Wiki link above) GENC list are different from the country names currently in the data source such as:
  • ‘Taiwan’ over the currently used ‘Taiwan, province of China’
  • ‘Russia' over the currently used ‘Russian Federation’

Context

When we rolled out the new version of the KPI dashboard which now has regional breakdowns of DAU, a Mozillian requested changing ‘Taiwan, province of China’ to ‘Taiwan’. [~accountid:6345617db391eab61f71c0a2] consulted Legal who pointed the team to the Wiki page and suggested we implement the changes outlined above.

┆Issue is synchronized with this Jira Story

[data-sync-user]

➤ Misun Mizener commented:

Assigning to Alexander Nicholson as I understand you helped with the current country mapping dataset. Tagging George Kaberere for context (as this came up during the Beyond DAU sprint discussions).

[data-sync-user]

➤ Sean Rose commented:

I have thoughts/concerns:

• How far-reaching/stringent will the requirement to change “Country” fields to “Country or Region” be? Looking at the BigQuery ETL code I currently see country_name (or equivalent) columns in at least 68 tables/views, and hundreds more that are just country (though a lot of the latter appear to be country codes).
• I’d advise we be careful when making changes to the country_codes_v1 tables as those are heavily ( https://mozilla.acryl.io/dataset/urn:li:dataset:(urn:li:dataPlatform:bigquery,mozdata.static.country_codes_v1,PROD)/Lineage?filter_degree___false___EQUAL___0=3%2B,2,1&is_lineage_mode=false&page=1&unionType=0 ) used ( https://mozilla.acryl.io/dataset/urn:li:dataset:(urn:li:dataPlatform:bigquery,moz-fx-data-shared-prod.static.country_codes_v1,PROD)/Lineage?filter_degree___false___EQUAL___0=3%2B,2,1&is_lineage_mode=false&page=1&unionType=0 ) in Looker (and also potentially elsewhere like Looker Studio and Redash, though we have less visibility into that). For Looker we can update LookML files and use Looker’s search & replace feature ( https://cloud.google.com/looker/docs/content-validation#using_the_content_validator_to_find_and_replace ) for non-LookML content, but in other cases we’d be asking people to fix breakages themselves.
• In country_codes_v1, with the region_name column presumably being renamed something like continent, should the subregion_name column be renamed something like continent_subregion?

[data-sync-user]

➤ Alexander Nicholson commented:

{quote}Certain 'country or region' names in the Legal-recommended (see the Wiki link above) GENC list ( https://nsgreg.nga.mil/registries/browse/results.jsp?registryType=genc&registerField=GEC&itemTypeField=fgp&entryTypeField=all ) are different from the country names currently in the data source such as:

• ‘Taiwan’ over the currently used ‘Taiwan, province of China’
• ‘Russia' over the currently used ‘Russian Federation’{quote}

I’ll do this in a PR shortly

{quote}* The field name ‘Region’ should be switched to ‘Continent’ (to capture values like Asia, North America, Antarctica, etc.){quote}

Looking into this currently. It seems since https://github.com/mozilla/bigquery-etl/pull/5562 ( https://github.com/mozilla/bigquery-etl/pull/5562|smart-link ) , all the non-continent regions have been removed, so continent might be more accurate also. This will require some coordinated changes.

{quote}* The field name ‘Country’ should be switched to ‘Country or Region’{quote}

This one is more complicated. Echoing some of Sean’s comments below, a few quick questions Misun Mizener:

1. For users of the core country utils, the field shouldn’t appear as “Country” by default. We don’t have a country field name in the core BigQuery table, it’s called country_codes_v1.name. In the Looker View, it’s also called name, though it could appear as Countries.name due to the Looker view being called Countries. We could change this view to something like Country Mapping or similar to the table Country Codes. Would this be sufficient? As it is right now by default it should simply show up for end-users as name/Name.
2. In terms of field names in tables, as Sean mentioned, we could do a one-time search for specifically country_name/country in a few repositories, but this won't necessarily prevent people from renaming them in future downstream tables/one-off queries. Field names in tables don't necessarily appear as-is in user-facing analysis anyway (as shown by the labels applied in this dashboard https://mozilla.cloud.looker.com/dashboards/1784 ( https://mozilla.cloud.looker.com/dashboards/1784?Date%20for%20YoY%20Comparison=2%20days%20ago )), could this be a guidance/documentation issue?