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

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)

Notes

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

For cross-model aggregation to work correctly

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 and products is N - 1. If you specify products 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)) ;;
}

tip

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.

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:

Common Metrics use cases​

Components of a Metric​

Create Metrics​

1. Simple aggregation​

2. Cross-model aggregation​

3. Multiple aggregations in one Metric​

4. Metric with filtering condition​

Using Metrics​

More advanced Metrics use cases​