跳到主要内容

用户增长分析案例

场景​

你是某互联网产品的增长分析师，产品 MAU 连续 3 个月停滞在 500 万左右，管理层希望 Q2 达到 800 万。请给出分析方案和增长策略建议。

分析框架​

第一步：用户增长模型​

MAUt+1=MAUt+新增用户−流失用户MAU_{t+1} = MAU_t + 新增用户 - 流失用户 净增长=新增用户−流失用户净增长 = 新增用户 - 流失用户

MAU 停滞意味着新增 ≈ 流失，需要同时提升新增和降低流失。

第二步：AARRR 漏斗诊断​

-- AARRR 漏斗各环节指标
-- Acquisition（获客）
SELECT
    channel,
    COUNT(DISTINCT user_id) AS new_users,
    SUM(cost) AS acquisition_cost,
    ROUND(SUM(cost) / COUNT(DISTINCT user_id), 2) AS cac
FROM user_acquisition
WHERE register_date BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY channel
ORDER BY new_users DESC;

-- Activation（激活）
SELECT
    DATE_FORMAT(register_date, '%Y-%m') AS month,
    COUNT(DISTINCT u.user_id) AS registered,
    COUNT(DISTINCT CASE WHEN a.core_action_done = 1 THEN a.user_id END) AS activated,
    ROUND(COUNT(DISTINCT CASE WHEN a.core_action_done = 1 THEN a.user_id END) /
          COUNT(DISTINCT u.user_id) * 100, 2) AS activation_rate
FROM users u
LEFT JOIN user_activation a ON u.user_id = a.user_id
GROUP BY DATE_FORMAT(register_date, '%Y-%m');

-- Retention（留存）
SELECT
    register_month,
    COUNT(DISTINCT user_id) AS cohort_size,
    ROUND(COUNT(DISTINCT CASE WHEN month_diff = 1 THEN user_id END) / COUNT(DISTINCT user_id) * 100, 2) AS m1_retention,
    ROUND(COUNT(DISTINCT CASE WHEN month_diff = 2 THEN user_id END) / COUNT(DISTINCT user_id) * 100, 2) AS m2_retention,
    ROUND(COUNT(DISTINCT CASE WHEN month_diff = 3 THEN user_id END) / COUNT(DISTINCT user_id) * 100, 2) AS m3_retention
FROM (
    SELECT
        u.user_id,
        DATE_FORMAT(u.register_date, '%Y-%m') AS register_month,
        TIMESTAMPDIFF(MONTH, u.register_date, l.login_date) AS month_diff
    FROM users u
    JOIN user_logins l ON u.user_id = l.user_id
) t
GROUP BY register_month;

第三步：增长瓶颈诊断​

第四步：Aha Moment 分析​

找到用户留存的关键行为：

-- 分析不同行为与 7 日留存率的相关性
WITH user_actions AS (
    SELECT
        u.user_id,
        -- 是否在首日执行了关键行为
        MAX(CASE WHEN a.action = 'add_friend' AND DATEDIFF(a.action_date, u.register_date) <= 1 THEN 1 ELSE 0 END) AS day1_add_friend,
        MAX(CASE WHEN a.action = 'post_content' AND DATEDIFF(a.action_date, u.register_date) <= 1 THEN 1 ELSE 0 END) AS day1_post,
        MAX(CASE WHEN a.action = 'complete_profile' AND DATEDIFF(a.action_date, u.register_date) <= 1 THEN 1 ELSE 0 END) AS day1_profile,
        -- 7 日留存
        MAX(CASE WHEN l.login_date = DATE_ADD(u.register_date, INTERVAL 7 DAY) THEN 1 ELSE 0 END) AS retained_d7
    FROM users u
    LEFT JOIN user_actions a ON u.user_id = a.user_id
    LEFT JOIN user_logins l ON u.user_id = l.user_id
    GROUP BY u.user_id
)
SELECT
    '添加好友' AS action,
    ROUND(AVG(CASE WHEN day1_add_friend = 1 THEN retained_d7 END) * 100, 2) AS retention_with,
    ROUND(AVG(CASE WHEN day1_add_friend = 0 THEN retained_d7 END) * 100, 2) AS retention_without
FROM user_actions
UNION ALL
SELECT
    '发布内容',
    ROUND(AVG(CASE WHEN day1_post = 1 THEN retained_d7 END) * 100, 2),
    ROUND(AVG(CASE WHEN day1_post = 0 THEN retained_d7 END) * 100, 2)
FROM user_actions
UNION ALL
SELECT
    '完善资料',
    ROUND(AVG(CASE WHEN day1_profile = 1 THEN retained_d7 END) * 100, 2),
    ROUND(AVG(CASE WHEN day1_profile = 0 THEN retained_d7 END) * 100, 2)
FROM user_actions;

第五步：增长策略建议​

策略方向具体措施预期效果优先级
提升激活优化新手引导，引导完成 Aha Moment激活率 +15%P0
降低流失流失用户召回推送月流失率 -5%P0
拓展渠道上线老带新裂变活动新增 +30%P1
提升留存建立内容推荐体系次留 +8%P1
用户运营RFM 分层差异化运营活跃度 +10%P2

量化目标推演​

目标MAU=800万=当前MAU+∑i=13(新增i−流失i)目标MAU = 800万 = 当前MAU + \sum_{i=1}^{3}(新增_i - 流失_i) 缺口=300万=3个月×(月均新增−月均流失)缺口 = 300万 = 3个月 \times (月均新增 - 月均流失) 每月净增需达100万=月新增180万×留存率提升−月流失降至80万每月净增需达 100万 = 月新增180万 \times 留存率提升 - 月流失降至80万

面试应答要点​

增长分析的关键思路
  1. 先看大盘：MAU = 新增 - 流失，判断是「进水」还是「出水」问题
  2. 再看漏斗：AARRR 每一层转化率，找到最大掉量环节
  3. 深挖行为：Aha Moment 分析，找到驱动留存的关键行为
  4. 量化推演：增长策略的 ROI 预估，优先级排序

相关链接​