Data Modeling
Use a geometric mean when changes compound through multiplication. An arithmetic average can say growth was flat or positive even when the cumulative result went down, because it averages rates instead of the factors that actually moved the value.
Why arithmetic averages fail for compounded growth
Arithmetic averages answer an additive question: if you put these values side by side, what is their typical level? Compounded growth asks a different question: after applying each change in sequence, what constant rate would produce the same ending value?
That distinction matters because growth rates do not add cleanly over time. A 50% increase followed by a 50% decrease does not return a value to its starting point. Starting from 100, the first period ends at 150. The second period reduces 150 by 50%, ending at 75. The arithmetic average of +50% and -50% is 0%, but the cumulative result is -25%.
The error comes from averaging the signs and magnitudes of the rates rather than multiplying the growth factors. For compounded growth, the path is multiplicative: 100 times 1.50 times 0.50 equals 75.
The calculation rule: average factors, not rates
Convert each rate into a factor before averaging. A growth rate of 8% becomes a factor of 1.08. A decline of 20% becomes a factor of 0.80. Then multiply the factors and convert back to a rate.
- Convert each period rate r into a factor: factor equals 1 plus r.
- Multiply all factors together to get the total compounded factor.
- Take the nth root of that total factor, where n is the number of periods.
- Subtract 1 to convert the average factor back into an average compounded rate.
For many data systems, the log form is easier to implement: average compounded factor equals exp of the average of ln of each positive factor. The average compounded rate is that result minus 1. This is mathematically equivalent for positive factors and is often easier to express with aggregate functions.
For compounded growth, never average percentage rates directly until you have asked whether the correct cumulative calculation multiplies their factors.
Worked counterexamples that expose the mistake
Counterexamples are the fastest way to find the problem in a metric definition. If a proposed average growth calculation fails these simple cases, it is probably not safe for compounded change.
Example 1: +50% then -50%. Arithmetic average rate is 0%. But the factors are 1.50 and 0.50. Their product is 0.75, so the cumulative change is -25%. The geometric mean factor is the square root of 0.75, about 0.866. The average compounded rate is about -13.4% per period.
Example 2: +100% then -50%. Arithmetic average rate is +25%. But the factors are 2.00 and 0.50. Their product is 1.00, so the cumulative change is 0%. The geometric mean rate is 0% per period.
Example 3: +10%, +10%, then -20%. Arithmetic average rate is 0%. But the factors are 1.10, 1.10, and 0.80. Their product is 0.968, so the cumulative change is -3.2%. The average compounded rate is about -1.08% per period.
| Rates | Arithmetic average | Compounded result | Geometric mean rate | What it shows |
|---|---|---|---|---|
| +50%, -50% | 0% | -25% | About -13.4% | Equal opposite rates do not cancel when compounded. |
| +100%, -50% | +25% | 0% | 0% | A positive arithmetic average can produce no cumulative gain. |
| +10%, +10%, -20% | 0% | -3.2% | About -1.08% | Small arithmetic neutrality can hide a real decline. |
How to identify metrics that need a geometric mean
Use a geometric mean when the metric describes repeated proportional change applied to a base that keeps changing. The telltale sign is that each period's ending value becomes the next period's starting value.
Good candidates include month-over-month growth rates, investment or portfolio returns, retention factors over multiple periods, index changes, recurring expansion or contraction rates, and any metric where the correct total effect is found by multiplying factors.
Do not use a geometric mean just because a metric is written as a percentage. Many percentages are ratios over a shared denominator, not compounded changes. For those, the correct aggregation may be a ratio of sums, a denominator-weighted average, or a plain arithmetic average depending on the question.
| Metric situation | Usually correct aggregation | Reason |
|---|---|---|
| Sequential monthly growth for one product | Geometric mean of factors | Each month compounds from the prior month. |
| Company conversion rate across channels | Ratio of total conversions to total eligible events | The denominator matters more than the average of percentages. |
| Average daily order count | Arithmetic mean | Counts are additive observations, not multiplicative factors. |
| Retention across successive periods | Product of retention factors, with geometric mean for equivalent period rate | The retained population carries forward. |
| Typical growth rate across independent products | Depends on the question: arithmetic, weighted, or geometric | Entity weighting and time compounding are separate decisions. |
When arithmetic averages are still the right answer
Arithmetic means are not wrong in general. They are wrong for the specific question of average compounded change. Use arithmetic averages when values are additive or when the question is about the typical observation rather than the equivalent compounded path.
Average order value, daily ticket counts, average support response time, and average revenue per account are usually arithmetic questions. Segment conversion rates, gross margins, or churn percentages may need weighting by denominator rather than geometric averaging. The right aggregation follows the business process that generated the number.
A useful diagnostic question is: If I replay these values in sequence, do I multiply the current value by each one? If yes, evaluate a geometric mean. If no, look for an additive, weighted, or ratio-of-sums aggregation instead.
Zero, negative, and missing values need explicit rules
The ordinary geometric mean requires positive factors. That is not a cosmetic limitation; it comes from the multiplication and logarithm behind the calculation.
A growth rate of -100% produces a factor of 0. The total compounded result becomes zero from that point forward, and the log form is not defined. A rate below -100% produces a negative factor, which usually does not make sense for ordinary business growth metrics such as revenue, users, or retained accounts.
Do not silently drop zero or invalid factors to make the calculation work. Dropping a -100% period can turn a real collapse into an apparently healthy average. Instead, encode a metric rule: reject invalid factors, return zero cumulative factor when a true zero factor occurs, or report the period as not applicable if the underlying business concept cannot be compounded.
Missing values are a separate issue. A missing period is not the same as 0% growth. Treat it as unknown unless the data model has a documented rule that imputes or derives the missing period.
A formula that drops zero or negative factors without a documented rule is usually changing the meaning of the metric, not cleaning the data.
Implementation guidance for data models
Model compounded metrics so the calculation is hard to misuse. Store the raw starting value, ending value, period boundaries, and derived growth factor where possible. Avoid storing only rounded percentage labels such as 12.3% because they hide the denominator and invite repeated rounding.
In SQL-style systems, remember that a normal average aggregate is arithmetic. To compute a geometric mean, derive positive factors first, then aggregate their logarithms and exponentiate the result. The exact syntax varies by database, but the shape is stable: exp of avg of ln of factor, minus 1 for the rate.
Round late. A sequence of rounded monthly rates can produce a different annual result from the underlying monthly factors. For reporting, round the final displayed rate, not the intermediate factors used in the calculation.
Also document the grain. A geometric mean of monthly growth rates is not the same object as a geometric mean of daily growth rates, even if both are displayed as percentages. The period length and ordering belong in the metric definition.
Store enough raw context to recompute the factor: starting value, ending value, period, entity, and any denominator used to derive the rate.
Common failure modes in dashboards and metric layers
Averaging already-averaged growth rates. A dashboard may average monthly growth rates that were already averaged across products, regions, or cohorts. This can combine two different aggregation problems: compounding over time and weighting across populations.
Mixing period lengths. Weekly, monthly, and quarterly growth rates cannot be averaged together without converting them to a common basis or explicitly weighting the time exposure.
Ignoring denominators. A product with 10 users and a product with 10 million users should not usually contribute equally to company-level growth. First decide whether the question is about the typical product or the total business.
Hiding collapses with filters. Removing zero, negative, or extreme periods because they break a formula can make the metric look stable while deleting the most important signal.
Confusing cumulative growth with average growth. Cumulative growth describes the total start-to-end change. Geometric mean growth describes the constant per-period rate that would produce that same result. They are related, but they answer different questions.
A practical checklist for choosing the aggregation
Before using an arithmetic average on a growth rate, run through this checklist.
- Does each period's ending value become the next period's starting value?
- Would the cumulative result be calculated by multiplying factors?
- Are all factors positive, or do zero and invalid factors require separate handling?
- Are the periods the same length, or is time weighting required?
- Are the rates based on comparable denominators?
- Is the question about the total business, the typical entity, or the typical period?
- Can the result be reproduced from raw starting and ending values?
If the answer points to sequential multiplication, use a geometric mean for the average compounded rate. If the answer points to counts, amounts, or ratios over different denominators, use the aggregation that matches that structure instead.
Key takeaways
- Arithmetic averages are for additive questions; geometric means are for average compounded change.
- Convert rates to factors before aggregating: 10% becomes 1.10, and -20% becomes 0.80.
- The geometric mean rate is the constant per-period rate that produces the same cumulative result.
- A percentage is not automatically a compounded metric; ratios across segments often need denominator weighting instead.
- Zero, negative, missing, rounded, and mixed-period values require explicit metric rules before calculation.
Next step
Audit one dashboard or metric layer for averaged growth rates. For each one, write down whether the cumulative effect is additive, denominator-weighted, or multiplicative. Recalculate at least one compounded metric from raw factors and compare it with the arithmetic average currently shown.
- Read Variance Aggregation Across Partitions: How to combine group statistics correctly without averaging incompatible variances.
- Read Data Modeling Before Dashboards: Build Metrics People Can Trust: Use business definitions, entities, events, and trusted marts before investing in dashboard polish.
- Read Warehouse First Analytics: Plain-English Guide: A practical introduction to using the data warehouse as the center of your analytics system.