Metrics by Example
Find the AQL pattern that fits your question: by intent, by metric shape, or by copy-paste snippet. Built on a shared e-commerce schema.
The Cookbook landing page. Three ways to find what you need:
- By question: "I want to do X" → the page that shows you how.
- By metric shape: what shape is the metric, and which function handles it.
- Snippet gallery: copy-paste AML for the most common patterns.
Shared e-commerce schema
Most examples on this page and across the Cookbook use this schema:
Models: users, orders, order_items, products, categories, merchants, cities, countries.
Key relationships:
order_items→orders(many:1) viaorder_idorder_items→products(many:1) viaproduct_idorders→users(many:1) viauser_idusers→cities→countries(geographic chain)products→categories(many:1)products→merchants(many:1)
By question
When you know the question but not the AQL feature:
| I want to… | Go to |
|---|---|
| Slice a metric by a dimension and add filters | Learn → Filtering, Grouping |
| Define a reusable metric or AQL dimension | Learn → Defining a Metric |
| Build a metric that spans multiple models | Learn → Cross-Model Queries |
| Compute a ratio (e.g. completion rate) | Learn → Defining a Metric, Cookbook → Percent of Total |
| Segment customers with a boolean flag (VIP, churned, etc.) | Cookbook → LoD Patterns |
| Bucket records into tiers (deciles, quartiles) | Cookbook → Ranking |
| Compute percent of total | Learn → Level of Detail, Cookbook → Percent of Total |
| Aggregate already-aggregated data (avg of monthly totals, max of per-user metric) | Learn → Nested Aggregation, Cookbook → Nested Aggregation |
| Rank items, with or without partitions | Cookbook → Ranking |
| Year-over-year, quarter-over-quarter comparison | Learn → Time Comparisons, Cookbook → Period Comparison |
| Smooth a time series with a moving average | Cookbook → Moving Average |
| Compound annual growth rate (CAGR) | Cookbook → CAGR |
| Analyze customer order frequency / cohort distributions | Cookbook → Customer Order Frequency |
By metric shape
Most metric questions resolve once you answer two questions:
- One aggregation, or several? A single
sum/count/avgis simple aggregation. Anything that aggregates an already-aggregated value (avg of monthly totals, percent of total, semi-additive sums) is multi-aggregation. - What's the relationship between aggregations or filters? Conditions, time periods, subgroups, accumulation, or nesting each map to a different AQL function.
Single-aggregation metrics
| Shape | Example | Where to go |
|---|---|---|
| Multiple tables/models | order_items | sum(order_items.quantity * products.price) | Cross-Model Queries |
| Filtered by condition | avg(order_items.quantity) | where(order_items.created_at matches @(last 3 months)) | where() + logical operators |
| Running / accumulated | orders.total | running_total(orders.created_at | year()) | Cumulative Metrics, window_sum |
| Different time period | orders.count | relative_period(orders.created_at, interval(-1 year)) | Period Comparison |
Multi-aggregation metrics
| Shape | Example | Where to go |
|---|---|---|
| Scalar combination of metrics | sum(products.price) / count(products.id) | Just write metric_a / metric_b |
| Aggregations linked by a condition | sum(balances.bank_balance) | where(dim_dates.date == max(balances.record_date)) | Semi-Additive Calculation |
| Calculated across different subgroups | (order_items.total_sales*1.0) / (order_items.total_sales | of_all(order_items)) | Level of Detail Patterns, Percent of Total |
| Nested aggregations | users | group(month(users.sign_up_at)) | select(count(users.id)) | avg() | Nested Aggregation |
Snippet gallery
Copy-paste-able definitions for the most common patterns.
Simple aggregation
Simple aggregation works by taking a table and an expression to evaluate over each row of that table.
// `products` source table expression can be omitted
products | sum(products.price * products.discount)avg(order_items.quantity)Conditional aggregation
avg(order_items.quantity)
| where(order_items.created_at matches @(last 3 months))Aggregation with scalar functions
sum(products.price) / count(products.id)products | sum(products.price * products.discount)Cross-model aggregation
order_items | select(order_items.quantity * products.price) | sum()
// or more succinctly
order_items | sum(order_items.quantity * products.price)order_items
| sum(order_items.quantity * products.price)
| where(orders.status != 'refunded')See Cross-Model Queries for the full walkthrough.
Cumulative metrics
// orders.value = order_items | sum(order_items.quantity * products.price)
sum(orders.value) | running_total()(sum(orders.value) | running_total() | where(continents.name == 'Asia'))
/ (sum(orders.value) | running_total())See Cumulative Metrics for the full walkthrough.
Window functions
window_sum(count(orders.id), order: orders.status)Multiple aggregation levels
orders | group(users.id) | select(sum(orders.value)) | avg()See Nested Aggregation.
Custom relationships
sum(order_items.revenue)
| with_relationships(
order_items.order_id > orders.id,
order_items.country_id > countries.id,
)See with_relationships.
Semi-additive metrics
sum(balances.bank_balance)
| where(dim_dates.date == (
max(balances.record_date) | of_all(balances.user_name)
))See Semi-Additive Calculation.
The examples here show AML metric definitions. To run one, put it in an explore { } block with the dimensions you want, as the samples on this page do. In practice you define metrics once in AML and write, or let a tool write, the explore that uses them.
Project profitability
Calculate discount factor, free cash flow, discounted cash flow, NPV and payback year for a project with reusable AQL functions.
Level of Detail Patterns
Worked examples of Level of Detail in AQL: nested aggregation, dimensionalize(), customer segmentation flags, and is_at_level() for per-level percentages.