【实战1.3】协议健康度监控：用数据评估DeFi协议

实战项目 1.3：协议健康度监控

项目信息

项目编号：1.3
所属方向：数据分析
难度：⭐⭐⭐ 中级
预计时间：5-6小时
前置技能：Dune SQL 进阶、实战 1.1-1.2 完成

项目目标

> 构建一套 DeFi 协议健康度评估框架，通过链上数据量化分析协议的运营状况，为投资决策和产品设计提供数据支撑

为什么要监控协议健康度？
═══════════════════════════════════════════════════════════

对投资者：
├── 发现被低估的优质协议
├── 预警潜在风险（TVL 流失、用户下降）
└── 验证叙事是否有数据支撑

对 PM/Builder：
├── 了解竞品真实运营数据
├── 学习成功协议的增长模式
├── 为产品决策提供数据依据

对协议方：
├── 监控自身运营健康度
├── 发现增长瓶颈
└── 向社区展示透明数据

核心问题：
"这个协议是在真正增长，还是只是靠激励维持？"
═══════════════════════════════════════════════════════════

健康度评估框架

五维评估模型

协议健康度五维模型
═══════════════════════════════════════════════════════════

                    ┌─────────────┐
                    │   TVL 规模   │
                    │   (资金量)   │
                    └──────┬──────┘
                           │
    ┌──────────────────────┼──────────────────────┐
    │                      │                      │
┌───┴───┐            ┌─────┴─────┐           ┌────┴────┐
│用户活跃│            │  收入能力  │           │资本效率 │
│ (MAU)  │            │ (Revenue) │           │(效率)   │
└───┬───┘            └─────┬─────┘           └────┬────┘
    │                      │                      │
    └──────────────────────┼──────────────────────┘
                           │
                    ┌──────┴──────┐
                    │  增长趋势   │
                    │  (Growth)   │
                    └─────────────┘

五个维度：
1. TVL（Total Value Locked）- 锁仓资金量
2. 用户活跃度 - DAU/MAU、新用户、留存率
3. 收入能力 - 协议收入、手续费收入
4. 资本效率 - TVL 利用率、收入/TVL 比率
5. 增长趋势 - 各指标的变化趋势
═══════════════════════════════════════════════════════════

指标定义

核心指标定义
═══════════════════════════════════════════════════════════

1. TVL 相关
   ├── TVL: 协议中锁定的资产总价值
   ├── TVL 变化率: (今日TVL - 昨日TVL) / 昨日TVL
   └── TVL 集中度: Top10 地址占总 TVL 的比例

2. 用户相关
   ├── DAU: 每日活跃用户数（有交互的独立地址）
   ├── MAU: 月活跃用户数
   ├── 新用户: 首次与协议交互的地址
   ├── 留存率: 第N天还活跃的用户比例
   └── 用户增长率: (本周新用户 - 上周新用户) / 上周新用户

3. 收入相关
   ├── 协议收入: 协议国库获得的收入
   ├── 手续费收入: 用户支付的总手续费
   ├── LP 收入: 流动性提供者获得的收入
   └── 收入增长率: 周环比/月环比

4. 效率相关
   ├── 资本效率: 交易量 / TVL
   ├── 收入效率: 协议收入 / TVL
   └── 用户价值: 收入 / MAU

5. 趋势相关
   ├── 7日趋势: 指标的7日移动平均
   ├── 30日趋势: 指标的30日移动平均
   └── 趋势方向: 上升/下降/平稳
═══════════════════════════════════════════════════════════

任务分解

实战任务清单
═══════════════════════════════════════════════════════════

Task 1: TVL 分析
├── 查询协议历史 TVL
├── 分析 TVL 变化趋势
├── 计算 TVL 集中度
└── 交付：TVL 趋势图 + 分析

Task 2: 用户分析
├── 计算 DAU/MAU
├── 分析新用户增长
├── 计算用户留存率
└── 交付：用户增长看板

Task 3: 收入分析
├── 查询协议收入数据
├── 分析收入构成
├── 计算收入效率
└── 交付：收入分析报告

Task 4: 健康度评分
├── 整合各维度数据
├── 建立评分模型
├── 生成健康度报告
└── 交付：协议健康度 Dashboard

═══════════════════════════════════════════════════════════

Task 1: TVL 分析

查询协议 TVL 历史

-- 查询 Uniswap V3 的 TVL 历史（使用 DeFiLlama 数据）
-- 注意：Dune 的 TVL 数据可能需要自己计算

SELECT
    day,
    total_liquidity_usd as tvl,
    -- 计算日变化
    total_liquidity_usd - LAG(total_liquidity_usd) OVER (ORDER BY day) as tvl_change,
    -- 计算变化率
    (total_liquidity_usd - LAG(total_liquidity_usd) OVER (ORDER BY day))
        / NULLIF(LAG(total_liquidity_usd) OVER (ORDER BY day), 0) * 100 as change_pct
FROM uniswap_v3_ethereum.pool_stats_daily
WHERE day >= NOW() - INTERVAL '90' DAY
ORDER BY day DESC

自己计算 TVL（更精确）

-- 从池子余额计算 TVL

WITH pool_balances AS (
    SELECT
        DATE_TRUNC('day', block_time) as day,
        pool,
        token0,
        token1,
        -- 这里需要结合价格计算
        amount0 * price0 + amount1 * price1 as pool_tvl
    FROM uniswap_v3_ethereum.pools p
    JOIN prices.usd_daily pr ON ...
    WHERE block_time >= NOW() - INTERVAL '90' DAY
)

SELECT
    day,
    SUM(pool_tvl) as total_tvl,
    COUNT(DISTINCT pool) as active_pools
FROM pool_balances
GROUP BY day
ORDER BY day DESC

TVL 集中度分析

-- 分析 TVL 来源集中度

WITH provider_tvl AS (
    SELECT
        liquidity_provider,
        SUM(liquidity_usd) as total_provided
    FROM uniswap_v3_ethereum.positions
    WHERE block_time >= NOW() - INTERVAL '30' DAY
    GROUP BY liquidity_provider
),

ranked AS (
    SELECT
        liquidity_provider,
        total_provided,
        total_provided * 100.0 / SUM(total_provided) OVER () as pct_of_total,
        ROW_NUMBER() OVER (ORDER BY total_provided DESC) as rank
    FROM provider_tvl
)

SELECT
    -- Top 10 集中度
    SUM(CASE WHEN rank <= 10 THEN pct_of_total ELSE 0 END) as top10_concentration,
    -- Top 50 集中度
    SUM(CASE WHEN rank <= 50 THEN pct_of_total ELSE 0 END) as top50_concentration,
    -- 总LP数量
    COUNT(*) as total_lp_count
FROM ranked

-- 集中度解读：
-- Top10 > 50%: 高度集中，有撤资风险
-- Top10 30-50%: 中等集中
-- Top10 < 30%: 去中心化程度较好

Task 2: 用户分析

DAU/MAU 计算

-- 计算协议的 DAU

SELECT
    DATE_TRUNC('day', block_time) as day,
    COUNT(DISTINCT tx_from) as dau
FROM uniswap_v3_ethereum.trades
WHERE block_time >= NOW() - INTERVAL '30' DAY
GROUP BY 1
ORDER BY 1 DESC

-- 计算 MAU 和 DAU/MAU 比率

WITH daily_users AS (
    SELECT
        DATE_TRUNC('day', block_time) as day,
        tx_from as user
    FROM uniswap_v3_ethereum.trades
    WHERE block_time >= NOW() - INTERVAL '60' DAY
),

metrics AS (
    SELECT
        DATE_TRUNC('month', day) as month,
        COUNT(DISTINCT user) as mau,
        COUNT(DISTINCT CASE WHEN day = DATE_TRUNC('day', NOW()) THEN user END) as today_dau
    FROM daily_users
    GROUP BY 1
)

SELECT
    month,
    mau,
    today_dau,
    ROUND(today_dau * 100.0 / mau, 2) as dau_mau_ratio
FROM metrics
ORDER BY month DESC

-- DAU/MAU 解读：
-- > 20%: 用户粘性很强
-- 10-20%: 用户粘性良好
-- < 10%: 用户粘性较弱

新用户增长分析

-- 识别新用户（首次交互）

WITH first_interaction AS (
    SELECT
        tx_from as user,
        MIN(DATE_TRUNC('day', block_time)) as first_day
    FROM uniswap_v3_ethereum.trades
    GROUP BY tx_from
)

SELECT
    first_day,
    COUNT(*) as new_users,
    SUM(COUNT(*)) OVER (ORDER BY first_day) as cumulative_users
FROM first_interaction
WHERE first_day >= NOW() - INTERVAL '90' DAY
GROUP BY first_day
ORDER BY first_day DESC

用户留存率计算

-- 计算用户留存率（Day 1, 7, 30）

WITH user_cohorts AS (
    SELECT
        tx_from as user,
        MIN(DATE_TRUNC('day', block_time)) as cohort_day
    FROM uniswap_v3_ethereum.trades
    GROUP BY tx_from
),

user_activity AS (
    SELECT DISTINCT
        tx_from as user,
        DATE_TRUNC('day', block_time) as active_day
    FROM uniswap_v3_ethereum.trades
),

retention AS (
    SELECT
        c.cohort_day,
        COUNT(DISTINCT c.user) as cohort_size,
        COUNT(DISTINCT CASE WHEN a.active_day = c.cohort_day + INTERVAL '1' DAY
            THEN c.user END) as day1_retained,
        COUNT(DISTINCT CASE WHEN a.active_day = c.cohort_day + INTERVAL '7' DAY
            THEN c.user END) as day7_retained,
        COUNT(DISTINCT CASE WHEN a.active_day = c.cohort_day + INTERVAL '30' DAY
            THEN c.user END) as day30_retained
    FROM user_cohorts c
    LEFT JOIN user_activity a ON c.user = a.user
    WHERE c.cohort_day >= NOW() - INTERVAL '60' DAY
        AND c.cohort_day <= NOW() - INTERVAL '30' DAY  -- 确保有30天数据
    GROUP BY c.cohort_day
)

SELECT
    cohort_day,
    cohort_size,
    ROUND(day1_retained * 100.0 / cohort_size, 2) as day1_retention_pct,
    ROUND(day7_retained * 100.0 / cohort_size, 2) as day7_retention_pct,
    ROUND(day30_retained * 100.0 / cohort_size, 2) as day30_retention_pct
FROM retention
ORDER BY cohort_day DESC

-- 留存率基准（DeFi）：
-- Day 1: 15-25% 良好
-- Day 7: 8-15% 良好
-- Day 30: 3-8% 良好

Task 3: 收入分析

协议收入查询

-- 查询 Uniswap 协议费用收入
-- 注意：Uniswap V3 协议费用开关由治理控制

SELECT
    DATE_TRUNC('day', block_time) as day,
    SUM(fee_usd) as total_fees,
    -- Uniswap 目前不收取协议费，全部给 LP
    SUM(fee_usd) as lp_revenue,
    0 as protocol_revenue  -- 如果开启协议费，这里会有值
FROM uniswap_v3_ethereum.trades
WHERE block_time >= NOW() - INTERVAL '30' DAY
GROUP BY 1
ORDER BY 1 DESC

收入效率分析

-- 计算收入效率指标

WITH daily_metrics AS (
    SELECT
        DATE_TRUNC('day', block_time) as day,
        SUM(amount_usd) as volume,
        SUM(fee_usd) as fees
    FROM uniswap_v3_ethereum.trades
    WHERE block_time >= NOW() - INTERVAL '30' DAY
    GROUP BY 1
),

tvl_data AS (
    SELECT
        day,
        tvl
    FROM uniswap_v3_ethereum.tvl_daily
    WHERE day >= NOW() - INTERVAL '30' DAY
)

SELECT
    d.day,
    d.volume,
    d.fees,
    t.tvl,
    -- 资本效率：交易量 / TVL
    ROUND(d.volume / NULLIF(t.tvl, 0), 2) as capital_efficiency,
    -- 费率：手续费 / 交易量
    ROUND(d.fees * 100.0 / NULLIF(d.volume, 0), 4) as fee_rate_pct,
    -- 收入效率：年化费用 / TVL
    ROUND(d.fees * 365 * 100.0 / NULLIF(t.tvl, 0), 2) as annualized_fee_yield_pct
FROM daily_metrics d
JOIN tvl_data t ON d.day = t.day
ORDER BY d.day DESC

-- 资本效率解读：
-- > 1: 每天交易量超过 TVL，资金利用率高
-- 0.5-1: 资金利用率良好
-- < 0.5: 资金利用率较低

Task 4: 健康度评分模型

评分算法

健康度评分算法
═══════════════════════════════════════════════════════════

总分 100 分，五个维度各 20 分：

1. TVL 得分 (20分)
   ├── TVL 规模: 0-10分
   │   └── >$1B: 10分, >$100M: 7分, >$10M: 4分, <$10M: 1分
   └── TVL 趋势: 0-10分
       └── 增长>10%: 10分, 增长0-10%: 7分, 下降<10%: 4分, 下降>10%: 1分

2. 用户得分 (20分)
   ├── MAU 规模: 0-10分
   │   └── >100K: 10分, >10K: 7分, >1K: 4分, <1K: 1分
   └── 用户增长: 0-10分
       └── 增长>20%: 10分, 增长0-20%: 7分, 下降<20%: 4分, 下降>20%: 1分

3. 收入得分 (20分)
   ├── 收入规模: 0-10分
   │   └── >$1M/月: 10分, >$100K: 7分, >$10K: 4分, <$10K: 1分
   └── 收入趋势: 0-10分
       └── 与 TVL 趋势评分类似

4. 效率得分 (20分)
   ├── 资本效率: 0-10分
   │   └── >1: 10分, >0.5: 7分, >0.1: 4分, <0.1: 1分
   └── 留存率: 0-10分
       └── Day7>15%: 10分, >10%: 7分, >5%: 4分, <5%: 1分

5. 风险得分 (20分)
   ├── TVL 集中度: 0-10分
   │   └── Top10<30%: 10分, <50%: 7分, <70%: 4分, >70%: 1分
   └── 审计/安全: 0-10分
       └── 多次审计+无事故: 10分, 有审计: 7分, 无审计: 2分

总分解读：
├── 80-100: 优秀，健康度很高
├── 60-80: 良好，值得关注
├── 40-60: 一般，存在风险
└── <40: 较差，谨慎参与
═══════════════════════════════════════════════════════════

SQL 实现评分

-- 健康度评分 SQL 示例

WITH tvl_metrics AS (
    -- TVL 相关指标
    SELECT
        tvl,
        tvl_change_pct_7d,
        CASE
            WHEN tvl > 1000000000 THEN 10
            WHEN tvl > 100000000 THEN 7
            WHEN tvl > 10000000 THEN 4
            ELSE 1
        END as tvl_size_score,
        CASE
            WHEN tvl_change_pct_7d > 10 THEN 10
            WHEN tvl_change_pct_7d > 0 THEN 7
            WHEN tvl_change_pct_7d > -10 THEN 4
            ELSE 1
        END as tvl_trend_score
    FROM protocol_tvl
),

user_metrics AS (
    -- 用户相关指标
    SELECT
        mau,
        user_growth_pct,
        day7_retention,
        CASE
            WHEN mau > 100000 THEN 10
            WHEN mau > 10000 THEN 7
            WHEN mau > 1000 THEN 4
            ELSE 1
        END as user_size_score,
        CASE
            WHEN user_growth_pct > 20 THEN 10
            WHEN user_growth_pct > 0 THEN 7
            WHEN user_growth_pct > -20 THEN 4
            ELSE 1
        END as user_growth_score
    FROM protocol_users
),

-- 汇总计算总分
final_score AS (
    SELECT
        (tvl_size_score + tvl_trend_score) as tvl_score,
        (user_size_score + user_growth_score) as user_score,
        -- ... 其他维度
        (tvl_size_score + tvl_trend_score +
         user_size_score + user_growth_score +
         -- ... 其他维度得分
        ) as total_score
    FROM tvl_metrics, user_metrics
)

SELECT
    total_score,
    CASE
        WHEN total_score >= 80 THEN '优秀'
        WHEN total_score >= 60 THEN '良好'
        WHEN total_score >= 40 THEN '一般'
        ELSE '较差'
    END as health_grade
FROM final_score

Dashboard 设计

协议健康度 Dashboard 结构
═══════════════════════════════════════════════════════════

页面布局：
┌─────────────────────────────────────────────────────────┐
│  协议名称: Uniswap V3          健康度评分: 85/100 [优秀] │
├─────────────────────────────────────────────────────────┤
│                                                         │
│  ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐       │
│  │  TVL    │ │  MAU    │ │  收入   │ │ 资本效率│       │
│  │ $2.5B   │ │  125K   │ │ $15M/月 │ │  0.85   │       │
│  │ ↑ 5.2%  │ │ ↑ 12%   │ │ ↑ 8%    │ │ ↑ 0.1   │       │
│  └─────────┘ └─────────┘ └─────────┘ └─────────┘       │
│                                                         │
├─────────────────────────────────────────────────────────┤
│                                                         │
│  TVL 趋势 (90天)              │  用户增长 (90天)        │
│  ┌───────────────────┐       │  ┌───────────────────┐  │
│  │ 📈                │       │  │ 📈                │  │
│  │                   │       │  │                   │  │
│  └───────────────────┘       │  └───────────────────┘  │
│                                                         │
├─────────────────────────────────────────────────────────┤
│                                                         │
│  五维雷达图                   │  评分明细               │
│  ┌───────────────────┐       │  ┌───────────────────┐  │
│  │      TVL          │       │  │ TVL:     18/20    │  │
│  │    ╱    ╲         │       │  │ 用户:   16/20    │  │
│  │ 风险     用户     │       │  │ 收入:   17/20    │  │
│  │    ╲    ╱         │       │  │ 效率:   18/20    │  │
│  │     效率          │       │  │ 风险:   16/20    │  │
│  └───────────────────┘       │  └───────────────────┘  │
│                                                         │
└─────────────────────────────────────────────────────────┘

═══════════════════════════════════════════════════════════

协议对比分析

对比多个 DEX

-- 对比主流 DEX 的健康度指标

SELECT
    project as dex_name,
    SUM(amount_usd) as volume_30d,
    COUNT(DISTINCT tx_from) as users_30d,
    SUM(fee_usd) as fees_30d,
    SUM(amount_usd) / COUNT(DISTINCT tx_from) as volume_per_user
FROM dex.trades
WHERE block_time >= NOW() - INTERVAL '30' DAY
    AND project IN ('uniswap', 'sushiswap', 'curve', 'balancer')
GROUP BY project
ORDER BY volume_30d DESC

PM 产品思考

健康度监控的产品应用
═══════════════════════════════════════════════════════════

1. 投资研究工具
   ├── 帮助用户评估协议风险
   ├── 发现被低估的协议
   └── 预警衰退中的协议

2. 协议自监控
   ├── 运营数据透明化
   ├── 发现增长瓶颈
   └── 社区信任建设

3. 聚合器优化
   ├── 根据健康度分配流量
   ├── 风险提示
   └── 智能推荐

4. 产品设计启示
   ├── 关注真正的用户留存
   ├── 收入可持续性 > 短期激励
   └── TVL 质量 > TVL 数量
═══════════════════════════════════════════════════════════

交付检查清单

项目交付要求
═══════════════════════════════════════════════════════════

□ Task 1: TVL 分析
  ├── □ 90天 TVL 趋势图
  ├── □ TVL 集中度分析
  └── □ TVL 评分

□ Task 2: 用户分析
  ├── □ DAU/MAU 趋势
  ├── □ 新用户增长曲线
  ├── □ 留存率计算
  └── □ 用户评分

□ Task 3: 收入分析
  ├── □ 收入趋势
  ├── □ 收入效率指标
  └── □ 收入评分

□ Task 4: 健康度 Dashboard
  ├── □ 五维评分实现
  ├── □ 雷达图/评分展示
  ├── □ 至少对比 3 个协议
  └── □ Dashboard 链接

加分项：
□ 实现健康度预警功能
□ 添加历史评分趋势
□ 与 DeFiLlama 数据对比验证
═══════════════════════════════════════════════════════════

学习资源

方向1完成总结

数据分析方向完成 ✅
═══════════════════════════════════════════════════════════

实战 1.1: DEX 用户分群 ✅
├── 用户分层方法
├── 行为特征分析
└── 产品策略建议

实战 1.2: 巨鲸钱包追踪 ✅
├── 巨鲸识别方法
├── 持仓/交易分析
└── 聪明钱跟踪

实战 1.3: 协议健康度监控 ✅
├── 五维评估框架
├── 核心指标计算
└── 健康度评分模型

下一方向预告：
方向2: 增长策略分析
├── 实战 2.1: 空投效果评估
├── 实战 2.2: 积分系统分析
└── 实战 2.3: 用户获取成本(CAC)分析
═══════════════════════════════════════════════════════════