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 type | Examples | Guide |
|---|---|---|
| Involve multiple tables/models | Sum of sales:order_items | sum(order_items.quantity * products.price) | Use cross-model AQL aggregation |
| Involve conditions | Average 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 accumulation | Running total of orders by year:orders.total_orders | running_total(orders.created_at | year()) | Use running_total or window aggregation functions |
| Involve different time period | Number 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 type | Examples | Guide |
|---|---|---|
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 conditions | Bank balance:sum(balances.bank_balance) | where(dim_dates.date == max(balances.record_date)) | Follow Semi-additive calculation |
| Calculated in different subgroups | 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 aggregations | Average 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.