@heather, you’re not provided a method to select a date range, or even just the order history for that matter. AFAICT It’s basically a full GDPR/CCPA type of request. I imagine its not a very optimized process on Amazon’s backend either, considering how long it takes.
The good new is that I’ve just posted an update to the Import CSV Line Items workflow that (mostly) works for the new Amazon Privacy Central CSV file. You will need to reload your browser tab to get the freshly-updated version of the Tiller Money Labs add-on that can run the new workflow.
The (very bad) news is… the more time I spend with the new format, the more I hate it. I have documented the many issues with this significant service downgrade in this topic.
TL;DR… The new format sometimes lacks key pricing fields making the import workflow lossy— possibly lossy enough to be unusable. Again, read more here if you want the details.
I think there may be some math we can use within the import workflow to leverage more dependable pricing fields, but I’m afraid the code modifications are then non-trivial and will require more time.
Sorry to not have a better solution, but Amazon has really crippled the order history that is available to customers.
===========
Actually, I’m pulling the new rule down for a bit. I just realized a problem with the way I mapped the fields. Sorry for the false start…
2 Likes
@randy
I appreciate the work you are putting forth to making this work. I recently received my Privacy Central report and I’d like to process all of our August purchases, but I’m going to wait until you release your updated workflow.
Years ago, Amazon maintained an e-mail address jeff@amazon.com that the man himself actually read for complaints. I don’t know if the company still treats e-mails to that address as seriously as they once did but I will send an e-mail there with a complaint on this topic. In my 23 years of using Amazon, this is by far the most frustrating experience I have had.
Thanks for your patience, everyone.
I’m getting closer on this but, after changing the importer and comparing to my personal transactions, I keep finding my assumptions about the CSV contents are wrong around pricing.
The report provides these price values:
- Price
- Price Tax
- Shipping Charge
- Item Subtotal
- Item Subtotal Tax
The report’s Price field appears to be the unit price and must be multiplied by Quantity to render the cost for that line item.
I was surprised the the amount paid is just Price and not Price + Price Tax— essentially, the Price field includes Price Tax.
I thought that the Item Subtotal would be the Price multiplied by the Quantity for a given product. Strangely, Item Subtotal seems to include other items on the same order… so it cannot be used as part of the line-item price (i.e. it is useless).
Given what I know now, my plan is to:
- Leverage the
Shipping Chargein the line-item total as it appears to be discrete for each line item (i.e. not a blanket shipping charge for all items in the order) - Use (
Price*Quantity) +Shipping Chargefor the Tiller-spreadsheetAmountfield - Ignore
Price Taxas I believe it is included inPrice - Assume that coupons, discounts and gift cards are somehow addressed in the
Pricefield— honestly, there is not a lot to work with here if the price calculation is complex
I appreciate what you said, @eslimko. I think it is best to wait a bit longer for some data that is mapped more intelligently. I think I’m getting close.
As someone who has spent considerable time building reports, dashboards, and trying to derive general intelligence from the AWS Cost & Usage Report, I can confirm that ambiguity, inconsistency, and fudge factors are a hallmark of Amazon CSV reporting.
2 Likes
Making headway this morning. I’m pretty comfortable at this point with the quality of the Amazon reporting— excepting coupons, discounts & gift cards which I have no data to use for testing.
The only thing delaying publishing is that I had to heavily rework the guts of the importer— it’s much more sophisticated 
now!— and I need to test with the other supported file formats to make sure I didn’t break something unintentionally.
Hope to post this afternoon.
2 Likes
Alright… new solution is published to help Tiller Money customers get their Amazon data so they can use our Line-item CSV importer and get back to breaking out their Amazon spending. 
Earlier in this thread, I flagged a missing data issue that resulted in lost line items. I’m satisfied that that issue is resolved by using different fields— I do not believe this version will drop transactions.
I recommend reloading your spreadsheet browser tab to ensure you pull down the new version of the add-on.
Please let me know if you encounter any discrepancies, crashes or other issues.
Thanks for your patience with this.
P.S. The changes have the potential to affect other importer workflows (e.g. Paypal, YNAB, etc) so keep an eye out for emergent issues in those workflows as well.
4 Likes
@randy
You’ll forgive me for my simplistic question, but I don’t know how to “reload my browser tab.” What does that mean?
And after I do, I am importing a csv file, as I did previously?
Your browser stores a version of the add-on from when the spreadsheet was first loaded. If I publish a new version and your tab in Chrome is not reloaded, you will still be running the old version. So just hit refresh in your web browser if the spreadsheet has been open for a while.
Hah, yea yea, you literally meant re-load the tab. I gotcha. I’ll give it a go!
Ok. Can’t wait to hear if it works for you.
@randy
OK, I’m getting close. When I see the “Categorize purchase offsets as:” I get two choices as follows: A and Transfer. The way I had previously set this up is that I have a category called Amazon which is of Transfer type, and I always selected Amazon for this categorization. Now I have “A” which is not a category which exists on my Categories sheet. So, before I go forward, I figured I’d ask you if there may be some bug in your implementation, or if I should select “A” assuming it is Amazon, or if I should do this differently than I have in the past and select “Transfer.”
Thoughts?
Good catch. This is a 
.
I can fix this though I’m not sure I can do that today. If you are eager to move forward today, as a workaround, you can just apply a filter to your sheet Category = 'A' and then apply Amazon to those transactions.
Thanks for flagging this, @eslimko.
Actually… I take that back… Looks like a really easy fix.
Give me 5 minutes.
1 Like
It looks like tax isn’t getting included.
I’ve got this from the old report:
[Amazon Order] 111-5792349-0729010 (1 items) Amazon $16.41
The new import gives me:
[Amazon Order] 111-5792349-0729010 (1 items) A $14.99
##### Order Summary
Item(s) Subtotal: $14.99
Shipping & Handling: $0.00
Total before tax: $14.99
Estimated tax to be collected: $1.42
Grand Total: $16.41
Thanks for the feedback, @kraig. I think I’ve fixed the Category = 'A' issue. You’ll just need to reload the browser tab with your spreadsheet before trying.
Can you review the line in the CSV that you reference and let me know what columns should be added to get to the sum of $16.41? Right now, the equation I’m using is Quantity * Price + Shipping Charge.
I’m hoping all the data is present to get to $16.41.
Let me know.
I went ahead and processed by 139 purchases in August from Amazon. Perhaps as @kraig pointed out I did not drag along the tax with any of these orders but I haven’t sorted that out yet.
And the Amazon category is working now, @eslimko?
(Looks like @kraig pulled his data in before that fix… or maybe it isn’t fixed…)
I’m not sure what the difference between Price and Item Subtotal is, but you could take either of them with the tax to get to the 16.41.
"Amazon.com","111-5792349-0729010","2020-07-31 02:33 UTC","","USD","14.99","1.42","0","14.99","1.42",...