【实战1.1】DEX用户分群画像：从数据到产品洞察

实战项目 1.1：DEX 用户分群画像

项目信息

项目编号：1.1
所属方向：交易行为分析
难度：⭐⭐ 入门
预计时间：3-4小时
前置技能：Dune SQL 基础（Day 12-13）

项目目标

> 通过链上数据对 DEX 用户进行分群，理解不同用户的行为差异，输出可落地的产品策略

产出清单：
├── ✅ Dune Dashboard：用户分群可视化
├── ✅ 产品洞察报告（1页）
└── ✅ 用户分层体验优化 PRD

Task 1：用户分群查询

核心 SQL

-- 项目1.1 Task 1: Uniswap 用户分群分析
-- 目标：按交易量对用户分层，统计各层特征

WITH user_trades AS (
  SELECT
    taker as user_address,
    COUNT(*) as trade_count,
    SUM(amount_usd) as total_volume,
    COUNT(DISTINCT DATE_TRUNC('day', block_time)) as active_days,
    MIN(block_time) as first_trade,
    MAX(block_time) as last_trade,
    AVG(amount_usd) as avg_trade_size
  FROM dex.trades
  WHERE
    block_time >= NOW() - INTERVAL '30' 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)'
    WHEN total_volume >= 1000 THEN '🦐 Small ($1K-$10K)'
    ELSE '🔬 Micro (<$1K)'
  END as user_tier,
  COUNT(*) as user_count,
  ROUND(SUM(total_volume), 0) as tier_total_volume,
  ROUND(100.0 * SUM(total_volume) / SUM(SUM(total_volume)) OVER(), 2) as volume_pct,
  ROUND(AVG(trade_count), 1) as avg_trades,
  ROUND(AVG(active_days), 1) as avg_active_days,
  ROUND(AVG(avg_trade_size), 0) as avg_trade_size
FROM user_trades
GROUP BY 1
ORDER BY tier_total_volume DESC

SQL 要点解析

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

1. WITH AS (CTE)
   └── 先计算每个用户的聚合数据，再分组

2. CASE WHEN 分层
   └── 按交易量划分5个层级
   └── 阈值设定：$1M / $100K / $10K / $1K

3. 窗口函数计算占比
   └── SUM(...) OVER() 计算总量
   └── 用于计算各层级的交易量占比

4. 多维度特征
   └── 交易次数、活跃天数、平均单笔金额
   └── 帮助理解不同层级用户的行为模式
═══════════════════════════════════════════════════════════

Task 2：分群特征深度分析

留存分析 SQL

-- Task 2: 各分群的周留存率
WITH user_first_week AS (
  SELECT
    taker as user_address,
    DATE_TRUNC('week', MIN(block_time)) as cohort_week,
    SUM(amount_usd) as first_week_volume
  FROM dex.trades
  WHERE
    block_time >= NOW() - INTERVAL '60' DAY
    AND project = 'uniswap'
    AND blockchain = 'ethereum'
  GROUP BY taker
),

user_tiers AS (
  SELECT
    user_address,
    cohort_week,
    CASE
      WHEN first_week_volume >= 100000 THEN 'Large+'
      WHEN first_week_volume >= 10000 THEN 'Medium'
      ELSE 'Small'
    END as tier
  FROM user_first_week
),

weekly_activity AS (
  SELECT
    t.taker as user_address,
    DATE_TRUNC('week', t.block_time) as activity_week
  FROM dex.trades t
  WHERE
    block_time >= NOW() - INTERVAL '60' DAY
    AND project = 'uniswap'
    AND blockchain = 'ethereum'
  GROUP BY 1, 2
)

SELECT
  ut.tier,
  ut.cohort_week,
  COUNT(DISTINCT ut.user_address) as cohort_size,
  COUNT(DISTINCT CASE WHEN wa.activity_week = ut.cohort_week + INTERVAL '1' WEEK
        THEN ut.user_address END) as week1_retained,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN wa.activity_week = ut.cohort_week + INTERVAL '1' WEEK
        THEN ut.user_address END) / COUNT(DISTINCT ut.user_address), 1) as week1_retention_pct
FROM user_tiers ut
LEFT JOIN weekly_activity wa ON ut.user_address = wa.user_address
GROUP BY 1, 2
ORDER BY ut.cohort_week DESC, ut.tier

行为模式分析 SQL

-- Task 2: 各分群的交易行为特征
WITH user_behavior AS (
  SELECT
    taker as user_address,
    SUM(amount_usd) as total_volume,
    COUNT(*) as trade_count,
    COUNT(DISTINCT token_bought_symbol) as unique_tokens_bought,
    COUNT(DISTINCT DATE_TRUNC('day', block_time)) as active_days,
    AVG(amount_usd) as avg_trade_size,
    STDDEV(amount_usd) as trade_size_stddev,
    MAX(block_time) - MIN(block_time) as trading_span
  FROM dex.trades
  WHERE
    block_time >= NOW() - INTERVAL '30' DAY
    AND project = 'uniswap'
    AND blockchain = 'ethereum'
    AND amount_usd > 0
  GROUP BY taker
)

SELECT
  CASE
    WHEN total_volume >= 100000 THEN 'Large+ (>$100K)'
    WHEN total_volume >= 10000 THEN 'Medium ($10K-$100K)'
    WHEN total_volume >= 1000 THEN 'Small ($1K-$10K)'
    ELSE 'Micro (<$1K)'
  END as tier,
  COUNT(*) as users,
  ROUND(AVG(trade_count), 1) as avg_trades,
  ROUND(AVG(unique_tokens_bought), 1) as avg_tokens,
  ROUND(AVG(active_days), 1) as avg_active_days,
  ROUND(AVG(avg_trade_size), 0) as avg_trade_size,
  ROUND(AVG(EXTRACT(DAY FROM trading_span)), 1) as avg_span_days
FROM user_behavior
GROUP BY 1
ORDER BY MIN(total_volume) DESC

Task 3：产品策略建议

分群特征总结

用户分群画像
═══════════════════════════════════════════════════════════

🐋 Whale（>$1M）
├── 特征：高频交易、多币种、长期活跃
├── 需求：低滑点、大额交易支持、API接入
├── 策略：VIP服务、专属客服、手续费折扣
└── 占比：<0.1%用户，>50%交易量

🦈 Large（$100K-$1M）
├── 特征：中高频、策略性交易、留存高
├── 需求：高级图表、限价单、组合交易
├── 策略：Pro版功能、社区特权
└── 占比：~1%用户，~30%交易量

🐟 Medium（$10K-$100K）
├── 特征：周活跃、尝试新币、价格敏感
├── 需求：Gas优化、新币发现、滑点保护
├── 策略：Gas补贴活动、空投资格
└── 占比：~5%用户，~15%交易量

🦐 Small（$1K-$10K）
├── 特征：月活跃、跟风交易、易流失
├── 需求：简单界面、教程引导、小额友好
├── 策略：新手任务、社区引导
└── 占比：~20%用户，~4%交易量

🔬 Micro（<$1K）
├── 特征：尝试性、单次交易、高流失
├── 需求：零门槛、快速上手
├── 策略：简化流程、不投入太多资源
└── 占比：~75%用户，<1%交易量
═══════════════════════════════════════════════════════════

产品优化 PRD 框架

## 用户分层体验优化 PRD

### 背景
通过链上数据分析发现，0.1%的Whale用户贡献50%+交易量，
但当前产品对不同用户群体缺乏差异化服务。

### 目标
- Whale留存率提升10%
- Large用户转化为Whale比例提升5%
- 新用户7日留存提升20%

### 方案

#### P0：Whale专属功能
- 大额交易拆单（减少滑点）
- 专属RPC节点（更快确认）
- API限额提升

#### P1：Large用户进阶
- Pro模式（高级图表、历史数据）
- 限价单功能
- 交易策略模板

#### P2：新手引导优化
- 首次交易Gas补贴
- 简化版界面（隐藏高级选项）
- 交互式教程

### 成功指标
- Whale月交易量（目标：+10%）
- Large→Whale转化率（目标：5%/月）
- 新用户7日留存（目标：25%）

关键收获

PM 核心洞察
═══════════════════════════════════════════════════════════

1. 80/20法则在Web3更极端
   └── 0.1%用户贡献50%+交易量
   └── 服务好头部用户是关键

2. 链上数据可以精准识别用户价值
   └── 不用猜测，直接看交易数据
   └── 可以做精准的用户分层运营

3. 不同用户需要不同产品体验
   └── Whale需要Pro功能
   └── 新手需要简化流程
   └── 一刀切的产品会流失两端用户

4. 数据驱动的产品决策
   └── 先分析数据，再提需求
   └── 用数据验证假设
═══════════════════════════════════════════════════════════

Dashboard 链接

创建完成后在此记录：
├── Dune Dashboard URL: [待填写]
├── 查询1 URL: [待填写]
├── 查询2 URL: [待填写]
└── 查询3 URL: [待填写]

下一步

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

• 项目 1.2：交易滑点与MEV影响分析
• 项目 2.1：新用户获取渠道分析

这两个项目会进一步加深对用户行为的理解。