google-analytics - BigQuery GA Open Funnel旧版SQL:排除查看过某些页面的 session

Question

我正在尝试在BigQuery中重新创建GA渠道，此打开的渠道将排除查看过某些页面的会话，我尝试使用以下方法：AND NOT REGEXP_MATCH，NOT IN，但仍无法正常工作，我仍在获取会话查看了我要排除的页面。

如果可能的话，我也想使其成为一个封闭的漏斗，此代码返回一个开放的漏斗。

另外，是否有更好的方法在Standard SQL中编写此查询？

需要帮助。谢谢。

选择COUNT（s0.firstHit）AS _test_your_details，
SUM（s0.exit）AS _test_your_details_exits，
COUNT（s1.firstHit）AS _test_additional_new_details，
SUM（s1.exit）AS _test_additional_new_details_exits，
COUNT（s2.firstHit）AS _test_new_dress，
SUM（s2.exit）AS _test_new_dress_exits，
COUNT（s3.firstHit）AS _test_test_details，
SUM（s3.exit）AS _test_test_details_exits，
COUNT（s4.firstHit）AS _test_cover_for_the_test，
SUM（s4.exit）AS _test_cover_for_the_test_exits，
COUNT（s5.firstHit）AS _test_your_order，
SUM（s5.exit）AS _test_your_order_exits
从
（选择s0.fullVisitorId，
s0.visitId，
s0.firstHit，
s0.exit，
s1.firstHit，
s1.exit，
s2.firstHit，
s2.exit，
s3.firstHit，
s3.exit，
s4.firstHit，
s4.exit，
s5.firstHit，
s5.exit
从
（选择s0.fullVisitorId，
s0.visitId，
s0.firstHit，
s0.exit，
s1.firstHit，
s1.exit，
s2.firstHit，
s2.exit，
s3.firstHit，
s3.exit，
s4.firstHit，
s4.exit
从
（选择s0.fullVisitorId，
s0.visitId，
s0.firstHit，
s0.exit，
s1.firstHit，
s1.exit，
s2.firstHit，
s2.exit，
s3.firstHit，
s3.exit
从
（选择s0.fullVisitorId，
s0.visitId，
s0.firstHit，
s0.exit，
s1.firstHit，
s1.exit，
s2.firstHit，
s2.exit
从
（选择s0.fullVisitorId，
s0.visitId，
s0.firstHit，
s0.exit，
s1.firstHit，
s1.exit
从
（选择fullVisitorId，
visitId，
MIN（hits.hitNumber）为firstHit，
MAX（IF（hits.isExit，1，0））AS出口
从TABLE_DATE_RANGE（[xxxxxxxx.ga_sessions _]，TIMESTAMP（'2018-11-01'），TIMESTAMP（'2018-11-30'））
在哪里REGEXP_MATCH（hits.page.pagePath，'/ test-您的详细信息'）
AND totals.visits = 1
AND channelGrouping不喜欢'％organic％'
AND hits.page.pagePath不在（'/ test-其他测试详细信息'，'/ test-测试服装'，'/ test-封面服装'）
AND NOT REGEXP_MATCH（hits.page.pagePath，r“ ^ /（测试-其他测试详细信息|测试-测试着装|测试-封面着装）”）
GROUP BY fullVisitorId，
visitId）s0
全面外联
（选择fullVisitorId，
visitId，
MIN（hits.hitNumber）为firstHit，
MAX（IF（hits.isExit，1，0））AS出口
从TABLE_DATE_RANGE（[xxxxxxxx.ga_sessions _]，TIMESTAMP（'2018-11-01'），TIMESTAMP（'2018-11-30'））
在哪里REGEXP_MATCH（hits.page.pagePath，'/ test-其他新详细信息'）
AND totals.visits = 1
AND channelGrouping不喜欢'％organic％'
GROUP BY fullVisitorId，
visitId）s1 ON s0.fullVisitorId = s1.fullVisitorId
AND s0.visitId = s1.visitId）s01
全面外联
（选择fullVisitorId，
visitId，
MIN（hits.hitNumber）为firstHit，
MAX（IF（hits.isExit，1，0））AS出口
从TABLE_DATE_RANGE（[xxxxxxxx.ga_sessions _]，TIMESTAMP（'2018-11-01'），TIMESTAMP（'2018-11-30'））
在哪里REGEXP_MATCH（hits.page.pagePath，“ / test-new dress”）
AND totals.visits = 1
AND channelGrouping不喜欢'％organic％'
GROUP BY fullVisitorId，
visitId）s2 ON s0.fullVisitorId = s2.fullVisitorId
AND s0.visitId = s2.visitId）s012
全面外联
（选择fullVisitorId，
visitId，
MIN（hits.hitNumber）为firstHit，
MAX（IF（hits.isExit，1，0））AS出口
从TABLE_DATE_RANGE（[xxxxxxxx.ga_sessions _]，TIMESTAMP（'2018-11-01'），TIMESTAMP（'2018-11-30'））
在哪里REGEXP_MATCH（hits.page.pagePath，'/ test-测试详细信息'）
AND totals.visits = 1
AND channelGrouping不喜欢'％organic％'
GROUP BY fullVisitorId，
visitId）s3 ON s0.fullVisitorId = s3.fullVisitorId
AND s0.visitId = s3.visitId）s0123
全面外联
（选择fullVisitorId，
visitId，
MIN（hits.hitNumber）为firstHit，
MAX（IF（hits.isExit，1，0））AS出口
从TABLE_DATE_RANGE（[xxxxxxxx.ga_sessions _]，TIMESTAMP（'2018-11-01'），TIMESTAMP（'2018-11-30'））
在哪里REGEXP_MATCH（hits.page.pagePath，'/ test-测试封面'）
AND totals.visits = 1
AND channelGrouping不喜欢'％organic％'
AND hits.page.pagePath不在（'/ test-其他测试详细信息'，'/ test-测试服装'）中
GROUP BY fullVisitorId，
visitId）s4 ON s0.fullVisitorId = s4.fullVisitorId
AND s0.visitId = s4.visitId）s01234
全面外联
（选择fullVisitorId，
visitId，
MIN（hits.hitNumber）为firstHit，
MAX（IF（hits.isExit，1，0））AS出口
从TABLE_DATE_RANGE（[xxxxxxxx.ga_sessions _]，TIMESTAMP（'2018-11-01'），TIMESTAMP（'2018-11-30'））
在REGEXP_MATCH（hits.page.pagePath，'/ test-您的订单'）
AND totals.visits = 1
AND channelGrouping不喜欢'％organic％'
AND hits.page.pagePath不在（'/ test-其他测试详细信息'，'/ test-测试服装'）中
AND NOT REGEXP_MATCH（hits.page.pagePath，r“ ^ /（测试-其他测试详细信息|测试-测试着装|测试-封面着装）”）
GROUP BY fullVisitorId，
visitId）s5 ON s0.fullVisitorId = s5.fullVisitorId
AND s0.visitId = s5.visitId）s012345

Answer 1

在标准SQL中，您可以在hits上编写一个简单的子查询以进行检查。例如：

SELECT 
  fullvisitorid, visitstarttime,
  ARRAY(
    SELECT AS STRUCT hitNumber, type, page FROM t.hits ORDER BY hitNumber
  ) hits
FROM
    `bigquery-public-data.google_analytics_sample.ga_sessions_20161104` t
WHERE 
  -- exclude sessions with pages containing '/asearch.html'
  -- subquery checks for occurences in the whole query and returns boolean TRUE if found 
  -- NOT turns it into FALSE which filters it out
  NOT (SELECT COUNT(1)>0 FROM t.hits WHERE page.pagePath = '/asearch.html')
ORDER BY array_length(hits) DESC
LIMIT 1000

我还编写了一个子查询来显示数组中会话的命中率。
在旧版SQL中，您可以使用 OMIT RECORD IF：

SELECT 
  fullvisitorid, visitstarttime, hits.page.pagePath
FROM
    [bigquery-public-data:google_analytics_sample.ga_sessions_20161104] t
-- OMIT RECORD IF excludes on record level 
-- if dimension is below record level, you need to aggregate (like with WITHIN)
-- in this case I used MAX() to surface any possible TRUE resulting from the comparison
OMIT RECORD IF MAX(hits.page.pagePath = '/asearch.html')
LIMIT 1000

希望有帮助！