While trying to recreate a sale using the fulfilled shipments report for FBA, , I ran into some trouble with the data they provide. Normally, when a sale has multiple items, you would expect the quantity field to have a value larger than 1. Instead I found that a sale where I sold 6 calendars had 6 different line items. In trying to figure out why I realized that there was much more going on than I expected. My guess is that for this sale in particular, the customer added each calendar separately to the order instead of changing the quantity to 6.

The fields that Amazon includes in the Fulfilled Shipments Data report of concern are:

  • order_id – This is a unique id for each customer order
  • amazon_order_item_id – Refers to an item in the customer order
  • shipment_id – A unique identifier for shipments. An order can be split into multiple shipments.
  • shipment_item_id – An id for the items in an order. Quantities can be changed so this refers to the items in a particular shipment.
  • merchant_order_id – Specified by the seller when requesting a shipment through FBA for a sale not on Amazon.com.
  • merchant_order_item_id – An id referring to an item in the merchant order

When attempting to merge the data back together, dealing with merchant orders is fairly straight forward. There are just the two values and normally the order doesn’t get split. For an Amazon order though, it can get split up, and there can be multiple item_id’s that have the same ASIN, price, quantity, and more. Initially this caused my 6 calendar order to be reported as 1 calendar because every one of the rows in the report looked the same. Another order I had was split into a quantity of 2, and another line with quantity 6, and that sale was being reported as having sold 2 instead of 8.

All 4 values for the order and shipment need to be pulled together to ensure an accurate accounting for everything in the order. To ensure this, I created a unique key across all 6 of these columns in the database table. That let me more easily sum up the quantities, shipping costs, prices, and calculate what the total order cost me, what fees there were, and how much I made. If care is not given to dealing with these 4 different values, then your calculations will be way off for orders that have multiple items.