1

How does excel calculate the site average values in the generic behaviour report page?

(i.e. average site bounce rate, %exit, time on page etc.)

The values I get in my manual Excel calculations of average values (using downloaded GA data) are different to the ones provided in the GA report.

Specifically the mean value I calculate for my bounce rate in Excel is 17.84% but Google Analytics tells me it's 53.10%

How can those numbers be so different?

The total number of pageviews for the site over the past month is ~ 7,400 so it's not a huge sample size, but it wouldn't seem to explain that sample size. Is Google doing some other kind of average, or silently filtering outliers?

Detailed Recreation added below

  • Set Google Analytics to a month.
  • Navigate to the Behaviour tab > Site content > All pages
  • Set sessions to all sessions

Google Analytics Average

The Google Analytics Average I'm using is the top of the bounce rate column on the main landing page. (Screenshot to identify it below.)

enter image description here

Excel Average

On the Google Analytics behaviour sheet I selected the maximum number of rows (Shown at the bottom of the report) and then exported it as an XLSX spreadsheet.

I got the excel mean average by using the AVERAGE formula on the bounce rate column. (Sum all the bounce rates and then divide by the total number)

Improve this question
4
  • As it stands, this is impossible to answer. The phrase downloading my behavior data is non-specific. If you can define this for us; ie: How did you download the data?, What columns are you looking at?, and so on, then we can compare apples to apples instead of apples to garter-belts. (not that that would necessarily be bad...)
    – closetnoc
    Commented Dec 7, 2014 at 19:09
  • @closetnoc Ok added re-creation instructions and re-worded everything for clarity. Hopefully it's clearer now!
    – Dominic Woodman
    Commented Dec 7, 2014 at 20:06
  • It was still not entirely clear how you got your data, but I think I found what you meant. However, I do not use Google Analytics enough to know how the data is derived. In fact, I barely use it at all except to compare some of the basic metrics with my daily web analysis. I will have to wait till someone who know better can answer. What I suspect is that these are not complete data-sets or the values are averages and cannot be extrapolated the way you expect or even both.
    – closetnoc
    Commented Dec 8, 2014 at 2:54
  • @closetnoc Ok added a little more detail, but I think I solved it anyway! Added an answer below.
    – Dominic Woodman
    Commented Dec 8, 2014 at 12:15

2 Answers 2

Reset to default
2

There are some numbers that don't make sense in your data.

Here's Bounce Rate data from my Analytics:

enter image description here

While the first row is "New Visitors" and the second "Returning Visitors".

By exporting this data set, I have the following results on Excel:

enter image description here

While the first row is "New Visitors", the second "Returning Visitors", the third the average coming from Analytics and the last is the Average done by Excel. As you can see the numbers vary a little bit but nothing serious and certainly not as you describe.

Also, by looking at the image you provided, do the Average calculation and the results are neither the 53.10% or the 17.84% but 32.73% (34.43 + 24.29 + 27.27 + 17.65 + 60 / 5).

Although the problem you report is not only related with Bounce Rate, take a look at this page from Google as it might give you insights on how it's calculated. https://support.google.com/analytics/answer/2525491?hl=en

Improve this answer
2
  • I think solved it, thanks to your last link! The image I posted was just a shot to show which part of the page I was talking about, not what I actually used for calculating it. i think the problem was with the data I was downloading. I was downloading all the site pages as opposed to the landing pages data. Bounce rate only makes sense for landing pages and so it's set to 0 for non-landing pages which lowering my average.
    – Dominic Woodman
    Commented Dec 8, 2014 at 12:05
  • That's great it worked, however I was thinking about the way to calculate that averages and they still doesn't make much sense as different types of user have different weight in the data calculation, but that's another conversation. I'm glad it helped!
    – nunorbatista
    Commented Dec 8, 2014 at 12:14
1

Solved it! (or at least I think I did)

Bounce Rate: The percentage of times someone arrives, reads and then leaves. It's only applicable if you landed on the page.

I was downloading data for all my pages, however bounce rate is only applicable for landing pages. It's set to 0 for non-landing pages.

This means when I average bounce rate using all my pages, my average is driven down by a large number of 0's because not all my pages are landed on so they automatically have a bounce rate of 0.

By downloading data for the Landing Page report (428 rows as opposed to 1700), I get an average in Excel 59% compared to 53% in GA. This difference is far more reasonable and could be explained by how GA samples to calculate the bounce rate.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.