Many companies still use spreadsheets to track performance data. Here’s everything you need for using Google Sheets with Databox to visualize performance.
Product and Design | Feb 11
Gasper Vidovic on February 6, 2019 (last modified on February 7, 2019) • 10 minute read
Every organization, no matter the size, uses some type of tabular data to keep track of their key metrics. It’s long been the simplest way to condense a lot of information into one place.
But let’s be honest — when it comes to quickly pulling useful insights, or even sharing performance across your team, it’s not the best.
Given that Databox is great at those two things, many of our users have asked for a way to share and present their Google Sheets data using Databox.
But, building a Google Sheets integration wasn’t as easy for us as building integrations with other tools. Spreadsheets can categorize data in infinite ways, presenting a much bigger development challenge for us.
Plus, to make it successful, we knew we needed to make it as easy to visualize data from Google Sheets as it is to visualize data from our other integrations. Like our other one-click integrations, we wanted to make it possible for our users to display metrics in a matter of seconds.
Today, we’re excited to share the result of our months of hard work to make all that happen.
Here’s a quick video tutorial of how it works. For a more in-depth walk-through, as well as specific use cases for getting started with the integration, continue reading below.
In the Databox web app, navigate to Databoards and create a new Databoard. In the left-hand panel, switch to the ‘Google Sheets’ source.
Once you connect your spreadsheet, you can create your first custom metric.
The Google Sheets integration is powered by Databox’s Query Builder tool. Query Builder provides a user interface (UI) where non-technical users can visualize their data just by making selections in the interface. With it, there is no need to learn a new query language or even write a line of code.
So, anyone on your team can use Query Builder to extract custom metrics from your spreadsheets in just a few clicks.
Here is a full explanation of each of the form fields that will help you build a custom metric:
On the right side of the Query Builder form, there is a live preview of your data, which also gives you an option to switch between visualization types like line graphs, pie charts, and numbers. We show only the appropriate visualization for the data you’ve selected.
Once the query is saved, your new metric will appear in the left-hand side panel (under this Google Sheets connection) and you can easily drag and drop it right into your dashboard.
Voila, that’s it!
Spreadsheets allow you to store any kind of data including CSV exports from other SaaS platforms, data you manually enter into a spreadsheet, or data you automatically push to spreadsheets via a script.
Obviously, there are unlimited use-cases. But, here are two examples that will give you a better sense of the possibilities.
Let’s take a look at this spreadsheet.
It’s a log of successful purchases for my imaginary online book shop. It includes the date and time (timestamp) of each purchase (column A), product name (B), revenue of each sale in US Dollars currency (C), referer (D), customer name (E), country of residence (F) and email (G).
We can make many custom metrics out of this spreadsheet.
To get the Revenue metric, select column C (the whole column “C:C”) for Values and for timestamps, select the whole column “A:A” for Dates.
For this kind of an example, the correct Data Type is the ‘Unprocessed data (event value)’. This Data Type supports cells populated with event values for the specified Date/Timestamp and for longer Date Ranges (i.e. This Month, This Quarter, etc.), with multiple events, data will be aggregated and the total will be displayed for the time period.
The example spreadsheet includes more data than just revenue, of course.
So, let’s also create a new metric with ‘Country’ dimension (column F), to see how my revenue is distributed across different countries. To create the ‘Revenue by Country’ metric, the query would look like the screen grab below, where I chose column “F:F” as the Dimension value:
In this case, I switched to the ‘Pie’ visualization because I’m interested in the total distribution in 2018 by country.
One custom metric can use only one dimension. But, if you want to view revenue by different dimensions (besides country), create a new query with a different dimension selected and leave the Values as column C and Dates as column A. For example, I created additional metrics so I can visualize revenue by referer and revenue by product, as you can see in my final Databoard for my book shop embedded below:
Another common use-case for Google Sheets is when a company manually enters key performance indicators (KPIs) into a spreadsheet each month. As an example, take a look at this spreadsheet:
It’s a spreadsheet template with seven KPIs for my imaginary company.
It includes the months (row 1), sessions (row 2), new customers for each month (row 3), a calculated conversion rate (row 4), monthly revenue in US Dollars currency (row 5), average revenue per customer (row 6), expenses in US Dollars (row 7) and profit (row 8).
(As an aside, the conversion rate is calculated inside this Google Sheet. I could have also set up this calculation inside Databox with the use of the Data Calculations feature.)
Let’s make a Databoard from this worksheet…
To get the Expenses metric, let’s select the row 7 (the whole row “7:7”) for Values and months (the first row “1:1”) for Dates.
For Dates, we have entered Months in the spreadsheet, but these are actually the first day of each Month.
For this example, the correct Data Type is the ‘Daily values (processed data)’. This Data Type supports cells populated with daily (or in this case monthly) values for the specified Date/Timestamp.
In Databox, the latest updated value will be displayed (in case you had multiple columns with the same date, but different times.). For longer Date Ranges (i.e. This Year, This Quarter, etc.), data from each day will be aggregated.
In our example, monthly data will be stored in the first of each month. To have more granular data, you could store daily values in your spreadsheet, too.
After creating all the custom metrics for the other columns in my spreadsheet, my final Databoard for my company then looks like this:
If you’re using Google Sheets, I’m sure you’re itching to give it a shot already. (Bonus points if you read this far — we love your studiousness!)
The Google Sheets integration is available with all plans that include our Query Builder functionality. If your plan has access to Query Builder, you can login and start using the Google Sheets integration, as of today. If you are using a free, agency free or basic account, log in and request a trial of Query Builder. Not using us at all? Start with a free account.
Product and Design | Feb 11
Product and Design | Oct 23 2019
Product and Design | Jun 26 2019