Subscriber Product Usage Dashboard
Subscribers who never open the product churn. This shows who is engaged, who is drifting, and how usage intensity is distributed — before the renewal date arrives.
- Role
- Requirements, analysis, and build
- Context
- RCS MediaGroup
- Period
- 2025 – 2026
- Stack
-
- Python
- FastAPI
- React
- Vite
- Tailwind
- BigQuery
- Warehouse read cached frame
- Single filter step applied once, at the entry point
- KPIs · trend · segments · distribution · table all derived from that one frame
- React dashboard whole page in one call
Objective
Retention work needs a usage view, not an acquisition view. A subscriber base looks healthy in aggregate right up to the renewal window; the signal that matters is how much of the base is actually using what it pays for, and how that is trending.
The team needed to answer that themselves, at any cut, without opening a query editor.
Approach
The dashboard is organised around a single idea: engagement is a distribution, not an average. So alongside the headline KPIs and the trend line, the primary views are segment breakdowns and an intensity distribution — because “average sessions per subscriber” hides the two groups you actually care about, the heavy users and the ones who have stopped showing up.
Everything is filterable, and every view derives from the same filtered frame. That sounds obvious and is worth stating: when segments, distributions and the detail table are each computed by their own query, they eventually disagree with one another, and the first time a user notices, the dashboard is finished.
Architecture
Warehouse read into a cached in-memory frame, one filter function applied at the entry point, then a set of pure computations — KPIs, trend, segments, distribution, intensity, detail table — hanging off that single filtered frame. A combined endpoint serves the whole page in one call; individual endpoints exist for partial refreshes. Authentication is token-based with hashed credentials, and a refresh endpoint forces a cache rebuild when upstream data lands early.
What I owned
The retention team’s brief, the backend, the aggregation logic and the React frontend. Deployment was data engineering’s.
Outcome
Usage questions stopped being ad-hoc queries. The distribution view in particular changed how the team talked about engagement — from a single average that always looked acceptable to a shape with a visible tail.