How to Visualize Total or Current Values using Google Sheets Query Builder

In this article we will describe how to create a Custom Metric from Current, Daily Total or Overall Total value using Google Sheets Query Builder.

Most common use-cases where using Total values or Daily values Data Types is correct will be covered here. Read more about data types here.

Note: We are syncing Google Sheets data Hourly. Date/time will be saved based on Databox sync time and won't be related with Date/time of updating a document if there is no date/time information beside entries in your document.

Use-case

In the spreadsheet example, three agents (Mark, Anna and John) are updating cells multiple times per day with a number of total calls for the current day.

This article is also useful in case you want to connect the Google Sheet document where:

1. You are updating one cell with a total value (want to see only current value visualized)
2. Your document is updated with total values listed by day or with date/timestamp information (new total value is entered in a new row every time)
3. You are exporting total or current values from another tool using a script and want to visualize only latest entered total value from Spreadsheet in Databox (don't want to aggregate numbers in Databox)

Goal

We want to visualize this metric using three Number visualizations and Pie Chart with % of calls for each agent and a total number of calls.

This Metric could be also visualized using all other available visualizations (also Line and Bar Chart to visualize patterns over a longer period - using daily, weekly or monthly granularity).


IN THIS ARTICLE

Related use-cases

Preparing a document

Before creating a Custom Metric please check and make sure that:

  • All value cells are formatted correctly as Numbers and not Strings. To do so select all the cells in Google Sheets and go under Format > Number > Number.
If you have currencies or other units added numbers, please use the appropriate format by going to Format > Number > More Formats > More currencies. If you’re using US Dollars, you can also select Format > Number > Currency as your format.
  • In columns (A and B) which will be used, there are no other values entered (ie. Totals).
It is unnecessary to add that to your spreadsheed, since totals could be calculated in Databox.

  • We are syncing Google Sheets data Hourly. Date/time will be saved based on Databox sync time and won't be related with Date/time of updating a document.

Creating a Metric

In the next steps, we will show you how to create a Metric using a Query Builder. At this point please connect your Google Sheets document with Databox.

If you are already familiar with steps for creating a new Databoard and connecting Data Source, then skip first two steps from this guide.
1
 At the start, we will create a new blank Databoard and open it in Databox designer. To do that hit the green + Create New button ( Databoards) and start building Databoard from scratch with selecting Start Blank option.

2
 Connect Google Sheets document with Databox: From the left-hand panel (Datablock Library), select Google Sheets Data Source and click on a green Connect button to connect a new Google Sheets document.
Login to your Google account when the popup window appears. Choose the appropriate Google Sheets account and File which you want to connect and then click on Activate. Selected Spreadsheet with all tabs will be connected.
Read a step by step guide about connecting Google Sheets here.

3
 After Google Document is successfully connected Click on + Create Custom Metric.
IMPORTANT: After this step, a Query Builder for Google Sheets will open in a Popup window.
4
 Next, we will select a Spreadsheet from your connected document. To do this, choose the Calls Spreadsheet at the bottom of the screen (Scroll down if you can't see this part of the window).
5
 For selecting the number of total daily calls as a value click on a Value field and select the whole B column (current numbers of total calls for today).

A Custom Metric name will be automatically populated with the title from the selected column (first row).
6
 Since there is no Date information in a document, leave Date field empty. All values (number of today's total calls) will be saved on a current date/time (today) in this case.
7
 Choose a column where you want to pull dimensions from. In this case, we want to use agent names from A column as Dimension. With creating a custom Metric with dimensions, the agent's name from Google document will be saved besides the value (total number of daily calls). This way all values can be saved as one "attributed" Metric (Custom Metric name could be changed to Calls by Agent now).
After selecting Dimensions, visualization preview on the right side will be shown. You can choose between four predefined visualization types (bar-chart, line-chart, table or pie-chart). Selected visualization will be saved with a Metric.
8
 Selecting the correct Data Type is a very important step when creating a custom Metric using Google Sheets Query Builder. For this case change Data Type to Daily values (processed data).
Values in a spreadsheet are updated multiple times per day and are showing a daily total number (value/number of calls for each agent resets every day) we want to see only the latest entered number for each day. Daily values data type will always show only the latest number for each of the agents for a day. Data will be aggregated for longer Date Ranges.
9
 Click on the green Save Query button to save a Custom Metric.

Visualizing a Metric

After the Metric is saved in a Query Builder, it will be listed and available from the left side panel (under custom tab).

1
 To add a Metric on a Databoard just Drag and Drop selected Datablock from the left side panel on a Databoard.

2
 To use this Metric with other  Visualization Types click on a Visualization types icon on the top left side below the Datablock Library icon. We will add an empty Number Visualization on a Databoard in our example.

Read more about available visualizations here.
3
 For selecting a Metric for just added Number Datablock click on a Datablock and then select Google Sheets Data Source and Metric/Dimension from the right sidebar and change Block title. We selected Mark as Dimension in this case, because we want to see the total number of today's calls for Mark on this Datablock.


Similar use-cases and examples:

1. Updating one cell with an overall Total Value

USE CASE

In this simple document, we are updating only one cell (B2) with a total account balance.

GOAL

As a result, we want to use only the latest updated amount and visualize it on a Datablock with Gauge visualization to see progress towards reaching a Goal.

QUERY SETUP

  • Metric Name: Account Balance
  • Value: B1 cell

    Select by clicking on a specific cell where a value is entered.

  • Date: Empty

    There is no Date/time information in this document - values will be pushed on the current date (today).

  • Dimension: Empty

    We don't have any additional information besides number to filter by. If there is only one specific cell selected we don't need dimensions selected.

  • Number Format: Will be automatically selected if currency format in Google Sheets document is set correctly for this cell.

  • Data Type: Total values will be selected automatically if only one cell is selected.

Choose Total values Data Type every time when you are creating a Metric from range or a cell where data doesn’t ever reset but represents the current status. The latest value pushed is the only one that matters (Metrics with this Data Type are usually visualized with an All time or Today date range).


2. Daily Total values listed by day (with date / timestamp)

USE CASE

In this example, the company is entering the total number of orders multiple times per day. Data reset every day, but if there are multiple entries/rows for one day in a document, the latest number is accurate.

In a document, there is information on the total number of orders for the last 7 days. With red squares, we marked all values inside a specific day and with orange arrow values which we want to use on a visualization (latest value for each day).

GOAL

As a result, we want to visualize the latest daily values on a Datablock with Bar chart visualization to see the total number of orders for the last 7 days.

It is also possible to use Cumulative option for Bar Chart in this case, to see growth through time on a daily basis. 

QUERY SETUP
  • Metric Name: Total Orders
  • Value: B column

    Select by clicking on a column letter where numbers of orders are entered. With selecting the whole column, new rows will be automatically synced with Databox.

  • Date: A column

    We have date information for every row in a document. The number of orders will be saved on dates provided in a document.

  • Dimension: Empty

    We don't have any additional information besides Number and Date to filter by.

Use case with dimensions could be if there will be another column with a webpage names. In this case we could create a Metric named Total orders by webpage
  • Number Format: Will be automatically selected if Google Sheets document is set correctly for selected range.
  • Data Type: Daily Values (processed data)

    Select Data Type by clicking on a Data Type drop-down.

Choose Daily values Data Type every time when you are creating a Metric from range where data reset daily. The latest value entered counts as the real and final value for each particular day.

3. Latest entered Total or Current value (using a script)

USE CASE

In this example, the current number of items on stock is added in a new row every time when it is updated. Each column represents a unique item (ID) and values are entered using a script which is automatically pulling current value from another tool.

With the red color we marked values which we want to visualize.

GOAL

As a result, we want to visualize the latest values entered for each of the items on a Number visualization.

QUERY SETUP

  • Metric Name: G87465, G88765, G98765 (Three Metrics)

    Note that you have to create three separated Metrics in this case, because only one column can be selected as value. It is easy to create a new Metric using Duplicate functionality.

  • Value: B, C and D column

    Select by clicking on a column letter where numbers of orders are entered. With selecting the whole column, new rows will be automatically synced with Databox.

  • Date: A column

    We have date information for every row in a document. The number of orders will be saved on dates provided in a document.

  • Dimension: Empty

    We don't have any additional information besides Number and Date to filter by.

Use case with dimensions could be if there will be another column with a webpage names. In this case we could create a Metric named Total orders by webpage
  • Number Format: Will be automatically selected if format in Google Sheets document is set correctly for selected range.
  • Data Type: Daily Values (processed data)

    Select Data Type by clicking on a Data Type drop-down.

Choose Total values Data Type every time when you are creating a metric from range or a cell where data doesn’t ever reset but represents the current status. The latest value pushed is the only one that matters (Metrics with this Data Type are usually visualized with an All time or Today date range).

Still need help? Contact Us Contact Us