SQL, data modeling, data quality, EDA, visualization · insight-0075

Decompose a revenue change into volume, price and mix before explaining it

Situation
Quarterly revenue rose 8% and leadership asks why, while passengers, average fares and the route mix all changed.
Action
Compute a price-volume-mix decomposition at a fixed segment grain (route or fare level): volume effect at old prices and mix, mix effect from changed shares, rate effect from changed prices within segments. Show it as a waterfall that sums exactly to the total change, and state the order and grain chosen.
Reason
Ratios and totals change for several reasons at once; an additive decomposition assigns each part of the change to an explicit cause in the metric's arithmetic.
Boundary
Revenue, margin, cost per unit and any KPI that is a weighted average across segments.

Symptom or risk

A single narrative ("prices went up") is chosen after the fact, and the actual driver, such as a shift toward long-haul routes, goes unmanaged.

When the segment grain is unstable (new routes, reclassifications), mix effects are poorly defined; report them separately.

Results depend on decomposition order and grain; a finer grain shifts effect from rate into mix.

Presenting arithmetic drivers as causal explanations; the decomposition says where, not why.