Building a Business KPI Dashboard

KPI Dashboard Feature

How I wrangled all my key metrics in one place using GeckoBoard, Zapier, & Google Sheets.

“What gets measured, gets improved.”

Isn’t there a wise saying like that somewhere? Well, it’s true.

But keeping a close eye on your metrics, or KPIs (Key Performance Indicators), doesn’t always come easy. Certainly not if you’re a visual thinker, like I am.

Having come from a design and creative background, it’s always been a struggle to force myself to focus on data. I’m usually much more comfortable operating on gut feel and intuition. Poring over numbers and analyzing trends sometimes feels boring and quite challenging for a visual thinker like me. For too long, I failed to make a habit of looking at my metrics—mostly because I just didn’t want to.

Sure, I got by. I kept tabs one metric: My bank account balance. Needless to say, there’ve been a few periods where “getting by” was questionable.

I realize now, that most of the ups and downs were due to a lack of visibility into what was truly happening in my business—The trends in the data that actually impacted that cash balance, it’s fluctuations, it’s flush months, it’s tight months.

This lack of visibility led me to work on the wrong things at the wrong time. Sometimes I’d mistake the level of urgency of a task (“my website needs a redesign right now!”). Other times I’d fail to see opportunities sitting right in front of me (like the fact that we’ve had a stellar close rate and just needed more leads, not a better product).

If you’re not making a habit of watching your key performance indicators (KPIs), you’re running blind. I know this now.

Still. Making a habit of “being data-driven” is another story.

Today I’ll show you how I—a non-data-nerd creative designer who once barely knew how to operate a spreadsheet—learned to become more data-driven and actually like it! I’ll show you how I built the MVP of my first KPI dashboard with just the bare essentials. Then, how I upgraded to a fully automated and more complete KPI dashboard that drives all of my “work-on-the-business” decisions today.

And of course, how you can build the same thing for your business.

Get the Complete Setup: Learn about my KPI strategy below. Then use this technical setup guide to build your own.

Where’s the data?!

If your business is anything like mine, the data you need is everywhere and nowhere.

Your business bank account; Your payment processor; Your site analytics; Your email marketing software; Your lead forms; Your social media profiles; Your ad campaigns.

Every one of these (and probably a few more) live in their own silos. Every account has a different reporting capability, each with different metrics that matter in different ways.

For example, you can log into Google Analytics and see your traffic is up. Great! But if you don’t log into your email marketing tool to see how many converted to subscribers, and how many of those opened your emails, you can’t easily see whether or not you’re attracting the right quality of traffic. And without knowing this, you can’t make smart decisions about how you’re driving traffic to your site.

As I said, I wasn’t disciplined enough to make a routine of tracking and analyzing my data. I’d glance at various metrics sporadically, only when I happened to be in a particular tool.

In my last business, I came up with a simple solution: Have my assistant collect all of the key metrics from my various apps, manually input them into a spreadsheet, then send me an email every Friday with a link to view that sheet.

This didn’t require complex programming to setup—beyond writing out the procedures for my assistant to follow when collecting the data points I wanted. And it forced me to look at the data, all in one place, on a weekly basis, because it landed in my inbox every Friday.

Plus, by delegating the task of digging up the numbers and updating the spreadsheet, I removed the boring/tedious part of the process (which historically led me to ignore my data altogether), so I can focus on just the meaningful (and enjoyable) part:  Reviewing the numbers. Connecting the dots. Thinking it over. Using what I learned to plan next steps.

Get a copy of my basic customer metrics spreadsheet to use when you’re just starting up. It’s included in my full setup guide.

Too much data!

The next challenge to overcome on your quest to becoming a data-driven business owner is dealing with data overload!

Every tool lets you slice and dice and dig deeper and deeper into the data. Not only can you see site traffic, but you can also see how many came from search engines, how many are on mobile devices, how many visited before, how much time they spent, which version of which links they clicked, what time of day they converted, what season was it… ahhh!!

Just because you have access to every metric imaginable, doesn’t mean they’re all helpful.

You have to decide which are your key metrics and which are optimization metrics.

Key metrics enable you to make big-picture decisions about your business, so these need to be included in your KPI Dashboard. Key metrics look backwards to clarify “what happened” and “why”. Key metrics help you move forward and decide where you need to focus your time and resources next.

Optimization metrics are useful when you’re digging into a particular piece of your business and making a project out of optimizing that piece. Pay-per-click ad campaigns, and all the metrics associated with these are a good example. If you’re focused on improving PPC performance this month, then go ahead and get your hands on all the numbers you need. Slice and dice to your heart’s content. Just don’t let these optimization metrics add noise to your big-picture, longer-term decision-making process. Keep them off your KPI Dashboard.

Which metrics matter (and when)?

I found it’s helpful to know which phase your business is in: “Startup” mode or “Growth” mode? The metrics that matter will differ in each phase.

“Startup” KPIs

I know companies may call themselves “Startups” for several years. Semantics. What I’m referring to here are your very early days, when you’re hacking and clawing for your first batch of customers, just to get your business to a point where it’s sustainable-enough to keep going.

During this early phase, it’s very easy to go overboard and waste a bunch of time and resources on KPI tracking. If you’re trying to wrangle too many KPIs into a fancy dashboard with pretty graphs, custom-programmed data reporting, automation and all the rest, you’re just spinning your wheels. The numbers aren’t significant enough yet to make actionable business decisions on. In fact, they’ll probably mislead you.

So like everything else in the early startup days, go MVP (minimum viable product).

For the first 18 months of building my business, Audience Ops, my KPI dashboard consisted of a single Google Spreadsheet, which I updated manually every time a new client signed up or cancelled service. Yup, I literally typed in my customer’s details myself every time.

Was it perfect? No way. Error-prone? You bet. Incomplete? Of course. But it gave me just enough visibility into the only metrics that truly mattered during those early months: How many active customers do we have, and what’s our MRR (monthly recurring revenue)?

Sure, I looked at other things that were going on, like traffic, email list engagement, our early funnel experiments, etc. But my top priority (and only things I could justify spending time on) were talking to customers, refining our product, plugging holes, and starting to build our systems.

Want a copy of my spreadsheet I used to track my customer metrics early on? Grab it here.

“Growth” KPIs

At some point, you’ll find yourself far enough along in your business that you’ll feel hampered by a lack of visibility on all your key metrics.

You’ve got plenty of customers. You had some marketing and product wins. Now you need to make high-level decisions as to which levers to pull and push in order to grow your business in the next quarter, the next year, and beyond. But you struggle to know what those levers are, since you don’t have an easy view into all of your key metrics.

It’s not that you’re unable to go get them and look. It’s that you don’t have the time to spend digging them out of your various apps and pushing aside all of the little optimization metrics that add noise. You need just the key metrics, ready to help you make those big-picture decisions.

This is where a truly complete KPI dashboard will do your business a lot of good.

I found myself in this position around 18 months into building my current business, Audience Ops. Frankly, I procrastinated on upgrading my KPI dashboard until about 24 months in and my business suffered for it.

These are the key metrics that matter most to me in my business. I’ve built all of these into my single KPI dashboard, which I’ll show you in a moment.  I broke them into four categories:

Customers:

  • Number of active customers
  • Number of new customer sign ups
  • Number of cancellations & customer churn rate
  • Monthly recurring revenue (MRR)
  • Average MRR per customer
  • Average customer lifetime (months)
  • Customer lifetime value

Pipeline:

  • Number of leads
  • Sales close rate
  • Avg. number of days to close a lead
  • Number of active leads in the past 3 months who haven’t closed

Traffic:

  • Overall site traffic
  • New visitors
  • Return visitors
  • Visitors to the homepage (which is our pricing and customer signup page)
  • Traffic from search
  • Traffic from paid ads
  • Top pages viewed
  • Top traffic sources

Email list:

  • Subscribers
  • New subscribers added
  • Unsubscribes
  • Subscribers who requested free samples
  • Subscribers who opted in for our primary lead magnet (webinar)
  • Subscribers who opted in for our secondary lead magnet (email course)

My KPI Dashboard

Note: The numbers are obviously placeholders. I didn’t want not to share my real numbers here because A) who knows when you’ll be reading this, so they’re almost certainly no longer accurate and B) my numbers aren’t what’s important. What I’m showing here is how I laid out my KPIs so I can quickly and easily see them, think about them, and act upon them.

KPI Dashboard
Get the Complete Setup: Below, I’ll share the broad strokes of how I approached building out my dashboard. For the nitty-gritty technical details, get my complete download with ready-to-use samples and scripts. 

Building My Dashboard

As I said, my basic spreadsheet got me through the first year+, but it was far from perfect. So when I set out to upgrade to a more robust KPI dashboard, I had the following goals:

  • No more manual entry. It has to update itself in realtime.
  • It has to include all of my most important KPIs (and nothing else).
  • It has to be easily accessed on mobile. I do at least half of my work from my pocket these days, so I need my numbers with me.

I’m using GeckoBoard for the dashboard itself. I love the customizable visual layout, plus it has solid mobile app.

Although GeckoBoard has a large library of integrations with most of the popular apps, I found that most of these “out of the box” data points were too basic. For example, I can connect my Stripe account and see show the number of active customers I currently have, but I can’t get MRR growth over time, new signups by month, etc.

So I needed a place to gather, calculate, and store the data I need. GeckoBoard can pull in data from a Google Spreadsheet and show it in any number of ways (a number, bar graph, line graph, etc.).  I created a couple of Google Spreadsheets, where I’m gathering data, logging history, running calculations, and generating the raw KPI data points.

Now the question is, how do I get all of the data I need from all of my apps (Stripe, Drip, Google Analytics, Gravity Forms, etc.) into my Google Spreadsheets?

Luckily, a close friend of mine is extremely good at getting these apps to talk to each other. His name is Zapier 🙂

Zapier has practically become a powerful programming language in itselt, at least for non-programmers like me. It’s ability to chain API automations together and it’s extremely robust set of options, filters, and vast library of apps you can connect is truly amazing. It’s a necessity for most online businesses, especially a productized service business like mine.

I’ve got 3 main “Zaps” that power my KPI dashboard. Each of these has multiple steps chained together (some have more than 10 steps!):

– New lead (triggered when someone fills out our consultation form, a Gravity Form)
– New customer signup (triggered when a Stripe subscription is created)
– Customer cancelled (triggered when a Stripe subscription is cancelled)

Zapier Workflows

Now, all of this may seem easy enough so far. Connect apps to Zapier, Zap their data in Google Spreadsheets, then view that data in GeckoBoard. But there’s more to it than that… And that “more to it” was what turned this from a 1-day project into a 2-week project.

The tricky part was slicing and dicing the data once it was in my Google Spreadsheets to form it into metrics that I can view in GeckoBoard. Let me explain:

Getting a total customer count: Easy enough.

Calculating total MRR, taking into account monthly, quarterly, & annual subscribers: A little formula work, but not too hard.

Updating active customer records when they cancel: Required some of Googling, trial and error, but I eventually figured it out.

Recording a “snapshot” of the MRR and customer count on a monthly basis, so I can chart growth over time: That’s where I got stuck…

You see, formulas in spreadsheet cells only work in real time. The present. Data exists over here, show it over there. But if you need to automatically store a data point on a recurring schedule—for example every 1st of the month get the current MRR and add it to a list sorted by month—That requires some actual programming. Code. Google Apps Script to be exact, which is JavaScript.

Sure, I’ve hacked apart some jQuery stuff in my day, but writing custom-built JavaScript functions from scratch? That’s above my pay grade. At first, I combed through StackOverflow and Google support forums to see if someone has done this (seemingly simple) task before. Nothing worked.

After 2 days of tearing my hair out on this, I stopped. I wrote out what I needed this simple, magical, script to do, and I posted the job on Upwork. I hired 2 different guys from India, and 24 hours and $30 later, I had the script I needed. I love the Internet.

I spent another few days syncing up my Drip email marketing data to GeckoBoard, again using Zapier and Google Spreadsheets. And a bit more time tweaking the layout of my dashboard until I felt it’s where I need it to be.

Want more of the technical details behind how I connected these tools together?  Get the bonus: Guide to Building a KPI DashboardDownload it all here.

Goals & Targets

One final note: Once you have your slick KPI Dashboard, what do you do with it (besides stare at the pretty graphs all day)?

Set some goals and metrics targets.

GeckoBoard has a cool feature, which allows you to set a specific “Goal” for any metric shown on your dashboard. For example, you can say “I want to get at least 50 new leads per month” or “the number of cancellations shouldn’t exceed 3 per month”. It will then highlight that number so you can see visually, whether that goal has been met, or what percentage of the way there you are.

Metrics goals

Now—based on your historic data—you can begin to figure out how much traffic has resulted in how many leads and how many customers that produced. Then you can start making informed projections and targets to aim for going forward.

And with that information, you’re able to back up your decision to focus time and energy where your business truly needs it at any given time.

Get the Complete Setup: My technical setup guide shows you how I hooked up my KPI dashboard, and provides you with ready-to-use spreadsheets and scripts and instructions. Grab it and use it for free.
  • This is awesome Brian! You’ve gotten me fired up to set this up for my business. 🙂 Thanks for taking the time to write this up!

  • Mind blown, Brian!! This is perfect for a numberphobic loawyer like me. I can track my numbers without the stress of gathering them. Yes, please! Of course, I’ve already shared this gem. You’re brilliant, thanks

  • And this is why I didn’t unsubscribe from Brian Casel when I purged my email of the dozens and dozens of subscriptions I was receiving on a regular basis!

  • Steve Metivier

    Hey Brian,
    As usual, this, along with the Guide to Building a KPI Dashbard, is amazing in its detail (and readability)! Thanks very much!
    I’m curious – did you look at any of the metric tracking services like Baremetrics and ProfitWell? If so, why did you undertake the project to build your own?

    • Thanks Steve!

      Yes, I’ve used Baremetrics and like it. However, that only gives me Stripe customer/revenue data. It doesn’t pull in all of my other metrics that matter to me like leads, traffic, email list. Pulling all of these together in GeckoBoard gives me everything in one place.

  • Thank you Brian, this is awesome!
    I’ve been stuck KNOWING that I needed to track my KPI but with no clue about HOW to do it… this helps me big time. 👍

  • Really impressive, Brian. This is beautiful, and I applaud your great presentation of the details. Love it.