如何统计3个月后无活动的独立客户并按月展示活跃/流失数？

需求 #

统计3个月未活动的独立客户，按月展示各月活跃、流失客户数量。

输入数据 #

预期结果 #

已尝试的方法 #

• 合并两张表格
• 用DATETIME_DIFF()计算与最新记录的月份差，但数据展示不符合预期

实现步骤（Google Data Studio） #

1. 计算每个客户的最后活跃月份
   新建计算字段最后活跃月份，公式为MAX(月份)，维度选择ID，以此得到每个客户最后一次有活动的月份。

2. 确定客户的流失月份
   新建计算字段流失月份，公式为DATE_ADD(PARSE_DATE("%B %Y", 最后活跃月份), INTERVAL 3 MONTH)。先把文本格式的月份转成日期类型，再往后推3个月，得到该客户的流失判定月份。

3. 生成连续月份序列
   要覆盖从最早数据月份（August 2022）到流失月份最大值（这里取到December 2022即可）的所有月份。可以在Google Sheets中制作辅助表，列好所需月份后导入到Data Studio。

4. 计算各月活跃、流失数

   • 活跃客户数：使用COUNT_DISTINCT(CASE WHEN 流失月份 > PARSE_DATE("%B %Y", 当前月份) THEN ID END)，统计流失月份晚于当前月份的独立客户数。
   • 流失客户数：使用COUNT_DISTINCT(CASE WHEN 流失月份 = PARSE_DATE("%B %Y", 当前月份) THEN ID END)，统计流失月份刚好等于当前月份的独立客户数。

按照上述步骤就能得到与预期一致的结果：

• August到October 2022：两个客户的流失月份都晚于当前月，因此活跃2、流失0
• November 2022：ID1的流失月份就是本月，流失1；ID2未到流失时间，活跃1
• December 2022：ID1已流失，ID2仍在活跃期，因此活跃1、流失0

关键注意点 #

• 必须确保月份字段为日期类型，若原数据是文本格式，一定要用PARSE_DATE("%B %Y", 月份)转换，否则日期计算会出错。
• 连续月份序列是核心，否则会漏掉像December 2022这种无直接活动记录但需要统计的月份。

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