Are you tired of spending your time collecting, organising and presenting data while someone else is doing the interesting job ? Would you rather be a Reporting Squirrel or Analytics Ninja ? Here are five tips to automate your dashboard or reporting and help you save a lot of time so you can actually deliver valuable work.
All the examples in this article are illustrated in the following spreadsheet: Dashboard tips automation. Make a copy of the spreadsheet to get the edit access.
1 . Organize your spreadsheet
This may sound too obvious but I’ve seen so many unorganized spreadsheets. When you start importing a lot of data, especially from a lot of various sources, you can be easily overwhelmed. You end up losing time switching between a ton of sheets without realizing it.
https://docs.google.com/spreadsheets/d/17NJx9D0VMjn-vacOfiLkeJIDnPSQw4vogTfxd5CuGek/edit?usp=sharing
I usually split my spreadsheet into three parts:
- One or several sheets called Data. These sheets are used to gather your raw data. I view it as an unexploited gold mine.
- The Dispatcher sheet is where you start organizing your data the way you need it to fit in your dashboard structure. The dispatcher sheet can still be messy but it must only contain the data you’ll need for your dashboard or reports.
- The final sheet is the Dashboard.
You may also notice a selector sheet in the screenshot above, which we will talk about later.
Stay with me! Giphy
2 . Automatically import and refresh data with Supermetrics or similar tools
Supermetrics is a great tool to help you easily import a ton of data into your spreadsheet from various sources in just a few clicks! Following the spreadsheet organization above, I recommend you make one big query and pull all the data you need into your data sheet. It makes it easy to refresh and helps you keep some consistency in your data source.
One of the best advantages of Supermetrics is the refresh schedule option. You can easily call all the data you need for the last year and this year with a daily refresh. It might be a lot of data for one sheet, but using the right formulas and functions you can organize these data very quickly.
3. Use VLOOKUP and MATCH functions
I’m guessing that a lot of people are already familiar with the VLOOKUP/MATCH function combo. However you really need to make it one of your bread-and butter-tools, so you know exactly when and how to use it.
Dynamically get the channel you want for a specific month
Here is a simple illustration. Playing with Google Analytics acquisition channel, sources or medium is always a pain. You end up mixing various datasets going against the Google Analytics acquisition structure. So here is my advice: Call all the data you need from all the channels you need in your raw data sheet. Then in your dispatcher you can just use VLOOKUP and MATCH functions with some cell references so you can dynamically get the value you need. It’s especially useful when your boss is changing the metrics to be tracked every hour!
4. The query function
Once you’ve mastered the VLOOKUP/MATCH combo, you’ll rapidly see some strong limitations. You always need some cell as reference, plus you actually need to write and copy the formula for each data you need.
With the query function, which is a kind of easy-to-use SQL formula, you can easily and effectively query the data you want from your raw data by crossing row and column parameters. It also allows you to build dynamic pivot tables and get the aggregated data you want without adding another sheet! The best part is that you can add cell references in almost every part of the formula and build some nice dynamic selection.
Build dynamic chart for free !
Here is what a query formula looks like. It might sound complicated at first but it’s actually very easy to understand:
=QUERY( ‘data — source 1(fb)’!A:F, “select A, “&Selector!B7&” where A<date ‘“&text( Dashboard!A2 ,”yyyy-MM-dd”)&”’ label A ‘Date’”)
First you need to add the range of cells referring to your raw data (‘data — source 1(fb)’!A:F). Then fill the select function with the letter directly referencing the name of any column of your data sheet (obviously you will choose the one that contain the data you want!). The letter is literally the name of the column you want to select, independent of your cell range. The where function works as a filter in this example. You select the column you want and give it a conditional statement. So in the example above, I’m selecting the data from the column referenced in cell B7 (“&Selector!B7&”) and where the column A (where A) is less than the date provided in cell A2 (Dashboard!A2).
For a better understanding I strongly encourage you to take a look at the Google Spreadsheet I have shared with you. Make a copy of the sheet to get edit access.
As you will start to see, the magic with the Query function is that almost everything can be defined with a referenced cell. With a validation cell (and the selector sheet) you can dynamically build your formula.
Almost done! Are you a reporting squirrel or analytics ninja? Giphy
5. Build your own data collector with Google Apps Script
Here are probably the most advanced tip I can give you. It happens that tools like Supermetrics don’t always have all the data you need. It also happens that you want to archive dynamically calculated data on a daily basis. To solve these two main issues you need Google Apps Script. It’s free and not that complicated to use.
I have to admit that I totally stole this idea from an article written by Ben Collins, accessible here. It will give you all the details on how to save data. Here is the main part of the script presented in this article :
function saveData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var data1= sheet.getRange(‘Sheet1!A1’).getValue();
var data2 = sheet.getRange(‘Sheet1!B1’).getValue();
var data2 = sheet.getRange(‘Sheet1!C1’).getValue();
sheet.appendRow([data1,data2,data2]);
}
You don’t need to know a lot of Javascript (none at all, if you’re willing to be a bit geeky) to use this script. Just go to the Google Script editor of any sheet, copy and past the script above, try to understand it and you should be able to do something with it! I’ve recently built a script allowing you to automatically collect and save Daily, Weekly and Monthly reach from FB, accessible here.
This article doesn’t mean to be a full tutorial, but rather aims to show you that with a little bit of work it’s kind of easy to almost fully automate your daily or weekly dashboard, and almost for free.
Please if you have any questions, use the commentary section or contact me directly on Twitter @soul_shaolin!
Simon Breton 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 visualization and loves curating news from tech, media and the entertainment industry through his blog and twitter.
