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 — 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.
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”.
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…”
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”.
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”.
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”.
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.
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”.
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”.
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.
Now, click on the “report1” sheet, and voila! There’s all of the data, extracted right out of Analytics and inserted into Google Spreadsheet.
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.
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. I discussed how to load Google Spreadsheets data into a Google Site 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.
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.
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.
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, 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.