People Who Viewed One Page Viewed Which Other Pages How Many times?
May 12, 2016
|
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