Identify Churn Risks
Zendesk + Product Events

-- Ticket Escalation Risk Analysis
WITH ticket_metrics AS (
  SELECT 
    cm.customer_id,
    cm.company_name,
    COUNT(*) as total_tickets,
    COUNT(CASE WHEN t.priority = 'urgent' THEN 1 END) as urgent_tickets,
    COUNT(CASE WHEN t.status = 'escalated' THEN 1 END) as escalated_tickets,
    AVG(t.satisfaction_rating) as avg_csat,
    AVG(EXTRACT(EPOCH FROM (t.solved_at - t.created_at))/3600) as avg_resolution_hours
  FROM customer_master cm
  JOIN zendesk_tickets t ON cm.zendesk_org_id = t.organization_id
  WHERE t.created_at >= CURRENT_DATE - INTERVAL '60 days'
  GROUP BY cm.customer_id, cm.company_name
),
risk_scores AS (
  SELECT *,
    CASE 
      WHEN escalated_tickets >= 3 OR avg_csat < 2 THEN 'Critical'
      WHEN urgent_tickets >= 5 OR avg_csat < 3 THEN 'High'  
      WHEN total_tickets > 10 OR avg_resolution_hours > 48 THEN 'Medium'
      ELSE 'Low'
    END as support_risk_level,
    (escalated_tickets * 20 + urgent_tickets * 10 + 
     CASE WHEN avg_csat < 2 THEN 40 WHEN avg_csat < 3 THEN 20 ELSE 0 END +
     CASE WHEN avg_resolution_hours > 48 THEN 15 ELSE 0 END) as support_risk_score
  FROM ticket_metrics
)
SELECT 
  customer_id,
  company_name,
  total_tickets,
  escalated_tickets,
  avg_csat,
  support_risk_level,
  support_risk_score
FROM risk_scores
WHERE support_risk_level IN ('Critical', 'High')
ORDER BY support_risk_score DESC;

-- Usage Decline Analysis
WITH usage_trends AS (
  SELECT 
    cm.customer_id,
    cm.company_name,
    DATE_TRUNC('week', pe.event_date) as week,
    COUNT(DISTINCT pe.session_id) as weekly_sessions,
    COUNT(*) as weekly_events,
    COUNT(DISTINCT DATE(pe.event_date)) as active_days,
    AVG(pe.session_duration_minutes) as avg_session_duration
  FROM customer_master cm
  JOIN product_events pe ON cm.product_user_id = pe.user_id
  WHERE pe.event_date >= CURRENT_DATE - INTERVAL '12 weeks'
  GROUP BY cm.customer_id, cm.company_name, DATE_TRUNC('week', pe.event_date)
),
baseline_vs_recent AS (
  SELECT 
    customer_id,
    company_name,
    -- Baseline (weeks 5-12)
    AVG(CASE WHEN week <= CURRENT_DATE - INTERVAL '4 weeks' 
        THEN weekly_sessions END) as baseline_sessions,
    AVG(CASE WHEN week <= CURRENT_DATE - INTERVAL '4 weeks' 
        THEN weekly_events END) as baseline_events,
    -- Recent (last 4 weeks)  
    AVG(CASE WHEN week > CURRENT_DATE - INTERVAL '4 weeks' 
        THEN weekly_sessions END) as recent_sessions,
    AVG(CASE WHEN week > CURRENT_DATE - INTERVAL '4 weeks' 
        THEN weekly_events END) as recent_events
  FROM usage_trends
  GROUP BY customer_id, company_name
  HAVING COUNT(*) >= 8 -- Ensure sufficient data
)
SELECT 
  customer_id,
  company_name,
  baseline_sessions,
  recent_sessions,
  ROUND((recent_sessions - baseline_sessions) / baseline_sessions * 100, 1) as session_change_pct,
  ROUND((recent_events - baseline_events) / baseline_events * 100, 1) as event_change_pct,
  CASE 
    WHEN recent_sessions < baseline_sessions * 0.3 THEN 'Critical'
    WHEN recent_sessions < baseline_sessions * 0.6 THEN 'High'
    WHEN recent_sessions < baseline_sessions * 0.8 THEN 'Medium'
    ELSE 'Low'
  END as usage_risk_level
FROM baseline_vs_recent
WHERE recent_sessions < baseline_sessions * 0.8
ORDER BY session_change_pct ASC;

-- Combined Churn Risk Assessment
WITH support_risk AS (
  SELECT 
    cm.customer_id,
    COALESCE(COUNT(CASE WHEN t.priority = 'urgent' THEN 1 END) * 10, 0) +
    COALESCE(COUNT(CASE WHEN t.status = 'escalated' THEN 1 END) * 20, 0) +
    CASE WHEN AVG(t.satisfaction_rating) < 2 THEN 40 
         WHEN AVG(t.satisfaction_rating) < 3 THEN 20 ELSE 0 END as support_score
  FROM customer_master cm
  LEFT JOIN zendesk_tickets t ON cm.zendesk_org_id = t.organization_id 
    AND t.created_at >= CURRENT_DATE - INTERVAL '60 days'
  GROUP BY cm.customer_id
),
usage_risk AS (
  SELECT 
    cm.customer_id,
    CASE 
      WHEN COUNT(pe.event_id) = 0 THEN 60
      WHEN COUNT(DISTINCT DATE(pe.event_date)) < 5 THEN 40
      WHEN AVG(pe.session_duration_minutes) < 2 THEN 30
      WHEN COUNT(pe.event_id) < 50 THEN 20
      ELSE 0
    END as usage_score
  FROM customer_master cm
  LEFT JOIN product_events pe ON cm.product_user_id = pe.user_id 
    AND pe.event_date >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY cm.customer_id
),
account_risk AS (
  SELECT 
    cm.customer_id,
    CASE 
      WHEN cm.mrr < 100 THEN 15
      WHEN DATE_PART('day', CURRENT_DATE - cm.signup_date) < 90 THEN 10
      ELSE 0
    END as account_score
  FROM customer_master cm
)
SELECT 
  cm.customer_id,
  cm.company_name,
  cm.mrr,
  cm.subscription_tier,
  sr.support_score,
  ur.usage_score,
  ar.account_score,
  (sr.support_score + ur.usage_score + ar.account_score) as total_risk_score,
  CASE 
    WHEN (sr.support_score + ur.usage_score + ar.account_score) >= 80 THEN 'Critical'
    WHEN (sr.support_score + ur.usage_score + ar.account_score) >= 50 THEN 'High'
    WHEN (sr.support_score + ur.usage_score + ar.account_score) >= 25 THEN 'Medium'
    ELSE 'Low'
  END as risk_category,
  CASE 
    WHEN (sr.support_score + ur.usage_score + ar.account_score) >= 80 THEN 'Immediate intervention required'
    WHEN (sr.support_score + ur.usage_score + ar.account_score) >= 50 THEN 'Proactive outreach recommended'
    WHEN (sr.support_score + ur.usage_score + ar.account_score) >= 25 THEN 'Monitor closely'
    ELSE 'Healthy account'
  END as recommended_action
FROM customer_master cm
JOIN support_risk sr ON cm.customer_id = sr.customer_id
JOIN usage_risk ur ON cm.customer_id = ur.customer_id  
JOIN account_risk ar ON cm.customer_id = ar.customer_id
ORDER BY total_risk_score DESC;