HOW TO
- Access Custom Metrics and Metric Builder for BigQuery
- Use the Metric Builder for BigQuery
- Create a Custom BigQuery Metric [Example]
- Add a Custom BigQuery Metric to a Databoard
- Additional Information
How to access Custom Metrics and Metric Builder for BigQuery
Navigate to Metrics > Custom Metrics to access the Metric Builder for BigQuery. Click the green + Create Custom Metric button and select your connected BigQuery Data Source from the Data Source drop-down list.
Metric Builder is available on the Professional and higher plans. Request a trial of Metric Builder by following these steps.
How to use the Metric Builder for BigQuery
A) Custom Metric Name: Enter a name for your Custom Metric. This Custom Metric name will be available in the Designer after saving.
B) SQL: Construct query strings to access data from your Database using Structured Query Language (SQL). Learn more here.
C) Data Type: Selecting the appropriate Data Type ensures that your Custom Metrics will be synced correctly in Databox.
- 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.
- 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 be not 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." - 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, individual Metric Values will be aggregated for the entire Date Range and the aggregated Value will be displayed for the Custom Metric.
How to create a Custom BigQuery Metric [Example]
The Metric Builder for BigQuery is designed so you can directly enter SQL queries. For our example, we will use a BigQuery Data Source and create a Custom Metric that returns a list of Authors and their associated Review Scores.
- Navigate to Metrics > Custom Metrics
- Click the green + New Custom Metric button
- Select the appropriate BigQuery Data Source
- In order to accurately explain what this Custom Metric is reporting on, we'll name the Custom Metric "Authors by Score"
- In the SQL field, we will enter the query below. A semicolon at the end of the query is not necessary
SELECT date(time_ts) AS date, score, author FROM [bigquery-public-data:hacker_news.stories] ORDER BY score DESC LIMIT 10;
6. We will select Total Values as the Data Type. This selection is based on the way data is stored and updated in our Database
7. Click Preview data to generate a Data Preview of the Custom Metric
If you don’t see any data, double-check your SQL query and try it directly in your Database. If no results are displayed there, an error exists in your query.
8. Once we confirm that these are the results we're looking for, we'll click Save to save the Custom Metric
How to add a Custom BigQuery Metric to a Databoard
Learn how to add a Custom Metric to a Databoard here.
Additional Information
- Standard SQL and partitioned tables are supported. To use Standard SQL, you need to unselect the "Use Legacy SQL" checkbox when connecting the database.
- The query must contain a column named "Date" and it needs to be casted. Example: CAST(date_column_name AS STRING) as date.
- One column should always contain the Metric Value and this should be within an Aggregation Function. The Data Aggregation Settings only shows how the data should be aggregated for longer periods, so you should still aggregations like SUM() or COUNT().
- Example: If you want the count of rows as value column, and have those counts summed for longer periods of time, in this case, you should use COUNT(column_name) in the query, and select "SUM - All Values" as Data Aggregation function.
- When working with dimensions, use aggregation functions alongside GROUP BY in the query directly to aggregate the data properly. Example: GROUP BY date, Column_name.
- Query results are limited to 10 MB. If more than 10 MB of data is returned in a response to a query, an error message will be displayed in Databox. Please edit your query to successfully sync the data.
- ORDER BY date DESC to get the freshest data first.
- "Null" values will not be recognized. Use a COALESCE function when needed. Learn more here.