基于BigQuery的站点搜索分析

Question

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

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

在这里，我想检查一下：

当用户(在n次点击/交互之后)考虑在我们的网站search

• Following

• 上搜索他/她在搜索

• 之前访问的最后一个url，在 actions之后访问的结果页面

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

• product-list-page/
• product-list-page/2
• product-list-page/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

我一直都在了解这件事，所以，如果你知道更好的事情，就来找我吧！