Customer Segmentation & Marketing Analytics

-- RFM Scoring using T-SQL Window Functions
WITH rfm_base AS (
    SELECT
        c.CustomerID,
        c.CustomerName,
        c.Region,
        -- Recency: days since last payment
        DATEDIFF(day,
            MAX(p.PaymentDate),
            CAST(GETDATE() AS DATE)
        )                                   AS recency_days,
        -- Frequency: active policy count
        COUNT(DISTINCT pol.PolicyID)        AS frequency,
        -- Monetary: total lifetime premium
        SUM(pol.AnnualPremium)              AS monetary
    FROM       dbo.Customers  c
    INNER JOIN dbo.Policies   pol ON c.CustomerID = pol.CustomerID
    LEFT JOIN  dbo.Payments   p   ON pol.PolicyID = p.PolicyID
    WHERE  pol.PolicyStatus IN ('ACTIVE', 'LAPSED')
    GROUP BY c.CustomerID, c.CustomerName, c.Region
),
rfm_scored AS (
    SELECT *,
        -- Lower recency = more recent = higher score
        6 - NTILE(5) OVER (ORDER BY recency_days DESC) AS R_score,
        NTILE(5) OVER (ORDER BY frequency  ASC)                AS F_score,
        NTILE(5) OVER (ORDER BY monetary   ASC)                AS M_score
    FROM rfm_base
)
SELECT
    CustomerID, CustomerName, Region,
    recency_days, frequency, monetary,
    R_score, F_score, M_score,
    (R_score + F_score + M_score)       AS rfm_total,
    -- Composite segment label
    CASE
        WHEN R_score >= 4 AND F_score >= 4 AND M_score >= 4
        THEN 'Champions'
        WHEN R_score >= 3 AND F_score >= 3
        THEN 'Loyalists'
        WHEN R_score <= 2 AND F_score >= 3
        THEN 'At-Risk'
        WHEN M_score <= 2
        THEN 'Price-Sensitive'
        WHEN recency_days <= 365 AND F_score = 1
        THEN 'New Customer'
        ELSE 'Dormant'
    END                                 AS rfm_segment
FROM rfm_scored
ORDER BY rfm_total DESC;