Continuing my recent infatuation with dashboards in Google spreadsheets, I’ve been playing around with the Google Analytics Add-on to build a custom web analytics dashboard in a Google spreadsheet.
The idea originated from discussions with my wife about the tedium of preparing month-end website performance reports for her content management clients. This dashboard was a solution to streamline the monthly web reporting and offer insights that might otherwise be missed. Rather than having to visit Google Analytics for each website in turn and click around gathering the necessary data, this pulls it all together in one place.
As can be seen in the above animation, the dashboard allows the user to choose which website’s data they wish to look at. The dashboard displays monthly traffic metrics for the past year (and adjusts automatically to always display the most recent year), as well as Social and Referral metrics, all shown in tabular and graphical formats.
The full dashboard also has tabs for “Top Pages” and “Top Referrals”, which show the results for the last month, and all time. For example, here is the “Page Stats” tab:
This was designed to meet specific needs, but could easily be tailored to show different information. The beauty of pulling Google Analytics data into a Google spreadsheet is the vast level of customisation it gives you.
Editor’s note: this post is intended to give a flavour of how this dashboard was created, rather than a comprehensive step-by-step walk through. If you have any questions leave a comment or shoot me an email.
How to create the dashboard
There are several steps to creating the dashboard:
1. Setting up the Google Analytics Add-On in your Google spreadsheet
2. Setting up the Report Configuration in your Google spreadsheet (the instructions to pull the raw data)
3. Pulling the raw data from Google Analytics into your Google spreadsheet
4. Transferring the raw Google Analytics data into summary tables in the dashboard
5. Finishing the dashboard, e.g. formatting, adding trend lines, charts etc.
Before launching into the mechanics of the report, it’s always a good idea to think through what questions you’re trying to answer as this will determine what data you need. The motivation for this dashboard was to facilitate gathering web traffic data across a number of different websites and then to display the key metrics in one place.
Step 1: Setup the Google Analytics Add-on and find your web property IDs
Google has made it super easy to pull data from Google Analytics into a Google spreadsheet with the Google Analytics Add-on. So first step is to install this – it’s found in the “Add-ons” menu –> “Get Add ons” –> search for “Analytics”, then install. No worries, it’s free!
For a great introduction to this add-on tool, check out the official Google docs here.
Once this is installed, return to the “Add-ons” menu and click “Google Analytics > Create a New Report”. A pane slides in on the right side of your browser and prompts you to setup a report. You will see the different websites that you have in your Google Analytics site, so create a report for each one in turn (this allows you to grab the View (Profile) ID / ids for each).
The Report Configuration is the instructions we’re sending to Google Analytics, telling Google what data we want to extract.
This is the key driver of the dashboard, which can be modified to tell Google exactly what we need.
Step 2: Building the Report Configuration tab
In the Report Configuration, we can specify which website we want to pull data from (by changing the View (Profile) ID / ids field), the start and end date of our request, the dimensions (e.g. pages on website or date ranges), the metrics (e.g. users, sessions, page views, bounce rate), and certain limits on how many records to retrieve. There’s a whole heap more customisation, but that’s a topic for another day.
The trick to making the dashboard interactive and up-to-date is to use formulas to control which website ID and what dates are in the report.
I’m running several reports here to get different datasets for the dashboard. The ones I’ve setup pulled the following datasets:
– Total monthly uniques, sessions, pageviews, going back 1 year
– Monthly uniques, sessions, page views for the different channel groupings (e.g. search, direct, social etc.), going back 1 year
– Monthly uniques, sessions, page views for the different social groupings (e.g. Facebook,twitter etc.), going back 1 year
– Last full month and month prior to that for the Top 15 pages on the website
– Last full month and month prior to that for the Top 15 referrals on the website
To give the user choice over which website they want data for, I use the data validation method that I discussed in some detail in my previous post. I use the View (Profile) ID / ids information I’ve gathered using the Google Add-on in Step 1. It’s exactly the same set up here as in my previous post, and I put the drop-down menu of websites at the top of the dashboard.
For the start and end dates for each report, I enter formulas into the Reports so they update automatically. Using today’s date as a reference I can then set up other dates based on that, using the following formulas:
1. Current date, using the today formula, which will automatically update: =today()
2. The same month but a year earlier, to give a year timeframe. The following formula achieves this: =date(year(today())-1,month(today()),1)
3. The last day of the most recent full month. This formula includes an “if” condition to determine if today’s date is the end of the month and, if it is, to use that date, otherwise to go back to the previous full month. Take a look: =if(today()=eomonth(today(),0),today(),eomonth(today(),-1))
4. The first day of the most recent full month, using the formula: =eomonth(E6,-1)+1 where “E6” is the cell reference of the formula in point 3.
The dimensions and metrics are the specific pieces of data and numbers that I want to pull out of Google Analytics to analyse.
For this exercise, the dimensions (think row headings on left side of tables) are:
And for metrics (think numbers in the tables), I’ve used:
ga:users, ga:sessions, ga:page views (monthly overview data tables)
ga:pageviews, ga:avgTimeOnPage, ga:bounceRate (specific page/referral tables)
There are a vast number of dimensions and metrics available, which gives a ton of options over what your custom report shows. For a full list of dimensions and metrics, here’s the Google documentation.
This is what the Report Configuration tab looks like for this dashboard:
Step 3: Creating the dashboard
Run the report as follows, to get back the raw Google Analytics data:
Google will create a series of new tabs in your spreadsheet. Each tab corresponds to one of the reports in your Report Configuration tab, and the tab will have the same name. Next step is to create the “Dashboard” tab which displays summary data from the raw data.
Create empty tables with the current month as a row heading, using the following formula:
Then use the following formula to create the month prior to the current one, as another row heading:
I then keep “rolling” the months back, by changing the “-1” to “-2” for two months prior, then “-3”, “-4” all the way back to “-12”, to give the current month plus 12 preceding months in an empty table.
(The yearly column in the table is created with the same formulas but replacing “MMMM” with “YYYY”.)
Use formulas to pull across the data from the raw Google Analytics tab tables. In most cases, it’s enough to use a simple link to the cell that has the data.
However, for the Channel and Social overview tables, where I want to show each channel or social channel month-by-month, I had to use a multi-condition lookup formula, since I needed to specify three criteria (year & month & channel). Take a look:
Crazy huh! This kick-ass formula, inspired by this post from Excel wizard Chandoo, uses the powerful index/match formula on steroids, in an array formula to lookup multiple values across multiple columns in the Google Analytics raw data table. It concatenates the year, month and channel, that I want to use as my lookup value, then looks for this concatenated value in the Google Analytics raw data across the year, month and channel columns. When it finds the right match it returns the corresponding result. It’s truly awesome, a vicious left, right, left knockout combo.
The following formula is used for the monthly % growth column:
where an IFERROR function is used for cases when there is not enough data for the calculation.
At this stage, the dashboard is a series of plain tables, populated with data that changes based on the user’s choice of website, and automatically adjusted for the current time period. The next step is to add some visual components and format the dashboard into something more user friendly.
Step 4: Adding flourishes to the dashboard
I recently learnt that Google spreadsheets has a formula for Sparklines: =sparkline(). Sparklines were first created by data viz legend Edward Tufte. It’s very cool and applicable here, giving a useful handle on the web data. The trend line looks like this:
I added one of these sparklines for each dataset that was based on a timescale.
The last component of the dashboard was to add some simple charts, giving a graphical depiction of data in the tables. In this case, I added a bar chart for the monthly uniques table and some pie charts to show the most recent month’s channel and social metrics, but of course, there are a multitude of options using Google’s built in charting capabilities.
As a final touch, I hid all of the Google report tabs (don’t worry, they still function in the background!), removed the gridlines and then gave all the tables a consistent format, with light grey borders and light blue shading on alternate lines.
The final result is: