Pre-Churn Behavior Pattern Analysis

Pre-Churn Behavior Pattern Analysis Results

Statistical Comparison: Churned vs Retained

Analysis window: 30 days before churn/present
Sample: 47 churned, 203 retained users over 6 months

Interpretation

All metrics show statistically significant differences (p < 0.05). Users who churned:

• Log in 70% less frequently
• Use 50% fewer unique features
• Engage with core features 75% less
• Are active 64% fewer days per month
• Have 3x more support tickets

Risk Thresholds (Optimized for Your Product)

Based on your data distribution (18.8% churn rate):

Churn Risk Scoring Formula

def churn_risk_score(user_metrics):
    """
    Calculate churn risk score from user behavior metrics.
    Returns score 0-100 (higher = more at risk)
    """
    score = 0
    
    # Login frequency (30 points)
    if user_metrics['login_count'] < 3:
        score += 30
    elif user_metrics['login_count'] < 8:
        score += 15
    
    # Core feature engagement (40 points - strongest predictor)
    if user_metrics['core_feature_uses'] < 6:
        score += 40
    elif user_metrics['core_feature_uses'] < 18:
        score += 20
    
    # Days active (20 points)
    if user_metrics['days_active'] < 5:
        score += 20
    elif user_metrics['days_active'] < 11:
        score += 10
    
    # Support ticket volume (10 points)
    if user_metrics['support_tickets'] > 2:
        score += 10
    elif user_metrics['support_tickets'] >= 1:
        score += 5
    
    return score  # 0-100 scale

Intervention Playbook

Email Templates by Risk Tier

Critical Risk (70-100): Personal Founder Outreach

Subject: Quick check-in about [Product Name]

Hi [First Name],

I’m [Your Name], founder of [Product Name]. I noticed your account activity has dropped off recently, and I wanted to personally reach out.

I know how busy things get, but if there’s something we’re missing or if you’re running into challenges, I’d genuinely love to hear about it. Even 5 minutes on a call would be incredibly valuable.

Would you be open to a quick conversation this week? I can work around your schedule.

Thanks for being a customer,
[Your Name]
[Your Title]
[Direct phone/calendar link]

Expected response rate: 35-45%

SQL Query for Weekly At-Risk Report

WITH user_metrics AS (
  SELECT
    user_id,
    COUNT(DISTINCT CASE WHEN event_type = 'login' THEN DATE(timestamp) END) AS login_count,
    COUNT(DISTINCT feature_name) AS unique_features,
    COUNT(*) FILTER (WHERE event_type IN ('core_action_1', 'core_action_2', 'core_action_3')) AS core_feature_uses,
    COUNT(DISTINCT DATE(timestamp)) AS days_active
  FROM event_logs
  WHERE timestamp >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY user_id
),
support_metrics AS (
  SELECT
    user_id,
    COUNT(*) AS support_tickets
  FROM support_tickets
  WHERE ticket_date >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY user_id
),
risk_scores AS (
  SELECT
    s.user_id,
    s.email,
    s.signup_date,
    s.plan,
    COALESCE(um.login_count, 0) AS login_count,
    COALESCE(um.core_feature_uses, 0) AS core_feature_uses,
    COALESCE(um.days_active, 0) AS days_active,
    COALESCE(um.unique_features, 0) AS unique_features,
    COALESCE(sm.support_tickets, 0) AS support_tickets,
    (CASE 
       WHEN COALESCE(um.login_count, 0) < 3 THEN 30 
       WHEN COALESCE(um.login_count, 0) < 8 THEN 15 
       ELSE 0 
     END +
     CASE 
       WHEN COALESCE(um.core_feature_uses, 0) < 6 THEN 40 
       WHEN COALESCE(um.core_feature_uses, 0) < 18 THEN 20 
       ELSE 0 
     END +
     CASE 
       WHEN COALESCE(um.days_active, 0) < 5 THEN 20 
       WHEN COALESCE(um.days_active, 0) < 11 THEN 10 
       ELSE 0 
     END +
     CASE 
       WHEN COALESCE(sm.support_tickets, 0) > 2 THEN 10
       WHEN COALESCE(sm.support_tickets, 0) >= 1 THEN 5
       ELSE 0 
     END) AS risk_score
  FROM subscriptions s
  LEFT JOIN user_metrics um ON s.user_id = um.user_id
  LEFT JOIN support_metrics sm ON s.user_id = sm.user_id
  WHERE s.cancel_date IS NULL
    AND s.signup_date < CURRENT_DATE - INTERVAL '14 days'
)
SELECT
  user_id,
  email,
  signup_date,
  plan,
  login_count,
  core_feature_uses,
  days_active,
  unique_features,
  support_tickets,
  risk_score,
  CASE
    WHEN risk_score >= 70 THEN 'CRITICAL - Founder outreach needed'
    WHEN risk_score >= 50 THEN 'HIGH - CS team intervention'
    WHEN risk_score >= 30 THEN 'MEDIUM - Automated re-engagement'
    ELSE 'LOW - Healthy'
  END AS risk_tier
FROM risk_scores
WHERE risk_score >= 30
ORDER BY risk_score DESC, signup_date ASC;

Implementation Steps

Week 1: Setup

1. Export event logs, subscription data, and support tickets
2. Set up SQL query or script to calculate risk scores weekly
3. Create notification system for at-risk users
4. Draft and approve email templates

Week 2: Pilot

1. Identify current at-risk users (scores >50)
2. Launch interventions with high-risk tier first
3. Track open rates, response rates, outcomes

Week 3+: Scale & Iterate

1. Expand to medium-risk tier
2. Measure impact: Are interventions reducing churn?
3. Refine thresholds based on results

Measurement Framework

Track these metrics weekly to validate intervention effectiveness:

Success criteria: 20% reduction in churn rate within 3 months (from 18.8% to 15%)