【实战2.3】DAU/MAU与季节性分析：活跃度趋势与驱动因素

实战项目 2.3：DAU/MAU 与季节性分析

项目信息

项目编号：2.3
所属方向：用户增长分析
难度：⭐⭐⭐ 进阶
预计时间：4-5小时
前置技能：用户留存（实战2.2）、Dune SQL 进阶

---

项目目标

分析 DeFi 协议的活跃度趋势、粘性指标、事件驱动变化和季节性规律

产出清单：
├── ✅ Dune Dashboard：DAU/MAU 趋势图
├── ✅ 事件-活跃度关联分析
└── ✅ 活跃度提升策略

---

Task 1：DAU/MAU 趋势与粘性分析

核心 SQL — 每日活跃用户与月活跃用户

-- 项目2.3 Task 1: Uniswap DAU/MAU 趋势
-- 目标：计算 DAU、MAU 和 DAU/MAU 比值（粘性指标）

WITH daily_users AS (
  SELECT
    DATE_TRUNC('day', block_time) as dt,
    COUNT(DISTINCT taker) as dau,
    SUM(amount_usd) as daily_volume,
    COUNT(*) as daily_trades
  FROM dex.trades
  WHERE block_time >= NOW() - INTERVAL '180' DAY
    AND project = 'uniswap'
    AND blockchain = 'ethereum'
    AND amount_usd > 0
  GROUP BY 1
),

monthly_users AS (
  SELECT
    DATE_TRUNC('day', block_time) as dt,
    COUNT(DISTINCT taker) as mau_on_day
  FROM dex.trades
  WHERE block_time >= NOW() - INTERVAL '210' DAY
    AND project = 'uniswap'
    AND blockchain = 'ethereum'
  GROUP BY 1
),

-- 滚动 30 天 MAU
rolling_mau AS (
  SELECT
    d.dt,
    COUNT(DISTINCT all_users.taker) as mau_30d
  FROM daily_users d
  CROSS JOIN LATERAL (
    SELECT DISTINCT taker
    FROM dex.trades
    WHERE block_time BETWEEN d.dt - INTERVAL '30' DAY AND d.dt
      AND project = 'uniswap'
      AND blockchain = 'ethereum'
  ) all_users
  GROUP BY d.dt
)

SELECT
  du.dt,
  du.dau,
  rm.mau_30d as mau,
  ROUND(100.0 * du.dau / NULLIF(rm.mau_30d, 0), 1) as dau_mau_ratio,
  du.daily_volume,
  du.daily_trades,
  ROUND(du.daily_volume / NULLIF(du.dau, 0), 0) as volume_per_user
FROM daily_users du
LEFT JOIN rolling_mau rm ON du.dt = rm.dt
WHERE du.dt >= NOW() - INTERVAL '180' DAY
ORDER BY du.dt

简化版 SQL（性能更好）

-- 简化版：按周聚合，避免 LATERAL JOIN 性能问题
WITH weekly_stats AS (
  SELECT
    DATE_TRUNC('week', block_time) as week,
    COUNT(DISTINCT taker) as wau,
    COUNT(DISTINCT DATE_TRUNC('day', block_time) || '-' || taker) as total_user_days,
    SUM(amount_usd) as weekly_volume,
    COUNT(*) as weekly_trades
  FROM dex.trades
  WHERE block_time >= NOW() - INTERVAL '180' DAY
    AND project = 'uniswap'
    AND blockchain = 'ethereum'
    AND amount_usd > 0
  GROUP BY 1
),

monthly_stats AS (
  SELECT
    DATE_TRUNC('month', block_time) as month,
    COUNT(DISTINCT taker) as mau
  FROM dex.trades
  WHERE block_time >= NOW() - INTERVAL '180' DAY
    AND project = 'uniswap'
    AND blockchain = 'ethereum'
  GROUP BY 1
)

SELECT
  ws.week,
  ws.wau,
  ROUND(ws.total_user_days::decimal / 7, 0) as avg_dau,
  ms.mau,
  -- DAU/MAU 粘性指标
  ROUND(100.0 * (ws.total_user_days::decimal / 7) / NULLIF(ms.mau, 0), 1) as stickiness_pct,
  ws.weekly_volume,
  ROUND(ws.weekly_volume / NULLIF(ws.wau, 0), 0) as volume_per_user
FROM weekly_stats ws
LEFT JOIN monthly_stats ms ON DATE_TRUNC('month', ws.week) = ms.month
ORDER BY ws.week

DAU/MAU 比值解读

DAU/MAU 比值 = 产品粘性指标
═══════════════════════════════════════════════════════════

DAU/MAU 含义：
├── 50%+ = 极高粘性（用户每月用 15+ 天）→ 社交/游戏
├── 20-50% = 高粘性（用户每月用 6-15 天）→ 工具/生产力
├── 10-20% = 中等粘性（用户每月用 3-6 天）→ 电商/交易
├── 5-10% = 低粘性（用户每月用 1-3 天）→ DeFi DEX
└── <5% = 极低粘性（偶尔使用）

DeFi 协议参考值：
├── Uniswap：5-10%（按需交易）
├── Aave：10-15%（持仓管理）
├── OpenSea (NFT)：3-8%（交易+浏览）
├── Snapshot：2-5%（投票时才用）
└── MetaMask：15-25%（钱包=入口）

重要：DeFi DAU/MAU 低不一定是坏事
      DEX 是"按需使用"产品，不需要日活
      更重要的是"需要时想到你"
═══════════════════════════════════════════════════════════

---

Task 2：事件驱动的活跃度分析

核心 SQL — 关键事件前后的活跃度变化

-- 项目2.3 Task 2: 事件对 DAU 的影响分析
-- 目标：对比关键事件日前后的活跃度变化

WITH daily_dau AS (
  SELECT
    DATE_TRUNC('day', block_time) as dt,
    COUNT(DISTINCT taker) as dau,
    SUM(amount_usd) as volume
  FROM dex.trades
  WHERE block_time >= NOW() - INTERVAL '180' DAY
    AND project = 'uniswap'
    AND blockchain = 'ethereum'
  GROUP BY 1
),

-- 计算 7 天移动平均（平滑噪音）
smoothed AS (
  SELECT
    dt,
    dau,
    volume,
    AVG(dau) OVER (ORDER BY dt ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) as dau_7d_avg,
    AVG(volume) OVER (ORDER BY dt ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) as volume_7d_avg
  FROM daily_dau
),

-- 日环比和周同比
with_changes AS (
  SELECT
    dt,
    dau,
    volume,
    dau_7d_avg,
    -- 日环比
    ROUND(100.0 * (dau - LAG(dau) OVER (ORDER BY dt)) /
      NULLIF(LAG(dau) OVER (ORDER BY dt), 0), 1) as dau_dod_change,
    -- 周同比（与 7 天前对比）
    ROUND(100.0 * (dau - LAG(dau, 7) OVER (ORDER BY dt)) /
      NULLIF(LAG(dau, 7) OVER (ORDER BY dt), 0), 1) as dau_wow_change,
    -- 标记异常日（DAU 偏离 7 天均值 > 50%）
    CASE WHEN ABS(dau - dau_7d_avg) / NULLIF(dau_7d_avg, 0) > 0.5
         THEN 'ANOMALY' ELSE 'NORMAL' END as anomaly_flag
  FROM smoothed
)

SELECT
  dt,
  dau,
  dau_7d_avg,
  dau_dod_change,
  dau_wow_change,
  volume,
  anomaly_flag
FROM with_changes
WHERE dt >= NOW() - INTERVAL '90' DAY
ORDER BY dt

事件-活跃度关联分析框架

核心洞察：
═══════════════════════════════════════════════════════════

1. DeFi 活跃度本质是"事件驱动"而非"习惯驱动"
   Web2：用户每天打开微信是习惯
   Web3：用户在 ETH 涨了/跌了/有空投时才来

2. 异常日 = 产品机会
   DAU 暴涨日通常是"市场事件"→ 这些用户是潜在留存用户
   如果在暴涨日做好引导，留存率可提升 2-3x

3. 竞品事件是获客窗口
   竞品被黑 / 竞品 UX 差评 → 用户迁移窗口
   需要提前准备好"迁移引导"功能
═══════════════════════════════════════════════════════════

---

Task 3：季节性与周期分析

核心 SQL — 周内分布与时段分析

-- 项目2.3 Task 3: 交易活跃度的周内分布和时段分析
WITH hourly_stats AS (
  SELECT
    EXTRACT(DOW FROM block_time) as day_of_week,  -- 0=周日
    EXTRACT(HOUR FROM block_time) as hour_utc,
    COUNT(DISTINCT taker) as unique_users,
    COUNT(*) as trade_count,
    SUM(amount_usd) as volume
  FROM dex.trades
  WHERE block_time >= NOW() - INTERVAL '90' DAY
    AND project = 'uniswap'
    AND blockchain = 'ethereum'
    AND amount_usd > 0
  GROUP BY 1, 2
)

SELECT
  CASE day_of_week
    WHEN 0 THEN '周日'
    WHEN 1 THEN '周一'
    WHEN 2 THEN '周二'
    WHEN 3 THEN '周三'
    WHEN 4 THEN '周四'
    WHEN 5 THEN '周五'
    WHEN 6 THEN '周六'
  END as weekday,
  hour_utc,
  unique_users,
  trade_count,
  ROUND(volume / 1e6, 2) as volume_millions,
  -- 计算相对活跃度（vs 平均值）
  ROUND(100.0 * unique_users / AVG(unique_users) OVER(), 0) as relative_activity
FROM hourly_stats
ORDER BY day_of_week, hour_utc

预期季节性规律

周内活跃度分布（相对值，100 = 平均水平）：

周一  ████████████████  115%  ← 最活跃（美股开盘联动）
周二  ███████████████   110%
周三  ██████████████    108%
周四  ██████████████    106%
周五  ████████████      100%
周六  ██████████         85%  ← 周末下降
周日  █████████          80%

时段分布（UTC）：
├── 高峰：13:00-18:00 UTC（美国交易时段）
├── 次高峰：08:00-12:00 UTC（欧洲交易时段）
├── 低谷：02:00-07:00 UTC（亚洲时段相对少）
└── 深夜：22:00-02:00 UTC（最低活跃）

季度规律：
├── Q1（1-3月）：高活跃（年初叙事+资金流入）
├── Q2（4-6月）：中等（叙事验证期）
├── Q3（7-9月）：低活跃（夏季低迷）
└── Q4（10-12月）：回升（年底冲刺+圣诞行情）

市场周期：
├── 牛市：DAU 5-10x 正常水平
├── 熊市：DAU 回落至基线
└── 黑天鹅：DAU 短暂暴涨 3-5x（恐慌交易）

---

活跃度提升策略

策略框架

活跃度 = 用户基数 × 使用频率 × 触发概率

三个杠杆：
├── 1. 扩大用户基数（获客 → 实战 2.1）
├── 2. 提高使用频率（留存 → 实战 2.2）
└── 3. 增加触发概率（本策略重点）

策略一：事件驱动激活

策略二：周期性激活

每周激活节奏：
├── 周一：发送"上周市场回顾 + 本周展望"（借市场关注度）
├── 周三：推送"Gas 费周中低谷提醒"（利用 Gas 规律）
├── 周五：发送"周末交易攻略"（预热周末）
└── 低活跃期：启动积分/交易挖矿活动

策略三：沉默用户唤醒

用户沉默分层：
├── 7 天未交易 → 轻度提醒（市场动态推送）
├── 14 天未交易 → 中度激活（你的持仓变化 / 新功能上线）
├── 30 天未交易 → 重度唤醒（限时交易奖励 / 手续费折扣）
└── 90 天+ 未交易 → 可能已流失（不再主动触达，等待自然回流）

---

PM 视角总结

Web3 增长指标 vs Web2

方向二完成总结

三个项目串联：
═══════════════════════════════════════════════════════════

实战 2.1 获客分析
  → 用户从哪来？首次交互做什么？漏斗流失在哪？
  → PM 价值：知道在哪里获取用户，优化转化率

实战 2.2 留存分析
  → 用户能不能留住？高留存用户有什么特征？
  → PM 价值：找到 Aha Moment，设计留存策略

实战 2.3 活跃度分析
  → 用户多久来一次？什么驱动他们回来？
  → PM 价值：理解事件驱动特性，设计触发机制

完整增长漏斗：获客 → 激活 → 留存 → 活跃 → 变现
这三个项目覆盖了前四步
═══════════════════════════════════════════════════════════

---

面试题准备

Q: 如何衡量一个 DeFi 协议的增长健康度？

30 秒版本： 看三组指标 — (1) 获客效率：新用户获取成本和首次交易转化率（12% 是基线）；(2) 留存质量：Week4 Cohort 留存率（DEX 5-8%，借贷 15-20%），以及 Aha Moment 到达率；(3) 活跃粘性：DAU/MAU 比值（5-10% 对 DEX 正常）和事件响应弹性（大行情时 DAU 放大倍数）。健康的协议应该在非事件日也有稳定的基线活跃。

追问：如果 DAU 在涨但留存在降，说明什么？ → 说明在"灌水" — 大量新用户进来但留不住。常见于空投预期期间。要区分"有机增长"和"激励驱动增长"，看空投结束后的 DAU 回落幅度。如果回落 >80%，说明产品本身没有留存力。

---

下一步

完成方向二全部三个项目后，推荐继续：

• 方向三 项目 3.1：空投方案设计与模拟
• 方向四 项目 4.1：女巫地址识别规则设计

方向三的激励机制设计会教你如何用 Token 驱动增长，但又不沦为"灌水"。