Skip to main content

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

Metrics with window functions

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 multiple aggregation levels

Average GMV per Customer
orders | group(users.id) | select(sum(orders.value)) | avg()

Let us know what you think about this document :)