✅ The Concept: Ratio Metric in dbt
In dbt’s metrics layer (enabled via dbt Metrics), a ratio metric is used to calculate things like:
- Conversion rate = conversions / sessions
- Return rate = returned orders / total orders
- Average order value = revenue / number of orders
To implement this, dbt requires that:
- The numerator metric is already defined
- The denominator metric is already defined
- The ratio metric references them
📦 Example Use Case: Conversion Rate
We want to define a conversion_rate metric based on:
- Numerator:
conversions - Denominator:
sessions
🔹 Step 1: Define the conversions Metric
metrics:
- name: conversions
label: Total Conversions
model: ref('fct_events')
description: Number of successful purchases
calculation_method: count
expression: event_id
filter: event_type = 'purchase'
time_grains: [day, week, month]
🔹 Step 2: Define the sessions Metric
- name: sessions
label: Total Sessions
model: ref('fct_events')
description: Number of user sessions
calculation_method: count
expression: session_id
filter: event_type = 'session_start'
time_grains: [day, week, month]
🔹 Step 3: Define the conversion_rate Ratio Metric
- name: conversion_rate
label: Conversion Rate
description: Percentage of sessions that result in a purchase
calculation_method: ratio
numerator: conversions
denominator: sessions
time_grains: [day, week, month]
format: percentage
✅ dbt will compute:
conversion_rate = count(purchase events) / count(session_start events)
🧠Optional Enhancements
You can also add:
dimensions: to slice the ratio by region, device type, etc.window: to compute rolling averages
dimensions: [device_type, country]
window: 7 days
✅ Summary
| Metric Name | Type | Notes |
|---|---|---|
conversions | Base | Defined using calculation_method: count |
sessions | Base | Another base metric |
conversion_rate | Ratio | Uses numerator + denominator |
📌 Why It’s Required
dbt needs both base metrics explicitly defined, so it knows:
- How to aggregate them
- At what time grain
- What filters to apply
- How to recompute them if logic changes