Subscription churn and renewal analytics pipeline

Problem

Monthly recurring revenue, churn and renewal rates were tracked in manual spreadsheets. The billing system could not answer historical questions, because it overwrites each service's next due date as soon as a renewal invoice is generated, so the live data no longer shows what was due, renewed or lost in any past month.

Goal

A repeatable, automated pipeline that produces trustworthy monthly churn and renewal metrics, history included, and a secure dashboard that replaces the spreadsheets.

My role

Designer and developer of the pipeline, the data model, the historical reconstruction logic, the metrics calculator and the dashboard.

Solution

A daily batch pipeline extracts billing data, normalises it and loads it into a cloud data warehouse, then reconstructs point-in-time renewal events from invoice history and computes monthly revenue metrics. A server-rendered dashboard behind single sign-on presents the results, and a chat assistant answers ad hoc data questions.

How it works

Extract. A throttled, retrying, paginated client pulls products, add-ons, customers, invoices with their line items, and cancellation requests.

Normalise. Every billing cycle is converted to monthly recurring revenue, quantities are resolved from whichever field holds them, and products are classified by first-match rules in a configuration file, so categories change without code changes.

Load the normalised tables into the warehouse.

Reconstruct. For each recurring service, the billing periods on its invoice line items (parsed from free-text descriptions when structured dates are missing) become a month-by-month coverage map. For each month, the service's start date and cycle length decide whether a renewal was due (the first month counts as acquisition), and the coverage map and cancellation records then classify that month as renewed, lost (with category and reason) or pending.

Measure. A calculator rolls the events up into monthly starting, new, churned and renewed recurring revenue, revenue churn and renewal conversion, sliced by segment and product category.

Present. A multi-page, server-rendered dashboard with interactive charts rejects any request without a valid identity token from the single-sign-on proxy, verified in code rather than trusted from the network. A chat assistant answers data questions through a headless AI agent.

Technical challenges

Rebuilding history the source system destroys, by inferring renewals from billing periods and cycle alignment.

Normalising many billing cycles to one monthly figure.

Rate limits and pagination across large invoice histories.

Classification rules that people who are not developers can maintain.

Defence in depth on an internal dashboard: the identity token is checked in code even behind the access proxy.

Capabilities

• Daily automated extract and load
• Recurring-revenue normalisation across billing cycles
• Historical renewal reconstruction
• Churn and renewal-conversion metrics by segment and category
• Single-sign-on protected multi-page dashboard
• Conversational data questions

Technology

• Python
• Cloud data warehouse
• FastAPI with Jinja2 and Plotly.js
• Serverless container services and jobs
• Identity-token verification
• Identity-aware access proxy
• YAML rule configuration
• pytest

Impact

Replaced manual spreadsheets with a repeatable daily pipeline and made past renewal performance measurable at all. No metrics are claimed.

Skills demonstrated

• Data engineering and ETL
• SaaS revenue metrics
• Temporal data reconstruction
• Warehouse modelling
• API integration with throttling and retries
• Dashboard development
• Secure internal tooling