Case Study
From Manual to Automated: Building a Live Payment Analytics Report with Python and AI
Background
A SaaS company mid-migration to a new billing system needed to answer a critical question daily: is the new payment infrastructure performing as well as the old one? The team needed to compare new vs. returning customer counts, revenue per day, and payment failure rates across a pre/post launch window — and doing this manually in Excel every morning wasn't sustainable.
What Was Built
A Python script that anyone on the team could run by dropping a fresh Stripe export into a folder and typing one command. The output: a fully formatted multi-sheet Excel workbook with summary tables, daily breakdowns, failure rate analysis, a key accounts watchlist, and embedded charts — ready to share with leadership in under 30 seconds.
Where AI Came In
The script was built collaboratively with Claude. The human defined the business logic — what counts as a new customer, which days to exclude, how to structure the pre/post comparison — and Claude translated that into working Python. This covered the pandas data pipeline, classification logic, and the full openpyxl workbook with styling, charts, and conditional formatting. Iteration was fast: business rules changed, edge cases emerged, and the script was updated in real time through conversation rather than tickets or rework cycles.
Real Business Impact
Within days of the report going live, the team identified that over 30% of payments were failing on Stripe — a rate far above normal. The visibility the report provided prompted immediate discussion with the billing team and Stripe, leading to the discovery of a misconfiguration in the API integration between the new billing system and Stripe. The issue was identified and fixed quickly, recovering revenue that would otherwise have been silently lost with no easy way to detect it.
The Result
30–45 minutes of daily manual Excel work reduced to under 30 seconds. More importantly, the report surfaced a critical payment failure issue that manual processes would have caught far later — or missed entirely.
Key Takeaway
The human brought domain knowledge — the business rules, edge cases, and judgment calls. AI accelerated the translation of that knowledge into working code. Neither could have produced this alone at the same speed — and in this case, the speed mattered.