在BigQuery中定义Analytics目标：如何获取目标转化与名称？求查询示例

嘿，我来帮你搞定这个问题～ 其实Google Analytics（不管是UA还是GA4）的目标是在GA后台界面创建的，但当数据同步到BigQuery后，BQ并不会自动把目标名称和转化数据绑定在一起——所以我们需要用SQL逻辑来“自定义”目标的匹配规则，把GA里的目标条件映射到BQ的数据上。下面分两种情况给你具体操作指南和查询示例：

一、如果是Universal Analytics（UA）数据 #

UA导出到BQ的表中，转化数据存在hits.goalCompletions字段，每个目标对应一个唯一的goalNumber。你需要先在GA后台记下每个目标ID对应的名称和触发规则，然后在SQL里用CASE语句把ID映射成目标名称。

查询示例：按日期和目标名称统计转化次数 #

SELECT
  date,
  -- 把目标ID映射成你自定义的目标名称
  CASE
    WHEN hits.goalNumber = 1 THEN '表单提交'
    WHEN hits.goalNumber = 2 THEN '感谢页访问'
    WHEN hits.goalNumber = 3 THEN '白皮书下载'
    ELSE '其他目标'
  END AS goal_name,
  SUM(hits.goalCompletions) AS total_conversions,
  COUNT(DISTINCT fullVisitorId) AS converting_users
FROM
  `your-project-id.analytics_123456789.ga_sessions_*`,
  UNNEST(hits) AS hits
WHERE
  _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
  AND hits.goalCompletions > 0 -- 只统计有转化的记录
GROUP BY
  date,
  goal_name
ORDER BY
  date,
  total_conversions DESC

二、如果是GA4数据 #

GA4的目标大多基于事件触发，所以你需要先明确GA后台里每个目标对应的事件名称（或事件参数），然后在BQ中通过筛选事件来匹配目标，再给目标命名。

查询示例：按日期和目标名称统计转化次数 #

SELECT
  event_date AS date,
  -- 根据事件规则映射目标名称
  CASE
    WHEN event_name = 'form_submit' THEN '表单提交'
    WHEN event_name = 'page_view' AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') LIKE '%/thank-you%' THEN '感谢页访问'
    WHEN event_name = 'file_download' AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'file_name') = 'whitepaper.pdf' THEN '白皮书下载'
    ELSE '其他目标'
  END AS goal_name,
  COUNT(*) AS total_conversions,
  COUNT(DISTINCT user_pseudo_id) AS converting_users
FROM
  `your-project-id.analytics_234567890.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
  -- 只包含能匹配到目标的事件
  AND (
    event_name = 'form_submit'
    OR (event_name = 'page_view' AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') LIKE '%/thank-you%')
    OR (event_name = 'file_download' AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'file_name') = 'whitepaper.pdf')
  )
GROUP BY
  date,
  goal_name
ORDER BY
  date,
  total_conversions DESC

关键步骤总结 #

1. 先确认GA后台的目标规则：比如UA的目标ID、GA4的触发事件/参数条件，这是你在BQ中定义目标的基础
2. 用CASE语句做映射：把BQ里的原始数据字段（如goalNumber、event_name、event_params）和目标名称关联起来
3. 按需聚合指标：根据需求统计转化次数、转化用户数、转化路径等

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