Pulling Data from Google Analytics API, Pt 1: VBA Macros

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

2. Tatvic Excel Plugin

3. Excellent Analytics

4. ShufflePoint

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:

GA-data-fetch1

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.

GA-data-fetch2

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

Advertisements

One Response

  1. Our company is currently trying to figure out methods to best streamline our analytics reporting, this is worth a bookmark to check into more in depth. Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: