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.
- 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!
Step 1 - Install the script and connect the Google Analytics API under 2 minutes
Watch this video:
Or follow these instructions:
- Open up Google drive and sign in if you haven’t already
- Create a new spreadsheet
- Tools menu > Script gallery
- 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.
- At the bottom of the dialog box, click on the Google APIs Console link
- Find the Google Analytics API and turn it on as well. Close the window when done.
- Go back to your Script editor screen and click OK. Close that as well.
- Back in your spreadsheet, click on the Google Analytics top menu and ”Create Core Report“ twice. The first time it will request authorization to run, and the second it will run.
- Done. Good work.
Step 2 - Get comfy with query parameters and the Google Analytics Query explorer
Now watch this video: (if it doesn’t work, watch it on Youtube here)
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:
Let’s break this down:
- ga:landingPagePath Is 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.
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 (|):
More advanced, let’s go ahead and get all pages within the /opinion/ category using the carat (^) to signal the ”start of“:
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 :)
Cool! Okay, here’s the official reference documentation on filters from Googleto keep you on your toes.
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:
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.
Get your Query Feed Explorer ready, and keep your Google docs spreadsheet handy. Watch this video:
Breakdown of fields from the core report (In the spreadsheet, Google analytics menu > create core report):
|query1||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|
|last-n-days||30 Optional - do not use if you’ve specified start / end dates|
|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:
Bonus: Free starter spreadsheet - Month over month increases in organic
Open up the spreadsheet here > https://docs.google.com/spreadsheet/ccc?key=0Anqo6cJNPgaQdHdFNGpOYjJOMThmSEtIYmstSmxUbmc&usp=sharing
Here’s the video explaining exactly what you need to do to get this up and running for you:
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.
P.S. A big thank you to Peter Small at http://www.salesseek.net/ for pointing out bugs!