月度客户转化关闭率计算：SQL实现需求问询

需要按月份维度统计两个核心指标：

• 客户转化关闭率：当月标记为first_login的客户中，后续产生sale行为的客户占比，公式：
  close rate = (有sale的客户数 / 当月first_login客户数) * 100
• 销售金额占比：后续产生的sale金额总和与当月first_login金额总和的占比，公式：
  close vol rate = (sale总金额 / first_login总金额) * 100

创建数据表的SQL脚本：

CREATE TABLE data (account_id int, cust_status varchar(25), amount int, date date);

INSERT INTO data VALUES
    ('1', 'first_login', '1000', '01/01/2021'),
    ('1', 'sale',        '500',  '28/01/2021'),
    ('2', 'first_login', '2000', '07/03/2021'),
    ('3', 'first_login', '1000', '01/06/2021'),
    ('3', 'sale',        '2000', '05/08/2021'),
    ('4', 'first_login', '5000', '18/06/2021'),
    ('4', 'sale',        '3000', '18/09/2021'),
    ('5', 'first_login', '5000', '02/06/2021'),
    ('6', 'first_login', '2000', '11/06/2021'),
    ('7', 'first_login', '1000', '22/06/2021'),
    ('8', 'first_login', '3000', '29/06/2021');

以下是兼容主流SQL数据库（MySQL需调整日期函数）的实现代码：

WITH first_login_data AS (
    SELECT 
        account_id,
        DATE_TRUNC('month', date) AS login_month,
        amount AS login_amount
    FROM data
    WHERE cust_status = 'first_login'
),
sale_data AS (
    SELECT 
        account_id,
        SUM(amount) AS total_sale
    FROM data
    WHERE cust_status = 'sale'
    GROUP BY account_id
)
SELECT
    TO_CHAR(fl.login_month, 'FMMonth') AS month,
    -- 计算转化关闭率，处理除数为0的情况
    CASE 
        WHEN COUNT(fl.account_id) = 0 THEN '0'
        ELSE ROUND(COUNT(sd.account_id)::FLOAT / COUNT(fl.account_id) * 100, 1) || '%'
    END AS close_rate,
    -- 计算销售金额占比，处理除数为0或无销售数据的情况
    CASE 
        WHEN SUM(fl.login_amount) = 0 THEN '0'
        WHEN SUM(sd.total_sale) IS NULL THEN '0'
        ELSE ROUND(SUM(sd.total_sale)::FLOAT / SUM(fl.login_amount) * 100, 2) || '%'
    END AS close_vol_rate
FROM first_login_data fl
LEFT JOIN sale_data sd ON fl.account_id = sd.account_id
GROUP BY fl.login_month
ORDER BY fl.login_month;

代码说明 #

1. CTE拆分数据：
   • first_login_data：筛选所有首次登录记录，提取登录月份和对应金额
   • sale_data：按客户聚合，统计每个客户的总销售金额
2. 关联计算：通过account_id左连接两个CTE，确保所有首次登录客户都被纳入统计
3. 指标计算：
   • 转化关闭率：用有销售记录的客户数除以当月首次登录客户数，乘以100后格式化百分比
   • 销售金额占比：用总销售金额除以首次登录总金额，乘以100后格式化百分比
4. 异常处理：添加CASE语句处理除数为0或无销售数据的情况，避免报错

内容的提问来源于stack exchange，提问作者Rookie