Pull Your Google Analytics Data into Spreadsheets

Facebooktwitterlinkedinmail

In this post I’ll talk about the importance of escaping the Google Analytics UI, and I’ll advocate my own solution for doing so: pulling Google Analytics data into spreadsheets. Finally, I’ll provide a few resources for automating the process, including my favorite, an ancient Google Drive script from SEOmoz.

The Google Analytics UI isn’t always enough

One of the most frustrating things about Google Analytics is its bulky and restrictive user interface. It feels slow, limits the dimensions and metrics that can be displayed together, and shows a lot of data that I just don’t care to see. Dashboards help a little bit with streamlining access to Analytics data, but they don’t even allow more than ten widgets, and those widgets can’t do everything. I just want to see the data that I need – no more, no less.

A better way: Bringing Google Analytics data into spreadsheets

In my frustration, I’ve experimented with a lot of solutions to this problem, and the one that makes the most sense to me is to get my Google Analytics data into a spreadsheet. Although they can get quite bulky and difficult to maintain, spreadsheets permeate the business world, and are pretty universally understandable. Furthermore, spreadsheets allow anyone to perform quantitative analysis on data without much programming knowledge, making the data both actionable and accessible.

Resources for automating and customizing Analytics access

Manually entering data into a spreadsheet would obviously take way too much time. Luckily there are many options for automatically pulling your data into spreadsheets using the Google Analytics API. Here are a few I’ve found to be useful:

  • Excellent Analytics – This is a tool for pulling data into Microsoft Excel, which is a great solution if you’re used to Excel or if your organization hasn’t adopted something like Google Drive. One of my biggest issues with Excel is version control, so I choose to use Google Spreadsheets.

  • Analytics Magic – This is an awesome tutorial on using a pre-built Google Apps script to get Google Analytics data into a spreadsheet. It also talks about building a web dashboard for presenting this data in beautiful ways. It’s an amazing tool, but getting side-by-side data was a bit of a hassle for me since each report needed to have its own sheet. Still, I’d definitely recommend exploring this option.

  • Google Analytics for Google Spreadsheets – Although it’s a bit ancient (2011!), this is the solution I’ve chosen to adopt. It lets you create truly custom, fully actionable reports without any huge formatting restrictions. The problem is that it uses the old Analytics API, so you’ll get an error that looks like this on some of your API calls:

    Error: userRateLimitExceededUnreg; User Rate Limit Exceeded. Please sign up

    This is something we can easily fix. Once you’ve copied the spreadsheet and followed this awesome guide to get you started, go and register for a developer key on the Google API Console site. On that site, navigate to Services -> Analytics API and switch it to ON. Next, navigate to API Access and make note of your API key (near the bottom).

    Now go back to Google Drive and click Tools -> Script Editor. Within the code, you’ll want to find this line:

    var URL = “https://www.google.com/analytics/feeds/data?ids=ga:” + profileNumber + “&start-date=” + startDateString + “&end-date=” + endDateString + “&max-results=” + maxRows + “&start-index=” + startFromRow;

    And change it to this (replace YOUR-API-KEY with your API key from Google’s API Console):

    var URL = “https://www.google.com/analytics/feeds/data?ids=ga:” + profileNumber + “&start-date=” + startDateString + “&end-date=” + endDateString + “&max-results=” + maxRows + “&start-index=” + startFromRow + “&key=YOUR-API-KEY”;

    That should get your spreadsheet up to date with the most recent authentication requirements for Google APIs!

Turning numbers into insights

With a basic understanding of the Google Analytics API, you can bring your Google Analytics data into spreadsheets with all of the metrics, dimensions, and date ranges you’ll ever need. Most importantly, though, these tools allow you (or anyone else) to turn the numbers you’re seeing into meaningful quantitative insights about your business. Want to run a linear regression on your bounce rate or forecast your organic search traffic? Getting your data into a spreadsheet is a good start.

When you don’t process or act on your data, all you’ve got are a bunch of numbers on a screen. In my future posts, I’ll be talking more and more about the methods we use to turn data insights into quantifiable results for our clients.

Leave a Reply

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