A governed retention analysis system over 442,211,685 accepted membership, payment, listening, and churn-label rows, with cutoff-safe cohorts, a calibrated repeat-subscriber model, and an assumption-bound intervention planner.
Subscriber Retention Intelligence
Which retention movements need investigation, and where could finite outreach be plausible without overstating the evidence?
The product separates descriptive movement, calibrated historical risk, and simulated economics. The challenger is supported only for 881,701 repeat subscribers, improves log loss by 5.57 percent, and concentrates churn 4.95 times above baseline in the top decile. Contact economics remain declared sensitivity assumptions, not causal forecasts.
A subscription lifecycle analyst can locate material renewal and engagement changes, compare cohorts and segments on governed definitions, and test a finite contact program without confusing historical risk with causal treatment response.
Analytics Engineer
Built a DuckDB and dbt retention warehouse over 442.2 million accepted membership, payment, listening, and churn-label rows, with cutoff and row-reconciliation tests and a complete transformed Parquet release.A subscription lifecycle analyst or retention lead reviewing renewal behavior, engagement change, historical churn risk, and finite contact capacity.
Context and stakes
The decision context behind the system.
The project turns a large historical subscription corpus into an auditable decision workflow. It identifies material retention changes, evaluates a calibrated risk model without hiding its population failure, and lets an analyst test finite outreach assumptions. It publishes both the interface and every accepted transformed row while keeping recurring project cost and public request-time compute at zero.
A full-depth retention analysis system over 442.2 million accepted rows that combines governed descriptive analysis, a carefully scoped churn model, an assumption-bound scenario planner, and a complete zero-cost public data release.
Approach
A bounded approach shaped by the published constraints.
The product separates descriptive movement, calibrated historical risk, and simulated economics. The challenger is supported only for 881,701 repeat subscribers, improves log loss by 5.57 percent, and concentrates churn 4.95 times above baseline in the top decile. Contact economics remain declared sensitivity assumptions, not causal forecasts.
Design constraints
- Only two observed label windows exist, so calibration validation is cross-sectional rather than a third temporal period
- March-new subscribers fail the probability-use boundary and are excluded from the scenario planner
- The source contains no randomized intervention, contact delivery, offer acceptance, or causal retention outcome
- Gross receipts are not recognized revenue, margin, lifetime value, or future receipts
Decision journey
The system moves through an inspectable sequence.
Each stage below comes from the published project manifest.
- Stage
Stream compressed archives through
Source archives remain outside Git and stream into typed partitioned Parquet
- Stage
Build detailed typed facts
DuckDB and dbt reconstruct subscriber state, snapshots, cohorts, segments, and metric marts
- Stage
Evaluate baseline and calibrated
A calibrated challenger scores only the population supported by the evaluation contract
- Stage
Bound probability use to
React consumes governed public aggregates while private routes remain local
- Stage
Publish the static analysis
Cloudflare Pages serves the zero-compute interface and Hugging Face serves the complete transformed Parquet release
Interactive architecture
The published architecture, made interactive.
Compressed sources stream into typed Parquet, then DuckDB and dbt reconstruct subscription state and build cutoff-safe snapshots, cohorts, segments, and metric marts. A calibrated nonlinear challenger is compared with a logistic baseline and is shipped only for repeat subscribers. The public interface is static, while the complete transformed data plane is distributed separately as Parquet.
Drag to pan · use controls to zoom · select any stageRead the architecture as text
- Source archives remain outsideStage 1 · Python
Source archives remain outside Git and stream into typed partitioned Parquet
- DuckDB and dbt reconstructStage 2 · DuckDB
DuckDB and dbt reconstruct subscriber state, snapshots, cohorts, segments, and metric marts
- A calibrated challenger scoresStage 3 · dbt Core
A calibrated challenger scores only the population supported by the evaluation contract
- React consumes governed publicStage 4 · Parquet
React consumes governed public aggregates while private routes remain local
- Cloudflare Pages serves theStage 5 · scikit-learn
Cloudflare Pages serves the zero-compute interface and Hugging Face serves the complete transformed Parquet release
Pivotal decisions
Technology choices recorded with their trade-offs.
These choices are derived from the project’s published decision record.
Use DuckDB, dbt Core, and partitioned Parquet within the published boundary
They preserve full analytical detail and governed SQL semantics on one machine without a managed warehouse bill.
- Constraints
- Static public delivery removes request-time compute and private data access but does not offer public subscriber drill-down
- The full dataset is distributed as Parquet rather than queried through a hosted warehouse, keeping recurring project cost at zero
- March-new subscribers are excluded from probability use even though they churn more often, because their calibration contract fails
- Alternatives
- A permanent cloud data warehouse
- Criteria
- Fit for the stated scope · Inspectable evidence · Explicit operating boundary
- Selected
- DuckDB, dbt Core, and partitioned Parquet
- Why
- They preserve full analytical detail and governed SQL semantics on one machine without a managed warehouse bill.
- Trade-off
- Local batch execution has no shared concurrency or provider-native monitoring.
- What supported it
- 442,211,685 Accepted rows published: All transformed analytical rows across membership, transaction, listening, and churn-label tables
- Revisit when
- Partition controls and one-thread DuckDB execution already process the full 442.2 million-row corpus locally
Use Static React on Cloudflare Pages within the published boundary
The public analytical product fits in governed extracts and needs no request-time private data access.
- Constraints
- Static public delivery removes request-time compute and private data access but does not offer public subscriber drill-down
- The full dataset is distributed as Parquet rather than queried through a hosted warehouse, keeping recurring project cost at zero
- March-new subscribers are excluded from probability use even though they churn more often, because their calibration contract fails
- Alternatives
- A server-backed public dashboard
- Criteria
- Fit for the stated scope · Inspectable evidence · Explicit operating boundary
- Selected
- Static React on Cloudflare Pages
- Why
- The public analytical product fits in governed extracts and needs no request-time private data access.
- Trade-off
- Public subscriber drill-down and arbitrary queries are deliberately unavailable.
- What supported it
- 5.57% Repeat-subscriber log-loss improvement: Selected challenger against the logistic baseline on the untouched March test window
- Revisit when
- A hosted query plane can be added only when a real latency or concurrency requirement justifies recurring compute
Use Hugging Face Parquet dataset within the published boundary
It publishes every transformed accepted row with file delivery and dataset browsing at zero recurring project cost.
- Constraints
- Static public delivery removes request-time compute and private data access but does not offer public subscriber drill-down
- The full dataset is distributed as Parquet rather than queried through a hosted warehouse, keeping recurring project cost at zero
- March-new subscribers are excluded from probability use even though they churn more often, because their calibration contract fails
- Alternatives
- Object storage plus a custom catalog or live query API
- Criteria
- Fit for the stated scope · Inspectable evidence · Explicit operating boundary
- Selected
- Hugging Face Parquet dataset
- Why
- It publishes every transformed accepted row with file delivery and dataset browsing at zero recurring project cost.
- Trade-off
- Users download or query Parquet rather than receiving an interactive hosted warehouse endpoint.
- What supported it
- 0.0166 Expected calibration error: Supported repeat-subscriber probability population only
- Revisit when
- Actual Fabric and Power BI engine evidence can reopen only under a separately approved budget
Use Calibrated histogram gradient boosting with a logistic baseline within the published boundary
The nonlinear challenger materially improves held-out log loss while calibration and lift remain measurable against a named simple comparator.
- Constraints
- Static public delivery removes request-time compute and private data access but does not offer public subscriber drill-down
- The full dataset is distributed as Parquet rather than queried through a hosted warehouse, keeping recurring project cost at zero
- March-new subscribers are excluded from probability use even though they churn more often, because their calibration contract fails
- Alternatives
- One model reported without a baseline or population gate
- Criteria
- Fit for the stated scope · Inspectable evidence · Explicit operating boundary
- Selected
- Calibrated histogram gradient boosting with a logistic baseline
- Why
- The nonlinear challenger materially improves held-out log loss while calibration and lift remain measurable against a named simple comparator.
- Trade-off
- Probability use is restricted to repeat subscribers and cannot cover the high-churn March-new group.
- What supported it
- 0 Maximum scenario formula error: Independent reconciliation across edge cases
- Revisit when
- Causal uplift modeling requires randomized intervention, delivery, acceptance, and outcome data that the source does not contain
Results and evaluation
Measured results within the disclosed evaluation boundary.
- Method
- Exact local and remote manifest reconciliation
- Meaning
- All transformed analytical rows across membership, transaction, listening, and churn-label tables
- Limitation
- Only two observed label windows exist, so calibration validation is cross-sectional rather than a third temporal period
Limitations and next steps
Useful evidence, bounded claims.
What this does not prove
- Only two observed label windows exist, so calibration validation is cross-sectional rather than a third temporal period
- March-new subscribers fail the probability-use boundary and are excluded from the scenario planner
- The source contains no randomized intervention, contact delivery, offer acceptance, or causal retention outcome
- Gross receipts are not recognized revenue, margin, lifetime value, or future receipts
- The source is a consumer music subscription and has no enterprise account, seat, contract, CSM, or support-case grain
- Actual Fabric runtime, Direct Lake behavior, and Power BI engine reconciliation remain unverified
What I would do next
- Partition controls and one-thread DuckDB execution already process the full 442.2 million-row corpus locally
- A hosted query plane can be added only when a real latency or concurrency requirement justifies recurring compute
- Actual Fabric and Power BI engine evidence can reopen only under a separately approved budget
- Causal uplift modeling requires randomized intervention, delivery, acceptance, and outcome data that the source does not contain
This public project uses a transformed consumer subscription dataset under the owner-approved source terms. It is not confidential enterprise data. Release-specific keys replace source identifiers, exact age and registration date are generalized, and private tokens, model artifacts, publication salt, and private subscriber views are excluded. Historical churn association does not establish treatment response.
Explore the rest of the work