Overview: Google Sheets Query Builder

In this article, we will create a Custom Metric using the Google Sheets Query Builder in order to highlight the features and functionality of the tool. For this example, we've already connected a Google Sheet to Databox (you can find sample spreadsheet here).

Learn how to connect Google Sheets to Databox.

We will create a new Custom Metric using Query Builder from the following Spreadsheet and use it on a Databoard. Our goal is to get how much Revenue came from each Referrer in Last Month.

IN THIS ARTICLE

Use-cases / Examples

1. Basic Options

A
  Custom Query Name: Create a name for your Custom Metric in this field. You will find this name/title as a metric option in the designer after saving the query.
We chose "Revenue by Referrer" for our query name. 
B
  Values (required) : Select a range with values from your connected Spreadsheet. Reference cells or ranges for Values by using ‘A1' notation (read more about 'A!' notation here) or by selecting it directly on the spreadsheet preview below.

You can reference values by:

  • Whole Row or Column: In case you will add a new column or row inside a selected range, also a new value will be pushed automatically into Databox.
  • Specific Cell or Range: Metrics inside selected range will be pushed. If a number inside the selected range is changed/updated, a new value will be pushed to Databox.
- Only numerical values are accepted. Using currencies or other units with your values is also supported. Google Sheets does a good job of automatically formatting the cells depending on the values, so you don’t need to worry about. If the values are rejected by Databox, please ensure 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.
C
  Timestamps / Dates To display your data the way you like, you will need to choose timestamp to specify on which dates you want to push data.
- Databox accepts any “Date” or “Date time” that is formatted as such inside Google Sheets. To ensure that all cells are formatted correctly, you can select those and go under “Format > Number > Date”. If you have hours added please select “Date time” format.

- A timezone can be applied and will be stored to Databox. In case your Date cells are formatted as Strings, we will assume your data is the US date format (Month/Day/Year). If no day is specified (only Month/Year), we will store that data on the first of each Month.

- Timestamp is a required field, except you've selected only one cell for a Value. In this case the Value will be stored with the current timestamp (now). By updating this cell in the Spreadsheet, the new Value will be stored to Databox. All other values stored in past stay preserved.
D
  Dimensions (optional) If you have additional information in your spreadsheet, for example: Country of purchase, Referer information, Browser, Product name, etc., you can reference cells or ranges for Dimensions by using ‘A1' notation or by selecting it directly on the spreadsheet preview below. Each metric can support up to one Dimension range. To use additional Dimensions, please add another custom metric.
We selected C column as a Dimensions since we want to filter data by Referrer.
E
  Read more about Format and Scale settings in Databox in this article.
F
  Click here to get more information about Data Types.

2. Spreadsheet Preview

In a spreadsheet preview you can easily select cells, rows or columns you want to use and check values directly from a spreadsheet without opening a Google document. Let's see what other options are available:

Maximize spreadsheet

Click on "Maximize spreadsheet" to hide some of the fields and Chart preview to maximize space for previewing your data.

Selecting Sheet from a connected Document:

At the bottom left side of the page below Spreadsheet Preview, you can find all your Spreadsheets (in our case first one is selected). Just click on tabs to switch between different Sheets.

3. Live Preview

In Live Preview, you can select between three visualization types for non-dimensional metrics (big number, line and bar chart). For metrics with dimension selected, you can decide between line and bar chart, table and pie.

Click on a Date Range below metric name in the Live preview to change "Date Range".

4. Save a Metric

When your query is ready, click "Save query." This creates a metric in Databox, with the metric name the same as the custom query name. In our case, this is "Total Revenue (Monthly)".

5. Use a Metric on a Databoard

The newly created metric can be added on a Databoard from a Databox Designer (Editor) or a Metrics Page:
  • Add a metric directly from our "Datablock Library" in the left-hand side panel under "Custom" tab (just drag and drop a metric)
  • Select one of our Visualization types (on the left-hand menu entitled “Visualization Types” and choose the visualization you want to represent your data) and select a metric from a "Datablock Properties" in the right side panel
  • On the "Metrics" page select your connected Google Sheets data-source (Filter by) and then click on a selected Metric from a list.

6. Use-Cases / Examples

In this section, we will show you how to set a Custom Metric for a few common use-cases.

1. How to Create a Metric without Dimensions

In this example, we want to create a metric named "Total Revenue" and use it on a Bar Chart where we wanted to see monthly values for every month. We also want for this metric to be updated when a new column for the next month is added.

Spreadsheet (you can also find it here)

How to create a metric using a Query Builder:

1
  Choose "MRR Overview" Sheet at the bottom of the screen.
2
  Click on a "Values field" and then select a whole 10th row in a spreadsheet preview part of the Query Builder.
3
  Select whole 1st row as a Timestamp.
4
  Change Data Type to "Unprocessed data (event value)", to see monthly data as bars and SUM of all months as a number above the chart. We've also changed Scale to "Thousand".
We won't select anything as a dimension since we only want to store values for specific dates.
5
  It is time to Save Query (Custom Query Name is filled automatically from a title in a spreadsheet, but you can change it)
Now you can use this metric on a Databoard!

2. How to Create one Value metric (one cell is updating)

We want to store data from the next spreadsheet. Let's assume that three employees are updating cells multiple times per day with a number of total calls for the current day. We want to build a separated custom metric to show total daily calls for each of the employees and show that on a number datablock (with more date ranges available). We will create a metric for "Mark" in this example.

Spreadsheet (you can also find it here)

How to create a metric using a Query Builder:

1
  Choose "Scheduled Calls" Sheet at the bottom of the screen.
2
  Click on a Values field and select B" field (a number of total Mark's calls for today). 
3
 We won't select Timestamp for this case since we do not have any date information in a spreadsheet (value will be stored on a current date). We also won't select a Dimension.
4
  Change Data Type to "Processed current data (current value)". Since values in a spreadsheet are updated every day and are showing daily total number we will also select "data resets daily" option. We can sum data to get a weekly or monthly total value on a Datablocks (Select "Aggregatable data").
5
  We choose  "Nr. of Calls - Mark" for a Custom Query Name (metric name)
6
  Save Query

Please contact us (click on "Contact Support" button or send us an email to [email protected]) if our help resources don’t answer your question, or if you are experiencing an undocumented problem.

Still need help? Contact Us Contact Us