Retention Analytics

Workflow

1. Cohort Retention Analysis

SQL — weekly retention cohorts:

WITH cohorts AS (
  SELECT user_id, DATE_TRUNC('week', created_at) AS cohort
  FROM users WHERE created_at >= CURRENT_DATE - INTERVAL '90 days'
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('week', event_time) AS active_week
  FROM events WHERE event = 'session_start'
)
SELECT
  c.cohort,
  COUNT(DISTINCT c.user_id) AS cohort_size,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN a.active_week = c.cohort + INTERVAL '1 week' THEN c.user_id END) / COUNT(DISTINCT c.user_id), 1) AS w1_pct,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN a.active_week = c.cohort + INTERVAL '2 weeks' THEN c.user_id END) / COUNT(DISTINCT c.user_id), 1) AS w2_pct,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN a.active_week = c.cohort + INTERVAL '4 weeks' THEN c.user_id END) / COUNT(DISTINCT c.user_id), 1) AS w4_pct,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN a.active_week = c.cohort + INTERVAL '8 weeks' THEN c.user_id END) / COUNT(DISTINCT c.user_id), 1) AS w8_pct
FROM cohorts c
LEFT JOIN activity a ON c.user_id = a.user_id
GROUP BY c.cohort ORDER BY c.cohort;

Retention benchmarks (B2B SaaS):

If W1 retention is below 40%: Activation problem. Fix onboarding. If W1 is fine but M3 drops: Value delivery problem. Users aren't finding ongoing value.

2. Customer Health Score

Composite score (0-100):

Health tiers:

3. Churn Prediction Signals

Early warning signals (14-30 days before churn):

SQL — at-risk detection:

SELECT
  u.user_id,
  u.company_name,
  u.plan,
  u.contract_end,
  COALESCE(recent.sessions_7d, 0) AS sessions_last_7d,
  COALESCE(prior.sessions_7d, 0) AS sessions_prior_7d,
  CASE
    WHEN COALESCE(recent.sessions_7d, 0) = 0 THEN 'critical'
    WHEN recent.sessions_7d < prior.sessions_7d * 0.5 THEN 'high_risk'
    WHEN recent.sessions_7d < prior.sessions_7d * 0.75 THEN 'medium_risk'
    ELSE 'healthy'
  END AS risk_level
FROM users u
LEFT JOIN (
  SELECT user_id, COUNT(*) AS sessions_7d
  FROM events WHERE event = 'session_start' AND event_time >= CURRENT_DATE - 7
  GROUP BY user_id
) recent ON u.user_id = recent.user_id
LEFT JOIN (
  SELECT user_id, COUNT(*) AS sessions_7d
  FROM events WHERE event = 'session_start' AND event_time BETWEEN CURRENT_DATE - 14 AND CURRENT_DATE - 7
  GROUP BY user_id
) prior ON u.user_id = prior.user_id
WHERE u.status = 'active'
ORDER BY risk_level DESC, u.contract_end ASC;

4. Win-Back Campaigns

Timing sequence:

Win-back incentive tiers:

Win-back benchmarks: Expect 5-15% of churned customers to return within 90 days with active win-back. 2-5% without any effort.

5. NPS & Satisfaction

NPS survey timing:

• After onboarding (day 14-30)
• Quarterly for active customers
• After major interaction (support resolution, feature launch)
• Never during billing issues or outages

NPS action framework:

6. Retention Metrics Dashboard