Extract Dimensions from Other Fields

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