Lessons learned from dozens of performance dashboard projects— make it so easy your intern can do it

Every day more and more people are tasked with being “data driven”. It’s great in theory, but in practice it can involve a messy process of slapping together unreliable & inscrutable data. Without any guidance it can cause more problems than it solves. So I’ve put together this article to help you avoid some of the pitfalls.

Brought to you by: The Excel Dashboard Toolkit and The Data Studio Toolkit

I’ve been working on dashboard projects for the last 7 years, including some fun ones with Google, Lego, Microsoft, and Philips. This article highlights some of the lessons I’ve learned along the way that can be applied to all projects and it will hopefully make your project just a little bit easier.

Start by listing out your limitations

At their core, dashboards are a visualization of your data. It might seem obvious but that means you can only use data that is actually available to you. You’re limited to whatever data is already being collected and stored. You might think you have sales data, but there’s a decent chance that data is spread out across multiple sources or being processed by hand and very hard to use.

So to save yourself from a case of over-promise-under-deliver, don’t even think about your design until you know what data you have and/or how challenging it will be to get it. There are a few basic guidelines to keep in mind:

• Data that is manually collected by a human will eventually have errors — I once worked on a project involving marketing data from teams in 40+ countries. Data was manually collected and collated every week from each of those teams. There wasn’t a single week when someone didn’t accidentally mistype a value or send it in late. There was even a team member whose full time job was managing that single point of data collection every week.
• Make sure you actually see an example of the data being collected. Trust but verify — oftentimes your dev team/analyst/etc. will genuinely think they are collecting a particular metric but they may not have actually looked at it to make sure it’s accurate or complete.
• Consider your historic data — people often forget that just because you have a metric today, doesn’t necessarily mean you have that same data going back forever into the past. If you’re hoping to see year-on-year changes, then you need to confirm you have the past 2+ years of data.
• Consider the time granularity— are you collecting your data daily, weekly, monthly or even quarterly? If the data comes in every month, you won’t be able to see daily or weekly changes.

I suggest that you make a spreadsheet to organize your dimensions/metrics. I typically structure my spreadsheet with these columns:

• Metric name
• Metric source
• Metric category
• Time granularity
• How long you’ve been collecting the metric
• How the metric is collected (manually or automated)
• How is the metric formatted (this can just be a sample value)
• Is the metric a must-have or a nice-to-have

Now put the pieces together

Now you know what you can put in your dashboard but you need to think about what you should put in your dashboard.

I have a deep dive on this topic you can read here: https://datastudio.medium.com/how-i-design-dashboards-in-data-studio-part-1-data-hierarchy-and-telling-your-story-7fc6061017f

How I design dashboards in Data Studio

But, for those that want the quick version, I like to use a narrative pyramid approach:

• Start by trying to describe what you’re building in 2–5 words.
• Then try describing it in a full sentence.
• Then describe it in 2–3 sentences.
• Then a paragraph.

If necessary, keep going until you have as many sentences as you need to fully explain what you’re building. Here’s an example for a Facebook Ad dashboard I was working on:

Now we want to map this to our dashboard considering that hierarchy.

What are the minimal metrics needed to answer the top-most section of the pyramid? If it boils down to one core metric, then that’s probably what you should highlight on the page.

Then move to the next section. What further metrics/guidance are needed to capture that section? What guidance will you need to add to make that make sense? These will most likely become the secondary or supporting metrics.

Continue all the way through until you have a sense of what metrics you need on the page, the hierarchy of those metrics, and the narrative that stitches them together.

In this example I knew I needed to explain the top KPIs in plain english (eg. “How many people have seen your ads?”), that I needed the other KPIs to include a trendline to quickly see performance over time, and that I needed a drill-down section that showed the individual ads and/or individual campaigns to see what was working best.

Start building — Prototype

After you’ve mapped out what you actually want to build, it’s time to kick things off! But wait — don’t dive into the deep end yet.

I highly suggest you start with a prototype.

Whether you have an internal dev team or you’re planning on using a service like Looker or QlikSense, it’s a good idea to start with a prototype. This can save you buckets of money and effort down the line.

I have not seen a single project where the end users didn’t make a big realization about what they could’ve/should’ve/would’ve included in their dashboards after it was delivered. There’s a lot that you can’t predict. It’s better to learn these things and get feedback before you’re locked into a $X,000/month contract or before you’ve rearranged your whole dev team.

So how do you build a prototype? Use a free or low-cost tool like Excel or Data Studio. You can DIY if you’re inclined because these services have a fairly easy learning curve, or you can simply hire someone to build one for you (this is one of my specialties, feel free to ping me if you need help).

You would be amazed how far you can get on these free/low-cost tools. For context I built both of these using them:

Built with Data Studio

Built with Excel

The idea is to build a prototype that illustrates the functionality and plugs in real data. This gives you something you can use to demonstrate the project’s value to your team and allows you to get feedback from the people that will actually use your dashboard. The earlier you get that feedback the better.

I’m a big fan of Data Studio. I’ve been using it since it was in beta and love all the free data connectors that are available. It’s easy/quick to make changes and adjustments once you’ve built your report. Any data that you can’t connect, can be manually added to a google sheet which integrates directly into Data Studio. There are lots of talented Data Studio specialists out there. Plus if you did the last two exercises, you already have a well-organized spec sheet for them to use.

On the other hand if you want to keep it simple and use a tool you’re probably already familiar with, you can just use Excel. Manually add your data and add your ideal charts/metrics. Excel will force you to consider the format of your data — basically if you can add the metric or build the visualization in Excel then it can be built better by your dev team. You can also bring on an Excel dashboard specialist, they can make Excel look as custom as you want (see example above).

Start building — v1.0

So you’ve got your prototype, you’ve shown it to your stakeholders, received lots of feedback and made some adjustments. You’re now ready to get this thing built!

Full disclosure: this part probably can’t be handled by your intern but I’ll give you some guidance to make it as pain-free as possible.

You’re going to take one of two paths:

1. A dev team is going to build it for you from scratch
2. Use an existing dashboard software/platform

If you’re using a dashboard software you’ll most likely have a customer success person who is going to walk you through the process, help you find someone to get you set-up and potentially offer some training. There’s not much I can add here other than this — you will be told that you can have everything you want from their software but you will inevitably not get everything you want. Don’t set expectations with other people on your team until you actually see the front-end with your data flowing into it.

If you’re using your own dev team or hiring a dev agency to handle it, then you’ll be going a bit deeper. I can’t explain all the ins and outs of managing a project with a dev team but I’ll give you a few tips that will help keep you and the devs happy.

• Remember that every feature will take time to build, probably more time than you think. Assume that there is no such thing as a “small” change or a “quick” request. There’s no easier way to make enemies of a dev team than saying “this should be a quick change”.
• Take the time to go deep on the exact functionality you need vs the functionality you want. This will make life a lot easier when you have to set priorities or if you need to slash features.
• Assume it will take twice as long as the estimated delivery date to complete the project. Projects that involve pulling performance metrics can hit all sorts of roadblocks, so you’re going to be better off assuming you will hit a few of them.
• Dedicate some time to independently validate your data. Once you have your dashboard you’ll need to compare it against something — a source of truth. Randomly select a few metrics across a few date ranges and compare them to data you manually collect. It’s hard to maintain accuracy if you’re pulling in data from lots of different sources and even with a QA/QC team, it will be worthwhile to check the data yourself.

Phew! That was a lot! And those are just the basics that I think you can apply to almost any project. I’ll go a bit deeper in future articles and cover topics like rolling out your performance dashboard to your team and using your prototype to win budget and buy-in for your project.

Did you find this article helpful? You can support my writing and level up your reporting by using one of our toolkits for Excel and Data Studio:

The Data Studio Toolkit — The Data Studio Toolkit gives you a set of copy-pasteable dashboards and UI elements that will make your own reports stand out. In this toolkit you get 6 dashboards (3 light and 3 dark themed) along with colors, guidelines/tips/tricks and UI elements in 7 different colors.

The Excel Dashboard Toolkit — The Excel Toolkit is built using Excel’s standard features (no plugins or custom coding required). You get 125 charts (25 designs in 5 different color palettes) along with header designs, shape styles, icons and RGB values.

If you need some help and this article struck a chord with you then I may be a good match for your project. I’ve worked with everyone from tech giants to the smallest startup teams, so don’t hesitate to reach out. If you’d like to chat you can ping me via LinkedIn or my website: datastudiopro.com.