A collections system built between three sets of requirements

The outside collection agency, the CFO, and the billing platform each needed something different from the same past-due accounts, and none of them talked to each other. I took requirements from all three and built the layer that sits between them. It has run daily since.

RoleSole builder and owner
StackAutomated data pulls · calculation engine · working ledger
DomainAR, collections, order-to-cash
StatusIn daily production use

At a glance

  • Problem: collections was Zoho's default reminder emails and an occasional call from a sales rep. There was no established process: past-due accounts were handled case by case, late fees went unassessed, and the business had no figure for what recovering bad debt cost it.
  • Built: a tracker that pulls invoice data on a daily cycle from the billing system and maintains information on every delinquent account through its life cycle in reaching payment, write-off, or referral to an outside collector. Then a second, separate automation that calculates late fees and assembles the evidence package the outside collectors need.
  • Result: balances over 90 days past due dropped more than 90% in the first year. What collecting cost became a number the business could look up.

Three sets of requirements, one system

Each party had non-negotiable requirements, expressed in their own vocabulary, and no two of them lined up. Working out where they conflicted took longer than building any of it.

The collection agency Needed accounts packaged their way before they'd take them: specific documentation, a defined referral threshold, and a clean handoff point so the same account wasn't being worked from both ends.
The CFO Needed the numbers to reconcile. Aging that matched the books, write-offs recorded so they could be defended, and late-fee assessments that an accountant could post without re-deriving them by hand.
The billing platform Set the hard constraints. What data it would expose, in what shape, and on what schedule. Everything upstream had to be designed around what it could actually deliver.

None of it arrived at once. The agency's requirements showed up when we started referring accounts, the CFO's when he wanted to see performance, and the platform's constraints were there the whole time. Working out where they conflicted and building the layer in between was the actual job.

Build one: the aging invoice tracker

  • Updates itself daily. Open receivables and aging pull from the billing platform on a daily cycle. Nobody exports anything, and the numbers are current when someone opens them.
  • Carries state on every invoice. Age, terms, balance, and the decisions made about it: suspended, cancelled, collect soon, refer out, write off. Records keep their history rather than disappearing when they resolve.
  • Follows each delinquent account through its whole life cycle to payment, write-off, or referral to an outside collector.
  • Keeps outcome ledgers by year. Payments recovered, accounts saved, referrals out, write-offs. Split by year, so recovery performance is comparable over time.

Build two: late fees and the collector evidence package

This is a separate system from the tracker. It does the work that otherwise gets done by hand, slowly, right when someone is already annoyed about the account.

  • Gathers the account's history from internal systems. It scans the company CRM for everything relevant to the account and pulls any signed agreements from the document system, then assembles it into a single summary for the outside collector.
  • Finds the messages that actually help them collect. Any email where the customer accepted the charges, acknowledged responsibility, or agreed to terms goes into the package. That is the evidence a collection agency needs and usually has to ask for weeks later, if it gets it at all.
  • Calculates interest-based late fees and collection fees and applies them to the qualifying invoices, then downloads each invoice with the fees applied.
  • Reverts every balance to its original figure once the documents are captured. The collector gets invoices showing what is owed with fees. The accountants open their system and find the numbers exactly where they left them.

That last step is what makes the rest of it usable. Applying fees is easy. Applying them, capturing the documents, and leaving no trace in the books takes a bit more thought.

What collecting actually costs

Recovering bad debt costs money, and most operations never put a figure on it. Agency fees, staff time, and the discount you take to settle. The tracking attaches cost and source to every recovery, so two questions have arithmetic answers. What did this dollar cost to get back? And where did the bad debt come from?

The second question is the one worth having. Once you can see which accounts, terms, and onboarding paths produce the bad debt, you can go fix those upstream instead of collecting the same money again next year.
>90%
reduction in balances over 90 days past due, first year
3 parties
agency, CFO, and billing platform requirements reconciled
Daily
automated refresh, no manual export

Tech

Billing platform integrationAutomated data pulls CRM data aggregation Interest, late-fee & collection-fee calculationAR & collections Order-to-cashRequirements gathering Process design
How it actually got built

It started as a report the company president pulled out of Zoho with a few past-due accounts marked on it, shared with the accountants and the CFO. He asked me to get the oldest balances under control. Everything that makes it a system, I built.

Then we started sending the hardest accounts to an outside agency. Preparing a file for them took a long time, and most of that time went into gathering information that already existed somewhere in our systems. That is where the late-fee and collections summary automation came from.

The CFO wanted performance insight after that: how much of the money collected was organic, how much came from outreach, how much came through the third party. Working with Claude, the lookups, balancing, updating, and visuals all became automated to some degree, with human oversight at the points that need a person.

What I'd change if I rebuilt it

I'd rebuild the tracker as a real application: an interactive front end to work in, with a database behind it instead of a sheet. That one is already written up as a development roadmap, hosting on a proper domain included. It spans a few modules today because it grew over two years, with pieces added as the process turned up things it needed.

I did build that version later. The Serial Inventory and Shipment Logger started with a proper web app front end, and its scope grew to cover automated invoice and subscription creation against rules Zoho does not support out of the box.