Marketing

GA4 + BigQuery:用 Claude 把同一套 SQL 变成 10 张营销仪表盘

2025年9月25日

星期二早上 8:14。Slack 里有 5 条未读消息,都是「能帮我搭个仪表盘吗」的变体。8:42 我整理出了清单:渠道分布、漏斗流失、落地页收入、投放效果、设备拆分、新老用户、地理分布、热门内容、多触点路径、互动质量。10 张仪表盘,全部要求「明早之前」。

两年前的我会拒绝,打开 Looker Studio 拖一下午字段。上周我说「可以」,当天下午 4:30 就交付了 10 张能用的仪表盘。诀窍不是魔法。诀窍是:不要再把仪表盘当成资产,把 SQL 当成资产。SQL 写对了,仪表盘只是上色。

这篇文章就是我用的工作流:10 条基础 SQL、给 Claude 把它转成 Looker Studio JSON 的 prompt、两次迭代、4 个坑。如果你写得来基础 SQL、并且 GA4 已经在导出到 BigQuery,你一天就能交付。下面是详细的复盘。

准备工作(15 分钟,不是 15 小时)

你需要三样东西,你大概率都已经有了:

一个开启了 BigQuery 导出的 GA4 属性。 Admin → Property → Product Links → BigQuery Links。标准属性免费。第一次日导出完成后,你的 BigQuery 项目里就会出现 events_* 实时表和 events_* 日表。
BigQuery 访问权限。 GCP 控制台、bq CLI,或者——我真正在用的——Looker Studio 自带的 BigQuery 面板。只要能对数据集跑一句 SELECT 1,就够了。
一个 Claude Pro 或 API 账号。 这次我用的是 Claude Sonnet 4.5,Sonnet 3.5 也能跑。你要做的是生成 JSON,不是写散文,所以模型需要愿意简洁。

我不假设你已经把 GA4 schema 背熟了。必须内化的两件事:GA4 是事件驱动(基于 event),不是会话驱动;每个用户级维度都嵌在一个嵌套结构(struct)里,你必须用 UNNEST 才能读出来。新手最常见的错误是写 WHERE source = 'google',返回零行——因为 source 在 traffic_source 里面,是一个 STRUCT,不是一列。一旦你 UNNEST 它,下面所有 SQL 直接就能跑。把 your-project.analytics_123456789 替换成你真实的项目和属性 ID。

10 条基础 SQL

下面是真正跑过的 SQL。我没有美化。每一张对应一张仪表盘,回答一个问题。我把它们放在项目的同一个 dashboards/ 目录里,文件名和仪表盘名一一对应。

1. 渠道分布(仪表盘:流量从哪来?)

sqlSELECT
  traffic_source.source,
  traffic_source.medium,
  COUNT(DISTINCT user_pseudo_id) AS users,
  COUNT(DISTINCT (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) AS sessions,
  SUM(IF(event_name = 'purchase', (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value'), 0)) AS revenue
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20250901' AND '20250925'
GROUP BY 1, 2
ORDER BY sessions DESC

回答:「上个月的流量和收入,自然、付费、邮件、直达各占多少?」这是你的 CMO 会先打开的一张。

2. 漏斗流失(仪表盘:我们从哪一步开始掉?)

sqlWITH steps AS (
  SELECT user_pseudo_id,
    MAX(IF(event_name = 'page_view', 1, 0)) AS s1,
    MAX(IF(event_name = 'view_item', 1, 0)) AS s2,
    MAX(IF(event_name = 'add_to_cart', 1, 0)) AS s3,
    MAX(IF(event_name = 'begin_checkout', 1, 0)) AS s4,
    MAX(IF(event_name = 'purchase', 1, 0)) AS s5
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20250901' AND '20250925'
  GROUP BY 1
)
SELECT 'Visit' AS step, SUM(s1) AS users UNION ALL
SELECT 'View item', SUM(s2) UNION ALL
SELECT 'Add to cart', SUM(s3) UNION ALL
SELECT 'Begin checkout', SUM(s4) UNION ALL
SELECT 'Purchase', SUM(s5)

回答:「相邻两步之间,我们丢掉了多少用户?」这是你的增长负责人会先打开的一张。对营销人来说,绝对值的柱状图比 Sankey 图更好用——人看得懂那个落差。

3. 落地页收入(仪表盘:哪些落地页真的赚钱?)

sqlSELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS landing_page,
  COUNT(DISTINCT user_pseudo_id) AS users,
  SUM(IF(event_name = 'purchase', 1, 0)) AS purchases,
  SUM(IF(event_name = 'purchase', (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value'), 0)) AS revenue
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20250901' AND '20250925'
  AND event_name IN ('page_view', 'purchase')
GROUP BY 1
HAVING users > 100
ORDER BY revenue DESC
LIMIT 50

回答:「如果只能砍掉一半落地页,留哪些?」users > 100 这个过滤是有意为之——3 个访客产出 $1,200 订单的页面,都是噪声。

4. 投放效果(仪表盘:哪些 UTM 真的在跑量?)

sqlSELECT
  traffic_source.name AS campaign,
  traffic_source.source,
  traffic_source.medium,
  COUNT(DISTINCT user_pseudo_id) AS users,
  SUM(IF(event_name = 'purchase', (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value'), 0)) AS revenue
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20250901' AND '20250925'
  AND traffic_source.name != '(not set)'
GROUP BY 1, 2, 3
ORDER BY revenue DESC

回答:「每个 campaign 名,带来了多少收入?」这是周一早上发给投放团队的那张。(not set) 过滤能把 Google 的噪声切掉。

5. 设备拆分(仪表盘:移动端的坑在哪里?)

sqlSELECT
  device.category,
  device.operating_system,
  device.web_info.browser,
  COUNT(DISTINCT user_pseudo_id) AS users,
  SUM(IF(event_name = 'purchase', (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value'), 0)) AS revenue
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20250901' AND '20250925'
GROUP BY 1, 2, 3

回答:「移动 Safari 和桌面 Chrome 的转化率分别是多少?哪个在拖低 AOV(Average Order Value,客单价)?」在甩锅给设计之前,先看收入列。

6. 新老用户(仪表盘:留存是不是真的?)

sqlWITH first_seen AS (
  SELECT user_pseudo_id, MIN(PARSE_DATE('%Y%m%d', _TABLE_SUFFIX)) AS first_day
  FROM `your-project.analytics_123456789.events_*`
  GROUP BY 1
)
SELECT
  CASE WHEN f.first_day >= DATE_SUB('2025-09-25', INTERVAL 30 DAY) THEN 'New' ELSE 'Returning' END AS user_type,
  COUNT(DISTINCT e.user_pseudo_id) AS users,
  SUM(IF(e.event_name = 'purchase', (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value'), 0)) AS revenue
FROM `your-project.analytics_123456789.events_*` e
JOIN first_seen f USING (user_pseudo_id)
WHERE _TABLE_SUFFIX BETWEEN '20250901' AND '20250925'
GROUP BY 1

回答:「上个月下单的用户里,多少是新客、多少是回头客?」LTV(Lifetime Value,用户生命周期价值)就藏在回头客里。

7. 地理分布(仪表盘:钱在世界上哪些地方?)

sqlSELECT
  geo.country,
  geo.city,
  COUNT(DISTINCT user_pseudo_id) AS users,
  SUM(IF(event_name = 'purchase', (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value'), 0)) AS revenue
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20250901' AND '20250925'
GROUP BY 1, 2
HAVING users > 50
ORDER BY revenue DESC
LIMIT 200

回答:「我们在哪些城市是赚钱的?哪些城市是烧着付费预算去够的?」这里用地图图可以,但能排序的表格更实用——人会滚动表格,不会去拖地图。

8. 热门内容(仪表盘:哪些页面最能留人?)

sqlSELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page,
  COUNT(DISTINCT user_pseudo_id) AS readers,
  AVG(IF(event_name = 'page_view', (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec'), 0)) AS avg_engagement_ms
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20250901' AND '20250925'
  AND event_name = 'page_view'
GROUP BY 1
HAVING readers > 200
ORDER BY avg_engagement_ms DESC
LIMIT 50

回答:「哪些页面留住了人?哪些页面 8 秒就被弹走?」这是发给内容团队的那张。

9. 多触点路径(仪表盘:路径长什么样?)

sqlWITH ordered AS (
  SELECT
    user_pseudo_id,
    event_name,
    traffic_source.source,
    ROW_NUMBER() OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS step
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20250901' AND '20250925'
    AND event_name IN ('purchase', 'view_item', 'add_to_cart')
)
SELECT
  STRING_AGG(CONCAT(step, ':', source), ' → ' ORDER BY step) AS path,
  COUNT(*) AS conversions
FROM ordered
GROUP BY user_pseudo_id
HAVING COUNT(*) >= 2

回答:「最终下单的旅程里,每个步骤出现了哪些来源?」这条是 10 条里最贵的——BigQuery 会扫很多字节——所以我一周跑一次,不是每次仪表盘刷新都跑。

10. 互动质量(仪表盘:这些会话是真的吗?)

sqlSELECT
  DATE(TIMESTAMP_MICROS(event_timestamp)) AS day,
  COUNT(DISTINCT user_pseudo_id) AS daily_users,
  COUNT(DISTINCT IF(
    (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') > 10000,
    user_pseudo_id, NULL)) AS engaged_users
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20250901' AND '20250925'
  AND event_name = 'session_start'
GROUP BY 1
ORDER BY 1

回答:「今天的来访者里,有多少真的在某个页面停留超过 10 秒?」互动会话占比超过 50% 算健康。低于 30% 要么是机器人流量、要么是即时跳出、要么是内容问题。

把 SQL 变成仪表盘的 Claude Prompt

这部分花了 30 分钟才调好。第一次尝试给出的 JSON 语法上没问题,但 Looker Studio 默默拒收。第二次产出了每张仪表盘 70 个字段,大部分是垃圾。下面是真正能用的版本。

You are a Looker Studio JSON generator. For each of the 10 SQL queries I will give you, output exactly one JSON object with this shape:

{
  "name": "",
  "title": "",
  "dataSource": {
    "type": "bigquery",
    "projectId": "your-project",
    "datasetId": "analytics_123456789",
    "query": ""
  },
  "fields": [
    {"name": "", "type": "dimension|metric", "dataType": "string|number", "aggregation": null|"SUM|COUNT_DISTINCT|AVG"}
  ],
  "charts": [
    {"id": "chart_1", "type": "table|bar|pie|geo|timeseries", "title": "...", "dimensions": ["..."], "metrics": ["..."]}
  ]
}

Rules:
- Keep the SQL exactly as I gave it, including _TABLE_SUFFIX date filters.
- Every column returned by the SQL must appear in `fields` with the correct dataType.
- Use SUM for revenue, COUNT_DISTINCT for users/sessions, AVG for engagement_time_ms.
- For each dashboard, suggest 2-4 chart types that fit the SQL shape (table + one visualization minimum).
- Output one JSON object per query, in the same order I gave them.
- Do not invent fields the SQL does not return. If a metric is ambiguous, mark it "needs_review".

Here are the 10 SQL queries:

两件事要记住。第一,一次只贴一条 SQL,不要 10 条一起塞,如果你想要干净输出。两种我都试过。一次贴 10 条也能出 10 张仪表盘,但 JSON 会有「漂移」——Claude 会在第 7 张悄悄重命名一个字段,Looker Studio 第 3 张图就渲染成空白。一次一条,10 张仪表盘字段名保持一致,这一点在你之后想跨报表共享过滤器时很重要。

第二,dataSource.query 字段才是承重墙。 Looker Studio 接受在报表定义里直接放一条自定义 BigQuery 查询;这意味着你的仪表盘不是绑在某张物化视图或调度查询表上。代价是加载时间——每次仪表盘刷新都会重跑 SQL,多触点路径(#9)会消耗 BigQuery 扫描字节的计费。10 条里有 9 条没问题。第 9 条改成每天调度一次、把结果落到一张日表里,仪表盘指这张日表。

4 个我踩过的坑

坑 1:实时导出里没有 traffic_source。 第一版渠道分布查询返回零行。原因:GA4 的实时导出(events_intraday_*)不会写入 traffic_source.source/medium/name。我切到日导出,数据就出来了。延迟 ~24 小时,对于每日刷新的仪表盘可以接受。

坑 2:event_params 是一个重复的 STRUCT,不是平铺的列。 这个写法 (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') 看着丑,却是提取单个事件参数的标准写法。你也可以在查询顶部一次性 UNNEST(event_params),然后直接引用 key 和 value.string_value,但子查询的形式能保证每个事件的行数是 1,这对收入加总至关重要。

坑 3:engagement_time_msec 是唯一可靠的互动信号。 我一开始用 user_engagement 事件做互动仪表盘,数据差了三倍。原因:user_engagement 只在互动会话上触发,而 GA4「互动」的定义比大多数营销人预期的 10 秒阈值更严格。page_view 事件里的 engagement_time_msec 才是真相。

坑 4:Looker Studio 的自定义查询数据源是只读的,而且没法作为视图共享。 你不能像普通表连接那样把一个自定义查询数据源发布到团队共享库。如果你想让同事复用这个数据源,得先把查询在 BigQuery 里调度落表,再让 Looker Studio 指这张表。内部团队用,自定义查询更快;客户交付物,先把查询调度起来。

这一天的时间线,给好奇的你

8:14 AM——Slack 消息。建了 dashboards/ 目录,一个 dashboards.sql 文件,装了上面那 10 条 SQL。

9:30 AM——10 条 SQL 全部在公开样例集 bigquery-public-data.ga4_obfuscated_sample_ecommerce 上验证过。形状对,列名符合预期。

10:15 AM——第一次 Claude 尝试。JSON 合法,但 Claude 在第 2 张仪表盘里捏造了一个 SQL 没返回的 conversion_rate 字段。第一次渲染时被抓到。

11:00 AM——第二次 Claude 尝试,一条 SQL 一次。输出干净。

12:30 PM——午饭。10 张仪表盘完成 70%。漏斗和渠道分布已经能用了。

2:00 PM——把贵的多触点查询(#9)调度成日表。仪表盘 9 改指这张表,不再跑实时查询。

3:00 PM——意识到埋在一个 thread 里的第 11 个需求:「能不能加一个内容衰减的?」现场写了第 11 条 SQL,喂给 Claude,20 分钟出了一张仪表盘。

4:30 PM——在 #marketing-reports 频道贴了 Looker Studio 链接。收到 3 个「哇」的表情、1 个「能加个地区过滤器吗」、还有财务的私信问下周能不能给她们做个 CAC 版本。

资产是 SQL,不是仪表盘

最后想留给你一个重新定义视角的说法。大部分营销仪表盘死掉,是因为有人把一段计算硬编到图表里,半年后没人记得哪个过滤器是对的,整张报表推倒重来。SQL 版本没有这个问题。SQL 是纯文本。SQL 可以在 git 里 diff。SQL 任何有 BigQuery 权限的人都能跑。SQL 还能被任何工具——Looker、Tableau、Power BI、Notion 图表、Slack 消息——渲染,不用 Claude 也能,有了 Claude 更快。

所以这个工作流的真正含义是:SQL 写一次,用 Claude 渲染成仪表盘,把仪表盘当衍生品,SQL 才是会复利的资产。 一年之后,你改一改第 3 条 SQL 再丢回 Claude,5 分钟就能回答一个新问题。一年之后,你不会记得 Looker Studio 哪个字段是哪个。

这才是那一天真正的意义。下午 4:30 的表情反应,只是收据。

Twitter LinkedIn Facebook Reddit Email

没有数据团队也能做留存分析:Claude + Klaviyo/Shopify 的 SQL 写法和读法漏斗流失诊断:7 步定位流失点(Claude + GA4 实操) 把 GSC + GA4 导出表喂给 Claude:一套 4 步内容分析工作流(以及它能回答的 3 个问题) Looker Studio + Claude 提示词库：12 种图表 + 该问的正确问题