Following up on the recent post Update on Importing Amazon Orders into Google Sheets (May 2023), we have more good news… The Import CSV Line-items workflow in Tiller Community Solutions add-on has been updated to include Amazon returns/refunds with data from Amazon’s "privacy central” service .
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:
(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.
Thank you Randy! Sure, it’s limited but that’s not Tiller’s fault and I really appreciate you re-building the workflow!
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.
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.)
Glad to hear it helps… and thanks for understanding the limitations, @njfulton.
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?
- Most of us have far fewer refunds than orders. I only have a few dozen over 20 years.
- There is a date filter in the importer. If you just set that to your start on the new file format you shouldn’t see duplicates.
It’s a drag but Amazon has really degraded the quality of their analytics.
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!)