Pinterest Stumbleupon Whatsapp
Ads by Google

If you had the choice between flying an airplane blindfolded or flying it with your eyes open, which would you prefer? The same holds true for running a website. Would you like to just publish a site and run it blindly, or would you like a full-featured, functional dashboard that shows you what’s going on with the site at all times?

If you’re familiar with Google Analytics, then you’ll be pleased to learn that Google offers a powerful API called the Google Core Reporting API, which lets you tap into the numbers from your Google Analytics account. You can use this API to run queries for information on anything from how long people are spending on your website, how many pageviews you’re getting every day, how many new versus returning visitors are visiting the site, and much more. Basically, most of what you can look up on Google Analytics, you can pull out of it using the API, and bring that data into a tool like Google Spreadsheet.

Once you’ve got that data in Google Spreadsheet, you can analyze it, plot it, and chart it, and then when you’re ready, you can publish the information to a website that can serve as your “dashboard”. Pretty cool huh? Don’t let words like “API” or “Analytics” scare you. You don’t have to be a hard core programmer to accomplish this — although familiarity with Google Analytics will certainly be helpful.

Connecting Google Core Reporting API

Recently, I showed you how you can use IFTTT automation to import Analytics data How To Create An Automated Google Spreadsheet Report From Analytics Data How To Create An Automated Google Spreadsheet Report From Analytics Data Read More — via Gmail — into Google Spreadsheets. Well, if you’re willing to get your hands dirty with the Google API, here’s the big secret; All you have to do to establish a connection between Google Spreadsheet and Google Analytics is to activate the Google Analytics script called “magic”. To do this, just open up a Google Spreadsheet and click on the “Tools” menu. Choose “Script Gallery” from the list. In the search field, type in “google analytics” and search for the “Google Analytics Report Automation (magic)” script.

Analytics-to-Docs1aa

 

Ads by Google

Click on the “Install” button. You’ve just installed this script into this specific Google Spreadsheet. Now, go back to your spreadsheet and click on “Tools” and “Script Manager”.

Analytics-to-Docs3

 

Now you’ll see the four functions from the Google Analytics scripts that are available to you to use. Keep in mind that so far you haven’t had to type a single line of code. Nor will you have to in order to complete this entire procedure.

Connecting With Your Google Analytics Account

However, you’ll still need to enable the script to access your Google Analytics account, so click on the “createCoreReport” function and then click on “Edit…”

 

Analytics-to-Docs4

This will open the actual script itself. Ignore all that strange-looking code, and just click on the “Resources” item in the menu, and choose “Use Google APIs”.

 

Analytics-to-Docs1a

You’ll see a list of Google APIs Services here. Look for the “Google Analytics API” service and click on the on/off switch to turn that one “on”.

Analytics-to-Docs1

Next, click on the “Google APIs Console” link at the bottom of that window, and this will take you to the Google Cloud Console with a list of APIs. Scroll down the list and look for “Analytics API”. Again, click on the “Off” button to turn the API “on”.

Analytics-to-Docs2

 

Close that window, and when you get back to the screen with the script on it, make sure to click on the “disk” icon to save everything you’ve just enabled. This is very important or you’ll lose everything you’ve just done.

While it may seem like a lot to have to go through, remember that you only have to do this one time. Once you’ve established the connection,  you can use the Google Analytics queries I’m going to show you as often as you like. The connection will remain established between your spreadsheet and Google Analytics from now on.

Querying Google Analytics From Google Spreadsheet

To create your first query, once again click on Tools, Script Manager, and select “createCoreReport”. Then click on “Run”. This will place a bunch of seemingly meaningless text into your spreadsheet. Let me tell you, the simplicity of this block of text is misleading. This is one of the most powerful queries to extract data from Google Analytics that you’ll ever come across.

Each row is a component of the query. IDs is where you identify your Analytics ID (which you can get from the Admin area of your Google Analytics account), you can define the start and end date for your query (or skip that and list number of days in “last-n-days”), and then you can define your metrics, dimensions, and more. I’ll show you how.

 

Analytics-to-Docs5

Here’s an example of a query that extracts a daily list of visitors, visits, and pageviews for the last 30 days. It then places that information into the sheet “report1”.

Analytics-to-Docs6

 

Once you have your query ready to go, you can run it by clicking on “Tools”, “Script Manager”, and then selecting the getData function. Then click on “Run”.

Analytics-to-Docs7

 

You’ll see a pop-up box showing you the status of the query. If you set up everything right, you should see “Success. Writing results” as the second to last line.

Analytics-to-Docs9

 

Now, click on the “report1” sheet, and voila! There’s all of the data, extracted right out of Analytics and inserted into Google Spreadsheet.

Analytics-to-Docs10

Now you can use Google Spreadsheets awesome data visualization tools to turn that data into some visual context that is meaningful to people that like looking at charts more than they like looking at a list of numbers.

Analytics-to-Docs11

So there you have it. You can create new queries (just place them on the same sheet as the first and label the columns query2 and value2 (and so on). You’re really only limited by your own knowledge of Google Analytics queries. Playing around in Analytics, or reading our MUO Analytics Manual can help you make some progress in that area.

Creating a Website Analytics Dashboard

The cool thing about creating your own simple dashboard is that you don’t have to keep performing the same navigation in Google Analytics to check out the charts. You write up the query once in Google Spreadsheets, and it’s ready to be loaded into a Google Sites page Make Your Own Free Multipurpose Web Page With Google Sites Make Your Own Free Multipurpose Web Page With Google Sites Read More . I discussed how to load Google Spreadsheets data into a Google Site Create a Really Useful Personal Information Dashboard Page With Google Sites Create a Really Useful Personal Information Dashboard Page With Google Sites Creating webpages used to be such a tedious process. Aside from the hassle with deciding where to publicly host the page, you needed to learn HTML and then figure out how to program the page... Read More previously here at MUO, so I won’t dwell on the point for too long here.

The key thing you want to do here is to load the Google Analytics charts you’ve created into a Google Site that’ll serve as your dashboard. Go to Google Sites, create a new site under your account, and then click on the “Insert” menu item and choose the Chart option.

Analytics-to-Docs12

The nice thing about Google Sites is that if you’ve created the Google Spreadsheets described above using your same Google account, then Google Sites will let you choose that spreadsheet from a list of available spreadsheets under your account. Once you do this, Google Sites automatically recognizes all of the charts you’ve created in that spreadsheet and gives you the option to import them into your new site.

Analytics-to-Docs13

You can create a simple list of charts as you scroll down the page, or you might even create a table on the page and place some of the charts side-by-side.

Analytics-to-Docs14

The point is that in just a few simple steps, you’ve just migrated very complex Google Analytics data into a Google Spreadsheet where you can manipulate that data and turn it into a chart that is much easier to understand. Then you can output that to a Google Site in just a few more steps, creating a quick — yet powerful — dashboard showing everything you could ever possibly want to know about the performance of your website! Unlike Google Analytics automated emails Automate Informative Google Analytics Reports With Custom Reporting Automate Informative Google Analytics Reports With Custom Reporting One thing that I've always wanted to get working better with Google Analytics is an automated reporting feature that provides the information that I want and in the format that is easy for whoever needs... Read More , you can actually format these dashboards to look exactly the way you want them to.

Now if increasing the performance of the website could just be just as easy as monitoring it, all would be right with the world. Do use the comments to give your feedback on this tutorial.

  1. Álex
    December 9, 2013 at 3:41 pm

    Hi, a great article, but I have exactly the same problem as Javier. Any ideas?

  2. Javier Piedragil
    December 6, 2013 at 3:59 pm

    Hi Ryan,

    I actually created this spreadsheet, my Google username is displayed in the upper right corner of the spreadsheet window. I was able to follow without errors all steps before running the getData script, including saving in the editor window.
    I hope this information could be useful.

    Javier

  3. Javier Piedragil
    December 4, 2013 at 6:34 pm

    Hello,

    I had a problem when running the getData script, the description of the error message was:

    Running on: Wed Dec 04 2013 12:32:44 GMT-0600 (CST)
    Found 1 report configurations.
    Executing query: query1
    Error executing query1: User does not have sufficient permissions for this profile.
    Script done

    I checked my user permissions for the site in Analytics, and all them are checked.
    Any help will be appreciated.

    Thanks

    • Ryan Dube
      December 5, 2013 at 2:04 am

      Hi Javier,

      One quick question - is this a spreadsheet that you actually own/created, or is it someone else's spreadsheet that you're editing? Also, were you able to get into the script manager or script editor okay without any errors, and when you do now, do you see the Analytics script in there? If so, is the Analytics API enabled in both locations as shown in this article? Both of them need to be turned on or this won't work.

      Let me know how you make out!
      -Ryan

  4. Josip F
    December 3, 2013 at 8:19 pm

    Great article. Thanks!

Leave a Reply

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