Approach (overview)
- Treat churn as a survival problem with right-censoring and cohort heterogeneity. Compute LTV as expected present value of future recurring revenue = sum_t P(customer active at t | data) * revenue_t discounted.
Models to consider
- Kaplan–Meier (nonparametric): fast, cohort-level survival curves that handle censoring; good for exploratory cohort comparisons.
- Parametric survival (Weibull/Log‑normal): smooth extrapolation of tail where censoring is heavy; allows analytic survival S(t).
- Cox proportional hazards: include time-invariant / time-varying covariates (plan, acquisition channel, promo) to model hazard heterogeneity without specifying baseline.
- BG/NBD or Pareto/NBD: suitable if purchase/renewal events are discrete and intermittent (non-contractual churn). Use if inter-purchase process matches model assumptions.
- Hierarchical/Bayesian survival: pools cohorts, shares strength for small cohorts and gives full posterior uncertainty.
Required inputs & pipeline responsibilities (Data Engineer focus)
- Event-level data: customer_id, event_type (signup, renewal, payment, cancellation), timestamp.
- Billing: amount, currency, discount code, promo start/end, billing cadence.
- Account state: plan, upgrades/downgrades, trial periods, refunds.
- Customer attributes: cohort (acq_date), channel, region, device.
- Build ETL that emits tidy customer-time table (one row per customer-per-period) and handles backfills, timezone normalization, deterministic censoring date.
- Create features for models: tenure, time-varying revenue, exposures, covariates; ensure reproducible cohort assignment and censoring flags.
Incorporating recurring revenue & discounts
- Define period granularity (monthly/weekly).
- revenue_t = billed_amount_t * (1 - discount_t) + episodic revenue (upsells).
- For variable ARPU, model revenue as conditional on being active: E[revenue_t | active] estimated empirically or via regression (GLM/GAM) with covariates.
- LTV formula: LTV = sum_{t=1..T} discount_factor(t) * S(t) * E[revenue_t | active].
- For churn-protection plans (annual prepay), treat prepaid periods as guaranteed revenue and adjust survival to next billing date.
Computing confidence intervals / uncertainty
- Nonparametric: use Greenwood’s formula for KM standard errors → CI on S(t); propagate into LTV via delta method or Monte Carlo sampling of S(t) within CIs.
- Parametric/Bayesian: posterior draws of parameters → generate S(t) and revenue_t per draw → produce LTV distribution and credible intervals.
- Bootstrap: resample customers (cluster bootstrap by customer) → refit model or recompute KM → get empirical LTV distribution.
- Present both point estimate and e.g., 90% interval; show fan charts over horizon.
Presentation & diagnostics
- Cohort survival curves (KM) + parametric fit overlay; hazard ratios from Cox for key covariates.
- LTV curve by cohort with shaded CI bands; table of 6/12/36-month LTV.
- Calibration: compare predicted vs observed retention/revenue in holdout periods.
- Sensitivity: show LTV under alternate discount rates and tail-extrapolation assumptions.
Trade-offs / practical notes
- KM is robust but won’t extrapolate; parametric/Bayesian needed for long horizons.
- Cox assumes proportional hazards; test violations and consider time-varying coef.
- For large-scale production, precompute cohort survival and revenue slices in data warehouse and serve model outputs via feature tables / scheduled jobs.
- Automate model retraining and monitoring (drift tests on survival curves, revenue residuals).
