Net Revenue Retention (NRR)

-- Calculate Net Revenue Retention by cohort
WITH cohort_revenue AS (
  SELECT 
    customer_id,
    DATE_TRUNC('month', first_payment_date) as cohort_month,
    first_payment_date,
    DATE_TRUNC('month', revenue_date) as revenue_month,
    revenue_amount,
    revenue_type  -- 'new', 'expansion', 'contraction', 'churn'
  FROM customer_revenue_history
  WHERE first_payment_date >= '2023-01-01'
),
starting_revenue AS (
  SELECT 
    cohort_month,
    customer_id,
    SUM(revenue_amount) as starting_mrr
  FROM cohort_revenue
  WHERE revenue_month = cohort_month
  GROUP BY 1, 2
),
revenue_changes AS (
  SELECT 
    cr.cohort_month,
    cr.customer_id,
    sr.starting_mrr,
    SUM(CASE WHEN cr.revenue_type = 'expansion' THEN cr.revenue_amount ELSE 0 END) as expansion_revenue,
    SUM(CASE WHEN cr.revenue_type = 'contraction' THEN cr.revenue_amount ELSE 0 END) as contraction_revenue,
    SUM(CASE WHEN cr.revenue_type = 'churn' THEN cr.revenue_amount ELSE 0 END) as churned_revenue
  FROM cohort_revenue cr
  JOIN starting_revenue sr ON cr.customer_id = sr.customer_id
  WHERE cr.revenue_month BETWEEN cr.cohort_month AND cr.cohort_month + INTERVAL '12 months'
  GROUP BY 1, 2, 3
),
nrr_calculation AS (
  SELECT 
    cohort_month,
    COUNT(DISTINCT customer_id) as customers,
    SUM(starting_mrr) as total_starting_mrr,
    SUM(expansion_revenue) as total_expansion,
    SUM(contraction_revenue) as total_contraction,
    SUM(churned_revenue) as total_churn,
    SUM(starting_mrr + expansion_revenue - contraction_revenue - churned_revenue) as ending_revenue
  FROM revenue_changes
  GROUP BY 1
)
SELECT 
  cohort_month,
  customers,
  total_starting_mrr,
  total_expansion,
  total_contraction,
  total_churn,
  ending_revenue,
  ROUND(100.0 * ending_revenue / total_starting_mrr, 1) as nrr_percent,
  ROUND(100.0 * total_expansion / total_starting_mrr, 1) as expansion_rate,
  ROUND(100.0 * total_contraction / total_starting_mrr, 1) as contraction_rate,
  ROUND(100.0 * total_churn / total_starting_mrr, 1) as churn_rate
FROM nrr_calculation
WHERE cohort_month <= CURRENT_DATE - INTERVAL '12 months'  -- Only complete cohorts
ORDER BY cohort_month DESC;

-- Track monthly NRR trends
WITH monthly_base AS (
  SELECT 
    DATE_TRUNC('month', month_date) as month,
    customer_id,
    mrr_amount
  FROM customer_mrr_history
  WHERE month_date >= '2023-01-01'
),
revenue_movements AS (
  SELECT 
    month,
    SUM(CASE WHEN customer_age_months >= 12 THEN mrr_amount ELSE 0 END) as base_revenue,
    SUM(CASE 
      WHEN customer_age_months >= 12 AND revenue_change_type = 'expansion' 
      THEN revenue_change_amount ELSE 0 
    END) as expansion_revenue,
    SUM(CASE 
      WHEN customer_age_months >= 12 AND revenue_change_type = 'contraction' 
      THEN ABS(revenue_change_amount) ELSE 0 
    END) as contraction_revenue,
    SUM(CASE 
      WHEN customer_age_months >= 12 AND revenue_change_type = 'churn' 
      THEN ABS(revenue_change_amount) ELSE 0 
    END) as churned_revenue
  FROM customer_revenue_movements
  WHERE month >= '2023-01-01'
  GROUP BY 1
),
monthly_nrr AS (
  SELECT 
    month,
    base_revenue,
    expansion_revenue,
    contraction_revenue,
    churned_revenue,
    base_revenue + expansion_revenue - contraction_revenue - churned_revenue as net_revenue,
    ROUND(100.0 * (base_revenue + expansion_revenue - contraction_revenue - churned_revenue) / NULLIF(base_revenue, 0), 1) as monthly_nrr
  FROM revenue_movements
)
SELECT 
  month,
  base_revenue,
  expansion_revenue,
  contraction_revenue,
  churned_revenue,
  monthly_nrr,
  AVG(monthly_nrr) OVER (
    ORDER BY month 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) as three_month_avg_nrr,
  monthly_nrr - LAG(monthly_nrr) OVER (ORDER BY month) as month_over_month_change
FROM monthly_nrr
ORDER BY month DESC;

-- Analyze NRR by customer segments
WITH customer_segments AS (
  SELECT 
    customer_id,
    CASE 
      WHEN annual_contract_value < 10000 THEN 'SMB'
      WHEN annual_contract_value < 100000 THEN 'Mid-Market'
      ELSE 'Enterprise'
    END as segment,
    industry,
    acquisition_channel
  FROM customers
),
segmented_nrr AS (
  SELECT 
    cs.segment,
    cs.industry,
    DATE_TRUNC('quarter', cr.cohort_month) as cohort_quarter,
    COUNT(DISTINCT cr.customer_id) as customers,
    SUM(cr.starting_mrr) as starting_revenue,
    SUM(cr.expansion_revenue) as expansion,
    SUM(cr.contraction_revenue) as contraction,
    SUM(cr.churned_revenue) as churn,
    ROUND(100.0 * (SUM(cr.starting_mrr) + SUM(cr.expansion_revenue) - SUM(cr.contraction_revenue) - SUM(cr.churned_revenue)) / SUM(cr.starting_mrr), 1) as nrr
  FROM revenue_changes cr
  JOIN customer_segments cs ON cr.customer_id = cs.customer_id
  WHERE cr.cohort_month >= '2023-01-01'
  GROUP BY 1, 2, 3
  HAVING COUNT(DISTINCT cr.customer_id) >= 10  -- Minimum sample size
)
SELECT 
  segment,
  industry,
  cohort_quarter,
  customers,
  starting_revenue,
  nrr,
  RANK() OVER (PARTITION BY cohort_quarter ORDER BY nrr DESC) as nrr_rank
FROM segmented_nrr
ORDER BY cohort_quarter DESC, nrr DESC;