在 Bigquery 中查找用户访问主页后的第二页

Question

我想知道用户在 BigQuery 中登陆主页后访问了哪个页面。

以下是我到目前为止想到的查询，但是当我查看第二页(从[行为]-->[网站内容]-->[所有页面]-->主要维度：着陆页，次要维度：第二页(时，返回的结果与Google Analytics不一致。

但是，当我查看下一页路径(从[网站内容]-->[所有页面]-->主要维度：着陆页，次要维度：下一页路径(时，查询结果匹配。

多篇文章说使用[第二页]比[下一页路径]更合适。

-- Total pageviews by pagepath after landing homepage
#standardSQL
SELECT
next_page.pagePath AS pagePath,
COUNT(*) as pageviews
FROM
(
SELECT
CONCAT(fullVisitorId, ".", CAST(visitId AS STRING)) AS session_id,
hits.page.pagePath AS pagePath,
hits.hitNumber AS hitNumber,
hits.type AS type
FROM
`GA_data.ga_sessions_*`,
UNNEST(hits) as hits
WHERE
_TABLE_SUFFIX BETWEEN '20190814'
AND '20191008'
AND hits.type = 'PAGE'
AND hits.page.pagePath = '/***/' -- Landing page URL
AND hits.isEntrance = TRUE
AND totals.visits = 1
) AS landing_hp
INNER JOIN (
SELECT
CONCAT(fullVisitorId, ".", CAST(visitId AS STRING)) AS session_id,
hits.page.pagePath AS pagePath,
hits.hitNumber AS hitNumber,
hits.type AS type
FROM
`GA_data.ga_sessions_*`,
UNNEST(hits) as hits
WHERE
_TABLE_SUFFIX BETWEEN '20190814'
AND '20191008'
AND hits.type = 'PAGE'
AND hits.isEntrance IS NULL
AND totals.visits = 1
) AS next_page ON landing_hp.session_id = next_page.session_id
WHERE
landing_hp.hitNumber < next_page.hitNumber
GROUP BY
pagePath
ORDER BY
pageviews DESC 

任何人都可以告诉我为什么会发生这种情况以及我应该使用什么查询？

Answer 1

您的第二个表不一定返回第二页，不是吗？对于所涉及的所有连接，以这种方式执行此操作也有点和低效。

最好切断所有连接并使用子查询：

SELECT 
fullvisitorid -- identify user
,visitstarttime -- identify session per user
-- visitid is timestamp of pre-midnight session
,visitstarttime<>visitid AS isMidnightSplitSession 
-- get hitnumber and pagepath from hits where the type is not event 
-- limit to one while sorting by hitnumber - offset 1 to get second page
,(SELECT AS STRUCT hitnumber, page.pagePath 
FROM UNNEST(hits) 
WHERE type<>'EVENT' 
ORDER BY hitnumber ASC 
LIMIT 1 OFFSET 1) AS secondPage
,(SELECT AS STRUCT hitnumber, page.pagePath FROM UNNEST(hits) 
WHERE type<>'EVENT' ORDER BY hitnumber ASC LIMIT 1 OFFSET 2) AS thirdPage
-- no need to left join with all those arrays and bloat up the table
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` t 
-- check that first page = '/home'
WHERE (SELECT page.pagePath FROM UNNEST(hits) WHERE isEntrance=true) = '/home'
and totals.pageviews>1 -- for testing purpose
LIMIT 1000