SQL 实战：新购率与续约率精准计算（含可复用模板 + 运营落地策略）

在用户增长与留存运营中，新购率（衡量新增用户转化效率）和续约率（衡量存量用户留存质量）是核心指标。但多数运营场景中，指标计算常因「定义模糊」「数据口径不一致」导致决策偏差。
本文将从技术视角出发，基于SQL实现两大指标的精准计算，再结合多维数据拆解，给出可落地的运营策略，适合数据分析师、运营技术岗及后端开发参考。

一、指标定义与数据模型

1.1 核心指标定义（避免口径争议）

指标	计算公式	业务含义	统计周期
新购率	周期内新购用户数 ÷ 周期内新增用户数 × 100%	新增用户中完成首次付费的比例	日/周/月（常用月）
续约率	周期内续约用户数 ÷ 周期内到期用户数 × 100%	到期用户中完成续购的比例	日/周/月（常用月）

关键概念补充：

新购用户：首次注册后，在「注册后N天内」（如30天）完成首次付费的用户；
到期用户：历史付费订单的「过期时间」落在统计周期内的用户；
续约用户：到期用户在「过期后M天内」（如7天）完成续购的用户。

1.2 依赖数据表与字段（通用模型）

假设业务系统有两张核心表（字段可根据实际业务调整）：

-- 1. 用户信息表（user_info）：存储用户基础信息
CREATE TABLE user_info (
    user_id VARCHAR(64) PRIMARY KEY, -- 用户唯一标识
    create_time DATETIME NOT NULL, -- 注册时间
    channel VARCHAR(32) NOT NULL, -- 获客渠道（如APP/公众号/地推）
    is_test TINYINT DEFAULT 0 -- 是否测试用户（1=是，0=否）
);

-- 2. 订单信息表（order_info）：存储付费订单数据
CREATE TABLE order_info (
    order_id VARCHAR(64) PRIMARY KEY, -- 订单唯一标识
    user_id VARCHAR(64) NOT NULL, -- 关联用户
    order_amount DECIMAL(10,2) NOT NULL, -- 订单金额
    pay_time DATETIME NOT NULL, -- 支付时间
    expire_time DATETIME NOT NULL, -- 订单过期时间（如会员到期日）
    order_status VARCHAR(16) NOT NULL, -- 订单状态（paid=已支付，canceled=已取消）
    refund_status VARCHAR(16) DEFAULT 'no_refund', -- 退款状态（refunded=已退款，no_refund=未退款）
    order_type VARCHAR(16) NOT NULL -- 订单类型（new=新购，renew=续购，upgrade=升级）
);

二、SQL精准计算核心指标

2.1 新购率计算（含多场景适配）

核心逻辑：

筛选统计周期内的「新增有效用户」（排除测试用户）；
匹配该批用户在「注册后N天内」的有效付费订单（已支付、未退款、金额>0）；
按周期分组计算新购率。

SQL模板（以月度统计为例，N=30天）：

WITH 
-- 步骤1：获取月度新增有效用户（首次注册时间在当月）
new_users AS (
    SELECT 
        user_id,
        create_time AS first_register_time,
        channel -- 保留渠道字段，用于后续多维分析
    FROM user_info
    WHERE 
        DATE_FORMAT(create_time, '%Y-%m') = '2024-01' -- 统计2024年1月，可参数化
        AND is_test = 0 -- 排除测试用户
),
-- 步骤2：获取新购有效订单（新增用户注册后30天内的首次付费）
new_pay_orders AS (
    SELECT 
        o.user_id,
        MIN(o.pay_time) AS first_pay_time -- 取首次付费时间（避免同一用户多笔新购订单重复统计）
    FROM order_info o
    JOIN new_users nu ON o.user_id = nu.user_id
    WHERE 
        o.pay_time BETWEEN nu.first_register_time 
                      AND DATE_ADD(nu.first_register_time, INTERVAL 30 DAY) -- 注册后30天内
        AND o.order_status = 'paid'
        AND o.refund_status = 'no_refund'
        AND o.order_amount > 0
    GROUP BY o.user_id
)
-- 步骤3：计算月度新购率（含渠道维度）
SELECT 
    DATE_FORMAT(nu.first_register_time, '%Y-%m') AS stat_month,
    nu.channel,
    COUNT(DISTINCT nu.user_id) AS total_new_users, -- 月度新增用户数
    COUNT(DISTINCT npo.user_id) AS new_pay_users, -- 月度新购用户数
    ROUND(
        IFNULL(COUNT(DISTINCT npo.user_id) / COUNT(DISTINCT nu.user_id), 0) * 100, 
        2
    ) AS new_purchase_rate -- 新购率（保留2位小数）
FROM new_users nu
LEFT JOIN new_pay_orders npo ON nu.user_id = npo.user_id
GROUP BY stat_month, nu.channel
ORDER BY stat_month, new_purchase_rate DESC;

2.2 续约率计算（解决「到期用户识别」「续约匹配」核心问题）

核心难点：

如何精准识别「周期内到期用户」（避免重复统计同一用户多笔到期订单）；
如何匹配「到期后M天内的续购订单」（排除跨周期续约干扰）。

SQL模板（以月度统计为例，M=7天）：

WITH 
-- 步骤1：获取月度到期有效用户（订单过期时间在当月，排除测试/退款订单）
expire_users AS (
    SELECT 
        user_id,
        expire_time,
        order_id AS expire_order_id,
        order_amount AS expire_order_amount -- 保留到期订单金额，用于用户分层
    FROM order_info
    WHERE 
        DATE_FORMAT(expire_time, '%Y-%m') = '2024-01' -- 统计2024年1月
        AND order_status = 'paid'
        AND refund_status = 'no_refund'
        AND order_amount > 0
    GROUP BY user_id, expire_time -- 同一用户当月多笔到期订单，按到期时间拆分
),
-- 步骤2：获取续约有效订单（到期后7天内的续购订单）
renew_orders AS (
    SELECT 
        o.user_id,
        o.pay_time AS renew_pay_time,
        eu.expire_time AS prev_expire_time -- 关联原到期时间，确保匹配准确性
    FROM order_info o
    JOIN expire_users eu ON o.user_id = eu.user_id
    WHERE 
        o.pay_time BETWEEN eu.expire_time 
                      AND DATE_ADD(eu.expire_time, INTERVAL 7 DAY) -- 到期后7天内
        AND o.order_status = 'paid'
        AND o.refund_status = 'no_refund'
        AND o.order_amount > 0
        AND o.order_id != eu.expire_order_id -- 排除原到期订单（避免误判）
)
-- 步骤3：计算月度续约率（含用户金额分层）
SELECT 
    DATE_FORMAT(eu.expire_time, '%Y-%m') AS stat_month,
    -- 按到期订单金额分层（高价值用户：>1000，中价值：300-1000，低价值：0）
    CASE 
        WHEN eu.expire_order_amount > 1000 THEN 'high_value'
        WHEN eu.expire_order_amount BETWEEN 300 AND 1000 THEN 'mid_value'
        ELSE 'low_value'
    END AS user_value_level,
    COUNT(DISTINCT eu.user_id) AS total_expire_users, -- 月度到期用户数
    COUNT(DISTINCT ro.user_id) AS renew_users, -- 月度续约用户数
    ROUND(
        IFNULL(COUNT(DISTINCT ro.user_id) / COUNT(DISTINCT eu.user_id), 0) * 100, 
        2
    ) AS renewal_rate -- 续约率
FROM expire_users eu
LEFT JOIN renew_orders ro 
    ON eu.user_id = ro.user_id 
    AND eu.expire_time = ro.prev_expire_time -- 精准匹配到期时间与续约时间
GROUP BY stat_month, user_value_level
ORDER BY stat_month, renewal_rate DESC;

三、指标多维拆解与数据洞察（技术驱动业务）

通过SQL扩展维度，可定位核心问题。以下是高频拆解场景及SQL示例：

3.1 按获客渠道拆解新购率（优化渠道投放）

-- 基于2.1的新购率SQL，新增渠道排序，定位高转化渠道
SELECT 
    channel,
    new_purchase_rate,
    total_new_users,
    -- 计算渠道贡献度（该渠道新购用户数 ÷ 全量新购用户数）
    ROUND(
        COUNT(DISTINCT npo.user_id) / (SELECT COUNT(DISTINCT user_id) FROM new_pay_orders) * 100,
        2
    ) AS pay_contribution_rate
FROM new_users nu
LEFT JOIN new_pay_orders npo ON nu.user_id = npo.user_id
GROUP BY channel
ORDER BY new_purchase_rate DESC, pay_contribution_rate DESC;

洞察结论示例：

「公众号」渠道新购率18.2%，贡献度45% → 加大投放；
「地推」渠道新购率3.5%，贡献度8% → 优化获客话术或暂停投放。

3.2 按用户生命周期拆解续约率（分层运营）

-- 新增用户注册时间维度，分析不同批次用户的续约表现
WITH user_life_cycle AS (
    SELECT 
        u.user_id,
        u.create_time,
        -- 按注册时间分组（如2023Q1/Q2/Q3/Q4）
        DATE_FORMAT(u.create_time, '%YQ%q') AS register_quarter
    FROM user_info u
    WHERE u.is_test = 0
)
SELECT 
    ulc.register_quarter,
    COUNT(DISTINCT eu.user_id) AS total_expire_users,
    COUNT(DISTINCT ro.user_id) AS renew_users,
    ROUND(
        IFNULL(COUNT(DISTINCT ro.user_id) / COUNT(DISTINCT eu.user_id), 0) * 100,
        2
    ) AS renewal_rate
FROM expire_users eu
JOIN user_life_cycle ulc ON eu.user_id = ulc.user_id
LEFT JOIN renew_orders ro ON eu.user_id = ro.user_id AND eu.expire_time = ro.prev_expire_time
GROUP BY ulc.register_quarter
ORDER BY ulc.register_quarter;

洞察结论示例：

2023Q4注册用户续约率42%，2023Q1注册用户续约率68% → 新用户留存能力弱，需优化新手引导。

四、基于SQL数据的运营策略落地

4.1 新购率提升策略（精准触达+转化优化）

数据洞察	运营策略	技术落地（SQL辅助）
某渠道新购率低但流量大	优化该渠道落地页，增加试用品权益	SQL筛选该渠道新增用户，推送定向优惠券
注册后7-15天新购转化率低	触发「挽回话术+限时折扣」推送	用SQL筛选注册后7天未付费用户，对接推送系统
低客单价用户新购率高	推出「小额入门套餐」降低决策门槛	SQL统计新购订单金额分布，确定入门套餐定价

示例：SQL筛选注册后7天未付费的高潜力用户（按渠道+注册设备）

SELECT 
    user_id,
    channel,
    create_time,
    -- 假设用户表有设备字段（device_type）
    device_type
FROM user_info
WHERE 
    create_time BETWEEN DATE_SUB(NOW(), INTERVAL 15 DAY) AND DATE_SUB(NOW(), INTERVAL 7 DAY)
    AND is_test = 0
    AND user_id NOT IN (
        SELECT DISTINCT user_id FROM order_info 
        WHERE pay_time BETWEEN create_time AND DATE_ADD(create_time, INTERVAL 7 DAY)
          AND order_status = 'paid'
    );

4.2 续约率提升策略（分层维护+到期提醒）

数据洞察	运营策略	技术落地（SQL辅助）
高价值用户续约率低	专属客服一对一跟进+续约赠礼	SQL筛选到期金额>1000且未续约用户，分配客服
到期后3天内续约占比80%	到期前3天触发「递进式提醒」（短信+APP推送）	SQL定时查询即将到期用户，对接提醒系统
连续续约2次以上用户续约率高	推出「年付折扣套餐」锁定长期用户	SQL筛选连续续约≥2次的用户，推送年付优惠

示例：SQL筛选高价值未续约用户（到期后7天内未续购）

SELECT 
    eu.user_id,
    eu.expire_order_amount,
    eu.expire_time,
    u.channel
FROM expire_users eu
JOIN user_info u ON eu.user_id = u.user_id
LEFT JOIN renew_orders ro ON eu.user_id = ro.user_id AND eu.expire_time = ro.prev_expire_time
WHERE 
    eu.expire_order_amount > 1000
    AND ro.user_id IS NULL -- 未续约
    AND eu.expire_time BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW(); -- 近7天到期

五、SQL性能优化与数据质量避坑

5.1 性能优化（处理千万级订单数据）

索引优化：给高频过滤字段加索引，避免全表扫描

-- 订单表核心索引（适配新购/续约计算场景）
ALTER TABLE order_info ADD INDEX idx_user_pay (user_id, pay_time);
ALTER TABLE order_info ADD INDEX idx_user_expire (user_id, expire_time);
-- 用户表索引
ALTER TABLE user_info ADD INDEX idx_create_time (create_time);

避免重复计算：用CTE（公共表表达式）替代子查询嵌套，提升可读性与性能；
分区表优化：订单表按pay_time或expire_time分区，缩小查询范围。

5.2 数据质量避坑（避免指标失真）

排除异常数据：测试用户、退款订单、零金额订单必须过滤；
时区统一：所有时间字段（create_time/pay_time）使用UTC时间，避免跨时区统计偏差；
去重逻辑：同一用户同一周期内多次新购/续约，按「首次行为」统计（用MIN(pay_time)去重）；
NULL值处理：用IFNULL避免分母为0导致的计算错误（如IFNULL(COUNT(DISTINCT ro.user_id), 0)）。

总结

新购率与续约率的核心价值，在于通过数据驱动运营决策——而SQL是实现「精准计算-多维拆解-策略落地」的关键工具。本文提供的SQL模板可直接适配多数业务场景，读者可根据实际表结构调整字段与筛选条件。

后续可扩展方向：

结合BI工具（如Tableau、Metabase）将SQL结果可视化，实时监控指标波动；
用Python+SQL实现自动化报表（如定时生成月度运营报告）；
引入用户行为数据（如点击/浏览日志），进一步拆解转化漏斗。

如果在实际使用中遇到表结构适配、性能瓶颈等问题，欢迎在评论区交流～

posted @ 2025-12-17 15:31 code_paio 阅读(71) 评论(0) 收藏举报

刷新页面返回顶部

cmggcode2025

SQL 实战：新购率与续约率精准计算（含可复用模板 + 运营落地策略）

一、指标定义与数据模型

1.1 核心指标定义（避免口径争议）

关键概念补充：

1.2 依赖数据表与字段（通用模型）

二、SQL精准计算核心指标

2.1 新购率计算（含多场景适配）

核心逻辑：

SQL模板（以月度统计为例，N=30天）：

2.2 续约率计算（解决「到期用户识别」「续约匹配」核心问题）

核心难点：

SQL模板（以月度统计为例，M=7天）：

三、指标多维拆解与数据洞察（技术驱动业务）

3.1 按获客渠道拆解新购率（优化渠道投放）

洞察结论示例：

3.2 按用户生命周期拆解续约率（分层运营）

洞察结论示例：

四、基于SQL数据的运营策略落地

4.1 新购率提升策略（精准触达+转化优化）

示例：SQL筛选注册后7天未付费的高潜力用户（按渠道+注册设备）

4.2 续约率提升策略（分层维护+到期提醒）

示例：SQL筛选高价值未续约用户（到期后7天内未续购）

五、SQL性能优化与数据质量避坑

5.1 性能优化（处理千万级订单数据）

5.2 数据质量避坑（避免指标失真）

总结

公告