google-analytics - 使用 hits.hitNumber 获取特定命中后发生的命中

Question

我想返回访问者在 session 期间点击特定页面后发生的所有添加到购物车事件。

使用演示商店帐户中的 google_analytics_sample 作为引用并使用以下查询

   SELECT
    hits.eCommerceAction.action_type,
    hits.hitNumber
    FROM
    `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` AS GA, UNNEST(GA.hits) AS hits
where 
hits.eCommerceAction.action_type = "2"

我需要编写什么查询才能仅返回添加到购物车，即使在特定页面查看命中后发生？比方说 REGEXP_CONTAINS(hits.page.pagePath, r'asearch')例如。

基本上我需要做这样的事情

where (hits.eCommerceAction.action_type = 2 and hits.hitNumber) > REGEXP_CONTAINS(hits.page.pagePath, r'asearch') and hits.hitNumber)

但我不确定我应该如何用我的查询来表达这一点。

Answer 1

我认为在子查询中操作而不是与 hits 数组交叉连接是有意义的。这样可以节省资源并保持数组的自然边界。

在这里的解决方案中，我从想要的命中子查询一些信息，并通过在同一个数组上的嵌套子查询来限制它们:

SELECT
  fullvisitorid, -- identify user
  visitstarttime, -- identify session per user
  -- feed output of subquery back into an array
  array(SELECT AS STRUCT
    hitNumber, page.pagePath, eventInfo.eventAction
  FROM GA.hits 
  WHERE
    -- add to cart
    eCommerceAction.action_type = "3"
    -- and its hitnumber must be over the min hitnumber of 'asearch'
    AND hitnumber > (SELECT MIN(hitnumber) FROM GA.hits WHERE REGEXP_CONTAINS(page.pagePath, r'asearch')) 
  ORDER BY hitnumber) AS myHits
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` AS GA
WHERE (SELECT COUNT(1)>0 FROM GA.hits WHERE eCommerceAction.action_type = "3")
  AND (SELECT COUNT(1)>0 FROM GA.hits WHERE REGEXP_CONTAINS(page.pagePath, r'asearch') )
LIMIT 1000

输出是 3 个字段:fullvisitorid + visitstarttime 用于标识 session 以及包含所需命中的数组。