Worked case: from sales table to finding

The retailer must decide whether to change August campaign allocation. We have an order export containing:

• order_id;
• completed_at;
• region;
• channel;
• gross_sales;
• refund_amount;
• status;
• is_test.

One row should represent one order. The available data can describe sales outcomes, but it contains neither campaign spend nor customer exposure. Therefore it cannot calculate return on ad spend or prove that a campaign caused a change.

We create this question contract:

For completed, non-test orders, how did weekly net revenue and order count in West change during the first six complete weeks of June–July compared with the same weekdays in the previous six weeks? Compare West with other regions, and determine whether the apparent decline reflects order volume, net value per order, refunds, or incomplete data.

Net revenue is defined as gross_sales - refund_amount. The deliverable is a weekly audit table, two chart recommendations, and a decision note that does not claim causality.

The export contains 48,216 rows. Profiling reveals:

• 47,998 distinct order IDs;
• 218 repeated order IDs;
• 31 blank regions;
• region labels West, west, W, and Western;
• 72 blank refund amounts;
• 14 negative gross-sales values;
• timestamps in ISO format with timezone offsets;
• 121 test orders;
• the final week contains only four days.

These findings immediately change the work. We cannot sum the file as provided, and we should not compare the partial final week with full weeks.

AI is asked to draft profiling code and an anomaly table, but it is not allowed to repair values. We manually inspect samples from each anomaly and consult the export owner.

The source owner confirms that repeated IDs are updated records and that the latest completed_at does not identify the latest update. A separate exported_at field, initially omitted from the inventory, does. We retain the row with the latest exported_at for each order ID and quarantine three tied conflicts.

The approved region mapping combines west, W, and Western into West while preserving the raw label. Blank regions remain Unknown; they are not assigned by store or channel because no authoritative mapping exists.

Blank refund amounts mean no refund in this export, according to source documentation, so they become zero. Negative gross sales are adjustment records that should not have order IDs, so they are removed from the order analysis and reported separately. Test orders and non-completed statuses are excluded under the question contract.

The incomplete final week is excluded from the primary comparison. After cleaning, 46,903 eligible orders remain. The cleaning log records every affected count.

The first output is not a chart. It is a weekly table with:

| week | region | orders | gross sales | refunds | net revenue | net per order | | --- | --- | ---: | ---: | ---: | ---: | ---: | | May 4 | West | 1,020 | $91,800 | $4,590 | $87,210 | $85.50 | | June 15 | West | 940 | $86,480 | $6,920 | $79,560 | $84.64 |

The complete table includes all twelve weeks and regions. We reconcile its eligible order count to the cleaned table and verify that regional net revenue sums to the overall total.

For each week, net revenue is independently reproduced in a spreadsheet pivot. We manually check ten selected orders, including one refund, one mapped region, one duplicate update, and one boundary timestamp. The spreadsheet and reviewed script agree.

Across the six-week comparison windows, West net revenue fell 8.4%. That statement alone is not useful enough. The components show:

• eligible order count fell 6.9%;
• net revenue per order fell 1.6%;
• refund rate rose from 4.8% to 6.1%;
• gross value per order was nearly stable;
• other regions’ net revenue fell 1.2% overall;
• West’s decline appears across five of six weeks rather than one outlier week.

We check the median order value because a few large orders can move the mean. The median is stable, reinforcing that order volume and refunds, not a broad order-value collapse, drive most of the change.

We also compare channels. Store orders fell sharply while web orders were stable. That is a diagnostic observation, not an explanation. Store closures, inventory, local demand, tracking, or other changes could be responsible.

Before writing the conclusion, we try to disprove it.

First, we include the partial final week using a daily average rather than a weekly total. The direction remains negative, but the estimated magnitude changes, so the primary report retains only complete weeks.

Second, we calculate results without converting blank refunds to zero. Net revenue becomes missing for those orders, but because only 72 rows are affected, the direction is unchanged. We document the source-backed zero rule.

Third, we use gross revenue instead of net revenue. The decline is smaller, which confirms that refunds matter.

Fourth, we inspect the unknown-region records. Their total is too small to explain the result.

Finally, we compare with the same period one year earlier. The history suggests some seasonal softening in West, but category and store definitions changed, so this comparison is supporting context rather than a clean baseline.

The final note reads:

West net revenue was 8.4% lower in the six complete weeks after June 14 than in the preceding matched six weeks. Approximately four-fifths of the dollar decline is associated with fewer completed orders; a higher refund rate explains most of the remainder. The decline appears in five of six weeks and is concentrated in the store channel. The order table does not contain campaign exposure or spend, so it cannot determine whether the campaign caused the change or whether cutting campaign allocation would help. Before reallocating budget, investigate store availability and refund reasons and join approved campaign data at a defined grain.

This note gives magnitude, decomposition, stability, scope, and limits. It answers what the table supports and refuses the causal leap the original request invited.

The analysis package contains the question contract, raw-file version, data dictionary, profiling output, cleaning log, region mapping, script, audit table, manual-check record, charts, and decision note. Each output includes the extraction date and comparison window.

If the export is refreshed, the same pipeline can rerun. Any changed source definition requires a new version rather than silently overwriting the result.