Compute Bounce Rate
June 26, 2016
|
SELECT
pagePathLevel1,
ROUND(SUM(totals.bounces) / SUM(totals.visits), 2) AS bounceRate
FROM (
SELECT
totals.visits,
-- If no session bounced, then SUM of nulls is null, not 0
IF(totals.bounces IS NULL, 0, 1) AS totals.bounces,
NTH(1, SPLIT(hits.page.pagePath, '/')) AS pagePathLevel1,
FROM
-- We need to explicitly FLATTEN otherwise SPLIT won't behave as expected
FLATTEN([google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910], hits.page.pagePath))
GROUP BY
pagePathLevel1