Google Analytics Location dimension past limitations
As you know it is possible to extract your Google Analytics data out of Google Analytics by using the Google Analytics APIs. There are several Google Analytics APIs that you can use but today we are going to talk about the Core Reporting API. The Core Reporting API is the one that you use to extract your data. By sending your View id and a list of Dimensions and metrics that you would like to extract for a set date range Google will return the data to you in JSon format.
I have been working on a system here at TARGIT that uses a custom made SSIS data reader to extract Google Analytics data out for use in SSAS. In my extract, I would like to know the location of the users that access my website. In the past we were given several dimension for this they are:
|ga:continent||The continents of property users, derived from IP addresses.||Africa, Americas, Asia, Europe|
|ga:subContinent||The sub-continent of users, derived from IP addresses.||Polynesia or Northern Europe.|
|ga:country||The country of users, derived from IP addresses.||Australia, Austria, Azerbaijan|
|ga:region||The region of users to your property, derived from IP addresses.||Oslo, Ostergotland County, Ostfold (In the U.S., a region is a state, such as New York)|
|ga:city||The cities of property users, derived from IP addresses.||Aalborg, Aarhus, Aartselaar, Abbotsford|
One of the problems we as developers have is the the fact that we are limited to a maximum of 7 dimensions in each request against the Google Analytics API. Due to this limitation in the number of dimensions I have had to think long and hard on what dimensions to extract to get the results needed. Initially I thought that extracting city as part of my main fact query would be enough. I created a second query that would populate my location dimension with Continent, subcontinent, country, region, city and then simply link the city from my main fact table over to the location dimension table.
This didn’t work and here is why……
City names aren’t unique in the world. We can see this by checking the Google Analytics Query Explorer.
I have added a filter for ga:city == Athens just to show you this issue. There are many many duplicate cities. The first Americans weren’t very original and liked to name their cities after old world cities. At the time it was probably a nice idea and a reminder of home. Now its just a nightmare for anyone wanting to create a unique dimension on city name.
About a year ago when I was working on this I ran across a feature request on the Google Analytics issue tracker. The request was made on December 2009 for ga:country to support in ISO Alpha-3, basically returns three-letter country codes. I will have to admit I had never heard of ISO standards. After a bit of Googling and research I agreed with the feature request it would be really nice if Google Analytics supported it. None of this really helped me because Google Analytics had not implemented it, and it was requested back in 2009 so I held out little hope that they would.
Google Analytics Location with ISO support
Now for the good news. On January 21, 2015 the Google Analytics team released some new Dimensions that will help us with this problem. You guessed it ISO and Ids for all of our dimensions.
|ga:subContinentCode||The sub-continent code of users, derived from IP addresses or Geographical IDs. Values are given as a UN M.49 code.||021,039|
|ga:countryIsoCode||The country ISO code of users, derived from IP addresses or Geographical IDs. Values are given as an ISO-3166-1 alpha-2 code.||GR, US|
|ga:regionid||The region ID of users to your property, derived from IP addresses or Geographical IDs.||20398, 21133|
|ga:cityid||The city IDs of property users, derived from IP addresses or Geographical IDs.||1007560 ,1012944|
Now lets check the query explorer again, can I extract just cityid in my fact query and use that to match back to my location dimension table?
Look at that thanks to the new ISO support we now have unique city id’s that we can use to link our data with.
In the past I was forced to limit the degree of my location table to country because I was unable to get a unique city back from Google Analytics. Now I will be able to change my application to allow for data all the way down to city. Thanks to the new ISO support I can link things back correctly.
As always please remember to link and share this, it help me know what you like seeing.