使用BigQuery进行网站搜索分析

Question

我是BigQuery导出模式的新手，我正在尝试分析电子商务网站中的网站搜索模式。

一旦有人使用网站中的搜索栏，我想得到每个会话的步骤。我想检索使用的关键字和URL(a.k.a=searchPath、previousSearchPath和nextSearchPath(。

在这里，我想检查一下：

• 当(n次点击/互动后(用户考虑在我们的网站中搜索时
• 他/她搜索前访问的最后一个url
• 搜索后他访问的结果页面
• 以下操作

到目前为止，我收到了这个查询。但是，prev_page_path/step/nextStep与某些逻辑不匹配。我获得了一些类似的东西：

• 产品列表页面/
• 产品列表页/2
• 产品列表页/3

SELECT
CONCAT(fullVisitorId, CAST(visitStartTime AS STRING)) AS sessionId,
IF(cd.index=7,cd.value,NULL) AS customDimension7,
IF(cd.index=179,cd.value,NULL) AS customDimension179,
h.contentGroup.contentGroup2 AS CG,
h.hitNumber AS hitNumber,
h.page.searchKeyword AS keyword,
LAG(h.page.pagePath, 1) OVER (PARTITION BY CONCAT(fullVisitorId, CAST(visitStartTime AS STRING)) ORDER BY h.hitNumber) AS prev_page_path,
h.page.pagePath AS step,
ROW_NUMBER() OVER (PARTITION BY CONCAT(fullVisitorId, CAST(visitStartTime AS STRING)) ORDER BY hitNumber ASC) AS rowNumber,
LEAD(h.page.pagePath, 1) OVER (PARTITION BY CONCAT(fullVisitorId, CAST(visitStartTime AS STRING)) ORDER BY hitNumber ASC) AS nextStep
FROM
`xxx-xx-xxx.xxxxxxxxxxxx.ga_sessions_*`,
UNNEST(hits) AS h,
UNNEST(h.customDimensions) AS cd
WHERE
_TABLE_SUFFIX = FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND cd.index = 12
AND h.page.searchKeyword IS NOT NULL
AND cd.value >= '1'
LIMIT
100

Answer 1

我终于添加了一个用于网站搜索跟踪的事件。因此，我现在有了触发该事件的会话，以及使用hit.page.searchKeywords的结果和计算所需度量所需的所有其他内容。

WITH event AS (
SELECT
CONCAT(fullVisitorId, visitStartTime) as sessionId,
CASE WHEN hit.eventInfo.eventAction= 'sitesearch' THEN hit.page.pagePath ELSE NULL END AS pagepath_search,
FROM `project.dataset.ga_sessions_*` AS ga,  UNNEST(hits) AS hit
WHERE _TABLE_SUFFIX = 'date'
),
keyword AS (
SELECT 
CONCAT(fullVisitorId, visitStartTime) as sessionId,
hit.page.searchKeyword as keyword,
hit.page.pagePath AS pagepath_result,
FROM `project.dataset.ga_sessions_*` AS ga,  UNNEST(hits) AS hit
WHERE _TABLE_SUFFIX = 'date'
)
SELECT 
keyword.keyword, 
evento.pagepath_search, 
COUNT(DISTINCT keywords.sessionId) AS total_sessions
FROM
event INNER JOIN keyword ON event.sessionId = keyword.sessionId

我一直在学习这方面的知识，所以，如果你知道一些可以更好的东西，请打电话给我！