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

Compute rate KPIs as a ratio of sums unless you deliberately want the average of unit rates

Situation
Network load factor is reported as AVG(load_factor) across flights, while finance computes passengers divided by seats.
Action
Name the question first. For "share of seats filled", use SUM(passengers) / SUM(capacity). For "typical flight's load factor", the average of unit ratios is valid but must be labelled. Publish one definition per KPI and keep both numerator and denominator available.
Reason
An average of ratios weights each unit equally; a ratio of sums weights by the denominator. They answer different questions and can move in opposite directions.
Boundary
Load factor, conversion, margin, CTR, defect rate and any per-unit KPI.

Symptom or risk

The two numbers differ because small and large flights get equal weight in the average, and teams argue over which is right.

In experiments randomized by user, an average of per-user ratios can be preferable for variance and robustness reasons.

Ratio of sums is dominated by large units; average of ratios is sensitive to tiny ones.

Averaging pre-computed daily ratios to get a monthly ratio.

Ratio metrics can be computed as the average of ratios or as the ratio of averages; the two weight units differently, and the average of per-user ratios has practical advantages in user-randomized experiments.
A Dirty Dozen: Twelve Common Metric Interpretation Pitfalls in Online Controlled Experiments