About a year ago, I created a web-based map of the visitors to a web site. I've been asked to switch it from using Urchin and web server log data, to retrieving data from Google analytics.
When I was using the older data sources, I had the IP address of each visit, and I would use the IP address to look up city name / country code / latitude / longitude. Sometimes the city was unknown in the database I was using, but I would almost always get the country code, with a centroid latitude/longitude.
This is important data, because I would put markers on a map showing where visitors were from, and sometimes a large proportion of visits from a given country were from unknown cities. For example, I have results like
Country: Anguilla Visits: 2 City: (not set) Latitude: 0.0000 Longitude: 0.0000
This is the only data point from Anguilla. So if I don't plot it, Anguilla is completely omitted.
Now with the Google Analytics Data Export API, IP address is unavailable. Thankfully, city name / country name / latitude / longitude are available most of the time, so I don't have to look those up by IP. But in the cases when the city is unknown, but country name is given, lat/long is given as 0.0000,0.0000.
How can I plot these country points on a map? It's difficult to look up country locations in a database because the country name is given, rather than a country code. Country names of course are somewhat variable. It really surprises me that Google would not make country code available at all, but I don't see it listed under the allowed "dimensions" for an API query.
I guess I could build up my own lookup table, fine-tuned to match Google's country names, but it would be subject to breakage if Google ever changed spelling or phrasing (e.g. "Democratic Republic of the Congo" vs. "Congo-Kinshasa").
Thanks for any suggestions.