Read this post for more information on the history of Amazon import workflows and the change in available data.
There are many files in the Privacy Central Your Orders report. The one that seems to contain the most complete (though still limited) return information is at this path: Retail.CustomerReturns.1.1/Retail.customerReturns.1.1.csv
(Let me know if the suffixes vary in your download.)
The Bad News
Let’s start with the limitations of the new workflow…
The Privacy Central returns CSV contains significantly less detail than the old refunds CSV. Most notably, it is missing a description of the item that is returned. When you import a return with the new workflow, you will mostly see the refund amount, the date, and the Amazon Order ID. (Personally, I have been searching my Transactions sheet for the Order Id in the return to find the description in the purchase line-item of the returned product.)
Due to the many missing pieces of information that were previously available, the new workflow cannot identify refunds that were previously imported. In other words, when you download the non-date-ranged Privacy Central returns data, the line-items workflow will attempt to import every return you have ever made (unable to reconcile with those you have already imported with the original workflow).
The Good News
The new workflow will avoid reimporting returns that you have already imported with the new workflow. So, on a go-forward basis, you will not have to worry about duplicate returns.
To manage the duplicates issue, I recommend either stripping older returns from the CSV using a text editor prior to import or using the date-range filter in the import workflow.
I’m sorry that this workflow is so limited. You might find that it is not worth the trouble. Unfortunately, there just isn’t much detail provided by Amazon and the solution of uploading multiple files to cross reference (say an orders CSV) for additional information isn’t practical at this time.
Let me know if you find this helpful or if there are any clarifications that would make this update more useful to others.
Hi @randy
Thank you for making return workflow working.
I see what you meant by it being limited.
I first import my amazon orders for a month, and then do refunds.
I noticed that refunds are imported like so:
[Amazon Return Item] Item in Order 111-7940460-4008267
But, if I search for that order ID manually in tiller spreadsheet, I can find the actual item being returned. I assume it might not work very well if one amazon order contained multiple items, and I returned only one of them. But, for the cases where one item is equal one order, would it be possible for you to search for that item id during import, and if amount matches (in my case it always did) then use that to get item name?
At the very least it would improve return workflow for hopefully majority of cases, and then for some I might have to manually figure it out which actual item was returned from that order.
WDYT?
Thank you!
I understand what you’re asking, @sagor. I think there are two possible approaches to cross referencing the limited returns CSV fields to build a richer record:
Performing a search in the user’s sheet for a matching Order Id and pulling data from that row (as you note).
Uploading two CSV files (e.g. the returns and orders) together and cross referencing the returns Order Id into a richer data source before row insertion.
These would both require special Amazon-specific code in the Import CSV Line Items workflow (e.g. if the user is running the Amazon returns workflow, invoke a bunch of special code). At this time, all of the line-items workflow (e.g. Mint, Paypal, Venmo, etc…) run the same set of operations and I’m hesitant to complicate that at this time (particularly since the Amazon data formats seem to be in flux).
One suggestion for a go-get-em community builder… I’d be open to building a new data mapper if someone in the community wanted to take on the challenge of building a public returns-csv-merger service. It wouldn’t be a huge project to create a web service that accepted a returns and orders CSV and outputs a “super CSV” containing the returns lines with information about what they contained. (The hardest problem would be matching bulk orders to single line items presumably based on a fuzzy price match— but this could just look for a simple exact match and then throw up its hands with a generic description when things got complicated.)
Before I start trying to figure out something that is not available to me, I thought I would ask first.
Does the Amazon Importing only work for USA. Or does it now work for Canada too?
Thanks for the update. It looks like I’ll need to manually strip out the old transactions in the CSV before importing every single time? If I ever forget I’m going to be surprised with 3,000 new transactions in my Transaction list! Perhaps the import code could look at the most recent Amazon imported transaction date, and stop there?
I think I am missing something. What is the value of importing the returns at all if there is no description? Wouldn’t the returns already be in your spreadsheet from your credit card? What additional information am I getting by importing the separate refunds item?
As an aside, I wouldn’t assume that most people don’t have very many returns. I have definitely returned more than a dozen items in just the last year. The return functionality is a big deal to me. It probably depends on the volume of purchases you have and what types of things you purchase. (I do understand that this has nothing to do with the Tiller team and community who are doing their best with the limited data Amazon now provides. Thank you for all your hard work!)
with a generic description when things got complicated.)