Metric
Explaining a change
Split a metric's change into parts that add up to it: volume against value, mix against rate, then by segment.
A product (revenue = orders × average order) splits as ΔN × AOV₀ + N₁ × ΔAOV. An average over groups splits into
mix Σ (share₁ − share₀) × rate₀ and rate Σ share₁ × (rate₁ − rate₀). Then cut the change by one dimension after
another (device, channel, tenure) and stop where it is concentrated, not spread. Compare with the same period a year
before, not only with last month.
WITH months(month, orders, revenue) AS (VALUES ('May', 33607, 6364573.92), ('June', 29889, 5687462.97)),
m AS (
SELECT MAX(orders) FILTER (WHERE month = 'May') AS n0, MAX(revenue) FILTER (WHERE month = 'May') AS r0,
MAX(orders) FILTER (WHERE month = 'June') AS n1, MAX(revenue) FILTER (WHERE month = 'June') AS r1
FROM months
)
SELECT r1 - r0 AS change,
ROUND((n1 - n0) * (r0 / n0), 2) AS volume_effect,
ROUND(n1 * (r1 / n1 - r0 / n0), 2) AS value_effect
FROM m;
| change | volume_effect | value_effect |
|---|---|---|
| -677110.95 | -704123.72 | 27012.77 |