Isn't an average of the daily rates a fair summary?

Only if each day should count the same, regardless of traffic. For 'what share of visits converted,' a day with 100 visits and a day with 10,000 should not weigh the same. The overall rate uses the totals.

The two numbers are close. Does it matter?

They are close when every day has similar volume. They diverge when volume swings, which is normal (weekends, a campaign, a launch). A gap of a few points is enough to send the wrong week to the board.

What if a day has zero visits?

Conversions divided by zero is an error, or NULL if you guard it. AVG skips NULL, so that day vanishes from the average of rates and the remaining days get more weight. The overall rate has the same problem only if every day is empty.

Is this the integer-division bug, where the rate comes back 0?

No. That one truncates 3/10 to 0 before you ever see a percentage. This one returns a real percentage. It is just the average of the pieces, not the rate of the whole.

Does the same thing happen to average order value?

Yes, if the AI averages each customer's average order, or each day's average, instead of total revenue divided by total orders. A customer with one order weighs the same as a customer with a hundred.

Average of Rates vs Overall Rate | SQL AVG

You ask: "What's our average conversion rate this week?" The AI says 25.5%. You check the raw counts. Monday converted 50 of 100 visits. Tuesday converted 10 of 1,000. The real rate is 60 out of 1,100, about 5.5%.

SELECT AVG(conversions::numeric / NULLIF(visits, 0))
FROM daily_stats

Both days are in the average. Monday is 50%. Tuesday is 1%. The average of those two rates is 25.5%. Tuesday brought almost all the traffic and almost none of the weight.

A rate of rates is not the rate

AVG of a percentage gives every group one vote. The overall rate gives every visit one vote.

-- what "the conversion rate" usually means
SELECT SUM(conversions)::numeric / NULLIF(SUM(visits), 0)
FROM daily_stats

60 / 1100 is 0.0545. The query above is a legal average. Nothing in the result says "each day weighed the same." That is why it reads as the answer.

This is not integer division returning 0. You get a plausible percentage, just of the wrong thing. It is also not AVG skipping NULL rows, though a day with zero visits becomes NULL and then does get skipped, which moves the average again.

The same shape shows up anywhere a ratio is stored per group: average order value per day, refund rate per country, click rate per campaign. Averaging those rows treats a tiny group like a huge one.

How to fix it

-- overall rate, from the totals
SELECT SUM(conversions)::numeric / NULLIF(SUM(visits), 0) AS overall_rate
FROM daily_stats
WHERE day >= CURRENT_DATE - 7

-- per day, if you actually want each day
SELECT day,
       conversions::numeric / NULLIF(visits, 0) AS daily_rate,
       visits
FROM daily_stats
ORDER BY day

Keep the volume next to any daily rate. A 50% day on 4 visits should not sit next to a 4% day on 40,000 without the counts.

If the table only has a precomputed conversion_rate column and no counts, the overall rate cannot be recovered. Say so, instead of averaging the rates.

How to catch it

SELECT SUM(conversions)::numeric / NULLIF(SUM(visits), 0) AS overall,
       AVG(conversions::numeric / NULLIF(visits, 0))     AS unweighted
FROM daily_stats
WHERE <the filter the AI used>

If overall and unweighted differ, the AI reported the second one. Ask which one you meant before you quote it.

The prompt that heads this off

When I ask for a rate, conversion, or percentage across days, customers,
or campaigns, compute it from the totals: sum of the numerators divided
by sum of the denominators. Do not average the per-group rates. If the
two differ, show both and say which is the overall rate.

The short version

Averaging daily rates gives every day one vote. The conversion rate gives every visit one vote. Divide the sums, and keep the counts beside any rate you show.

Read-only MCP gateway: mcpserver.design.

Related reading: