Net Promoter Score (NPS)

-- Calculate Net Promoter Score with detailed breakdowns
WITH nps_responses AS (
  SELECT 
    survey_id,
    customer_id,
    survey_date,
    nps_score,
    customer_segment,
    subscription_tier,
    account_age_months,
    CASE 
      WHEN nps_score >= 9 THEN 'Promoter'
      WHEN nps_score >= 7 THEN 'Passive'
      ELSE 'Detractor'
    END as nps_category,
    CASE 
      WHEN nps_score >= 9 THEN 1 ELSE 0
    END as is_promoter,
    CASE 
      WHEN nps_score >= 7 AND nps_score <= 8 THEN 1 ELSE 0
    END as is_passive,
    CASE 
      WHEN nps_score <= 6 THEN 1 ELSE 0
    END as is_detractor
  FROM customer_nps_surveys
  WHERE survey_date >= CURRENT_DATE - INTERVAL '12 months'
    AND nps_score IS NOT NULL
),
nps_calculation AS (
  SELECT 
    DATE_TRUNC('month', survey_date) as survey_month,
    customer_segment,
    subscription_tier,
    COUNT(*) as total_responses,
    SUM(is_promoter) as promoter_count,
    SUM(is_passive) as passive_count,
    SUM(is_detractor) as detractor_count,
    ROUND(100.0 * SUM(is_promoter) / COUNT(*), 1) as promoter_percentage,
    ROUND(100.0 * SUM(is_passive) / COUNT(*), 1) as passive_percentage,
    ROUND(100.0 * SUM(is_detractor) / COUNT(*), 1) as detractor_percentage,
    ROUND(AVG(nps_score), 1) as avg_score,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY nps_score), 1) as median_score
  FROM nps_responses
  GROUP BY 1, 2, 3
)
SELECT 
  survey_month,
  customer_segment,
  subscription_tier,
  total_responses,
  promoter_count,
  passive_count,
  detractor_count,
  promoter_percentage,
  passive_percentage,
  detractor_percentage,
  ROUND(promoter_percentage - detractor_percentage, 1) as net_promoter_score,
  avg_score,
  median_score,
  CASE 
    WHEN (promoter_percentage - detractor_percentage) >= 70 THEN 'Excellent'
    WHEN (promoter_percentage - detractor_percentage) >= 50 THEN 'Good'
    WHEN (promoter_percentage - detractor_percentage) >= 0 THEN 'Average'
    ELSE 'Needs Improvement'
  END as nps_rating,
  LAG(promoter_percentage - detractor_percentage) OVER (
    PARTITION BY customer_segment, subscription_tier 
    ORDER BY survey_month
  ) as prev_month_nps,
  ROUND((promoter_percentage - detractor_percentage) - 
    LAG(promoter_percentage - detractor_percentage) OVER (
      PARTITION BY customer_segment, subscription_tier 
      ORDER BY survey_month
    ), 1) as nps_change
FROM nps_calculation
WHERE total_responses >= 30  -- Minimum sample size for statistical relevance
ORDER BY survey_month DESC, net_promoter_score DESC;

-- Analyze NPS trends and patterns over time
WITH nps_trends AS (
  SELECT 
    nr.customer_id,
    nr.survey_date,
    nr.nps_score,
    nr.nps_category,
    c.signup_date,
    c.subscription_tier,
    c.industry,
    EXTRACT(days FROM AGE(nr.survey_date, c.signup_date)) as days_since_signup,
    LAG(nr.nps_score) OVER (PARTITION BY nr.customer_id ORDER BY nr.survey_date) as prev_nps_score,
    LAG(nr.survey_date) OVER (PARTITION BY nr.customer_id ORDER BY nr.survey_date) as prev_survey_date,
    ROW_NUMBER() OVER (PARTITION BY nr.customer_id ORDER BY nr.survey_date) as survey_sequence
  FROM nps_responses nr
  JOIN customers c ON nr.customer_id = c.customer_id
),
nps_journey_analysis AS (
  SELECT 
    customer_id,
    COUNT(*) as total_surveys,
    MIN(nps_score) as first_nps_score,
    MAX(nps_score) as latest_nps_score,
    ROUND(AVG(nps_score), 1) as avg_nps_score,
    latest_nps_score - first_nps_score as nps_change_total,
    CASE 
      WHEN latest_nps_score > first_nps_score THEN 'Improving'
      WHEN latest_nps_score < first_nps_score THEN 'Declining'
      ELSE 'Stable'
    END as nps_trend,
    STRING_AGG(nps_category, ' -> ' ORDER BY survey_date) as nps_journey,
    COUNT(CASE WHEN nps_category = 'Promoter' THEN 1 END) as promoter_surveys,
    COUNT(CASE WHEN nps_category = 'Detractor' THEN 1 END) as detractor_surveys
  FROM nps_trends
  WHERE total_surveys >= 2  -- Customers with multiple surveys
  GROUP BY customer_id
),
cohort_nps AS (
  SELECT 
    DATE_TRUNC('month', c.signup_date) as signup_cohort,
    nr.survey_date,
    EXTRACT(months FROM AGE(nr.survey_date, c.signup_date)) as months_since_signup,
    COUNT(*) as responses,
    ROUND(AVG(nr.nps_score), 1) as avg_nps,
    COUNT(CASE WHEN nr.nps_category = 'Promoter' THEN 1 END) as promoters,
    COUNT(CASE WHEN nr.nps_category = 'Detractor' THEN 1 END) as detractors,
    ROUND(100.0 * COUNT(CASE WHEN nr.nps_category = 'Promoter' THEN 1 END) / COUNT(*) - 
          100.0 * COUNT(CASE WHEN nr.nps_category = 'Detractor' THEN 1 END) / COUNT(*), 1) as cohort_nps
  FROM nps_responses nr
  JOIN customers c ON nr.customer_id = c.customer_id
  GROUP BY 1, 2, 3
  HAVING COUNT(*) >= 10  -- Minimum responses per cohort
)
SELECT 
  signup_cohort,
  months_since_signup,
  responses,
  avg_nps,
  cohort_nps,
  promoters,
  detractors,
  RANK() OVER (PARTITION BY signup_cohort ORDER BY months_since_signup) as cohort_maturity_rank
FROM cohort_nps
WHERE months_since_signup <= 24  -- First 2 years
ORDER BY signup_cohort DESC, months_since_signup ASC;

-- Analyze factors that drive NPS scores
WITH customer_attributes AS (
  SELECT 
    nr.customer_id,
    nr.nps_score,
    nr.nps_category,
    c.subscription_tier,
    c.industry,
    c.company_size,
    c.acquisition_source,
    -- Product usage metrics
    pu.monthly_active_days,
    pu.features_used_count,
    pu.support_tickets_count,
    -- Customer success metrics
    cs.onboarding_completion_rate,
    cs.time_to_value_days,
    cs.health_score,
    -- Financial metrics
    fm.monthly_revenue,
    fm.payment_issues_count
  FROM nps_responses nr
  JOIN customers c ON nr.customer_id = c.customer_id
  LEFT JOIN product_usage pu ON nr.customer_id = pu.customer_id 
    AND DATE_TRUNC('month', pu.usage_date) = DATE_TRUNC('month', nr.survey_date)
  LEFT JOIN customer_success cs ON nr.customer_id = cs.customer_id
  LEFT JOIN financial_metrics fm ON nr.customer_id = fm.customer_id 
    AND DATE_TRUNC('month', fm.transaction_date) = DATE_TRUNC('month', nr.survey_date)
),
nps_correlations AS (
  SELECT 
    'Product Usage - Active Days' as factor,
    CORR(nps_score, monthly_active_days) as correlation_coefficient,
    ROUND(AVG(CASE WHEN nps_category = 'Promoter' THEN monthly_active_days END), 1) as promoter_avg,
    ROUND(AVG(CASE WHEN nps_category = 'Detractor' THEN monthly_active_days END), 1) as detractor_avg
  FROM customer_attributes
  WHERE monthly_active_days IS NOT NULL
  
  UNION ALL
  
  SELECT 
    'Features Used Count' as factor,
    CORR(nps_score, features_used_count) as correlation_coefficient,
    ROUND(AVG(CASE WHEN nps_category = 'Promoter' THEN features_used_count END), 1) as promoter_avg,
    ROUND(AVG(CASE WHEN nps_category = 'Detractor' THEN features_used_count END), 1) as detractor_avg
  FROM customer_attributes
  WHERE features_used_count IS NOT NULL
  
  UNION ALL
  
  SELECT 
    'Support Tickets Count' as factor,
    CORR(nps_score, -support_tickets_count) as correlation_coefficient,  -- Negative because more tickets = lower NPS
    ROUND(AVG(CASE WHEN nps_category = 'Promoter' THEN support_tickets_count END), 1) as promoter_avg,
    ROUND(AVG(CASE WHEN nps_category = 'Detractor' THEN support_tickets_count END), 1) as detractor_avg
  FROM customer_attributes
  WHERE support_tickets_count IS NOT NULL
  
  UNION ALL
  
  SELECT 
    'Customer Health Score' as factor,
    CORR(nps_score, health_score) as correlation_coefficient,
    ROUND(AVG(CASE WHEN nps_category = 'Promoter' THEN health_score END), 1) as promoter_avg,
    ROUND(AVG(CASE WHEN nps_category = 'Detractor' THEN health_score END), 1) as detractor_avg
  FROM customer_attributes
  WHERE health_score IS NOT NULL
),
segment_nps AS (
  SELECT 
    subscription_tier,
    industry,
    company_size,
    acquisition_source,
    COUNT(*) as total_responses,
    ROUND(AVG(nps_score), 1) as avg_nps_score,
    COUNT(CASE WHEN nps_category = 'Promoter' THEN 1 END) as promoters,
    COUNT(CASE WHEN nps_category = 'Detractor' THEN 1 END) as detractors,
    ROUND(100.0 * COUNT(CASE WHEN nps_category = 'Promoter' THEN 1 END) / COUNT(*) - 
          100.0 * COUNT(CASE WHEN nps_category = 'Detractor' THEN 1 END) / COUNT(*), 1) as segment_nps
  FROM customer_attributes
  GROUP BY 1, 2, 3, 4
  HAVING COUNT(*) >= 20  -- Minimum sample size
)
SELECT 
  factor,
  ROUND(correlation_coefficient, 3) as correlation,
  promoter_avg,
  detractor_avg,
  ROUND(promoter_avg - detractor_avg, 1) as difference,
  RANK() OVER (ORDER BY ABS(correlation_coefficient) DESC) as correlation_rank
FROM nps_correlations
WHERE correlation_coefficient IS NOT NULL
ORDER BY ABS(correlation_coefficient) DESC;