Update on Importing Amazon Orders into Google Sheets (May 2023)

Chiming in here on the JSON question, I downloaded my info following the Tiller guide for Amazon but had a mix of CSV’s + JSON. Retail.OrderHistory.1 for me had a JSON file instead of CSV.

Hey @randy is there anyway you could share a template of what the CSV is supposed to look like for Retail.OrderHistory.1? I want to try to convert the JSON into the right CSV format but don’t know what the end result should look like.

I think that is kind of crazy, @danny. The format is so specific and fussy. I think you’ll have better luck with the Simple CSV Import Workflow. It’s like losing a key and then trying to create a 3D model of the key from a photo and hoping to open the door.

I just requested my Amazon data. I’ll take a peek at it when it finally arrives though Tiller is doing an offsite next week near Seattle… so it may be closer to the 20th that I can dig into it.

I’m relatively new to tiller from mint-- there was very little option to split up and appropriately categorize my amazon orders before so I have a lot of generic “household” or “discretionary” categories for amazon purchases in my legacy mint data. If I want to use this tool to import my amazon transactions will I need to go and delete the amazon transactions from my mint upload so as to not cause duplication issues?

Welcome, @Estaria. The two Amazon import workflows referenced— Simple CSV Import Workflow and the Import CSV Line Items workflow in TCS– are designed to create Amazon transactions at the line-item level. The latter created “order offsets” intended to wash out the existing credit card transactions. It can be a little confusing if you’re new to the workflow but we’ve done our best to document that functionality in the help doc.

1 Like

Is there any update on when returns will be added to the workflow?
It would be great if there were a youtube video about this amazon functionality

Did you try the Returns workflow, @rshahk?

I won’t be able to get to a video in the near future but if you let me know which part of the instructions are confusing, I can try to clarify…

I’m good to go. I figured it out and realized what I did wrong. Thanks.
Is there a way to have the categories suggested in autocat? I know Amazon categorizes each of its products and if I remember correctly those categories used to be on the order history but I dont see it anymore. Is there a way that we can pull those categories and is there a way that we can implement and map them into our categories in Tiller?

Based on my notes, I think the Category content that was in the older CSV is no longer there in the Privacy Central version of the report. Let me know if I’m wrong, @rshahk. (And the Refund workflow has a really sparse input with just 7 columns.)

You are correct, but there is ASIN and there should be a way to get a category from ASIN. I did a quick search and there is a way via Amazon to get it directly… you need an AWS account to be able to make the requests. There are also third-party websites that have API access as well - https://www.asindataapi.com/. It would be amazing if the importer could add categories to the orders before they import based on a new sheet that maps the amazon categories to our Tiller categories.

It looks like the ASIN field is captured, @rshahk, in the isbn key in the Metadata column. I know this is a little confusing, but Amazon has changed their format several times and we’ve been trying to keep the data compatible with previous imports.

Can you see the ASIN there? You should be able to write a formula (possibly regex) to extract the ASIN from the Metadata text object.

Hope this helps.

My export actually showed the ASIN in its own column. Getting the ASIN is not so much the issue but getting the category from the ASIN automatically without me needing to build anything custom would be unbelievable. Is there anyway that we could add this to the importer process in Tiller?

It’s possible but my very strong preference is to have the importer be a single step generic workflow with only the column mappings changing for different csv types. If you want to customize the output, I’d recommend the other workflow where you can build your own column mappings.

1 Like

How would you suggest doing this?
I was able to get all the possible amazon categories mapped to my Tiller categories.
I would like to have the it categorize manually after I import my amazon transactions(I plan to do this one a quarter) and add the categorization date. I would need to make an API call for each to be categorized

After a lot of research I figured it out:

  1. I created a sheet for all the possible amazon categories and mapped them to my tiller categories
  2. I paid $25 to get 10k requests from ASIN Data API - The World's Best Value Amazon Data API and am using the below formula to get the amazon categories on a separate sheet that I created that pulls a unique list of ASINs from the metadata on the transactions sheet from the import of amazon transactions. =INDEX(IMPORTDATA(“https://api.asindataapi.com/request?api_key=XXXXXXXXX&amazon_domain=amazon.com&asin=“&A2&”&type=product&language=en_US&output=csv&csv_fields=request.type%2Crequest.url%2Crequest.asin%2Crequest.amazon_domain%2Crequest.custom_id%2Cproduct.categories_flat”),2,6)
  3. I am waiting to complete step 2 but once that is done I can get the first category using the index and split functions. Then do a Vlookup to get the tiller transaction and then do a vlookup on the transactions sheet to present the recommend category form the amazon items line items

This sounds like a pretty awesome— if persnickety :wink:— solve, @rshahk!
I’m impressed by your attention to detail.

It’s inspiring to see someone so fully harnessing the raw power of spreadsheets!

Are you still able to see the Amazon Store Card description data in Tiller? I signed up for one and my transactions finally starting importing but the Description data is not appearing. These are confirmed Posted transactions on the Synchrony/Amazon website. Thank you.

@mu3484343 Mine are still feeding through with descriptions as of this past week and my latest transactions. What comes in through the descriptions for you?

Nevermind, it’s working now. Thanks!

That’s great to hear! I was worried my hack had stopped working!