​

Churn Analysis Plan

​

Executive Summary

​

1. Churn Definitions

​

1.1 Account Deletion Churn

• Definition: Users where account_deleted = true
• Use Case: Hard churn - users who explicitly left
• Metric: Account deletion rate

​

1.2 Inactivity-Based Churn

• Definition: Users with no chat activity in last 30/60/90 days
• Use Case: Soft churn - disengaged users
• Metric: Inactivity churn rate by time window

​

1.3 Engagement-Based Churn

• Definition: Users who signed up but never engaged (no chat activity)
• Use Case: Immediate drop-off analysis
• Metric: Sign-up to first engagement rate

​

2. Data Requirements

​

2.1 Tables

• all_users: User demographics and account status
• ai_chat_logs: User engagement data

​

2.2 Key Fields

• clerk_id (user identifier)
• created_at (signup date)
• account_deleted (deletion flag)
• is_test_user (filter out test users)
• country (geographic segmentation)
• signup_provider (acquisition channel)
• onboarding_completed (onboarding success)
• is_international (user type)

• clerk_id (user identifier)
• created_at (activity timestamp)
• session_id (session tracking)

​

3. Analysis Components

​

3.1 Descriptive Statistics

• Overall churn rate (by each definition)
• Churn rate by time period (monthly/quarterly)
• Average time to churn
• Churn velocity (acceleration/deceleration)

• Retention curves by signup cohort
• Cohort retention heatmap
• Month-over-month retention rates
• Lifetime value by cohort

• Churn rate by country
• Churn rate by signup provider
• Churn rate by onboarding status
• International vs domestic churn

​

3.2 Feature Engineering

• Total chat sessions
• Total prompts
• Days since signup
• Days since last activity
• Average prompts per session
• Session frequency
• Activity streak length

• Onboarding completed (binary)
• Time to first chat
• Early activity (first 7/14/30 days)
• Recent activity (last 7/14/30 days)

• Country
• Signup provider
• International flag
• Signup month/quarter

​

3.3 Predictive Modeling

1. Logistic Regression
   • Baseline interpretable model
   • Feature importance via coefficients
   • Probability calibration
2. Random Forest Classifier
   • Non-linear relationships
   • Feature importance ranking
   • Better accuracy for complex patterns

• Train/test split (80/20)
• Cross-validation (5-fold)
• Metrics: Accuracy, Precision, Recall, F1-Score, ROC-AUC
• Confusion matrix
• Calibration curves

• Top 10 features driving churn
• Feature contribution analysis
• Actionable insights extraction

​

3.4 Survival Analysis

• Overall survival probability
• Survival by segment (country, provider, etc.)
• Median survival time
• Confidence intervals

• Hazard ratios for features
• Time-varying effects
• Model validation

​

4. Visualizations

​

4.1 Churn Overview (Section 1)

• Overall churn rate comparison (3 definitions)
• Churn rate trend over time
• Churn funnel visualization

​

4.2 Cohort Analysis (Section 2)

• Cohort retention heatmap
• Retention curves by cohort
• Cohort size and churn distribution

​

4.3 Segmentation (Section 3)

• Churn rate by country (bar chart)
• Churn rate by signup provider
• Churn rate by onboarding status

​

4.4 Predictive Models (Section 4)

• ROC curves (both models)
• Feature importance (top 10)
• Confusion matrices
• Precision-Recall curves

​

4.5 Survival Analysis (Section 5)

• Kaplan-Meier survival curves
• Survival by segment comparison
• Hazard ratio forest plot

​

5. Implementation Plan

​

Phase 1: Data Preparation

1. Load data from database
2. Filter test users
3. Create churn labels (3 definitions)
4. Calculate activity metrics
5. Handle missing values

​

Phase 2: Descriptive Analysis

1. Calculate churn rates
2. Create cohort analysis
3. Segment analysis
4. Generate summary statistics
5. Create visualizations (Sections 1-3)

​

Phase 3: Feature Engineering

1. Build activity features
2. Build engagement features
3. Encode categorical variables
4. Scale numerical features
5. Create final feature matrix

​

Phase 4: Predictive Modeling

1. Split train/test data
2. Train Logistic Regression
3. Train Random Forest
4. Evaluate both models
5. Extract feature importance
6. Generate visualizations (Section 4)

​

Phase 5: Survival Analysis

1. Prepare survival data format
2. Fit Kaplan-Meier curves
3. Fit Cox proportional hazards model
4. Generate survival visualizations (Section 5)

​

Phase 6: Reporting

1. Generate summary CSV with key metrics
2. Create consolidated report index
3. Document findings and recommendations

​

6. Deliverables

​

6.1 Data Outputs

• churn_summary.csv - Key metrics and statistics
• cohort_retention.csv - Cohort retention matrix
• feature_importance.csv - Model feature rankings
• churn_predictions.csv - User-level predictions (optional)

​

6.2 Visualizations (Numbered)

1. 01_churn_rates_comparison.png - Overview of 3 churn definitions
2. 02_churn_trend_over_time.png - Monthly churn rates
3. 03_cohort_retention_heatmap.png - Cohort retention matrix
4. 04_retention_curves_by_cohort.png - Survival by cohort
5. 05_churn_by_country.png - Geographic analysis
6. 06_churn_by_segments.png - Multi-segment comparison
7. 07_model_roc_curves.png - Model performance
8. 08_feature_importance.png - Top predictive features
9. 09_confusion_matrices.png - Model accuracy breakdown
10. 10_kaplan_meier_survival.png - Overall survival curve
11. 11_survival_by_segment.png - Comparative survival
12. 12_hazard_ratios.png - Cox model results

​

6.3 Terminal Output

• Comprehensive statistics for each section
• Model performance metrics
• Key insights and recommendations

​

7. Technical Specifications

​

7.1 Libraries

import pandas as pd
import numpy as np
from sqlalchemy import create_engine
import matplotlib.pyplot as plt
import seaborn as sns

# Modeling
from sklearn.model_selection import train_test_split, cross_val_score
from sklearn.preprocessing import StandardScaler, LabelEncoder
from sklearn.linear_model import LogisticRegression
from sklearn.ensemble import RandomForestClassifier
from sklearn.metrics import (
    accuracy_score, precision_score, recall_score, f1_score,
    roc_auc_score, roc_curve, confusion_matrix, classification_report
)

# Survival Analysis
from lifelines import KaplanMeierFitter, CoxPHFitter

​

7.2 Code Style

• Clean, readable pandas code (no optimizations)
• Well-commented sections
• Clear function definitions
• Consistent naming conventions
• Progress indicators for long operations

​

7.3 Configuration

• Inactivity thresholds: 30, 60, 90 days
• Train/test split: 80/20
• Random state: 42 (for reproducibility)
• Cross-validation folds: 5
• Figure DPI: 300

​

8. Success Metrics

​

8.1 Analysis Completeness

• All 3 churn definitions analyzed
• All segments covered
• All visualizations generated
• Comprehensive summary statistics

​

8.2 Model Performance Targets

• ROC-AUC > 0.70
• Precision > 0.60
• Recall > 0.60
• F1-Score > 0.60

​

8.3 Actionability

• Clear feature importance rankings
• Specific segment recommendations
• Interpretable model insights
• Actionable churn prevention strategies

​

9. Expected Insights

​

9.1 Risk Factors

• Which features most strongly predict churn
• Critical time windows for intervention
• High-risk user segments

​

9.2 Retention Patterns

• How long users typically stay
• When churn accelerates
• Which cohorts retain best

​

9.3 Recommendations

• Early warning indicators
• Intervention strategies
• Segment-specific retention tactics

​

10. Next Steps

1. Review and Approve Plan ✓
2. Create churn_analysis.py script
3. Test on sample data
4. Run full analysis
5. Review outputs and refine
6. Document findings in separate report

​

Notes

• All test users will be filtered out
• Analysis focuses on real user behavior
• Results will inform product and retention strategies
• Script designed for easy re-running with updated data