segment_health.sql

WITH retention_flags AS (
    SELECT
        customer_id,
        MAX(activity_date) AS last_active_date,
        MAX(CASE WHEN days_since_signup >= 1 THEN 1 ELSE 0 END) AS retained_d1,
        MAX(CASE WHEN days_since_signup >= 7 THEN 1 ELSE 0 END) AS retained_d7,
        MAX(CASE WHEN days_since_signup >= 30 THEN 1 ELSE 0 END) AS retained_d30
    FROM fct_customer_daily_activity
    GROUP BY 1
),
snapshot AS (
    SELECT MAX(order_date) AS snapshot_date
    FROM fct_orders
)
SELECT
    c.acquisition_channel,
    c.customer_value_segment,
    c.geo_region,
    COUNT(*) AS customers,
    ROUND(AVG(c.total_revenue), 2) AS revenue_per_customer,
    ROUND(AVG(c.order_count), 2) AS orders_per_customer,
    ROUND(AVG(CASE WHEN c.order_count >= 2 THEN 1 ELSE 0 END), 4) AS repeat_rate,
    ROUND(AVG(COALESCE(r.retained_d1, 0)), 4) AS d1_retention,
    ROUND(AVG(COALESCE(r.retained_d7, 0)), 4) AS d7_retention,
    ROUND(AVG(COALESCE(r.retained_d30, 0)), 4) AS d30_retention,
    ROUND(
        AVG(
            CASE
                WHEN DATEDIFF('day', r.last_active_date, snapshot.snapshot_date) > 45 THEN 1
                ELSE 0
            END
        ),
        4
    ) AS at_risk_share,
    CASE
        WHEN AVG(COALESCE(r.retained_d30, 0)) < 0.12
            OR AVG(CASE WHEN c.order_count >= 2 THEN 1 ELSE 0 END) < 0.20
            OR AVG(
                CASE
                    WHEN DATEDIFF('day', r.last_active_date, snapshot.snapshot_date) > 45 THEN 1
                    ELSE 0
                END
            ) > 0.45 THEN 'At Risk'
        WHEN AVG(COALESCE(r.retained_d30, 0)) < 0.20
            OR AVG(CASE WHEN c.order_count >= 2 THEN 1 ELSE 0 END) < 0.30 THEN 'Watch'
        ELSE 'Healthy'
    END AS health_status
FROM dim_customers AS c
LEFT JOIN retention_flags AS r
    ON c.customer_id = r.customer_id
CROSS JOIN snapshot
GROUP BY 1, 2, 3;