如何识别符合条件的新用户？新增活动天数下达标新用户统计

1. 确定每个用户的首次达标日期 #

核心是找出每个用户第一次满足31天周期内累计消费超200的日期——这是判断“新增达标用户”的关键，毕竟我们只关注从未达标过的新用户。

实现步骤 #

• 先按用户+日期聚合每日消费，避免同一用户同一天多条订单导致重复统计；
• 用窗口函数计算每个用户每天的滚动31天累计消费；
• 筛选出累计消费超200的记录，取每个用户最早的达标日期，即为他们的首次达标时间点。

WITH user_daily_spend AS (
    -- 聚合用户每日消费总额
    SELECT 
        user_id,
        order_date,
        SUM(spend) AS daily_spend
    FROM user_table
    GROUP BY user_id, order_date
),
rolling_31d_spend AS (
    -- 计算滚动31天的累计消费
    SELECT 
        user_id,
        order_date,
        SUM(daily_spend) OVER (
            PARTITION BY user_id 
            ORDER BY order_date 
            RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW
        ) AS rolling_31d_total
    FROM user_daily_spend
),
first_qualify_date AS (
    -- 获取每个用户首次达标的日期
    SELECT 
        user_id,
        MIN(order_date) AS first_qualify_dt
    FROM rolling_31d_spend
    WHERE rolling_31d_total > 200
    GROUP BY user_id
)

2. 计算延长周期内的日均新增达标用户 #

假设原活动截止到original_end_date，延长后到extended_end_date，只需统计延长时间段内首次达标的用户数量，再除以延长天数即可得到平均值。

最终查询语句 #

SELECT 
    COUNT(DISTINCT user_id) AS total_new_qualified_users,
    (extended_end_date - original_end_date) AS extended_days,
    ROUND(COUNT(DISTINCT user_id)::FLOAT / (extended_end_date - original_end_date), 2) AS avg_daily_new_users
FROM first_qualify_date
WHERE first_qualify_dt > original_end_date
  AND first_qualify_dt <= extended_end_date;

关键细节说明 #

• 数据库语法适配：如果使用MySQL，需把RANGE BETWEEN INTERVAL '30 days' PRECEDING改成ROWS BETWEEN 30 PRECEDING AND CURRENT ROW（前提是日期连续；若日期不连续，需用日期差判断）；
• 首次达标逻辑：这里的首次达标日期是用户第一个满足31天累计消费超200的日期，确保只统计从未被触达过的新用户；
• 去重处理：通过MIN(order_date)和GROUP BY user_id保证每个用户只被统计一次，避免重复计算。

示例（PostgreSQL） #

假设原活动到2023-10-31，延长到2023-11-30，直接代入日期即可：

WITH user_daily_spend AS (
    SELECT 
        user_id,
        order_date,
        SUM(spend) AS daily_spend
    FROM user_table
    GROUP BY user_id, order_date
),
rolling_31d_spend AS (
    SELECT 
        user_id,
        order_date,
        SUM(daily_spend) OVER (
            PARTITION BY user_id 
            ORDER BY order_date 
            RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW
        ) AS rolling_31d_total
    FROM user_daily_spend
),
first_qualify_date AS (
    SELECT 
        user_id,
        MIN(order_date) AS first_qualify_dt
    FROM rolling_31d_spend
    WHERE rolling_31d_total > 200
    GROUP BY user_id
)
SELECT 
    COUNT(DISTINCT user_id) AS total_new_qualified_users,
    (DATE '2023-11-30' - DATE '2023-10-31') AS extended_days,
    ROUND(COUNT(DISTINCT user_id)::FLOAT / (DATE '2023-11-30' - DATE '2023-10-31'), 2) AS avg_daily_new_users
FROM first_qualify_date
WHERE first_qualify_dt > DATE '2023-10-31'
  AND first_qualify_dt <= DATE '2023-11-30';

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