A Guide to Custom Reporting Using the Google Analytics Spreadsheet Add-on
A technical puzzle posed by a client had me scratching my head for a while. It went a little something like this
Client: “We would like to show data from our Google Analytics live within the site”
me: “….”
me: “….”
Custom Reporting Dashboards
Monthly reporting to one side there are a number of ways we can create custom reports within Google Analytics or using third party tools such as Cyfe (I love Cyfe), trouble is this either involves providing a public URL, scheduling reports to run periodically and emailing them on set days/dates or stakeholders logging into Google Analytics and finding the reports you have created.
All very useful yes, but I know very few clients that can actually wrap their heads around Google Analytics never mind dig for data to help them make decisions. So why not create custom dashboards that are part of an internal system, or even nearly live data dashboards that can be accessed at any time?
The Solution
I am not a coder by default, I understand various graphic languages such as HTML, CSS, a bit of PHP & JavaScript and can read and bend them to my will (read:hack them to get them to do what I want), but that’s where it ends. Imagine my dismay after spending days trying to get my head around Google Analytics superProxy, I did get there after a few days and had a working dashboard (to a degree), but hooking the Core, Real-time & Multi Channel APIs is not easy, besides that you need the “App engine”, Git Hub scripts … it goes on.
Then it quickly dawned on me, if it was that hard for me, how hard would it be to try and translate what I have learned to someone else?
Turns out Google had already thought about this and offered the solution to my puzzle at the same time. So enter the “Google Analytics Spreadsheet Add-on”, once you get your head around some of the terminology & you are in the swing of configuring the custom reports you can have a live interactive graph of your data setup within 15 minutes.
Setting up the Google Analytics Spreadsheet Add-on
1 – Login to Google Drive using the same username and password that you use for Google Analytics
2 – Click on New > Google Sheets from the left hand navigation
3 – Click on “Add-ons” from the sheet menu, then “get add-ons”
4 – Search for “Google” in the search box and select Google Analytics by clicking the “Free” button
5 – An alert box will appear requesting various permissions, scroll down within that box & click “Accept”
Using the Google Analytics API within Google Sheets
Now that we have the API hooked up to our Google Sheets, we want to start polling the API, there is an extensive list of Dimensions and Metrics we can pull from the API all available here, but for the purposes of this walkthrough we are goiing to pull some basic stats..
1 – Click on “Add-Ons” & select “Google Analytics & then “Create New Report”
2 – On the right hand side of the sheet a 3 step form will appear with various input options
3 – Give the report a name, select the GA account, property and view you want to pull data from, then select the metric and dimension you want to poll. Clicking within the input fields will reveal option lists to make life a little easier
4 – I have chosen a simple report for Sessions by Source/Medium for this example, click create report when you are ready
5 – You will notice that a new sheet has been created which contains the configuration that you just requested. I have also added in “-ga:sessions” and max results of 3, (top 3 sorted by highest to lowest)
Running Reports Using the Google Analytics Add-On
So far so good, pretty pain free eh? It gets easier again! I am not going to go into too much detail about start and end dates or “Last N Days” here as I wanted to keep it simple, but when you are ready to go again.
1 – Click on “Add-ons” > “Google Analytics” > “Run Reports”
How easy was that! An alert box appears once more to inform of the status of the report you have just ran & in the background the eagle eyed will have noticed metrics magically appearing
Sharing the Report
Now that our data has been generated, next we need to present it in a much more readable format and share it, as ever the inbuilt capabilities of Google Sheets not only makes this easy but the permission based access also makes it shareable only with the eyes that need to see it. In my case though I wanted to make my data publicly available. As with any data in spreadsheets we look to present it through graphs and charts.
1 – Highlight your data
2 – Within the sheets menu click “insert” & “Chart” and select the chart type you want to use to present your data
3 – click insert
4 – change the chart title to make sense of your data (click on the title to edit)
5 – Click anywhere on the chart and notice the small drop down arrow, select it and click on “Publish Chart”
6 – From the alert that appears select “Embed” and click publish
7 – Copy the highlighted iframe snippet
8 – You can now take this snippet and publish to your own test area
What you will have is an interactive chart like this
Updating the data
There are 2 ways to handle updating the data within the reports you have configured, either manually run the report again and your chart will change, or, and this is the beautiful part, you can schedule the data to automatically update!
Scheduling Updates for Your Interactive Charts
1 – Click “Add-ons” within the sheets menu & select “Google Analytics” & then “Schedule Reports”
2 – Select the checkbox for “Enable reports to run automatically”
3 – Select when you want your reports to run
4 – Click save
This scheduling flexibility to automatically update our Google Analytics data saves a tonne of time.
Conclusion
So now we have a free way to create custom reporting dashboards from our Google Analytics data where all we need to do is configure them once and schedule to automatically update. Other than an iframe snippet there is no scripting involved, no oauth to worry about and the inbuilt permissions of Google Sheets takes care of the data integrity.
The following videos are more in depth guides to my simplistic guide, what they do offer though are introductions to working with more dimensions and metrics and custom date ranges and much much more.
