Randy - first off, I love the workflow. Very helpful. But it has limitations due to the “features” of how Amazon generates some of its reports. I was simply trying to document the list of exceptions that this workflow doesn’t capture, so that they can be manually adjusted. So far, I am aware of the following:
Gift Cards: when you check out an Amazon purchase, you can apply Amazon gift cards which lowers the amount (if any) that is charged to your credit card, but the item report will still list the transaction for the full amount prior to applying the gift card. example: order $25 item, apply $10 gift card, credit card is charged 15. there is no way to see this in the items report csv. If you download the orders & shipments report, in the Payment Instrument Column, it will list IF a gift card was used, but there is no way I have seen yet to determine how much was provided by the gift card. So the only automation here would be to ingest the orders csv and throw a flag to trigger a manual adjustment if it detects a gift card.
Shipping costs: if you dont have Prime, or you have Prime and order something from a vendor who charges shipping on Amazon, you are charged a shipping charge. This is visible in the orders csv in the “shipping charge” column, but not in the items csv. You’d have to import both files and then correlate the charges. Or document/flag it as something that needs a manual adjustment.
Grocery delivery charges: While every single item from a grocery delivery order is listed in the item report, they delivery fee/tip is not. But it shows up as an Amazon Grocery charge on your credit card. Again, only manual adjustments possible here.
Other issues that I assume, but have not come across yet personally: refunds and returns (you can see those in the corresponding csv files I assume)
The cause for my ask was to figure out if there were other similar exceptions that could cause a discrepancy - even if I manually adjust for all the issues above, I still have a discrepancy. I am guessing there are other situations that cause these issues, and wanted some hints to better ID them. Manual forensics on this is annoying because time stamps for the amazon report and credit card transactions have varying offsets/delays.