How to Create a Returns and Refunds Report: A Full Guide
A store reports a 12% return rate. Nothing physically came back for a third of it.
That is not an error in the data. It is what happens when a refund is recorded as a return, which is exactly how most platforms count.
The distinction sounds pedantic until someone in operations plans warehouse capacity from your number.
This guide covers what belongs in a returns and refunds report, and why the two words describe different events. It then covers the date problem that quietly breaks monthly comparisons, and how to build the report from an export.
What a returns and refunds report has to contain
Five things belong on the same view, and the rate is only one of them.
The refunded value, the count of refunded orders, the period, the denominator you divided by, and the split between full and partial refunds.
Add the reason codes if your platform captures them. A rate tells you the size of the problem, and reasons tell you which one you have.
Leave out shipping and tax at first. Both are usually tracked separately, and mixing them in makes the number impossible to reconcile later.
Returns and refunds are not the same thing
Platform documentation is unusually clear on this, and it is worth reading the exact wording.
WooCommerce's analytics documentation separates the two directly. It states that "refund describes the transaction that sends money back to a customer."
The other half of that definition is the important one. The returns metric records refunded value "whether the refund is full or partial and whether or not goods were physically returned."
So a goodwill credit for a damaged item counts in returns even though nothing shipped back. A price adjustment does too.
That single sentence is why a returns figure taken from commerce analytics cannot be handed to a warehouse team as a physical return forecast.
Shipping and tax are carved out as well. Refunded shipping charges and refunded taxes are not included in the refunds number. They appear as negative values in the shipping and tax figures instead.
The date problem
This one changes your trend line rather than a single cell, which makes it worse.
WooCommerce records refunds "as a negative number on the date that the return occurred (not the date the order was placed)."
Think about what that does to a monthly comparison. A March refund against a February order lands in March, and February's revenue is never restated.
Both months are now slightly wrong in opposite directions. February looks better than it was, and March carries a cost it did not create.
There are two defensible fixes, and you have to pick one in writing.
Report on refund date and label the report as a cash view. It matches your bank and your platform, and it will never match cohort analysis.
Or reassign each refund to its original order date. That gives a true picture per selling period, and it means last month's number changes after you publish it.
Neither is wrong. Publishing without saying which one you used is.
Choosing the denominator
A return rate needs a divisor, and there are three common ones that produce three different numbers.
| Denominator | What the rate means | Best for |
|---|---|---|
| Orders in the period | Share of orders that saw money back | Customer service load |
| Units shipped | Share of items coming back | Warehouse and restocking |
| Net sales value | Share of revenue reversed | Finance and margin |
Pick by audience. A finance review wants the value version, and an operations review wants units.
Then be careful with the sales figures around it, because those are defined too. WooCommerce gives gross sales as price times quantity excluding refunds, coupons, taxes and shipping, and net sales as gross sales minus returns and coupons.
Average order value there is net sales divided by orders. So refunds already sit inside the AOV you may be quoting elsewhere.
Our guide to turning raw e-commerce orders into a sales trend report covers the revenue side of the same export.
How to do it manually
Option 1: Two totals and one division
Filter the export to refund records for the period, then total the refunded value with SUMIFS.
Count the affected orders with COUNTIFS, and deduplicate order identifiers first if a single order can carry several refunds.
Divide once at the end. Keeping both totals visible is what lets someone else check your work.
The ceiling arrives fast. You get one rate for one period, with no view of which products or reasons drove it.
Option 2: One row per refund
Build a table with one row per refund. Columns for order date, refund date, days between, refunded value, full or partial, and reason.
Compute the day gap by subtracting the earlier date from the later one. Microsoft's guidance on the DATEDIF function warns that it "may calculate incorrect results under certain scenarios."
Now the report can be sliced. By product, by category, by channel, by reason, or by refund lag.
The lag column is the underrated one. A cluster of refunds 40 days after purchase points at durability, and a cluster at four days points at sizing or description.
The limit is volume and joins. Matching refunds to order lines and product data is past the point where formulas stay pleasant.
Option 3: A definitions tab
Record which date you report on, the denominator, and whether shipping and tax are included.
Then record the exclusions. Cancelled orders that never shipped, test transactions, and chargebacks each need a stated treatment.
Chargebacks are the one people forget. Money leaves, no return is recorded, and the two systems disagree forever.
The limitation is that writing a rule down does not apply it. Someone rebuilds the same filters next month.
The shared ceiling. All three assume the export carries both dates. If it only has the refund date, the cohort view is not recoverable from that file.
Where the manual route slows down
The first report takes a morning. The fourth takes longer, because three things drifted.
Partial refunds multiply. One order with three partial refunds becomes three rows, and an order count built by counting rows is now wrong.
Product catalogues change. A renamed SKU splits one product's history into two, and the worst offender disappears from the top of your list.
Then the definitions move quietly. Somebody includes refunded shipping this month because it seemed more complete, and the trend breaks.
There is a fourth cost that only appears in a meeting. When someone asks why finance has a different number, the answer is a date convention that lives in a tab you did not bring.
The tooling side has its own roundup, in AI tools for e-commerce analytics.
How to build it with Powerdrill Bloom
Step 1: Upload your order and refund exports
Upload the orders file and the refunds file together. Powerdrill Bloom profiles the columns on arrival, so missing refund dates, duplicate order identifiers, and inconsistent SKU values surface before any rate is computed.
Step 2: Describe the report in natural language
State the rules rather than building them. Name the date you report on, the denominator, whether shipping and tax are included, and what to exclude.
Then ask the questions that catch the errors. Ask how many orders carry more than one refund. Ask which refunds fall outside the reporting period of their original order. Then ask for the rate by product and by reason code.
Step 3: Export the chart, report, or deck
Take out the rate with its denominator, a distribution of refund lag in days, or slides that carry the date convention beside the number.
Common mistakes
Treating returns as physical returns. Platform metrics count refunded value whether or not goods came back. Say which one you mean.
Reporting the rate without the denominator. Orders, units, and value give three different answers. Name yours on the report.
Mixing refund date and order date. Pick one convention, label it, and never compare across the two.
Including refunded shipping and tax silently. Both are usually tracked separately. Adding them in makes reconciliation with finance impossible.
Counting rows instead of orders. Partial refunds create multiple rows per order. Deduplicate before counting.
Ignoring chargebacks. Money leaves without a refund record. Decide where they appear and write it down.
Comparing to a published industry rate. Other retailers use other denominators and other date rules. Compare against your own trend first.
Conclusion
Separate the refund transaction from the returns value, pick a date convention, choose one denominator, and report the rate beside the base it came from.
The rate is not the deliverable. The split by product, by reason, and by refund lag is what tells you whether to fix a listing, a size chart, or a supplier.
If rebuilding that split every month eats a day, try Powerdrill Bloom on your order export. See also the CSV AI assistant and AI report generator pages.
Frequently asked questions
What is the difference between a return and a refund?
A refund is the transaction that sends money back. Returns, as a metric, record the refunded value of goods and services whether or not anything was physically returned.
How do I calculate return rate?
Divide refunded activity by a stated base, then multiply by 100. The base can be orders, units shipped, or net sales value, and each gives a different figure.
Should I report on refund date or order date?
Either, as long as you label it. Refund date matches your platform and your bank, while order date gives a truer picture of each selling period.
Do refunded shipping and taxes count?
Usually not inside the refunds number. WooCommerce reports refunded shipping and taxes in the shipping and tax figures instead.
Why does my number differ from finance?
Most often the date convention, followed by whether shipping, tax, and chargebacks are included. Compare definitions before comparing figures.