Metrics By Examples

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)

Metrics that 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))

Aggregation with scalar functions

Average Price of Product

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

Total Actual Product Price

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

Metrics that involve multiple models

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

// Same metric but only on refunded orders
order_items 
| sum(order_items.quantity * products.price)
| where(orders.status != 'refunded')

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())

Metrics with window functions

Running Sum of Count over Order Status

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

Metrics with multiple aggregation levels

Average GMV per Customer

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

Metrics with 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,
  )

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)
  ))