礼包码报表：数据驱动运营决策（深度技术篇）

本文是礼包码系列的第6篇，也是最后一篇。前五篇我们分别介绍了礼包码的基础架构、生成算法、批量管理、兑换事务和历史追溯。今天，我们来聊聊整个系统的"眼睛"——报表系统，看看如何通过数据驱动运营决策。

一、礼包码数据的价值

运营活动的效果如何？玩家喜欢什么奖励？渠道投放的ROI怎样？

这些问题，光靠"感觉"是回答不了的。

礼包码系统的真正价值，不在于它能发出多少礼包，而在于它能为运营决策提供什么数据支撑。每一次兑换、每一个码的生成和使用，都是一颗数据珍珠。把它们串起来，就是一条完整的运营洞察项链。

• 活动效果：投放了10000个码，实际兑换了多少？兑换率是5%还是80%？这直接反映了活动的吸引力。
• 渠道质量：A渠道的码被抢光了，B渠道还有一半没用完。下次投放，资源往哪里倾斜？
• 玩家行为：兑换高峰期在什么时候？凌晨还是晚上？工作日还是周末？这决定了推送时机。
• 风险预警：某个码在1分钟内被兑换了100次？异常行为，需要介入调查。

传统的运营决策，往往依赖经验："我觉得这个活动会火"、"上次类似的活动效果不错"。但经验有局限性——市场在变，玩家在变，你的经验可能已经过时了。

数据驱动的运营，是让数字说话。不是"我觉得"，而是"数据显示"。这不是否定经验的价值，而是用数据验证经验，用数据修正偏见。

每一次活动，都是一次数据积累。今年的"春节活动"数据，可以和去年对比；今年的"618活动"数据，可以指导明年的策略规划。

数据越积累，价值越大。一个运行了三年的礼包码系统，沉淀下来的不只是几百万条兑换记录，而是对用户行为、市场规律的深刻理解。这些理解，是竞争对手短期内无法复制的护城河。

第一层：看数据。知道发生了什么，这是最基础的。 第二层：懂原因。知道为什么发生，这需要分析能力。 第三层：能预测。知道将要发生什么，这需要建模能力。

大多数团队停留在第一层，少数团队能到第二层，能到第三层的凤毛麟角。报表系统的目标，是帮助团队从第一层走向第二层，最终达到第三层。

二、数据仓库架构设计

在深入指标和报表之前，我们需要先理解数据是如何被组织和存储的。这就像建房子，先要打好地基。

2.1 数据分层架构

现代数据仓库通常采用分层架构，把数据按照处理阶段分成不同的层次：

┌─────────────────────────────────────────────────────────────┐
│                      ADS (应用数据层)                         │
│   ┌─────────┐  ┌─────────┐  ┌─────────┐  ┌─────────┐        │
│   │ 运营报表 │  │ 实时监控 │  │ 数据API  │  │ 导出服务 │        │
│   └─────────┘  └─────────┘  └─────────┘  └─────────┘        │
├─────────────────────────────────────────────────────────────┤
│                      DWS (汇总数据层)                         │
│   ┌─────────────┐  ┌─────────────┐  ┌─────────────┐         │
│   │ 活动日汇总表 │  │ 渠道日汇总表 │  │ 用户日汇总表 │         │
│   └─────────────┘  └─────────────┘  └─────────────┘         │
├─────────────────────────────────────────────────────────────┤
│                      DWD (明细数据层)                         │
│   ┌─────────────┐  ┌─────────────┐  ┌─────────────┐         │
│   │ 兑换明细宽表 │  │ 码生命周期表 │  │ 用户行为表  │         │
│   └─────────────┘  └─────────────┘  └─────────────┘         │
├─────────────────────────────────────────────────────────────┤
│                      ODS (原始数据层)                         │
│   ┌─────────────┐  ┌─────────────┐  ┌─────────────┐         │
│   │ 兑换流水表  │  │ 码生成记录表 │  │ 用户基础表  │         │
│   └─────────────┘  └─────────────┘  └─────────────┘         │
└─────────────────────────────────────────────────────────────┘

这一层直接同步业务数据库的原始数据，保持和业务系统一致的结构。可以把它想象成一个"数据备份仓库"，不做任何加工处理。

-- ODS层：兑换流水表（原始数据）
CREATE TABLE ods_gift_code_redeem_log (
    id              BIGINT PRIMARY KEY,
    code_id         BIGINT NOT NULL COMMENT '礼包码ID',
    code            VARCHAR(32) NOT NULL COMMENT '礼包码',
    user_id         BIGINT NOT NULL COMMENT '用户ID',
    activity_id     BIGINT NOT NULL COMMENT '活动ID',
    channel_id      BIGINT COMMENT '渠道ID',
    reward_id       BIGINT NOT NULL COMMENT '奖励配置ID',
    redeem_time     DATETIME NOT NULL COMMENT '兑换时间',
    redeem_ip       VARCHAR(45) COMMENT '兑换IP',
    device_id       VARCHAR(64) COMMENT '设备ID',
    status          TINYINT NOT NULL COMMENT '状态：1成功 2失败',
    fail_reason     VARCHAR(255) COMMENT '失败原因',
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_code_id (code_id),
    INDEX idx_user_id (user_id),
    INDEX idx_activity_id (activity_id),
    INDEX idx_redeem_time (redeem_time)
) COMMENT '兑换流水原始表';

这一层对原始数据进行清洗、转换和整合，形成宽表。宽表的意思是把多个相关表的数据合并到一起，方便后续分析。

-- DWD层：兑换明细宽表（经过清洗和关联）
CREATE TABLE dwd_gift_code_redeem_detail (
    -- 兑换基础信息
    redeem_id           BIGINT PRIMARY KEY,
    code_id             BIGINT NOT NULL,
    code                VARCHAR(32) NOT NULL,
    user_id             BIGINT NOT NULL,
    
    -- 活动维度
    activity_id         BIGINT NOT NULL,
    activity_name       VARCHAR(128) COMMENT '活动名称',
    activity_type       VARCHAR(32) COMMENT '活动类型：NEW_USER/DAILY/CHANNEL',
    activity_start_time DATETIME COMMENT '活动开始时间',
    activity_end_time   DATETIME COMMENT '活动结束时间',
    
    -- 渠道维度
    channel_id          BIGINT,
    channel_name        VARCHAR(64) COMMENT '渠道名称',
    channel_type        VARCHAR(32) COMMENT '渠道类型：SOCIAL/ADS/PARTNER',
    
    -- 奖励维度
    reward_id           BIGINT NOT NULL,
    reward_name         VARCHAR(128) COMMENT '奖励名称',
    reward_type         VARCHAR(32) COMMENT '奖励类型：GOLD/ITEM/SKIN',
    reward_value        DECIMAL(18,2) COMMENT '奖励价值（等价人民币）',
    
    -- 用户维度
    user_register_time  DATETIME COMMENT '用户注册时间',
    user_level          INT COMMENT '用户等级',
    user_vip_level      INT COMMENT 'VIP等级',
    is_new_user         TINYINT COMMENT '是否新用户（注册7天内）',
    user_total_recharge DECIMAL(18,2) COMMENT '历史充值总额',
    
    -- 时间维度
    redeem_time         DATETIME NOT NULL,
    redeem_date         DATE COMMENT '兑换日期',
    redeem_hour         TINYINT COMMENT '兑换小时（0-23）',
    day_of_week         TINYINT COMMENT '星期几（1-7）',
    is_weekend          TINYINT COMMENT '是否周末',
    
    -- 设备和位置
    redeem_ip           VARCHAR(45),
    device_id           VARCHAR(64),
    device_type         VARCHAR(32) COMMENT '设备类型：iOS/Android',
    province            VARCHAR(32) COMMENT '省份',
    city                VARCHAR(32) COMMENT '城市',
    
    -- 兑换结果
    status              TINYINT NOT NULL,
    fail_reason         VARCHAR(255),
    
    -- ETL字段
    etl_time            DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_activity_date (activity_id, redeem_date),
    INDEX idx_channel_date (channel_id, redeem_date),
    INDEX idx_user_date (user_id, redeem_date),
    INDEX idx_redeem_date (redeem_date)
) COMMENT '兑换明细宽表';

这一层对明细数据进行聚合汇总，按照不同的分析维度预先计算好指标。这样查询报表时就不需要每次都扫描大量明细数据，性能会好很多。

-- DWS层：活动日汇总表
CREATE TABLE dws_activity_daily_summary (
    activity_id         BIGINT NOT NULL,
    stat_date           DATE NOT NULL,
    
    -- 码的统计
    total_codes         INT COMMENT '累计生成码数',
    available_codes     INT COMMENT '可用码数',
    redeemed_codes      INT COMMENT '已兑换码数',
    code_usage_rate     DECIMAL(5,2) COMMENT '码使用率（%）',
    
    -- 兑换统计
    total_redeems       INT COMMENT '总兑换次数',
    success_redeems     INT COMMENT '成功兑换次数',
    fail_redeems        INT COMMENT '失败兑换次数',
    redeem_success_rate DECIMAL(5,2) COMMENT '兑换成功率（%）',
    
    -- 用户统计
    unique_users        INT COMMENT '独立兑换用户数',
    new_users           INT COMMENT '新用户数',
    old_users           INT COMMENT '老用户数',
    new_user_ratio      DECIMAL(5,2) COMMENT '新用户占比（%）',
    avg_redeems_per_user DECIMAL(5,2) COMMENT '人均兑换次数',
    
    -- 时间统计
    peak_hour           TINYINT COMMENT '峰值兑换小时',
    peak_hour_redeems   INT COMMENT '峰值小时兑换量',
    avg_redeem_interval INT COMMENT '平均兑换间隔（秒）',
    
    -- 价值统计
    total_reward_value  DECIMAL(18,2) COMMENT '发放奖励总价值',
    avg_reward_value    DECIMAL(18,2) COMMENT '平均奖励价值',
    
    -- 同环比
    redeem_dod          DECIMAL(5,2) COMMENT '兑换量日环比（%）',
    redeem_wow          DECIMAL(5,2) COMMENT '兑换量周同比（%）',
    
    -- ETL字段
    etl_time            DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (activity_id, stat_date),
    INDEX idx_stat_date (stat_date)
) COMMENT '活动日汇总表';

这一层是直接面向报表和API的数据层，数据格式完全按照前端展示需求来设计。

-- ADS层：实时监控指标表
CREATE TABLE ads_realtime_monitor (
    metric_name         VARCHAR(64) PRIMARY KEY,
    metric_value        DECIMAL(18,2),
    metric_unit         VARCHAR(16),
    compare_value       DECIMAL(18,2) COMMENT '对比值（昨日同期/上周同期）',
    change_rate         DECIMAL(5,2) COMMENT '变化率（%）',
    trend               VARCHAR(8) COMMENT '趋势：UP/DOWN/FLAT',
    status              VARCHAR(16) COMMENT '状态：NORMAL/WARNING/ALERT',
    update_time         DATETIME,
    
    INDEX idx_update_time (update_time)
) COMMENT '实时监控指标表';

2.2 数据流转管道

数据从业务系统流向报表系统，需要经过一个ETL（Extract-Transform-Load）管道：

┌──────────────┐    ┌──────────────┐    ┌──────────────┐    ┌──────────────┐
│   业务数据库   │───▶│   消息队列    │───▶│  流处理引擎   │───▶│   实时数仓    │
│   (MySQL)    │    │  (Kafka)     │    │ (Flink)     │    │  (ClickHouse) │
└──────────────┘    └──────────────┘    └──────────────┘    └──────────────┘
       │                                                           │
       │                    ┌──────────────┐                       │
       └───────────────────▶│   批处理引擎  │◀──────────────────────┘
                            │   (Spark)    │
                            └──────────────┘
                                   │
                                   ▼
                            ┌──────────────┐
                            │   离线数仓    │
                            │  (Hive/Doris) │
                            └──────────────┘

2.3 数据质量保障

数据质量是报表系统的生命线。垃圾进，垃圾出——如果源数据有问题，再漂亮的报表也是误导。

-- 数据完整性检查：是否有丢失数据
SELECT 
    DATE(redeem_time) as stat_date,
    COUNT(*) as record_count,
    COUNT(DISTINCT user_id) as unique_users
FROM ods_gift_code_redeem_log
WHERE redeem_time >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
GROUP BY DATE(redeem_time)
HAVING record_count < 100 OR unique_users < 10;  -- 阈值可根据业务调整

-- 数据一致性检查：兑换记录和码的状态是否一致
SELECT 
    r.code_id,
    r.user_id,
    r.redeem_time,
    c.status as code_status,
    CASE WHEN c.status = 1 AND r.status = 1 THEN 'OK'
         WHEN c.status = 0 AND r.status = 2 THEN 'OK'
         ELSE 'INCONSISTENT' END as check_result
FROM ods_gift_code_redeem_log r
LEFT JOIN gift_code c ON r.code_id = c.id
WHERE r.redeem_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
HAVING check_result = 'INCONSISTENT';

-- 数据及时性检查：ETL是否按时完成
SELECT 
    table_name,
    max_etl_time,
    TIMESTAMPDIFF(MINUTE, max_etl_time, NOW()) as delay_minutes
FROM (
    SELECT 'dwd_gift_code_redeem_detail' as table_name,
           MAX(etl_time) as max_etl_time
    FROM dwd_gift_code_redeem_detail
    UNION ALL
    SELECT 'dws_activity_daily_summary' as table_name,
           MAX(etl_time) as max_etl_time
    FROM dws_activity_daily_summary
) t
HAVING delay_minutes > 120;  -- 超过2小时报警

三、核心数据指标

要做好报表，首先要明确：哪些指标是值得关注的？

3.1 兑换类指标

这是最核心的指标。兑换率低，说明活动吸引力不足，或者投放渠道不对。兑换率过高（比如超过100%），则需要警惕——可能是码被泄露或滥用了。

但兑换率不是越高越好。100%的兑换率，意味着没有冗余，没有容错空间。如果渠道需要追加投放，手上已经没有码了。一般建议预留10-20%的余量。

-- 计算活动兑换率
SELECT 
    a.activity_id,
    a.activity_name,
    COUNT(DISTINCT gc.id) as total_codes,
    COUNT(DISTINCT CASE WHEN gc.status = 1 THEN gc.id END) as redeemed_codes,
    ROUND(COUNT(DISTINCT CASE WHEN gc.status = 1 THEN gc.id END) * 100.0 / 
          NULLIF(COUNT(DISTINCT gc.id), 0), 2) as redeem_rate_pct,
    
    -- 有效兑换率（只计算未过期、未取消的码）
    COUNT(DISTINCT CASE WHEN gc.status = 1 AND gc.expire_time > NOW() THEN gc.id END) as valid_redeemed,
    ROUND(COUNT(DISTINCT CASE WHEN gc.status = 1 AND gc.expire_time > NOW() THEN gc.id END) * 100.0 / 
          NULLIF(COUNT(DISTINCT CASE WHEN gc.expire_time > NOW() AND gc.cancelled_at IS NULL THEN gc.id END), 0), 2) 
          as valid_redeem_rate_pct
FROM activity a
LEFT JOIN gift_code gc ON a.id = gc.activity_id
WHERE a.start_time >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
GROUP BY a.activity_id, a.activity_name
ORDER BY redeem_rate_pct DESC;

10000个码，是1小时内被抢光，还是1个月都没发完？兑换速度反映了活动的热度和紧迫感。

-- 计算兑换速度指标（T50/T90/T99）
WITH redeem_timeline AS (
    SELECT 
        gc.activity_id,
        gc.id as code_id,
        r.redeem_time,
        a.start_time as activity_start,
        ROW_NUMBER() OVER (PARTITION BY gc.activity_id ORDER BY r.redeem_time) as redeem_rank,
        COUNT(*) OVER (PARTITION BY gc.activity_id) as total_redeemed
    FROM gift_code gc
    INNER JOIN gift_code_redeem_log r ON gc.id = r.code_id AND r.status = 1
    INNER JOIN activity a ON gc.activity_id = a.id
    WHERE gc.status = 1
),
percentiles AS (
    SELECT 
        activity_id,
        MIN(redeem_time) as first_redeem,
        MAX(redeem_time) as last_redeem,
        MAX(CASE WHEN redeem_rank <= total_redeemed * 0.5 THEN redeem_time END) as t50_time,
        MAX(CASE WHEN redeem_rank <= total_redeemed * 0.9 THEN redeem_time END) as t90_time,
        MAX(CASE WHEN redeem_rank <= total_redeemed * 0.99 THEN redeem_time END) as t99_time,
        MAX(activity_start) as activity_start,
        MAX(total_redeemed) as total_redeemed
    FROM redeem_timeline
    GROUP BY activity_id
)
SELECT 
    activity_id,
    total_redeemed,
    TIMESTAMPDIFF(MINUTE, activity_start, first_redeem) as minutes_to_first,
    TIMESTAMPDIFF(MINUTE, activity_start, t50_time) as minutes_to_t50,
    TIMESTAMPDIFF(MINUTE, activity_start, t90_time) as minutes_to_t90,
    TIMESTAMPDIFF(MINUTE, activity_start, t99_time) as minutes_to_t99,
    TIMESTAMPDIFF(HOUR, activity_start, last_redeem) as hours_total
FROM percentiles
ORDER BY total_redeemed DESC;

这个指标帮助评估系统的承载能力。如果峰值兑换量远超系统设计容量，下次活动前就需要扩容。

-- 计算不同时间粒度的峰值兑换量
SELECT 
    activity_id,
    redeem_date,
    -- 分钟级峰值
    MAX(minute_redeems) as peak_minute_redeems,
    -- 小时级峰值
    MAX(hour_redeems) as peak_hour_redeems,
    -- 峰值发生时间
    SUBSTRING_INDEX(GROUP_CONCAT(peak_minute ORDER BY minute_redeems DESC), ',', 1) as peak_minute_time,
    SUBSTRING_INDEX(GROUP_CONCAT(peak_hour ORDER BY hour_redeems DESC), ',', 1) as peak_hour_time
FROM (
    SELECT 
        activity_id,
        DATE(redeem_time) as redeem_date,
        DATE_FORMAT(redeem_time, '%Y-%m-%d %H:%i') as minute_key,
        DATE_FORMAT(redeem_time, '%Y-%m-%d %H') as hour_key,
        COUNT(*) as minute_redeems,
        0 as hour_redeems,
        DATE_FORMAT(redeem_time, '%H:%i') as peak_minute,
        '' as peak_hour
    FROM dwd_gift_code_redeem_detail
    WHERE status = 1
    GROUP BY activity_id, DATE(redeem_time), DATE_FORMAT(redeem_time, '%Y-%m-%d %H:%i')
    
    UNION ALL
    
    SELECT 
        activity_id,
        DATE(redeem_time) as redeem_date,
        '' as minute_key,
        DATE_FORMAT(redeem_time, '%Y-%m-%d %H') as hour_key,
        0 as minute_redeems,
        COUNT(*) as hour_redeems,
        '' as peak_minute,
        DATE_FORMAT(redeem_time, '%H') as peak_hour
    FROM dwd_gift_code_redeem_detail
    WHERE status = 1
    GROUP BY activity_id, DATE(redeem_time), DATE_FORMAT(redeem_time, '%Y-%m-%d %H')
) t
GROUP BY activity_id, redeem_date
ORDER BY peak_minute_redeems DESC;

3.2 用户类指标

一个用户兑换10次，和10个用户各兑换1次，意义完全不同。前者可能是刷码，后者才是真正的用户触达。

-- 用户兑换行为分析
SELECT 
    activity_id,
    activity_name,
    -- 用户统计
    COUNT(DISTINCT user_id) as unique_users,
    COUNT(*) as total_redeems,
    ROUND(COUNT(*) * 1.0 / COUNT(DISTINCT user_id), 2) as avg_redeems_per_user,
    
    -- 新老用户分布
    COUNT(DISTINCT CASE WHEN is_new_user = 1 THEN user_id END) as new_users,
    COUNT(DISTINCT CASE WHEN is_new_user = 0 THEN user_id END) as old_users,
    ROUND(COUNT(DISTINCT CASE WHEN is_new_user = 1 THEN user_id END) * 100.0 / 
          COUNT(DISTINCT user_id), 2) as new_user_ratio,
    
    -- 兑换次数分布
    COUNT(CASE WHEN redeem_cnt = 1 THEN 1 END) as single_redeem_users,
    COUNT(CASE WHEN redeem_cnt BETWEEN 2 AND 5 THEN 1 END) as normal_multi_users,
    COUNT(CASE WHEN redeem_cnt > 5 THEN 1 END) as heavy_users
FROM (
    SELECT 
        d.activity_id,
        d.activity_name,
        d.user_id,
        d.is_new_user,
        COUNT(*) as redeem_cnt
    FROM dwd_gift_code_redeem_detail d
    WHERE d.status = 1
      AND d.redeem_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
    GROUP BY d.activity_id, d.activity_name, d.user_id, d.is_new_user
) user_redeems
GROUP BY activity_id, activity_name
ORDER BY unique_users DESC;

用户兑换了礼包，然后呢？是拿了奖励就跑，还是留下来继续玩？这个指标衡量的是活动的长期价值。

-- 用户留存分析（兑换后7/14/30天留存）
WITH redeem_users AS (
    SELECT DISTINCT
        user_id,
        MIN(redeem_date) as first_redeem_date
    FROM dwd_gift_code_redeem_detail
    WHERE status = 1
      AND redeem_date >= DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY)
    GROUP BY user_id
),
retention_check AS (
    SELECT 
        r.user_id,
        r.first_redeem_date,
        -- 检查用户在各留存日是否活跃
        MAX(CASE WHEN DATE_ADD(r.first_redeem_date, INTERVAL 1 DAY) = u.active_date THEN 1 END) as day1_active,
        MAX(CASE WHEN DATE_ADD(r.first_redeem_date, INTERVAL 7 DAY) = u.active_date THEN 1 END) as day7_active,
        MAX(CASE WHEN DATE_ADD(r.first_redeem_date, INTERVAL 14 DAY) = u.active_date THEN 1 END) as day14_active,
        MAX(CASE WHEN DATE_ADD(r.first_redeem_date, INTERVAL 30 DAY) = u.active_date THEN 1 END) as day30_active
    FROM redeem_users r
    LEFT JOIN user_daily_active u ON r.user_id = u.user_id
    WHERE u.active_date BETWEEN r.first_redeem_date AND DATE_ADD(r.first_redeem_date, INTERVAL 30 DAY)
    GROUP BY r.user_id, r.first_redeem_date
)
SELECT 
    first_redeem_date,
    COUNT(*) as total_users,
    SUM(IFNULL(day1_active, 0)) as day1_retained,
    SUM(IFNULL(day7_active, 0)) as day7_retained,
    SUM(IFNULL(day14_active, 0)) as day14_retained,
    SUM(IFNULL(day30_active, 0)) as day30_retained,
    ROUND(SUM(IFNULL(day1_active, 0)) * 100.0 / COUNT(*), 2) as day1_retention_pct,
    ROUND(SUM(IFNULL(day7_active, 0)) * 100.0 / COUNT(*), 2) as day7_retention_pct,
    ROUND(SUM(IFNULL(day14_active, 0)) * 100.0 / COUNT(*), 2) as day14_retention_pct,
    ROUND(SUM(IFNULL(day30_active, 0)) * 100.0 / COUNT(*), 2) as day30_retention_pct
FROM retention_check
GROUP BY first_redeem_date
ORDER BY first_redeem_date DESC;

3.3 渠道类指标

不同渠道的用户质量差异很大。通过对比各渠道的兑换率、ROI和留存，可以优化投放策略。

-- 渠道综合效果分析
SELECT 
    c.channel_id,
    c.channel_name,
    c.channel_type,
    
    -- 投放统计
    COUNT(DISTINCT gc.id) as total_codes,
    SUM(gc.cost) as total_cost,
    
    -- 兑换统计
    COUNT(DISTINCT CASE WHEN gc.status = 1 THEN gc.id END) as redeemed_codes,
    ROUND(COUNT(DISTINCT CASE WHEN gc.status = 1 THEN gc.id END) * 100.0 / 
          NULLIF(COUNT(DISTINCT gc.id), 0), 2) as redeem_rate,
    
    -- 用户统计
    COUNT(DISTINCT r.user_id) as unique_users,
    COUNT(DISTINCT CASE WHEN d.is_new_user = 1 THEN r.user_id END) as new_users,
    ROUND(COUNT(DISTINCT CASE WHEN d.is_new_user = 1 THEN r.user_id END) * 100.0 / 
          NULLIF(COUNT(DISTINCT r.user_id), 0), 2) as new_user_ratio,
    
    -- 付费统计（7日内）
    COUNT(DISTINCT CASE WHEN p.pay_time IS NOT NULL THEN r.user_id END) as pay_users,
    SUM(IFNULL(p.pay_amount, 0)) as total_revenue,
    ROUND(SUM(IFNULL(p.pay_amount, 0)) / NULLIF(SUM(gc.cost), 0), 2) as roi_7d,
    
    -- 用户质量指标
    ROUND(AVG(d.user_level), 1) as avg_user_level,
    ROUND(AVG(d.user_total_recharge), 2) as avg_ltv
    
FROM channel c
LEFT JOIN gift_code gc ON c.channel_id = gc.channel_id
LEFT JOIN gift_code_redeem_log r ON gc.id = r.code_id AND r.status = 1
LEFT JOIN dwd_gift_code_redeem_detail d ON r.id = d.redeem_id
LEFT JOIN (
    SELECT user_id, MIN(pay_time) as pay_time, SUM(amount) as pay_amount
    FROM user_payment
    WHERE pay_time <= DATE_ADD(redeem_time, INTERVAL 7 DAY)
    GROUP BY user_id
) p ON r.user_id = p.user_id
WHERE gc.created_at >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
GROUP BY c.channel_id, c.channel_name, c.channel_type
ORDER BY roi_7d DESC;

3.4 时间类指标

玩家什么时候最活跃？什么时候最容易看到推送？这个指标帮助优化投放和推送时机。

-- 兑换时段热力图数据（小时 x 星期）
SELECT 
    day_of_week,
    redeem_hour,
    COUNT(*) as redeem_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 4) as percentage,
    -- 与平均值比较
    ROUND(COUNT(*) * 1.0 / AVG(COUNT(*)) OVER(), 2) as index_vs_avg
FROM dwd_gift_code_redeem_detail
WHERE status = 1
  AND redeem_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
GROUP BY day_of_week, redeem_hour
ORDER BY day_of_week, redeem_hour;

-- 生成热力图数据格式（可直接用于前端展示）
SELECT 
    CONCAT('[', GROUP_CONCAT(
        CONCAT('{x:', redeem_hour, ',y:', day_of_week - 1, ',value:', redeem_count, '}')
        ORDER BY day_of_week, redeem_hour
    ), ']') as heatmap_data
FROM (
    SELECT day_of_week, redeem_hour, COUNT(*) as redeem_count
    FROM dwd_gift_code_redeem_detail
    WHERE status = 1 AND redeem_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
    GROUP BY day_of_week, redeem_hour
) t;

3.5 奖励类指标

哪种奖励最受欢迎？哪种奖励兑换后用户满意度最高？哪种奖励对留存的贡献最大？

-- 奖励效果综合分析
SELECT 
    reward_id,
    reward_name,
    reward_type,
    reward_value,
    
    -- 热度指标
    COUNT(*) as redeem_count,
    COUNT(DISTINCT user_id) as unique_users,
    RANK() OVER (ORDER BY COUNT(*) DESC) as popularity_rank,
    
    -- 满意度代理指标（兑换后7日活跃天数）
    ROUND(AVG(active_days_7d), 1) as avg_active_days_7d,
    
    -- 付费转化
    COUNT(DISTINCT CASE WHEN has_paid_7d = 1 THEN user_id END) as pay_convert_users,
    ROUND(COUNT(DISTINCT CASE WHEN has_paid_7d = 1 THEN user_id END) * 100.0 / 
          NULLIF(COUNT(DISTINCT user_id), 0), 2) as pay_convert_rate,
    
    -- 留存贡献
    ROUND(AVG(day7_retention) * 100, 2) as avg_day7_retention_pct,
    
    -- ROI
    ROUND(SUM(total_pay_7d) / NULLIF(reward_value * COUNT(*), 0), 2) as roi
    
FROM (
    SELECT 
        d.reward_id,
        d.reward_name,
        d.reward_type,
        d.reward_value,
        d.user_id,
        COUNT(DISTINCT u.active_date) as active_days_7d,
        MAX(CASE WHEN p.pay_time IS NOT NULL THEN 1 ELSE 0 END) as has_paid_7d,
        SUM(IFNULL(p.amount, 0)) as total_pay_7d,
        MAX(CASE WHEN u.active_date = DATE_ADD(d.redeem_date, INTERVAL 7 DAY) THEN 1 ELSE 0 END) as day7_retention
    FROM dwd_gift_code_redeem_detail d
    LEFT JOIN user_daily_active u ON d.user_id = u.user_id 
        AND u.active_date BETWEEN d.redeem_date AND DATE_ADD(d.redeem_date, INTERVAL 7 DAY)
    LEFT JOIN user_payment p ON d.user_id = p.user_id 
        AND p.pay_time BETWEEN d.redeem_time AND DATE_ADD(d.redeem_time, INTERVAL 7 DAY)
    WHERE d.status = 1
      AND d.redeem_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
    GROUP BY d.reward_id, d.reward_name, d.reward_type, d.reward_value, d.user_id
) reward_user
GROUP BY reward_id, reward_name, reward_type, reward_value
ORDER BY redeem_count DESC;

四、报表系统设计

指标有了，接下来是如何呈现。好的报表系统，要像一位优秀的分析师——不仅展示数据，还要讲清楚数据背后的故事。

4.1 报表分层架构

报表系统应该像洋葱一样，一层一层剥开：

┌─────────────────────────────────────────────────────────────┐
│                    第一层：概览大屏                           │
│  ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐           │
│  │今日兑换量│ │兑换率   │ │活跃活动 │ │异常预警 │           │
│  │12,345   │ │67.8%    │ │15个     │ │2个      │           │
│  │↑ 15%    │ │↓ 3.2%   │ │↑ 3个    │ │⚠️       │           │
│  └─────────┘ └─────────┘ └─────────┘ └─────────┘           │
│  ┌───────────────────────────────────────────────────────┐  │
│  │                  趋势图表（折线图）                      │  │
│  └───────────────────────────────────────────────────────┘  │
├─────────────────────────────────────────────────────────────┤
│                    第二层：维度分析                           │
│  ┌───────────────────┐  ┌───────────────────┐               │
│  │    渠道分析        │  │    时间分析        │               │
│  │  (柱状图/条形图)   │  │  (热力图/日历图)   │               │
│  └───────────────────┘  └───────────────────┘               │
│  ┌───────────────────┐  ┌───────────────────┐               │
│  │    奖励分析        │  │    用户分析        │               │
│  │  (饼图/环形图)     │  │  (漏斗图/雷达图)   │               │
│  └───────────────────┘  └───────────────────┘               │
├─────────────────────────────────────────────────────────────┤
│                    第三层：明细查询                           │
│  ┌───────────────────────────────────────────────────────┐  │
│  │  筛选条件：[活动] [渠道] [时间] [奖励] [更多 ▼]        │  │
│  ├───────────────────────────────────────────────────────┤  │
│  │  用户ID │兑换时间│奖励│渠道│状态│操作                 │  │
│  │  10001 │02-28 15:30│黄金礼包│微信│成功│[详情]        │  │
│  │  10002 │02-28 15:29│白银礼包│抖音│成功│[详情]        │  │
│  │  ...   │...        │...    │... │... │...            │  │
│  └───────────────────────────────────────────────────────┘  │
│  [导出CSV] [导出Excel]                                      │
└─────────────────────────────────────────────────────────────┘

运营负责人打开报表，第一眼看到的是什么？

• 今日/本周/本月的总兑换量
• 核心指标的同比/环比变化
• 异常预警（兑换率异常、峰值异常等）

这一层回答的问题是：整体情况如何？有没有需要立即关注的问题？

从概览发现异常后，需要下钻分析。支持多维度交叉分析，比如"新用户在周末对各奖励类型的偏好"。

需要看到具体的数据时，支持灵活筛选和数据导出。

4.2 可视化图表设计

不同的数据，适合不同的图表。选错图表类型，会让数据变得难以理解。选择正确的图表类型，能让数据自己说话。

4.2.1 趋势折线图（带预警线）

兑换量趋势图
┌────────────────────────────────────────────────────────┐
│  1500 ┤                          ╭───╮                 │
│       │                        ╭╯    ╲                │
│  1200 ┤                   ╭───╯      ╲   ╭──╮        │
│       │              ╭───╯           ╲─╯   ╲         │
│   900 ┤        ╭───╮╯                         ╲       │
│       │   ╭───╯    ╲                            ╲──╮ │
│   600 ┤───╯         ╲                              ╲─│
│       │                                            ╲ │
│   300 ┤ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─│ ← 预警线
│       │                                              ╲│
│     0 ┼────────────────────────────────────────────── │
│       02-22  02-23  02-24  02-25  02-26  02-27  02-28│
│                                                       │
│  ━━━ 今日    ┄┄┄ 昨日    ▲ 峰值    ⚠ 异常点         │
└────────────────────────────────────────────────────────┘

设计要点：

• 显示当前数据和历史对比
• 标注峰值和异常点
• 添加预警线（阈值）
• 鼠标悬停显示具体数值

4.2.2 渠道对比柱状图

渠道效果对比（按兑换率排序）
┌────────────────────────────────────────────────────────┐
│                                                        │
│  微信小程序 ████████████████████████████████  89.2%    │
│  抖音     ██████████████████████████  76.5%          │
│  B站      ████████████████████  65.3%                │
│  微博     ████████████████  52.1%                    │
│  小红书   ████████████  43.8%                        │
│  QQ      ████████  31.2%                            │
│  官网     ████  18.5%                                │
│                                                        │
│  ━━━ 兑换率    ━━━ 预警阈值(40%)                      │
└────────────────────────────────────────────────────────┘

设计要点：

• 按数值大小排序，一目了然
• 用颜色区分达标/未达标
• 支持点击下钻查看详情

4.2.3 时段热力图

兑换时段热力图（颜色越深 = 兑换越活跃）
┌────────────────────────────────────────────────────────┐
│        0  2  4  6  8 10 12 14 16 18 20 22             │
│  周一  ░  ░  ░  ▒  ▒  ▓  ▓  ▒  ▒  ▓  ▓  ▒            │
│  周二  ░  ░  ░  ▒  ▒  ▓  ▓  ▒  ▒  ▓  ▓  ▒            │
│  周三  ░  ░  ░  ▒  ▒  ▓  ▓  ▒  ▒  ▓  ▓  ▒            │
│  周四  ░  ░  ░  ▒  ▒  ▓  ▓  ▒  ▒  ▓  ▓  ▒            │
│  周五  ░  ░  ░  ▒  ▓  ▓  ▓  ▓  ▓  ▓  ▓  ▓            │
│  周六  ▒  ▒  ▒  ▓  ▓  ▓  ▓  ▓  ▓  ▓  ▓  ▓            │
│  周日  ▒  ▒  ▒  ▓  ▓  ▓  ▓  ▓  ▓  ▓  ▓  ▓            │
│                                                        │
│  ░ 低活跃  ▒ 中等  ▓ 高活跃  █ 峰值                   │
│                                                        │
│  💡 最佳推送时段：周末 14:00-20:00                     │
└────────────────────────────────────────────────────────┘

设计要点：

• 用颜色深浅表示活跃程度
• 标注最佳时段建议
• 支持按活动/渠道筛选

4.2.4 用户转化漏斗

用户转化漏斗分析
┌────────────────────────────────────────────────────────┐
│                                                        │
│  活动曝光    100,000  ████████████████████████████████│
│     ↓  45%                                            │
│  点击进入     45,000  ██████████████████████████      │
│     ↓  62%                                            │
│  获取礼包码   27,900  ████████████████████            │
│     ↓  78%                                            │
│  打开游戏     21,762  ████████████████                │
│     ↓  85%                                            │
│  成功兑换     18,498  ██████████████                  │
│     ↓  72%                                            │
│  7日留存     13,318   ████████████                    │
│                                                        │
│  整体转化率：18.5%    关键流失点：点击→获取码          │
└────────────────────────────────────────────────────────┘

设计要点：

• 清晰展示每步转化率
• 标注关键流失点
• 支持对比不同活动/渠道

4.3 实时 vs 离线报表

-- 实时监控SQL示例（ClickHouse）
SELECT 
    'current_redeem_count' as metric,
    COUNT(*) as value,
    '次' as unit,
    (SELECT COUNT(*) FROM gift_code_redeem_log 
     WHERE redeem_time >= DATE_SUB(NOW(), INTERVAL 1 DAY)) as compare_value,
    ROUND((COUNT(*) - compare_value) * 100.0 / NULLIF(compare_value, 0), 2) as change_rate
FROM gift_code_redeem_log
WHERE redeem_time >= toStartOfMinute(NOW() - INTERVAL 5 MINUTE);

-- 实时异常检测
SELECT 
    activity_id,
    DATE_FORMAT(redeem_time, '%Y-%m-%d %H:%i') as minute,
    COUNT(*) as redeem_count,
    CASE 
        WHEN COUNT(*) > 1000 THEN 'ALERT'   -- 超过1000次/分钟告警
        WHEN COUNT(*) > 500 THEN 'WARNING'  -- 超过500次/分钟预警
        ELSE 'NORMAL'
    END as status
FROM gift_code_redeem_log
WHERE redeem_time >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)
GROUP BY activity_id, DATE_FORMAT(redeem_time, '%Y-%m-%d %H:%i')
HAVING status != 'NORMAL';

离线报表可以做更复杂的计算，比如用户留存分析、LTV计算、归因分析等。

4.4 报表性能优化

当数据量达到千万级甚至亿级时，报表查询性能就成了关键问题。

把经常查询的指标预先计算好，存入汇总表。

-- 每日凌晨执行预聚合
INSERT INTO dws_activity_daily_summary
SELECT 
    activity_id,
    CURRENT_DATE - INTERVAL 1 DAY as stat_date,
    -- ... 各种聚合指标
FROM dwd_gift_code_redeem_detail
WHERE redeem_date = CURRENT_DATE - INTERVAL 1 DAY
GROUP BY activity_id;

按日期分区，查询时只扫描需要的分区。

-- 创建分区表
CREATE TABLE dwd_gift_code_redeem_detail (
    -- 字段定义...
) PARTITION BY RANGE (TO_DAYS(redeem_date)) (
    PARTITION p202601 VALUES LESS THAN (TO_DAYS('2026-02-01')),
    PARTITION p202602 VALUES LESS THAN (TO_DAYS('2026-03-01')),
    PARTITION p202603 VALUES LESS THAN (TO_DAYS('2026-04-01')),
    -- ...
);

-- 查询时自动利用分区裁剪
SELECT * FROM dwd_gift_code_redeem_detail
WHERE redeem_date >= '2026-02-01' AND redeem_date < '2026-03-01';
-- 只会扫描 p202602 分区

对于复杂的分析查询，可以创建物化视图。

-- 创建物化视图（自动刷新）
CREATE MATERIALIZED VIEW mv_channel_daily_stats
REFRESH FAST ON DEMAND
AS SELECT 
    channel_id,
    redeem_date,
    COUNT(*) as redeem_count,
    COUNT(DISTINCT user_id) as unique_users,
    -- ... 其他指标
FROM dwd_gift_code_redeem_detail
GROUP BY channel_id, redeem_date;

对于高频查询的结果，可以缓存起来。

查询请求 → Redis缓存 → 命中？→ 返回结果
                ↓ 未命中
            数据库查询 → 写入缓存 → 返回结果

缓存策略：

• 热点数据：TTL 5分钟
• 历史数据：TTL 24小时
• 实时数据：不缓存或 TTL 30秒

五、数据分析方法论

有了数据和报表，接下来是如何分析。分析不是简单的"看数据"，而是要有方法、有逻辑、有结论。

5.1 趋势分析法

今天的兑换量是5000，这个数字本身没有意义。上周五是4000，上上周是3000——增长趋势很明显。

1. 确定基准线：选择合适的对比周期（环比/同比/目标）
2. 识别趋势方向：上升/下降/持平
3. 判断趋势强度：缓慢/中等/剧烈
4. 预测未来走向：基于历史规律

-- 趋势分析SQL：计算移动平均和趋势
WITH daily_stats AS (
    SELECT 
        redeem_date,
        COUNT(*) as daily_redeems
    FROM dwd_gift_code_redeem_detail
    WHERE redeem_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
    GROUP BY redeem_date
),
with_ma AS (
    SELECT 
        redeem_date,
        daily_redeems,
        -- 7日移动平均
        AVG(daily_redeems) OVER (
            ORDER BY redeem_date 
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) as ma_7d,
        -- 环比变化
        LAG(daily_redeems) OVER (ORDER BY redeem_date) as prev_day,
        -- 同比变化
        LAG(daily_redeems, 7) OVER (ORDER BY redeem_date) as prev_week
    FROM daily_stats
)
SELECT 
    redeem_date,
    daily_redeems,
    ma_7d,
    ROUND(ma_7d - LAG(ma_7d) OVER (ORDER BY redeem_date), 2) as ma_trend,
    ROUND((daily_redeems - prev_day) * 100.0 / NULLIF(prev_day, 0), 2) as dod_change,
    ROUND((daily_redeems - prev_week) * 100.0 / NULLIF(prev_week, 0), 2) as wow_change,
    CASE 
        WHEN daily_redeems > ma_7d * 1.2 THEN 'ABOVE_NORMAL'
        WHEN daily_redeems < ma_7d * 0.8 THEN 'BELOW_NORMAL'
        ELSE 'NORMAL'
    END as status
FROM with_ma
ORDER BY redeem_date DESC;

兑换量趋势分析（含移动平均）
┌────────────────────────────────────────────────────────┐
│  2000 ┤                    ╭──╮                        │
│       │    ·    ·      ╭──╯   ╲    ·                  │
│  1500 ┤  ·   ·    ╭───╯       ╲──╮   ·   ·            │
│       │·          ╱              ╲                  · │
│  1000 ┤──────────╱─────────────────╲─────────────·───│ ← 7日MA
│       │    ·    ·    ·     ·    ·    ╲   ·   ·       │
│   500 ┤                              ╲               │
│       └───────────────────────────────╲──────────────│
│        02-01 02-05 02-10 02-15 02-20 02-25 02-28     │
│                                                        │
│  · 日兑换量    ━━━ 7日移动平均                        │
│                                                        │
│  📊 分析结论：                                         │
│  • 整体呈上升趋势，7日MA从800升至1200 (+50%)          │
│  • 02-15出现异常峰值，需关注原因                       │
│  • 近3日稳定在MA附近，趋势健康                         │
└────────────────────────────────────────────────────────┘

5.2 归因分析法

数据异常了，怎么找原因？通过维度下钻和相关性分析。

                    ┌─────────────────┐
                    │  总体指标变化    │
                    │  (如兑换率下降)  │
                    └────────┬────────┘
                             │
              ┌──────────────┼──────────────┐
              ▼              ▼              ▼
        ┌─────────┐    ┌─────────┐    ┌─────────┐
        │ 渠道维度 │    │ 时间维度 │    │ 用户维度 │
        └────┬────┘    └────┬────┘    └────┬────┘
             │              │              │
        ┌────┼────┐    ┌────┼────┐    ┌────┼────┐
        ▼    ▼    ▼    ▼    ▼    ▼    ▼    ▼    ▼
       微信 抖音 ...   周末 工作日 ...  新用户 老用户 ...

-- 归因分析：找到兑换率下降的根本原因
WITH base_metrics AS (
    -- 基准期（上周）
    SELECT 
        channel_id,
        'baseline' as period,
        COUNT(DISTINCT gc.id) as total_codes,
        COUNT(DISTINCT CASE WHEN gc.status = 1 THEN gc.id END) as redeemed,
        ROUND(COUNT(DISTINCT CASE WHEN gc.status = 1 THEN gc.id END) * 100.0 / 
              NULLIF(COUNT(DISTINCT gc.id), 0), 2) as redeem_rate
    FROM gift_code gc
    WHERE gc.created_at BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 14 DAY) 
                            AND DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
    GROUP BY channel_id
    
    UNION ALL
    
    -- 对比期（本周）
    SELECT 
        channel_id,
        'current' as period,
        COUNT(DISTINCT gc.id) as total_codes,
        COUNT(DISTINCT CASE WHEN gc.status = 1 THEN gc.id END) as redeemed,
        ROUND(COUNT(DISTINCT CASE WHEN gc.status = 1 THEN gc.id END) * 100.0 / 
              NULLIF(COUNT(DISTINCT gc.id), 0), 2) as redeem_rate
    FROM gift_code gc
    WHERE gc.created_at >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
    GROUP BY channel_id
)
SELECT 
    c.channel_name,
    MAX(CASE WHEN period = 'baseline' THEN redeem_rate END) as baseline_rate,
    MAX(CASE WHEN period = 'current' THEN redeem_rate END) as current_rate,
    MAX(CASE WHEN period = 'current' THEN redeem_rate END) - 
    MAX(CASE WHEN period = 'baseline' THEN redeem_rate END) as rate_change,
    -- 贡献度计算
    ROUND((MAX(CASE WHEN period = 'current' THEN redeemed END) - 
           MAX(CASE WHEN period = 'baseline' THEN redeemed END)) * 100.0 /
          NULLIF((SELECT SUM(CASE WHEN period = 'current' THEN redeemed END) - 
                        SUM(CASE WHEN period = 'baseline' THEN redeemed END)
                  FROM base_metrics WHERE period != 'total'), 0), 2) as contribution_pct
FROM base_metrics b
JOIN channel c ON b.channel_id = c.channel_id
GROUP BY c.channel_name
ORDER BY rate_change ASC;

📋 兑换率下降归因分析报告
════════════════════════════════════════════════════════

📌 问题描述
  • 总体兑换率从 68.5% 下降至 52.3% (-16.2pp)
  • 影响范围：全渠道
  • 持续时间：近7天

🔍 归因分析

  【渠道维度】
  ┌─────────┬────────┬────────┬────────┬────────┐
  │ 渠道    │ 基准期 │ 本期   │ 变化   │ 贡献度 │
  ├─────────┼────────┼────────┼────────┼────────┤
  │ 微博    │ 72.3%  │ 31.2%  │ -41.1pp│ 45.2%  │ ← 主要原因
  │ 小红书  │ 58.6%  │ 42.1%  │ -16.5pp│ 28.3%  │
  │ 抖音    │ 81.2%  │ 68.5%  │ -12.7pp│ 18.5%  │
  │ 微信    │ 65.4%  │ 63.2%  │  -2.2pp│  8.0%  │
  └─────────┴────────┴────────┴────────┴────────┘
  
  【时间维度】
  • 工作日影响更大（-18.3pp vs 周末-8.6pp）
  • 晚间时段（18:00-22:00）下降最明显

💡 根因定位
  1. 微博渠道投放策略变更（新接入了子渠道X）
  2. 子渠道X用户质量较差，兑换率仅15%
  3. 拉低了微博整体兑换率

✅ 建议措施
  1. 暂停子渠道X投放
  2. 微博渠道重新评估ROI
  3. 建立渠道准入机制

5.3 漏斗分析法

用户从看到活动到成功兑换，需要经过多个步骤。每个步骤都有流失，漏斗分析帮助找出问题最大的环节。

-- 漏斗分析SQL
WITH funnel_steps AS (
    SELECT 
        user_id,
        -- 步骤1：活动曝光
        MAX(CASE WHEN event_type = 'activity_expose' THEN event_time END) as step1_time,
        -- 步骤2：点击进入
        MAX(CASE WHEN event_type = 'activity_click' THEN event_time END) as step2_time,
        -- 步骤3：获取礼包码
        MAX(CASE WHEN event_type = 'get_code' THEN event_time END) as step3_time,
        -- 步骤4：打开游戏
        MAX(CASE WHEN event_type = 'open_game' THEN event_time END) as step4_time,
        -- 步骤5：成功兑换
        MAX(CASE WHEN event_type = 'redeem_success' THEN event_time END) as step5_time
    FROM user_event_log
    WHERE activity_id = 1001
      AND event_date BETWEEN '2026-02-01' AND '2026-02-28'
    GROUP BY user_id
)
SELECT 
    '步骤1: 活动曝光' as step_name,
    COUNT(*) as user_count,
    100.00 as conversion_rate,
    0 as drop_rate
FROM funnel_steps WHERE step1_time IS NOT NULL

UNION ALL

SELECT 
    '步骤2: 点击进入',
    COUNT(*),
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM funnel_steps WHERE step1_time IS NOT NULL), 2),
    ROUND(100 - COUNT(*) * 100.0 / (SELECT COUNT(*) FROM funnel_steps WHERE step1_time IS NOT NULL), 2)
FROM funnel_steps 
WHERE step2_time IS NOT NULL 
  AND step2_time > step1_time

UNION ALL

SELECT 
    '步骤3: 获取礼包码',
    COUNT(*),
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM funnel_steps WHERE step1_time IS NOT NULL), 2),
    ROUND(100 - COUNT(*) * 100.0 / (SELECT COUNT(*) FROM funnel_steps WHERE step2_time IS NOT NULL), 2)
FROM funnel_steps 
WHERE step3_time IS NOT NULL 
  AND step3_time > step2_time

-- ... 继续其他步骤
;

5.4 同期群分析（Cohort Analysis）

把同一时期（同一天/同一周/同一月）开始使用产品的用户分为一组，追踪他们后续的行为。

-- 同期群留存分析SQL
WITH user_cohort AS (
    -- 按用户首次兑换日期分组
    SELECT 
        user_id,
        MIN(DATE(redeem_time)) as cohort_date
    FROM dwd_gift_code_redeem_detail
    WHERE status = 1
    GROUP BY user_id
),
cohort_activity AS (
    -- 计算每个用户在每个时段的活跃情况
    SELECT 
        c.cohort_date,
        DATEDIFF(a.active_date, c.cohort_date) as days_since_first,
        COUNT(DISTINCT c.user_id) as active_users
    FROM user_cohort c
    LEFT JOIN user_daily_active a ON c.user_id = a.user_id
    WHERE c.cohort_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
    GROUP BY c.cohort_date, DATEDIFF(a.active_date, c.cohort_date)
)
SELECT 
    cohort_date,
    SUM(CASE WHEN days_since_first = 0 THEN active_users ELSE 0 END) as day0,
    SUM(CASE WHEN days_since_first = 1 THEN active_users ELSE 0 END) as day1,
    SUM(CASE WHEN days_since_first = 7 THEN active_users ELSE 0 END) as day7,
    SUM(CASE WHEN days_since_first = 14 THEN active_users ELSE 0 END) as day14,
    SUM(CASE WHEN days_since_first = 30 THEN active_users ELSE 0 END) as day30,
    -- 计算留存率
    ROUND(SUM(CASE WHEN days_since_first = 7 THEN active_users ELSE 0 END) * 100.0 /
          NULLIF(SUM(CASE WHEN days_since_first = 0 THEN active_users ELSE 0 END), 0), 2) as day7_retention
FROM cohort_activity
GROUP BY cohort_date
ORDER BY cohort_date DESC;

用户留存同期群分析（按首次兑换周分组）
═══════════════════════════════════════════════════════════════
           │ 第1周  │ 第2周  │ 第3周  │ 第4周  │ 第5周  │ 第6周
───────────┼────────┼────────┼────────┼────────┼────────┼──────
2026-W01   │ 5,000  │ 3,250  │ 2,750  │ 2,400  │ 2,150  │ 1,950
           │ 100%   │ 65%    │ 55%    │ 48%    │ 43%    │ 39%
───────────┼────────┼────────┼────────┼────────┼────────┼──────
2026-W02   │ 6,200  │ 4,092  │ 3,224  │ 2,790  │ 2,480  │
           │ 100%   │ 66%    │ 52%    │ 45%    │ 40%    │
───────────┼────────┼────────┼────────┼────────┼────────┼──────
2026-W03   │ 4,800  │ 3,120  │ 2,544  │ 2,112  │        │
           │ 100%   │ 65%    │ 53%    │ 44%    │        │
───────────┼────────┼────────┼────────┼────────┼────────┼──────
2026-W04   │ 7,500  │ 5,025  │ 3,975  │        │        │
           │ 100%   │ 67%    │ 53%    │        │        │
═══════════════════════════════════════════════════════════════

📊 分析结论：
• 第2周留存稳定在65-67%，表现良好
• 第4周留存约44-48%，有提升空间
• W04批次用户质量最佳，可持续关注

5.5 异常检测方法

当数据量很大时，靠人工发现异常是不现实的。需要建立自动化的异常检测机制。

基于历史数据的均值和标准差设定阈值。

-- 统计阈值法：3σ原则
WITH daily_stats AS (
    SELECT 
        redeem_date,
        COUNT(*) as daily_redeems
    FROM dwd_gift_code_redeem_detail
    WHERE redeem_date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
    GROUP BY redeem_date
),
stats AS (
    SELECT 
        AVG(daily_redeems) as mean_value,
        STDDEV(daily_redeems) as std_value
    FROM daily_stats
)
SELECT 
    d.redeem_date,
    d.daily_redeems,
    s.mean_value,
    s.std_value,
    (d.daily_redeems - s.mean_value) / s.std_value as z_score,
    CASE 
        WHEN ABS((d.daily_redeems - s.mean_value) / s.std_value) > 3 THEN 'ANOMALY'
        WHEN ABS((d.daily_redeems - s.mean_value) / s.std_value) > 2 THEN 'WARNING'
        ELSE 'NORMAL'
    END as status
FROM daily_stats d
CROSS JOIN stats s
ORDER BY ABS(z_score) DESC;

与上一个周期对比，变化超过阈值则告警。

-- 环比变化告警
SELECT 
    activity_id,
    redeem_date,
    daily_redeems,
    prev_day_redeems,
    ROUND((daily_redeems - prev_day_redeems) * 100.0 / NULLIF(prev_day_redeems, 0), 2) as change_pct,
    CASE 
        WHEN change_pct > 50 THEN 'ALERT_UP'    -- 上涨超过50%
        WHEN change_pct < -50 THEN 'ALERT_DOWN' -- 下跌超过50%
        WHEN change_pct > 30 THEN 'WARNING_UP'
        WHEN change_pct < -30 THEN 'WARNING_DOWN'
        ELSE 'NORMAL'
    END as status
FROM (
    SELECT 
        activity_id,
        redeem_date,
        COUNT(*) as daily_redeems,
        LAG(COUNT(*)) OVER (PARTITION BY activity_id ORDER BY redeem_date) as prev_day_redeems
    FROM dwd_gift_code_redeem_detail
    WHERE redeem_date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
    GROUP BY activity_id, redeem_date
) t
WHERE ABS(change_pct) > 30;

结合同比和环比，更准确地判断异常。

-- 同环比联合检测
SELECT 
    metric_name,
    metric_date,
    current_value,
    prev_day_value,
    prev_week_value,
    ROUND((current_value - prev_day_value) * 100.0 / NULLIF(prev_day_value, 0), 2) as dod_change,
    ROUND((current_value - prev_week_value) * 100.0 / NULLIF(prev_week_value, 0), 2) as wow_change,
    CASE 
        -- 同环比同时大幅上涨/下跌，更可能是真实异常
        WHEN dod_change > 30 AND wow_change > 30 THEN 'STRONG_ALERT_UP'
        WHEN dod_change < -30 AND wow_change < -30 THEN 'STRONG_ALERT_DOWN'
        -- 仅环比变化，可能是周期性波动
        WHEN ABS(dod_change) > 30 AND ABS(wow_change) < 15 THEN 'WEAK_WARNING'
        ELSE 'NORMAL'
    END as alert_level
FROM daily_metrics
WHERE metric_date = CURRENT_DATE - INTERVAL 1 DAY;

六、运营决策支持

数据分析的最终目的，是支持运营决策。数据不是终点，决策才是。

6.1 活动优化决策

根据数据调整奖励配置：

-- 奖励效果分析，指导优化
SELECT 
    reward_type,
    COUNT(*) as redeem_count,
    AVG(day7_retention) as avg_retention,
    AVG(pay_convert_rate) as avg_convert,
    -- 综合评分 = 热度*0.3 + 留存*0.4 + 转化*0.3
    ROUND((redeem_count / max_redeems * 30) + 
          (avg_retention * 40) + 
          (avg_convert * 30), 2) as composite_score
FROM reward_analysis
GROUP BY reward_type
ORDER BY composite_score DESC;

🎯 奖励优化建议
═══════════════════════════════════════════════════

【保留】黄金礼包、钻石礼包
  • 综合评分 > 80
  • 留存贡献高，用户满意度高

【优化】白银礼包
  • 热度中等，但转化偏低
  • 建议：增加额外小奖励提升吸引力

【替换】铜币礼包
  • 综合评分 < 40
  • 用户反馈"奖励太少"
  • 建议：替换为抽奖券或限时道具

6.2 渠道优化决策

-- 渠道ROI综合评估
SELECT 
    channel_name,
    total_cost,
    total_revenue,
    ROUND(total_revenue / NULLIF(total_cost, 0), 2) as roi,
    -- LTV预测（简化版：30日收入 * 3）
    ROUND(total_revenue_30d * 3 / NULLIF(total_cost, 0), 2) as predicted_ltv_roi,
    -- 建议动作
    CASE 
        WHEN roi >= 2 THEN 'SCALE_UP'      -- 扩大投放
        WHEN roi >= 1 THEN 'MAINTAIN'      -- 维持投放
        WHEN roi >= 0.5 THEN 'OPTIMIZE'    -- 优化投放
        ELSE 'PAUSE'                        -- 暂停投放
    END as suggested_action
FROM channel_roi_summary
ORDER BY roi DESC;

6.3 风险预警系统

-- 风险监控仪表盘SQL
SELECT 
    '高频兑换' as risk_type,
    COUNT(*) as risk_count,
    GROUP_CONCAT(DISTINCT code_id) as affected_codes
FROM (
    SELECT code_id, COUNT(*) as cnt
    FROM gift_code_redeem_log
    WHERE redeem_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
    GROUP BY code_id
    HAVING cnt > 10
) t

UNION ALL

SELECT 
    'IP异常' as risk_type,
    COUNT(DISTINCT redeem_ip) as risk_count,
    GROUP_CONCAT(DISTINCT redeem_ip) as affected_ips
FROM gift_code_redeem_log
WHERE redeem_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
GROUP BY redeem_ip
HAVING COUNT(*) > 100

UNION ALL

SELECT 
    '设备关联' as risk_type,
    COUNT(DISTINCT device_id) as risk_count,
    ''
FROM (
    SELECT device_id, COUNT(DISTINCT user_id) as user_cnt
    FROM gift_code_redeem_log
    WHERE redeem_time >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
    GROUP BY device_id
    HAVING user_cnt > 5
) t;

``` ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │ 风险检测 │────▶│ 风险评估 │────▶│ 预警通知 │ │ (实时监控) │ │ (自动分级) │ │ (多渠道) │ └─────────────┘ └─────────────┘ └─────────────┘ │ ▼ ┌─────────────┐ │ 人工确认 │ │ (可选) │ └─────────────┘ │ ▼ ┌─────────────┐ │ 处置措施 │ │ (自动/手动) │ └