Skip to main content

Single vs Multiple-aggregation metrics

Overview

Working with complex metrics seem hard at first, but with a systematic approach and the help of AQL metric functions, you should be able to breeze through your metric development process. This document describes a method to assist you on your path to build the metrics you want.

To begin, it's crucial to determine whether the metric you require involves a single aggregation or multiple aggregations. If the metric involves a single aggregation, please refer to the table in the Single-aggregation metrics section found below. Conversely, if your metric involves multiple aggregations, try to determine the relationship between these aggregations, and follow the table in the Multiple-aggregation metrics below.

Single-aggregation metrics

Try to determine the key logic of the aggregation and follow the table below:

Single aggregation typeExamplesGuide
Involve multiple tables/modelsSum of sales:
order_items | sum(order_items.quantity * products.price)
Use cross-model AQL aggregation
Involve conditionsAverage Ordered Quantity of Order Items over the Last 3 Months:
avg(order_items.quantity) | where(order_items.created_at matches @(last 3 months))
Use where function together with logical operators
Involve accumulationRunning total of orders by year:
orders.total_orders | running_total(orders.created_at | year())
Use running_total or window aggregation functions
Involve different time periodNumber of Orders Previous year:
orders.count_orders | relative_period(orders.created_at, interval(-1 year))
Use time-based functions such as exact_period or relative_period

Multiple-aggregation metrics

Try to determine the relationships between aggregations and follow table below:

Multiple-aggregation typeExamplesGuide
Simple/scalar combinations (+/-/*/:)?Average Price of Product:
sum(products.price) / count(products.id)
Just write them normally e.g. metric_a / metric_b
Related through some conditionsBank balance:
sum(balances.bank_balance) | where(dim_dates.date == max(balances.record_date))
Follow Semi-additive calculation
Calculated in different subgroups (Level of Detail)Percent of total:
(order_items.total_sales*1.0) / (order_items.total_sales | of_all(order_items))
Use of_all function with specific dimensions
Nested aggregationsAverage of monthly sign-ups:
users | group(month(users.sign_up_at)) | select(count(users.id)) | avg()
Follow Create Nested Aggregation

Once the relationship between aggregations are clear, you can refer back to Single-aggregation metrics to work on each independent aggregation.


Let us know what you think about this document :)