People Who Viewed One Page Viewed Which Other Pages How Many times?

May 12, 2016
By Bounteous
SELECT
  sp as pageA,
  pp as pageB,
  COUNT(*) AS crossCount
FROM (
  SELECT
    sess.fullvisitorid AS sfvi,
    pages.fullVisitorid AS pfvi,
    sess.hits.page.pagePath AS sp,
    pages.hits.page.pagePath AS pp
  FROM
    FLATTEN([google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910], hits) AS sess
  CROSS JOIN
    FLATTEN([google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910], hits) AS pages
  WHERE
    -- ensures each combo is only counted a single time
    pages.fullvisitorid < sess.fullvisitorid
    AND pages.hits.page.pagePath < sess.hits.page.pagePath
  GROUP BY sfvi, pfvi, sp, pp )
GROUP BY pageA, pageB
ORDER BY pageA, crossCount DESC