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 Access the Google Sheets Query Builder
Navigate to Metrics > Query Builder to access the Query Builder for Google Sheets. Click the green + Create Custom Metric button and select your connected Google Sheet from the Data Source drop-down list.
Connecting Google Sheets and accessing Query Builder is available on the Professional and Performer plans. Request a free trial of Google Sheets by following
these steps.
How to Use the Google Sheets Query Builder
A
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). Select the cell(s) and navigate to
Format > Number > Number in your Google Sheet to verify that the entries are entered as Numbers and not Strings.
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.
B
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 enables Date Ranges to 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. Dates should be formatted as mm/dd/yyyy.
Learn more about which Google Sheets Date Formats are supported in Databox
here.
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.
C
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 one associated Dimension. To use additional Dimensions from your Google Sheet with the selected Metric Value, you 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 two separate Custom Metrics in Databox.
If no Dimension is selected, the Custom Metric will either store one aggregated Metric Value for each Date Range or the latest Metric Value. This behavior will depend on the selected Data Type.
D
Aggregation function: Selecting the appropriate Aggregation function ensures that values for your Custom Metrics will be aggregated correctly in Databox. The Aggregation function is closely connected with the selected Data Type. Learn more
here.
E
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.
Learn more about Data Types
here.
F
Advanced metric definition: Allows you to customize Custom Metric settings, such as Description,
Store
all values, even if Date/Time is an exact match to another entry, invert the metric value (e.g.,
Trending down is positive), set the
Default Number Format, adjust the
Default Comparison Function, and many more settings. Learn more about advanced settings for the Custom Metric
here.
G
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.
H
Custom Metric Name : Enter a name for your Custom Metric. This Custom Metric name will be available in the Designer after saving.
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 you would like to view in the Google Sheet Preview. To make the Metric Value selection as straightforward as possible, the selected Worksheet should host the data you are syncing with the Custom Metric.
How to Create Custom Google Sheets Metrics [Examples]
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
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.
2
Further, we'll select the
Date column from our Google Sheet, which is column
A
3
This Metric does not need a Dimension, so we will leave the
Dimension field blank
4
We want to get the sum of all values per selected Date Range. Therefore, we'll leave preselected
SUM as an
Aggregation function
5
We want all values synced with Databox, so we'll leave preselected
All
Values as
Data Type
6
There are identical Date stamps on this Google Sheet. Therefore, we'll navigate to the
Advanced metric definition > Data Aggregations tab and select
Store all values, even if Date/Time is an exact match to another entry 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. We are interested in analyzing the trend of this Metric, so we will select a
Line Chart
8
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.
9
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
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
2
Further, we'll select the Date column from our Google Sheet, which is column A
3
We want to view Impressions based on the Platform they were tracked in, so we will select column B, Platform, as the Dimension
4
We want to get the sum of all values per selected Date Range. Therefore, we'll leave preselected SUM as an Aggregation function
5
We want all values synced with Databox, so we'll leave preselected
All Values as
Data Type
6
There are identical date stamps on this Google Sheet. Therefore, we'll navigate to the
Advanced metric definition > Data Aggregations tab and select
Store all values, even if Date/Time is an exact match to another entry 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. We are interested in analyzing the distribution of Impressions across the Platforms, so we will select a Pie Chart
8
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.
9
Click Save to save the Custom Metric
How to Add a Custom Google Sheets Metric to a Databoard
How to Add a Custom Google Sheets Metric to a Databoard from the Metric Library
1
Open the selected Databoard in the Databox Designer, or create a new Databoard
2
Click on the Metric Library icon on the lefthand side of the Designer
3
Select the Google Sheet for which you want to view Datablocks from the Data Source drop-down list. The Custom Metrics that have been created for the Google Sheet will populate the Metric Library
4
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 Metric 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 Editor panel to populate the Datablock
Additional Information
- Each Google Sheets Custom Metric supports only one data Aggregation function. To view the same Google Sheets Custom Metric with different Aggregation functions selected (i.e., "SUM" on one vs. "AVG" on another), duplicate Custom Metrics and create the views you desire by following these steps.
