Overview: Metric Builder for Google Sheets

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

HOW TO

• Access Custom Metrics and Metric Builder for Google Sheets
• Switch between Manual Setup and the Wizard
• Use the Metric Builder for Google Sheets
• Create a Custom Google Sheets Metric [Example]
• Add a Custom Google Sheets Metric to a Databoard
• Additional Information

How to access Custom Metrics and Metric Builder for Google Sheets 

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

How to switch between Manual Setup and the Wizard

You can create Custom Metrics using the Metric Builder for Google Sheets via manual setup or a more advanced wizard. 

• Manual setup gives you the ability to select cells and create your Custom Metrics manually, as you do with other Metric Builders like the Metric Builder for Excel. 
• As long as your Sheet is formatted in an optimal way to sync with Databox, the wizard will also be available to provide more guidance and make it even easier to build Custom Metrics using data from your Google Sheets. The wizard option will only be available if your Sheet is formatted in Columns. This is the only format that the Wizard currently recognizes and supports.

You can switch between manual setup and the wizard by clicking on the Switch to Manual Setup or Switch to Wizard buttons in the top right of the Metric Builder. 

How to Use the Metric Builder for Google Sheets 

IN THIS SECTION

• How to use the Metric Builder for Google Sheets (Wizard)
• How to use the Metric Builder for Google Sheets (Manual Setup)

How to use the Metric Builder for Google Sheets (Wizard)

Verify your data:

The first page in the Google Sheet Wizard is intended to ensure we're recognizing the setup of your Google Sheet correctly by verifying the Date, Metric, and Dimension columns. Values should be numerical, while Dimensions are typically strings. Once complete, click Continue.

• A) Original document: The Original document link allows you to quickly access your Google Sheet to update data or make any edits you see fit.
• B) Refresh sheets data: Click Refresh sheets data if you want to see an up-to-date preview of your Google Sheets document. This is especially beneficial if you've made changes in your Sheet.
• C) Switch to manual setup: Click Switch to manual setup if you'd like to abandon the Wizard and would prefer to select cells and set up the Custom Metric from your Google Sheet manually.
• D) Column A: Column A typically represents Dates data. Verify that this column stores the dates that your data should be pushed to. Learn how to format Dates to sync data with Databox here.
• E) Column B: Column B typically represents a list of Dimensions, which are strings that help categorize your metric values. For example, a Dimension may be "Source" or "Sales Rep," which would allow you to split up your metric value by Source or Rep. Dimensions are optional. Verify that this column stores the Dimensions that correspond with your metrics.
• F) Column C: Column C typically represents Metric values, which are numerical values. Verify that this column stores the Metrics in your Sheet.
• G) Google Sheets Preview: The preview displays a live view of your Google Sheet. The first 500 rows of your Google Sheet will be visible in the preview, but data is synced from all rows in the Sheet.
• H) Google Sheet Worksheets: At the bottom of the Preview, you can see the Worksheets that make up your Google Sheet. You can navigate between Worksheets to ensure we're recognizing and syncing the right data with Databox.
• I) Continue: Click Continue once you've verified the setup of your Sheet to move to the next page in the Wizard.

Select a Metric Value:

Now that the Sheet is verified, you'll start building out the Custom Metric. To do this, you'll first select the Metric column that you'd like to use in the Custom Metric. This column should store numerical values.

• A) Switch to manual setup: Click Switch to manual setup if you'd like to abandon the Wizard and would prefer to select cells and set up the Custom Metric from your Google Sheet manually.
• B) Select sheet: Select the Worksheet we should be syncing data from.
• C) Select Metric: Select the Metric column you'd like to use in the Custom Metric.
• D) Go Back: if you need to go back to the previous step, click  Go back.
• E) Continue: Click Continue once you've verified the metric you'd like to include in your Custom Metric to move to the next page in the Wizard.

Add a Dimension:

Next, we have the option of selecting a Dimension for our Custom Metric. Dimensions allow you to re-categorize the returned Metric values based on a similar attribute. Examples of Dimensions are "Country," "Source" and "Campaign." Including a Dimension is optional when building Custom Metrics. If you don't select a Dimension, just 1 value is returned for the Custom Metric. If you do select a Dimension, the metric value is split up based on the Dimension categories.

• A) Switch to manual setup: Click  Switch to manual setup if you'd like to abandon the Wizard and would prefer to select cells and set up the Custom Metric from your Google Sheet manually.
• B) Select sheet: Select the Worksheet we should be syncing data from.
• C) Select Dimension: Select the Dimension column you'd like to use in the Custom Metric.
• D) None: if you don't want to include a Dimension in your Custom Metric, select  None.
• E) Go Back: if you need to go back to the previous step, select  Go back.
• F) Continue: Click  Continue once you've verified the metric you'd like to include in your Custom Metric to move to the next page in the Wizard. 

Select date: 

In order 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.

• A) Switch to manual setup: Click  Switch to manual setup if you'd like to abandon the Wizard and would prefer to select cells and set up the Custom Metric from your Google Sheet manually.
• B) Select sheet: Select the Worksheet we should be syncing data from.
• C) Select Date: Select the Date column you'd like to use in the Custom Metric.
• D) Use current time of storing data: Select this option if you want your data stored based on the time it's synced with Databox.
• E) Go Back: if you need to go back to the previous step, select  Go back.
• F) Continue: Click  Continue once you've verified the metric you'd like to include in your Custom Metric to move to the next page in the Wizard.

Finalize Custom Metric:

• A) Switch to manual setup: Click  Switch to manual setup if you'd like to abandon the Wizard and would prefer to select cells and set up the Custom Metric from your Google Sheet manually.
• B) 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.
• C) Name your Custom Metric: Enter a name for your Custom Metric. This Custom Metric name will be available in the Designer after saving.
• 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) Store all Values: Select this option if you'd like all data synced with Databox, even if the date/time of the entires are identical.
• G) Favourable Trend: There are some cases where an increase in the Metric value does not signify an improvement in performance and vice versa. In those cases, an increase in the Metric value should be displayed in red while a decrease in the Metric value should be displayed in green. Select the Favourable Trend based on what defines success for your Custom Metric. Learn more here. 
• H) Metric Format: Choose the Format for your Custom Metric. Learn more here.
• I) Advanced: In the Advanced window, you access additional settings to customize your Custom Metric. This includes adding a Description, changing the Data Aggregation, or updating Visual Preferences. Learn more about advanced settings for the Custom Metric here.
• J) Go Back: if you need to go back to the previous step, select  Go back.
• K) Continue: Click Continue once you've verified the metric to save the Custom Metric.

How to use the Metric Builder for Google Sheets (Manual Setup)

• 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 Metric 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 500 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]

When creating Custom Google Sheets Metrics you can use help of a wizard or you can choose to create Custom Metric manually, 

IN THIS SECTION

• How to create a Custom Google Sheets Metric with the Wizard (Example)
• How to create a Custom Google Sheets Metric manually (Example)

How to create a Custom Google Sheets Metric with the Wizard

IN THIS SECTION

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

Creating Custom Metric with the wizard is selected by default, as long as your Google Sheet is formatted in a way that Databox recognizes. You can click  Switch to manual setup button if you would prefer to set up your Custom Metric manually.

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 Wizard, we'll verify the Date, Metric (value), and Dimension (categories) columns from our Google Sheet. Values should be numerical, while Dimensions are typically strings. Once complete, we'll click Continue
2. Next, we'll start building the Custom Metric. First, we'll select the Value (numerical data) we want to include in our Custom Metric. In this case, we'll select  Impressions (Column C)  and click Continue
3. Since we don't want to include a Dimension (i.e., we just want 1 number returned to show total Impressions, we don't want it broken up by a category), we will select  None on the Segment by Dimension page, then will click Continue
4. We need to specify the dates that this data should be pushed to. In our Sheet, this is column A. We'll select this on the Select Date page then click Continue
5. Finally, we need to add some information on the new Custom Metric we're building, including a Custom Metric name and how data should be aggregated (grouped) when it's synced with Databox. We'll name this Custom Metric Impressions, and will keep  SUM of All Values selected as the  Aggregation function and Data type. This is because we want to add together all synced values for the metric
6. There are identical Date stamps on this Google Sheet, and we want them all included in the Custom Metric. Therefore, we'll leave the checkbox  Store all values, even if Date/Time is an exact match to another entry checked to ensure all of our data is synced correctly
7. Next, we'll select the visualization that we want to be associated with the pre-built Datablock for this Custom Metric in the Data Preview section. We are interested in analyzing the trend of this Metric, so we will select a Line Chart. When building Databoards, we will still be able to visualize this metric using different visualization types as we see fit
8. Click Continue 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 Wizard, we'll verify the  Date, Metric (value), and  Dimension (categories) columns from our Google Sheet. Values should be numerical, while Dimensions are typically strings. Once complete, we'll click  Continue
2. Next, we'll start building the Custom Metric. First, we'll select the Value (numerical data) we want to include in our Custom Metric. In this case, we'll select  Impressions (Column C) and click  Continue
3. We want to view Impressions split up based on the Platform they were tracked in, so we will select Platform (Column B) as the Dimension and click Continue
4. We need to specify the dates that this data should be pushed to. In our Sheet, this is column A. We'll select Date (Column A) on the  Select Date page then click  Continue
5. Finally, we need to add some information on the new Custom Metric we're building, including a Custom Metric name and how data should be aggregated (grouped) when it's synced with Databox. We'll name this Custom Metric  Impressions by Platform, and will keep  SUM of All Values selected as the  Aggregation function and  Data type. This is because we want to add together all synced values for the metric, based on the Dimension (Platform)
6. There are identical Date stamps on this Google Sheet, and we want them all included in the Custom Metric. Therefore, we'll leave the checkbox  Store all values, even if Date/Time is an exact match to another entry checked to ensure all of our data is synced correctly
7. After that, 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. When building Databoards, we will still be able to visualize this metric using different visualization types as we see fit
8. Click Continue to save the Custom Metric

How to create a Custom Google Sheets Metric manually

IN THIS SECTION

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

The  Switch to wizard button is greyed out if the data in your Google Sheets file is not structured in columns. This is a requirement in order to be able to use Metric Builder for Google Sheets wizard. Learn how to structure data in columns in your Google Sheets file here. 

If you don't want to restructure the data in your Google Sheets file, follow the steps outlined below to create your Custom Google Sheets Metrics via manual setup.

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.
   1. 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. 

How to Add a Custom Google Sheets Metric to a Databoard

IN THIS SECTION

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

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. 2 Click on the  Visualization Types icon on the lefthand side of the Designer
3. 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.