Item Subtotal is not helpful because it a) isn’t always populated and b) often includes other items in the order. Maybe I misinterpreted the way the tax is applied. Let me peek at a few transactions and see if the correction you’re proposing, @kraig, is the right one…
I’m unable to upload this as a csv file, but here is an example dataset with a few items and with >1 quantities:
Marketplace,Order ID,Order Date,Purchase Order Number,Currency,Price,Price Tax,Shipping Charge,Item Subtotal,Item Subtotal Tax,ASIN,Product Condition,Quantity,Payment Instrument Type,Order Status,Shipment Status,Ship Date,Shipping Option,Shipping Address,Billing Address,Carrier Name & Tracking Number,Product Name,Gift Message,Gift Sender Name,Gift Recipient Contact Details
Amazon.com,114-8708283-7274626,2020-08-15 15:45 UTC,,USD,9.99,0.95,0,23.15,2.21,XXX,New,1,XXX,Closed,Shipped,2020-08-16 00:28 UTC,next-1dc,XXX,XXX,AMZN_US(XXX),x,,,
Amazon.com,114-8708283-7274626,2020-08-15 15:45 UTC,,USD,19.99,1.9,0,109.96,10.45,XXX,New,2,XXX,Closed,Shipped,2020-08-16 21:16 UTC,next-1dc,XXX,XXX,AMZN_US(XXX),x,,,
Amazon.com,114-8708283-7274626,2020-08-15 15:45 UTC,,USD,39.99,3.8,0,109.96,10.45,XXX,New,1,XXX,Closed,Shipped,2020-08-16 21:16 UTC,next-1dc,XXX,XXX,AMZN_US(XXX),x,,,
Amazon.com,114-8708283-7274626,2020-08-15 15:45 UTC,,USD,6.58,0.63,0,23.15,2.21,XXX,New,2,XXX,Closed,Shipped,2020-08-16 00:28 UTC,next-1dc,XXX,XXX,AMZN_US(XXX),x,,,
Amazon.com,114-8708283-7274626,2020-08-15 15:45 UTC,,USD,29.99,2.85,0,109.96,10.45,XXX,New,1,XXX,Closed,Shipped,2020-08-16 21:16 UTC,next-1dc,XXX,XXX,AMZN_US(XXX),x,,,
Item(s) Subtotal: $133.11
Shipping & Handling: $0.00
-----
Total before tax: $133.11
Estimated tax to be collected: $12.66
-----
Grand Total:$145.77
Two charges, one for $76.62 and another for $69.15
My Amazon category is working, @randy. However admittedly I didn’t check the financial math so there is a possibility I got incorrect valyes due to the tax issue that @kraig is discussing. But I gather I am past the point-of-no-return as I just imported all this data in (139 items worth).
You should be able to strip those out easily with a filter, @eslimko, if you want to reimport them. Just create a filter where Date Added is today and the Metadata column starts with {"amazon":.
There should be 139 rows and you can just delete them.
Thanks. It helps to have a skilled person as a guide. I only started with Tiller a handful of months ago and haven’t used it in a very sophisticated fashion yet. Before I re-import I’ll see where this discussion between you and kraig ends.
I just pushed new rules, @kraig & @eslimko, that use this math for the Amount column:
Amount column = Quantity * (Price + Price Tax) + Shipping Charge
It seems to be accurate in most cases— much more accurate than what I had before— but I have found a couple cases where a comparison between the Amazon (website) invoice and the tax calculation doesn’t exactly match up.
The rules can take about 10 minutes to clear caching. If you want to give it a try, wait until after 4:15 PM PST.
So far so good. I did find a couple of line items with 0 amounts, that are part of a cancelled order.
That condition is when Order Status == Cancelled
Hey you know while we are all here… I have seen quotes end up escaped too 
[Amazon Item] TOUCH TCH-11307 Series 1/2\" and 3/4\" Overlay Soft Close Concealed Kitchen Cabinet Door Hinges Frameless/Face Frame (10 Pairs (20 Pcs) Screw-on, Frameless 1/2\" Overlay)
In the report its
TOUCH TCH-11307 Series 1/2"" and 3/4"" Overlay Soft Close Concealed Kitchen Cabinet Door Hinges Frameless/Face Frame (10 Pairs (20 Pcs) Screw-on, Frameless 1/2"" Overlay)
Looks really good. I cleaned up the cancelled orders and everything matched up exactly as expected. Big thanks for taking time over the weekend to work on this one!! 
3 Likes
Looks good to me @randy. The new workflow you have done does the trick. Privacy Central is definitely less convenient given we can’t request a date range from Amazon and it takes a few days to get. But, assuming they continue to allow access, at least we’re back on track with what we’ve formerly been doing with Tiller. Thanks for the help.
1 Like
I’m glad we sorted it out, @eslimko. I think we’ve bought ourselves some more time with this workflow.
Thanks for your help and patience.
A few final updates for those of you following this thread…
- I updated the Amazon refund importer (the new data is very limited)
- I added new Account filter functionality
These changes are all detailed here.
1 Like
OK - I came across a chrome extension that may work for us: Amazon Order History Reporter @ https://chrome.google.com/webstore/detail/amazon-order-history-repo/mgkilgclilajckgnedgjgnfdokkgnibi/related?hl=en
I am going to mess around with it to see what I can do to make it work with Tiller.
That is a cool open source solution, @al1. I tried it about a year ago and it did a great job at scraping my account page. I’d love to hear more as you dig into this.
After reviewing, a multi-product purchase in one order all items are included in the one row also, there is not the amount paid for each item, only a total for order. Sadly this option will not accurately work as a replacement.
2 Likes
OK - I built a web scrape of order invoices using the webscrape chrome extension. I just got the downloaded data so I have not yet incorporated it into Tiller but you can use the webscrape if you want as to pull down your orders… this will not work with wholefoods orders as the invoice is a different layout.
Let me know if you have any feedback… I just cant wait for amazon to bring this feature back and needed a more immediate solution.
Instructions:
1 - install the chrome extension: https://chrome.google.com/webstore/detail/web-scraper-free-web-scra/jnhgnonknehpejjnehehllkliplmbmhn?hl=en
2 - read about using the extension: https://www.webscraper.io/
3 - import the scrape detail:
{"_id":“amazon_orders”,“startUrl”:[“https://www.amazon.com/gp/your-account/order-history/ref=ppx_yo_dt_b_pagination_1_2?ie=UTF8&orderFilter=months-6&search=&startIndex=[0-350:10]"],“selectors”:[{“id”:“element”,“type”:“SelectorElement”,“parentSelectors”:[“invoice”],“selector”:"[width=‘90%’] > tbody > tr > td”,“multiple”:false,“delay”:0},{“id”:“order”,“type”:“SelectorText”,“parentSelectors”:[“element”],“selector”:“tr:nth-of-type(2) td”,“multiple”:false,“regex”:"(.{19})\s*$",“delay”:0},{“id”:“date”,“type”:“SelectorText”,“parentSelectors”:[“element”],“selector”:“tr:nth-of-type(1) td”,“multiple”:false,“regex”:"\b(?!Order|Placed|:)\b\S+.",“delay”:0},{“id”:“pay_method”,“type”:“SelectorText”,“parentSelectors”:[“element”],“selector”:“nobr”,“multiple”:false,“regex”:"(?<=^…)(.)",“delay”:0},{“id”:“sub_total”,“type”:“SelectorText”,“parentSelectors”:[“element”],“selector”:“td tr:contains(‘Total before tax:’) td:nth-of-type(2)”,“multiple”:false,“regex”:"",“delay”:0},{“id”:“tax”,“type”:“SelectorText”,“parentSelectors”:[“element”],“selector”:“td td td td tr:contains(‘Estimated tax to be collected:’) td:nth-of-type(2)”,“multiple”:false,“regex”:"",“delay”:0},{“id”:“giftcard”,“type”:“SelectorText”,“parentSelectors”:[“element”],“selector”:“td tr:contains(‘Gift Card Amount:’) td:nth-of-type(2)”,“multiple”:false,“regex”:"",“delay”:0},{“id”:“item”,“type”:“SelectorText”,“parentSelectors”:[“item_element”],“selector”:“td[colspan=‘1’]”,“multiple”:true,“regex”:"",“delay”:0},{“id”:“price”,“type”:“SelectorText”,“parentSelectors”:[“item_element”],“selector”:“td[align][colspan]”,“multiple”:false,“regex”:"",“delay”:0},{“id”:“item_element”,“type”:“SelectorElement”,“parentSelectors”:[“element”],“selector”:“table[cellspacing=‘3’]”,“multiple”:true,“delay”:0},{“id”:“grand_total”,“type”:“SelectorText”,“parentSelectors”:[“element”],“selector”:“tr:contains(‘Grand Total:’) td:nth-of-type(2) b”,“multiple”:false,“regex”:"",“delay”:0},{“id”:“order_element”,“type”:“SelectorElement”,“parentSelectors”:[“element”],“selector”:“table[width][align][cellspacing=‘0’]:nth-of-type(1)”,“multiple”:false,“delay”:0},{“id”:“order_detail_element”,“type”:“SelectorElement”,“parentSelectors”:[“element”],“selector”:“table:nth-of-type(4) table[cellspacing=‘3’]”,“multiple”:false,“delay”:0},{“id”:“status”,“type”:“SelectorText”,“parentSelectors”:[“item_element”],“selector”:“center”,“multiple”:false,“regex”:"",“delay”:0},{“id”:“invoice”,“type”:“SelectorLink”,“parentSelectors”:["_root"],“selector”:".a-unordered-list a:nth-of-type(2)",“multiple”:true,“delay”:0}]}
4 - log into your amazon account.
5 - navigate to your orders page and adjust the filter (default is 6 months) - see #6 below and adjust accordingly: https://www.amazon.com/gp/css/order-history?ref_=nav_orders_first
6 - Adjust the webscrape Edit medadata url to reflect the number of pages to include all your orders - the scrape imported above is set for 350 as in “[0-350:10]” at the end of the URL. Each of the order pages includes 10 orders so adjust this accordingly. Read about this here: https://www.webscraper.io/documentation/scraping-a-site
7 - Start the “Scrape” orders page.
8 - This will take some time (maybe hours) depending on how many orders you are scraping.
9 - export to .csv
10 - use how you want.
I’m excited to hear more, @al1…
- Did you build the “scrape detail” JSON?
- How does the output look?
- What level of control do you have of the CSV output? E.g. could you remap it to match the old Amazon Order History report?
- When items are split across multiple charges, there is a “Credit Card transactions” area in the invoice footer? Can you access and log this section?
- Can you give a sense on how long the scrape operation took for your data set (e.g. in minutes/order)?
- What advantages do you expect from this workflow versus the new Privacy Central workflow?
Thanks for sharing your workflow with the community.
-
Did you build the “scrape detail” JSON?
YES -
How does the output look?
These are the current column headers: “web-scraper-order” “web-scraper-start-url” “order” “date” “pay_method” “sub_total” “tax” “giftcard” “item” “price” “grand_total” “status” “invoice” “invoice-href” -
What level of control do you have of the CSV output? E.g. could you remap it to match the old Amazon Order History report?
Have not gotten to that part yet but my idea is to import into Tiller sheets than pull a query off that into the correct column order… cleaning up any loose ends as well as preparation for next run. -
When items are split across multiple charges, there is a “Credit Card transactions” area in the invoice footer? Can you access and log this section?
the data shows card ending in XXXX by line item in the order -
Can you give a sense on how long the scrape operation took for your data set (e.g. in minutes/order)?
For 320 orders, maybe an hour or so? I let run and went to bed for the night… it was finished in the morning. -
What advantages do you expect from this workflow versus the new Privacy Central workflow?
You get the orders in an hour or so vs waiting days.