How I Built a Simple, Flexible and Efficient Dashboard with Supermetrics (and Why!)

Building a dashboard is hard; making it useful and easily updatable is even harder. If there are a lot of marvelous tools out there to help you accomplish this task, the one you really want is always too expensive and, let’s be honest, most of the time far from perfect. Beyond the tool, there are at least two others challenges you’re likely facing when building a dashboard today.

First, the way we build a dashboard has changed. As Marius Moscovici writes in a recent article, the old business intelligence dashboard might be dead. What we need are real-time, actionable data. If this doesn’t make sense to you, I can only encourage you to read Lean Analytics.

Second, most of the time, building a dashboard doesn’t come without insights and recommendations. So in addition to pulling data, reviewing them and building charts, you also have to dig for analysis and then summarize results. “Dashboards can’t wait”, thus you’ll need to automate everything you can!

To make it clear, building a dashboard is about communicating real-time data and detecting insights to solve business problems.

When it comes to Supermetrics, I’m not saying I have the perfect answer. But it’s the best way I’ve found so far to tackle most of the problems I previously described on a limited budget. So let’s see what Supermetrics is.

What is Supermetrics?

Supermetrics is a business analytics software also defined as a “data grabber.” It will help you to pull data from various sources (FB, Twitter, GA, etc.) into one place like a Google Spreadsheet or Excel file. Once you’ve got all your data formatted in the same way in one sheet, you can easily build charts and perform calculations.

The tutorial

This step-by-step tutorial is broken down in three steps :

1 — The Set Up: Instal Supermetrics on a Google Spreadsheet and learn the basics.

2 — The Mechanics: Pull data from various sources in the same sheet and add filters.

3 — The Dashboard: Quickly organize your data in a presentable, easily-updatable way.

Let’s go!

So easy a child can do it!

The Set Up : Instal Supermetrics on a Google Spreadsheet and learn the basics

In this first step, I’ll show you how to install Supermetrics in a Google spreadsheet and how to grab your first data.

• Open a Google Spreadsheet.
• Go to “Add-ons” then “Get add-ons”, search for Supermetrics and add it. It will ask you the permission to access your google account, click “allow”.
• Once the access to Supermetrics is open, you should find it in the google spreadsheets add-ons list. Open the Supermetrics drop down menu and click “Launch”. Supermetrics interface should appear to the left of your spreadsheet.

You can now start playing with the Supermetrics interface. You should easily find your way around, it’s not that complicated. The Supermetrics interface is split into eight different sections :

Data Source: Allow you to select accounts you want to work with, for example, the Google Analytics or Facebook.

Select pages/apps: Helps you to select the view or pages within your accounts.

Select metrics: Lets you select the metrics you want to analyze, for example, sessions, users,reach or link clicks.

Split by: Allows you to split you metrics by day, week, month or even post, page URL, etc. You can split your data by row or column.

Filter: This function allows you to filter your data. We will use it later in this tutorial to filter Facebook traffic acquisition.

Options: As its name implies. Since this article aims to introduce the very basics of Supermetrics, I’ll let you discover what’s in here on your own.

• You can now add your first data source. Please add Google Analytics or Facebook Insight to follow the path of this tutorial. Every time you add a new source, you will need to login to the account of this new source and give Supermetrics access. As you can see below, a lot of sources are available through Supermetrics:

• After adding a source, select a view, then a data range, then a dimension in “split by” and so on. When you’re ready, just click “Get Data To Table.” Below is the query I built for this example with my personal website:

Supermetrics pulls the data starting from the cell you’ve selected when you launch your query. You can easily get another table of data rows under the first one — one with Google Analytics Data and one with Facebook data, for example.

Advanced tips: If you go to the Supermetrics menu in add-ons and click “Manage queries”, Supermetrics will open a new sheet with a list of all your queries detailed. From here you can easily edit your query or build a new one.

Let’s keep working.

Supermetrics is really useful but still doesn’t give you superpowers…

The Mechanics : Pull data from various sources in the same sheet and add filter

Now let’s say we want to compare the Facebook reach of a page with that same page’s Google Analytics sessions and users acquired from Facebook.

• First, we’re going to update the Google Analytics query we’ve just created. Select one of the cells from the GA data table. Supermetrics should recognize the query by updating the interface and offering you “Query actions.” From there click “Modify.”
• Go to filter section, then “Add filter.” Select “Social Network” in the first drop-down menu on the first field. Choose “Contains” in the drop-down menu on the second field. Write “Facebook” in the last entry. With this, we’re going to query only the sessions and users coming from Facebook.
• Once your filter ready click “Apply changes.” Data should refresh.
• Select a cell under the Google Analytics data table. Build a query with Facebook as a source. Choose your page. Select the same date range as the Google Analytics query. For the metrics, you can select Organic reach, Paid reach and Viral reach.
• Launch your query and you should get something like this:

One you gathered the data you want, you can easily play with it and build the charts you want:

Here is another great advantage of Supermetrics: You can access data that aren’t normally available. For example viral reach isn’t accessible through the Facebook dashboard or Insight export.

The Dashboard : Quickly organize your data in a presentable, easily-updatable way

We’re almost done. For the last step, I’ll show you how I organize and refresh my data.

• Add a second sheet to your document. Name the new one “dashboard” and rename the first one “data”
• Now you just have to link the data from the first sheet to the second by adding some calculation or charts. Even if you’re not very comfortable with spreadsheets, you should be able to work it out. Here’s what I’ve done from the two tables of data I previously gathered:

4. The real trick is that Supermetrics allows you to refresh all your data in one click. If you’ve set up your data range correctly, let’s say “Last 7 days,” you can refresh your sheet every day and pull all the data from the last 7 days. To do this, go to add-ons, “Supermetrics” and “Refresh all.” That’s it.

Is that all?

That’s all for this article. First, I wanted to cover the basic aspects of Supermetrics. Second, I wanted to show that building a dashboard is nothing too fancy. Obviously communicating with data is much more complicated than organizing a spreadsheet but you don’t need a 10 million visitor website or a social media war room to start doing it. Think about the metrics you need, dig deeper to understand them, build your own KPI based on your business needs. Tools like Supermetrics doesn’t only help you to do a better job, they also help you better understand what all this data stuff is about!