Rank cancelled Direct Debit mandates by revenue at risk

Every weekday at 8am, check GoCardless for Direct Debit mandates that stopped being collectable in the last 24 hours, work out how much recurring revenue each lost payer represents, and post one prioritized digest to my finance Slack channel.

To find the cancellations, use GoCardless List Events filtered to the mandates resource type with the cancelled and expired actions, and a created_at[gt] filter set to 24 hours before the run. Use the events rather than only listing mandates by status, because a mandate's own created_at is the date it was originally set up, not the date it died, so a date filter on List Mandates would miss a mandate that was signed three years ago and cancelled last night. The event carries the cancellation timestamp and the underlying reason or cause, which I want in the digest. For each mandate id that comes back, call Get Mandate to confirm it is genuinely still cancelled or expired right now and to read the linked customer id. If the events lookup returns nothing at all, cross-check with List Mandates filtered by status cancelled and status expired before concluding it was a quiet day.

For each dead mandate, pull the payer with Get Customer, and their payment history with List Payments filtered to that customer id. Look at the payments that actually succeeded, not the failed or pending ones, since I want to know what this person has really paid me rather than what was attempted.

All GoCardless amounts are integers in the smallest currency unit, so pence or cents. Divide by 100 before showing any money figure in the digest or writing it to the sheet, and keep the currency code alongside it. Do not mix currencies into a single total; if payers span more than one currency, rank within each currency and label the amounts clearly.

Estimate the annual value at risk for each payer from their historical collection amount and cadence. Work out the typical successful collection amount and how regularly the collections came in, for example roughly monthly, quarterly, or annually, then project that forward across a year. A payer on 200 a month is a far bigger loss than a payer on 200 a year, so the cadence matters as much as the amount. Where the history is too thin or too irregular to project honestly, say so instead of inventing a confident number, and fall back to the total they have paid to date as the context figure. Also note how long they had been paying, since a long-standing payer walking away is worth flagging differently from someone who cancelled after one collection.

Rank the cancellations by estimated annual value at risk, highest first. Split them into the high value payers who justify a personal call from someone on my team, and the smaller ones where the sensible next step is just sending a re-signup link. Use the size of the projected annual loss to make that split, and mention the boundary you applied so I can argue with it.

Post the result as a single message to my finance channel using the Slack Bot Send a Message action. One message, not one per payer. Open with the headline number, being how many payers were lost and the total annual value at risk, then list each lost payer in ranked order with their name, the cancellation reason, the estimated annual value at risk, how long they had been paying, and a specific recommended next step. Group the personal-call payers above the re-signup-link ones so the expensive problems are read first.

Then append one row per cancelled mandate to my churn tracker spreadsheet using the Google Sheets Append Values action, so the trend builds up over time. Each row should carry the cancellation date, customer name, customer email, mandate id, cancellation reason, total paid to date, estimated annual value at risk, currency, and the recommended next step. Match the column order already in the sheet.

Two hard rules. Never reinstate a mandate and never create a new one, even though the Reinstate Mandate and Create Mandate actions exist. A customer who cancelled at their bank has to authorise a fresh Direct Debit themselves, so anything I do from this side will not hold. This workflow reports and recommends only, and every action it takes on GoCardless must be read-only. Second, if there were no cancellations in the window, stay quiet: do not post an empty digest, do not post a nothing-to-report message, and do not write anything to the spreadsheet.