Custom Reporting using Google Analytics and Google Docs - The Ultimate Analytics Mashup

Posted by Jamie

Realtime Google Analytics data inside a Google Doc—a panacea!

Don't believe me? Check out that screenshot below. In this blog post I'll show how you can do this yourself, and I've created an easy template to help get you started.

Google Analytics is my favorite analytics product. And it's only been getting better with the new interface, flow visualization, and multi-channel funnels. Google Analytics is still best game in town for the price (it's free)!

But, despite all the flexibility that Google Analytics offers, sometimes you want to access data in a spreadsheet and create a truly custom report. That's where the Google Analytics Data Feed API comes in.

This blog post is going to show you how to create a custom report by connecting a Google Spreadsheet directly with your data from Google Analytics. When data is available directly in a spreadsheet you're able to make interesting comparisons, create the dashboard of your dreams, or chart data however you'd like. And the only requirement is that you have Analytics setup for your website. I've created a simple Google Spreadsheet template that makes the whole thing easy.

Analytics geeks: hold onto your seats!

It all started with the Data Feed Query Explorer

(Those who want to start accessing data in Google Docs should jump right to the next section.)

Before we dive in, a little background. A few weeks ago I was looking for a solution to directly access Google Analytics data in Microsoft Excel or Google Docs using the Google Analytics API.

I first discovered Google's excellent Data Feed Query Explorer.  The explorer lets you connect to your Analytics account and pull custom data until your heart's content. This tool is not only an efficient way to figure out what's available via the API, but it's also great for pulling custom data. Want to see which organic keywords drove conversions on your site? Enter the details as below, after authenticating and adding your appropriate profile ID:

The Data Feed Query Explorer is a great way to explore the Google Analytics API, and to understand what data is available. If you're interested in understanding the API, experiment with the tool but also check out the the API documentation.

While this tool is helpful, it didn't meet my goal of accessing this data within a live spreadsheet such as Google Spreadsheets. Enter Mikael Thuneberg. Mikeal wrote an excellent set of scripts that pulls data from the Google Analytics API, and allows you to access that data within a Google Spreadsheet. Nice work, Mikeal. He provides this code free of charge (and it's included in my template below), but feel free to reach out to him if you're interested in paying an expert for your custom reporting needs.

I used Mikeal's scripts to create a template that accesses Google Analytics data and allows you to customize it in almost any way. Let's get started!

Connecting Google Analytics to Google Docs

I've created a brief screencast to walk you through connecting your Google Analytics account to the template I've created, but the instructions are also written out below the video. (A small disclaimer: this spreadsheet is provided without warranty or support, so please use at your own risk!)

1) Make sure you have a Google Analytics account with data. Duh.

Make sure you're logged into Google Analytics on the computer you'll be using with my spreadsheet template.

2) Open the spreadsheet template and save a copy.

Open this Google Spreadsheet template, and save a copy to your own Google Account (as you cannot edit this public version). Once the spreadsheet is open, choose "File"... "Make a copy".

Get the Google Spreadsheet template here!
(open this and save a copy to your own Google account)

3) Enter your Google Analytics username.

Give the browser a few moments to make the duplicate copy. Once the copy is created, enter your Google Analytics username (usually an email address).

4) Enter your Google Analytics password.

Enter your Google Analytics password. Once entered, you may hide that row to obfuscate your password.

If the cell below the Profile ID shows an Auth Token (a very long alphanumeric string) you have successfully authenticated. If you have an issue, ensure you are logged into the same Google Account for which you are trying to access. If you still have any issues, such as a CAPCHA warning, wait 30 minutes and try again.

5) Enter your Google Analytics Profile ID.

You'll need to determine the Google Analytics Profile ID of the site you'd like to create a custom report for, and enter it into the Google Spreadsheet.

Log into GA (in a seperate browser window) and open the profile for which you'd like to access data. Getting the profile ID isn't easy, and it differs based on which version of GA you use.

Once you're logged into Google Analytics, grab the profile ID from the browser address bar. Here's where you can find it depending which interface of Google Analytics you're using.

Finding your Profile ID in the Old Google Analytics Interface:

If you're using the old Google Analytics interface, your profile is highlighted below in yellow. In the example below it is 2917495 and should be entered into the spreadsheet as characters only.

Finding your Profile ID in the New Google Analytics Interface:

If you're using the new Google Analytics interface, your profile is highlighted below in yellow. In the example below it is 2917495 and should be entered into the spreedsheet as characters only.

Once you have the profile ID, add it to the appropriate field in the spreadsheet template. If everything worked, the cell below the Profile ID should display an Auth Token (a very long alphanumeric string). If you have any issues, ensure you are logged into the same Google Account for which you are try