This topic demonstrates how to set up a dashboard which provides insights into your current inventory and contains instructions for clients on both the legacy architecture or new architecture. You are on the legacy architecture if you do not have the Data Warehouse Views option under the Manage Data menu. If you are on the legacy architecture, submit a new support request with the subject INVENTORY ANALYSIS once you reach the designated section in the Calculated columns instructions below.
cataloginventory_stock_item table:
item_id
product_id
qty
catalog_product_entity table:
entity_id
sku
created_at
catalog_product_entity table:
Product's most recent order date
Many to One
MAX
sales_order_item.product_id => catalog_product_entity.entity_id
created_at
Ordered products we count
Product's first order date
Many to One
MIN
sales_order_item.product_id => catalog_product_entity.entity_id
created_at
Ordered products we count
Seconds since product's most recent order date
Same Table
AGE
Product's most recent order date
Product's lifetime number of items sold
Many to One
SUM
sales_order_item.product_id => catalog_product_entity.entity_id
qty_ordered
Ordered products we count
Avg products sold per week (all time)
Same Table
CALCULATION
Product's lifetime number of items sold
Product's first order date
Decimal
cataloginventory_stock_item table:
Sku
One to Many
JOINED_COLUMN
cataloginventory_stock_item.product_id => catalog_product_entity.entity_id
sku
Product's lifetime number of items sold
One to Many
JOINED_COLUMN
cataloginventory_stock_item.product_id => catalog_product_entity.entity_id
Product's lifetime number of items sold
Seconds since product's most recent order date
One to Many
JOINED_COLUMN
cataloginventory_stock_item.product_id => catalog_product_entity.entity_id
Seconds since product's most recent order date
Avg products sold per week (all time)
One to Many
JOINED_COLUMN
cataloginventory_stock_item.product_id => catalog_product_entity.entity_id
Avg products sold per week (all time)
Weeks on hand
Same Table
CALCULATION
qty
Avg products sold per week (all time)
Decimal
catalog_product_entity table:
Product's most recent order date
Many to One
MAX
sales_order_item.product_id => catalog_product_entity.entity_id
created_at
Ordered products we count
Product's first order date
Many to One
MIN
sales_order_item.product_id => catalog_product_entity.entity_id
created_at
Ordered products we count
Seconds since product's most recent order date
Same Table
AGE
Product's most recent order date
Product's lifetime number of items sold
Many to One
SUM
sales_order_item.product_id => catalog_product_entity.entity_id
qty_ordered
Ordered products we count
Avg products sold per week (all time)
cataloginventory_stock_item table:
Sku
One to Many
JOINED_COLUMN
cataloginventory_stock_item.product_id => catalog_product_entity.entity_id
sku
Product's lifetime number of items sold
One to Many
JOINED_COLUMN
cataloginventory_stock_item.product_id => catalog_product_entity.entity_id
Product's lifetime number of items sold
Seconds since product's most recent order date
One to Many
JOINED_COLUMN
cataloginventory_stock_item.product_id => catalog_product_entity.entity_id
Seconds since product's most recent order date
Avg products sold per week (all time)
One to Many
JOINED_COLUMN
cataloginventory_stock_item.product_id => catalog_product_entity.entity_id
Avg products sold per week (all time)
Weeks on hand
Inventory on hand
: this metric performs a
qty
column ordered by theInventory on hand by sku
Inventory on hand
All time
None
Sku
Weeks on hand
Table
Inventory with less than 2 weeks on hand (order now)
Metric: Inventory on hand
Weeks on hand
< 2
Time period: All time
Time interval: None
Group by: Sku
Chart type: Table
Inventory with more than 26 weeks on hand (put on sale)
Metric: Inventory on hand
Weeks on hand
> 26
Time period: All time
Time interval: None
Group by: Sku
Chart type: Table
If you run into any questions while building this analysis, or simply want to engage the Professional Services team, contact support.