Measures and metrics
Define the numbers — additive measures and the ratio metrics built from them — with one canonical definition each.
A metric is a governed calculation. Percolate has two kinds, and telling them apart is the key design decision of the whole layer.
Measures: a single aggregate on one base table
Most metrics are a SUM or COUNT anchored to one fact table. This is where you encode the business rules — the refund exclusion, the discount subtraction — once:
revenue:
kind: measure
base: order_items
sql: "SUM(order_items.quantity * order_items.unit_price - order_items.discount)"
filters: ["orders.status = 'completed'"] # refunds excluded, forever
orders_count:
kind: measure
base: orders
sql: "COUNT(DISTINCT orders.order_id)" # DISTINCT prevents fan-out
filters: ["orders.status = 'completed'"]
mrr:
kind: measure
base: subscriptions
sql: "SUM(CASE WHEN subscriptions.status='active' THEN subscriptions.mrr_amount ELSE 0 END)"Read revenue closely: the discount subtraction and the completed-only filter are now the definition of revenue. No query can accidentally omit them, because no query writes this SQL — the layer does.
Ratio metrics: numerator ÷ denominator
AOV, CAC, and gross margin are ratios of two measures. Crucially, the numerator and denominator can live on different base tables — the layer computes each separately (grouped by the same dimensions) and divides per group:
aov: # revenue / orders — same fact family
kind: ratio
numerator: revenue
denominator: orders_count
cac: # spend / new customers — DIFFERENT tables
kind: ratio
numerator: marketing_spend
denominator: new_customersCAC divides marketing_spend (from marketing_spend) by new_customers (from customers). They share no table. Because both resolve the channel dimension to a matching set of values, the layer can compute each measure grouped by channel and divide them row-by-row. Defining CAC becomes two lines instead of a fragile hand-written join.
When you define a metric, you're making a business decision, not a SQL one: Does revenue include refunds? (No.) Is CAC blended or per-channel? (Per-channel, here.) Write these down — they're the contract the whole company will rely on.