SQL 实战：客户留存率精细化计算与运营策略落地

一、核心前提：明确留存率的业务定义
在写 SQL 前，需先统一业务口径（避免计算偏差）：
初始用户群：某时间窗口内首次发生核心行为的用户（如首次注册、首次付费）
留存用户群：初始用户群中，在后续指定时间窗口内再次发生核心行为的用户
核心指标：
日留存（N 日留存）：初始日用户在第 N 日仍活跃的比例
周留存（N 周留存）：初始周用户在第 N 周仍活跃的比例
付费留存：初始付费用户在后续周期内再次付费的比例
二、SQL 计算核心逻辑（基于 MySQL 语法）
2.1 基础表结构说明
假设业务核心表（实际场景需根据自身表结构调整）：

-- 用户核心行为表（含注册/登录/付费等行为）
CREATE TABLE user_behavior (
  user_id VARCHAR(64) COMMENT '用户唯一标识',
  behavior_type TINYINT COMMENT '行为类型：1=注册，2=登录，3=付费',
  behavior_time DATETIME COMMENT '行为发生时间',
  order_id VARCHAR(64) COMMENT '付费订单号（behavior_type=3时非空）'
);

2.2 步骤 1：提取初始用户群（以 “首次注册用户” 为例）
先获取指定周期内的首次注册用户（初始用户群），用窗口函数ROW_NUMBER()去重：

-- 提取2024年1月的首次注册用户（按日期分组）
WITH first_register_user AS (
  SELECT 
    user_id,
    DATE(behavior_time) AS register_date -- 注册日期（按日聚合）
  FROM user_behavior
  WHERE behavior_type = 1 -- 仅筛选注册行为
    AND DATE(behavior_time) BETWEEN '2024-01-01' AND '2024-01-31'
  GROUP BY user_id, DATE(behavior_time)
  -- 确保每个用户只保留首次注册日期（避免重复统计）
  HAVING MIN(behavior_time) = behavior_time
)

2.3 步骤 2：关联后续行为，计算留存率
以 “日留存” 为例，计算初始用户在注册后 1 日、3 日、7 日的留存：

WITH first_register_user AS (
  -- 同上，提取首次注册用户
),
-- 关联用户后续行为，计算留存天数
user_retention AS (
  SELECT 
    fr.register_date,
    fr.user_id,
    -- 计算后续行为与注册日期的间隔天数
    DATEDIFF(DATE(ub.behavior_time), fr.register_date) AS retention_days
  FROM first_register_user fr
  LEFT JOIN user_behavior ub 
    ON fr.user_id = ub.user_id
    -- 后续行为需在注册后发生，且仅统计登录/付费（核心活跃行为）
    AND ub.behavior_time > fr.behavior_time
    AND ub.behavior_type IN (2, 3)
  GROUP BY fr.register_date, fr.user_id, retention_days
)
-- 最终计算各注册日的N日留存率
SELECT 
  register_date AS '初始日期',
  COUNT(DISTINCT user_id) AS '初始用户数',
  -- 1日留存率
  ROUND(
    COUNT(DISTINCT CASE WHEN retention_days = 1 THEN user_id END) / 
    COUNT(DISTINCT user_id), 
    3
  ) AS '1日留存率',
  -- 3日留存率
  ROUND(
    COUNT(DISTINCT CASE WHEN retention_days = 3 THEN user_id END) / 
    COUNT(DISTINCT user_id), 
    3
  ) AS '3日留存率',
  -- 7日留存率
  ROUND(
    COUNT(DISTINCT CASE WHEN retention_days = 7 THEN user_id END) / 
    COUNT(DISTINCT user_id), 
    3
  ) AS '7日留存率'
FROM user_retention
GROUP BY register_date
ORDER BY register_date;

2.4 多场景 SQL 扩展
场景 1：周留存计算（按自然周分组）

-- 核心修改：按周聚合初始用户，留存天数改为周数差
WITH first_register_user AS (
  SELECT 
    user_id,
    DATE_FORMAT(behavior_time, '%Y-%m-%d') AS register_week -- 格式：2024-01-01（当周周一）
  FROM user_behavior
  WHERE behavior_type = 1
    AND behavior_time BETWEEN '2024-01-01' AND '2024-01-31'
  GROUP BY user_id, DATE_FORMAT(behavior_time, '%Y-%m-%d')
),
user_retention AS (
  SELECT 
    fr.register_week,
    fr.user_id,
    -- 计算周数差（留存周数）
    TIMESTAMPDIFF(WEEK, fr.behavior_time, ub.behavior_time) AS retention_weeks
  FROM first_register_user fr
  LEFT JOIN user_behavior ub 
    ON fr.user_id = ub.user_id
    AND ub.behavior_time > fr.behavior_time
    AND ub.behavior_type IN (2, 3)
)
SELECT 
  register_week AS '初始周（周一）',
  COUNT(DISTINCT user_id) AS '初始用户数',
  ROUND(
    COUNT(DISTINCT CASE WHEN retention_weeks = 1 THEN user_id END) / 
    COUNT(DISTINCT user_id), 
    3
  ) AS '1周留存率',
  ROUND(
    COUNT(DISTINCT CASE WHEN retention_weeks = 4 THEN user_id END) / 
    COUNT(DISTINCT user_id), 
    3
  ) AS '4周留存率'
FROM user_retention
GROUP BY register_week;

场景 2：付费留存（仅统计再次付费的用户）

WITH first_pay_user AS (
  -- 提取首次付费用户（初始用户群：首次付费）
  SELECT 
    user_id,
    DATE(behavior_time) AS first_pay_date,
    MIN(behavior_time) AS first_pay_time -- 首次付费时间
  FROM user_behavior
  WHERE behavior_type = 3
    AND DATE(behavior_time) BETWEEN '2024-01-01' AND '2024-01-31'
  GROUP BY user_id
)
SELECT 
  first_pay_date AS '首次付费日期',
  COUNT(DISTINCT user_id) AS '首次付费用户数',
  -- 7日付费留存（首次付费后7日内再次付费）
  ROUND(
    COUNT(DISTINCT CASE WHEN DATEDIFF(DATE(ub.behavior_time), first_pay_date) BETWEEN 1 AND 7 
                        THEN user_id END) / 
    COUNT(DISTINCT user_id), 
    3
  ) AS '7日付费留存率',
  -- 30日付费留存
  ROUND(
    COUNT(DISTINCT CASE WHEN DATEDIFF(DATE(ub.behavior_time), first_pay_date) BETWEEN 1 AND 30 
                        THEN user_id END) / 
    COUNT(DISTINCT user_id), 
    3
  ) AS '30日付费留存率'
FROM first_pay_user fpu
LEFT JOIN user_behavior ub 
  ON fpu.user_id = ub.user_id
  AND ub.behavior_type = 3 -- 仅统计再次付费行为
  AND ub.behavior_time > fpu.first_pay_time
GROUP BY first_pay_date
ORDER BY first_pay_date;

三、数据驱动的留存运营策略（技术落地业务）
通过 SQL 计算出留存数据后，核心是针对性优化，以下是基于数据的分层策略：
3.1 低留存用户（1 日留存）：止损 + 激活
SQL 筛选：提取 1 日留存用户的行为日志，分析流失原因

-- 筛选低留存用户的行为路径
SELECT 
  fr.user_id,
  GROUP_CONCAT(CONCAT(behavior_type, '-', DATE(behavior_time)) ORDER BY behavior_time SEPARATOR '|') AS behavior_path
FROM first_register_user fr
LEFT JOIN user_behavior ub ON fr.user_id = ub.user_id
WHERE fr.register_date = '2024-01-01' -- 低留存日期
  AND NOT EXISTS (
    SELECT 1 FROM user_behavior ub2 
    WHERE ub2.user_id = fr.user_id 
      AND DATEDIFF(DATE(ub2.behavior_time), fr.register_date) = 1
  )
GROUP BY fr.user_id;

运营动作：
若行为路径显示 “注册后未完成新手任务”：推送新手引导弹窗 + 完成任务送优惠券
若 “注册后无任何浏览行为”：优化注册后首页推荐算法，精准匹配用户兴趣
3.2 中留存用户（1 日留存 20%-50%，7 日留存 %）：提升粘性
SQL 分析：统计该群体的高频行为，找到核心兴趣点

-- 统计中留存用户的TOP3行为类型
SELECT 
  ub.behavior_type,
  COUNT(DISTINCT ub.user_id) AS user_count,
  ROUND(COUNT(DISTINCT ub.user_id)/ (SELECT COUNT(DISTINCT user_id) FROM first_register_user WHERE register_date BETWEEN '2024-01-01' AND '2024-01-07'),3) AS ratio
FROM first_register_user fr
JOIN user_behavior ub ON fr.user_id = ub.user_id
WHERE fr.register_date BETWEEN '2024-01-01' AND '2024-01-07'
  AND DATEDIFF(DATE(ub.behavior_time), fr.register_date) BETWEEN 1 AND 7
GROUP BY ub.behavior_type
ORDER BY user_count DESC
LIMIT 3;

运营动作：
基于高频行为推送相关内容 / 商品（如高频浏览某类商品，推送新品通知）
设计 “连续活跃奖励”：7 日内活跃 5 天送会员体验
3.3 高留存用户（7 日留存≥30%）：留存 + 转化
SQL 筛选：提取高留存用户的付费意愿信号（如浏览付费页面、加入购物车未付款）

-- 高留存用户的付费意愿行为
SELECT 
  fr.user_id,
  COUNT(CASE WHEN ub.behavior_type = 4 THEN 1 END) AS pay_page_view_count, -- 浏览付费页
  COUNT(CASE WHEN ub.behavior_type = 5 THEN 1 END) AS cart_count -- 加入购物车
FROM first_register_user fr
JOIN user_behavior ub ON fr.user_id = ub.user_id
WHERE fr.register_date BETWEEN '2024-01-01' AND '2024-01-07'
  AND DATEDIFF(DATE(ub.behavior_time), fr.register_date) = 7
GROUP BY fr.user_id
HAVING COUNT(DISTINCT CASE WHEN DATEDIFF(DATE(ub.behavior_time), fr.register_date) BETWEEN 1 AND 7 THEN ub.user_id END) / COUNT(DISTINCT fr.user_id) ≥ 0.3
ORDER BY pay_page_view_count DESC;

运营动作：
推送限时折扣券，刺激付费转化
邀请加入 VIP 社群，提供专属服务，锁定长期留存
四、SQL 优化与落地技巧
性能优化：
对user_id、behavior_time、behavior_type建立联合索引：INDEX idx_user_behavior (user_id, behavior_time, behavior_type)
大表计算时，用DATE(behavior_time)分区表，减少扫描范围
口径一致性：
所有留存计算统一 “时间窗口闭合规则”（如包含首尾日期：BETWEEN start_date AND end_date）
避免重复统计同一用户（用DISTINCT user_id而非COUNT(user_id)）
工具集成：
将 SQL 封装为定时任务（如 Airflow），每日 / 每周自动生成留存报表
对接 BI 工具（如 Metabase、Tableau），可视化留存曲线，快速定位异常（如某日留存骤降）
五、总结
客户留存率的核心是 “用 SQL 精准度量，用数据驱动策略”：先通过窗口函数、关联查询等语法实现多场景留存计算，再基于分层数据定位用户痛点，最后落地针对性运营动作。实际应用中，需根据业务核心行为（如电商的下单、内容平台的阅读）调整表结构和 SQL 逻辑，让技术真正服务于留存增长。