Building a dashboard is hard, making it useful and easily updatable is even harder. If there are a lot of marvellous 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 facing when building a dashboard today.
What’s 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…) in 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 the charts or the perform calculation you need.
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 : how to pull data from Google analytics and Facebook.
3 — The dashboard : quickly organise your data in a presentable and easily updatable way.
First Step : Install and get your first data
In this first step I’ll show you how to install Supermetrics in a Google spreadsheets 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 “Lauch”. Supermetrics interface should appear at 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 in eight different sections :
Data Source : Allow you to select, for exemple the GA account or FB account you want to work with.
Select pages/apps : Helps you to select the view or pages within your GA or FB account.
Select metrics : Lets you select the metrics you wants (sessions, users,reach, 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 raw or column.
Filter : This function allow you to filter your query. We will use it later in this tutorial to filter Facebook traffic acquisition.
Options : As its name implies… Since this note aims to introduce the very basics of Supermetrics I let you discover what’s in there by yourself.
- You can now add your first 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 log in in the account of this new source and allow Supermetrics to access to this new source. 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 dimensionin “split by” and so on… when you are ready just click “Get Data To Table”. You can find below the query I built for this exemple 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 few rows under the first one. — One with Google Analytics Data — 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 new one.
Let’s keep working.
The Mechanics : Pull data from various sources in the same sheet and add filter.
Now let’s say we want to compare the FB reach of a page with GA website’s sessions and users acquired from Facebook (over the same period of time).
- First we’re going to update the GA query we’ve just created. Select one of the cells from the GA data table. Supermetrics should recognise 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 dropdown 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 GA data table. Build a query with Facebook as a source. Choose your page. Select the same date range as the GA query. For the metrics you can select Organic reach, Paid reach and Viral reach, for exemple. Then launch your query, you should get something like this :
One you gathered the data you want you can easily play with it and build the chart you want :
Here is another great advantage of Supermetrics. You can access data that aren’t normally available. Here for example viral reach isn’t accessible through Facebook dashboard or Insight export.
The dashboard : organise and update your data.
We’re almost done. For the last step, I’ll show you how I organise and refresh my data. It’s not the only way to do it, I’m sure their is plenty of other possibilities.
- Add a second sheet to your document. Name the new one “dashboard” and rename the first one “data” (obviously the one with the 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 confortable with spreadsheet you should handle it easily. Here is what I’ve done from the two table of data I’ve had 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 everyday and pull all the data from the last 7 days. To do this, go to add-ons, “Supermetrics” and “Refresh all”. That’s it.
Here is the basics to build a dashboard with Supermetrics.