How to report on Daily and Total Values using the Query Builder for Google Sheets

In this article, we'll outline how to create Custom Metrics using the Total Value and Daily Value Data Types through multiple examples. Most of the common use-cases for using these Data Types will be covered in this document. 

For each example, we'll walk through how to prepare the Google Sheet, how to create the Custom Metric, and how to visualize the Custom Metric in Databox. Use Case #1 goes into the most detail, so it's recommended that you start with this before moving on to any other examples.

IN THIS ARTICLE [Examples]

  • Example #1: Calls by Agent 
    Data Type: Daily Values
    Update Method: Replacing cell Value with up-to-date Daily Value 
    Date/Time: No date/time information is included on the Google Sheet

  • Example #2:Account Balance
    Data Type: Total Values
    Update Method: Replacing cell Value with up-to-date Total Value
    Date/Time: No date/time information is included on the Google Sheet

  • Example #3:Total Orders
    Data Type: Daily Values
    Update Method: Add new row/column to reflect up-to-date Total Value
    Date/Time: Date/time information is included on the Google Sheet

  • Example #4: # of Items in Stock
    Data Type: Total Values
    Update Method: Add new row/column to reflect up-to-date Daily Value
    Date/Time: Date/time information is included on the Google Sheet

Example #1: Calls by Agent

For our example, let's say three agents (Mark, Anna, and John) are updating cells in a Google Sheet throughout the day to track their calls for the current day. 

add_user_popup@2x

OBJECTIVE

We want to create a report that shows the number of calls for each individual Agent, and also shows the distribution of calls amongst each Agent in a Pie Chart. 

add_user_popup@2x

PREPARING THE GOOGLE SHEET

In order to ensure your Google Sheet is formatted correctly, ask yourself the following questions. 

  • How are the individual Value cells formatted?

    All cells containing numeric values that you want to push into Databox must be formatted as Numbers rather than Strings in the Google Sheet. To update this setting in the Google Sheet for basic values, select the cells and choose the menu item Format > Number > Number.

    If you have currencies, percentages, etc. entered in your Google Sheet, select the cells and choose the menu item Format > Number > More Formats. From here, you can choose the appropriate formatting option for your data (Currency, Percentage, etc.).  
  • How are the individual Date cells formatted? 

    All cells containing date and/or time information must be formatted as Dates in the Gooogle Sheet. To update this setting in the Google Sheet, select the cells and choose the menu item Format > Number > Date. Any Date Format will work here, so feel free to further customize the date/time format as you see fit. 

    Google Sheets data is synced hourly. If there is no date/ time information included on your Google Sheet (like in Use Case #1), the date/time that the data is saved to will be based on the Databox sync time. 

  • Are there any additional entries on my Google Sheet, such as Totals or Averages? 
Pro Tip: In order to create a scalable process for pushing this data into Databox, we typcially select entire row or column when creating Custom Metric. Therefore, it is important that no additional values are entered in your "active" rows/ columns (for Use Case #1, the "active" columns are columns A and B). 

add_user_popup@2x

CUSTOM METRIC SETUP

Once we've ensured our Google Sheet is formatted correctly, we're ready to connect it to Databox and create a Custom Metric using the Google Sheets Query Builder. 

add_user_popup@2x

Based on the Google Sheet from Use Case #1 (above), we want to build a Custom Metric for "Calls by Agent." This one Custom Metric will allow us to create the entire report that we need. 

1
 First, we will create a new Databoard and open it in the Designer. To do this, click the green + Create New button from the main Databoard page and select the option Start Blank. 

add_user_popup@2x

2
 Next, open the Datablock Library on the lefthand side of the Designer. Select the appropriate  Google Sheet as the Data Source and click + New Custom Metric. This will open the Google Sheet Query Builder popup window. 
add_user_popup@2x
3
  Enter the new Custom Metric name in the Custom Metric Name text field. For Use Case #1, this will be Calls by Agent. If you do not enter a Custom Metric Name, this field will be automatically populated with the header from the Metric Value column/ row.
4
 The "Value" field refers to the numerical values that will be pushed into Databox. For Use Case #1, we will select the Calls column (column B). We will select all of Column B for the Value field, so if any new Agents start tracking their data on this Google Sheet it is automatically recognized and synced with Databox. 

add_user_popup@2x

5
 The "Date" field refers to the date/ time the data will be saved to in Databox. For Use Case #1, we do not have a Date column or row. Therefore, we will leave the Date Field empty.

Google Sheets data is synced hourly. If there is no date/ time information included on the Google Sheet, the date/time that the data is saved to will be based on the Databox sync time. 
6
  "Dimensions" allow us to recategorize the Values based on common property. For Use Case #1, we want to split up all of the Call values based on the Agent who logged the Call. Therefore, we will select column A (Agents) as the Dimension.
Pro Tip: After selecting the Dimension field, a Data Preview will be displayed on the righthand side of the Query Builder window. The visualization type options will be based on the type of Custom Metric you are creating. The selected visualization will be saved with the Custom Metric and available in the Datablock Library. This will not be the only option for visualizing the Custom Metric, this will just be the pre-built visualization.
7
 Selecting the correct "Date Type" is a very important step when creating a Custom Metric using the Google Sheets Query Builder. For Use Case #1, we must change the Data Type to Daily Values (processed data).   
Pro Tip: The "Daily Values" Data Type should be used when the same cell in the Google Sheet is updated multiple times a day to reflect the new total Daily Value. Each day, the Value resets. For longer Date Ranges, data will be aggregated.

This means if we were viewing the data on a Number Block for a longer Date Range (like This Week), the final cell entry from each day would be added together to produce the total Value for the week. For Use Case #1, this means the final number of Calls logged each day would be added together to display the total number of Calls for the week.
add_user_popup@2x
8
 Click the green Save Query button to save the Custom Metric
add_user_popup@2x

VISUALIZING THE CUSTOM METRIC

After the Custom Metric is created, you can add it to your report using a variety of visualization types. 

To add the Custom Metric to the Databoard with the pre-defined visualization, just drag and drop the Datablock from the Datablock Library onto the Databoard.

add_user_popup@2xTo use this Metric with another visualization, click on the Visualization Type icon on the lefthand side of the Designer. Drag and drop your selected visualization onto the Databoard. Using the righthand Datablock Settings, you can then map the Datablock to the correct Data Source (Google Sheet) and Custom Metric. 

Example #2: Account Balance

The next Google Sheet we want to sync data from is simply tracking our current Account Balance.  

add_user_popup@2x

OBJECTIVE

In Databox, we want to view the current Account Balance on a Gauge so we can visually track our progress towards reaching our Goal. 

add_user_popup@2x

PREPARING THE GOOGLE SHEET

1.  How are the individual Value cells formatted? 
Currency Format.

2. How are the individual Date cells formatted? 
There are no Date cells are included on our Google Sheet. Therefore, the date/time that the data is saved to will be based on the Databox sync time. 

3. Are there any additional entries on my Google Sheet, such as Totals or Averages? 
No.

CUSTOM METRIC SETUP

add_user_popup@2x

1
  In order to accurately explain what this Custom Metric is reporting on, we'll name the Custom Metric "Account Balance" 
2
  The Value we want to report on is stored in cell B1. Therefore, we will select B1 as the Value
3
  We don't want to split this Metric Value up based on any Dimension, so we'll leave the Dimension field blank
4
  In the Google Sheet, there isn't a Date/ Time entered, Instead, we want the data saved based on the Databox sync time. So, we will leave the Date field blank
5
  Since our Value cell is correctly formatted in the Google Sheet, the Number Format field automatically populates with Prefix Currency in Databox
6
 We will select Total Values as the Data Type since the cell Value selected represents the total Account Balance at any given time. This field will automatically populate in Databox because only 1 cell in the Google Sheet is selected
Pro Tip: The "Total Values" Data Type should be used when you're creating a Custom Metric based off of a cell or row of cells where the current Total Metric Value is always stored. The Value never resets, it is just continuously updated with the new Metric Value.  

For longer Date Ranges, data will not be aggregated. This means if we were viewing the data on a Number Block for a longer Date Range (like This Year), the latest cell entry for the year would be displayed on the Datablock. This Value is not added together with any previous entries in the cell. 
7
 In the Data Preview, select the visualization that you want to set for this pre-built Datablock. The selected visualization will be saved with the Custom Metric and available in the Datablock Library. This will not be the only option for visualizing the Custom Metric, this will just be the pre-built visualization option. 
8
 Click the green  Save Query button to save the Custom Metric

Example #3: Total Orders

In this example, a company is exporting the total number of orders processed throughout the day from another tool into a Google Sheet. Depending on the number of orders, there may be multiple row entries in a day. However, the latest row entry for each day is the Value that represents the total numbers of orders for that day and is the value we want to report on in Databox.

In the screenshot below, each day is blocked off with a red rectangle. The orange arrow is pointing to the latest row entry for each day. 

add_user_popup@2x

OBJECTIVE

From this Google Sheet, we want to sync with Databox the total number of orders processed each day and visualize that data for the last 7 days. 

add_user_popup@2x

PREPARING THE GOOGLE SHEET

1.  How are the individual Value cells formatted? 
Number Format.

2.  How are the individual Date cells formatted? 
Date Format. 

3.  Are there any additional entries on my Google Sheet, such as Totals or Averages? 
No.

CUSTOM METRIC SETUP

add_user_popup@2x

1
 In order to accurately explain what this Custom Metric is reporting on, we'll name the Custom Metric "Total Orders"
2
 The Value we want to report on is stored in column B. Therefore, we will select column B as the Value
3
 We don't want to split this Metric Value up based on any Dimension, so we'll leave the Dimension field blank
4
 In the Google Sheet, the Date information is entered in column A. Therefore, we'll select column A as the Date
5
 Since our Value cell is correctly formatted in the Google Sheet, the Number Format field automatically selects Auto in Databox
6
 We will select Daily Values as the Data Type since the cell Values we want to report on are the most recent entries for each day in the Google Sheet
Pro Tip: The "Daily Values" Data Type should be used when you are creating a Custom Metric from a range of cells that resets daily. The latest value entered represents the real and final Value for the day.  For longer Date Ranges, data will be aggregated. This means if we were viewing the data on a Number Block for a longer Date Range (like Last 7 Days), the final cell entry from each day would be added together to produce the total Value for the week. For Use Case #3, this means the final row displaying the number of Orders for the day would be added together to display the total number of Orders for the past 7 days. 
7
 In the Data Preview, select the visualization that you want to set for this pre-built Datablock. The selected visualization will be saved with the Custom Metric and available in the Datablock Library. This will not be the only option for visualizing the Custom Metric, this will just be the pre-built visualization option.
8
 Click the green Save Query button to save the Custom Metric

Example #4: # of Items in Stock

In this example, the current number of items in stock for each product is being tracked in a Google Sheet. Each column represents a unique product ID and the Values are entered using a script that is automatically pulling the product information into the Sheet from another tool.

The red square highlights the latest entry in the screenshot below. This represents the current number of items in stock for each product. 

add_user_popup@2x

OBJECTIVE

In Databox, we want to pull in the latest entry from the Google Sheet in order to monitor the current number of items in stock for each product.

add_user_popup@2x

PREPARING THE GOOGLE SHEET

1.  How are the individual Value cells formatted? 
Number Format.

2. How are the individual Date cells formatted?
Date Format. 

3.  Are there any additional entries on my Google Sheet, such as Totals or Averages? 
No.

CUSTOM METRIC SETUP

add_user_popup@2x

1
  In order to accurately explain what this Custom Metric is reporting on, we'll name the Custom Metric "G87465," which is one of our Product Names  

In order to report on all of the data from this Google Sheet, we would need to create a separate Custom Metric for each product. Therefore, Custom Metric #1 would be "G87465" (Product 1), Custom Metric #2 would be "G88765" (Product 2), and Custom Metric #3 would be "G98765" (Product 3). 

2
  The Value we want to report on is stored in column B. Therefore, we will select column B as the Value
3
 We don't want to split this Metric Value up based on any Dimension, so we'll leave the Dimension field blank
4
 In the Google Sheet, the corresponding Dates are entered in column A. Therefore, we will select column A as the Date
5
  Since our Value cell is correctly formatted in the Google Sheet, the Number Format field automatically selects Auto in Databox
6
 We will select Total Values as the Data Type since the cell Value selected represents the total Account Balance at any given time. This field will automatically populate in Databox because only 1 cell in the Google Sheet is selected

Pro Tip: The "Total Values" Data Type should be used when you are creating a Custom Metric from a range of cells where daata doesn't ever resent, because it represents the current status of the data. The latest value entered represents the real and final Value for the day. 

For longer Date Ranges, data will not be aggregated. This means if we were viewing the data on a Number Block for a longer Date Range (like This Year), the latest row entry for the year would be displayed on the Datablock. This Value is not added together with any previous entries in the cell. 

7
 In the Data Preview, select the visualization that you want to set for this pre-built Datablock. The selected visualization will be saved with the Custom Metric and available in the Datablock Library. This will not be the only option for visualizing the Custom Metric, this will just be the pre-built visualization option. 
8
 Click the green  Save Query button to save the Custom Metric
Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.

Still need help? Contact Us Contact Us