TopN AnalyticsBuilding a Single Source of Truth for Driver Lifecycle Across a Multi-Product Cab Fleet
A leading pan-India fleet management company supplying cabs to driver-partners across multiple cities.
Where things stood
The company needed a consistent way to understand where each driver stood in their relationship with the business at any point — had they just joined, come back after a short break, returned after fully leaving, or dropped off entirely?
Without a shared definition, this classification risked being computed differently by every team that needed it: finance for settlement and deposit calculations, operations for win-back campaigns, and vendor/incentive teams to determine whether a referred driver's allocation counted as a genuine new conversion — a common and costly failure mode where different parts of the business quietly report inconsistent numbers about the same underlying driver population.
What we built
A MySQL operational fleet database feeds a PostgreSQL analytics warehouse, transformed via an embedded analytical engine for the window-function-heavy lifecycle logic.
A two-stage build — a base table refreshed every four hours, feeding a fully-enriched table refreshed roughly seven times daily — is consumed downstream by financial settlement, vehicle-utilization, and vendor-payout systems.
How it works
TopN Analytics built a single, centrally-computed source-of-truth table that classifies every allocation and deallocation event using one consistent rule set: a first-ever allocation is a 'New Join'; a same-day car swap is tagged separately from a genuine service gap; a driver returning within 60 days is a 'Rejoin,' while a driver returning after more than 60 days is a 'Resurrection' (win-back); and a driver's final, unreplaced deallocation is classified as 'Attrition.'
The table also tracks drivers holding more than one car concurrently, distinct from a standard swap, and has become the shared reference point other systems build on rather than each recomputing the same logic independently.
What we delivered and learned
The lifecycle categorization is anchored on a single, consistently-applied 60-day threshold separating a short-term 'Rejoin' from a full 'Resurrection' win-back, rather than varying by team or use case.
This table is directly read by at least four other analytics systems spanning collections, vehicle-leasing settlement, EV driver-utilization tracking, and vendor referral/incentive payout — confirming it functions as genuine shared infrastructure rather than a one-off report.
What it's built on
Want the full picture?
We're happy to walk through the details, numbers, and trade-offs directly.