The Google Docs library for the analytics API provides a great tool for managing complex or repetitive reporting requirements, but it can be tricky to use. It would be great if it was a simple as dropping a spreadsheet formula on a page, but Google’s library stops a few steps short of that – it needs some script around it. This sheet closes that gap, providing a framework for template driven analytics reports in Google Docs.
With it you can set up a report template, and click a menu to populate it with your analytics results and run your calculations – without needing to write a line of script – the code is there if you want to build on it, but you can get useful reports without writing a line of script.
Prerequisites
While you don’t have to write code to use this, there are some technical requirements. To get the most out of it you’ll need to have:
- your Google analytics tagging and views set up
- familiarity with Google’s reporting API
- familiarity with Google Docs spreadsheets – some knowledge of Google apps scripting is an advantage
If you are looking for something more user-friendly or tailored to your needs, contact us and book a consultation to discuss – we can help with your analytics setup and bespoke reporting solutions.
Getting started
Setting this up takes a few steps, but you only need to do this once:
- Open the shared Google spreadsheet
- Make a copy
- Enter a view ID in the settings sheet – get this from the Google Analytics admin page.
- Authorise the script
- Authorise the API – in the API console – this is the only time you need to go into the script view using
- Tools|Script Editor
- Once in script editor select Resources|Advanced Google services
- On the bottom of the Advanced Google services dialogue is a link to the Google Developers Console, follow this and ensure that Google analytcs API is set to On
- You’re done. You can go back to the spreadsheet and run the report (on the Analytics menu). From now on all you need to do is tweak any settings on the template and run the report.
Setting up your own report template
You can explore how the template works using the example.
Anywhere you want to retrieve value(s) from Google Analytics, place this spreadsheet function on the template:
= templateShowMetric(profile, metric, startdate, enddate, dimensions, segment, filters, sort, maxresults)
This works as a custom spreadsheet function, for example
=templateShowMetric(Settings!$B$2,$B7,Settings!$B$3,Settings!$B$4,$C7,$D7,$E7,$F7,$G7)
Note that in the example, several of the references are to the settings sheet, but they don’t have to be, you can use any cell or literal value in the formula – it’s just a spreadsheet function.
To get the values for the API query, I’d suggest using Google’s query explorer.
To set this up for a weekly report, say, you would have all the queries reference a single pair of cells with start and end dates. Each week you would change the date cells run the report again – all queries will be run exactly as before, but for the new dates.
Using spreadsheet references for query parameters is key. This opens up use of relative and absolute references – for example if you need to run the same query against 50 segments, you list your segments down a column, set up segment as a relative reference, and copy the formula down spreadsheet style. You can use this to do calculations on the sheet and use results in the analytics API, for example you might calculate start and end dates relative to current date.
Future posts will cover setting up templates in more detail.
Under the hood
The templateShowMetric function generates a JSON string. When you trigger the script, the report generator copies everything on the template to the report sheet and:
- runs any analytics queries specified by a templateShowMetric function
- removes any formulas that reference the settings sheet (so you can use the settings sheet to pass values to the template, but your reports are not dependant on the settings staying the same)
