A longitudinal NPS system for a 358-branch automotive network

Modernizing a national dealer NPS program running for over a decade: from a legacy Excel VBA engine to a Python pipeline — 17,000+ respondents per wave, hundreds of automated reports.

Challenge

A national automotive principal measures NPS and customer satisfaction across hundreds of dealers (Sales, General Repair, Body & Paint) every wave, year after year. In a program like this the thing you guard most is consistency: change a definition carelessly once and ten years of data can no longer be compared.

Approach

I steward this program from both sides. The legacy side: an Excel VBA + MySQL reporting engine producing Monthly & YTD reports per dealer, per region, and nationally — a system proven through years of operation. The modernization side: a staged migration to a Python/Pandas pipeline with 10-level hierarchy aggregation, open-text categorization, and an automated report generator.

The governing principle: modernize without breaking comparability — every new formula is cross-validated against the legacy engine’s output before replacing it.

Result

The program has run for over ten years at a scale of ~150,000 respondents per year at its peak, serving 358 branches in a single reporting hierarchy and producing hundreds of automated report files per cycle — consistent frontline measurement that anchors national dealer-network decisions.