如何在Looker Studio中计算滚动24周的新客、留存、流失客营收

一、补全首单日期的聚合配置 #

假设你已将订单表自连接到自身（别名orders_first），关联键为客户名称，接下来在Looker Studio数据源中创建聚合计算字段：

首单日期 = MIN(orders_first.订单日期)

注意：计算字段的聚合方式需设为「按客户名称聚合」，确保每个客户对应唯一的首单日期。

二、定义核心时间范围字段 #

创建两个全局时间字段，用于后续客户分类判断：

滚动24周起始日 = DATE_SUB(CURRENT_DATE(), INTERVAL 24 WEEK)
近52周起始日 = DATE_SUB(CURRENT_DATE(), INTERVAL 52 WEEK)

三、创建三类营收计算字段 #

1. 新客营收 #

统计首单落在滚动24周内的客户，在该周期内的订单金额：

新客营收 = CASE
  WHEN 订单日期 >= 滚动24周起始日 AND 首单日期 >= 滚动24周起始日 THEN 订单金额
  ELSE 0
END

2. 流失客营收 #

针对「曾下单（首单在滚动24周前）但近24周（滚动24周起始日之前的24周）无下单」且当前滚动24周内有下单的客户，统计其订单金额：
首先补充获取客户最近历史下单日期（需二次自连接订单表，别名orders_prev，关联客户名称并筛选订单日期 < 滚动24周起始日的记录）：

最近历史下单日期 = MAX(orders_prev.订单日期)

再创建流失客营收字段：

流失客营收 = CASE
  WHEN 订单日期 >= 滚动24周起始日
       AND 首单日期 < 滚动24周起始日
       AND 最近历史下单日期 < DATE_SUB(滚动24周起始日, INTERVAL 24 WEEK)
  THEN 订单金额
  ELSE 0
END

3. 老客营收增减 #

统计「近52周至近24周之间有下单，且当前滚动24周内也下单」的客户，在滚动24周内的订单金额；若需计算增减值，可额外关联该客户同期历史营收做差值：

老客营收 = CASE
  WHEN 订单日期 >= 滚动24周起始日
       AND EXISTS(SELECT 1 FROM orders o WHERE o.客户名称 = 客户名称 AND o.订单日期 >= 近52周起始日 AND o.订单日期 < 滚动24周起始日)
  THEN 订单金额
  ELSE 0
END

若需计算营收增减，可新增字段：老客营收增减 = 老客营收 - 历史同期营收，其中历史同期营收为该客户在近52-24周的订单金额总和

四、图表配置与过滤 #

1. 创建时间序列图表，维度选择订单日期并设置为「按周聚合」
2. 度量添加上述三个营收字段，设置聚合方式为「求和」
3. 添加过滤器：订单日期 >= 滚动24周起始日，确保图表始终展示最近24周的数据

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