AI Tools

没有数据团队也能做留存分析:Claude + Klaviyo/Shopify 的 SQL 写法和读法

没有数据团队也能做留存分析:Claude + Klaviyo/Shopify 的 SQL 写法和读法
目录

上个月我围观了一家 30 人 DTC 品牌在 Slack 上吵了三天,只为搞清楚他们的邮件自动化到底有没有用。有人说"复购率 18%",另一个人说"复购率 9%"。两边用的都是 Klaviyo 的同一块仪表板,两边都是对的,两边也都错了。问题在于:他们争论的指标,是一个把历史上所有客户都混在一起平均出来的数字,这种数字对"下一步该干什么"几乎毫无参考价值。

Cohort retention(队列留存分析,简称"留存")才是答案。它也是大部分营销人不得不外包给数据分析师的分析,所以往往根本做不出来。这篇文章里我讲一下怎么用 Claude 加上 Klaviyo、Shopify 的数据,15 分钟搞定一份留存表,完全不需要懂 SQL 也能读懂结果。

数据在哪里

做电商队列留存,你需要两边的数据:

Shopify(订单和客户身份的真值来源)

  • customers —— 一行一个人,字段包括 id、email、created_at(注册或首次下单时间)
  • orders —— 一行一笔交易,字段包括 id、customer_id、created_at、total_price、financial_status
  • refunds —— 在算留存前,先把退款订单剔掉

Klaviyo(行为数据更丰富 —— 打开、点击、浏览等)

  • Placed Order —— 转化事件,带 $value 和 ItemNames
  • Ordered Product —— 订单行项目级别
  • Profile 属性 —— 包括 first_seen(Klaviyo 第一次识别到这个 profile 的时间)和 Source(如果你在打标签,这就是注册来源)

要算营收留存,只用订单事件就够了。Klaviyo 更适合"行为队列"(比如"所有点击过新品邮件的人"),但要做普通的营收留存,Shopify 更快。

该给 Claude 的提示词

大部分人写提示词是错的。他们把"写一份队列留存 SQL"直接丢给 Claude,拿到的 SQL 确实能跑,但自己看不懂。解法是:让 Claude 写 SQL 的同时把"为什么这么写"也讲出来。

这是我用的提示词:

你是一位资深数据分析师,要为一位不懂技术的营销人写 SQL。请为我的 Shopify + Klaviyo 数据写一份 BigQuery(或者 PostgreSQL,看你的库)的队列留存查询。

要求:

  1. 只能用这些表:[列出你的表]。只能用这些列:[列出列名]。
  2. 每一行 SQL 都加注释 —— 我看不懂 SQL,要靠注释读。
  3. 用 CTE(WITH ... AS),每个 CTE 用英文全称命名,别用缩写。
  4. 给每个输出列起一个营销人会说的别名(比如 month_0_repeat_rate,不要 m0_rr)。
  5. 输出一个队列矩阵:行是月度获客队列,列是首次下单之后的 0、1、2、3、4、5、6 月,值是这个队列当月又下单的占比。
  6. 在最下面,不要再带 SQL,加一段大白话,告诉我"每一列是什么意思,一个营销人应该先看哪三个数字"。

样例数据结构:[从你的表里贴 3-5 行样例进来]。

真正起作用的是第 2 条和第 6 条。这两条逼着 Claude 在写代码的同时教你读代码。最终的输出是一份 60-80 行的查询,每一行都有注释,旁边附一段大白话:"先看 M1 列 —— 这就是首月复购率。"

输出长什么样

假设你跑了过去 12 个月的数据,你会得到一张这样的表(数据是示意,不是真实客户):

队列 规模 M0 M1 M2 M3 M4 M5 M6
2024-08 1,420 100% 12% 18% 21% 23% 24% 25%
2024-09 1,610 100% 14% 20% 23% 25% 26% —
2024-10 1,890 100% 11% 17% 19% 21% — —
2024-11 2,310 100% 15% 22% 25% — — —
2024-12 2,580 100% 16% 24% — — — —

从形状上能看出几件事:

  • 队列规模在涨。每月新客都在增加 —— 这是好消息。
  • M0 永远是 100%,因为定义上每个队列成员都下过第一单。
  • M1 才是真正有意义的第一个数字。

不懂数据,怎么读这张表

按顺序看三件事:

**1. M1 —— 首月复购率。**这是整张表里最 actionable(可直接行动)的一个数字。M1 低于 15%,说明你的购后邮件流(指买家下完单后的一系列自动触达邮件)坏了,或者产品选错了;20% 以上对大部分 DTC 品类算健康;30% 以上算优秀。上面这张示意表里,M1 半年都卡在 11-16%,到 11 月突然拉起来。发生了什么?大概率是邮件流重做了一遍。数据告诉你这次改版起效了。

**2. 曲线什么时候走平。**顺着每行从左往右看,百分比会涨,然后趋平 —— 那个趋平的值,大概就是你的"窗口期复购率"。如果趋平在 20%,说明平均 5 个客户里有 1 个会再买;如果趋平在 40%,你做的是真品牌。曲线的形状(趋平的速度)反映的是购买周期。趋平慢 = 长决策周期,趋平快 = 消费品。

**3. 队列之间的趋势。**从上到下读 M1 这一列。它是在涨、在跌、还是平的?如果 M1 在涨,说明你的获客质量在变好(或者产品在变好) —— 即使上个季度的营收是平的。如果 M1 在跌,你的漏斗顶部在引进越来越差的客户,下一季度的营收在订单还没下的时候就出问题了。

一个很多人漏掉的标记:对角线。右下角那些空格子不是缺数据 —— 是这些队列还没活到 M6。M0 是现在,M6 是六个月前。所以一行只有 M0-M2 的,是你最新的队列。从这里你能看出"这个月的情况"。

我踩过的坑

  • 不要把退款订单算进队列分母 —— 只算完成订单(financial_status = 'paid' 或者非退款),否则退款多的那个月看起来像留存崩盘。
  • 不要混着 guest checkout(游客结账) 和登录用户算,除非你按 email 去重。Shopify 默认允许同一个人的两个 customer_id。
  • **Shopify 时区和 Klaviyo 时区不一致时,不要用 Klaviyo 的 Placed Order 事件跑这个分析。**M0 会跳到错误的月份。选一个唯一的"这件事什么时候发生"的真值来源。
  • **第一次跑别上来就跑 24 个月窗口。**先跑 6 个月。窗口越长,你暗中假设的季节性越多。先小后大。

这套东西到底是干嘛用的

学这个不是为了取代你的数据团队,而是为了不再被他们卡住。开头那个 Slack 吵三天的例子,如果邮件经理自己跑过这份表,一分钟就能结束争论。她不需要 1000 行的分析 —— 她需要看到 M1 已经连跌了三个月,这个信号会直接促使她打开 Klaviyo 的购后邮件流,去看具体邮件的内容。

这才是真正的转变。队列留存从一个"交付物"变成一个"习惯"。而养成这个习惯的成本,刚刚降到了一个好用的提示词。