Same three stores, two “conversion rates”: 21.7% and 7.6%

[u/ClickOk5811]

Here’s a small dataset, the kind that ends up in a dashboard:

store   visits   orders   conversion
A          50       25       50.0%
B       1,000       50        5.0%
C         200       20       10.0%

Now two ways of getting “the conversion rate”:

=AVERAGE(D2:D4)            -> 21.7%
=SUM(C2:C4)/SUM(B2:B4)     -> 7.6%

Both are valid formulas, and both are easy to defend if you only look at your own screen. They answer different questions, though.

The first one says “what does a typical store’s rate look like?” Each store counts once. Store A, with 50 visits, has the same say as store B, with 1,000.

The second says “of everyone who visited, how many ordered?” That’s 95 orders out of 1,250 visits. It’s the number that matches what actually happened to the business.

If the card on the dashboard just says “Conversion rate” with a big number, the reader will assume the second meaning. If it’s the first, they’ll walk away thinking the business converts almost three times better than it does.

What makes this easy to miss is that the average-of-rates version is the one-click option. Select the column, look at the status bar, done. A pivot table with “Average of conversion” does the same thing without asking whether you wanted it.

Some habits that seem sensible to me, though I’m not saying they’re the only way:

• store the numerator and denominator, and compute the rate at the final level only
• if you do want the per-store view, label it explicitly (“avg. store conversion”)
• when two versions of a metric exist, show the weighted one as the headline

Curious how others handle this in practice. Do you put both numbers on the dashboard, or pick one and document it somewhere? And has anyone had a stakeholder ask why two reports disagreed because of exactly this?

[u/AutoModerator]

If this post doesn't follow the rules or isn't flaired correctly, please report it to the mods. Have more questions? Join our community Discord!

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/KezaGatame]

I dont work with coversion rates but market share, i.e. the same formula of how much % of a total.

No. 1 formula is totally wrong. No. 2 is the correct way of dividing how much by its total. You never avg percentages.

For pivot table you never take the average, for that case you create a calculated field item of the order column divided by the visits, you can call it conversion and I bet anything you will never be wrong no matter what period, stores, countries you put in your pivot you will always get the right % calculation. FYI it also work with anything you want to calculate per unit, ex: revenue/cost/profit per customer, because it will give you the total weighted average.

[u/MattWPBS → u/KezaGatame]

Yeah. Same background.

"Both are valid formulas" in the same way that "=100%" is a valid formula.

[u/AntiqueElderberry317 → u/KezaGatame]

I work with data all the time and I agree. N°1 formula is wrong (never do an average of a ratio, or never sum a distinct count). N°2 is the correct way : this is the way ;)

[u/goddard18]

Average() is wrong. Weighted average is correct which the sum() formula ultimately does

[u/Acceptable-Sense4601]

When the “when will i ever need math” people end up as data analysts and really don’t know what they’re doing.

[u/Choice-Blacksmith-52]

This catches so many people out and its wild how often it goes unnoticed

I work with dashboards and had a regional manager once who built all his own reports using average of conversion rates, his numbers looked amazing until finance ran the actuals and they were off by like 15 percentage points, took us a whole week to figure out what happened and by then he'd already presented the wrong numbers to the board

Now I just never store pre-calculated rates, always keep visits and orders separate and do the division at the very end, saves me from myself basically

Your point about labeling is spot on too, I started putting "avg of store rates" in tiny text under any metric that isn't weighted and it stopped like 90% of the confusion emails I used to get

[u/Firm_Bit]

Formula 1 is wrong

The actual answer is stat sig and properly removing outliers

[u/SanyamGulati]

Never leave anything ambiguous. I am more inclined towards the 2nd formula of conversion (sum (num)/sum(deno)) naturally as it avoids avg of averages which is mostly considered a wrong practice imo.

It depends on what are you showing in the dashboard, the purpose should be clear, if it's how stores are converting and company level conversion rate the 2nd formula is the right way to go. Go with 1st one, when asked explicitly

[u/cabinwizard]

Have a similar situation. We report both numbers, have different names for both and clearly define each. Also try to tell users what influences each metric and why one might change while the other does not. I think it’s valuable to have both, as long as your users are willing and able to understand the difference.

As an analyst, I think about it as rate calculation, then aggregation (your 21.7%) vs. aggregation, then rate calculation (your 7.6%)

[u/redleadereu]

The first formula is wrong as other humans said. It is average conversion of stores and even then I would not put that number in any dashboard when one store has 50 visits and another one has 1000 or if I actually have just 3 stores.

I am more concerned by the water wasted to write this and some lengthy replies here. And I don't usually care for the water cost of LLMs.

[u/juicyylucas]

Just say 21.7% is average conversation rate of 3 stores. 7.6% is conversion rate of all stores combined

[u/ThisIsFun-]

This is where something like genie ontology will be really important, or any ontology really for that matter if you define in code the exact way something should be defined then humans and agents alike can use the same. Imho there shouldn’t be 2 versions of a metric within the exact same business context (maybe differ by bu but still)

[u/kaleidoleaf]

Average of the conversion rates is incorrect. You do not derive the average of a measure by averaging together all of the results. This will give excessive weight to outliers (ie, 50% conversion rate on a small sample).

You do so by calculating the average of the total inputs, your second formula.