How to Make a Product Returns Report with AI: A Full Guide
A product returns report breaks refunds down by SKU, category, and reason so you can tell a fit problem from a fulfillment problem. Build it in three moves. Separate merchandise that came back from orders that were cancelled. Join each case to the original order line. Then read the rate per SKU instead of for the whole store.
What a product returns report actually shows
A single store-wide rate is the least useful number in ecommerce. It moves when your product mix moves. It says nothing about what to do next.
A useful report answers three questions at once:
- Which SKUs are driving the return rate? A store at 15% can contain one line at 35% while everything else sits near 8%.
- Why are items coming back? Sizing, damage, and "not as described" point at three completely different teams.
- What did it cost? A returned unit is lost revenue plus outbound shipping plus processing. Sometimes the unit is no longer sellable.
The scale is worth keeping in view. The U.S. Census Bureau's Quarterly Retail E-Commerce Sales report was released August 18, 2026. It put second-quarter 2026 ecommerce sales at $340.2 billion, seasonally adjusted, up 3.8 percent from the first quarter. Ecommerce accounted for 17.1 percent of total retail sales that quarter. Refunds are a percentage of a very large number.
Not every refund is a return
This is the distinction most reports get wrong, and it points you at the wrong fix.
A return is a customer sending back merchandise they received. A cancellation refund is money going back because the order was never fulfilled. Both appear as negative amounts in the same payments export. They have almost nothing else in common.
Cancellations have their own clock, and in the United States they have a rule attached. The Federal Trade Commission's guide to the Mail, Internet, or Telephone Order Merchandise Rule covers shipment timing. If you make no shipment statement, it says "you must have a reasonable basis for believing that you can ship within 30 days." That is why direct marketers call it the 30-day Rule.
What happens next is specified too. Absent the customer's consent to a delay, the guide says you must refund "without being asked", and in full, for the unshipped merchandise. And if you can neither ship nor send the required notice in time, "you must cancel the order and make a prompt refund."
The consequence for your report is simple. A refund spike caused by a stockout is a fulfillment story with a compliance deadline behind it. A spike caused by sizing is a product-page story. Reporting them on one line tells you neither.
Scope note: the FTC guide is a compliance document about shipment timing, not a returns-policy standard. It is quoted here only to establish that cancellations are a distinct category with a distinct clock.
Reason codes are the actionable column
The return rate tells you how big the problem is. The reason code tells you whose problem it is. That is why a returns report without reason coverage stalls in the meeting.
| Reason code | What it usually points at | Who can act on it |
|---|---|---|
| Wrong size or fit | The size guide and the photography | Merchandising |
| Damaged in transit | Packaging or the carrier | Fulfillment |
| Not as described | The product page copy | Content |
| Changed mind | Expectations set before checkout | Marketing |
| Defective | A supplier or a single batch | Quality and sourcing |
| Blank or unmapped | The intake form itself | Operations |
The last row is not filler. A blank rate above roughly a fifth of cases means every other row in the table describes only part of the picture.
What you need before you start
Three exports, and they usually already exist:
- An RMA or returns export with order ID, SKU, quantity, refund amount, reason code, and the date received.
- The original order lines for the same period, so each case can be measured against what was actually sold.
- A refunds or payments export, if cancellations are recorded somewhere else.
If reason codes are missing or mostly blank, that gap is itself the first finding. Say so on the page rather than working around it.
How to build a returns report manually
The manual route works. It breaks in predictable places.
Option 1: Pivot the returns export on its own. Fast, and misleading. Counting cases without the matching order lines gives you volume, not rate. A SKU that sold 10,000 units and one that sold 90 can show the same count.
Option 2: Join to orders in a spreadsheet. This is the correct shape. You look up each order line to get units sold, then compute the rate per SKU. Then the problems start. SKUs get renamed between systems, so lookups silently come back blank. Items arrive later than the month of sale, so a naive monthly cut understates recent rates.
Option 3: Cut by cohort instead of by calendar month. Measure against the month the item was sold, not the month it came back. This is the accurate version and the slowest to maintain. Every refresh reopens the join.
The output is fine in all three cases. The maintenance is what costs you.
How to make a product returns report with AI
Step 1: Upload your returns and order files
Drop the RMA export, the order lines, and the refunds file into Powerdrill Bloom together. Excel, CSV, and PDF are all accepted, and the columns do not need to be aligned first.
Step 2: Describe the report in natural language
Say what you want rather than how to compute it. For example: "Join these on order ID and SKU. Separate cancellations from received merchandise. Give me the rate by SKU and by category, cut by month of sale, with a reason breakdown. Flag any SKU above 25%."
The agent works out the joins and tells you which rows it could not reconcile. That reconciliation list matters more than the arithmetic.
Step 3: Review the unmatched rows, then export
Read the unmatched and unreasoned rows first. They are where the number is most likely to be wrong. Then export as a sheet, an Office document, or slides. If this is monthly, set a scheduled task so the next version builds itself from fresh exports.
Common mistakes
Reporting the blended return rate only. A store-wide percentage can improve while every individual line gets worse, because volume shifted toward the cleaner category. Always show the split next to the total.
Cutting by the date the item came back. Merchandise arrives weeks after the sale. A report cut that way makes recent months look artificially clean, and delays your read on a new product.
Treating blank reason codes as zero. If a third of cases have no reason, your breakdown describes the two thirds that do. Show the coverage rate beside it.
Ignoring the resale outcome. An item that goes back on the shelf and one that gets written off cost very different amounts. If your data has a disposition field, use it.
Comparing yourself to a benchmark you cannot source. Category benchmarks circulate widely and are rarely traceable to a primary source. Your own trend, per SKU, is the comparison that supports a decision.
For the revenue side of the same export, see turning raw ecommerce orders into a sales trend report. For tooling options, our roundup of AI tools for ecommerce analytics covers the wider category.
Conclusion
This is not hard arithmetic. It is a joining problem wrapped around two judgment calls: what counts as merchandise coming back, and which month a case belongs to. Both are where reports go wrong.
Hand the joining to an agent. Spend your time on the reason codes and the unmatched rows.
Build your first returns report free from the exports you already have. For recurring retail work, the Excel AI assistant and the AI report generator handle the same files.
Census Bureau and FTC material quoted here was retrieved on September 2, 2026.
Frequently asked questions
What is a product returns report?
It is a report showing sent-back units and refund value broken out by SKU, category, and reason code, measured against units sold. It separates merchandise coming back from cancellations so the rate points at a specific cause.
How do you calculate return rate by SKU?
Divide sent-back units for that SKU by units sold for the same SKU, cut by month of sale. Doing it per SKU is the whole point, because a store-level figure hides the outliers.
What is the difference between a return and a refund?
A return means merchandise came back. A refund is money going back, which also happens when an order is cancelled or never shipped. Both appear as negative amounts in a payments export, so a report has to separate them explicitly.
Should the report be cut by sale date or by the date items come back?
By sale date. Merchandise arrives weeks after purchase, so the other cut flatters the most recent months and delays your read on a new product.
Can AI build this report from separate exports?
Yes. Upload the RMA file, the order lines, and the refunds export together, then describe the report in natural language. The joins, the flagged unmatched rows, and the per-SKU rate come out of one run.