How to Detect Silent Churn: Paying Customers Who Stopped Using Your Product

# Define meaningful product events for your app
MEANINGFUL_EVENTS = [
    "report_created",
    "dashboard_viewed",
    "query_executed",
    "export_downloaded",
    "integration_used",
    "team_member_invited",
]
 
# Events that DON'T count as meaningful engagement
SHALLOW_EVENTS = [
    "page_viewed",       # Too passive
    "logged_in",         # Doesn't indicate value received
    "settings_opened",   # Often precedes cancellation
    "billing_viewed",    # Definitely precedes cancellation
]

# customer_health_score.py
from datetime import datetime, timedelta
from dataclasses import dataclass
 
 
@dataclass
class CustomerHealth:
    email: str
    stripe_status: str
    mrr: float
    last_meaningful_event: datetime | None
    events_last_30d: int
    events_last_7d: int
    days_inactive: int
    health_score: float  # 0-100
    risk_level: str      # "healthy", "at_risk", "critical", "zombie"
 
 
def calculate_health_score(
    stripe_status: str,
    last_meaningful_event: datetime | None,
    events_last_30d: int,
    events_last_7d: int,
) -> tuple[float, str]:
    """
    Calculate a customer health score from 0-100.
    Higher = healthier. Weights:
      - Recent activity (last 7d): 40%
      - Trend (30d volume): 30%
      - Recency (days since last event): 30%
    """
    now = datetime.utcnow()
    
    # Recency score (0-100)
    if last_meaningful_event is None:
        recency_score = 0
    else:
        days_since = (now - last_meaningful_event).days
        if days_since <= 3:
            recency_score = 100
        elif days_since <= 7:
            recency_score = 80
        elif days_since <= 14:
            recency_score = 60
        elif days_since <= 30:
            recency_score = 30
        else:
            recency_score = max(0, 10 - (days_since - 30))
    
    # Recent activity score (0-100)
    if events_last_7d >= 10:
        recent_score = 100
    elif events_last_7d >= 5:
        recent_score = 75
    elif events_last_7d >= 1:
        recent_score = 40
    else:
        recent_score = 0
    
    # Trend score (0-100)
    if events_last_30d >= 30:
        trend_score = 100
    elif events_last_30d >= 15:
        trend_score = 70
    elif events_last_30d >= 5:
        trend_score = 40
    elif events_last_30d >= 1:
        trend_score = 15
    else:
        trend_score = 0
    
    # Weighted composite
    score = (recent_score * 0.4) + (trend_score * 0.3) + (recency_score * 0.3)
    
    # Risk level
    if score >= 60:
        risk = "healthy"
    elif score >= 35:
        risk = "at_risk"
    elif score >= 10:
        risk = "critical"
    else:
        risk = "zombie"
    
    return score, risk

WITH customer_activity AS (
  SELECT
    sc.email,
    sc.id AS stripe_customer_id,
    s.status AS subscription_status,
    SUM(si.unit_amount * si.quantity) / 100.0 AS mrr,
    MAX(pe.event_time) AS last_meaningful_event,
    COUNT(CASE WHEN pe.event_time >= CURRENT_DATE - INTERVAL '30 days' 
               THEN 1 END) AS events_30d,
    COUNT(CASE WHEN pe.event_time >= CURRENT_DATE - INTERVAL '7 days' 
               THEN 1 END) AS events_7d,
    COALESCE(
      CURRENT_DATE - MAX(pe.event_time)::date,
      999
    ) AS days_inactive
  FROM stripe_customers sc
  JOIN stripe_subscriptions s ON sc.id = s.customer_id
  JOIN stripe_subscription_items si ON s.id = si.subscription_id
  LEFT JOIN product_events pe
    ON sc.email = pe.user_email
    AND pe.event_name IN (
      'report_created', 'dashboard_viewed', 'query_executed',
      'export_downloaded', 'integration_used'
    )
    AND pe.event_time >= CURRENT_DATE - INTERVAL '90 days'
  WHERE s.status = 'active'
  GROUP BY sc.email, sc.id, s.status
)
SELECT
  email,
  mrr,
  days_inactive,
  events_30d,
  events_7d,
  CASE
    WHEN events_7d >= 5 THEN 'healthy'
    WHEN events_7d >= 1 AND events_30d >= 5 THEN 'healthy'
    WHEN events_30d >= 1 AND days_inactive <= 14 THEN 'at_risk'
    WHEN days_inactive BETWEEN 15 AND 30 THEN 'critical'
    ELSE 'zombie'
  END AS risk_level
FROM customer_activity
WHERE days_inactive >= 14  -- Only show at-risk and worse
ORDER BY mrr DESC;

# intervention_workflow.py
 
def trigger_intervention(customer: CustomerHealth):
    """Dispatch the right intervention based on risk level."""
    
    if customer.risk_level == "at_risk":
        # Automated: "We noticed you haven't logged in..."
        send_reengagement_email(
            email=customer.email,
            template="gentle_checkin",
            context={
                "days_inactive": customer.days_inactive,
                "last_feature_used": get_last_feature(customer.email),
            },
        )
        # Flag in CRM for CS awareness
        update_hubspot_property(
            customer.email,
            "customer_health_status",
            "at_risk",
        )
    
    elif customer.risk_level == "critical":
        # Human outreach: assign to CSM
        create_hubspot_task(
            assigned_to=get_csm_for_customer(customer.email),
            title=f"Silent churn risk: {customer.email} ({customer.days_inactive}d inactive)",
            notes=(
                f"Customer paying ${customer.mrr:.0f}/mo but hasn't used "
                f"the product in {customer.days_inactive} days. "
                f"Last 30d events: {customer.events_last_30d}. "
                f"Recommend personal check-in."
            ),
            priority="HIGH",
        )
    
    elif customer.risk_level == "zombie":
        # Alert RevOps leadership
        send_slack_alert(
            channel="#revops-alerts",
            text=(
                f"🧟 Zombie customer: {customer.email}\n"
                f"MRR: ${customer.mrr:.0f}/mo | "
                f"Inactive: {customer.days_inactive} days | "
                f"Zero product events in 30+ days\n"
                f"Likely churning at next renewal or card expiry."
            ),
        )