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:


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

Breaking the 500-Row Barrier in Google Analytics

Google Analytics has many strengths as an analytics tool, but it has its limitations. A major one has been the restriction of only being able to export 500 rows of a report at a time.  For example, the screenshot below shows that the report for Site Search Terms has over 22,000 rows, yet we can only select a maximum of 500 rows to display at once (number of rows displayed is the number that will be exported):

GA 500 row limit

GA 500 row limit

It may not be particularly useful to look at all 22,000 rows, but even if we want to get a report on the top 2,000 search terms, we would have to create 4 reports, selecting 500 rows each time, exporting, then moving on to next 500. Tedious – to put it mildly.

Accessing data via the Google API offers one way around this limitation.  However, the Google Analytic Blog has provided a more straightforward solution that anybody can use.  In this Tip for Exporting Rows, it is explained that the 500-row limit can be overcome using these easy steps:

1. Go to the report you want to export.

2. Append &limit=x to the end of the report URL (where x=number of rows you want to export)
Example: https://www.google.com/analytics/reporting/search_keywords?id=6146&pdr=20090727-20090826&cmp=average#lts=1251391512461&limit=2000

3. Hit enter to refresh the page (double-check to make sure the &limit= is still there)

4. Export as CSV.

Voila! Easy and efficient!  Thanks to the folks at the Google Analytics Blog for sharing this trick.

Gettin’ Certified: Google Analytics & AdWords

Observations on Google Analytics IQ and Google Advertising Professional Qualified Individual Exams

In the search marketing business we all work hard to deliver the goods and continuously improve our skills – we need to in order to  stay relevant in a rapidly changing environment and to demonstrate our value to our employers/clients.  We can let the quality of our work speak for itself, but  it can also be useful to have some more official validation of our general knowledge of our domain.  Which is where certification fits in.  Sometimes required by employers,  sometimes essential in establishing credibility with clients, and sometimes just good to have in the back pocket.

Recently I completed two certification exams in order to ‘prove my proficiency’ in the wonderful world of Google: Google Analytics Individual Qualification (IQ) and Google Advertising Professional Exam.  So I thought I’d share my experiences and observations with others who may be pursuing either or both of these designations.

Google Analytics Individual Qualification (IQ)

The Google Analytics Individual Qualification was announced on the Google Analytics Blog in March, 2009.  It is earned simply by passing the Google Analytics IQ test.  This is meant to prove that you have proficiency in using Google Analytics.  The idea is to separate beginners/casual users – who can, after all, make quite effective use of Google Analytics due its user friendliness – from those that leverage GA more fully to enhance organizational performance and teach others to do so.

In order to prepare for the test, Google offers ‘Conversion University‘, delivered via a series of free, short, on-demand online videos.  This is a pretty comprehensive look at most of the key features of GA and provides a good grounding in how to use the program.  It is useful to go through the videos even if you have no intention of the writing the test, especially because the broken into highly digestible chunks.

Although recommended, you don’t need to go through Conversion University to get to the test – you can write the IQ Test anytime.  It is delivered online at your convenience, costs $50 and you have 90 minutes to complete about 100 multiple choice questions.  Once you pass, they send you a colorful certificate that you can print out and hang in your cubicle. Ta da…


Google Advertising Professional Exam

The Google Advertising Professional Exam is one of the requirements for becoming a Qualified Individual in the Google Advertising Professionals program.  (The other requirements relate to mainly to maintaining a minimum level of AdWords account activity.)

Again, Google provides some thorough resources for exam preparation through the AdWords Learning Center.  The Learning Center has a series of online lessons in 9 sections that are available both as video or text lessons.  Personally, I focused on the text lessons, which I expedited by clicking on the ‘Print Section’ link to see the whole section on one page, rather than broken up into a bunch of short pages.  A nice feature of the Learning Center is that there are short multiple choice quiz questions at the end of each lesson, which helps to a) reinforce the learning and b) prepare for the exam, which is very similar in style and content.

As with the Conversion University, the AdWords Learning Center contains a lot of good information on getting the most from AdWords and is useful even if you are not planning to write the exam.  It does, however, also have a lot of information that may not be relevant to you – such as billing policies in foreign countries or tactics for ‘selling’ AdWords to potential clients.

If it is recommended to take review Conversion University prior to taking the Google Analytics test, I would rate is as HIGHLY recommended to go through the Learning Center – at least the quizzes – before taking on the AdWords exam.  When you are ready, you can jump into the exam via the ‘Pro Center’ tab in your My Client Center.

The AdWords exam has a bit of a bad reputation for being sprinkled with questions that are of limited relevance and/or trickily worded to create ambiguous meaning.  This was an assessment conveyed to me by both colleagues and others in the industry, such as the bloggers at SEO Speedwagon (which is the best seo-related blog name I’ve come across in a while).  While there were definitely some of these questions cropping up, I seemed to me that Google may have responded to some of the criticisms and tightened up the test to make it a more accurate reflection of professional competence.  Overall, I found it a pretty accurate reflection of the Learning Center – even to the extent of some Learning Center quiz questions showing up in the exam.

Bottom Line

Based on my experience, and discussions with colleagues, there is about 9-12 hrs of study involved in each of these exams, but may be less depending on your level of knowledge/experience with these programs.

Both Google Analytics IQ and Google Advertising Pro qualifications have an expiry date: AdWords offers a better deal because it is good for 2 yrs, while you will have to retake the Google Analytics IQ exam in 18 mths to retain your status.

Hard to put a value on these certifications but they do represent tangible evidence that you probably know what you are talking about and can enhance the credibility of yourself and your organization.  Hey, if you can get Google to vouch for you, why not go for it?