1162266 - Add Kosovo (XK) and "Error" to the Countries table

Status: RESOLVED INCOMPLETE

(Content Services Graveyard :: Tiles: Data Processing, defect)

Description

[Matthew Ruttley [:mruttley]]

sbohan discovered a data disparity when querying data for a range of dates, and then the same range of dates but split up by country (and then totalled).

See:

SELECT SUM (impressions) AS impressions
FROM impression_stats_daily
INNER JOIN tiles ON tiles. ID = impression_stats_daily.tile_id
WHERE DATE >= '2015-4-1' AND DATE <= '2015-4-30' AND LOWER (title) LIKE '%booking.com%'

Vs

SELECT countries.country_name, SUM (impressions) AS impressions
FROM impression_stats_daily
INNER JOIN tiles ON tiles. ID = impression_stats_daily.tile_id
INNER JOIN countries ON countries.country_code = impression_stats_daily.country_code
WHERE DATE >= '2015-4-1' AND DATE <= '2015-4-30' AND LOWER (title) LIKE '%booking.com%'
GROUP BY countries.country_name
ORDER BY impressions DESC

(please total the impressions in the second query)

I've dug a little further into this and found it iss from an ISO3166-1-alpha2 (https://en.wikipedia.org/wiki/ISO_3166-1_alpha-2) country and errors.

We currently have some data from Kosovo (Defacto Country code XK) in the impression_stats_daily table. This has no corresponding country name in the countries table so causes data disparities when querying per country (if the full country name is desired in an INNER JOIN from countries). Perhaps we should add it.

Also, there are now a few thousand pings per day on the ERROR country code, it could be worth looking into what causes them.

Comment 1

[Olivier Yiptong [:oyiptong]]

This stems from the fact that we're using ISO3166, and not ISO3166-1-alpha2.

Our Geo DB, MaxMind, doesn't seem to support ISO3166-1-alpha2:

http://dev.maxmind.com/geoip/legacy/codes/iso3166/

Comment 2

[Olivier Yiptong [:oyiptong]]

A country that our Geo DB does not recognized would be classified as ERROR

Comment 3

[Olivier Yiptong [:oyiptong]]

That said, if we do have data from XK, then the geo DB supports more than it claims it does. We can then update our Countries table to match it.

Comment 4

[Olivier Yiptong [:oyiptong]]

> This stems from the fact that we're using ISO3166, and not ISO3166-1-alpha2

I'll eat my tongue. This is not factually correct since ISO3166-1-alpha2 is a subset of ISO3166.
Hmmm. I suppose we need to try to get a better source for all alpha2 country codes, better than this: http://dev.maxmind.com/geoip/legacy/codes/iso3166/

Comment 5

[Takanori MATSUURA]

This bug lies at rest in the graveyard.