Weekly invoices built from the data, not by hand

An automated weekly run builds every charge for a fulfilment and last mile provider from its own operational data, applies each customer's rates, discounts and surcharges, checks the result and delivers it by email, FTP or file upload.

2022Data Engineering & AutomationManaged Services

Illustration of a weekly invoice for a fictional customer, with charge lines in dollars for storage, receiving, pick and pack and last mile deliveries, a volume discount line highlighted and linked to an invoice register card showing the charge line count, checks passed, delivery channel and the six stages the invoice passed through from cycle close to delivery. A small delivery van runs a dashed route between stops above the invoice.

01 / THE ASK

The decision behind the brief.

The client runs fulfilment centre operations and last mile delivery in the United States, for customers in fashion, food, beauty, medicine and adventure gear. Every one of them is invoiced weekly. The invoices were put together by hand, which made the billing week long, repetitive and easy to get wrong. The client wanted the invoice to come out of its own operational data.

02 / WHAT WE FOUND

What the work revealed.

Pricing was the hard part, not the arithmetic. Each invoice is the product of the week's service volumes, that customer's rate card, volume-based discount tiers and accessorial surcharges, and no two customers are priced the same way. Rate lists and accessorial charges changed often, so a working method that was right last month was not reliably right this month.

The inputs also sat apart from each other. Someone had to collect figures from different systems before any charge could be computed, and that collection step was where most of the time went. Mistakes were found after the invoice had already gone out, which turned into customer queries, credit and debit notes and a second pass at the same week.

03 / WHAT WE BUILT

Collect the data. Apply the terms. Check it before it goes out.

Bring the sources together

The disaggregated operational and commercial data is integrated into one PostgreSQL store, so every figure a charge depends on is in one place at the point of billing.

Hold the commercial terms as data

Each customer's rate card, volume discount tiers and accessorial charges are held as versioned data rather than as knowledge held by the person preparing the invoice. A rate change is an update, not a rewrite of the method.

Compute every charge line

Python applies the terms to the week's volumes, line by line, including the volume-based discounts and the surcharges that used to be worked out by hand.

Illustration comparing manual invoicing, where a query leads to a credit note and a re-issue back into the working, with an automated run where computed, discount and surcharge lines are drawn as a grid of parcels and two lines are held for review, above a five-step flow from weekly data to rate matching, charge computation, validation and delivery. A shelving rack of parcels and a loaded hand trolley sit beside the manual panel.

INSIDE THE WORKFLOW

Manual billing found its errors after the invoice had gone out, as queries and credit notes. The run now applies the terms and checks the charge lines before anything is delivered.

Check the run before anything is sent

Error-checking rules run over the computed invoice. Lines that disagree with the terms or the source data are held for review rather than delivered. [CONFIRM: which checks exist and what happens to a line that fails one.]

Produce the invoice in the format the customer wants

The invoice is generated in each customer's preferred layout and file type, so the client is not reformatting output after the fact.

Deliver on schedule

A scheduler sends each invoice by email, FTP or the customer's own file upload route, without anyone having to send it.

04 / THE RESULT

The billing week runs itself.

The weekly invoice now comes out of the data the business already produces. Complex pricing, volume discounts and surcharges are applied the same way every week, whatever changed in the rate list, and the checks run before delivery rather than after a customer notices. Invoices go out on time, which matters to a business that is paid against them.

The work the billing team used to do by hand is gone, and with it most of the queries, credit notes and repeat passes that followed a wrong invoice.

05 / ENGINEERING DETAILUnder the hood

Weekly operational data
Match the rate card
Compute every charge
Check the run
Deliver the invoice

Data store. PostgreSQL holds the integrated operational and commercial data behind every charge.

Computation. Python and its data libraries apply rate cards, volume discount tiers and accessorial surcharges to the week's volumes.

Workflow. A cloud workflow keeps the data in step and runs the weekly cycle end to end.

Checks. Rule-based error checking runs over the computed invoice before delivery.

Output and delivery. Invoices are generated per customer in the agreed format and file type, then scheduled out by email, FTP or predefined file upload. [CONFIRM: hosting, scheduler and anything else we are comfortable naming.]

PostgreSQL data store
Python computation
Cloud workflow automation
Rule-based invoice checks
Per-customer invoice formats
Scheduled delivery by email, FTP or file upload

NEXT CASE / 11Putting a vineyard's own data on the map