Product and Design

Google Sheets, meet Databox.

The Google Sheets Integration is Live! Turn your Google Spreadsheets into interactive dashboards that are available on desktop, office TVs, and your mobile devices.

Avatar Gasper Vidovic on February 6, 2019 (last modified on February 7, 2019) • 10 minute read

Over the last year, Google Sheets has been the most requested Databox integration–by far. Today, that integration is now live.

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.

How to Integrate Databox with Your Google Sheet

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.

 

Step 1: Connect Google Sheets

In the Databox web app, navigate to Databoards and create a new Databoard. In the left-hand panel, switch to the ‘Google Sheets’ source.

Step 2: Create a custom metric

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:

  1. Custom Metric Name: You can enter a name for your metric. Alternatively, leave it blank and skip to the next fields. Once you choose a Value from your spreadsheet, we will automatically populate that Value’s row or column header as the custom metric name.
  2. Value (Metric): The first step is to select a column, row, or cell range that hold the numerical values you want to visualize. Put your cursor in the Value form field, then make a selection directly on the spreadsheet below by highlighting the cells in the spreadsheet with your mouse. You can also enter a cell range manually by using the ‘A1 notation.’ (If you are unfamiliar with that concept, you can read more about ‘A!’ notation here). Values must be numerical values, not strings. You can use currencies or other units with numeric values and we will recognize them. In fact, Google Sheets does a good job of automatically formatting the cells, so you shouldn’t need to worry about formatting them inside Databox.
  3. Date (Timestamp): The next step is to select a column, row, or cell range with dates (timestamps). Again, when this input is selected in the Databox Query Builder interface, you can make a selection directly on the spreadsheet below it by highlighting the cells you want to visualize. Values and Dates must have corresponding values, so make sure you select a similar range of cells. Databox accepts any ‘Date’ or ‘Date time’ that is formatted inside Google Sheets. To ensure that all cells are formatted correctly, you can select them and go under “Format > Number > Date” within your Google Sheet. If you have hours added please select “Date time” format. If your Date cells are formatted as Strings in Google Sheets (not recommended), we will assume your data is the US date if in the format (Month/Day/Year) and if no day is specified (only Month/Year), we will store values on the first of each Month.
  4. Dimension (optional): If you have additional information in your spreadsheet, for example, Country of purchase, Referer information, Browser, Product name, etc., you can reference cells or ranges for Dimensions by using ‘A1′ notation or by selecting it directly on the spreadsheet preview below the Query Builder form. Each metric can support up to one Dimension range. To visualize multiple Dimensions, you must create another custom metric.
  5. Number format and Scale (Read more about Format and Scale settings in Databox in this article).
  6. Data type: This is the last setting that is important for the correct visualization of your data. The data type describes the data you’re pulling into Databox and depends on the use-case and kind of values. There are three datatypes that are described right in the interface. You can learn more about Data types in our help documentation. If you are not sure whether you’ve selected the right data type, you can use the live preview as a way to determine whether your data is being summarized correctly…

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.

Step 3: Drag and drop your custom metric onto a Databoard

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!

PS. In addition to visualizing data in a Databoard, you can also use your Google Sheets custom metrics in Alerts, Scorecards and in Data Calculations.)

Common Use-Cases

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.

(Example 1): Sales (Event) Data

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:

 

(Example 2): Monthly (Overview) Data

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:

 

Tips & Tricks

  1. My first tip is to read our help articles related to Google Sheets. We describe all the Query Builder fields and provide more examples.
  2. When you’re missing some data or get stuck, the best way to troubleshoot is to check out the Query log. Navigate to the Query Builder page (under ‘Data Manager’), search for your custom (query) metric and then check the ‘Log’. You might have changed your spreadsheet somehow by moving rows and columns or something else. The log will guide you in the right direction.
  3. When referring to Values, Dates or Dimensions, you should usually select a whole column or whole row, rather than fixed ranges. That way, when you add new rows and columns, the data will automatically be recognized and your custom queries won’t need to be changed.
  4. If you see discrepancies when displaying longer time periods, you might have selected the wrong Data Type in the Query Builder interface. Read this article to learn more.
  5. Ensure you have correctly formatted your Dates in your spreadsheet (using Date or Date Time format) and Values (Numbers or Currency). To define the correct format, open your Google Sheet and select the cells (row or column) and navigate to ‘Format > Number > …’.
  6. If it looks like your December data is stored for November, check your timezone settings. When connecting a spreadsheet, we take the timezone setting from Google Sheets (check under ‘File > Spreadsheet settings… > Time Zone’). It has to be the same as your user timezone in Databox (check under ‘My profile’).

Connect your own Google Sheet

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.

About the author
Avatar
Gasper Vidovic is Director of Product at Databox. Also a traveler, lawyer & icon enthusiast.
You may also like...
Read more

Now in Databox: Monitor All of Your Important Business Metrics From One Screen

With the all-new Metrics screen, it only takes a few clicks for anyone on your team to create a personalized view of all the business metrics that matter.

Product and Design   |  Oct 23

Read more

8 Proven Strategies for Converting Free Trial Users to Paying Customers

We asked pros from 60 SaaS businesses to share tips for converting trial users to paid customers. Here’s how they’ve improved their paid activation rates.

Product and Design   |  Jun 26

Read more

Inside the Design of the New Databox Logo

We recently launched a new logo. Here’s why we did it and the thought that went into the design.

Product and Design   |  May 7