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
)
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
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