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).

Instagram followers over time

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.

Summary sheets per time period

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.

Hidden rows to configure date ranges and query

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!

Trendy sparklines

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.

View the example dashboard.

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!