A problem with the Australian Google Analytics service is it does not aggregate the geographical information on a State by State basis.
A bit of knuckle grease and lateral thinking Kerrie Smith and I have been able to solve this problem.
Step 1:
- Copy the Map Overlay ‘Country/Territory Detail’ table data
[tip:set ’show rows’ to 100 at the bottom right of page] - Paste the data into a spreadsheet
[tip:I first pasted the data into MS Word first & then copy-paste just the table data into the spreadsheet. This allowed me to keep the data in a table structure]
Step 2:
- Get the Australian postcode information
[tip:use this spreadsheet of Australian Postcodes and example Google Analytic data 320kb] - Paste it in next to your Territory detail.
[tip: make sure no leading or trailing spaces beside the city names]
Step 3:
- Associate the city name with the state / postcode using the excel function VLOOKUP()
[tip:my formula =VLOOKUP(G3, $A$2:$C$13584, 2, FALSE)] - Copy the formula to all the cities listed in the Google Analytics data.
Step 4:
- Sort by State
- Total each value on a State by State basis.
- Graph until your heart’s content…
Caution: There will be distortions if Google Analytics lists a town that exists in more than one State.
3 Comments
A small tip: under the report title (and above the map) there is a button called “export” - CSV or TSV are probably the most useful for this.
Ideally, of course it would let you auto-import that data into a Google Docs spreadsheet…..
You’re right - thinking back - I reckon I did precisely that. One curious ‘feature’ was the amount of data exported was related to the number of rows shown. The export did not default to ‘all’.
Some other problems to be aware of:
* If the town is not spelt correctly, then it will not find it in the reference table.
* Check for Variations - eg Albury Wodonga | Albury-Wodonga | Albury/Wodonga really should be either Albury or Wodonga but not both.
* If there are two Australian towns of the same name, the first one will be used to return the State information - eg Abbotsford is in both NSW and Vic. Out of ~13000 towns, 1600 names are shared across different States.
* If the town appears to be spelt correctly but does not work then check if there are any stray spaces before or after the your town names.
* If your town name is not specified or not Australian, it will produce an #N/A not available error.
Post a Comment