When I navigate here on the Amazon site:
Your Account > Your Orders > Order History Reports
I’m seeing a different header row when I request
Report Type: Items.
My header row looks like this:
- Order Date
- Order ID
- UNSPSC Code
- Release Date
- Seller Credentials
- List Price Per Unit
- Purchase Price Per Unit
- Payment Instrument Type
- Purchase Order Number
- PO Line Number
- Ordering Customer Email
- Shipment Date
- Shipping Address Name
- Shipping Address Street 1
- Shipping Address Street 2
- Shipping Address City
- Shipping Address State
- Shipping Address Zip
- Order Status
- Carrier Name & Tracking Number
- Item Subtotal
- Item Subtotal Tax
- Item Total
- Tax Exemption Applied
- Tax Exemption Type
- Exemption Opt-Out
- Buyer Name
- Group Name
Are you sure you’re requesting the same report, @matt.rowell?
That’s 100% on me! Somehow, my report got switched to Orders and Shipments…smh. It’s working now as expected; Thank you!
Glad to hear it is resolved, @matt.rowell.
Yes … they are CC transactions from the feed. I will need to review the process as I don’t think these transactions got zeroed out (others did) … but I need to check. If they don’t get “matched” (and updated) won’t my expenses be double counted in the budget categories?
I will think on the process.
Happy New Year,
Yes, @cas3, they would get double counted if there isn’t an offset that neutralizes them.
The “matching” process is more of a “soft match” since the scripts do not look for and match to specific rows in your
Transactions sheet. Essentially new purchases are added to the sheet as both line item expenses and order total credits. The order total credits should essentially sum to the amounts of the credit card charges (from Tiller Money Feeds) and zero them out— leaving only the line items which can be itemized and categorized.
More details here…
Just noticed that Amazon is now including prime now orders in the report, which seems to be working just fine with the importer.
Thanks for your continued help on this. I am trying to figure out how to properly classify transactions so that the do not get double counted; I am also trying to figure out how to deal with these transactions during the statement reconciliation process. I have included a transaction below for you to comment on / advise:
This is the transaction from the credit card statement. I had been categorizing Amazon transactions with the Shopping Tag until reconciliation.
Through a significant amount of effort (I can not see a clear way to map a cc transaction to an Amazon order except by brute force) I determined that it relates to these two transactions from the CSV import file:
NOTE: The TOTAL charge to my CC does NOT EQUAL the total of the CSV line items because of discounts applied during purchase.
NOTE: I think that the way I am categorizing the (3) lines will result in my BUDGET / SPEND total to be approximately twice my actual spend.
In order to BEST reflect my spending do you have a recommendation on how to categorize the CC transaction?
Do you have any advise on the best way to trace back from the CC transaction to the line item detail for reconciliation?
Any suggestions are welcome.
Happy New Year!!!
I’ve also experienced the difference in order/line item values compared to credit card charges due to discounts, gift card balance, or points being applied to the order. The problem is that the Amazon export CSV does not include this information. Personally, I have been manually reconciling these orders and items by editing the item cost and order total to reflect the order details on Amazon. It is a pain, to be sure, but I am able to get an accurate reconciliation at least.
As for how to categorize the credit card transaction and avoid double-counting, I have a
Transfer category for
Amazon Purchase that I apply to both the credit card transaction and the [Amazon Order] transaction. Only the [Amazon Item] transactions have an
Expense category assigned, so the expenses are properly reflected in my
Savings Budget sheet.
I’m sorry I couldn’t give any better advice for reconciling discounts, but at least you’re not alone. I hope that helps!
Thank you. Your advise / recommendation makes sense.
For Amazon Business Purchases what are the columns needed to import? It seems the default report does not have what Tiller is needing - this is the Tiller instructions:
- Log into your Amazon Business account
- Navigate to Amazon Business Analytics
- Select Orders in the Reports menu
- Select a report time period
- Click Download CSV in the upper right
- Upload the report CSV file below
This is good news, @kraig. I hadn’t noticed.
Regarding balancing out the charges, @cas3, I use the process that @cculber2 suggests with the
Transfer category. It’s imperfect, for several reasons stated above, but it is close enough for me.
As for brute force matching… I wish the data set were robust enough to match to and neutralize individual credit card transactions— that is where I started with this project— but I ran into too many issues and realized it wasn’t possible with the provided data set. So we are left with the offsets ("[Amazon Order]") approach which gets relatively close.
Thanks to all for their input. Now that i understand what us happening I can work with the process.
Randy - thank you for the script.
I’m not seeing this for my accounts - did you have to do anything special? I’m having trouble telling amazon and primenow order apart based on the description and I hoped this was going to be a way to do it!
Can you clarify what you’re not seeing, @mattjbowser? The report on the Amazon site? Prime orders in the CSV? The workflow in the add-on?
I can extract data that we find in the CSV but if the CSV doesn’t offer the detail you’re looking for, there is nothing we can do in the add-on. Let me know if there are fields in the CSV that you’d like to extract differently.
@randy I was responding to @kraig who mentioned his report also included primenow purchases - my report does not, only regular amazon purchases.
FYI… Amazon order reports are not accounting for 3rd party seller paid for shipping. Just noticed this on a return… the return price was larger than the order price by the paid shipping… researching, the AMZ order report only includes the cost as well as the tax.
Has anyone had issues generating reports lately? Mine has just been stuck on processing, last success was on Dec 13 for me.
Yes, it seems to be broken for everyone. We’ve been discussing it on a new thread.
Amazon Order History Reports Never Generate