3PL Client KPI Reports: AI Builds Weekly Reports From WMS Exports

A 3PL can build a weekly client KPI report by exporting the week's receipts, picks and shipments from the WMS, calculating dock-to-stock time, pick accuracy and on-time ship in a spreadsheet, and asking AI to draft the commentary. A person checks the numbers and the wording, then sends one page per client.

The calculation belongs in the spreadsheet, with definitions written down. The AI's job is to read a finished table and write three or four honest sentences about it, to flag what moved, and to suggest which chart fits. Most reporting problems come from fuzzy definitions, so the first hour goes there and not on the AI.

Follow me on Instagram@sagnikteaches

Define the three KPIs so a client cannot read them two ways

A KPI is only useful if you and the client agree on what is counted. Write each definition in one sentence and put it in the report footer. These are common starting definitions; replace them with whatever your contract says.

Connect on LinkedInSagnik Bhattacharya

Two questions decide whether each KPI means anything. For dock-to-stock: do weekends and holidays count, and does an inbound that waits for a client's quality release count as 3PL time? For pick accuracy: is an error caught at pack-out (before shipping) a miss, or only an error the customer reports? Counting only errors found after shipping flatters the number, so say which you use. The onboarding checklist tutorial covers writing these definitions into the SLA at the start.

Subscribe on YouTube@sagnikteaches

The pipeline: from three exports to one page

A weekly run has five steps and, once built, takes about 30 to 45 minutes for the whole client list.

1. Export last week's receipts, order lines and shipments from the WMS (the same three reports each time).
2. Paste them into a workbook template with one tab per export and a calculations tab.
3. Check row counts and date ranges against the export screen.
4. Read the KPI table, charts and flags that the template produces.
5. Give the AI the table only, and ask for commentary. Review, edit, send.

The workbook does the maths with formulas, so the same numbers appear every time. If your WMS can schedule these exports to a shared folder, even better; if not, a clerk does steps one and two in ten minutes.

Main example: one client's week, worked through

Illustrative figures for a fashion-accessories client at a small 3PL. Last week the WMS recorded:

• Receipts: 9 inbound deliveries; check-in to available times of 6, 8, 9, 10, 11, 14, 18, 22 and 52 hours.
• Order lines shipped: 3,420, of which 31 were flagged as pick errors (wrong item, wrong quantity or short pick).
• Orders due to ship: 1,180; shipped by the cut-off: 1,121.

The sums are short. Pick accuracy is (3,420 - 31) / 3,420 = 99.09%. On-time ship is 1,121 / 1,180 = 95.0%. Dock-to-stock needs more care: the average of the nine times is 150 / 9 = 16.7 hours, but the median is 11 hours, because one delivery took 52. Report both, or report the median with the longest named, since a mean hides what happened and a single number hides the outlier.

This client's contract says on-time should be at least 98% and pick accuracy at least 99.5%. So the headline picture is mixed: on-time is below target by three points and pick accuracy is just under target. That is the story the commentary must tell without softening it.

Prompt: commentary from the table, nothing else

You are writing the commentary section of a weekly 3PL client report.
Use ONLY the numbers in the table below. Do not invent causes.

Client targets: on-time ship 98%, pick accuracy 99.5%, dock-to-stock under 24 hours.

This week:  on-time 95.0% (1,121 of 1,180), pick accuracy 99.09% (31 errors
in 3,420 lines), dock-to-stock median 11 h, mean 16.7 h, longest 52 h.
Last four weeks, on-time: 97.4, 96.8, 97.9, 96.1. Pick accuracy: 99.6, 99.4, 99.7, 99.5.

Write: (1) one headline sentence, (2) up to three sentences on what moved versus the
last four weeks, (3) a list headed "Questions for the ops team" with anything
you cannot explain from the numbers. Plain language, no adjectives like
"great" or "concerning". If a movement is within normal range, say so.

Illustrative output the assistant might give: "On-time ship was 95.0% against a 98% target, the lowest of the last five weeks. Pick accuracy was 99.09%, below the 99.5% target and below each of the last four weeks. Dock-to-stock had a median of 11 hours, but one delivery took 52. Questions for the ops team: What delayed the 52-hour receipt? Were the 59 late orders concentrated on one day? Were the 31 pick errors concentrated in one zone or with one picker?" That is what you want: facts in, questions out, no invented cause. Where a draft claims "due to the staff shortage on Wednesday", delete it unless someone confirms it.

Chart rules that keep the report honest

Ask the AI to suggest charts, but apply these rules yourself. They are the difference between a report clients trust and one they argue with.

• Start the axis at zero for counts, not for percentages near 100. A pick accuracy line from 99.0% to 99.7% on a zero-based axis looks flat, which hides real misses. Use a tight axis for rates, but print the target as a horizontal line and label the axis ends.
• One chart per KPI, same shape every week. A line over the last 8 to 12 weeks with the target line. Clients learn to read it in a glance.
• Show the denominator. "95.0%" means little without "1,121 of 1,180". Put counts under each rate.
• Never smooth a bad week away. A four-week average beside the weekly line is fine; replacing the weekly line with it is not.
• Median beside mean for time measures. One long delay skews an average, so show the median and the longest.
• Same colours for the same meaning. Target line grey, this week in one strong colour, and no red or green as the only signal, since some readers cannot tell them apart.
• Small numbers get a caution. If a client had 12 orders last week, a single late order makes on-time 91.7%. Print the counts and add a note when the denominator is under about 50.

Illustrative before and after: the first draft of one chart showed on-time as a column chart with the axis running from 90% to 100%, so a drop from 97.4% to 95.0% looked like a cliff. The revised version is a line with the 98% target drawn across, the counts printed beneath, and the axis running from 85% to 100%. The drop is still visible, but proportionate.

Sample one-page layout

Keep the page short enough that a client reads it. A layout that works:

1. Header: client name, week ending date, the date and time the data was pulled.
2. Three KPI tiles: this week, target, last four weeks, and counts.
3. Three small charts: one per KPI, the last ten weeks each.
4. Commentary: the reviewed text, no more than 100 words.
5. Exceptions table: the longest receipt, the largest late orders and any repeated error SKUs, with order numbers.
6. Footer: the three definitions and any agreed exclusions.

The exceptions table is where trust is earned. "31 pick errors" is a claim; a list of six order numbers and the SKU mismatch is evidence.

Where the headline number hides the real story

An on-time figure that improved because the cut-off moved

A client asks for the cut-off to move from 2 pm to 4 pm, and on-time rises from 94% to 98% the following week without anything changing on the floor. The report should say the rule changed and show both measures for a few weeks, so the improvement is not mistaken for performance. Ask the AI to compare the weeks under both rules by recalculating from the same export, not by guessing.

A pick-accuracy dip traced to one SKU pair

Say 22 of 31 errors in a week involve two visually similar products stored side by side. Ask the AI to list the most frequent wrong-item pairs from the error export. The report says "22 of 31 errors involved SKU pair A and B, now stored apart", which is the first time the number leads to an action. For the root-cause side of this, see tracing pick errors back to their cause.

A dock-to-stock spike caused by a client's own hold

A 52-hour receipt turns out to have waited for the client's quality release. The honest treatment is to report the full elapsed time and annotate "includes 38 hours awaiting client release", or report a second measure that excludes client holds if the contract allows. Do not quietly drop it. An AI draft that says "the receipt was delayed by the 3PL" is wrong; the data shows elapsed time, not who caused it.

A tiny client where percentages mislead

A dental-supply client ships 35 orders a week. Two late orders mean 94.3% on-time, which would look alarming next to a large client's 97%. The report shows "33 of 35" first and the percentage second, and the commentary notes the small base. Rolling four-week totals, with the weekly figure beside them, give a fairer picture.

A month that rolls up weekly reports

At month end, do not average the weekly percentages. Sum the numerators and denominators across the weeks, then divide. If week one was 90 of 100 and week two was 198 of 200, the month is 288 of 300, or 96.0%, whereas the simple average of 90% and 99% gives 94.5% and understates the busy week. A formula in the template does this; ask the AI to explain it back to you in one sentence to check it understood the instruction.

Building the workbook: prompts for the formulas, and a check on each

You do not need a data team, only a workbook with four tabs: Receipts, Lines, Shipments and Report. Ask the AI for formulas one KPI at a time, give it the column names, and test each on a handful of rows. Here is the request for dock-to-stock:

Sheet "Receipts" has: client (A), receipt_id (B), checkin_datetime (C),
available_datetime (D). Write formulas to:
1. add a column E with elapsed hours = (D - C) * 24
2. return, for one client named in Report!B1 and a week starting in Report!B2,
   the median and the maximum of column E for rows in that week
Use only functions that work in both Excel and Google Sheets. Explain any
array formula in one sentence.

Illustrative reply: a helper column of =(D2-C2)*24, and a median such as =MEDIAN(FILTER(E:E, (A:A=Report!B1)*(C:C>=Report!B2)*(C:C<Report!B2+7))). Two cautions. First, check that the function exists in your spreadsheet version: Microsoft lists FILTER for Excel for Microsoft 365, Excel 2021 and Excel 2024, so Excel 2019 and older cannot run it; ask the assistant for an alternative if so. Second, test with a receipt you know: check-in at 08:00, available at 17:30 the same day, should return 9.5. If it returns 0.396, the column is a fraction of a day and the multiplier is missing.

For pick accuracy, ask for a count of error-flagged lines divided by a count of shipped lines per client and week, and test it against the 3,420 and 31 above. For on-time, ask for a formula that compares ship-confirm time with the cut-off rule held in a small settings table, one row per client. Keeping cut-offs in a table means a rule change is one edit, with a date, and not a hunt through formulas.

What the reviewer checks each week

A short checklist stops the bad habits creeping in. It takes five minutes per report once you are used to it:

1. Date range and row counts on the header match the WMS export screen.
2. Denominators look sensible against last week (a halving of order lines needs an explanation).
3. Each headline number agrees with the table, not just the commentary.
4. No cause is stated in the commentary that nobody has confirmed.
5. Any target missed has either an explanation or a question for ops.
6. The exceptions table lists order numbers that exist in the WMS.

An escalation rule so a bad week gets a call, not just a page

Decide in advance what turns a report into a conversation. A simple rule: two consecutive weeks below target on any KPI, or a single week more than five points below target, triggers a call from the account manager before the report goes out. Ask the AI to draft the call notes from the numbers, with three questions to ask the client and three facts to bring. Illustrative: on-time at 93% for the second week running; facts to bring are the late-order count, the days affected and the receipts that arrived late; questions are whether the client changed order release times or promotions that week. This keeps the report from becoming a surprise.

How the AI goes wrong in reporting, and the controls

• Invented causes. The prompt says "use only the numbers", and the reviewer deletes any cause nobody has confirmed.
• Arithmetic slips. Never let the AI compute the KPIs. Check two of the three figures by hand each week for the first month, then monthly.
• Trend overstatement. A movement from 99.4% to 99.1% on a few thousand lines is often noise. Ask the AI to say "within normal range" when the figure sits inside the range of the last 8 weeks, and give it that range.
• Tone drift. Reports begin neutral and drift into reassurance. Keep the instruction "no adjectives" and compare this week's wording with last week's.
• Data in the wrong tab. A pasted export one day short of the range quietly under-counts. Print the date range and row count on the report header.

The trap of reporting what is easy, not what matters

Teams often start with the KPIs the WMS prints by default, such as lines picked per hour, because they need no calculation. Clients rarely care about those. Ask each client in the first month which one number would make them call you, and add it to the template as a fourth tile if it is not one of the three. A seller running promotions may care about orders shipped within four hours of release; a wholesaler may care about short shipments. Keep the headline to three or four tiles, and push everything else into an appendix tab that you only share on request.

Illustrative: a client says every complaint they receive from shoppers is about delivery dates, so the 3PL adds "orders handed to the carrier on the day promised" as the fourth tile. Within a month the report shows that 14 of the 21 late carrier hand-overs followed a late replenishment, and the conversation moves from blame to forecasting.

Roll-out in four weeks

Do not launch to every client at once. In week one, build the template for your largest client and send the report internally only; compare its numbers with whatever they already receive. In week two, send it to that client alongside the old report. In week three, add two clients with different mixes, such as one with many small orders and one with pallet shipments, to expose template gaps. In week four, retire the old reports and move the rest in groups of three. Keep a change log of definitions so that a client who asks why a number changed gets a dated answer.

The same exports feed other analyses. A slotting review, as in the WMS pick-frequency tutorial, uses the order-line export you already pull for pick accuracy, so the extra work is small. If your clients also ask about invoices, the billing reconciliation tutorial shows the cost side of the same data.

Client data and sign-off

Weekly reports contain order counts and error lists, usually without consumer details. Keep it that way: remove names and addresses from anything you paste into an AI tool, and send only aggregate tables and order numbers. Use a business plan that does not train on your content, and check client contracts for any data-processing limits. Name one person as the approver for each report; a report that goes out with nobody having read it is how a wrong number reaches a client.

Questions before you send clients a weekly report

Should every client get the same report?

Use one template with the same three headline KPIs and definitions for everyone, then add a single client-specific section, such as a returns count for a fashion client. A common template keeps the work to minutes per client, and common definitions stop arguments about whose numbers are right.

Can AI write the commentary without a person checking it?

Not safely. The AI writes a draft from the numbers you give it, and a person reads it against the table before sending. Models sometimes state a cause as fact or describe a small movement as a trend, so keep a reviewer on every report.

How do we handle a week with bad numbers?

Report it plainly with the cause and the fix. Ask the AI to draft the explanation from your incident notes, then edit it yourself. Clients forgive a bad week with a clear cause far more readily than a good week that later turns out to have been massaged.

What if two clients define on-time differently?

Record each client's definition in the report footer and in your fee-and-SLA sheet. Calculate against each client's own rule, and never switch a definition part-way through a quarter without telling them.

Further reads

• 3PL Pricing Calculator: AI Quotes Storage, Pick and Pack Rates — A 3PL operator builds its own rate card and quote calculator: cost-based storage, pick and pack rates, a worked quote and a margin check.
• 3PL Quote: What 3PLs Charge and How to Compare Every Fee With AI — Typical 3PL fee ranges, plus a method to turn three differently structured quotes into one comparable monthly cost using AI and a spreadsheet.
• AI Tools for Warehousing: Which Suit a Small Warehouse or 3PL? — Stock counts, pick errors and slotting: when a spreadsheet and a chat assistant is enough and when a warehouse system's AI add-on pays.
• Questions to Ask a 3PL About Its AI and Automation: A Checklist — Grouped questions for a 3PL's AI and automation claims, what a good answer sounds like, red flags to walk away from, and a 30-day trial to test them.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: standard warehouse KPI definitions (dock-to-stock, pick accuracy, on-time shipping); exact definitions come from each client's contract.