基于MySQL实现60天、90天及每日用户流失率分析的技术请求

Got it, let's adapt your existing monthly churn calculation to handle daily, 60-day, and 90-day churn rates. The core idea stays aligned with your original approach—tracking user activity periods and identifying when they stop returning—but we'll adjust the time granularity and churn criteria for each scenario. Here's the breakdown:

Daily Churn Rate Calculation #

For daily churn, we'll define it as the percentage of users active on a given day who don't return to log in within the next day (you can adjust this threshold to match your business definition, e.g., 7 days of inactivity).

-- Daily churn rate: % of daily active users (DAU) who don't return within 1 day
WITH daily_usage AS (
    -- Get distinct users active each day
    SELECT 
        who_identifier,
        DATE(when_timestamp) AS active_date
    FROM events
    WHERE event = 'login'
    GROUP BY who_identifier, DATE(when_timestamp)
),
next_active_tracking AS (
    -- For each user's active day, get their next login date
    SELECT 
        who_identifier,
        active_date,
        LEAD(active_date, 1) OVER (PARTITION BY who_identifier ORDER BY active_date) AS next_login_date
    FROM daily_usage
),
churned_daily_users AS (
    -- Count users who either never log in again, or wait >1 day to return
    SELECT 
        active_date,
        COUNT(DISTINCT who_identifier) AS churned_user_count
    FROM next_active_tracking
    WHERE DATEDIFF(day, active_date, next_login_date) > 1 
        OR next_login_date IS NULL
    GROUP BY active_date
),
daily_active_summary AS (
    -- Calculate total daily active users (DAU)
    SELECT 
        active_date,
        COUNT(DISTINCT who_identifier) AS dau
    FROM daily_usage
    GROUP BY active_date
)
-- Combine to get daily churn rate
SELECT 
    das.active_date,
    das.dau,
    COALESCE(cdu.churned_user_count, 0) AS churned_users,
    ROUND(COALESCE(cdu.churned_user_count, 0) * 100.0 / das.dau, 2) AS daily_churn_rate_percent
FROM daily_active_summary das
LEFT JOIN churned_daily_users cdu ON das.active_date = cdu.active_date
ORDER BY das.active_date;

60-Day Churn Rate Calculation #

For 60-day churn, we'll group time into 60-day periods (like your monthly grouping) and calculate the percentage of users active in a period who don't return in the next consecutive 60-day period.

-- 60-day churn rate: % of users active in a 60-day window who don't return in the next window
WITH sixty_day_periods AS (
    -- Group user logins into 60-day cycles
    SELECT 
        who_identifier,
        -- Generate a unique ID for each 60-day period starting from 1970-01-01
        FLOOR(DATEDIFF(day, '1970-01-01', DATE(when_timestamp)) / 60) AS period_id
    FROM events
    WHERE event = 'login'
    GROUP BY who_identifier, FLOOR(DATEDIFF(day, '1970-01-01', DATE(when_timestamp)) / 60)
),
next_period_tracking AS (
    -- Track each user's next active 60-day period
    SELECT 
        who_identifier,
        period_id,
        LEAD(period_id, 1) OVER (PARTITION BY who_identifier ORDER BY period_id) AS next_period_id
    FROM sixty_day_periods
),
churned_sixty_day_users AS (
    -- Count users who skip the next period or never return
    SELECT 
        period_id,
        COUNT(DISTINCT who_identifier) AS churned_user_count
    FROM next_period_tracking
    WHERE next_period_id != period_id + 1 
        OR next_period_id IS NULL
    GROUP BY period_id
),
sixty_day_active_summary AS (
    -- Calculate total active users per 60-day period
    SELECT 
        period_id,
        COUNT(DISTINCT who_identifier) AS period_active_users
    FROM sixty_day_periods
    GROUP BY period_id
)
-- Combine to get 60-day churn rate with readable date ranges
SELECT 
    DATE_ADD('1970-01-01', INTERVAL period_id * 60 DAY) AS period_start,
    DATE_ADD('1970-01-01', INTERVAL (period_id + 1) * 60 DAY) AS period_end,
    sdas.period_active_users,
    COALESCE(csdu.churned_user_count, 0) AS churned_users,
    ROUND(COALESCE(csdu.churned_user_count, 0) * 100.0 / sdas.period_active_users, 2) AS sixty_day_churn_rate_percent
FROM sixty_day_active_summary sdas
LEFT JOIN churned_sixty_day_users csdu ON sdas.period_id = csdu.period_id
ORDER BY sdas.period_id;

90-Day Churn Rate Calculation #

This follows the exact same logic as the 60-day calculation—we just swap the 60-day interval for 90 days:

-- 90-day churn rate: % of users active in a 90-day window who don't return in the next window
WITH ninety_day_periods AS (
    -- Group user logins into 90-day cycles
    SELECT 
        who_identifier,
        FLOOR(DATEDIFF(day, '1970-01-01', DATE(when_timestamp)) / 90) AS period_id
    FROM events
    WHERE event = 'login'
    GROUP BY who_identifier, FLOOR(DATEDIFF(day, '1970-01-01', DATE(when_timestamp)) / 90)
),
next_period_tracking AS (
    -- Track each user's next active 90-day period
    SELECT 
        who_identifier,
        period_id,
        LEAD(period_id, 1) OVER (PARTITION BY who_identifier ORDER BY period_id) AS next_period_id
    FROM ninety_day_periods
),
churned_ninety_day_users AS (
    -- Count users who skip the next period or never return
    SELECT 
        period_id,
        COUNT(DISTINCT who_identifier) AS churned_user_count
    FROM next_period_tracking
    WHERE next_period_id != period_id + 1 
        OR next_period_id IS NULL
    GROUP BY period_id
),
ninety_day_active_summary AS (
    -- Calculate total active users per 90-day period
    SELECT 
        period_id,
        COUNT(DISTINCT who_identifier) AS period_active_users
    FROM ninety_day_periods
    GROUP BY period_id
)
-- Combine to get 90-day churn rate with readable date ranges
SELECT 
    DATE_ADD('1970-01-01', INTERVAL period_id * 90 DAY) AS period_start,
    DATE_ADD('1970-01-01', INTERVAL (period_id + 1) * 90 DAY) AS period_end,
    ndas.period_active_users,
    COALESCE(cndu.churned_user_count, 0) AS churned_users,
    ROUND(COALESCE(cndu.churned_user_count, 0) * 100.0 / ndas.period_active_users, 2) AS ninety_day_churn_rate_percent
FROM ninety_day_active_summary ndas
LEFT JOIN churned_ninety_day_users cndu ON ndas.period_id = cndu.period_id
ORDER BY ndas.period_id;

Key Notes for Adaptation #

• Churn Definition Flexibility: If your business defines churn as "N days of inactivity" (e.g., 7 days instead of 1 for daily), adjust the DATEDIFF threshold in the daily query, or modify the period gap check for 60/90-day calculations.
• Time Zone Handling: If your when_timestamp uses a specific time zone, use CONVERT_TZ to align dates to your business's local time before grouping.
• Performance: For large datasets, pre-aggregate daily active users into a dedicated table to speed up repeated queries. Adding indexes on who_identifier and when_timestamp will also help optimize window function performance.

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