【实战2.1】新用户获取渠道分析：链上漏斗与首次交互

实战项目 2.1：新用户获取渠道分析

项目信息

项目编号：2.1
所属方向：用户增长分析
难度：⭐⭐⭐ 进阶
预计时间：4-5小时
前置技能：Dune SQL 进阶（Day 31-32）、用户分群（实战1.1）

---

项目目标

分析新钱包的"入口"和首次交互行为，理解 DeFi 用户从哪里来、第一步做什么、多久完成首笔交易

产出清单：
├── ✅ Dune Dashboard：新用户获取渠道可视化
├── ✅ 用户转化漏斗图
└── ✅ 获客策略 PRD

---

Task 1：新钱包首次 DeFi 交互分析

核心 SQL — 新用户首次使用的 DeFi 协议

-- 项目2.1 Task 1: 新钱包首次 DeFi 交互分析
-- 目标：找到新钱包创建后第一个使用的 DeFi 协议

WITH wallet_first_tx AS (
  -- 找到每个钱包的首笔交易时间
  SELECT
    "from" as wallet,
    MIN(block_time) as first_tx_time
  FROM ethereum.transactions
  WHERE block_time >= NOW() - INTERVAL '30' DAY
    AND "from" NOT IN (
      SELECT address FROM ethereum.creation_traces
      WHERE block_time < NOW() - INTERVAL '90' DAY
    )
  GROUP BY "from"
),

first_defi AS (
  -- 找到每个新钱包的首次 DeFi 交互
  SELECT
    d.taker as wallet,
    d.project as first_defi_project,
    d.block_time as first_defi_time,
    d.amount_usd as first_trade_amount,
    ROW_NUMBER() OVER (PARTITION BY d.taker ORDER BY d.block_time) as rn
  FROM dex.trades d
  INNER JOIN wallet_first_tx w ON d.taker = w.wallet
  WHERE d.block_time BETWEEN w.first_tx_time AND w.first_tx_time + INTERVAL '7' DAY
    AND d.blockchain = 'ethereum'
    AND d.amount_usd > 0
)

SELECT
  first_defi_project,
  COUNT(*) as new_users,
  ROUND(AVG(first_trade_amount), 2) as avg_first_trade_usd,
  ROUND(MEDIAN(first_trade_amount), 2) as median_first_trade_usd,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER(), 2) as pct_of_new_users
FROM first_defi
WHERE rn = 1
GROUP BY first_defi_project
ORDER BY new_users DESC
LIMIT 15

SQL 要点解析

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

1. 新钱包识别
   └── 近 30 天首笔交易 + 排除 90 天前已存在的地址
   └── 平衡准确性和查询性能

2. 首次 DeFi 交互
   └── ROW_NUMBER() 窗口函数找第一笔
   └── 限定 7 天窗口（创建后一周内）

3. 聚合器 vs 原生协议
   └── dex.trades 的 project 字段区分
   └── 1inch/Paraswap 新用户 = 通过聚合器入场

4. 金额分析
   └── AVG vs MEDIAN 对比，避免鲸鱼偏差
═══════════════════════════════════════════════════════════

预期数据洞察

---

Task 2：转化漏斗分析

核心 SQL — 从创建钱包到首笔交易的漏斗

-- 项目2.1 Task 2: 新用户转化漏斗
-- 漏斗：创建钱包 → 首笔转账 → 首笔 Token 交互 → 首笔 DeFi 交易

WITH new_wallets AS (
  SELECT
    "from" as wallet,
    MIN(block_time) as first_tx_time,
    DATE_TRUNC('day', MIN(block_time)) as cohort_day
  FROM ethereum.transactions
  WHERE block_time >= NOW() - INTERVAL '14' DAY
  GROUP BY "from"
),

wallet_milestones AS (
  SELECT
    nw.wallet,
    nw.first_tx_time,
    nw.cohort_day,
    -- 里程碑1：首笔 ERC20 Transfer
    MIN(CASE WHEN et.evt_block_time IS NOT NULL
        THEN et.evt_block_time END) as first_token_transfer,
    -- 里程碑2：首笔 DEX 交易
    MIN(CASE WHEN dt.block_time IS NOT NULL
        THEN dt.block_time END) as first_dex_trade,
    -- 里程碑3：首笔借贷交互
    MAX(CASE WHEN dt.project IN ('aave', 'compound', 'morpho')
        THEN 1 ELSE 0 END) as has_lending
  FROM new_wallets nw
  LEFT JOIN erc20_ethereum.evt_Transfer et
    ON nw.wallet = et."from"
    AND et.evt_block_time BETWEEN nw.first_tx_time AND nw.first_tx_time + INTERVAL '7' DAY
  LEFT JOIN dex.trades dt
    ON nw.wallet = dt.taker
    AND dt.block_time BETWEEN nw.first_tx_time AND nw.first_tx_time + INTERVAL '7' DAY
    AND dt.blockchain = 'ethereum'
  GROUP BY nw.wallet, nw.first_tx_time, nw.cohort_day
)

SELECT
  cohort_day,
  COUNT(*) as total_new_wallets,
  COUNT(CASE WHEN first_token_transfer IS NOT NULL THEN 1 END) as did_token_transfer,
  COUNT(CASE WHEN first_dex_trade IS NOT NULL THEN 1 END) as did_dex_trade,
  COUNT(CASE WHEN has_lending = 1 THEN 1 END) as did_lending,
  -- 转化率
  ROUND(100.0 * COUNT(CASE WHEN first_token_transfer IS NOT NULL THEN 1 END)
    / COUNT(*), 1) as token_transfer_rate,
  ROUND(100.0 * COUNT(CASE WHEN first_dex_trade IS NOT NULL THEN 1 END)
    / COUNT(*), 1) as dex_trade_rate,
  ROUND(100.0 * COUNT(CASE WHEN has_lending = 1 THEN 1 END)
    / COUNT(*), 1) as lending_rate
FROM wallet_milestones
GROUP BY cohort_day
ORDER BY cohort_day DESC

预期漏斗数据

新用户 7 天内转化漏斗（估算）：

创建钱包          100%  ████████████████████  100,000
  ↓
首笔转账           65%  █████████████         65,000
  ↓                     ← 35% 流失：Gas 费门槛 / 不知道做什么
首笔 Token 操作    30%  ██████                30,000
  ↓                     ← 35% 流失：不理解 approve / 担心安全
首笔 DEX 交易      12%  ██                    12,000
  ↓                     ← 18% 流失：滑点困惑 / 不知道买什么
首笔借贷            3%  █                      3,000
                        ← 9% 流失：超额抵押概念 / 清算恐惧

---

Task 3：获客渠道归因与时间分析

核心 SQL — 首笔交易到首笔 DeFi 的时间分布

-- 项目2.1 Task 3: 从首笔交易到首笔 DeFi 的时间分析
WITH new_wallets AS (
  SELECT
    "from" as wallet,
    MIN(block_time) as first_tx_time
  FROM ethereum.transactions
  WHERE block_time >= NOW() - INTERVAL '30' DAY
  GROUP BY "from"
),

time_to_defi AS (
  SELECT
    nw.wallet,
    nw.first_tx_time,
    MIN(dt.block_time) as first_defi_time,
    DATE_DIFF('hour', nw.first_tx_time, MIN(dt.block_time)) as hours_to_first_defi
  FROM new_wallets nw
  INNER JOIN dex.trades dt
    ON nw.wallet = dt.taker
    AND dt.block_time > nw.first_tx_time
    AND dt.block_time <= nw.first_tx_time + INTERVAL '30' DAY
    AND dt.blockchain = 'ethereum'
  GROUP BY nw.wallet, nw.first_tx_time
)

SELECT
  CASE
    WHEN hours_to_first_defi < 1 THEN '< 1小时'
    WHEN hours_to_first_defi < 24 THEN '1-24小时'
    WHEN hours_to_first_defi < 72 THEN '1-3天'
    WHEN hours_to_first_defi < 168 THEN '3-7天'
    ELSE '7天+'
  END as time_bucket,
  COUNT(*) as user_count,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER(), 1) as pct
FROM time_to_defi
GROUP BY 1
ORDER BY MIN(hours_to_first_defi)

预期时间分布洞察

---

产品洞察报告

关键发现

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

1. 品牌 = 最大获客渠道
   Uniswap 占新用户首选 DEX 的 35-45%，品牌认知是
   Web3 获客的核心壁垒（vs Web2 的渠道投放）

2. 漏斗最大流失在"首笔转账→Token操作"
   65%→30% 的断崖下降说明：
   ├── approve 机制是新用户最大困惑点
   ├── "允许合约花费你的代币" 吓退大量用户
   └── 账户抽象（ERC-4337）能大幅改善这一步

3. 快速行动者 vs 慢热型是两类完全不同的用户
   1小时内交易 = 经验用户/空投猎人（不需要引导）
   1-3天交易 = 真正的新用户（需要教育+安全感）

4. 聚合器是隐藏的获客渠道
   15-20% 新用户首选聚合器（1inch/CoW），说明
   "最优价格"对新用户的吸引力 > 品牌忠诚度
═══════════════════════════════════════════════════════════

PM 产品策略建议

---

获客策略 PRD

PRD：DeFi 新手引导优化

产品名称：Smart Onboarding Flow
目标：将新用户 7 天内 DEX 交易转化率从 12% 提升到 25%

用户旅程优化：
Step 1：钱包连接后 → 显示"1 分钟完成首笔交易"引导
Step 2：自动推荐低 Gas 时段 → 降低首次成本
Step 3：合并 approve+swap → 一步完成（AA 钱包）
Step 4：首笔交易后 → 推荐下一步操作（存款/LP）
Step 5：3 天未操作 → 推送教育邮件/通知

成功指标：
├── 首笔 DEX 交易转化率：12% → 25%
├── 创建到首笔交易时间：中位数 18h → 6h
└── 7 天留存率：8% → 15%

---

面试题准备

Q: 如何分析 DeFi 协议的用户获取效率？

30 秒版本： 看三个维度 — (1) 新用户首选协议占比（品牌力）；(2) 创建钱包到首笔交易的时间（摩擦度）；(3) 转化漏斗各步骤流失率（优化点）。核心洞察：Web3 获客的最大瓶颈不是"找到用户"而是"让用户完成第一步"。

追问：和 Web2 获客有什么区别？ → Web2 看渠道 ROI（SEM/SEO/社交），Web3 看链上行为路径。Web2 能直接触达用户（邮件/推送），Web3 只能通过 Token 激励和社区间接获客。

---

下一步

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

• 项目 2.2：用户留存与生命周期分析
• 项目 3.1：空投方案设计与模拟

留存分析会帮你理解"获取来的用户能不能留住"。