在BigQuery中基于GA360数据统计仅访问project1的会话数

看来你需要精准筛选出只访问了project1、没有任何其他项目命中记录的会话，这在多域名对应多项目的GA360数据场景里，得先做会话级的项目聚合再过滤，我来帮你完善并解释完整的查询语句：

首先，我们需要先聚合每个会话中出现的所有项目，再筛选出仅包含project1的会话，最后按日期统计数量。以下是完整的可执行查询：

WITH session_projects AS (
  SELECT
    date,
    fullVisitorId,
    visitId,
    totals.visits,
    -- 收集会话内所有非空的唯一项目名称
    ARRAY_AGG(DISTINCT hits.customDimensions.value IGNORE NULLS) AS project_list
  FROM
    `your-ga360-dataset.ga_sessions_*`
  CROSS JOIN UNNEST(hits) AS hits
  CROSS JOIN UNNEST(hits.customDimensions) AS customDimensions
  WHERE
    -- 定位到索引为9的自定义维度（项目名称）
    customDimensions.index = 9
    -- 可选：添加日期范围过滤，比如查询近30天数据
    AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
  GROUP BY
    date, fullVisitorId, visitId, totals.visits
),
filtered_sessions AS (
  SELECT
    date,
    totals.visits,
    'project1' AS project
  FROM
    session_projects
  WHERE
    -- 确保会话内只有project1这一个项目，无其他项目命中
    ARRAY_LENGTH(project_list) = 1
    AND project_list[OFFSET(0)] = 'project1'
)
SELECT
  date,
  SUM(visits) AS visits,
  project
FROM
  filtered_sessions
GROUP BY
  date, project
ORDER BY
  date DESC;

关键逻辑拆解： #

• session_projects 临时表：先展开GA数据中的hits和对应的customDimensions数组，筛选出索引为9的项目维度记录，再按会话（fullVisitorId+visitId）聚合，把该会话下出现的所有不同项目收集到project_list数组中。
• filtered_sessions 临时表：筛选出数组长度为1且唯一元素是project1的会话，这样就确保这些会话全程只有project1的命中记录，没有其他项目的访问痕迹。
• 最终统计：按日期和项目汇总会话数，SUM(visits)等价于统计会话数量，因为每个会话的totals.visits值固定为1。

实用提示： #

• 记得把your-ga360-dataset.ga_sessions_*替换成你实际的GA360数据集和表前缀。
• 如果某些命中没有设置索引9的自定义维度，IGNORE NULLS会自动忽略这些空值，避免干扰项目集合的判断。
• 如果你需要统计用户数而非会话数，可以把最终统计里的SUM(visits)改成COUNT(DISTINCT fullVisitorId)。

内容的提问来源于stack exchange，提问作者Greenleaf