Building an automated dashboard with Google Sheets (with example)
KPI reporting on the cheap
In January this year I focussed a lot of effort on redefining KPIs for kingandmcgaw.com, and building dashboards to make that data transparent to the teams responsible for delivering them.
I decided to build these dashboards in Google Sheets, using the Supermetrics plugin to automate the import of data from various sources (Google Analytics, Bing, Mailchimp, Facebook, Instagram and our back-office). Though there are many other powerful tools available, we’re already using Sheets extensively, so familiarity was an important factor.
What we’ll cover
- Automating data collection
- Querying the data from summary sheets
- Aggregating, charting and distributing
But first, examples
Here is an example showing campaign KPIs:
Here is another, showing trading KPIs
Not exactly eye candy, but they serve their purpose well. So how do they work? Let me show you my secret weapons – Supermetrics, and the Google Sheets QUERY function.
Automating data collection with Supermetrics
We use Supermetrics to automate the collection of data from various sources. There are many subtleties to this (it probably warrants its own post), but the key is to create one sheet per data source, with date in the first column so we can query it for different time periods. Here is what our Instagram follower count looks like (I wrote about how to automate this in my previous post).
Using this technique, we end up with a lot of sheets within our document that are specific to each thing we’re tracking. Here is a snapshot of some of ours from the campaigns dashboard.
We set Supermetrics up to import all the data each night, which means it’s always kept up to date with each full day’s activities. It would also be possible to copy/paste these values in from other sources, for a semi-manual version.
Querying the data from summary sheets
Once we have the raw data, we need to set up summary sheets. I’ve found that last week (LW), last month (LM) and month-to-date (MTD) fit well with our needs.
Each of these sheets has a hidden section where I hide the formulas I use to establish the date range. Here’s an example which uses various formulas to determine last month’s dates, and the equivalent period in the previous year.
Notice the “query” section? We can pass that into Google Sheets’ QUERY function to to pull in relevant data from our data sheets. Because the data we’re querying always has a date in column A, we’re free to pass the same query into everything on our summary sheets by referencing the cell it lives in.
If you want to see a trend for the time period in question (useful to know if performance is steady or spiky), just change SUM to SPARKLINE et voila!
Distributing
These reports are sent weekly and monthly by email as PDFs. We use Supermetrics to do this, but you could just as easily do it with a Google Apps script, or download them and send them as PDFs. We also bring these up on a screen during relevant team meetings.
Pulling it all together with an example
I’ve put together an example you can view and copy. The data is randomised and the date ranges are not dynamic, but hopefully it’s enough to demonstrate some of the techniques you’ll need to make your own.
In summary
- Collect data for each source in its own sheet, using date as the first column. Automate this if possible.
- Configure a summary sheet for each time period you’re interested in
I hope you found this article useful. Please recommend if you enjoyed it, and add a comment if you have any questions!
Hacker Noon is how hackers start their afternoons. We’re a part of the @AMI family. We are now accepting submissions and happy to discuss advertising & sponsorship opportunities.
If you enjoyed this story, we recommend reading our latest tech stories and trending tech stories. Until next time, don’t take the realities of the world for granted!
