case study — data & analytics engineering

Automated Billing Reconciliation
& Anomaly Detection

A rule-based reconciliation engine for high-volume logistics billing: it matches ~99,000 invoices and payments, flags the billing anomalies that quietly erode margin, and ranks every exception by € at risk. Includes an exploratory unsupervised machine-learning extension for anomaly detection.

SQL — window functions, CTEs Python · pandas Power BI SQLite

Synthetic dataset modelled on billing/logistics operations — no proprietary information used. Fully reproducible from a fixed seed.

Developed with an AI-assisted workflow: I defined the business logic, reconciliation rules and validation criteria, and used AI tools to accelerate the Python implementation.

99K+

transactions reconciled
(50,250 invoices · 48,779 payments)

99.7%

payment match rate

€214k

revenue at risk surfaced

2,767

exceptions ranked by impact

01 — The problem

Reconciliation is manual, slow, and leaks margin

A logistics operation issues tens of thousands of invoices a month. Matching them to incoming payments is a spreadsheet-driven process, so billing errors slip through: duplicate invoices, wrong rates, misapplied fuel surcharges, short payments, unapplied cash. Each erodes margin or inflates days-sales-outstanding — and at volume, nobody can review them all by hand.

What I built

An end-to-end, one-command pipeline

A synthetic-but-realistic dataset (500 customers, 12 months, ~3% of transactions seeded with six real-world error types as hidden ground truth), a SQL reconciliation core, an Isolation Forest anomaly layer, and a rigorous evaluation of both against that ground truth.

The six anomaly types

Injected at ~3%, held out as ground truth

Rate discrepancy · duplicate invoice · fuel-surcharge error · short/over payment · unapplied cash · overdue. Plus deliberately dirty data — mixed date formats, noisy customer keys, swapped currency labels — so the cleaning stage is genuine.

02 — Approach

Most of the logic lives in SQL; ML closes the gap

Cleaning, exact and fuzzy invoice-to-payment matching, duplicate detection via window functions, and rate-card revalidation are all expressed in SQL. Python handles data generation, the anomaly model, and evaluation.

01GeneratePython + Faker, seeded. Rate cards, terms, dirty data, hidden labels.

02CleanNormalise keys, fix currency labels, parse mixed date formats.

03MatchExact on reference, then fuzzy by customer + amount (window rank).

04RulesDuplicates, rate revalidation, short/over pay, unapplied cash.

05ML (exploratory)Isolation Forest scores each invoice for subtle anomalies.

06RankExceptions register: aging, severity, € at risk → Power BI.

Data model

Four reference/transaction tables + a hidden ground-truth table

03 — The result

Rules are precise; ML recovers what rules can't see

Because every anomaly was seeded, detection could be measured exactly. Three systems compared against the same ground truth:

Detection performance

Precision / recall / F1 vs. seeded ground truth

System	Precision	Recall	F1
Rules only	99.4%	83.3%	0.91
ML only	18.8%	19.0%	0.19
Rules + ML	53.2%	93.8%	0.68

Combined lifts recall 83% → 94%. Precision falls because ML adds candidates a reviewer triages in € order — a fuller net, not a cleaner one.

Recall by anomaly type

Where each layer earns its keep

Rules Machine learning

The insight. The rule engine hits 99% precision but is structurally blind to one whole error class — subtle fuel-surcharge mistakes sit under its fixed 2% threshold, giving 0% recall. The Isolation Forest, learning the normal shape of an invoice, recovers 63% of them. Machine learning here complements business rules — it doesn't replace them: rules give explainability and precision on known patterns, ML extends coverage to the unknown ones.

04 — Delivery

Reconciliation Control Tower

The pipeline feeds a two-page Power BI report. Executives see exposure and trend; operations drill into a ranked, filterable exception queue with a model-performance panel — the numbers below are the live pipeline output.

99.7%

Match rate

€214k

€ at risk

2,767

Open exceptions

~3%

Anomaly rate

Control Tower — Executive page: KPI cards, € at risk by anomaly type, monthly match-rate trend, and top 10 customers by € at risk — **Page 1 — Executive.** Exposure at a glance: match rate, € at risk, open exceptions and detection recall, with € at risk by anomaly type, the match-rate trend, and the top accounts driving exposure.

Control Tower — Operational page: slicers, exceptions register ranked by € at risk, anomaly type by detection method matrix, and a model-performance panel — **Page 2 — Operational.** The review queue: a filterable exceptions register ranked by € at risk, the anomaly-type × detection-method matrix, and a model-performance panel comparing rules, ML and the combined system — rare in a portfolio, and the point of the whole project.

05 — Under the hood

Tooling & reproducibility

SQL

Cleaning, exact + fuzzy matching, window-function duplicate detection, rate-card revalidation, exceptions register.

Python

pandas + Faker generator with a fixed seed; date parsing; orchestration in one script.

scikit-learn (exploratory)

Isolation Forest on per-invoice features: rate deviation, surcharge ratio, payment behaviour, peer z-scores.

Power BI

Two-page Control Tower over the exported exceptions, trend and KPI feeds.

One command, fully reproducible. A single run_pipeline.py regenerates the data, runs the SQL layer, scores the model, evaluates against ground truth, and exports the dashboard feeds — deterministically, in ~16 seconds.

Related case study. The same decision-logic mindset applied on the job: an internal application that automated billing-credit approvals, cutting turnaround from ~2 business days to 2–3 minutes — From Manual Reviews to Rules-Based Auto-Approval →

Discuss this project ← All projects

Automated Billing Reconciliation& Anomaly Detection