How To Pull Custom Google Analytics Reports Into Google Spreadsheets

How To Pull Custom Google Analytics Reports Into Google Spreadsheets

Who isn’t loving the new version of Google Analytics? It’s clean, it’s fast, and the navigation lets you dive deep in one click instead of 10 billion. If I had only one complaint (and I might have a couple more), it would be that we’ve lost the ability to easily see a few specific things, without the need for advanced segments or custom reporting.

I’ve found that we’ve lost some features since the first release of v5 (Google’s codename for the new version of Google Analytics). Chief among them is the loss of a lot of metrics and dimensions in pivot tables, and accessibility of a few metrics, such as the total number of goal completions for a given page, keyword, etc. For some reason, GA only shows a goal conversion rate in their default reports, with no way to see total goal completions, which is frustrating in an agency setting when leads (goals, in GA) are all that matter to the client.

Enter the Google Analytics API. It’s free, with a pretty generous daily request limit, and if you know how to wrangle the API request calls just right, you can pull just about anything you can dream of.

Here is a little example I put together a few weeks ago in preparation for a presentation at the Extended Google I/O conference here in Salt Lake City. When I say  I put it together, I mean that Excel/Google Spreadsheet genius Mikael Thuneberg did most of the heavy lifting – please go check out his work, he has a lot of great tools. I just tweaked a few things and put it into a clean user interface.

Go get your copy of the Spreadsheet I put together here: http://goo.gl/QqlHZ

  1. Once you’ve opened it, click File -> Make a Copy…
  2. Now you have your own private, secure copy. Go enter your GA username and password in the Settings tab.
    settings screenshot
  3. Find the profile number for your GA profile by logging in at google.com/analytics and navigating to that profile’s reporting dashboard. Now look in your address bar and copy/paste the number next to the letter ‘p,’ as shown in this screenshot:
    how to find the google analytics profile number
  4. Now configure the Reports tab to pull any data you want – note that by default, I set the Public Template to only pull 10 rows, but you can edit that in B23 to pull up to 10,000 URLs. I’ve found that Google Spreadsheets does fine up to 2,000-3,000 rows, and then starts to get slow and buggy, but if you’re patient, 10,000 will work. If you need to pull MORE than 10,000 rows, then run 10k at a time by changing the “start at” cell to 10001 the second time you run the report, etc.

To make the tool simple, I’ve made metric and dimension selections available in a drop-down, so that you can easily click and choose any metric. Use a comma to separate multiple metrics, dimensions, filters, etc.

I highly recommend checking out the Google Data Feed Query Explorer to get a more advanced feel for what you can pull. Plus, if you have custom Advanced Segments, you can use the Query Explorer to discover their ID, and enter it into the Google Spreadsheet Report Builder.

There is a little more information in the slides, posted below, and here is a short list of what I’m building into the next version of my report:

  • Management API v3 implementation.
  • Oauth 2.0 authentication (which means no more manually entering a username and password – rather, you get the usual “Grant/Deny application” buttons and you’re done).
  • More interactive user experience, and a report that doesn’t build every time a parameter is updated.

Stay tuned for more developments, and let me know if you’ve found this helpful (or not) in the comments below – what Google analytics reports will you start pulling with it?

Get Internet Marketing Insight For Your Company - SEO.com

1 Comment

  1. Russ Schon says

    Thanks for the information.

    It was a little intense but very insightful. I can’t wait to try the nuts’n’bolts for myself.

    Regards,
    Russ  

Leave a Reply