Sample deliverable

Reporting Trust Review, on a synthetic distributor

What the review delivers, shown on the reports of a synthetic US wholesale distributor: 9,713 orders in 2025, eight report queries, six KPIs traced back to the source tables, each definition written down and each difference explained. A client review covers up to 20 KPIs in the client's own Power BI, Excel or SQL reports.

Five of six KPIs needed a correction or a second name

The company and its reports are made up for this sample, and the errors were written into the report queries on purpose. Each reproduces an error DF1 found when it reviewed its own analysis of a public dataset. Every report runs without an error; the findings are about definitions: what a number counts, and whether its label says so. Each figure below comes from running the report query as written, and again under the corrected definition.

KPIAs the report computes itAfter the reviewFinding
Total revenue, 2025$15,072,148 in one report, $14,092,618 in anotherEach is right for what it counts; they differ by $979,531 (6.5%)Two measures under one name
Average order value$632.96$1,649.57Averaged over order lines, not orders. The per-order figure is 160.6% higher
On-time delivery rate72.8%89.2%1,504 orders delivered on the promised day were counted late
Active customers2,0591,454Ship-to locations counted as customers: overstated by 41.6%
Repeat customers, monthly share94.9%88.9%A first order counts as repeat if the customer orders again later
Cancellation rate3.46%3.46%Defined correctly. No change needed

Why the two revenue figures differ

Each step is computed from the source tables, order by order, and the steps add up to the second figure.

StepAmount
Monthly revenue view: line value of delivered orders placed in 2025$15,072,148
Line value of orders not marked delivered (shipped, invoiced, backordered, canceled)+$691,381
Line value of orders with no payment recorded-$1,758,886
Backordered orders: deposit paid, net of the full line value-$15,390
Billed orders paid by card: paid amount net of the line value+$114,495
Billed orders paid by ACH: paid amount net of the line value-$47,019
Payments on canceled orders that have no lines+$35,888
Revenue by payment method view: all payments on orders placed in 2025$14,092,618

The largest step is orders with no payment recorded: 1,084 orders, 755 of them delivered. 505 of those were placed in the 45 days before the extract, so they would be within terms if the terms are 45 days; the rest belong on the receivables aging. Every one of the 2,340 orders paid by card was paid about 3.0% above its line value, consistent with a card surcharge; 1,413 of 3,995 orders paid by ACH were paid about 2.0% below it, consistent with an early-payment discount. Both causes are for the receivables team to confirm. The data shows the pattern, not the policy behind it.

Definition, report query and finding for each KPI

In a client review each entry also names an owner, the person who decides what the KPI means. This sample has no client, so the owner is left open.

Revenue

Definition after the review
Line value (quantity times unit price, plus freight) of delivered orders, by order month. Cash collected is a separate measure with its own name.
Finding
Two reports are both labeled "total revenue". One sums the lines of delivered orders ($15,072,148); the other sums every payment on orders placed in the year ($14,092,618). The first is revenue; the second is cash collected, and it is lower mainly because 755 delivered orders had no payment recorded when the extract was taken. The reconciliation above accounts for every dollar.
Report as written
SELECT strftime('%Y-%m', o.order_date) AS month,
       SUM(l.quantity * l.unit_price + l.freight) AS total_revenue
FROM orders o
JOIN order_lines l ON l.order_id = o.order_id
WHERE o.status = 'delivered'
  AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
GROUP BY month
ORDER BY month;
SELECT p.method,
       SUM(p.amount) AS total_revenue
FROM payments p
JOIN orders o ON o.order_id = p.order_id
WHERE o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
GROUP BY p.method;

Average order value

Definition after the review
Line value summed per order, then averaged over delivered orders.
Finding
The report takes the average over order lines. An order has 2.61 lines on average, so the reported $632.96 is the value of a line, not an order: $1,649.57. Because lines per order differ by state, the state ranking changes as well: 24 of 25 states move.
Report as written
SELECT AVG(l.quantity * l.unit_price + l.freight) AS avg_order_value
FROM orders o
JOIN order_lines l ON l.order_id = o.order_id
WHERE o.status = 'delivered'
  AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01';
SELECT a.state,
       COUNT(DISTINCT o.order_id) AS orders,
       AVG(l.quantity * l.unit_price + l.freight) AS avg_order_value
FROM orders o
JOIN ship_to s ON s.ship_to_id = o.ship_to_id
JOIN accounts a ON a.account_id = s.account_id
JOIN order_lines l ON l.order_id = o.order_id
WHERE o.status = 'delivered'
  AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
GROUP BY a.state
HAVING COUNT(DISTINCT o.order_id) >= 100
ORDER BY avg_order_value DESC;

On-time delivery rate

Definition after the review
Delivered orders whose delivery day is on or before the promised day, as a share of delivered orders.
Finding
The promised date is stored as a date and the delivery as a date and time. Compared as text, a delivery at 10:00 on the promised day sorts after the promised date, so it counts as late. 1,504 orders are affected. 5 delivered orders have no delivery date in the extract; both definitions count them as not on time, and they should be fixed at the source.
Report as written
SELECT COUNT(*) AS delivered_orders,
       SUM(CASE WHEN o.delivered_at <= o.promised_date THEN 1 ELSE 0 END) AS on_time
FROM orders o
WHERE o.status = 'delivered'
  AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01';

Active customers

Definition after the review
Distinct accounts (account_id) with a delivered order in the year. A ship-to location is a delivery address of an account.
Finding
The report counts distinct ship-to locations: 2,059. 516 of the 1,454 active accounts received deliveries at more than one location, so every customer-based ratio built on this count (revenue per customer, share of new customers) is understated or overstated with it.
Report as written
SELECT COUNT(DISTINCT o.ship_to_id) AS active_customers
FROM orders o
WHERE o.status = 'delivered'
  AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01';

Repeat customers

Definition after the review
Customers who order in a month and placed an order before that month, as a share of customers ordering in the month.
Finding
The report flags an account as repeat if it has more than one order at any time, including orders placed after the month. A new customer whose first order is in March and second in July counts as repeat in March. Across the 12 months, 5,502 customer-months are flagged where 5,156 had ordered before.
Report as written
SELECT strftime('%Y-%m', o.order_date) AS month,
       COUNT(DISTINCT s.account_id) AS customers,
       COUNT(DISTINCT CASE WHEN r.account_id IS NOT NULL THEN s.account_id END) AS repeat_customers
FROM orders o
JOIN ship_to s ON s.ship_to_id = o.ship_to_id
LEFT JOIN (
    SELECT s2.account_id
    FROM orders o2
    JOIN ship_to s2 ON s2.ship_to_id = o2.ship_to_id
    GROUP BY s2.account_id
    HAVING COUNT(*) > 1
) r ON r.account_id = s.account_id
WHERE o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
GROUP BY month
ORDER BY month;

Cancellation rate

Definition after the review
Canceled orders as a share of orders placed, counted from the orders table.
Finding
Defined correctly. 158 canceled orders have had their lines deleted; a report built on the line table would miss them and show 1.86% instead of 3.46%. This report counts from the orders table, so no change is needed. The review records it so the definition is not changed later.
Report as written
SELECT AVG(CASE WHEN o.status = 'canceled' THEN 1.0 ELSE 0.0 END) AS cancellation_rate
FROM orders o
WHERE o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01';

Average order value by state, before and after

The review proves one fix on a copy of the model. Here it is order value: the same ranking, recomputed per order for states with at least 100 delivered orders. AZ enters the ten highest states and OR leaves. A territory plan built on the reported ranking would point at the wrong states.

StateDelivered ordersLines per orderPer line (reported)Per orderRank beforeRank after
TN2262.70$798.93$2,159.9311
CA1,3783.04$680.07$2,064.8942
LA1043.03$679.99$2,059.5953
SC1822.60$762.80$1,982.4524
VA3002.84$667.28$1,897.3175
NY7262.93$643.77$1,886.98106
PA3792.88$649.33$1,870.8997
AZ2363.00$608.46$1,822.79128
GA3972.40$697.76$1,671.4639
TX9142.47$653.96$1,617.72810
OR1072.40$670.79$1,611.15611
CO1753.09$508.28$1,568.412512
MI2132.62$588.01$1,537.661713
KY1942.55$595.92$1,517.441614
OH4712.43$625.20$1,517.191115
IL4402.44$602.21$1,467.191516
WA3282.40$580.69$1,395.071817
NC3282.55$545.58$1,393.892218
NJ2722.53$545.55$1,381.912319
MD1432.29$602.53$1,377.811420
MO1552.23$604.29$1,348.921321
FL7762.17$554.93$1,202.112122
MN1032.09$575.73$1,201.761923
IN2192.11$562.17$1,183.382024
MA2061.97$532.35$1,049.202425

What to change, in order

  1. Rename the two revenue measures

    "Revenue" for the line value of delivered orders and "Cash collected" for payments, with the reconciliation kept as a standing check. Effect: the $979,531 gap stops reading as an error, and the reconciliation explains it every month. Effort: small.

  2. Compute order value per order

    Sum the lines of each order before averaging. Effect: average order value rises from $632.96 to $1,649.57, and the state ranking changes, as proven above. Effort: small.

  3. Count accounts, not ship-to locations

    Join orders to accounts through ship-to. Effect: active customers fall from 2,059 to 1,454. Effort: small.

  4. Compare delivery days, not timestamps

    Cast the delivery time to a date before the comparison. Effect: the on-time rate rises from 72.8% to 89.2%. Effort: small.

  5. Count repeat customers from earlier orders only

    Flag an account as repeat in a month only when it ordered before that month. Effect: the monthly repeat share falls from 94.9% to 88.9%. Effort: medium.

  6. Decide how to report payments on 80 canceled orders

    $35,888 of payments sit on canceled orders that have no lines, so they appear in cash collected and in no revenue report. Whether they are refunded, credited or kept is a decision for the controller, not a definition. Needs a decision.

What this sample is and is not

  • It is a sample, not client work. The company is synthetic: 2,600 accounts, 4,369 ship-to locations, 17,804 orders, 45,827 order lines and 16,870 payments, generated from a fixed seed so that every run gives the same tables. No real company or person is described.
  • The eight report queries were written for the sample. They reproduce errors of the kind DF1 found when it reviewed its own SQL analysis of a public marketplace dataset: two measures under one name, an average over the wrong grain, a timestamp compared with a date, a repeat flag that looks ahead, and locations counted as customers.
  • Each query ran as written against the tables. The review computed every KPI a second time, under the report's definition and under the corrected one; the run stops if its reproduction of a report and the report itself differ by more than a rounding cent.
  • It covers six KPIs. The package covers up to 20, in Power BI, Excel or SQL, and ends with a readout with the people who use the reports. Figures generated on 2026-11-15.

The same review, on your reports.

$4,900 fixed, two weeks. Tell us what you would want checked first.