Advanced SQL Analytics Techniques for Business Intelligence

Predictive Customer Lifetime Value

-- Advanced CLV calculation with cohort analysis and predictive elements
WITH customer_cohorts AS (
    SELECT 
        customer_id,
        MIN(order_date) as first_order_date,
        DATE_TRUNC('month', MIN(order_date)) as cohort_month
    FROM orders
    GROUP BY customer_id
),

monthly_customer_activity AS (
    SELECT 
        c.customer_id,
        c.cohort_month,
        DATE_TRUNC('month', o.order_date) as activity_month,
        EXTRACT(EPOCH FROM (DATE_TRUNC('month', o.order_date) - c.cohort_month)) / 
        EXTRACT(EPOCH FROM INTERVAL '1 month') as period_number,
        COUNT(DISTINCT o.order_id) as orders_count,
        SUM(o.order_total) as revenue,
        AVG(o.order_total) as avg_order_value
    FROM customer_cohorts c
    JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.cohort_month, DATE_TRUNC('month', o.order_date)
),

retention_rates AS (
    SELECT 
        cohort_month,
        period_number,
        COUNT(DISTINCT customer_id) as customers_active,
        FIRST_VALUE(COUNT(DISTINCT customer_id)) OVER (
            PARTITION BY cohort_month 
            ORDER BY period_number
        ) as cohort_size,
        COUNT(DISTINCT customer_id) * 1.0 / 
        FIRST_VALUE(COUNT(DISTINCT customer_id)) OVER (
            PARTITION BY cohort_month 
            ORDER BY period_number
        ) as retention_rate
    FROM monthly_customer_activity
    GROUP BY cohort_month, period_number
),

customer_metrics AS (
    SELECT 
        c.customer_id,
        c.cohort_month,
        COUNT(DISTINCT mca.activity_month) as active_months,
        SUM(mca.revenue) as total_revenue,
        AVG(mca.revenue) as avg_monthly_revenue,
        MAX(mca.activity_month) as last_active_month,
        
        -- Calculate customer age in months
        EXTRACT(EPOCH FROM (COALESCE(MAX(mca.activity_month), CURRENT_DATE) - c.cohort_month)) / 
        EXTRACT(EPOCH FROM INTERVAL '1 month') as customer_age_months,
        
        -- Historical CLV (actual)
        SUM(mca.revenue) as historical_clv,
        
        -- Frequency and monetary components
        COUNT(DISTINCT mca.activity_month) * 1.0 / 
        NULLIF(EXTRACT(EPOCH FROM (MAX(mca.activity_month) - c.cohort_month)) / 
               EXTRACT(EPOCH FROM INTERVAL '1 month'), 0) as purchase_frequency,
        
        SUM(mca.revenue) / NULLIF(COUNT(DISTINCT mca.activity_month), 0) as avg_revenue_per_active_month
        
    FROM customer_cohorts c
    LEFT JOIN monthly_customer_activity mca ON c.customer_id = mca.customer_id
    GROUP BY c.customer_id, c.cohort_month
),

predictive_clv AS (
    SELECT 
        cm.*,
        
        -- Get cohort-level retention curve
        COALESCE(AVG(rr.retention_rate) OVER (
            PARTITION BY cm.cohort_month
        ), 0.1) as avg_cohort_retention,
        
        -- Predictive CLV calculation
        -- Formula: (Average Monthly Revenue × Purchase Frequency × Gross Margin) / (1 + Discount Rate - Retention Rate)
        CASE 
            WHEN avg_cohort_retention > 0 AND avg_cohort_retention < 1 THEN
                (COALESCE(avg_revenue_per_active_month, 0) * 
                 COALESCE(purchase_frequency, 0) * 
                 0.3) /  -- Assuming 30% gross margin
                (1 + 0.01 - avg_cohort_retention)  -- 1% monthly discount rate
            ELSE historical_clv
        END as predicted_clv,
        
        -- Risk segmentation
        CASE 
            WHEN EXTRACT(EPOCH FROM (CURRENT_DATE - last_active_month)) / 
                 EXTRACT(EPOCH FROM INTERVAL '1 month') > 6 THEN 'High Risk'
            WHEN EXTRACT(EPOCH FROM (CURRENT_DATE - last_active_month)) / 
                 EXTRACT(EPOCH FROM INTERVAL '1 month') > 3 THEN 'Medium Risk'
            WHEN last_active_month >= CURRENT_DATE - INTERVAL '1 month' THEN 'Active'
            ELSE 'Inactive'
        END as customer_status,
        
        -- Value tier classification
        NTILE(5) OVER (ORDER BY historical_clv) as value_quintile
        
    FROM customer_metrics cm
    LEFT JOIN retention_rates rr ON cm.cohort_month = rr.cohort_month 
                                 AND ROUND(cm.customer_age_months) = rr.period_number
)

SELECT 
    customer_id,
    cohort_month,
    customer_status,
    value_quintile,
    active_months,
    customer_age_months,
    ROUND(total_revenue, 2) as historical_clv,
    ROUND(predicted_clv, 2) as predicted_clv,
    ROUND(avg_revenue_per_active_month, 2) as avg_monthly_revenue,
    ROUND(purchase_frequency, 3) as purchase_frequency,
    ROUND(avg_cohort_retention, 3) as cohort_retention_rate,
    
    -- Strategic recommendations
    CASE 
        WHEN customer_status = 'Active' AND value_quintile >= 4 THEN 'VIP Program'
        WHEN customer_status = 'Active' AND value_quintile = 3 THEN 'Loyalty Program'
        WHEN customer_status = 'Medium Risk' AND value_quintile >= 3 THEN 'Retention Campaign'
        WHEN customer_status = 'High Risk' AND value_quintile >= 3 THEN 'Win-Back Campaign'
        WHEN customer_status = 'Inactive' THEN 'Re-engagement Required'
        ELSE 'Standard Marketing'
    END as recommended_action
    
FROM predictive_clv
WHERE predicted_clv > 0
ORDER BY predicted_clv DESC;