Overview: Query Builder for Google Sheets

With the Query Builder for Google Sheets, you can create Custom Metrics to sync your Google Sheets data with Databox. 

HOW TO

How to access the Google Sheets Query Builder

Navigate to  Data Manager > Query Builder to access the Query Builder for Google Sheets. Click the green + Create Custom Metric button and select your connected Google Sheets Account from the Data Source drop-down list.

    The ability to connect Google Sheets and access Query Builder is available in Plus and Business Accounts. Agency Basic Accounts have the ability to connect Google Sheets and access Query Builder in the Agency Account only. 

How to use the Google Sheets Query Builder

A
  Custom Metric Name : Enter a name for your Custom Metric. This Custom Metric name will be available in the Designer after saving. 
B
  Value:  Select a cell or a range of cells from your Google Sheet that stores the Metric Value(s). These cells must contain numerical values (Currency and other Unit formats are supported). To verify that the entries are entered as Numbers and not Strings, select the cell(s) and navigate to Format > Number > Number in your Google Sheet. 

A cell or a range of cells can be entered using A1 notation or by highlighting the selection directly in the Spreadsheet Preview section at the bottom of the Query Builder. Learn more about A1 notation here.

If you select an entire row or column as the Value selection, any new entries in the selected row or column will automatically sync the new data to the Custom Metric in Databox. 

C
  Date:  To display your data the way you like, you will need to include entries in your Google Sheet that specify which date(s) you want to push your data to. This will make it so Date Ranges display the appropriate data in Databox. 
To verify that your entries are entered correctly as Dates, select the cell(s) and navigate to  Format > Number > Date in your Google Sheet. Any "Date" or "Date time" Format is accepted in Databox.
In most cases, a Date selection is required to create a Custom Google Sheets Metric. Learn more about when a Date selection is not required here.
D
  Dimension (Optional): The Dimension parameter re-categorizes the Metric value based on common criteria. Examples of Dimensions are Referrer, Browser, Country, and Product Name. 
Each Metric Value can support up to 1 associated Dimension. To use additional Dimensions from your Google Sheet with the selected Metric Value, you will need to build another Custom Metric. This means that if you want to report on Sessions by Browser and Sessions by Country, you would need to create 2 separate Custom Metrics in Databox. 
If no Dimension is selected, the Custom Metric will either store 1 aggregated Metric Value for each Date Range, or the latest Metric Value. This behavior will depend on the selected Data Type. 
E
  Format: Set the Format for the Custom Metric. If your Metric Values are formatted correctly in your Google Sheet, this should automatically update with the appropriate Format. Examples of Formats are Currency, Percentage and Duration. 
Learn more about Format settings in Databox here.
F
  Scale: Set the Scale for the Custom Metric. The Scale will determine the rounding rules put in place for the Custom Metric. 
Learn more about Scale settings in Databox here.
G
  Data Type: Selecting the appropriate Data Type ensures that your Custom Metrics will be synced correctly in Databox. The Data Type is largely dependent on the way you plan to update your Google Sheet with new data. 
  • Total Values: This Data Type should be selected when your Metric reflects the current total Value for the Metric. In Databox, the latest entry will be displayed for the Custom Metric.

    For longer Date Ranges, data will not be aggregated and the most recent Value will be displayed for the selected Date Range. This is the main difference between "Total Values" and "Daily Values."

    When building Custom Metrics with  Total Values selected as the Data Type, a Date selection is not always required. 

    Examples of Custom Metrics that should have Total Values selected are Total Followers and Current Account Balance.  

  • Daily Values: This Data Type should be selected when your Metric reflects the most up-to-date daily Value for the Metric. In Databox, the latest daily entry will be displayed for the Custom Metric.

    For longer Date Ranges, individual Metric Values will be aggregated for the entire Date Range and the aggregated Value will be displayed for the Custom Metric. This is the main difference between "Daily Values" and "Total Values."

    Examples of Custom Metrics that should have  Daily Values selected are Daily New Followers, Daily Calls by Sales Rep, and New Purchases. 

  • Event Values: This Data Type should be selected when your Metric Value selection reflects individual Value(s) for the Metric at the specified Date/ Timestamp. This Data Type is the default selection because it is appropriate for the majority of use cases. 

    For longer Date Ranges with multiple entries, individual Metric Values will be aggregated for the entire Date Range and the aggregated Value will be displayed for the Custom Metric. When building Custom Metrics with Event Values selected as the Data Type, a Date selection is always required. 

    While updating the Google Sheet, new rows should not be added to the top of the document. This would result in duplicate data syncing with Databox, and potential inconsistencies. 

    If you update a single cell's Value in your Google Sheet, the old entry and the updated entry will both be saved in Databox. To prevent an inconsistency, you should go into Databox and resave the Custom Metric, being sure to select the Purge Data option.

    If there are entries with identical Date/ Timestamps on your Google Sheet and the Event Values checkbox is not selected, the latest Metric Value entered on the Sheet will be stored in Databox.  If there are entries with identical Date/ Timestamps on your Google Sheet and the Event Values checkbox is selected, individual Metric Value will be aggregated and the aggregated Value will be stored in Databox. 

    Examples of Custom Metric that should have Event Values selected as the Data Type are Closed Won Amount, Impressions, and Sessions. 
H
  Data Preview: As you build your Custom Metric, a Data Preview will update to reflect your selection. You can view the Data Preview for different visualizations by clicking on the icons at the top of the Data Preview. 
When you save the Custom Metric, the selected visualization will be stored and a pre-built Datablock will be generated. This Datablock will be available in the Datablock Library when the appropriate Google Sheet Data Source is selected. 
I
  Google Sheet Preview: The Google Sheet Preview displays a live view of your Google Sheet. You can use this Google Sheet Preview to simplify the selection process for the Value, Date, and Dimension fields. The first 1000 rows of your Google Sheet will be visible in the Google Sheet Preview, but data is synced from all rows in the Sheet. 
J
  Google Sheet Worksheets: Select the Worksheet that you would like to view in the Google Sheet Preview. In order to make the Metric Value selection as straightforward as possible, the selected Worksheet should host the data that you are syncing with the Custom Metric. 

How to create Custom Google Sheets Metrics [Examples]

IN THIS SECTION

How to create a Custom Google Sheets Metric without Dimensions [Example]

Let's say we want to create a Custom Metric to track Impressions from the Google Sheet below.

1
 We'll enter Impressions as the Custom Metric Name
Pro Tip: Typically, the Custom Metric Name is the same as the column or row header from the Google Sheet. 
2
 In the Google Sheet Preview, we'll select column C as the Value. Since we are selecting the full column, any additional Values added to the Worksheet will be automatically synced with Databox
3
 Next, we'll select the Date column from our Google Sheet, which is column A
4
 This Metric does not need a Dimension, so we will leave the Dimension field blank
5
 The Scale and Format fields are set appropriately so we will not update these selections
6
 There are identical Date stamps on this Google Sheet, and we want all Values synced with Databox. Therefore, we will select Event Values and the corresponding checkbox to ensure all of our data is synced correctly
7
 Next, we'll select the visualization that we would like associated with the pre-built Datablock for this Custom Metric from the Data Preview section. I'm interested in analyzing the trend of this Metric, so I will select a Line Chart
8
 Click Save to save the Custom Metric

How to create a Custom Google Sheets Metric with Dimensions [Example]

Let's say we want to create a Custom Metric to track Impressions by Platform from the Google Sheet below. 

1

 We'll enter  Impressions by Campaign as the Custom Metric Name

Pro Tip: Typically, the Custom Metric Name is the same as the column or row header from the Google Sheet. 
2

 In the Google Sheet Preview, we'll select column C as the Value. Since we are selecting the full column, any additional Values added to the Worksheet will be automatically synced with Databox

3

 Next, we'll select the  Date column from our Google Sheet, which is column A

4

 We want to view Impressions based on the Platform they were tracked in, so we will select column B, Platform, as the Dimension

5

 The  Scale and Format fields are set appropriately so we will not update these selections

6

 There are identical Date stamps on this Google Sheet, and we want all Values synced with Databox. Therefore, we will select  Event Values with the corresponding checkbox to ensure all of our data is synced correctly

7

 Next, we'll select the visualization that we would like associated with the pre-built Datablock for this Custom Metric from the  Data Preview section. I'm interested in analyzing the distribution of Impressions across the Platforms, so I will select a Pie Chart

8

 Click  Save to save the Custom Metric

How to add a Custom Google Sheets Metric to a Databoard

IN THIS SECTION

How to add a Custom Google Sheets Metric to a Databoard from the Datablock Library

1
 Open the selected Databoard in the Databox Designer, or create a new Databoard
2

 Click on the  Datablock Library icon on the lefthand side of the Designer

3

 Select the Google Sheet you want to view Datablocks for from the  Data Source drop-down list. The Custom Metrics that have been created for the Google Sheet will populate the Data Library

4
 The Custom Metrics that have been created for the Google Sheet will populate the Data Library. Find the Datablock you want to add to your Databoard, using the Search bar if necessary
5
 Drag and drop the selected Datablock onto your Databoard. The Datablock will automatically populate with data from the Google Sheet that was selected in the Datablock Library

How to add a Custom Google Sheets Metric to a Databoard from the Visualization Library

1
 Open the selected Databoard in the Databox Designer, or create a new Databoard
2
 Click on the  Visualization Types icon on the lefthand side of the Designer
3
 Find the Visualization you want to add to your Databoard
4
 Drag and drop the selected Visualization onto your Databoard. Use the righthand Datablock Settings panel to populate the Datablock
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

Related Articles