Create Metrics in Datasets
Common Metrics use cases
The next step after defining a Dataset is to add Metrics.
We will use the familiar ecommerce
dataset to introduce you some of the most common metric use cases:
- Simple aggregation: Number of orders (all time)
- Cross-model aggregation: Total order value (all time)
- Multiple aggregations in one metric: Average order value (Total order value / orders count) of customers (all time)
- Metric with filtering condition: Average order value in the last 3 months
The final setup of a dataset with metrics will look something like this:
Final setup
Dataset Definition
Dataset ecommerce {
label: 'Ecommerce'
owner: "[email protected]"
data_source_name: 'demodb'
models: [
orders,
order_items,
products,
users,
]
relationships: [
relationship(orders.user_id > users.id, true),
relationship(order_items.order_id > orders.id, true),
relationship(order_items.product_id > products.id, true)
]
// 1. Simple aggregation
metric count_orders {
label: 'Count Orders'
type: 'number'
definition: @aql count(orders.id) ;;
}
// 2. Cross-model aggregation:
metric sum_order_value {
label: 'Sum Order Values'
type: 'number'
definition: @aql sum(order_items, order_items.quantity * products.price) ;;
}
// 3. Multiple aggregation
metric average_order_value {
label: 'Average Order Value'
type: 'number'
definition: @aql sum_order_value / count_orders;;
}
// 4. Metric with condition:
metric aov_last3m {
label: 'AOV Last 3 Months'
type: 'number'
definition: @aql average_order_value | where(orders.created_at matches @(last 3 months)) ;;
}
}
Model Relationships
![](https://cdn.holistics.io/docs/modeling/simple-dataset-relationships.png)
Components of a Metric
A basic Metric in Holistics is required to have the following components:
- The table that we will run the aggregation on
- The field to be aggregated
- The aggregation function
These three components are combined into an aggregation expression. All of the following syntax are equivalent:
// Full form
aggregate_function(table, table.field)
// Shorthand form
aggregate_function(table.field)
// With pipe operator
table | aggregate_function(table.field)
- Please refer to Aggregate Functions document for more details on different forms of aggregation functions.
- Please refer to Table Expression document for more details on how to construct table expressions. In this guide, we will work with a simple table expression consisting of only a model name.
- The
|
symbol is the pipe operator that take the result of the expression on the left and pass it to the expression on the right.
Create Metrics
1. Simple aggregation
The most basic metric in Holistics involves aggregating a single field in a single model. In this case, we simply count
the values of id
field in the orders
model:
// 1. Simple aggregation
metric count_orders {
label: 'Count Orders'
type: 'number'
definition: @aql count(orders.id) ;;
}
2. Cross-model aggregation
Here we want to calculate the value of orders, which equals to quantity of items in the order x the item's price. The definition is now a bit more complicated:
metric sum_order_value {
label: 'Sum Order Values'
type: 'number'
definition: @aql sum(order_items, order_items.quantity * products.price) ;;
// This is also valid: order_items | sum(order_items.quantity * products.price)
}
In this metrics, there are two tables involved: order_items
and products
. To let Holistics know exactly from which table should we run the aggregation on, we have to explicitly specify it. In this case, it is the order_items
table.
This AQL expression equals to the following SQL:
SELECT
SUM(order_items.quantity * products.price) AS sum_order_value
FROM order_items
LEFT JOIN products on order_items.product_id = products.id
There are two specific requirements to make cross-model aggregation works correctly:
Aggregation table must be specified: If it is not specified in the metric definition like so:
definition: @aql sum(order_items.quantity * products.price) ;;
you will encounter an Invalid expression error (that basically says the Holistics engine cannot know which table to aggregate on)
Aggregation table must be the N-table in an N - 1 relationship: For example, the relationship between
order_items
andproducts
is N - 1. If you specifyproducts
as the aggregation table like so:definition: @aql sum(products, order_items.quantity * products.price) ;;
you will encounter a fan-out error.
This is a very simple example of cross-model calculation. For more details about this feature, please check out the dedicated Cross-model Calculation doc.
3. Multiple aggregations in one Metric
To calculate Average Order Value (AOV), we need to divide total order value by number of orders. In other words, AOV is an aggregation that involves two other aggregations.
Division is a simple arithmetic operation. We can combine the two aggregations like so:
metric average_order_value {
label: 'Average Order Value'
type: 'number'
definition: @aql sum(order_items, order_items.quantity * products.price)
/ count(orders, orders.id);;
}
However, we can reuse the definition of those two metrics by simply referring their names:
metric average_order_value {
label: 'Average Order Value'
type: 'number'
definition: @aql sum_order_value / count_orders;;
}
4. Metric with filtering condition
To calculate a metric only on a subset of records, like AOV of orders in the last 3 months, we append a condition to the metric:
metric aov_last3m {
label: 'AOV Last 3 Months'
type: 'number'
definition: @aql average_order_value | where(orders.created_at matches @(last 3 months)) ;;
}
Useful documents that you should check out:
- where: For more details on condition functions
- AQL Operators: List of logical operators that you can use in the conditions
- AQL Operators - Datetime section: How to write datetime filtering conditions
Using Metrics
After everything is done, if you go to the Data tab to preview your dataset, you will see a new Metrics section on the left panel with all of the metrics you have created.
![](https://cdn.holistics.io/docs/modeling/metrics-in-datasets.png)
More advanced Metrics use cases
Metrics in Holistics is a powerful tool that allows complex calculations. Here are some topics that may interest you once you have mastered the basics of Metrics: