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

so far i haven’t had a problem with the deduper, probably just being overly cautious. i’ll let it run for a larger window

My preferred Amazon data hack at the moment is that if you make purchases on an Amazon Store Card (this is the Synchrony card, not the Chase card), the transaction description will load your actual item description straight into the description, no separate Amazon download needed. If you buy multiple items, it will just list them all and not break up the transaction amount, but my workaround there is to place multiple orders for different groupings of items when I would want to separate them (and often they still arrive together in the same box when your orders are placed close together). I still often leave things in my default “Amazon” category, but when they’re big enough or part of categories I’m tracking more closely, I overwrite the AutoCat with a different category. Anyway, just another data workaround for Amazon for those searching and end up overwhelmed with the Amazon data download process.

1 Like

Oh, that’s a great idea! I’ll have to see about getting that card.

To fix your delivery issue: you may have noticed that there is an “Amazon Delivery Day” option on shipping? It’s one day of the week you can choose to have your Amazon packages delivered, and if you choose that on separate orders they’ll try to group the packages together in boxes and orders.

Yes, that’s a great option too! Usually I am emergency ordering diapers or cat food or other things that can’t wait a week, so I usually override that option, but it seems that as long as it’s coming from the same warehouse, even picking the faster shipping options, they do generally pack multiple separate orders into the same box!

It’s a little bit lame but I did my best to update the returns/refunds workflow, @sdre & @sagor.

1 Like

A post was merged into an existing topic: Update on Importing Amazon Returns & Refunds into Google Sheets (May 2023)

Hi Randy - could you clarify how these amazon fresh purchases work? I’m assuming the reason to exclude would be because they are already showing up in someone’s bank account another way - is that correct? What would the description be if it doesn’t come through the bank as “amazon fresh”? Is it the same as whole foods? Just trying to decipher an upload I did for someone else and the totals are WAY off – the amazon upload has tens of thousands more in charges than the bank account shows are for Amazon and I think that these “panda” or amazon fresh charges maybe have something to do with it.

Speaking for myself, I had to manually add a bunch of Amazon transactions during the period when this wasn’t working. I modified the prices and item descriptions and didn’t do it the way you did it. So, in short, no, the de-duper just won’t work on my data. I guess I’m seconding the suggestion to make the time window more easily adjustable to only include the last X days or month or something. Due to the nature of the data, at this point, it’s virtually impossible to get anything later than four months ago.

Oh my, @kirsti.rehler. Personally, I had chosen to exclude the Amazon Fresh transactions because they were so voluminous and also because, as primarily grocery items, they seemed readily categorizable as “Groceries” en masse at the credit card transaction level (i.e. I don’t bother to try to itemize my Safeway purchases).

To be honest, while I think they are messy and suffocating to import, I didn’t anticipate and hadn’t seen them create problems in terms of data integrity. If you have some insights into why that happened, I would be interested to hear more.

I imagine the “panda01” transactions are showing up on the user’s credit card statements. I’m not certain how the payee would be captured— “Amazon Fresh” and “Whole Foods” seem like likely candidates.

We are all a little bit frustrated with the data quality and workflow eccentricities now that Amazon has significantly modified what they are sharing. You all have some great suggestions on how to improve this workflow. The CSV-tool had been a relatively-generic piece of code that had flags and mappings to work with many different CSV sources… and that had worked great with what Amazon was outputting. Unfortunately, their new format— especially the more limited set of fields, the unfiltered date range, the mass of Amazon Fresh transactions— really push at the margins of what is a viable general-purpose importer. Customizing the workflow for this new data set would certainly alleviate some of the frustrations. We will get to this but there are many prioritized product improvements that are currently more urgently earning the development team’s attention. Hopefully we can make this stopgap workflow work for us in the interim… or someone in the community can share something better… :wink:

3 posts were split to a new topic: Line-item Descriptions with (Synchrony) Amazon Store Card

The new import is not working for me. It just keeps on loading and loading with the message: " Analyzing line items history…". I’ve tried it on 3 browsers. I deleted all activity except for June 1 forward so it’s not a huge file with a lot of transactions, just a month and a half worth. Has anyone else had this problem? Any idea how to fix it?

I JUST requested my data and will try (re)running the workflow when I have the data, @danielwe. (They have been known to abruptly change their output format in ways that breaks our tooling.)

Just tested it. Worked great. Amazon CSV contained 3.7k records and those were matched against 40k transactions in my Transactions sheet.

If it gets stuck on the “Analyzing screen” for more than 10 minutes that is almost definitely a crash. You might be able to find something helpful by opening up the developer pane in your browser and check for an error in the Console tab. I’m surprised you’re having an issue with a small file.

this happened to me as well. I removed the actual messages in the “Gift Message” cells; changed them all to “Not Available” and the import then worked.

@randy

Thanks for the update here!

@randy do you know if the default file type for the Amazon download is still a CSV? Is it possible it’s a JSON now?

Any tips for if a customer only has a JSON after following the steps?

I’m not aware of a JSON download type. If that is available, can someone send me the link?
(The CSV importer will definitely not work on a different file type.)

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.