How to report on Period and All Values using the Metric Builder for Google Sheets

This article will outline how to create Custom Metrics using the All Value and Period Value Data Types through multiple examples. Most of the common use-cases per these Data Types will be covered in this document. 

For each example, we'll walk through how to prepare the Google Sheet, create the Custom Metric, and 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]

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

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

1. Example #3: Total Orders
   Data Type: Period 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

1. Example #4: # of Items in Stock
   Data Type: All 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. 

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. 

PREPARING THE GOOGLE SHEET

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

1. 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 select the appropriate formatting option for your data (Currency, Percentage, etc.).  
2. How are the individual Date cells formatted? 
   
   All cells containing date and/or time information must be formatted as Dates in the Google Sheet. To update this setting in the Google Sheet, select the cells and choose the menu item Format > Number > Date. Learn more about which Google Sheets Date Formats are supported in Databox here.
   
   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. 
   
   
3. Are there any additional entries on my Google Sheet, such as Totals or Averages? 

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 Metric Builder. 

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 + New Databoard button from the main Databoard page and select the option Start Blank.
2. Next, open the Metric Library on the left-hand side of the Designer. Select the appropriate Google Sheet as the Data Source and click  Create new Custom metric... . This will open the Google Sheet Metric Builder popup window. 
   • 
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. 
   
   
   • 
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.
   1. 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.
   1. After selecting the Dimension field, a Data Preview will be displayed on the righthand side of the Metric 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 Metric 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 ''Aggregation function'' is an important step when creating a Google Sheets Custom Metric. With the Aggregation function, you define how you want to aggregate data in Databox. For Use Case #1, we have to select SUM as the Aggregation function.
8. Selecting the correct "Date Type" is a very important step when creating a Custom Metric using the Google Sheets Metric Builder. For Use Case #1, we must change the Data Type to Period Values.     
   1. Pro Tip: The "Period 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.
9. Next, you need to select the ''Period'' to define how you'd like to aggregate data. For Use Case #1, we want to take the latest value for each day and sum them for longer periods, so we select Day as Period.
10. 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 Metric Library. This will not be the only option for visualizing the Custom Metric. This will be the pre-built visualization option. We are interested in analyzing the distribution of Calls across the Agents, so we will select a  Pie Chart.  
11. Click the green Save button to save the Custom Metric.

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 Metric Library onto the Databoard.

To 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 Editor, 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.  

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. 

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

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. 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
4. We don't want to split this Metric Value up based on any Dimension, so we'll leave the  Dimension field blank
5. Next, we will select Latest as the  Aggregation function, because we aim to see the latest entry displayed as the Custom Metric value
6. We'll leave preselected  All  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 one cell in the Google Sheet is selected
   1. Pro Tip: The "All 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 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, and this will be the pre-built visualization option. 
    
8. Click the green  Save 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 number of orders for that day and is the value we want to report on in Databox.

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

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. 

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

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. In the Google Sheet, the Date information is entered in column A. Therefore, we'll select column A as the Date
4. We don't want to split this Metric Value up based on any Dimension, so we'll leave the  Dimension field blank
5. Next, we will select SUM as the  Aggregation function because we aim to see a sum of values for the Custom Metric
6. We will select Period 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
   1. Pro Tip: The "Period 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. We also need to select the ''Period'' to define how you'd like to aggregate data. For Use Case #3, we want to take the latest value for each day and sum them for longer periods (e.g., 7 days), so we select  Day as  Period. 
    
8. 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 Metric Library. This will not be the only option for visualizing the Custom Metric, and this will be the pre-built visualization option. We are interested in monitoring Orders data by days, so we will select a Bar Chart.
    
9. Click the green Save 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 automatically pulls 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. 

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.

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

1. 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. In the Google Sheet, the corresponding Dates are entered in column A. Therefore, we will select column A as the Date
4. We don't want to split this Metric Value up based on any Dimension, so we'll leave the  Dimension field blank
5. Next, we will select  Latest as the Aggregation function because we aim to see the latest entry displayed as the Custom Metric value
6. We will select All Values as the Data Type since the cells Values selected represent the total current number of Items at a specified time. 

   1. Pro Tip: The "All Values" Data Type should be used when you are creating a Custom Metric from a range of cells where data 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 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, and this will be the pre-built visualization option. 
    
8. Click the green  Save button to save the Custom Metric