Using the importer tool with amazon, shows some discrepancies where total net spend for the time period is just a little off compared to before using the importer tool. I have identified some issues that I think the importer doesn’t account for: gift cards (CC transaction <> sum of transaction line items), Amazon /Wholefoods delivery charges (just shows up as CC charge, but no corresponding charge in item report) and shipping costs (I had one transaction where I didnt get free shipping, and the amazon item-level report excludes shipping costs, so it doesnt get added). Are there other similar glitches like that I should be aware of? After adjusting for these known issues, I am still off by ~$25 for 6 months worth of transactions which is fine, but I’d love if the numbers tied fully.
Frankly, the tool was built and validated using pretty vanilla transactions. I’d be happy to refine it, but (since I don’t have a dataset like yours) I would need some guidance on how to get to the right numbers.
As built, the importer maps the CSV column
Item Total to
Amount in the Transactions sheet. Do you see a clean & dependable formula for creating a more accurate amount using the data in the CSV?
Thanks for digging into this, @joern.
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.
Thanks for so thoroughly detailing these exceptions, @joern. I hope it is OK that I duplicated these exceptions (as you wrote them) in the CSV Import documentation. (I will remove them if you would prefer that.)
It sounds like there isn’t a simple fix involving smarter field mapping.
Regarding your note about “flagging manual adjustments”, I think we can improve this workflow. My import rules have a list of columns that are mapped from the CSV to the
Metadata column in the
Transactions sheet. (They are stored in JSON object format.) If you’d like me to add any columns header names that you can leverage for auto-generating flags, let me know which ones you’d like to add.
ARRAYFORMULA() can be used similar to this implementation to autogenerate flags based on fields in the
Thank you for reply, glad to help and to see my input in the documentation.
One other scenario I noticed btw:
The dates/time stamps of the CSV and the credit card often differ, sometimes by 3-4 days or more. The item CSV time stamp will typically predate the credit card time stamp. If you pull and ingest the CSV from Amazon between two such time stamps, you will have unbalanced transactions (Amazon transactions in Tiller transactions without corresponding credit card transactions). That will rectify itself once the credit card transaction is processed, but it might cause some short-term confusion, as it did with me.
Interesting point, @joern.