Situation: I was asked to analyze rising churn for a subscription product after quarterly revenue dipped. Initial dashboards showed overall churn ~8%/month but anecdotal reports suggested specific customer groups were leaving faster.Task: Identify any high-risk segment, quantify the risk, persuade Product to run targeted retention actions, and measure impact.Action:- Data sources & filters: I joined billing, product-usage, and CRM tables (payments, feature_events, support_tickets). SQL filters limited to active subscribers in the last 12 months, excluding trial-only accounts and involuntary churn (payment failures).- Metrics and identification: I computed monthly churn rate, 30/60/90-day retention, and LTV. Using cohort analysis and a logistic regression, I found customers on the Basic plan who used <3 core-feature events/week and had 2+ support tickets in month 1 had churn 18% vs. baseline 8%.- SQL snippet used to create the segment:sql
SELECT user_id
FROM subscriptions s
JOIN usage u ON s.user_id = u.user_id
JOIN tickets t ON s.user_id = t.user_id
WHERE s.plan = 'Basic'
AND u.avg_weekly_events < 3
AND t.ticket_count_month1 >= 2
AND s.start_date BETWEEN DATEADD(month, -12, CURRENT_DATE) AND CURRENT_DATE;
