取消嵌套匹配和取消嵌套会话范围的自定义维度 BigQuery 代码过滤条件

Question

我正在尝试根据具有某些自定义维度值的用户过滤漏斗。遗憾的是，有问题的自定义维度是会话范围的，而不是基于命中的，所以我不能在这个特定的查询中使用 hits.customDimensions。做到这一点并达到预期结果的最佳方法是什么？ 查找我到目前为止的进度：

#standardSQL 选择  SUM((从 UNNEST(hits) 中选择 1，其中 page.pagePath = '/one - Page' 限制 1)) One_Page，  SUM((从存在的地方从 UNNEST(命中)中选择 1(从 UNNEST(命中) 中选择 1，其中 PAGE.PAGEPath = '/one - Page') 和 page.pagePath = '/two - Page' 限制 1)) Two_Page，  SUM((从存在的地方的UNNEST(命中)中选择1(从UNNEST(命中)中选择1，其中PAGE.PAGEPath = '/one - Page')和page.pagePath = '/three - Page' LIMIT 1)) Three_Page，  SUM((从存在的地方选择1(从UNNEST(命中)中选择1，其中PAGE.PAGEPath = '/one - Page')和page.pagePath = '/four - Page' LIMIT 1)) Four_Page 从"xxxxxxx.ga_sessions_*"， 解巢(命中)为 h， UNNEST(customDimensions) AS cusDim 哪里 _TABLE_SUFFIX介于"20190320"和"20190323"之间 和 h.hitNumber = 1 AND cusDim.index = 6 和 cusDim.value IN ('60'，'70)

Answer 1

使用自定义维度进行细分

您可以根据自定义维度中的条件过滤会话。只需编写一个子查询来计算感兴趣的案例并设置为">0"。示例数据示例：

SELECT
fullvisitorid,
visitstarttime,
customdimensions
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_20170505` t
WHERE
-- there should be at least one case with index=4 and value='EMEA' ... you can use your index and desired value
-- unnest() turns customdimensions into table format, so we can apply SQL to this array
(select count(1)>0 from unnest(customdimensions) where index=4 and value='EMEA')
limit 100

注释 WHERE 语句以查看所有数据。

漏斗

首先，您可能想要大致了解命中数组中发生的情况：

SELECT
fullvisitorid,
visitstarttime,
-- get an overview over relevant hits data
-- select as struct feeds hits fields into a new array created by array()-function
ARRAY(select as struct hitnumber, page from unnest(hits) where type='PAGE') hits
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_20170505` t
WHERE
(select count(1)>0 from unnest(customdimensions) where index=4 and value='EMEA')
and totals.pageviews>3
limit 100

现在您已经确定数据有意义，您可以创建一个包含相关步骤命中数的漏斗数组：

SELECT
fullvisitorid,
visitstarttime,
-- create array with relevant info
-- cross join hit numbers from step pages to get all combinations so that we can check later which came after the other
ARRAY(
select as struct * from
(select hitnumber as step1 from unnest(hits) where type='PAGE' and page.pagePath='/home') left join
(select hitnumber as step2 from unnest(hits) where type='PAGE' and page.pagePath like '/google+redesign/%') on true left join
(select hitnumber as step3 from unnest(hits) where type='PAGE' and page.pagePath='/basket.html') on true
) AS funnel
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_20170505` t
WHERE
(select count(1)>0 from unnest(customdimensions) where index=4 and value='EMEA')
and totals.pageviews>3
limit 100

为了更清楚起见，将其放入 WITH 语句中，并通过总结相应的情况来运行分析：

WITH f AS (
SELECT
fullvisitorid,
visitstarttime,
totals.visits,
-- create array with relevant info
-- cross join hit numbers from step pages to get all combinations so that we can check later which came after the other
ARRAY(
select as struct * from
(select hitnumber as step1 from unnest(hits) where type='PAGE' and page.pagePath='/home') left join
(select hitnumber as step2 from unnest(hits) where type='PAGE' and page.pagePath like '/google+redesign/%') on true left join
(select hitnumber as step3 from unnest(hits) where type='PAGE' and page.pagePath='/basket.html') on true
) AS funnel
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_20170505` t
WHERE
(select count(1)>0 from unnest(customdimensions) where index=4 and value='EMEA')
and totals.pageviews>3
)
SELECT 
COUNT(DISTINCT fullvisitorid) as users,
SUM(visits) as allSessions,
SUM( IF(array_length(funnel)>0,visits,0) ) sessionsWithFunnelPages,
SUM( IF( (select count(1)>0 from unnest(funnel) where step1 is not null ) ,visits,0) ) sessionsWithStep1,
SUM( IF( (select count(1)>0 from unnest(funnel) where step1 is not null and step1<step2 ) ,visits,0) ) sessionsFunnelToStep2,
SUM( IF( (select count(1)>0 from unnest(funnel) where step1 is not null and step1<step2 and step2<step3 and step1<step3) ,visits,0) ) sessionsFunnelToStep3
FROM f

请在使用前进行测试。