Learn how to create and publish your metrics
Property | Required | Value | Description |
---|---|---|---|
label | No | string | Custom label. This is what you’ll see instead of the metric name. |
type | Yes | metric type | Metrics must be one of the supported types. |
description | No | string | Description of the metric. A default description is created if this isn’t included |
sql | No | string | Custom SQL used to define the metric. |
hidden | No | boolean | If set to true, the metric is hidden. By default, this is set to false if you don’t include this property. |
format | No | string | This option will format the output value on the results table and CSV export. Supports spreadsheet-style formatting (e.g. #,##0.00). Use this website to help build your custom format. |
groups | No | string or string[] | If you set this property, the metric will be grouped in the sidebar with other metrics with the same group label. |
urls | No | Array of { url, label } | Adding urls to a metric allows your users to click metric values in the UI and take actions, like opening an external tool with a url, or open at a website. You can use liquid templates to customise the link based on the value of the dimension. |
show_underlying_values | No | Array of dimension names | You can limit which dimensions are shown for a field when a user clicks View underlying data. The list must only include dimension names from the base model or from any joined models. |
filters | No | Array of {filter field: value} | You can add filter logic to limit the values included in the metric calculation. You can add many filters. See which filter types are supported here. |
Type | Category | Description |
---|---|---|
percentile | Aggregate | Generates a percentile of values within a column |
median | Aggregate | Generates the 50th percentile of values within a column |
average | Aggregate | Generates an average (mean) of values within a column |
boolean | Non-aggregate | For fields that will show if something is true or false |
count | Aggregate | Counts the total number of values in the dimension |
count_distinct | Aggregate | Counts the total unique number of values in the dimension |
date | Non-aggregate | For adding calculations to metrics that return dates. |
max | Aggregate | Generates the maximum value within a numeric column |
min | Aggregate | Generates the minimum value within a numeric column |
number | Non-aggregate | For adding calculations to metrics that return numbers. |
string | Non-aggregate | For metrics that contain letters or special characters |
sum | Aggregate | Generates a sum of values within a column |
PERCENTILE_CONT
function.
The percentile
metric can be used on any numeric dimension or, for custom SQL, any valid SQL expression that gives a numeric table column.
For example, this creates a metric median_price
by taking the 50% percentile of the item_price
dimension:
PERCENTILE_CONT(0.5)
function.
The median
metric can be used on any numeric dimension or, for custom SQL, any valid SQL expression that gives a numeric table column.
For example, this creates a metric median_price
by taking the 50% percentile of the item_price
dimension:
AVG
function.
The average
metric can be used on any numeric dimension or, for custom SQL, any valid SQL expression that gives a numeric table column.
For example, this creates a metric avg_price
by taking the average of the item_price
dimension:
boolean
metric can be used on any valid SQL expression that gives you a TRUE
or FALSE
value. It can only be used on aggregations, which means either aggregate metrics or custom SQL that references other metrics. You cannot build a boolean
metric by referencing other unaggregated dimensions from your model.
boolean
metrics don’t do any aggregations; they just reference other aggregations.
For example, the avg_price
metric below is an average of all of the item_price
values in our product table. A second metric called is_avg_price_above_20
is a boolean
type metric. The is_avg_price_above_20
metric has a custom SQL expression that tells us whether the avg_price
value is greater than 20.
COUNT
function.
The count
metric can be used on any dimension or, for custom SQL, any valid SQL expression that gives a set of values.
For example, this creates a metric number_of_users
by counting the number of user_id
values in the table:
COUNT DISTINCT
function.
The count_distinct
metric can be used on any dimension or, for custom SQL, any valid SQL expression that gives a set of values.
For example, this creates a metric number_of_unique_users
by counting the number of unique user_id
values in the table:
date
metric can be used on any valid SQL expression that gives you a date value. It can only be used on aggregations, which means either aggregate metrics or custom SQL that references other metrics. You cannot build a date
metric by referencing other unaggregated dimensions from your model.
Creating a max or min date metric with type: date
If you want to create a metric of a maximum or minimum date, you can’t use type: max
or of type: min
metrics because these are only compatible with numeric type fields. Instead, you can calculate a maximum or minimum date by defining a metric of type: date
and using some custom sql, like this:
MAX
function.
The max
metric can be used on any numeric dimension or, for custom SQL, any valid SQL expression that gives a numeric value.
Because type: max
metrics only work with numerical fields, you can’t use them to find a maximum date. Instead, you can use the MAX()
function in the sql
parameter of a metric of type: date
to get a maximum date (you can see an example of this in the date section.
For example, this creates a metric max_delivery_cost
by looking at the delivery_cost
dimension and taking the largest value it finds:
MIN
function.
The min
metric can be used on any numeric dimension or, for custom SQL, any valid SQL expression that gives a numeric value.
Because type: min
metrics only work with numerical fields, you can’t use them to find a minimum date. Instead, you can use the MIN()
function in the sql
parameter of a metric of type: date
to get a minimum date (you can see an example of this in the date section.
For example, this creates a metric min_delivery_cost
by looking at the delivery_cost
dimension and taking the smallest value it finds:
number
metric doesn’t perform any aggregation but can be used to perform simple transformations on other metrics.
The number
metric can be used on any valid SQL expression that gives you a numeric or integer value. It can only be used on aggregations, which means either aggregate metrics or custom SQL that references other metrics. You cannot build a number
metric by referencing other unaggregated dimensions from your model.
For example, this creates a metric called total_gross_profit_margin_percentage
based on the total_sale_price
and total_gross_profit_margin
aggregate metrics:
SUM
function.
The sum
metric can be used on any numeric dimension or, for custom SQL, any valid SQL expression that gives a numeric table column.
For example, this creates a metric total_revenue
by adding up the values in the revenue
dimension:
string
metric can be used on any valid SQL expression that gives you a string value. It can only be used on aggregations, which means either aggregate metrics or custom SQL that references other metrics. You cannot build a string
metric by referencing other unaggregated dimensions from your model.
string
metrics are rarely used because most SQL aggregate functions don’t return strings. One common exception is MySQL’s GROUP_CONCAT
function.
For example, this creates a metric product_name_group
by combining the unique values of a dimension called product_name
:
format
parameter to have your metrics show in a particular format. Turntable supports spreadsheet-style format expressions for all metric types.
To help you build your format expression, we recommend using https://customformats.com/.
Description | Format Expression | Raw Value | Formatted Output |
---|---|---|---|
Adds “km” suffix to the value | #,##0.00" km" | 100000.00 | 100,000.00 km |
15000.25 | 15,000.25 km | ||
500 | 500.00 km | ||
Format date with 12-hour clock | m/d/yyyy h:mm AM/PM | 2023-09-05T15:45:00Z | 9/5/2023 3:45 PM |
2024-01-20T08:30:00Z | 1/20/2024 8:30 AM | ||
Display the full name of the day | dddd | 2023-09-05T15:45:00Z | Tuesday |
2024-01-20T08:30:00Z | Saturday | ||
Format positive, negative, and zero values | "⬆️ "0;"⬇️ "0;0 | -500 | ⬇️ 500 |
200 | ⬆️ 200 | ||
0 | 0 | ||
Text formatting | "Delivered in "@ | 2 weeks | Delivered in 2 weeks |
18 hours | Delivered in 18 hours | ||
Percentage formatting | #,##0.00% | 0.6758 | 67.58% |
0.1 | 10.00% | ||
0.002 | 0.20% | ||
No formatting | 0 | 12345232 | 12345232 |
56.7856 | 57 | ||
Currency formatting | [$]#,##0.00 | 15430.75436 | $15,430.75 |
15430.75436 | $15,430.75 | ||
Compact currency in thousands | [$]#,##0,"K" | 15430.75436 | $15K |
15430.75436 | $15.43K | ||
Compact currency in millions | [$]#,##0.00,,"M" | 13334567 | $13.33M |
120000000 | $120.00M |
(Legacy) format, compact, and round options
format
, compact
, and round
options and only apply the spreadsheet-style formatting expression.Option | Equivalent format expression | Description | Raw value | Displayed value |
---|---|---|---|---|
km | ’#,##0.00” km“‘ | Adds the suffix km to your value | 10 | 10 km |
mi | ’#,##0.00” mi“‘ | Adds the suffix mile to your value | 10 | 10 mi |
usd | ’[$]#,##0.00’ | Adds the $ symbol to your number value | 10 | $10.00 |
gbp | ’[$£]#,##0.00’ | Adds the £ symbol to your number value | 10 | £10.00 |
eur | ’[$€]#,##0.00’ | Adds the € symbol to your number value | 10 | €10.00 |
jpy | ’[$¥]#,##0.00’ | Adds the ¥ symbol to your number value | 10 | ¥10 |
percent | ’#,##0.00%‘ | Adds the % symbol and multiplies your value by 100 | 0.1 | %10 |
id | ’0’ | Removes commas and spaces from number or string types so that they appear like IDs. | 12389572 | 12389572 |
1,500
appears as 1.50K
), then I would write something like this in my .yml:Value | Alias | Equivalent format expression | Example output |
---|---|---|---|
thousands | ”K” and “thousand” | ’#,##0,” K”’ or ’#,##0.00,” K“‘ | 1K |
millions | ”M” and “million” | ’#,##0,,” M”’ or ’#,##0.00,,” M“‘ | 1M |
billions | ”B” and “billion” | ’#,##0,,,” B”’ or ’#,##0.00,,,” B“‘ | 1B |
trillions | ”T” and “trillion” | ’#,##0,,,,” T”’ or ’#,##0.00,,,,” T“‘ | 1T |
sql: "${dimension_in_this_model}"
Or from joined models like this: sql: "${other_model.dimension_in_other_model}"
sql: "${metric_in_this_model}"
Or from joined models like this: sql: "${other_model.metric_in_other_model}"
View underlying data
. If you have fields from a joined table included in your results table, then we’ll also show you all of the fields from the joined Table.
View underlying data
by adding the list of dimensions to your .yml
files:
my_joined_table_name.my_dimension
.
The order that the fields are listed in show_underlying_values
is the order that they’ll appear in on the view underlying data
table.
groups
parameter.
To do this, you need to set up group_details
in the model’s configuration. Then, you can use these groups to organize metrics and dimensions. You can create nested groups up to 2 levels.
Filters
tab in the Explore view, instead, they are applied automatically in the SQL query that fetches your results. That means filters added using the filter
parameter can’t be removed in the UI and won’t be visible to users unless they look at the SQL query.
Type | Example (in English) | Example (as code) |
---|---|---|
is | User name is equal to katie | user_name: “katie” |
is not | User name is not equal to katie | user_name: “!katie” |
contains | User name contains katie | user_name: “%katie%“ |
does not contain | User name does not contain katie | user_name: ”!%katie%“ |
starts with | User name starts with katie | user_name: “katie%“ |
ends with | User name ends with katie | user_name: “%katie” |
is greater than (number) | Number of orders is greater than 4 | num_orders: ”> 4” |
in the past (date) (interval) | Date is before x (days / months / years) | date: “inThePast 14 months” |
in the next (date) (interval) | Date is after x (days / months / years) | date: “inTheNext 14 days” |
is greater than or equal to | Number of orders is greater than or equal to 4 | num_orders: ”>= 4” |
is less than | Number of orders is less than 4 | num_orders: ”< 4” |
is less than or equal to | Number of orders is less than or equal to 4 | num_orders: ”<= 4” |
is null | Status is NULL | status: “null” |
is not null | Status is not NULL | status: “!null” |
is [boolean] | Is complete is true | is_complete: “true” |
is not [boolean] | Is complete is false or null | is_complete: “!true” |
%!_>
in your filter value you can either put the value in quotes, or escape special characters with a backslash \
. For example, if you wanted to filter for subscription status of is_subscribed
you can write the metric in one of these ways:
completed
or shipped
you should write the metric like:
AND
is_closed_account = TRUE AND is_7d_active = FALSE
.
model_name.field
, like this:
shipped
. You would need to do something like this in your .yml: