Retail Analytics - Inventory

Table Name: retail_analytics_inventory

The Retail Analytics Inventory table provides daily, weekly and monthly snapshots of Amazon’s inventory of your products. It is the main source for understanding sellable vs. unsellable inventory levels, aging, out of stock rates (Procurable OOS), and unhealthy inventory levels (as defined by Amazon).

*Note: some metrics are provided for WEEKLY and MONTHLY periods only, and are not available for DAILY:

• sell_through_rate
• unhealthy_inventory
• unhealthy_units

Most data in this table is ‘snapshot’ data, meaning that it should not be summed/aggregated. Instead, the value for the most recent date within the time period should be used. The exceptions are net_received and net_received units, which should be aggregated by PERIOD over the time-span of interest. See example query below for more information.

‘Procurable Product OOS’ and ‘unfilled_customer_order_units’ are only provided for Distributor View = ‘Manufacturing’, and are not available in Sourcing view.

Business questions & scenarios

What is this data useful for?

• Inventory coverage: How many weeks of cover does Amazon have for my key products?
• Understock: What products are we missing sales for because Amazon runs out of stock? How much pent up demand is there?
• Overstock: What are my unhealthy and aged products, where Amazon is likely to ask for cost support or a RMA?

Schema & update details

Historical data & change management

Data dictionary: fields in this table

Table & data nuances

• Time periods may not match: All retail analytics tables are available at DAILY, WEEKLY, MONTHLY granularity. There may be Amazon-driven discrepancies between different granularities that Amazon has not explained. For example, if you compare WEEKLY data to the sum of the seven DAILY periods in that week, the total net receipts, received units, etc. may not be an exact match. We recommend using the highest-granularity data you can, to minimize discrepancies and stakeholder questions.
• Manufacturing and sourcing view differences: This table contains data for both distributor views, manufacturing and sourcing. Each view behaves differently and contains different data. Learn more
• Amazon wants you to connect a product catalog. This table contains data by date by ASIN, but does not provide any product attributes like category, size, or even product title to help you identify or aggregate products. Amazon expects you to connect this table to a catalog/dimension table that maps ASINs to attributes, such as the Product Catalog table or our custom catalog upload.
• Large data volume. Inventory is the largest Retail Analytics data source because it contains daily rows for every product in your catalog. If you have a large catalog, we strongly recommend using weekly and monthly granularity where possible.

How to query this table

It’s important to filter this table by period, distributor view, and program to avoid duplication. The example query below will provide ASIN-level inventory data for a 5-day time period, correctly handling both summed and snapshot data in a single query.

1. Change distributor view: search for and distributor_view = 'Manufacturing' and replace ‘Manufacturing’ with ‘Sourcing’).See notes above regarding field availability by distributor view.
2. Change program: search for and program = ‘Amazon Retail’ and replace ‘Amazon Retail’ with ‘Amazon Business’ or another program.
3. Adjust dates as needed. The example query below focuses on a four day period from Black Friday 2023 to the following Tuesday. There are two date references - the first in the ‘agg’ subquery provides a range over which aggregable columns should be summed. The second date in the main query determines the snapshot date to be used for snapshot columns - ideally, this should be set to the last day of the date range used in the ‘agg’ subquery.

with agg as 

(select 
	ASIN
	, sum(net_received) as net_received
	, sum(net_received_units) as net_received_units	
	from retail_analytics_inventory 
	where period = 'DAILY'	
	and distributor_view = 'Manufacturing'
	and program = 'Amazon Retail'
	and id is not null
	and date_trunc('day', date)::date between '2023-11-24' and '2023-11-27'
	group by 1)

select 
	
	rai.asin
	, agg.net_received 
	, agg.net_received_units 
	, open_purchase_order_quantity 
	, sellable_on_hand_units 
	, sellable_on_hand_inventory 
	, aged_90plus_days_sellable_inventory 
	, aged_90plus_days_sellable_units 
	, unsellable_on_hand_inventory 
	, unsellable_on_hand_units 
	, unhealthy_inventory 
	, unhealthy_units 
		
	from retail_analytics_inventory  rai
	left join agg on agg.asin = rai.asin	
	
	where period = 'DAILY'	
	and distributor_view = 'Manufacturing'
	and program = 'Amazon Retail'
	and date_trunc('day', date)::date = '2023-11-27'

On this page

• Retail Analytics - Inventory
• Business questions & scenarios
• Schema & update details
• Historical data & change management
• Data dictionary: fields in this table
• Table & data nuances
• How to query this table

Related pages

Manufacturing View vs. Sourcing View