Building an Automated KPI Dashboard With Blockspring and Google Sheets

Goal: Keep an eye on your empire from a spreadsheet, using Google Sheets and Blockspring to build an auto-refreshing KPI dashboard.  If you’d like to cut straight to our template, request your copy from the CIFL Vault on Trello.


Within a business, every employee has their own mini-empire, over which they have complete ownership.

A VP of Marketing’s mini-empire looks very different than the VP of Customer Success’s mini-empire.

An effective office chair adjusts to the height, tilt and desired lumbar support of the sitter – an effective dashboard does the same.

This is why spreadsheets + Blockspring are a great combination for KPI dashboard construction: they allow a team or employee to mold their data to the way they specifically operate.

Because dashboards are only useful when they provoke action on a daily basis – otherwise they’re just vessels for data puke.

THE CUPCAKE DASHBOARD

Following the principle of a cupcake release, our goal is always to build the minimum viable dashboard to monitor the impact of our work.

Like a cupcake needs cake, frosting and sprinkles to taste delicious, our dashboard needs only three ingredients:

kpi-dashboard-cupcake

  1. Cake = raw data
  2. Frosting = aggregating that raw data to be human-readable
  3. Sprinkles = just the actionable bit (can take the form of a single KPI, a metric broken down by cohort, or even a list of individual customers)

Once those three ingredients are combined, you’re ready to chow down on true data analysis. Let’s break down the recipe for each layer.

Don’t forget: To grab your own copy of our spreadsheet KPI dashboard, access the template vault at the end of the post.

Baking the data Cake

Our utensil of choice for pulling data into a Google Spreadsheet is Blockspring, which connects with most of the APIs we use on a regular basis.

If you haven’t tried Blockspring yet, we put together this guide on getting started.

One of our key principles of spreadsheet app design is having only one purpose for each tab in a sheet. This keeps everyone clear on where data is coming from, and when it was last updated.

So when building an automated KPI dashboard, we set up a separate spreadsheet tab for each unique data source (each of which is pulled via one ‘Block’ using Blockspring) – for example, ‘Opportunities’ data from Salesforce gets dumped into a separate tab than Mailchimp subscriber data.

Cake Batter Ingredients

To follow that example, let’s walk through a dashboard the League built for a video production agency in New York City. They wanted to keep an eye on a few key areas of the business:

  • Sales Pipeline
  • Email + Social Media
  • Paid Acquisition
  • Web Analytics

Our first step in baking the cake, was figuring out how to get the data from each of the services their agency used: which Blocks will we use to pull data?

blockspring sidebar timer

To allow us to refresh this data on a consistent timer (hourly or daily, depending on the source), Blockspring built a handy CRON-timer function into their Google Sheets Add-On:

That’s all for the cake! It gives us a solid foundation to add frosting and sprinkles – data that is reliably formatted and consistently refreshed, in tabs that are clearly labeled.

Mixing the Data Frosting

Frosting is actually made from most of the same ingredients as cake batter: milk, butter, and sugar – they’re just combined and mixed differently.

The same is true of the middle layer of our data cupcake – we aggregate data we pulled in the cake layer, in order to make it useful in our analysis.

There are a few key jobs that frosting-level queries perform:

Pull XX values based on criteria

Comparing your 10 best or worst-performing sales accounts.

Sum and count a KPI by XX

Comparing sales by product.

MAKING A HISTORICAL COMPARISON

Comparing sales by quarter.

Once you have a good feel for the query, filter, and index-match functions in Google Sheets, it’s a matter of using the correct function for the job.

For example, focusing on our Salesforce dashboard from above, if we wanted to compare our sales by type of business (new vs. existing), we’d write a query function to format the raw data into a human readable table:

raw-query-salesforce-blockspring

The key is understanding the basic spreadsheet functions and knowing when to deploy them. We lean on query() above all other functions, since it provides a couple key advantages:

– Allows you to aggregate many KPIs (sums, counts, avgs, etc) with one function call
– Makes it easy to trace where data is pulling from, since queries are written out in standard language

Now that you’ve mixed the frosting and have your data ready for display, you just need to add the sprinkles: charts to bring your data to life.

Sprinkling in Visual Flavor

Just like adding sprinkles to a cupcake, adding the final visual layer to a dashboard is probably the simplest task, but also the most important.

In our experience, visual summaries of data can take a few forms (all of which are doable in Google Sheets):

  • Simple tables: to summarize raw values, or produce a customer list for a given segment
  • Comparison charts: compare values in a table to an average or median, using sparkline charts
  • Line charts: to view a KPI’s progression over time
  • Pie charts: to view a breakdown between categories
  • Gauge charts: to show progress towards a goal

For the query example above, which aggregated Salesforce pipeline data by type of business (new vs. existing), the query function would produce this data table and pie chart:

pie-chart-salesforce-query

Once you’ve aggregated your data in the middle (frosting) layer, visualization might be as simple as formatting the data properly and/or making a graph using the Google charts wizard (both in this case).

We won’t dwell too much here since the Google help docs are great for charting, but if you run into any issues feel free to reach out to us at help@codingisforlosers.com (Sparklines in particular can get tricky).

Sharing the Cupcake

So you’ve baked this fantastic automated KPI dashboard, containing all of the metrics that you need to do your job day-to-day.

But what happens when you want to share this cupcake with your team, your boss, or your investors?

It wouldn’t make much sense for you to share the entire dashboard sheet, since you probably have 4-5 tabs containing metrics on various areas of the business – most of it irrelevant for a given outside audience.

You probably just want to share the One Metric That Matters, so that they can quickly get a feel for the numbers and move on with their day.

One way to do this is to construct a ‘Summary’ tab, that pulls the most important metrics from throughout your dashboard. This lets anyone give a quick peek into your world, without getting lost in the minutiae.

You can set this up to distribute to your team by using Blockspring to either send a summary email or a Slack message. Just like the Blocks we discussed in the cake layer, these messages can be set to run on a timer (daily or weekly, depending on the audience).

Cupcake Dashboards for any Taste

You can apply this dashboard methodology to any business context, but the core principles will remain the same.

In summary, we love using KPI dashboards built with Google Sheets because they allow us to:

1) Dump in a near-unlimited number of data sources using Blockspring
2) Aggregate and visualize data in a way that’s specifically useful to our day-to-day work
3) Share data with colleagues in a way that’s specifically relevant to them

Ready to dive in with a custom spreadsheet dashboard?  Access the CIFL template vault below, where you can grab your own copy.

Happy baking!