Magento Business Intelligence Help Center

Analyzing inventory levels

In this article, we demonstrate how to set up a dashboard that will provide insights to your current inventory.

This analysis contains advanced calculated columns that can only be created by a Magento BI analyst. Once you've completed the Getting Started section, file a support request with the subject [INVENTORY ANALYSIS], before getting started with this analysis.

Columns to track

  • cataloginventory_stock_item table
    • item_id
    • product_id
    • qty
  • catalog_product_entity table
    • entity_id
    • sku
    • created_at

Columns to create

  • catalog_product_entity table
    • Product's most recent order date
      • Select a definition: Max
      • Select table: sales_flat_order_item
      • Select column: Order's date
      • Path: catalog_product_entity.entity_id = sales_flat_order_item.product_id
      • Filter:
        • Order items we count
    • Seconds since product's most recent order date
      • Select a definition: Age
      • Select column: Product's most recent order date
    • Product's first order date will be created by an analyst as part of your [INVENTORY ANALYSIS] ticket
    • Product's lifetime number of items sold will be created by an analyst as part of your [INVENTORY ANALYSIS] ticket
    • Seconds since product's first order date will be created by an analyst as part of your [INVENTORY ANALYSIS] ticket
    • Avg products sold per week (all time) will be created by an analyst as part of your [INVENTORY ANALYSIS] ticket
  • cataloginventory_stock_item table
    • Sku
      • Select a definition: Joined Column
      • Create Path:
        • Many: cataloginventory_stock_item.product_id
        • One: catalog_product_entity.entity_id
      • Select table: catalog_product_entity
      • Select column: Sku
      • Path: catalog_product_entity.entity_id = cataloginventory_stock_item.product_id
    • Avg products sold per day (all time)
      • Select a definition: Joined Column
      • Select table: catalog_product_entity
      • Select column: Avg products sold per day (all time)
      • Path: catalog_product_entity.entity_id = cataloginventory_stock_item.product_id
    • Product's lifetime number of items sold
      • Select a definition: Joined Column
      • Select table: catalog_product_entity
      • Select column: Product's lifetime number of items sold
      • Path: catalog_product_entity.entity_id = cataloginventory_stock_item.product_id
    • Seconds since product's most recent order date
      • Select a definition: Joined Column
      • Select table: catalog_product_entity
      • Select column: Seconds since product's most recent order date
      • Path: catalog_product_entity.entity_id = cataloginventory_stock_item.product_id
    • Days on hand will be created by an analyst as part of your [INVENTORY ANALYSIS] ticket
    • Weeks on hand will be created by an analyst as part of your [INVENTORY ANALYSIS] ticket
  • Inventory on hand
    • In the cataloginventory_stock_item table
    • This metric performs a Sum
    • On the qty column
    • Ordered by the [None]
  • Avg products sold per day (all time)
    • In the cataloginventory_stock_item table
    • This metric performs a Average
    • On the Avg products sold per day (all time) column
    • Ordered by the [None]

Note: Make sure to add all new columns as dimensions to metrics before building new reports.

  • Inventory on hand by Sku
    • Metric A: Inventory on hand
      • Metric: Inventory on hand
    • Time period: All time
    • Interval: None
    • Group by: Sku, Days on hand
    • Chart Type: Table
  • Inventory with less than 2 weeks on hand (order now)
    • Metric A: Inventory on hand
      • Metric: Inventory on hand
      • Filter:
        • Weeks on hand Less Than 2
    • Time period: All time
    • Interval: None
    • Group by: Sku
    • Chart Type: Table
  • Inventory with more than 26 weeks on hand (order now)
    • Metric A: Inventory on hand
      • Metric: Inventory on hand
      • Filter:
        • Weeks on hand Greater Than 26
    • Time period: All time
    • Interval: None
    • Group by: Sku
    • Chart Type: Table

If you run into any questions while building this analysis, or simply want to engage our Professional Services team to build the analysis for you, simply open a support ticket here.

Was this article helpful?
0 out of 0 found this helpful

Comments

Powered by Zendesk