As mentioned back in April, the release of the Google Analytics Data Export API was bound to lead to a variety of options for accessing/manipulating GA data without going through the interface. More recently, the Google Analytics Blog did a quick list of some of the approaches that are now available for extracting GA data into Excel. There are some real advantages to being able to pull Google Analytics data into Excel – especially for larger sites with lots of rows of relevant data. Being a ‘roll up the sleeves’ kinda guy, I thought I would test these various methods and assess some of the pros/cons of each. There are 4 tools mentioned in the Google Analytics Blog:
1. VBA Macros (with template by Mikael Thuneberg) – described in this blog post
I plan to look at them each in some detail, so I will do a separate blog post on each one, starting with…
VBA Macros (demonstrated by Mikael Thuneberg)
This is the most straightforward approach and requires no additional tools other than Excel. Mr. Mikael Thuneberg has provided a good explanation of how it works in his blog Mikael’s Excel Page and – even better for those of us not well-versed in VBA – a spreadsheet template that can be used to fetch data. So the easiest way to get started is to download GA_data_fetch.xls from Mikael’s page and fill in the boxes. Below is a screenshot of what I got with my login info and the default report settings:
You can use Google’s Dimensions & Metrics Reference to find other dimensions and metrics that can be added (using & between them), in which case the possibilities are almost endless.
The worksheet uses an array formula to display multiple dimensions and metrics. It is set up to display 34 rows of data but the VBA module can fetch up to 10,000 rows (which I think might be the max allowed by GA in 24-hr period – see the API Quota Policy). In order to display a larger set, you will have to expand the area of the array formula. This can be easily done:
1) select the entire expanded area (including existing data)
2) F2 to enter edit mode
3) CTRL+SHIFT+ENTER to copy array formula to empty cells.
A very flexible solution that quickly gets data into Excel for further manipulation. You can set it up to pull data from different profiles into different worksheets in the same Excel file to have easy access to them. And it’s free!
Next up: Tatvic Excel Plugin