Golden Nugget Secret for Amazon Refunds and Amazon Reimbursements
14 Mar 2016
I was wondering the technical aspect of how reimbursements are done on Amazon and how I could get lost money falling in between the cracks.
This method uses excel, and requires about 15 minutes of time the first time around, and maybe 5 minutes of time later on.
Basically, you pull reports, and based where you get that report, put it in “said” tab in excel. Then use “Auto Output tab” and insert formulas from reddit thread. Then file for money.
1.Criteria for reimbursement:
- Order was not returned to FBA within 45 days
- You have not already been reimbursed by Amazon
- Only open cases with 5-10 order ID’s max
- Do not send seller support a giant list of order ID’s
- “RefundMasterList” Reports > Payments > Transaction View > Filter view by: Refund Reports (download gets truncated after 600 transactions, make sure to download multiple times using various date ranges)
- “ReturnsToFBA” Reports > Fulfillment > Customer Concessions > Returns
- “Reimbursements” Reports > Fulfillment > Reimbursements
- “AllOrders” Reports > Payments > Date Range Report
3.Create tabs and headers
4.Copy and paste Column B from “RefundMasterList” into Column B of “AutoOutput”. Highlight column, Data > Remove Duplicates.
- A2 =INDEX(RefundMasterList!$A:$A, MATCH(AutoOutput!B2, RefundMasterList!$B:$B, 0))
- C2 =IF(ISERROR(MATCH(B2, ReturnsToFBA!$B:$B, 0)), “No”, “Yes”)
- D2 =IF(ISERROR(MATCH(B2, Reimbursements!$D:$D, 0)), “No”, “Yes”)
- E2 =INDEX(AllOrders!$I:$I, MATCH(AutoOutput!B2, AllOrders!$D:$D, 0))
- F2 =IF((TODAY()-A2)>45,”Yes”,”No”)
- G2 =SUMIFS(RefundMasterList!$G:$G, RefundMasterList!$B:$B, AutoOutput!B2, RefundMasterList!$E:$E, “Product charges”)
- H2 =IF(C2=”Yes”, “No”, IF(D2=”Yes”, “No”, IF(E2=”Seller”, “No”, IF(F2=”No”, “No”, “Yes”))))
- DO NOT SORT DATA after completing. It will mess up all the formulas. First copy all the information, right click, Paste Options, Values (V). Then you can sort the data as you please
- Why is the date column just a bunch of numbers? Highlight the column, right click it, format cells as: date
- Some orders aren’t reimbursable even though the file says it is reimbursable? Generally this worksheet finds 100% of potentially reimbursable transactions, but some false positives may be included. Check manually if the order ID is valid for reimbursement prior to opening a case with seller support
- Column F is returning a #N/A value: Here is how to fix: If the date in column A is less than 45 days ago, enter “No” in column F. If column A is more than 45 days ago, enter “Yes” is column F
- How do I put the formulas in the rest of the spreadsheet? On the cell containing the formula, double click the bottom right box in the cell, the formula will auto-fill the rest of the sheet.
On a side note, kind of sad Amazon doesn’t do this for us, but I do see why they don’t do it for us… they are “potentially”, (are) saving “billions” a year from sellers.
Post below your returns
Popular: The masterlist of resources we use and recommend.
We are the fastest growing Amazon FBA Podcast with over 150,000 Amazon sellers tuning in. See why this is the best Amazon Podcast.
Amazon Seller Tools for ourselves, and for you, which we use to supercharge our Amazon listings and build epic kindoms. Comes with a 7 day trial.