How to use Back-Filling Historical Returns From Shopify Order Data
A step-by-step method for pulling Shopify refund objects, joining them back to the original order, and restating historical Marketing ROI net-of-returns — without waiting a fresh return window to elapse.
Back-Filling Historical Returns From Shopify Order Data
Retroactively joining Shopify refund events to their original orders so past Marketing ROI can be restated net-of-returns without waiting a fresh return window.
Back-filling historical returns is the operational process of pulling every refund event Shopify has recorded — full refunds, partial refunds, restocking fees, shipping refunds, and exchanges — and stitching each one back to the order it originated from. Once every past order carries its true net revenue, you can restate marketing ROI, ROAS, and cohort LTV against numbers that match the P&L instead of the gross figures your ad platforms still report.
The alternative is waiting six to nine months for a fresh return window to close on new spend. Back-filling collapses that wait into a one-time SQL job against data Shopify has already stored, which is why it is the first step most performance teams take when they discover their reported ROAS is 10-25% higher than reality.
If you have ever discovered that your Meta and Google dashboards report a ROAS that never quite lines up with what shows up in the bank, returns are usually the gap. Shopify records the sale at checkout and the refund weeks later — two events, two dates, and only one of them makes it into your ad platform's conversion API by default.
The good news: Shopify already stores every refund as a structured object attached to the original order. You do not need new tracking. You need a query, a join, and a decision about how to allocate partial refunds across line items.
How Shopify models a refund
In the Shopify data model, a refund is a child of an order. Every refund has an `order_id`, a `created_at` timestamp, a `note`, and a `refund_line_items` array that itemises which SKUs were returned, in what quantity, and at what price.
Alongside the line items, the refund object carries a `transactions` array (the money movement — usually a `refund` kind against the original gateway) and an `order_adjustments` array that captures restocking fees, shipping refunds, and manual write-offs. Ignoring `order_adjustments` is the most common cause of a back-fill that undercounts net revenue by 2-4%.
Two events are commonly confused with refunds and should be handled separately: order edits (a merchant-side change to the original order, which restates the order itself rather than creating a refund child), and exchanges (which in modern Shopify are represented as a refund plus a new order, and must not be double-counted as revenue).
REST vs GraphQL: pick one and commit
The REST Admin API exposes refunds under `/admin/api/2024-XX/orders/{id}/refunds.json`, but it caps at 250 per page and does not surface the newer `returns` object cleanly. For any back-fill covering more than ~12 months of history, use the GraphQL `refunds` and `returns` connections — pagination is cursor-based and you get restocking data on the same request. Mixing the two APIs mid-job leads to duplicate keys and drift between reports.
Extracting the refund history
Start with an unbounded pull: every refund created since the earliest order date you care about. For most stores that means 24-36 months of history. Do not filter by refund date at extract time — filter at the join step. The reason is subtle but important: a refund created in March against an order placed in October needs to restate October's ROAS, not March's.
Pull the following fields at minimum: `refund.id`, `refund.order_id`, `refund.created_at`, `refund.processed_at`, the full `refund_line_items` array with `line_item_id`, `quantity`, `subtotal`, and `total_tax`, plus `transactions.amount` and `order_adjustments.amount` with `kind`. Persist the raw JSON alongside the parsed columns — you will want to reconcile edge cases later without re-hitting the API.
When refunds land relative to the original order date
Apparel
Beauty
Electronics
The distribution above is why the choice of return window for restatement matters. If you cut the back-fill at 30 days, you capture roughly three-quarters of apparel and beauty refunds but only about half of the electronics tail. For most stores in this revenue band, 60 days is the pragmatic ceiling; anything longer and you are correcting numbers by rounding-error amounts.
Joining refunds back to the original order
The core join is trivial: `refunds.order_id = orders.id`. What is not trivial is what you compute after the join. For each order you now need three derived columns: `gross_revenue` (unchanged from the original), `refunded_amount` (sum of refund transactions minus positive order adjustments like restocking fees), and `net_revenue` (gross minus refunded).
For partial refunds, the naive approach is to subtract the refund total from order revenue and stop there. That works for headline ROAS but breaks line-item ROI: if a customer bought a €90 dress and a €30 top and returned only the top, the dress still generated revenue and the top did not. Allocate refunds at the `refund_line_items` grain when downstream analysis is SKU- or category-level.
Typical restatement impact by vertical on a 24-month back-fill (Shopify stores, €1M-€15M revenue)
| Vertical | Gross return rate | Net-of-restocking rate | ROAS restatement | % orders with partial refund |
|---|---|---|---|---|
| Apparel & footwear | 22-32% | 19-28% | -18% to -26% | 8-12% |
| Beauty & personal care | 6-10% | 5-9% | -5% to -8% | 3-5% |
| Home & lifestyle | 8-14% | 7-12% | -7% to -11% | 6-9% |
| Electronics & accessories | 10-16% | 8-13% | -9% to -14% | 4-7% |
| Food, supplements, consumables | 2-5% | 2-4% | -2% to -4% | 1-2% |
The final step is re-attribution. Every order carries the UTM parameters and channel it was acquired through; when you restate net revenue, you must push that restatement back to the same channel and time bucket the acquisition was booked to. Otherwise apparel refunds get spread proportionally across every channel and you lose the ability to see that, for instance, a specific Meta creative is driving high-return customers.
Edge cases that break naive back-fills
Multi-currency stores are the first landmine. The refund transaction stores an amount in the presentment currency and another in the shop currency. If your gross-revenue column is in shop currency and you subtract the presentment-currency refund, you will silently under- or over-refund by the FX delta on the day of the refund. Always net within a single currency and convert once at the end.
Subscription and recurring orders need a different join key. The refund attaches to the specific recurring order it refunds, not to the parent subscription contract — so LTV restatements have to walk the full contract history rather than treating the first order as the only refundable event. Exchanges, meanwhile, will show up as a refund plus a new order; if you sum revenue naively across both, you double-count.
Reconciliation check before you publish restated numbers
Run one query before you push restated ROAS to stakeholders: sum all refund transactions in your back-fill window and compare against Shopify's Finance → Payouts report over the same window. The two should agree within 1-2% (the delta is usually chargebacks, which Shopify records under a different object). If the gap is larger, you have either missed `order_adjustments` or double-counted an exchange.
Frequently asked questions
24 months is the sweet spot for most stores. It covers a full seasonal cycle plus a comparison year, which is what you need to restate cohort LTV and year-over-year ROAS. Going further adds diminishing analytical value because your product mix, pricing, and channel strategy have all shifted.
GraphQL for anything more than a few months of history. The `refunds` and `returns` connections are cursor-paginated, expose restocking and return-reason data in one request, and don't have the 250-item page cap the REST endpoint imposes. REST is fine for a proof-of-concept pull on a single quarter.
Allocate the refund at the `refund_line_items` grain, not the order grain. Each refunded line item carries its own subtotal, so you can subtract that from the specific SKU's revenue instead of pro-rating across the whole basket. This matters most for apparel and multi-item baskets.
Treat the refund half as a revenue reversal and ignore the new-order half for the purpose of restating the original acquisition's ROI. The new order is genuinely new fulfillment cost but it did not require a new acquisition, so double-counting it as ad-driven revenue will inflate ROAS.
Restocking fees are positive `order_adjustments` — they add back to your net revenue and should reduce the refunded amount. Shipping refunds are negative and further reduce net revenue. Both live in the `order_adjustments` array on the refund object; ignoring them typically under-nets revenue by 2-4%.
Send a `refund` event via the Conversions API (Meta) or an offline conversion adjustment (Google Ads) keyed on the original order's event ID and dated to the original conversion timestamp. Both platforms will then restate their in-platform ROAS. Do not send it dated to the refund day — that inflates the wrong week's costs.
Meta accepts adjustments up to 90 days after the original conversion; Google Ads allows up to 55 days for most conversion types. Refunds that land later can still be netted in your own warehouse ROI view but the platform-side dashboards will not update. Track the delta between the two so stakeholders know which number to trust.
The mechanics are the same but the join is against each recurring order, not the parent contract. Because a subscriber may refund their third shipment but not the first two, cohort LTV needs to be recomputed order-by-order — a simple order-1 revenue reversal will misstate the cohort curve.
Apparel and footwear stores typically see reported ROAS drop 18-26%. Beauty, home, and electronics fall in the 5-14% range. Consumables and supplements see the smallest impact, usually under 5%. The exact number depends on your product mix and whether you were already netting shipping refunds.
Both. Run it once historically to restate the past, then schedule a daily or weekly incremental job that catches new refund events and pushes adjustments to your ad platforms and warehouse. The incremental job is small — usually a few hundred refund events per day for stores in this revenue band.
Track CAC, channels, and funnel conversion in one place
Metricuno connects ad spend, funnel events, and revenue so you can see CAC by channel, cohort, and campaign — without stitching together five tools.