Payment Reminder Automation

The Client

A mid-market distribution company with a 12-person Accounts Receivable team, processing hundreds of invoices every month across multiple ERP instances.

Cash flow is the lifeblood of a distribution business, and this company's cash flow depended on one thing above all: getting overdue invoices in front of the right account managers, quickly and consistently. The team had grown and invoice volumes had climbed, but the process for chasing payment hadn't changed in years.

The Challenge

Every Monday, an AR analyst followed the same manual routine: run a SQL query, export the results to Excel, manually format and filter the data by account manager, then email each manager their list of overdue invoices.

It took four hours every week. It was error-prone, dependent on a single person, and couldn't scale — and if that analyst was on holiday, it simply didn't get done.

That last point was the real cost. It wasn't just four hours of a skilled person's week — it was a process with no safety net. When invoice volumes spiked, when an account manager changed, or when the analyst was away, the routine had to be stretched or re-learned. Every Monday morning, the team was left wondering whether the lists were complete, current, and correct.

Our Approach

We replaced the whole process with a fully automated pipeline on Microsoft Fabric:

1. Bronze — Raw AR data is ingested from the ERP every night.
2. Silver — The data is cleaned, joined, and aged: invoices matched to customers, currency converted, overdue flags applied.
3. Gold — A dimensional model is built: FactAR, DimCustomer, DimDate, DimAccountManager.
4. Semantic Model — Measures like OverdueAmount, DaysOverdue, and aging buckets are defined, with row-level security by account manager.
5. Power Automate — Every Monday at 8 AM, a flow queries the model, generates a per-manager PDF, and emails it out.

The key design choice was row-level security. Each manager's report is generated from a model that already restricts what they're allowed to see — so the automation is personalised and secure at the same time. There's no separate filtering step to get wrong, and no risk of one manager receiving another's customer list.

The Results

• 4 hrs/week → 0 — the process is now fully automated.
• Personalised — each manager sees only their own customers.
• Secure — everything stays inside the Microsoft ecosystem.
• Scalable — with a governed gold layer in place, the AR director's live Power BI dashboard was built in 30 minutes.

The automation didn't just remove four hours of work — it removed the uncertainty that came with it. The report is delivered before the team arrives, every single Monday, whether or not the analyst who used to own the process is in the office. And because the same gold layer now powers a live dashboard, the team's next question — "what else can we automate?" — already has somewhere to start.