Amazon order history

February 22, 2022

One of my goals this year to spend less money on Amazon and buy things locally instead. Here’s how I’m keeping track.

Amazon has a privacy page where you can request your personal data, including your order history. For this purpose, I’m only interested in “Your Orders,” but there are many other records you can request a copy of.

Once requested, it may take a day or two before your data is available for download. The orders data comes in several files, and I’m interested in the “Retail.OrderHistory” CSV.

I’m using datasette to poke through my data.

Install datasette and csvs-to-sqlite:

$ pip install datasette
$ pip install csvs-to-sqlite

Convert CSV into a SQLite database:

$ csvs-to-sqlite Retail.OrderHistory.1.csv amz.db

Run datasette:

$ datasette amz.db

Datasette starts a local web server so now I have a simple interface for my data where I can browse or write my own SQL queries.

So, how much have I spent at Amazon over the years¹?

select
  sum([Total Owed])
from
  [Retail.OrderHistory.1]
where
 [Product Name] NOT LIKE '%reload%'

Just over $40,000.

In order to track progress on my goal of spending less, I wanted to know how much I spent and how many items I ordered per month during 2021.

select
  substr([Order Date],7,4) as Year,
  substr([Order Date],1,2) as Month,
  printf("%.2f", sum([Total Owed])) as Spend,
  sum(Quantity) as Items
from
  [Retail.OrderHistory.1]
where
    substr([Order Date],7,4) >= "2021" AND [Product Name] NOT LIKE "%reload%"
group by
  Year, Month
order by
  Year, Month

Datasette has buttons for quickly exporting the results of a query as JSON or CSV. Copying and pasting the CSV into Numbers, I can quickly generate a couple of graphs:

That’s the data for 2021. At the beginning of each month in 2022, I will re-request my data and run the queries again. Then I’ll be able to see the difference in monthly spending year-over-year.

Additional query

Since I have the data, what are the most expensive items that I’ve ordered from Amazon?

Five most expensive items:

select
  [Unit Price],
  [Product Name]
from
  [Retail.OrderHistory.1]
order by
  [Unit Price] desc
limit
  5

Mine:

Unit Price,Product Name
949.0,Canon EOS 70D Digital SLR Camera (Body Only)
549.99,2018 UPPAbaby Cruz Stroller - Emmett (Green Melange/Silver/Saddle Leather)
447.99,"DIY KIT: 240GB Mercury EXTREME Pro 3G SSD +OWC USB 2.0 Express 2.5"" Enclosure Ki"
433.36,Panasonic Lumix DMC-LX5 10.1 MP Digital Camera with 3.8x Optical Image Stabilized Zoom and 3.0-Inch LCD (Black)
399.96,Xbox 360 Limited Edition Call of Duty: Modern Warfare 3 Bundle