Google Analytics API made easy - Google docs Magic Tutorial

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!

Step 1 - Install the script and connect the Google Analytics API under 2 minutes

Watch this video:

Or follow these instructions:

  1. Open up Google drive and sign in if you haven’t already
  2. Create a new spreadsheet
  3. Tools menu > Script gallery
  4. In the dialog box search, type in “google analytics reporting” and look for the “Google Analytics Reporting Automation (magic script). Click on install.
  5. 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.
  6. Click on tools, script editor.
  7. It will open up a new screen, go to Resources menu and choose ”Use Google APIs“
  8. 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.
  9. At the bottom of the dialog box, click on the Google APIs Console link
  10. Find the Google Analytics API and turn it on as well. Close the window when done.
  11. Go back to your Script editor screen and click OK. Close that as well.
  12. 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.
  13. Done. Good work.

Step 2 - Get comfy with query parameters and the Google Analytics Query explorer

Open this up first: Google Analytics Query Explorer , and keep the official Google core reporting reference guide handy.

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:

ga:landingPagePath=~how

 

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.

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.

 

ga:landingPagePath=~how;ga:country==Canada

 

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:

 

 -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.

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
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:

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:

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.

Happy reporting!

P.S. A big thank you to Peter Small at http://www.salesseek.net/ for pointing out bugs!

Dave Sottimano

Dave Sottimano

David Sottimano comes from a varied background in Corporate Marketing and Professional Sales. His love affair between the internet and marketing has finally found the perfect balance at Distilled, and continues to flourish each day. He graduated...   read more

Get blog posts via email

37 Comments

  1. Very useful tips. Thanks for sharing. Enhancing productivity by automating reports with methods like this is crucial to seize time. I will be trying these out.

    reply >
  2. love the magic of google doc. Google please increase limits in doc. :-)

    reply >
  3. Thanks David, This was really useful and informative post. I'll definitely be applying some of this and was great to work through.

    I found a couple of things which might trip others up:

    Copy + Pasting the script gave me invalid character errors, I had to delete and re-enter the quotation marks

    In your cheat sheet section you've specified the formulas for first/last day 2 months ago as:
    gaAutoDate(0,-3,1)
    gaAutoDate(0,-2,0)
    I think these should actually be (As you applied in your spreadsheet);
    gaAutoDate(0,-2,1)
    gaAutoDate(0,-1,0)

    reply >
    • David Sottimano

      Dear Peter, thank you for being awesome. I've corrected the spreadsheet and put the code into a textarea so you shouldn't have to correct all the quotation marks.

      I've added a credit link to you at the bottom of the post - again, thanks for catching the errors!

  4. For an even easier solution that can also access other sources like AdWords and Bing Ads, you could try our Supermetrics Functions: https://drive.google.com/templates?q=supermetrics&sort=hottest&view=public.

    There's no complicated setup, you just fetch an authentication token from supermetrics.com/functions-login, and you can then use a getData function to get any data to a sheet (no need to have a separate sheet for each query like in Google's solution).

    I made a copy of the "Month over month increases in organic" sheet, automating it with our tool instead of the "magic" script, so you can see how simple it is: https://docs.google.com/spreadsheet/ccc?key=0Aq7lsks1xCJ6dHFucGtFenQxU1FCZUx3Z0QtTTFiVWc&usp=sharing

    reply >
  5. This is brilliant! Thanks David

    reply >
  6. Which version of the GA API does this work with? Do you have any examples using segment IDs? How do you get around API rate limits?

    reply >
  7. David, very thought provoking and well conceived post. Some of it was over my head but I got a lot out of it. Thanks for the very detailed presentation.

    reply >
    • David Sottimano

      Let me know what's over your head so I can help. Feel free to come back and ask questions or catch me on Twitter @dsottimano

  8. I got this error. with ga:70431596 ID What could be wrong?

    Running on: Mon Nov 11 2013 13:30:19 GMT+0100 (CET)
    Found 3 report configurations.
    Executing query: query1
    Error executing query1: Invalid value 'ga:70431596
    '. Values must match the following regular expression: 'ga:[0-9]+' Reason: invalid
    Executing query: query2
    Error executing query2: Invalid value 'ga:70431596
    '. Values must match the following regular expression: 'ga:[0-9]+' Reason: invalid
    Executing query: query3
    Error executing query3: Invalid value 'ga:70431596
    '. Values must match the following regular expression: 'ga:[0-9]+' Reason: invalid
    Script done

    reply >
    • David Sottimano

      If you're entering the ga: ID properly, example: ga:70431596 - with no other special characters, there might be something you're not seeing, like whitespace.

      Use the trim() function to remove spaces, or just manually ensure that there are no whitespaces in the cell before or after the ga ID.

  9. Jonathan Cox

    Whenever trying to run GetData, I am met with this error message:
    Error executing query1: Authorization is required to perform that action. Please run the script again to authorize it.

    I have done what it says and repeatedly tried to run it, all this does is keep making me authorise it then just spits out the same error. I followed the tutorial all the way, so i'm pretty sure i've got it all right up till here... Any help would be appreciated.

    Thanks,
    Jonathan

    reply >
  10. Morgan

    Hi David,

    Great write-up. The video in step 2 isn't available any longer. Any idea where to find it?

    Thanks!

    reply >
    • Dave Sottimano

      So annoying, I re-uploaded a new video. Sorry about that, I have no idea why it was having so many problems - it should be fixed now :)

  11. Google always come up with some great idea for user and it provides great freedom to the user of its application, we can learn a lots of thing while using google application like google docs, android, voice search and many more.
    Thanks for sharing

    What Google Wants You to Know Again

    reply >
  12. Saddu

    Hey,

    thank you very much. Your post was the definetly the most informative for me about Google Analytics API. I just have one question. Everything works perfect, but I want to have a report that looks like: date - visits - visitors - and then again date - visits - visitors. The metrics "ga:day" or "ga:month" dont work. Do you have guys have an idea to fix that problem? Or just an idea how can i report like that? I need the daily visits, visitors and so on.

    Btw: Sorry for my English, I'm from Germany :)

    reply >
    • Dave Sottimano

      ga:month definitely works ;) Are you using ga:month as a metric or a dimension, it should be dimension

  13. Useful tips, thanks for sharing.
    But is there a way to do it without Google spreadsheet? Thanks

    reply >
  14. Ken

    Great post -- we're starting to use this process on our team to get our stats into a Google Doc automatically and I can already tell it's going to save us a ton of time. Thanks for the videos as well.

    reply >
  15. Hi Dave, Thanks for the post.
    Im having trouble with the automation of the report. When I add the triggers it says error 414 url too long to process. Any ideas?

    reply >
  16. Wells

    Is there a way to track goal completions, and conversions?

    reply >
  17. This video doesn't work which makes step 2 harder
    https://www.youtube.com/watch?v=iXmCW6a%E2%80%9428

    reply >
  18. Gina

    Is it possible to have new data import into an existing sheet and specify not to override existing data? Could you possibly direct new data to go to sheet-name and something like next row without data? I would like to run weekly scripts to pull organic traffic and for the past 7 days and have this data accumulate in the same cell. Any help is appreciated! Thank you!

    reply >
    • Dave Sottimano

      Hi Gina, you actually don't need to bother with that at all, good news right!

      You just need to create extra reports with different time frames (n-days), or even easier, you can use ga:week as a dimension and everything will fall nicely into place for you. If you're stuck, let me know and I'll guide you through it. david.sottimano@distilled.net.

  19. Dmitry

    Hello, did you get my question? Don't see it, as well as answer :( It was about combining data from several queries to one sheet.

    reply >
    • Dave Sottimano

      Sorry Dmitry, your question must have slipped through our system. What can I help you with?

  20. Mike

    I dont see the option under resources to "use google api." Any ideas?

    reply >
  21. Thank you, very useful blogpost. A question:
    Id like to know how i can collect data month over month: I want to collect metrics (visits, time on page and conversion ratios) for januari in column a, same metrics for februari in column b. Is there a way to get the output of lets say query1 in sheet1 column 'january' .... and the output of query2 in sheet1 column 'februari' ?
    (for this id also have to add script to re-arrange rows versus columns..?)

    reply >
    • Dave Sottimano

      Hi, you don't need to go through all of that pain.

      Just use ga:month as your dimension, and then it's just common excel style functions to place the data wherever you like.

    • Thank you, already figured it out ( i forgot about the transpose fuction :D

  22. Bas

    HI Dave,

    I must say, very nice post. It got me were I wanted to go. Which is as folows:

    1) Make an automated update with Google magic script 2) to my Google Drive files. 3) Then I crawl the url with Excel and get my beloved data.

    Nice thought but one problem!

    My data puke from Google API magic script shows points instead of comma's. I read a lot about this topic but didn't find a solution yet. My location is set as The Netherlands and this should change the points to comma's. But, in my case I think because I refresh a data set every 5 minutes so then this solution doesn't work. Please correct me if I am wrong here. I have several manual solutions in Excel with Wizard text to column were I replace the point with the comma. But this defeats the purpose of having automated updates.

    Can you or anybody help me out on this one?

    Thanks,
    Bas

    reply >

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>