Create Arbitrary Groupings

SELECT
  hits.page.pagePath,
  CASE 
    WHEN pagePathLevel1 = 'helmets' THEN "Products" 
    WHEN pagePathLevel1 = 'vests' THEN "Products"
    WHEN pagePathLevel1 IN ('basket.html', 'shipping.html', 'billing.html', 'confirm.html') THEN "Checkout"
    WHEN pagePathLevel1 IS NULL OR pagePathLevel1 = 'login.html' THEN "RestOfSite"
    ELSE 'Unknown'
  END AS contentGroup1,
  CASE 
    WHEN pagePathLevel2 = 'foldable.html' THEN "Foldable" 
    WHEN pagePathLevel2 IN ('orange.html', 'yellow.html') THEN "Color"
  END AS contentGroup2
FROM (
  SELECT
    fullVisitorId,
    visitID,
    hits.page.pagePath,
    NTH(1, SPLIT(hits.page.pagePath, '/')) AS pagePathLevel1,
    NTH(2, SPLIT(hits.page.pagePath, '/')) AS pagePathLevel2,
  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 
    hits.page.pagePath, 
    contentGroup1, 
    contentGroup2