Preparing a returns file

Why the returns data lives in the refunds file

AXIOTRA has no separate returns import. The physical side of a refund is carried by two optional columns on the refunds file, return_received and restocked, and the refund detector reads nothing else: a row with return_received true and restocked false is raised as a finding, every other combination is silent. Both columns accept 1, true, yes or y; an empty cell reads as false. So a refunds file exported straight from Stripe, Shopify Payments or an Amazon settlement says, for every refund, that nothing came back and nothing was restocked, and the detector has nothing to raise.

The returns portal, the 3PL and the WMS know the other half: whether a parcel was scanned in, what it graded as, and whether it was put away into a sellable location. This page is the recipe for joining that export to the refund export so that each refund row carries the truth about its unit. It is a spreadsheet job, done once per period, and it is the difference between a detector that stays silent and one that lists every stranded return.

What to export from the returns side

You need one row per returned unit, or per RMA line, with at least: the order id the return belongs to (or the RMA number plus a lookup from RMA to order), the SKU and quantity, the date the unit was received or checked in, the disposition or grade after inspection (sellable, damaged, return to vendor, missing), and the date and location of the put-away that followed. Most returns portals export the first four; the put-away date and location usually come from the warehouse or WMS, because the portal only knows that the parcel arrived.

If your warehouse cannot export a put-away event, use its inventory adjustment log instead: an increment for that SKU into a sellable location on or after the received date is the same fact. If neither exists, you can still set return_received from the portal and leave restocked empty; the detector will then raise a finding for every received return, and you will have to close the ones that were in fact put away by hand. That is noisy, but it is honest, and it is better than the alternative of setting restocked true on trust.

The join, step by step

1. Start from the refund export, one row per refund, with refund_id, source, order_id, amount, issued_at exactly as the CSV import reference describes them. Keep the platform's own refund id and the same order_id string you used in the orders file; the join to orders is an exact match on that string.

2. Join the returns rows on order_id. When an order has several refunds (a partial refund at label creation and the balance on receipt is common), attach the return to the refund whose issued_at is closest after or before the received date, and leave the other refunds with both flags false. When one refund covers several units, the flags describe all of them: set restocked true only if every unit was put away.

3. Set return_received true when a receiving scan or check-in date exists for the unit. A label that was created, or a carrier scan that shows the parcel in transit, is not a receipt; the unit is not in your building until the warehouse says so.

4. Set restocked true when a put-away into a sellable location, or an inventory increment for the SKU, exists on or after the received date. A disposition of sellable is not enough on its own: grading a unit sellable and moving it to the shelf are two steps, and the second is the one that gets skipped. Units graded damaged or return to vendor stay restocked false, because the stock was in fact not restored; whether that is a problem is a judgement the finding leaves to you.

5. Apply your grace period before you upload. A unit received yesterday and not yet put away is lag, not a leak. Either exclude receipts younger than your normal inspection time (two to five business days is typical) from the file, or upload them and expect findings that close themselves on the next upload. The detector applies no grace period of its own.

6. Save as CSV with the header refund_id,source,order_id,amount,currency,status,issued_at,return_received,restocked and upload it as the refunds file. Rows are matched on source plus refund_id and replaced, so uploading the same file again with corrected flags is safe and is the normal way to update the picture each week.

What each connection does to the two flags

CSV only. The refunds file is the only writer, so whatever you upload stands until you upload again. This is the simplest case and the one the recipe above assumes.

Stripe connection. Refund rows arrive with both flags false, because Stripe knows nothing about parcels. A CSV row with source stripe and the Stripe refund id (re_...) as refund_id replaces the connection's row and sets the flags. One trap: every sync re-reads refunds created in the last 30 days and writes them again with both flags false, so flags on a refund younger than 30 days survive only until the next sync. Do the join for refunds older than the re-read window, which is also the population where a missing restock is a real miss rather than lag.

Shopify connection. The flags are derived from the restock type on each refund line: a line refunded with restock type Return reads as return_received true and restocked true, because Shopify adjusts inventory the moment that refund is created; a line refunded with no restock reads as both false. A refund whose lines mix the two reads as received but not restocked and is raised. Shopify records what was ticked in the refund dialog, not whether the parcel arrived or what it graded as, so a Shopify-connected store still needs this join to see units that were restocked in Shopify but never physically put away. Replacing a connection row from CSV requires Shopify's numeric refund id as refund_id, and the row will be rewritten from Shopify the next time that order is updated.

Amazon connection. Refund events arrive with both flags false, and the stored refund id is built from the Amazon order id and the posted timestamp, so a CSV row will not replace it. Upload the returns join with your own refund_id and source amazon; the Refunds screen will show two rows for that order, and the finding is raised once, on the row that carries the flags.

Checking the result

The Refunds screen in your workspace lists every refund row with its order, amount, and the two flags shown as received / no and yes / no. After the upload, the rows you flagged should read received and no. Each of them produces a finding titled with the order name and the words return received, inventory not restored, with the refund row and the return record attached as evidence.

The money on the finding is the refund amount plus the cost of goods for the order's SKU multiplied by its quantity, when a costs row covers that SKU; without a cost the finding reports the refund amount and marks the restock value UNKNOWN. So the same weekly routine that produces the returns file should also keep the costs file current, or the findings will be sorted by refund amount rather than by what is actually stranded.

Refunds that have no return at all, where return_received stays false, never produce a finding. They are a different problem, refunded without a return, and the Refunds screen filtered to received = no is the working list for it.