SaaS Churn Analysis

Logo Churn Rate (monthly) = Customers Lost / Customers at Start of Period

Example:
  Start of month: 200 customers
  Canceled: 5
  Logo churn rate: 5/200 = 2.5%

Gross Revenue Churn Rate = MRR Lost to Cancellations / MRR at Start of Period

Example:
  Start MRR: $100,000
  Churned MRR: $4,000 (from cancellations)
  Gross churn: 4%

NRR = (Beginning MRR + Expansion MRR - Contraction MRR - Churned MRR) / Beginning MRR × 100

Components:
  + Expansion MRR: upsells, upgrades, seat additions from existing customers
  - Contraction MRR: downgrades, reduced seats
  - Churned MRR: cancellations

Example:
  Beginning MRR: $100,000
  Expansion: +$8,000
  Contraction: -$2,000
  Churn: -$4,000
  NRR = ($100,000 + $8,000 - $2,000 - $4,000) / $100,000 = 102%

GRR = (Beginning MRR - Contraction MRR - Churned MRR) / Beginning MRR × 100
     (excludes expansion — pure retention, no upsell credit)

Healthy GRR benchmarks:
  Enterprise SaaS: >90%
  Mid-market: >85%
  SMB SaaS: >75%

import pandas as pd
from datetime import datetime

def build_cohort_table(subscriptions_df: pd.DataFrame) -> pd.DataFrame:
    """
    Build a cohort retention table from subscription data.
    
    Input DataFrame columns:
        - customer_id: str
        - signup_date: datetime
        - cancel_date: datetime | None (None = still active)
    
    Returns:
        Pivot table: rows = cohort month, columns = months_since_signup,
        values = retention percentage
    """
    df = subscriptions_df.copy()
    df['cohort_month'] = df['signup_date'].dt.to_period('M')
    df['active_through'] = df['cancel_date'].fillna(pd.Timestamp.now())
    
    rows = []
    for cohort, group in df.groupby('cohort_month'):
        cohort_size = len(group)
        for month_offset in range(0, 25):  # 0–24 months
            cutoff = cohort.to_timestamp() + pd.DateOffset(months=month_offset)
            active = group[group['active_through'] >= cutoff].shape[0]
            retention = active / cohort_size * 100
            rows.append({
                'cohort': str(cohort),
                'month': month_offset,
                'cohort_size': cohort_size,
                'active': active,
                'retention_pct': round(retention, 1)
            })
    
    result = pd.DataFrame(rows)
    pivot = result.pivot(index='cohort', columns='month', values='retention_pct')
    return pivot

Cohort     | M0    | M1    | M3    | M6    | M12
-----------|-------|-------|-------|-------|------
2025-01    | 100%  | 91%   | 81%   | 72%   | 58%
2025-02    | 100%  | 93%   | 84%   | 76%   | —
2025-03    | 100%  | 89%   | 79%   | —     | —
2025-04    | 100%  | 94%   | —     | —     | —

def revenue_cohort_table(mrr_events_df: pd.DataFrame) -> pd.DataFrame:
    """
    Revenue cohort analysis tracking MRR per acquisition cohort.
    
    Input DataFrame columns:
        - customer_id: str
        - event_date: datetime
        - event_type: str  # 'signup', 'expansion', 'contraction', 'churn'
        - mrr_change: float
    
    Returns:
        Cohort revenue retention table (% of original MRR retained+expanded)
    """
    # Group by signup cohort
    signups = mrr_events_df[mrr_events_df['event_type'] == 'signup'].copy()
    signups['cohort_month'] = signups['event_date'].dt.to_period('M')
    
    # For each cohort, track MRR over time
    # NRR by cohort = sum(all MRR changes for cohort customers) / initial MRR
    pass

Early churn (M1-M3): Onboarding failure, value not delivered
  → Diagnosis: activation rate, time-to-first-value, support tickets
  
Mid-term churn (M4-M12): Competitive displacement, budget cuts
  → Diagnosis: NPS trends, feature adoption, renewal engagement
  
Late churn (M12+): Strategic shifts, contract terms, enterprise competition
  → Diagnosis: executive sponsor changes, usage trends, renewal conversations

def churn_by_tenure(subscriptions_df: pd.DataFrame) -> dict:
    """Calculate churn rate for different tenure buckets."""
    buckets = {
        '0-3mo': (0, 90),
        '3-6mo': (90, 180),
        '6-12mo': (180, 365),
        '12-24mo': (365, 730),
        '24mo+': (730, float('inf'))
    }
    
    results = {}
    for bucket_name, (min_days, max_days) in buckets.items():
        mask = (
            (subscriptions_df['tenure_days'] >= min_days) &
            (subscriptions_df['tenure_days'] < max_days)
        )
        bucket_df = subscriptions_df[mask]
        if len(bucket_df) == 0:
            continue
        churned = bucket_df[bucket_df['cancel_date'].notna()].shape[0]
        results[bucket_name] = {
            'total_customers': len(bucket_df),
            'churned': churned,
            'churn_rate_pct': round(churned / len(bucket_df) * 100, 1)
        }
    return results

CHURN_RISK_WEIGHTS = {
    'days_since_last_login': 0.25,       # Usage drop
    'feature_adoption_pct': -0.20,       # Inverse: more features = lower risk
    'support_tickets_30d': 0.15,         # Escalations
    'nps_score': -0.15,                  # Inverse: high NPS = lower risk
    'days_to_renewal': -0.10,            # Closer renewal = higher urgency
    'billing_failures_90d': 0.15,        # Payment issues
}

def churn_risk_score(customer: dict) -> float:
    """
    Calculate 0-100 churn risk score for a customer.
    Higher = more likely to churn.
    
    Inputs:
        customer: dict with keys matching CHURN_RISK_WEIGHTS
    
    Returns:
        Risk score 0-100 (>70 = high risk, 40-70 = medium, <40 = low)
    """
    raw_score = 0
    for factor, weight in CHURN_RISK_WEIGHTS.items():
        if factor in customer:
            # Normalize each factor to 0-100 scale first
            normalized = normalize_factor(factor, customer[factor])
            raw_score += normalized * weight
    
    # Scale to 0-100
    return max(0, min(100, raw_score * 100 + 50))

def get_at_risk_accounts(customers: list, threshold: float = 70.0) -> list:
    """Return customers with churn risk score above threshold, sorted by MRR."""
    at_risk = [
        {**c, 'risk_score': churn_risk_score(c)}
        for c in customers
    ]
    return sorted(
        [c for c in at_risk if c['risk_score'] >= threshold],
        key=lambda x: x.get('mrr', 0),
        reverse=True  # Highest MRR first — prioritize by revenue impact
    )

🔴 High risk:
  - No login in 14+ days (was weekly user)
  - DAU/MAU ratio dropped >50% MoM
  - Core feature not used in 30 days
  - Below 20% feature adoption vs peers

🟡 Medium risk:
  - Login frequency dropped >30% MoM
  - Support ticket with "cancel" or "refund" keyword
  - NPS score ≤ 6 (detractor)
  - Seat count reduced

🟢 Healthy signals:
  - Expanded seats or upgraded tier
  - Used 3+ core features this month
  - NPS ≥ 9 (promoter)
  - Referred another customer

🔴 High risk:
  - Payment failure (retry in progress)
  - Requested invoice-based payment shift (budget freeze)
  - Contract not opened with 30 days to renewal

🟡 Medium risk:
  - Asked about pricing alternatives
  - Billing contact changed
  - Discount request submitted

MRR Bridge: January → February

Beginning MRR:     $100,000
+ New Business:      +$8,500   (23 new customers × $370 avg)
+ Expansion:         +$3,200   (upgrades + seat additions)
- Contraction:       -$1,100   (downgrades + seat reductions)
- Churn:             -$4,300   (11 cancellations × $390 avg)
= Ending MRR:      $106,300

Net New MRR:        +$6,300
MoM Growth:          6.3%

from dataclasses import dataclass

@dataclass
class MRRBridge:
    period: str
    beginning_mrr: float
    new_mrr: float           # New customers
    expansion_mrr: float     # Upsells/upgrades
    contraction_mrr: float   # Downgrades (negative or positive — store as positive)
    churned_mrr: float       # Cancellations (store as positive)
    
    @property
    def ending_mrr(self) -> float:
        return self.beginning_mrr + self.new_mrr + self.expansion_mrr - self.contraction_mrr - self.churned_mrr
    
    @property
    def net_new_mrr(self) -> float:
        return self.ending_mrr - self.beginning_mrr
    
    @property
    def growth_rate_pct(self) -> float:
        return self.net_new_mrr / self.beginning_mrr * 100 if self.beginning_mrr else 0
    
    @property
    def quick_ratio(self) -> float:
        """SaaS Quick Ratio = (New + Expansion) / (Contraction + Churn). >4 = healthy."""
        numerator = self.new_mrr + self.expansion_mrr
        denominator = self.contraction_mrr + self.churned_mrr
        return numerator / denominator if denominator else float('inf')
    
    def to_summary(self) -> str:
        return (
            f"MRR Bridge ({self.period})\n"
            f"  Beginning: ${self.beginning_mrr:,.0f}\n"
            f"  + New:       ${self.new_mrr:,.0f}\n"
            f"  + Expansion: ${self.expansion_mrr:,.0f}\n"
            f"  - Contraction: ${self.contraction_mrr:,.0f}\n"
            f"  - Churn:     ${self.churned_mrr:,.0f}\n"
            f"  = Ending:  ${self.ending_mrr:,.0f}\n"
            f"  Growth: {self.growth_rate_pct:.1f}% | Quick Ratio: {self.quick_ratio:.1f}x"
        )

□ Did they complete onboarding? (activation rate)
□ Did they use the core feature at least once? (activation event)
□ How long did it take to reach first value moment?
□ Did they get a human touchpoint in first 48 hours?

Day 1-7:   Personal outreach from CSM — "What would make this a 10/10?"
Day 7-14:  Offer 1:1 onboarding session + extend trial if applicable
Day 14-21: Share customer success story in their industry/use case
Day 21-30: Executive touchpoint if MRR > $500/mo

□ Usage trend: up, flat, or declining in last 60 days?
□ When did they last use the feature most tied to their stated goal?
□ Any support escalations or complaints in the last 90 days?
□ Have they been pitched by a competitor? (ask directly)
□ Is this a budget-driven decision or product-driven?

Budget:
  → Offer pause plan (90-day pause vs cancel)
  → Right-size to smaller plan vs lose them entirely
  → Annual prepay at 20% discount to lock in

Product gaps:
  → Roadmap call with PM — "here's what's coming"
  → Workaround documentation for their specific use case
  → Connect to power-user customer for peer validation

Competitor evaluation:
  → Direct competitive comparison matrix
  → Migration cost analysis (switching is expensive)
  → Win-back offer if they've already left (45-day re-engagement)

60 days out:
  □ Usage review: send personalized "Your results with [Product]" email
  □ Identify any open issues — resolve before renewal conversation

45 days out:
  □ QBR or check-in call — confirm value, surface upsell opportunity
  □ Flag to AE if NPS < 7 or usage declining

30 days out:
  □ Renewal proposal sent — include current plan + upsell option
  □ Executive sponsor confirmation (for accounts >$1k/mo)

14 days out:
  □ Follow-up if no response — switch to phone
  □ Escalate to manager if no reply

7 days out:
  □ Final decision call — accept reduced terms if needed to retain

{
  "period": "Q4 2025",
  "generated_at": "2026-01-15",
  "retention_metrics": {
    "logo_churn_rate_monthly": 2.1,
    "mrr_gross_churn_rate_monthly": 3.8,
    "net_revenue_retention_pct": 108,
    "gross_revenue_retention_pct": 96.2,
    "quick_ratio": 3.2
  },
  "mrr_bridge": {
    "beginning_mrr": 285000,
    "new_mrr": 42000,
    "expansion_mrr": 18500,
    "contraction_mrr": 4200,
    "churned_mrr": 10800,
    "ending_mrr": 330500
  },
  "at_risk_pipeline": {
    "high_risk_count": 8,
    "high_risk_mrr_at_risk": 24600,
    "medium_risk_count": 15,
    "medium_risk_mrr_at_risk": 38200
  },
  "cohort_highlights": {
    "best_cohort": { "month": "2025-03", "m12_retention": 74 },
    "worst_cohort": { "month": "2025-08", "m3_retention": 71 },
    "avg_m12_retention": 68.5
  },
  "benchmarks": {
    "nrr_vs_industry": "above_median",
    "grr_vs_industry": "top_quartile",
    "logo_churn_vs_industry": "median"
  }
}

cohort_retention_csv_template:
Cohort,Size,M1,M2,M3,M6,M9,M12,M18,M24
2025-01,45,91%,84%,81%,73%,67%,61%,55%,49%
2025-02,52,93%,87%,83%,—,—,—,—,—
...