> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cfosilvia.com/llms.txt
> Use this file to discover all available pages before exploring further.

# CHURNPLAN

# Churn Analysis Plan

## Executive Summary

This document outlines a comprehensive churn analysis strategy for understanding user retention, identifying at-risk users, and predicting churn using multiple methodologies.

***

## 1. Churn Definitions

We'll analyze churn using three complementary approaches:

### 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

**From all\_users:**

* `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)

**From ai\_chat\_logs:**

* `clerk_id` (user identifier)
* `created_at` (activity timestamp)
* `session_id` (session tracking)

***

## 3. Analysis Components

### 3.1 Descriptive Statistics

**Churn Metrics:**

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

**Cohort Analysis:**

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

**Segmentation Analysis:**

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

### 3.2 Feature Engineering

**User Activity Features:**

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

**Engagement Features:**

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

**Demographic Features:**

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

### 3.3 Predictive Modeling

**Models to Implement:**

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

**Model Evaluation:**

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

**Feature Importance:**

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

### 3.4 Survival Analysis

**Kaplan-Meier Survival Curves:**

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

**Cox Proportional Hazards:**

* 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

```python theme={null}
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


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.