A leading debt-collections technology platform billed 300+ lenders across SMS, WhatsApp, IVR, email and legal notices — every invoice computed by hand from six fragmented databases. We built a Redshift billing data mart and automated the whole thing.
As the client grew, the finance team hit an unsustainable bottleneck. Each client ran a distinct pricing structure, and every month meant manually merging pricing with usage from disparate source systems — slow, error-prone, and impossible to scale.
Hours of manual cost computation every month across 300+ clients, each with unique pricing and usage patterns.
Slab-based and count-based tiers with effective date ranges per client, plus monthly license fees — non-trivial to automate.
Six-plus separate databases for SMS, WhatsApp, IVR, email and notices, with no unified view of cost or usage.
Finance and business teams couldn't independently slice, drill down, or compare costs and usage across clients and channels.
Every new channel or pricing tier meant reworking a manual process — growth made the problem worse, not better.
Multi-file loan allocations, DPD bucket movements and campaign attribution layered extra complexity onto every calculation.
An end-to-end platform on the client's existing AWS estate, built on three pillars: a purpose-built Redshift data mart, automated ETL, and pre-computed, dashboard-ready metrics.
Three denormalized Redshift tables — Usage, Pricing and Cost — capturing day-level metrics across every channel, per client, built for query speed and minimal joins.
MWAA-orchestrated EMR Spark pipelines extract from six sources, apply each client's pricing logic, and compute cost metrics on a daily schedule — handling multi-file allocations and DPD transitions.
Pre-computed KPIs across Cost, Usage and Gross Margin — structured around six dimensions and ready to plug into QuickSight or any BI tool to publish dashboards rapidly.
Dimensions: Time (daily / weekly / monthly) · Module · Client · Loan Type · Region · DPD Bucket
| Before | After |
|---|---|
| Manual monthly billing calculations | Fully automated daily cost computation |
| Fragmented data across 6+ systems | Unified billing data mart in Redshift |
| No structured cost or usage metrics | Pre-computed, dashboard-ready KPIs |
| Weeks to onboard new pricing tiers | Extensible schema for rapid additions |
| No gross margin visibility | Margin metrics ready for instant visualization |
If complex, per-client pricing is eating your finance team's month, we can build the same kind of automated data mart for you. Let's scope it.
Book a consultation →