Mysql Query to calculate Bounce Rate

Question

We need to calculate how many users bounce in our websites. Given a table that contains the GUID (User-Id), Hostname and Path for each Pageview. If there is only 1 GUID per Hostname then it is considered as bounce.

Table Pageviews:

- GUID

- Hostname

- Path

I was able to do this query but I think it might be improved. Specially in terms of performance.

SELECT 1,  (Bounces / All) * 100 AS `Bounce Rate` From (

SELECT

    count(*) AS All,

    (

        SELECT count(*)

        FROM

        (

            SELECT GUID

            FROM pageviews

            GROUP BY GUID

            HAVING count(GUID) = 1

        )

    ) AS Bounces

    FROM pageviews

)

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

This feels really ugly... but I think it's semantically correct:

SELECT bounces,
       uniqueUsers,
       bounces / uniqueUsers * 100 AS `global bounce rate`
FROM (
  SELECT
        (SELECT count(1)
         FROM (
                SELECT guid, hostname
                FROM PageViews
                GROUP BY guid, hostname
                HAVING count(1) = 1
              ) bd
        ) bounces,
        (SELECT count(1)
         FROM (
                SELECT guid, hostname
                FROM PageViews
                GROUP BY guid, hostname
              ) ud
        ) uniqueUsers
) data

Example data:

guid   hostname   path
----   --------   ----
1      host1      irrelevant  << 1st visit
1      host1      irrelevant  << 2nd visit => not a bounce
1      host1      irrelevant  << 3rd visit => still only counts as 1
1      host1      irrelevant  << 4th visit
1      host1      irrelevant  << 5th visit
1      host2      irrelevant  << bounce
1      host3      irrelevant  << bounce
2      host2      irrelevant  << bounce
3      host4      irrelevant  << bounce

Query result:

bounces   uniqueUsers   global bounce rate
-------   -----------   ------------------
4         5             80.0000

Note that all 5 guid 1 hits against host1 only counts as 1 unique user, but guid 1 on host2 counts as a second (I think this is the desired logic).

Mysql Query to calculate Bounce Rate

Sign up