BigQuery Google Analytics:访问一组页面或具有特定自定义维度值的会话数

Question

使用Google BigQuery中的Google Analytics数据，我试图重新创建GA片段并提取会话等基本指标。分段定义为：自定义尺寸A=1或5或自定义维度B具有值或页面=页面A或Page=pageB

以下代码适用于这两个自定义尺寸条件。一旦我添加了页面部分，结果总是一天中的所有会话。

SELECT 
COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitStartTime AS STRING))) AS Sessions
FROM
`123456789.ga_sessions_20200201` as Data,
unnest(hits) as hits
WHERE totals.visits = 1
-- custom dimension A
and (
(SELECT
value
FROM
UNNEST(hits.customDimensions)
WHERE
index = 9
GROUP BY
1) is not null
-- custom dimension B
or (SELECT
value
FROM
UNNEST(hits.customDimensions)
WHERE
index = 10
GROUP BY
1) in ('1','5') 
-- Page
or Exists(Select
hits.page.pagePath AS Page
FROM
`123456789.ga_sessions_20200201` ,
unnest(hits) as hits
Where totals.visits = 1
AND hits.type = 'PAGE'
and hits.page.pagePath in ('pageA','pageB'))
)

Answer 1

您的错误是，您在第三个条件中使用了ga_sessions表。因此，它扫描所有表，exists条件返回True。因此，所有的行都是如此。

此外，你不必加入unnest(点击(。它为每个会话创建多行。如果在不连接嵌套命中的情况下处理它，则每个会话都将有一行。所以，计算它们会更容易。

我还更新了它，简化了查询，所以我认为这将为您的数据集中提供所需的内容。

SELECT 
COUNT(*) AS Sessions
FROM
`123456789.ga_sessions_20200201` as Data
WHERE totals.visits = 1
and exists (
SELECT
1
FROM
UNNEST(hits) as hit
WHERE
EXISTS (select 1 from unnest(hit.customDimensions) where index = 9 and value is not null) -- custom dimension A
OR EXISTS (select 1 from unnest(hit.customDimensions) where index = 10 and value in ('1', '5')) -- custom dimension B
OR (hit.type = 'PAGE' and hit.page.PagePath in ('pageA', 'pageB')) -- Page
)