Google Analytics API made easy - Google docs Magic Tutorial

Update (May 30th 2014) The new Google spreadsheets have replaced the script gallery with add-ons, so you won't be able to install the Magic script from Gdocs. You'll need to copy paste the original script which can be found here into your script editor.

Even if you're not familiar with the Google Analytics API, chances are that you've probably needed access. Maybe it's because you need 5000 results from the GA interface, but your browser keeps crashing. Maybe you need more than 5000 URLs and you're not in the mood to hack the URL in order to get it. Or maybe you just wish that you could perform month over month growth calculations quick and easily.

Well, today's tutorial is about showing you how to get the data you need quick and easily - even if you don't know your variables from your arrays. A clever Googler, Nick Mihailovski put together the Google Analytics Magic script which integrates with Google docs back in August 2012 - and it's the best thing since sliced bread. Except, I've noticed that many marketers either don't know about it, or find it too daunting.

I'm here to demystify the whole process, give you some code examples and show you how this is going to automate reporting - all in the interest of keeping you at the pub longer.

Why do you need this?

If you're in digital marketing, this will help you do your job better, faster, and hopefully lighten the workload.

It's quicker than the GA interface, and will work overtime while you're sleeping.

The API allows you to return up to 7 dimensions at a time! Yes, that's right - you can have Operating system, country, city, landing page, keyword, and device in one simple report!

The data goes directly into a Google docs spreadsheet where you can "Excel" to your heart's content, share with clients / colleagues, store the data in the cloud, and pull as much data as you like, and more.

Combine your data with powerful Google docs spreadsheet functions like ImportXML, or even write your own JavaScript to email you stats.

It's FREE, and unlike many other Analytics tools out there, doesn't limit you with "their" functions, clunky dialog boxes, and frozen Excel sheets from excessive calculations.

You can do this in 1 hour or less, no excuses! Anyone can do this, and you'll be thanking yourself daily once you understand the concept. I'll make this dead easy for you, so let's go!

In the dialog box search, type in "google analytics reporting" and look for the "Google Analytics Reporting Automation (magic script). Click on install.

It will ask you to authorize the script, go ahead and do that. If it returns an error, just double check that the script gallery dialog box shows a green "installed" box. Refresh your page to see the new custom menu "Google analytics" within the spreadsheet.

Click on tools, script editor.

It will open up a new screen, go to Resources menu and choose "Use Google APIs"

Within the new dialog box, click on the tab that says "off" next to Google Analytics API to turn it on. It's the second option.

Filters & Regular Expressions - How to get specific items

If I wanted to match any page that contained "how", in the Filter field, I would use:

ga:landingPagePath=~how

Let's break this down:

ga:landingPagePathIs the dimension I want to filter from, because I only want to report on specific pages.

=~Lets the API know I want to use Regular Expressions

how Means that I want to match any landing page that contains "how" in it. FYI -This is pretty lazy RegEx but it still works.

If you need help with Regular expressions, LunaMetrics has a great tool and guide for you to start learning. On to more advanced...

Let's say I wanted to retrieve all pages that had "how" or "what" in them, all I'd need to do is add the pipe (|):

ga:landingPagePath=~how|what

More advanced, let's go ahead and get all pages within the /opinion/ category using the carat (^) to signal the "start of":

ga:landingPagePath=~^/opinion

Even more advanced, let's use the AND (;) & OR (,) operators to create multiple filters. Here, I'm going to pull out any landing pages containing the word "how" AND from Canada. Note, OR takes precedence over AND and each regular expression can't exceed 128 characters, so chain your requests together with multiple filters :)

Sorting metrics by ascending / descending order

If you remember from the last video, my visit count was pretty messy. In order to sort my results from highest to lowest (Descending order), use a hyphen (-) before your metric in the sort field. In the example below, I've asked the API to return all visit stats from highest to lowest:

-ga:visits

If you wanted descending order, probably for ga:month for example - just use ga:month without the hyphen.

Step 3 - Get your data and understand the Google docs setup / output

Now that you understand what each field represents, and how to structure the parameters - let's finally get some data and see how this beauty works.

value1 - Required, for multiple reports, the number needs to increment

type

core - Required, no need to touch this

ids

ga:58087753 - Your profile ID, Required

start-date

2013-10-01 - Either specify the start / end dates or use last n- days field

end-date

2013-10-30

last-n-days

30 Optional - do not use if you've specified start / end dates

metrics

ga:visitors,ga:newVisits,ga:visits

dimensions

ga:landingPagePath

sort

-ga:visits

filters

ga:medium==organic

segment

Optional, you can get built in segments from the query explorer or even use your own custom segments

start-index

Use this when you need to return more than 10,000 results - read more here

max-results

10 - Number of results you wish to retrieve up to 10k, then you need to use start-index and page through.

sheet-name

Dave This will become the sheet name for this query

Step 4 - Extend and get fancy (report month over month increases while you sleep, and get it emailed to you)

Before you watch the next video, there's something I need to say: I am not a professional programmer. I'm an amateur but I always test it to make sure it works :)

I'm going to be adding some code to the Magic script that will allow me to automatically update the start / end dates (for month on month stats), and a line or two that will email me every month. Below, you'll find the custom code that gives the script a bit more of a punch:

Watch the video, this one is a bit longer at 9 minutes, but I tried my best to make it quick:

Here's the video explaining exactly what you need to do to get this up and running for you:

Important FAQ's

Q: I've just shared my spreadsheet with someone else, but they can't get the data / don't see the Google analytics menu?

A: They might need to reinstall the Google Analytics Magic script from the gallery. Only authorized accounts can pull data from GA profiles to which they've been authorized. For example, if I share my spreadsheet with you, and you install the GA Magic script, you still won't be able to get data for Distilled.net - unless we gave you access, but that's highly unlikely ;)

Q: My second report that I copy pasted doesn't generate a new sheet!?

A: Ensure that your second report values are reflective of it's position. In the fields, ensure that is says Query2, Value2, and the sheet name is unique.

Q: I've received an error message saying sheet limits exceeded!

A: 400,000 cells or 256 columns of data is allowed currently. Sorry, just the way it is right now, you can read more on limits here.

Q: Script execution time exceeded error

A: The maximum time a script can run is around 5-6 minutes. Reduce the amount of reports you run in one spreadsheet.

Q: It's not emailing me!

A: Did you ensure to add your email address at the bottom of the script? Tools > Script editor, go way down to the bottom and change the email address. Make sure you've changed the URL of the spreadsheet too. Double check that your triggers are still intact as well.

Q: My formulas aren't updating automatically!?

A: onOpen is a built in function in every Google doc that will refresh every formula in the spreadsheet. You need to add a script trigger for onOpen that runs AFTER the getData function so that your formulas / charts refresh without you having to go into the spreadsheet.

Q: The data just doesn't make sense! Am I getting bad data?

A: Double check the output from the report (the sheet), and check if the data has been sampled or not (you'll see TRUE or FALSE). If it's sampled, you might not be getting the real picture - and it's likely due to combining too many dimensions / metrics. Otherwise, you might be trying to compare apples and oranges (hit metrics versus session metrics), like pageviews and visits. You might want to read this post by Avinash to get you up to speed.

That's all folks, if you have any problems getting up and running I'll do my best to answer questions. Feel free to contact a fellow "Magic Scripter" Matt Bennet with more detailed questions. Remember that this is only a basic tutorial, and Google docs is capable of doing pretty much anything (ImportXML, a CMS, SEO tools, and even a Twitter archive), so please do build on this.