【实战2.2】用户留存与生命周期分析：Cohort留存与高价值用户特征

实战项目 2.2：用户留存与生命周期分析

项目信息

项目编号：2.2
所属方向：用户增长分析
难度：⭐⭐⭐ 进阶
预计时间：5-6小时
前置技能：用户分群（实战1.1）、获客分析（实战2.1）

---

项目目标

计算 DeFi 协议的留存率，识别高留存用户特征，理解用户生命周期

产出清单：
├── ✅ Dune Dashboard：Cohort 留存率热力图
├── ✅ 高留存用户特征报告
└── ✅ 留存策略设计文档

---

Task 1：Cohort 留存率分析

核心 SQL — 周留存率 Cohort 热力图

-- 项目2.2 Task 1: Uniswap 周 Cohort 留存分析
-- 目标：按用户首次交易周分群，计算后续每周留存率

WITH user_cohort AS (
  -- 确定每个用户的 cohort（首次交易周）
  SELECT
    taker as user_address,
    DATE_TRUNC('week', MIN(block_time)) as cohort_week
  FROM dex.trades
  WHERE block_time >= NOW() - INTERVAL '90' DAY
    AND project = 'uniswap'
    AND blockchain = 'ethereum'
  GROUP BY taker
),

weekly_active AS (
  -- 每个用户每周是否活跃
  SELECT DISTINCT
    taker as user_address,
    DATE_TRUNC('week', block_time) as active_week
  FROM dex.trades
  WHERE block_time >= NOW() - INTERVAL '90' DAY
    AND project = 'uniswap'
    AND blockchain = 'ethereum'
)

SELECT
  uc.cohort_week,
  COUNT(DISTINCT uc.user_address) as cohort_size,
  -- Week 0 = 注册周，必然 100%
  ROUND(100.0 * COUNT(DISTINCT CASE
    WHEN wa.active_week = uc.cohort_week + INTERVAL '1' WEEK
    THEN uc.user_address END) / COUNT(DISTINCT uc.user_address), 1) as week1_retention,
  ROUND(100.0 * COUNT(DISTINCT CASE
    WHEN wa.active_week = uc.cohort_week + INTERVAL '2' WEEK
    THEN uc.user_address END) / COUNT(DISTINCT uc.user_address), 1) as week2_retention,
  ROUND(100.0 * COUNT(DISTINCT CASE
    WHEN wa.active_week = uc.cohort_week + INTERVAL '4' WEEK
    THEN uc.user_address END) / COUNT(DISTINCT uc.user_address), 1) as week4_retention,
  ROUND(100.0 * COUNT(DISTINCT CASE
    WHEN wa.active_week = uc.cohort_week + INTERVAL '8' WEEK
    THEN uc.user_address END) / COUNT(DISTINCT uc.user_address), 1) as week8_retention
FROM user_cohort uc
LEFT JOIN weekly_active wa ON uc.user_address = wa.user_address
GROUP BY uc.cohort_week
HAVING COUNT(DISTINCT uc.user_address) >= 100  -- 过滤太小的 cohort
ORDER BY uc.cohort_week DESC

SQL 要点解析

关键技术点：
═══════════════════════════════════════════════════════════

1. Cohort 定义
   └── 按"首次交易周"分群
   └── 每个用户只属于一个 cohort

2. 留存率计算
   └── 分子：在第 N 周仍活跃的用户数
   └── 分母：cohort 初始用户数
   └── CASE WHEN + 窗口计算实现

3. 数据质量
   └── HAVING >= 100 过滤样本过小的 cohort
   └── 避免统计噪音

4. 性能优化
   └── DISTINCT 去重，一个用户一周内多次交易只算一次
   └── 90 天窗口平衡数据量和计算速度
═══════════════════════════════════════════════════════════

预期留存数据

典型 DEX Cohort 留存（估算）：

        Week0  Week1  Week2  Week4  Week8
        100%   │      │      │      │
Cohort1 ████   ██     █      █      ▌
        100%   18%    10%    6%     3%

对标参考（Web2 vs Web3）：
├── Web2 电商：Week1 40%, Week4 20%, Week8 15%
├── Web2 社交：Week1 50%, Week4 30%, Week8 25%
├── Web3 DEX：  Week1 15-20%, Week4 5-8%, Week8 2-4%
└── Web3 借贷：Week1 25-35%, Week4 15-20%, Week8 10-15%

洞察：DeFi 留存远低于 Web2，但借贷 > DEX
原因：借贷有"持续持仓"的粘性，DEX 是"按需交易"

---

Task 2：高留存用户特征识别

核心 SQL — 留存用户 vs 流失用户行为对比

-- 项目2.2 Task 2: 高留存用户特征分析
-- 目标：对比 4 周后仍活跃 vs 流失用户的首周行为差异

WITH user_cohort AS (
  SELECT
    taker as user_address,
    DATE_TRUNC('week', MIN(block_time)) as cohort_week
  FROM dex.trades
  WHERE block_time >= NOW() - INTERVAL '90' DAY
    AND project = 'uniswap'
    AND blockchain = 'ethereum'
  GROUP BY taker
),

first_week_behavior AS (
  -- 用户首周行为特征
  SELECT
    dt.taker as user_address,
    uc.cohort_week,
    COUNT(*) as first_week_trades,
    SUM(dt.amount_usd) as first_week_volume,
    COUNT(DISTINCT dt.token_bought_symbol) as unique_tokens,
    COUNT(DISTINCT DATE_TRUNC('day', dt.block_time)) as active_days,
    AVG(dt.amount_usd) as avg_trade_size
  FROM dex.trades dt
  INNER JOIN user_cohort uc ON dt.taker = uc.user_address
  WHERE dt.block_time BETWEEN uc.cohort_week AND uc.cohort_week + INTERVAL '1' WEEK
    AND dt.project = 'uniswap'
    AND dt.blockchain = 'ethereum'
  GROUP BY dt.taker, uc.cohort_week
),

retention_status AS (
  -- 判断用户是否在第 4 周仍活跃
  SELECT
    uc.user_address,
    uc.cohort_week,
    MAX(CASE WHEN dt.block_time BETWEEN uc.cohort_week + INTERVAL '4' WEEK
                                     AND uc.cohort_week + INTERVAL '5' WEEK
         THEN 1 ELSE 0 END) as retained_week4
  FROM user_cohort uc
  LEFT JOIN dex.trades dt ON uc.user_address = dt.taker
    AND dt.project = 'uniswap'
    AND dt.blockchain = 'ethereum'
  GROUP BY uc.user_address, uc.cohort_week
)

SELECT
  CASE WHEN rs.retained_week4 = 1 THEN '留存用户' ELSE '流失用户' END as status,
  COUNT(*) as user_count,
  ROUND(AVG(fb.first_week_trades), 1) as avg_first_week_trades,
  ROUND(AVG(fb.first_week_volume), 0) as avg_first_week_volume,
  ROUND(AVG(fb.unique_tokens), 1) as avg_unique_tokens,
  ROUND(AVG(fb.active_days), 1) as avg_active_days,
  ROUND(AVG(fb.avg_trade_size), 0) as avg_trade_size
FROM retention_status rs
INNER JOIN first_week_behavior fb
  ON rs.user_address = fb.user_address
GROUP BY 1
ORDER BY 1

预期高留存用户特征

核心发现：
═══════════════════════════════════════════════════════════

留存的"魔法数字"（Aha Moment）：

首周完成 3+ 笔交易的用户，
留存率是只做 1 笔的 4 倍。

这类似于：
├── Facebook: "7 天内加 10 个好友"
├── Slack: "发送 2000 条消息"
├── Dropbox: "上传第一个文件"
└── Uniswap: "首周完成 3+ 笔交易"

产品启示：引导用户尽快完成前 3 笔交易
═══════════════════════════════════════════════════════════

---

Task 3：用户生命周期价值（LTV）分析

核心 SQL — 不同用户层级的 LTV

-- 项目2.2 Task 3: 用户生命周期价值（协议视角）
-- LTV = 用户在协议中产生的总手续费

WITH user_lifetime AS (
  SELECT
    taker as user_address,
    MIN(block_time) as first_trade,
    MAX(block_time) as last_trade,
    COUNT(*) as total_trades,
    SUM(amount_usd) as total_volume,
    -- 假设 DEX 手续费率 0.3%
    SUM(amount_usd) * 0.003 as estimated_fees_generated,
    DATE_DIFF('day', MIN(block_time), MAX(block_time)) as lifetime_days,
    COUNT(DISTINCT DATE_TRUNC('week', block_time)) as active_weeks
  FROM dex.trades
  WHERE block_time >= NOW() - INTERVAL '180' DAY
    AND project = 'uniswap'
    AND blockchain = 'ethereum'
    AND amount_usd > 0
  GROUP BY taker
)

SELECT
  CASE
    WHEN total_volume >= 1000000 THEN 'Whale (>$1M)'
    WHEN total_volume >= 100000 THEN 'Large ($100K-$1M)'
    WHEN total_volume >= 10000 THEN 'Medium ($10K-$100K)'
    ELSE 'Small (<$10K)'
  END as user_tier,
  COUNT(*) as user_count,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER(), 1) as pct_users,
  ROUND(SUM(estimated_fees_generated), 0) as total_fees,
  ROUND(100.0 * SUM(estimated_fees_generated) / SUM(SUM(estimated_fees_generated)) OVER(), 1) as pct_fees,
  ROUND(AVG(estimated_fees_generated), 2) as avg_ltv,
  ROUND(AVG(lifetime_days), 0) as avg_lifetime_days,
  ROUND(AVG(active_weeks), 1) as avg_active_weeks
FROM user_lifetime
GROUP BY 1
ORDER BY total_fees DESC

预期 LTV 分布

用户 LTV 分布（帕累托法则）：

用户层级       | 占比  | 手续费占比 | 平均 LTV
───────────────┼───────┼───────────┼──────────
🐋 Whale       |  1%   |   55%     | $15,000+
🦈 Large       |  5%   |   25%     | $1,500
🐟 Medium      | 20%   |   15%     | $75
🦐 Small       | 74%   |    5%     | $3

关键洞察：
├── 1% 的用户贡献 55% 的收入（极端帕累托）
├── 74% 的用户 LTV < $5（几乎不产生收入）
├── 产品策略应该聚焦 Top 6% 用户的留存
└── 小额用户的价值在于"网络效应"而非直接收入

---

高留存用户特征报告

报告框架

一、留存现状
   Uniswap Week4 留存率约 5-8%
   显著低于 Web2 产品（20-30%）
   但高于大多数竞品 DEX

二、高留存用户画像
   ├── 首周 3+ 笔交易（Aha Moment）
   ├── 交易 3+ 种不同 Token（探索型用户）
   ├── 首周 3+ 天活跃（习惯形成）
   └── 首笔交易金额 > $500（有承诺感）

三、流失原因假设
   ├── 一次性需求（只是换一次 Token）
   ├── Gas 费过高（小额不划算）
   ├── 替代品切换（发现更好的 DEX）
   └── 市场周期（熊市减少交易）

四、策略建议
   ├── P0：引导用户首周完成 3 笔交易
   ├── P1：为高价值用户提供 VIP 体验
   ├── P1：低 Gas 时段推送交易提醒
   └── P2：跨协议留存（DEX → 借贷 → LP）

---

留存策略设计文档

策略一：Aha Moment 加速

目标：让更多用户在首周完成 3 笔交易

方案：
├── 首笔交易后 → 推荐"下一步操作"（swap 另一个热门 Token）
├── 第 2 笔交易后 → 解锁"交易成就"徽章
├── 第 3 笔交易后 → 推荐进阶功能（限价单/LP）
└── 3 天内未完成 → 推送低 Gas 提醒

预期效果：Week4 留存率 +3-5 个百分点

策略二：高价值用户留存

目标：Top 6% 用户（>$10K 交易量）Week8 留存率翻倍

方案：
├── 专属 Dashboard（持仓分析/交易历史/收益追踪）
├── 大额交易优化（MEV 保护/最优路由/滑点预警）
├── 流失预警（7 天未交易 → 触发挽回机制）
└── 社区权益（治理投票权重/手续费返还）

预期效果：高价值用户 Week8 留存率 5% → 12%

---

面试题准备

Q: DeFi 协议的留存率为什么比 Web2 低很多？如何提升？

30 秒版本： 三个原因 — (1) Web3 是"按需使用"而非"日常工具"，用户不需要每天 swap；(2) 切换成本为零（不需要重新注册/导数据）；(3) 缺少传统的留存手段（推送/邮件/推荐）。提升方法：找到 Aha Moment（首周 3 笔交易）→ 加速新用户到达 → 为高价值用户提供差异化体验。

追问：借贷协议的留存为什么比 DEX 高？ → 因为借贷有"持续持仓"的天然粘性 — 你的抵押品和债务锁在协议里，不需要主动操作也算"留存"。DEX 是交易型产品，每次使用都需要主动决策。产品设计启示：增加"持续状态"（LP、质押、订阅）能显著提升留存。

---

下一步

完成本项目后，推荐继续：

• 项目 2.3：DAU/MAU 与季节性分析
• 项目 4.1：女巫地址识别规则设计

DAU/MAU 分析会帮你理解宏观活跃度趋势和驱动因素。