Why averaging daily rates gives you the wrong number

Export a week of daily figures, average the click-through rate column, and you get a number for the week. It is almost always wrong, sometimes wildly. The same goes for conversion rate, bounce rate, cost per click and Search Console's average position. Rates and averages cannot be averaged. They have to be worked out again from the totals.

The mistake is everywhere because it is the obvious thing to do: a spreadsheet has an AVERAGE() function, a dashboard rolls days up into weeks, and the result looks perfectly reasonable.

A two-day example

Average the two daily rates and you get 25.5%. The real rate for the two days is 11 clicks from 1,002 impressions: 1.1%. The average treats Monday's two impressions as if they mattered as much as Tuesday's thousand.

A rate is a ratio of two totals. The rate for a longer period is the ratio of the longer period's totals, never the average of the shorter periods' rates.

Real data is rarely that extreme, but it does not need to be. Any day, campaign or page with few impressions or visits pulls a plain average towards its own rate, which is usually the noisiest one.

Where it happens

• Click-through rate: clicks ÷ impressions.
• Conversion rate: conversions ÷ sessions (or users, or clicks).
• Cost per click and cost per conversion: spend ÷ clicks, spend ÷ conversions.
• Engagement or bounce rate: engaged sessions ÷ sessions.
• Average order value: revenue ÷ orders.
• Search Console's average position, which is the worst case, covered below.

It is not only days. Averaging the conversion rates of your channels, campaigns or countries gives a site-wide rate that is just as wrong, for the same reason: a channel with ten visits counts as much as one with ten thousand.

Average position, the worst case

Search Console's average position is already an average over impressions. Average it again across days, queries or pages and the small rows take over. Say a page ranks at position 2 for a query seen 1,000 times, and at position 40 for one seen 10 times:

• Plain average of the two positions: 21, page three of the results.
• Weighted by impressions: 2.4, near the top of page one.

The plain average makes a page that people almost always see near the top look as if nobody would ever find it, because a long tail of rare queries where it ranks badly counts as much as the one query that brings its traffic.

How to get the right number

Add up the parts, then divide. Keep the two totals a rate is made of, sum each over the period or group you want, and work the rate out once at the end.

• In a spreadsheet, total row: =SUM(clicks)/SUM(impressions), not =AVERAGE(ctr). The same for every rate above, with its own two columns.
• For average position, weight each row by its impressions: =SUMPRODUCT(position, impressions)/SUM(impressions).
• In Looker Studio, when the data comes from exported rows with a rate column, do not set that column's aggregation to Average. Create a calculated field such as SUM(Clicks) / SUM(Impressions) instead.
• In GA4 and Search Console themselves, the total shown for a date range is already worked out from the totals. Use it rather than recalculating it from the daily rows.
• When you export data, export the counts (clicks, impressions, sessions, conversions, spend), not just the rates. Without them the right figure cannot be rebuilt.

How Glancely handles it

When a chart is viewed by week or month, Glancely adds up clicks, impressions, sessions, conversions and spend for each week or month and works every rate out again from those totals. Search Console's average position is weighted by impressions. The same applies when several properties, sites or ad accounts are shown together: rates are recalculated from the combined totals, never added or averaged. We learned this the hard way: an unweighted average position once read many times worse than the truth.