时间戳

首页 > 解决方案 > 使用 BigQuery 进行网站搜索分析

google-analytics - 使用 BigQuery 进行网站搜索分析

问题描述

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

一旦有人使用网站中的搜索栏，我想获得每个会话的步骤。我想检索使用的关键字和网址（aka = searchPath、previousSearchPath 和 nextSearchPath）。

对此，我想检查一下：

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

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

标签： google-analyticsgoogle-bigquery

解决方案

我终于设法为网站搜索跟踪添加了一个事件。因此，我现在拥有触发该事件的会话，以及使用 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

我一直在学习这方面的知识，所以如果你知道可以更好的东西，请联系我！

推荐阅读