Metrics by Example

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) via order_id
• order_items → products (many:1) via product_id
• orders → users (many:1) via user_id
• users → 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:

1. One aggregation, or several? A single sum/count/avg is simple aggregation. Anything that aggregates an already-aggregated value (avg of monthly totals, percent of total, semi-additive sums) is multi-aggregation.
2. 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.

Total Price of all Products after Discount

// `products` source table expression can be omitted
products | sum(products.price * products.discount)

Average Ordered Quantity of Order Items

avg(order_items.quantity)

Conditional aggregation

Average Ordered Quantity of Order Items over the Last 3 Months

avg(order_items.quantity)
| where(order_items.created_at matches @(last 3 months))

Aggregation with scalar functions

Average Price of Product

sum(products.price) / count(products.id)

Total Actual Product Price

products | sum(products.price * products.discount)

Cross-model aggregation

Gross Merchandise Value (GMV)

order_items | select(order_items.quantity * products.price) | sum()

// or more succinctly
order_items | sum(order_items.quantity * products.price)

GMV of refunded orders

order_items
| sum(order_items.quantity * products.price)
| where(orders.status != 'refunded')

See Cross-Model Queries for the full walkthrough.

Cumulative metrics

Cumulative Sum of GMV

// orders.value = order_items | sum(order_items.quantity * products.price)
sum(orders.value) | running_total()

Running Percentage of Asia GMV over global GMV

(sum(orders.value) | running_total() | where(continents.name == 'Asia'))
  / (sum(orders.value) | running_total())

See Cumulative Metrics for the full walkthrough.

Window functions

Running Sum of Count over Order Status

window_sum(count(orders.id), order: orders.status)

Multiple aggregation levels

Average GMV per Customer

orders | group(users.id) | select(sum(orders.value)) | avg()

See Nested Aggregation.

Custom relationships

Sum of revenue using 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 Bank Balance Any Customers

sum(balances.bank_balance)
| where(dim_dates.date == (
    max(balances.record_date) | of_all(balances.user_name)
  ))

See Semi-Additive Calculation.

---

In Holistics, data exploration is typically done through the UI, which generates AQL queries in the explore { } format. The examples here show AML metric definitions. In practice, you define metrics once in AML and the UI generates the explore syntax for you.