提问人：Shewholovesdata 提问时间：11/10/2023 更新时间：11/15/2023 访问量：29
需要帮助在 BigQuery 中重新创建 UA 高级细分受众群
Need help recreating a UA advanced segment in BigQuery
问：
我目前正在尝试将 UA 的高级段重新创建到 bigquery 中，但遇到了使数字匹配的问题。
我需要从曾经完成过类别为“预订”的活动的用户中排除所有会话，以及来自美国以外的所有会话。我当前的查询使我接近 looker 仪表板中的数字，但它比应有的要少。
这是我当前的代码：
WITH Excluded_Users AS ( SELECT visitId AS exclusion_id FROM my_data.ga_sessions_*, UNNEST(hits) AS hits WHERE hits.eventInfo.eventCategory = 'Booking' )
SELECT CONCAT( FORMAT("%04d", EXTRACT(YEAR FROM PARSE_DATE("%Y%m%d", date))), '-', FORMAT("%02d", EXTRACT(MONTH FROM PARSE_DATE("%Y%m%d", date))) ) AS year_month,COUNT(DISTINCT IF(totals.visits=1, CONCAT(fullVisitorId, CAST(visitId AS STRING)), NULL) ) as sessions,
FROM my_data.ga_sessions_* s
LEFT JOIN Excluded_Users e ON s.visitId = e.exclusion_id
WHERE e.exclusion_id IS NULL AND s.totals.visits = 1 AND geoNetwork.Country = 'United States'
GROUP BY 1
答：
1赞
Martin Weitzmann
11/14/2023
#1
有几件事：
visitId只是会话的时间戳（忽略午夜拆分，不像visitStartTime)- 我认为 GA UA 使用 + 来计算会话
fullvisitoridvisitstarttime - 仅在必要时交叉联接，使用子查询节省一些资源
hits - 您没有从 UA 共享您的区段设置，但我们希望我们能:)解决它
SELECT
CONCAT( FORMAT("%04d", EXTRACT(YEAR FROM PARSE_DATE("%Y%m%d", date))), '-', FORMAT("%02d", EXTRACT(MONTH FROM PARSE_DATE("%Y%m%d", date))) ) AS year_month,
COUNT(1) as sessions,
-- no need to count(distinct fullvisitorid||visitstarttime) since every row is a session
FROM `my_data.ga_sessions_*` s
WHERE
NOT exists(select 1 from unnest(hits) where eventInfo.eventCategory = 'Booking')
AND s.totals.visits = 1
AND geoNetwork.Country = 'United States'
GROUP BY 1
评论
0赞
Shewholovesdata
11/17/2023
谢谢！！现在，如果我想包含其他指标，如网页浏览量和目标完成情况，该怎么办？我缩短了示例的查询，但我这样构建它的原因是我需要包含其他自定义指标。
0赞
Martin Weitzmann
11/17/2023
sum(totals.pageviews)应该做第一个技巧。对于目标完成，您可以打开另一个问题......这取决于目标的类型。
评论