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
UI Report path | |
Amazon update frequency | Daily, with 48-72 hour latency. Learn more |
Reason update frequency | Every 8 hours |
Time Zone | Country local time zone |
Granularity [?] | 1 row per:
- ASIN
- Date
- Period
- Program
- Distributor View |
Historical data & change management
Time Period | DAILY | WEEKLY | MONTHLY |
History available from Amazon [?] | 1095 days | 6 weeks | 36 months |
Reason lookback period [?] | 14 days | 2 weeks | 2 months |
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.
- Change distributor view: search for
and distributor_view = 'Manufacturing'
and replace‘Manufacturing’
with‘Sourcing’).
See notes above regarding field availability by distributor view. - Change program: search for
and program = ‘Amazon Retail’
and replace‘Amazon Retail’
with‘Amazon Business’
or another program. - 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'