Customer Health Score

-- Calculate comprehensive customer health scores
WITH customer_usage_metrics AS (
  SELECT 
    customer_id,
    DATE_TRUNC('month', event_date) as month,
    COUNT(DISTINCT DATE(event_date)) as active_days,
    COUNT(*) as total_events,
    COUNT(DISTINCT feature_used) as features_used,
    AVG(session_duration_minutes) as avg_session_duration,
    COUNT(DISTINCT user_id) as active_users_count
  FROM customer_product_usage
  WHERE event_date >= CURRENT_DATE - INTERVAL '90 days'
  GROUP BY 1, 2
),
customer_engagement_metrics AS (
  SELECT 
    customer_id,
    DATE_TRUNC('month', event_date) as month,
    COUNT(CASE WHEN event_type = 'feature_discovery' THEN 1 END) as new_features_tried,
    COUNT(CASE WHEN event_type = 'advanced_action' THEN 1 END) as advanced_actions,
    COUNT(CASE WHEN event_type = 'collaboration' THEN 1 END) as collaboration_events,
    COUNT(CASE WHEN event_type = 'content_creation' THEN 1 END) as content_created
  FROM customer_engagement_events
  WHERE event_date >= CURRENT_DATE - INTERVAL '90 days'
  GROUP BY 1, 2
),
customer_support_metrics AS (
  SELECT 
    customer_id,
    DATE_TRUNC('month', created_date) as month,
    COUNT(*) as support_tickets,
    COUNT(CASE WHEN priority = 'high' OR priority = 'critical' THEN 1 END) as high_priority_tickets,
    AVG(resolution_time_hours) as avg_resolution_time,
    AVG(customer_satisfaction_score) as avg_csat,
    COUNT(CASE WHEN ticket_type = 'bug' THEN 1 END) as bug_reports
  FROM customer_support_tickets
  WHERE created_date >= CURRENT_DATE - INTERVAL '90 days'
  GROUP BY 1, 2
),
customer_business_metrics AS (
  SELECT 
    customer_id,
    DATE_TRUNC('month', transaction_date) as month,
    SUM(revenue) as monthly_revenue,
    COUNT(DISTINCT invoice_id) as invoices_count,
    AVG(EXTRACT(days FROM AGE(payment_date, invoice_date))) as avg_payment_delay,
    COUNT(CASE WHEN payment_status = 'failed' THEN 1 END) as failed_payments
  FROM customer_billing
  WHERE transaction_date >= CURRENT_DATE - INTERVAL '90 days'
  GROUP BY 1, 2
),
health_score_calculation AS (
  SELECT 
    COALESCE(cum.customer_id, cem.customer_id, csm.customer_id, cbm.customer_id) as customer_id,
    COALESCE(cum.month, cem.month, csm.month, cbm.month) as month,
    
    -- Usage Score (0-25 points)
    LEAST(25, 
      COALESCE(cum.active_days, 0) * 0.8 + 
      COALESCE(cum.features_used, 0) * 2 + 
      COALESCE(cum.active_users_count, 0) * 1.5
    ) as usage_score,
    
    -- Engagement Score (0-25 points)
    LEAST(25,
      COALESCE(cem.new_features_tried, 0) * 3 +
      COALESCE(cem.advanced_actions, 0) * 0.5 +
      COALESCE(cem.collaboration_events, 0) * 0.3 +
      COALESCE(cem.content_created, 0) * 0.2
    ) as engagement_score,
    
    -- Support Score (0-25 points, inverted - fewer tickets = higher score)
    GREATEST(0, 25 - 
      COALESCE(csm.support_tickets, 0) * 2 -
      COALESCE(csm.high_priority_tickets, 0) * 5 -
      COALESCE(csm.bug_reports, 0) * 3 +
      COALESCE(csm.avg_csat, 3) * 3
    ) as support_score,
    
    -- Business Value Score (0-25 points)
    LEAST(25,
      COALESCE(cbm.monthly_revenue, 0) / 1000 * 2 +
      GREATEST(0, 10 - COALESCE(cbm.avg_payment_delay, 0)) * 1.5 -
      COALESCE(cbm.failed_payments, 0) * 5
    ) as business_value_score,
    
    -- Component metrics for analysis
    cum.active_days,
    cum.features_used,
    cem.new_features_tried,
    csm.support_tickets,
    csm.avg_csat,
    cbm.monthly_revenue
    
  FROM customer_usage_metrics cum
  FULL OUTER JOIN customer_engagement_metrics cem ON cum.customer_id = cem.customer_id AND cum.month = cem.month
  FULL OUTER JOIN customer_support_metrics csm ON cum.customer_id = csm.customer_id AND cum.month = csm.month
  FULL OUTER JOIN customer_business_metrics cbm ON cum.customer_id = cbm.customer_id AND cum.month = cbm.month
)
SELECT 
  customer_id,
  month,
  usage_score,
  engagement_score,
  support_score,
  business_value_score,
  ROUND(usage_score + engagement_score + support_score + business_value_score, 1) as total_health_score,
  CASE 
    WHEN (usage_score + engagement_score + support_score + business_value_score) >= 90 THEN 'Excellent'
    WHEN (usage_score + engagement_score + support_score + business_value_score) >= 70 THEN 'Healthy'
    WHEN (usage_score + engagement_score + support_score + business_value_score) >= 40 THEN 'At Risk'
    ELSE 'Critical'
  END as health_category,
  active_days,
  features_used,
  support_tickets,
  monthly_revenue
FROM health_score_calculation
ORDER BY month DESC, total_health_score DESC;

-- Advanced health scoring with trend analysis and predictive elements
WITH historical_health_scores AS (
  SELECT 
    customer_id,
    month,
    total_health_score,
    LAG(total_health_score, 1) OVER (PARTITION BY customer_id ORDER BY month) as prev_month_score,
    LAG(total_health_score, 3) OVER (PARTITION BY customer_id ORDER BY month) as three_months_ago_score,
    CASE WHEN next_month_revenue IS NOT NULL THEN 1 ELSE 0 END as retained_next_month
  FROM customer_health_scores chs
  LEFT JOIN customer_billing cb ON chs.customer_id = cb.customer_id 
    AND cb.transaction_date >= chs.month + INTERVAL '1 month'
    AND cb.transaction_date < chs.month + INTERVAL '2 months'
),
trend_analysis AS (
  SELECT 
    customer_id,
    month,
    total_health_score,
    prev_month_score,
    three_months_ago_score,
    retained_next_month,
    COALESCE(total_health_score - prev_month_score, 0) as month_over_month_change,
    COALESCE(total_health_score - three_months_ago_score, 0) as three_month_trend,
    CASE 
      WHEN prev_month_score IS NOT NULL THEN 
        CASE 
          WHEN total_health_score > prev_month_score THEN 'Improving'
          WHEN total_health_score < prev_month_score THEN 'Declining'
          ELSE 'Stable'
        END
      ELSE 'New Customer'
    END as trend_direction
  FROM historical_health_scores
),
risk_indicators AS (
  SELECT 
    ta.*,
    CASE 
      WHEN total_health_score < 40 THEN 'High Risk'
      WHEN total_health_score < 70 AND trend_direction = 'Declining' THEN 'Medium Risk'
      WHEN month_over_month_change < -10 THEN 'Trend Risk'
      WHEN total_health_score >= 90 AND trend_direction = 'Improving' THEN 'Expansion Opportunity'
      ELSE 'Low Risk'
    END as risk_category,
    CASE 
      WHEN total_health_score < 30 THEN 'Immediate'
      WHEN total_health_score < 50 OR month_over_month_change < -15 THEN 'This Week'
      WHEN total_health_score < 70 OR month_over_month_change < -10 THEN 'This Month'
      ELSE 'Monitor'
    END as intervention_urgency
  FROM trend_analysis ta
)
SELECT 
  customer_id,
  month,
  total_health_score,
  prev_month_score,
  month_over_month_change,
  three_month_trend,
  trend_direction,
  risk_category,
  intervention_urgency,
  retained_next_month,
  -- Predictive probability based on historical patterns
  CASE 
    WHEN total_health_score >= 90 THEN 0.95
    WHEN total_health_score >= 80 THEN 0.85 + (month_over_month_change * 0.01)
    WHEN total_health_score >= 70 THEN 0.75 + (month_over_month_change * 0.015)
    WHEN total_health_score >= 60 THEN 0.65 + (month_over_month_change * 0.02)
    WHEN total_health_score >= 50 THEN 0.50 + (month_over_month_change * 0.025)
    WHEN total_health_score >= 40 THEN 0.35 + (month_over_month_change * 0.03)
    ELSE 0.15 + GREATEST(-0.20, month_over_month_change * 0.035)
  END as predicted_retention_probability
FROM risk_indicators
ORDER BY 
  CASE intervention_urgency 
    WHEN 'Immediate' THEN 1 
    WHEN 'This Week' THEN 2 
    WHEN 'This Month' THEN 3 
    ELSE 4 
  END,
  total_health_score ASC;

-- Analyze health scores by customer segments and characteristics
WITH customer_segments AS (
  SELECT 
    customer_id,
    subscription_tier,
    customer_size,
    industry,
    acquisition_channel,
    account_age_months,
    CASE 
      WHEN account_age_months <= 3 THEN 'New'
      WHEN account_age_months <= 12 THEN 'Growing'
      WHEN account_age_months <= 24 THEN 'Mature'
      ELSE 'Established'
    END as customer_lifecycle_stage
  FROM customers
),
segmented_health_analysis AS (
  SELECT 
    cs.subscription_tier,
    cs.customer_size,
    cs.industry,
    cs.customer_lifecycle_stage,
    COUNT(*) as customer_count,
    ROUND(AVG(chs.total_health_score), 1) as avg_health_score,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY chs.total_health_score), 1) as median_health_score,
    COUNT(CASE WHEN chs.total_health_score >= 90 THEN 1 END) as excellent_health_count,
    COUNT(CASE WHEN chs.total_health_score >= 70 THEN 1 END) as healthy_count,
    COUNT(CASE WHEN chs.total_health_score < 40 THEN 1 END) as critical_health_count,
    ROUND(100.0 * COUNT(CASE WHEN chs.total_health_score >= 70 THEN 1 END) / COUNT(*), 1) as healthy_percentage,
    ROUND(100.0 * COUNT(CASE WHEN chs.total_health_score < 40 THEN 1 END) / COUNT(*), 1) as at_risk_percentage
  FROM customer_segments cs
  JOIN customer_health_scores chs ON cs.customer_id = chs.customer_id
  WHERE chs.month = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')  -- Last complete month
  GROUP BY 1, 2, 3, 4
),
health_correlations AS (
  SELECT 
    'Overall' as segment,
    CORR(chs.total_health_score, cbm.monthly_revenue) as health_revenue_correlation,
    CORR(chs.total_health_score, cum.features_used) as health_feature_correlation,
    CORR(chs.total_health_score, csm.avg_csat) as health_satisfaction_correlation
  FROM customer_health_scores chs
  JOIN customer_billing_metrics cbm ON chs.customer_id = cbm.customer_id AND chs.month = cbm.month
  JOIN customer_usage_metrics cum ON chs.customer_id = cum.customer_id AND chs.month = cum.month
  JOIN customer_support_metrics csm ON chs.customer_id = csm.customer_id AND chs.month = csm.month
  WHERE chs.month >= CURRENT_DATE - INTERVAL '3 months'
)
SELECT 
  subscription_tier,
  customer_size,
  customer_lifecycle_stage,
  customer_count,
  avg_health_score,
  median_health_score,
  healthy_percentage,
  at_risk_percentage,
  excellent_health_count,
  critical_health_count,
  RANK() OVER (ORDER BY avg_health_score DESC) as health_rank
FROM segmented_health_analysis
WHERE customer_count >= 10  -- Minimum segment size for statistical relevance
ORDER BY avg_health_score DESC;