Build a Credit Control Dunning Letter Run

Chasing overdue invoices works best when it is regular and consistent: a polite reminder a week after the due date, a firmer letter a fortnight later, a final notice, and only then a referral to collections or a stop on the account. That sequence — dunning — is easy to describe and fiddly to run by hand from an aged debt listing. Each customer's level depends on their most overdue invoice and on which letter they last received; disputed invoices must not be chased; customers on an agreed payment plan should not get a final notice; a 12.50 balance is not worth a letter. This page builds the weekly run with pandas from an open receivables export and a letter history: it decides each customer's level, applies the exclusions and a minimum gap between letters, produces a workbook for credit control to review, generates a letter per customer listing the overdue invoices, and records the run so the next one escalates correctly. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

Three inputs: the open receivables listing (one row per unpaid invoice with due date, outstanding amount and a status column where disputes are marked), the customer list with contact email and a payment-plan flag, and the history of letters already sent. The run date is Friday 2 October 2026.

Step 1 — The policy, as data

Write the dunning policy down as a table: the days overdue at which each level applies, what it is called, and the rules around it:

import pandas as pd

RUN_DATE = pd.Timestamp("2026-10-02")
LEVELS = pd.DataFrame({"level": [1, 2, 3, 4], "min_days": [7, 21, 35, 50],
                       "name": ["Friendly reminder", "Second reminder", "Final notice", "Referral to collections"]})
MIN_GAP_DAYS = 14          # at least two weeks between letters
MIN_BALANCE = 50.00        # do not chase smaller overdue balances
MAX_LEVEL_ON_PLAN = 1      # customers on a payment plan only get friendly reminders
print(LEVELS.to_string(index=False))

Keeping the thresholds in one table makes the policy visible to the people who own it — credit control, not the developer — and lets it change without hunting through code. Level 4 is not a letter to the customer but an internal referral; it appears in the run so someone acts on it.

Step 2 — Overdue position per customer

items = pd.read_excel("ar_open_items.xlsx")
customers = pd.read_excel("customers.xlsx")
history = pd.read_excel("dunning_history.xlsx")

items["Days overdue"] = (RUN_DATE - items["Due date"]).dt.days
chaseable = items[(items["Days overdue"] > 0) & (items["Status"].fillna("") != "disputed")]
position = (chaseable.groupby(["Customer", "Name"], as_index=False)
            .agg(Overdue=("Outstanding", "sum"), Invoices=("Invoice", "count"), Oldest_days=("Days overdue", "max")))
last = history.sort_values("Sent").groupby("Customer").tail(1).set_index("Customer")
position = (position.merge(customers, on="Customer", how="left")
            .join(last.rename(columns={"Sent": "Last sent", "Level": "Last level"}), on="Customer"))
position["Last level"] = position["Last level"].fillna(0).astype(int)
print(position[["Customer", "Name", "Overdue", "Oldest_days", "Last level", "Last sent"]].to_string(index=False))

Disputed invoices are removed before anything else — chasing an invoice the customer has queried damages the relationship and rarely gets it paid — and invoices not yet due are not overdue at all. Each customer's position is the total overdue, the number of overdue invoices and the age of the oldest, joined to the last letter they received. Zenith's 2,250 disputed invoice is not counted, although their other invoice is.

Step 3 — Decide each customer's letter

The level a customer qualifies for comes from their oldest overdue invoice. What they actually get also depends on history and exclusions: at most one level above their last letter, not within two weeks of it, nothing for tiny balances, and only friendly reminders on a payment plan:

def decide(r):
    qualifies = int(LEVELS.loc[LEVELS["min_days"] <= r["Oldest_days"], "level"].max()) if r["Oldest_days"] >= LEVELS["min_days"].min() else 0
    if qualifies == 0:
        return 0, "not yet overdue enough"
    if r["Overdue"] < MIN_BALANCE:
        return 0, f"overdue balance below {MIN_BALANCE:.0f}"
    if pd.notna(r["Last sent"]) and (RUN_DATE - r["Last sent"]).days < MIN_GAP_DAYS:
        return 0, f"last letter {(RUN_DATE - r['Last sent']).days} days ago"
    level = min(qualifies, r["Last level"] + 1)
    if r["Payment plan"]:
        level = min(level, MAX_LEVEL_ON_PLAN)
        if level <= r["Last level"]:
            return 0, "on payment plan — no escalation"
    return level, LEVELS.set_index("level").loc[level, "name"]

position[["Level", "Action"]] = position.apply(decide, axis=1, result_type="expand")
print(position[["Name", "Overdue", "Oldest_days", "Last level", "Level", "Action"]].to_string(index=False))

Three letters go out this week. Oakridge's oldest invoice is 49 days overdue, which qualifies for a final notice, but their last letter was a friendly reminder, so they get the second reminder; Café Señor gets a first reminder; Zenith, at 63 days with a second reminder already sent, gets the final notice. Brightside is 84 days overdue but on a payment plan, Granite had a letter a week ago, Harbour Print's 12.50 is below the threshold and Northwind is only three days late. Never jumping more than one level above the last letter matters most for customers who were never chased: an account that slipped through for 80 days still gets a friendly reminder first, not a referral out of nowhere — the sequence is what makes the final notice credible. The two-week gap stops a customer who received a second reminder last Friday from getting a final notice today. Each decision carries its reason, including the ones that result in no letter, so a reviewer can see why a large overdue balance is not being chased this week.

Step 4 — Review workbook, letters and the log

from pathlib import Path
from string import Template

run = position[position["Level"] > 0].copy()
LETTER = Template("""Dear $name,

$opening

Invoice      Due date      Days overdue      Amount
$lines
Total overdue: $total

$closing

Credit Control
""")
TEXT = {1: ("Our records show the following invoices are now overdue. If you have already paid, please ignore this reminder.",
            "We would be grateful for payment at your earliest convenience."),
        2: ("We wrote to you recently about the invoices below, which remain unpaid.", "Please arrange payment within 7 days."),
        3: ("Despite previous reminders, the invoices below remain unpaid.",
            "Unless payment is received within 7 days we will refer the debt for collection and may suspend your account.")}
out = Path("letters"); out.mkdir(exist_ok=True)
for r in run.itertuples(index=False):
    if r.Level == 4:
        continue                                   # internal referral, no customer letter
    inv = chaseable[chaseable["Customer"] == r.Customer].sort_values("Due date")
    lines = "\n".join(f"{inv_no:<12} {due:%d %b %Y}   {days:>12}   {amount:>10,.2f}"
                      for inv_no, due, days, amount in inv[["Invoice", "Due date", "Days overdue", "Outstanding"]].itertuples(index=False))
    opening, closing = TEXT[r.Level]
    (out / f"{RUN_DATE:%Y-%m-%d}_{r.Customer}_L{r.Level}.txt").write_text(
        LETTER.substitute(name=r.Name, opening=opening, lines=lines, total=f"{r.Overdue:,.2f}", closing=closing))

with pd.ExcelWriter(f"dunning_run_{RUN_DATE:%Y-%m-%d}.xlsx", engine="openpyxl", date_format="dd mmm yyyy", datetime_format="dd mmm yyyy") as xw:
    run.to_excel(xw, sheet_name="Letters", index=False)
    position[position["Level"] == 0].to_excel(xw, sheet_name="No letter", index=False)
    chaseable.to_excel(xw, sheet_name="Overdue items", index=False)

log = pd.concat([history, run[["Customer"]].assign(Sent=RUN_DATE, Level=run["Level"].values)], ignore_index=True)
log.to_excel("dunning_history.xlsx", index=False)
print(sorted(p.name for p in out.iterdir()))
print((out / f"{RUN_DATE:%Y-%m-%d}_C003_L3.txt").read_text())

The review workbook separates customers getting a letter — with level, amount and reason — from those who are not, so credit control can check both lists before anything is sent. Letters are generated from a template per level with the customer's overdue invoices in a table; Zenith's final notice lists only the undisputed invoice. Plain text keeps the example short; the same data fills a Word letter in Generate Word Documents from Excel Rows with Python or an HTML email. Finally, the run is appended to the letter history — only after review and sending in a real process — which is what makes next week's run escalate correctly rather than starting everyone again at level 1.

Common pitfalls

• Chasing disputed invoices. Remove them before working out levels.
• Skipping levels. Escalate at most one level above the last letter sent.
• Letters every run. Enforce a minimum gap since the last letter.
• Final notices to customers on a payment plan. Cap their level.
• No history. Record what was sent, or every run starts from the beginning.

Conclusion

A dunning run is a small set of rules applied consistently: compute each customer's overdue position from undisputed invoices, find the level their oldest invoice qualifies for, cap it at one above their last letter, respect a minimum gap, a minimum balance and payment plans, and record every decision with its reason. Write a review workbook, generate a letter per customer from a template per level, and append the run to the letter history so the next run continues the sequence.

Frequently asked questions

How do I decide which dunning letter to send? Base the level on the days overdue of the customer's oldest undisputed invoice, but never more than one level above the last letter sent to them.

How do I stop customers getting reminders too often? Keep a history of letters sent and skip customers who received one within a minimum gap, such as 14 days.

Should disputed invoices be included in reminder letters? No. Remove invoices marked as disputed before calculating the overdue balance and level.

How do I generate a letter for each customer from Excel data? Group the overdue invoices by customer and fill a text, Word or HTML template per dunning level with the customer's name, invoice table and total.

Related

• Up one level: Building Finance and Operations Reports — finance reports from Python.
• Build an Accounts Receivable Aging Report — the aged debt picture behind the run.
• Send Each Recipient Their Own Excel Report — emailing each customer.
• Build a Supplier Payment Run Proposal — the payables counterpart.