Compute Bounce Rate

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