A UK energy supplier's arrears path: the logic that decides, every cycle, which of hundreds of thousands of customers in debt receives which letter — and which must not receive one at all. Owning it means a mis-targeted letter is a regulatory escalation. It has stayed at zero.
British households owe their energy suppliers a record £4.79 billion (Ofgem, June 2026), with 1.5 million accounts on repayment plans. Every supplier runs a path for that money: bill, missed payment, reminder, letters of escalating seriousness, then a payment plan, a debt-recovery partner or — under strict rules — a prepayment meter. The regulator requires suppliers to identify customers who are struggling and offer support before chasing; since 2023 the industry has paid tens of millions of pounds in redress for getting that wrong.
So the question every cycle is not "who owes us money?" It is: which customers should receive which communication this week, and can we prove that nobody received one they should not have? The first half is analytics. The second half is why the job exists.
Six systems, none of which agree on what "a customer" is. Billing (balances, bill dates, tariffs). Payments (what arrived, when, by what method). Arrangements (repayment plans and whether they are being kept). A register of customers in vulnerable circumstances, which must be honoured absolutely. Disputes and complaints. And the communications log — every letter ever sent, which the path itself writes to. All of it on a lakehouse, in tables of 8.5 million rows and more, refreshed daily.
-- the spine: one row per account in arrears, with every reason it might be excluded (illustrative) WITH arrears AS ( SELECT a.account_id, b.balance_due, b.oldest_unpaid_bill_date, DATEDIFF(current_date(), b.oldest_unpaid_bill_date) AS days_in_arrears FROM billing.balances b JOIN customer.accounts a USING (account_id) WHERE b.balance_due > 0 ) SELECT ar.*, v.flag AS protected_flag, -- vulnerability register: absolute d.open_dispute_id, -- disputes: hold p.plan_status, -- arrangement: kept / broken / none c.last_letter_code, c.last_letter_date -- what we already sent FROM arrears ar LEFT JOIN care.register v ON v.account_id = ar.account_id LEFT JOIN service.disputes d ON d.account_id = ar.account_id AND d.status = 'open' LEFT JOIN collections.plans p ON p.account_id = ar.account_id LEFT JOIN comms.last_letter c ON c.account_id = ar.account_id;
Tracing the path produced the design principle everything else follows: exclusions run first, and they are absolute. A customer on the vulnerability register, in an open dispute, or keeping a repayment plan does not "score lower" for a letter — they are removed from consideration before scoring begins. The four scripts that decide letters are ordered so that the protective ones cannot be skipped by any later branch.
Cohorting arrears by age shows the same shape every supplier knows and few dashboards say out loud: money that is chased correctly in the first sixty days mostly comes back; money that reaches six months rarely does, and by then the customer is more likely to be someone who needs support than someone who needs a letter. That is why the path's value is not in letters sent — it is in sending the right one early and the wrong one never.
| GATE | THE RULE | WHY IT IS WHERE IT IS |
|---|---|---|
| G1 | On the vulnerability register → no chase letter, ever. Route to the support team. | Absolute regulatory duty. It runs first so no later rule can override it. |
| G2 | Open dispute → hold. Nothing until it is resolved. | Chasing a disputed amount is the classic complaint that becomes an Ombudsman case. |
| G3 | Repayment plan being kept → no letter. Broken plan → re-enter the path at the stage the balance and age imply. | A kept plan is the path working. A letter to that customer breaks trust and the plan. |
| G4 | A letter in the last 14 days → wait. | Minimum gaps are how the path stays proportionate. Two letters in a week reads as harassment. |
| G5 | Age and balance decide the stage. Under 14 days → nothing. Small balances never reach final notice. | Escalation must be proportionate to both time and amount, not either alone. |
| G6 | Never send a stage the account has already passed. | The communications log is a memory the path must read before it writes. |
The path runs as four scripts in a fixed order — extract the spine, exclude (gates one to four), assign (gates five and six), publish the letter file and the partner feeds. Between assign and publish sits the check that matters: the count of accounts in arrears, minus the count excluded, must equal the count assigned a letter, which must equal the rows in the file that leaves the building. If any pair disagrees, nothing is sent and a human looks.
# the invariants that guard the letter file — the run fails loudly if any is false (illustrative) assert letters.merge(register, on="account_id").empty, "a protected customer is in the letter file" assert letters.merge(open_disputes, on="account_id").empty, "a disputed account is in the letter file" assert (letters.days_since_last_letter >= 14).all(), "minimum gap breached" assert (letters.stage > letters.last_stage_sent).all(), "a stage would be sent twice" assert len(arrears) - len(excluded) == len(letters) == rows_in(letter_file), "tie-out failed — do not publish" # partner feeds: the agency's case list must match ours before it leaves assert partner_feed.account_id.isin(letters.query("stage == 'partner'").account_id).all()
The same discipline governs the feeds to external debt-recovery partners: their case lists are reconciled against the supplier's system of record on every run, so an account that paid, or entered a plan, or was flagged, drops out of the partner's queue the same day — not when someone remembers.
Around the path sit twenty-five recurring reports the client's teams depend on — billing, debt, engineer jobs, churn, payment methods, move-ins and move-outs — each built the same way: a requirement written down with the stakeholder, SQL developed and validated against the source, a scheduled refresh, and a check that tells someone when the numbers stop tying. The arrears path is simply the one where the check has legal consequences.
At scale, the leak is not the money you fail to chase. It is the letter you should never have sent. One wrong communication to a customer in difficulty costs more — in redress, in regulatory attention, in trust — than a thousand late ones. So the path is built backwards from that: exclusions first and absolute, a memory it reads before it writes, and a tie-out that would rather send nothing than send one wrong. Zero escalations is what that discipline looks like from the outside.
Running a collections process where a wrong communication is a regulatory event? This is The Read at its most serious — the standing check that your path stays provably right, cycle after cycle. It starts with a two-week TRACE of the path you already have.
Start with a TRACE → Case 03 → Case 01 →