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

    by Simon Breton
    April 14, 2016
    Shot from the movie War Games.

    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.

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

    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:

    You can easily build a dashboard by mixing multiple sources to better understand and measure your actions
    • 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:

    We won’t break the secret of the Facebook Algorithm today. To be honest my data are totally fake (however, the fact that paid reach is always higher is true!).

    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:

    Looking at the formula bar you can see how the two sheets are linked with an average function

    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.

    You can also Schedule refresh and email your data.

    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!

    Please feel free to share other Supermetrics recipes or any feedback you might have with Simon. You can follow him on twitter @soul_shaolin.

    Simon is a freelance data analyst working remotely for brands, media and publishers. After beginning a career path in the cinema industry, he shifted toward digital marketing and analytics. He is passionate about data visualisation and loves curating news from tech, media and the entertainment industry through his blog and twitter.

    Tagged: analytics dashboard data google sheets supermetrics tutorial

    Comments are closed.

  • MediaShift received funding from the Bay Area Video Coalition (BAVC), which receives support from the Bill & Melinda Gates Foundation, to launch the MetricShift section to create a vibrant hub for those interested in media metrics, analytics and measuring deeper impact.

    About MetricShift

    MetricShift examines the ways we can use meaningful metrics in the digital age. We provide thoughtful, actionable content on metrics, analytics and measuring impact through original reporting, aggregation, and audience engagement and community.

    Executive Editor: Mark Glaser

    Metrics Editor: Jason Alcorn

    Associate Metrics Editor: Tim Cigelske

    Reader Advisory Board

    Chair: Anika Anand, Seattle Times Edu Lab

    Brian Boyer, NPR

    Clare Carr, Parse.ly

    Anjanette Delgado, Gannett

    Hannah Eaves, consultant, Gates Foundation

    Alexandra Kanik, Ohio Valley Resource

    Ian Gibbs, Guardian

    Lindsay Green-Barber, CIR/Reveal

    Celeste LeCompte, ProPublica

    Alisa Miller, PRI

    Connect with MetricShift

    Facebook group: Metrics & Impact

    Twitter: #MetricShift

    Email: jason [at] jasalc [dot] com

  • Who We Are

    MediaShift is the premier destination for insight and analysis at the intersection of media and technology. The MediaShift network includes MediaShift, EducationShift, MetricShift and Idea Lab, as well as workshops and weekend hackathons, email newsletters, a weekly podcast and a series of DigitalEd online trainings.

    About MediaShift »
    Contact us »
    Sponsor MediaShift »

    Follow us on Social Media