Mik Mik - 9 months ago 45
MySQL Question

Mysql Query to calculate Bounce Rate

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
)

Answer Source

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).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download