RFM analysis in BigQuery with dbt — the practical guide

-- models/intermediate/int_rfm__customer_metrics.sql
SELECT
    customer_id,
    MAX(order_date)                     AS last_order_date,
    COUNT(DISTINCT order_id)            AS total_orders,
    SUM(order_total)                    AS total_revenue,
    DATE_DIFF(CURRENT_DATE(), MAX(order_date), DAY) AS days_since_last_order,
    DATE_DIFF(CURRENT_DATE(), MIN(order_date), DAY) AS customer_tenure_days,
    SAFE_DIVIDE(SUM(order_total), COUNT(DISTINCT order_id)) AS avg_order_value
FROM {{ ref('mart_orders') }}
WHERE order_status = 'completed'
GROUP BY 1

-- models/intermediate/int_rfm__scored.sql
SELECT
    customer_id,
    days_since_last_order,
    total_orders,
    total_revenue,
    avg_order_value,
    -- Recency: lower days = better = higher score
    5 - NTILE(5) OVER (ORDER BY days_since_last_order ASC) + 1 AS recency_score,
    -- Frequency: more orders = higher score
    NTILE(5) OVER (ORDER BY total_orders ASC) AS frequency_score,
    -- Monetary: more revenue = higher score
    NTILE(5) OVER (ORDER BY total_revenue ASC) AS monetary_score
FROM {{ ref('int_rfm__customer_metrics') }}
WHERE total_orders > 0

-- models/marts/mart_rfm_segments.sql
SELECT
    customer_id,
    days_since_last_order,
    total_orders,
    total_revenue,
    avg_order_value,
    recency_score,
    frequency_score,
    monetary_score,
    CONCAT(
        CAST(recency_score AS STRING),
        CAST(frequency_score AS STRING),
        CAST(monetary_score AS STRING)
    ) AS rfm_score,
    CASE
        -- Champions: recent, frequent, high-value
        WHEN recency_score >= 4 AND frequency_score >= 4 AND monetary_score >= 4
        THEN 'Champions'
 
        -- Loyal: frequent and high-value, recency varies
        WHEN frequency_score >= 4 AND monetary_score >= 4
        THEN 'Loyal Customers'
 
        -- Potential Loyalists: recent, moderate frequency
        WHEN recency_score >= 4 AND frequency_score >= 2 AND frequency_score <= 3
        THEN 'Potential Loyalists'
 
        -- New Customers: very recent, low frequency
        WHEN recency_score >= 4 AND frequency_score = 1
        THEN 'New Customers'
 
        -- At Risk: used to be good, not recent
        WHEN recency_score <= 2 AND frequency_score >= 3 AND monetary_score >= 3
        THEN 'At Risk'
 
        -- Can't Lose Them: were champions, going dormant
        WHEN recency_score <= 2 AND frequency_score >= 4 AND monetary_score >= 4
        THEN 'Cant Lose Them'
 
        -- Hibernating: low everything
        WHEN recency_score <= 2 AND frequency_score <= 2
        THEN 'Hibernating'
 
        -- Need Attention: mid-range across the board
        WHEN recency_score = 3 AND frequency_score = 3
        THEN 'Need Attention'
 
        -- About to Sleep: below average recency, low-mid frequency
        WHEN recency_score <= 3 AND frequency_score <= 2
        THEN 'About to Sleep'
 
        ELSE 'Other'
    END AS segment_name
FROM {{ ref('int_rfm__scored') }}

-- How many customers in each segment?
SELECT
    segment_name,
    COUNT(*) AS customers,
    SUM(total_revenue) AS segment_revenue,
    AVG(total_revenue) AS avg_revenue_per_customer
FROM {{ ref('mart_rfm_segments') }}
GROUP BY 1
ORDER BY segment_revenue DESC

-- Heatmap data: recency × frequency, colored by customer count
SELECT
    recency_score,
    frequency_score,
    COUNT(*) AS customer_count,
    SUM(total_revenue) AS total_revenue,
    AVG(monetary_score) AS avg_monetary_score
FROM {{ ref('int_rfm__scored') }}
GROUP BY 1, 2
ORDER BY 1 DESC, 2 ASC

-- models/snapshots/rfm_snapshot.sql
{% snapshot rfm_segment_snapshot %}
{{
    config(
      target_schema='snapshots',
      unique_key='customer_id',
      strategy='check',
      check_cols=['segment_name'],
    )
}}
SELECT customer_id, segment_name, CURRENT_DATE() AS snapshot_date
FROM {{ ref('mart_rfm_segments') }}
{% endsnapshot %}

-- models/activation/rfm_email_audiences.sql
SELECT
    c.email,
    r.segment_name,
    r.days_since_last_order,
    r.total_revenue,
    CASE
        WHEN r.segment_name = 'Cant Lose Them'
        THEN 'winback_urgent'
        WHEN r.segment_name = 'At Risk'
        THEN 'winback_standard'
        WHEN r.segment_name = 'Potential Loyalists'
        THEN 'nurture_to_loyal'
        WHEN r.segment_name = 'New Customers'
        THEN 'onboarding_series'
        WHEN r.segment_name = 'Champions'
        THEN 'referral_ask'
    END AS email_campaign
FROM {{ ref('mart_rfm_segments') }} r
JOIN {{ ref('dim_customers') }} c ON r.customer_id = c.customer_id
WHERE email_campaign IS NOT NULL