Customer Satisfaction Score (CSAT)

-- Calculate Customer Satisfaction Score across different touchpoints
WITH csat_responses AS (
  SELECT 
    survey_id,
    customer_id,
    survey_date,
    interaction_type,
    csat_score,
    survey_channel,
    customer_segment,
    subscription_tier,
    support_agent_id,
    CASE 
      WHEN csat_score >= 4 THEN 1 ELSE 0  -- 5-point scale: 4-5 = satisfied
    END as is_satisfied,
    CASE 
      WHEN csat_score >= 4 THEN 'Satisfied'
      WHEN csat_score = 3 THEN 'Neutral'
      ELSE 'Dissatisfied'
    END as satisfaction_category,
    DATE_TRUNC('month', survey_date) as survey_month,
    DATE_TRUNC('week', survey_date) as survey_week
  FROM customer_csat_surveys
  WHERE survey_date >= CURRENT_DATE - INTERVAL '12 months'
    AND csat_score IS NOT NULL
    AND csat_score BETWEEN 1 AND 5
),
csat_by_interaction AS (
  SELECT 
    interaction_type,
    survey_month,
    COUNT(*) as total_responses,
    SUM(is_satisfied) as satisfied_responses,
    ROUND(100.0 * SUM(is_satisfied) / COUNT(*), 1) as csat_percentage,
    ROUND(AVG(csat_score), 2) as avg_csat_score,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY csat_score), 1) as median_csat_score,
    COUNT(CASE WHEN satisfaction_category = 'Satisfied' THEN 1 END) as satisfied_count,
    COUNT(CASE WHEN satisfaction_category = 'Neutral' THEN 1 END) as neutral_count,
    COUNT(CASE WHEN satisfaction_category = 'Dissatisfied' THEN 1 END) as dissatisfied_count
  FROM csat_responses
  GROUP BY 1, 2
),
csat_trends AS (
  SELECT 
    interaction_type,
    survey_month,
    total_responses,
    csat_percentage,
    avg_csat_score,
    LAG(csat_percentage) OVER (
      PARTITION BY interaction_type 
      ORDER BY survey_month
    ) as prev_month_csat,
    ROUND(csat_percentage - LAG(csat_percentage) OVER (
      PARTITION BY interaction_type 
      ORDER BY survey_month
    ), 1) as csat_change,
    satisfied_count,
    neutral_count,
    dissatisfied_count
  FROM csat_by_interaction
  WHERE total_responses >= 20  -- Minimum sample size
)
SELECT 
  interaction_type,
  survey_month,
  total_responses,
  csat_percentage,
  avg_csat_score,
  median_csat_score,
  prev_month_csat,
  csat_change,
  satisfied_count,
  neutral_count,
  dissatisfied_count,
  CASE 
    WHEN csat_percentage >= 85 THEN 'Excellent'
    WHEN csat_percentage >= 75 THEN 'Good'
    WHEN csat_percentage >= 65 THEN 'Average'
    ELSE 'Needs Improvement'
  END as csat_rating,
  RANK() OVER (PARTITION BY survey_month ORDER BY csat_percentage DESC) as monthly_rank
FROM csat_trends
ORDER BY survey_month DESC, csat_percentage DESC;

-- Analyze factors that drive customer satisfaction scores
WITH interaction_details AS (
  SELECT 
    cr.survey_id,
    cr.customer_id,
    cr.csat_score,
    cr.satisfaction_category,
    cr.interaction_type,
    cr.support_agent_id,
    cr.survey_date,
    -- Support ticket details
    st.ticket_id,
    st.issue_category,
    st.priority,
    st.resolution_time_hours,
    st.first_response_time_minutes,
    st.escalation_count,
    -- Customer context
    c.subscription_tier,
    c.account_age_months,
    c.industry,
    c.company_size,
    -- Agent performance
    sa.experience_level,
    sa.department
  FROM csat_responses cr
  LEFT JOIN support_tickets st ON cr.survey_id = st.survey_id
  LEFT JOIN customers c ON cr.customer_id = c.customer_id
  LEFT JOIN support_agents sa ON cr.support_agent_id = sa.agent_id
  WHERE cr.interaction_type = 'support_ticket'
),
satisfaction_correlations AS (
  SELECT 
    'Resolution Time' as factor,
    CORR(csat_score, -resolution_time_hours) as correlation,  -- Negative because faster = better
    ROUND(AVG(CASE WHEN satisfaction_category = 'Satisfied' THEN resolution_time_hours END), 1) as satisfied_avg,
    ROUND(AVG(CASE WHEN satisfaction_category = 'Dissatisfied' THEN resolution_time_hours END), 1) as dissatisfied_avg
  FROM interaction_details
  WHERE resolution_time_hours IS NOT NULL
  
  UNION ALL
  
  SELECT 
    'First Response Time' as factor,
    CORR(csat_score, -first_response_time_minutes) as correlation,
    ROUND(AVG(CASE WHEN satisfaction_category = 'Satisfied' THEN first_response_time_minutes END), 1) as satisfied_avg,
    ROUND(AVG(CASE WHEN satisfaction_category = 'Dissatisfied' THEN first_response_time_minutes END), 1) as dissatisfied_avg
  FROM interaction_details
  WHERE first_response_time_minutes IS NOT NULL
  
  UNION ALL
  
  SELECT 
    'Account Age (Months)' as factor,
    CORR(csat_score, account_age_months) as correlation,
    ROUND(AVG(CASE WHEN satisfaction_category = 'Satisfied' THEN account_age_months END), 1) as satisfied_avg,
    ROUND(AVG(CASE WHEN satisfaction_category = 'Dissatisfied' THEN account_age_months END), 1) as dissatisfied_avg
  FROM interaction_details
  WHERE account_age_months IS NOT NULL
),
categorical_analysis AS (
  SELECT 
    issue_category,
    COUNT(*) as total_tickets,
    ROUND(100.0 * SUM(CASE WHEN satisfaction_category = 'Satisfied' THEN 1 ELSE 0 END) / COUNT(*), 1) as satisfaction_rate,
    ROUND(AVG(csat_score), 2) as avg_csat_score,
    ROUND(AVG(resolution_time_hours), 1) as avg_resolution_time
  FROM interaction_details
  WHERE issue_category IS NOT NULL
  GROUP BY issue_category
  HAVING COUNT(*) >= 10
  
  UNION ALL
  
  SELECT 
    CONCAT('Priority: ', priority) as issue_category,
    COUNT(*) as total_tickets,
    ROUND(100.0 * SUM(CASE WHEN satisfaction_category = 'Satisfied' THEN 1 ELSE 0 END) / COUNT(*), 1) as satisfaction_rate,
    ROUND(AVG(csat_score), 2) as avg_csat_score,
    ROUND(AVG(resolution_time_hours), 1) as avg_resolution_time
  FROM interaction_details
  WHERE priority IS NOT NULL
  GROUP BY priority
  HAVING COUNT(*) >= 10
  
  UNION ALL
  
  SELECT 
    CONCAT('Agent Level: ', experience_level) as issue_category,
    COUNT(*) as total_tickets,
    ROUND(100.0 * SUM(CASE WHEN satisfaction_category = 'Satisfied' THEN 1 ELSE 0 END) / COUNT(*), 1) as satisfaction_rate,
    ROUND(AVG(csat_score), 2) as avg_csat_score,
    ROUND(AVG(resolution_time_hours), 1) as avg_resolution_time
  FROM interaction_details
  WHERE experience_level IS NOT NULL
  GROUP BY experience_level
  HAVING COUNT(*) >= 10
)
SELECT 
  factor,
  ROUND(correlation, 3) as correlation_coefficient,
  satisfied_avg,
  dissatisfied_avg,
  ROUND(satisfied_avg - dissatisfied_avg, 1) as difference,
  CASE 
    WHEN ABS(correlation) >= 0.3 THEN 'Strong'
    WHEN ABS(correlation) >= 0.1 THEN 'Moderate'
    ELSE 'Weak'
  END as correlation_strength
FROM satisfaction_correlations
WHERE correlation IS NOT NULL
ORDER BY ABS(correlation) DESC;

-- Analyze CSAT performance by support agents and teams
WITH agent_performance AS (
  SELECT 
    sa.agent_id,
    sa.name as agent_name,
    sa.department,
    sa.experience_level,
    DATE_TRUNC('month', cr.survey_date) as performance_month,
    COUNT(*) as total_surveys,
    SUM(cr.is_satisfied) as satisfied_responses,
    ROUND(100.0 * SUM(cr.is_satisfied) / COUNT(*), 1) as agent_csat_percentage,
    ROUND(AVG(cr.csat_score), 2) as avg_csat_score,
    -- Operational metrics
    ROUND(AVG(st.resolution_time_hours), 1) as avg_resolution_time,
    ROUND(AVG(st.first_response_time_minutes), 1) as avg_first_response_time,
    COUNT(CASE WHEN st.escalation_count > 0 THEN 1 END) as escalated_tickets,
    ROUND(100.0 * COUNT(CASE WHEN st.escalation_count > 0 THEN 1 END) / COUNT(*), 1) as escalation_rate
  FROM csat_responses cr
  JOIN support_agents sa ON cr.support_agent_id = sa.agent_id
  LEFT JOIN support_tickets st ON cr.survey_id = st.survey_id
  WHERE cr.interaction_type = 'support_ticket'
    AND cr.survey_date >= CURRENT_DATE - INTERVAL '6 months'
  GROUP BY 1, 2, 3, 4, 5
  HAVING COUNT(*) >= 10  -- Minimum surveys for statistical relevance
),
department_benchmarks AS (
  SELECT 
    department,
    performance_month,
    COUNT(*) as agents_count,
    ROUND(AVG(agent_csat_percentage), 1) as dept_avg_csat,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY agent_csat_percentage), 1) as dept_median_csat,
    ROUND(STDDEV(agent_csat_percentage), 1) as dept_csat_stddev,
    MAX(agent_csat_percentage) as dept_max_csat,
    MIN(agent_csat_percentage) as dept_min_csat
  FROM agent_performance
  GROUP BY 1, 2
),
agent_rankings AS (
  SELECT 
    ap.*,
    db.dept_avg_csat,
    db.dept_median_csat,
    ROUND(ap.agent_csat_percentage - db.dept_avg_csat, 1) as csat_vs_dept_avg,
    CASE 
      WHEN ap.agent_csat_percentage >= db.dept_avg_csat + db.dept_csat_stddev THEN 'Top Performer'
      WHEN ap.agent_csat_percentage <= db.dept_avg_csat - db.dept_csat_stddev THEN 'Needs Improvement'
      ELSE 'Average Performer'
    END as performance_tier,
    RANK() OVER (PARTITION BY ap.department, ap.performance_month ORDER BY ap.agent_csat_percentage DESC) as dept_rank,
    RANK() OVER (PARTITION BY ap.performance_month ORDER BY ap.agent_csat_percentage DESC) as company_rank
  FROM agent_performance ap
  JOIN department_benchmarks db ON ap.department = db.department AND ap.performance_month = db.performance_month
),
improvement_opportunities AS (
  SELECT 
    agent_id,
    agent_name,
    department,
    performance_month,
    agent_csat_percentage,
    avg_resolution_time,
    avg_first_response_time,
    escalation_rate,
    CASE 
      WHEN avg_resolution_time > (SELECT PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY avg_resolution_time) 
                                  FROM agent_performance 
                                  WHERE performance_month = ar.performance_month) THEN 'Slow Resolution'
      WHEN avg_first_response_time > (SELECT PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY avg_first_response_time) 
                                      FROM agent_performance 
                                      WHERE performance_month = ar.performance_month) THEN 'Slow Response'
      WHEN escalation_rate > (SELECT PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY escalation_rate) 
                              FROM agent_performance 
                              WHERE performance_month = ar.performance_month) THEN 'High Escalations'
      ELSE 'Process Optimization'
    END as primary_improvement_area
  FROM agent_rankings ar
  WHERE performance_tier = 'Needs Improvement'
)
SELECT 
  ar.performance_month,
  ar.agent_name,
  ar.department,
  ar.experience_level,
  ar.total_surveys,
  ar.agent_csat_percentage,
  ar.avg_csat_score,
  ar.dept_avg_csat,
  ar.csat_vs_dept_avg,
  ar.performance_tier,
  ar.dept_rank,
  ar.avg_resolution_time,
  ar.escalation_rate,
  io.primary_improvement_area
FROM agent_rankings ar
LEFT JOIN improvement_opportunities io ON ar.agent_id = io.agent_id AND ar.performance_month = io.performance_month
WHERE ar.performance_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '3 months')
ORDER BY ar.performance_month DESC, ar.agent_csat_percentage DESC;