Odoo の倉庫ごとに在庫回転率を追跡する方法

By the end of this recipe, your Odoo 19 instance will compute inventory turnover ratio (cost of goods sold / average inventory value) split by warehouse, by product category, and by 30/60/90-day rolling windows, with slow-mover alerts firing when a SKU's days-on-hand crosses a configurable threshold. Skill required: SQL fluency + Odoo accounting + warehouse familiarity. Time required: 3 hours setup, 15 minutes per refresh review. ECOSIRE has built this for distributors and manufacturers managing 10,000+ SKUs across multiple warehouses, and the recipe below is the playbook.

The reason most "inventory turnover" reports are wrong: they use ending inventory instead of average inventory, or they pull "qty on hand" instead of valuation. Turnover is a financial ratio, so it must use accounting valuations (which respect costing method: FIFO, AVCO, or standard). The recipe below uses account_move_line joined to stock_valuation_layer for accuracy.

What you will need

• Odoo version: 17, 18, or 19 with stock_account (auto-installed) and at least 12 months of inventory transactions.
• Costing method set per product: AVCO recommended for most SKUs.
• Read-only Postgres connection for analytics.
• Power BI Desktop or Excel with PowerQuery.
• Time: 3 hours setup, 15 minutes per refresh.

Step-by-step

1. Build the COGS-by-month view

CREATE OR REPLACE VIEW bi.cogs_by_warehouse_month AS
SELECT
    DATE_TRUNC('month', svl.create_date)::date AS month,
    sm.warehouse_id,
    sw.name AS warehouse_name,
    pp.id AS product_id,
    pt.categ_id,
    SUM(svl.value) * -1 AS cogs   -- outgoing moves are negative; cogs is positive
FROM stock_valuation_layer svl
JOIN stock_move sm ON svl.stock_move_id = sm.id
JOIN stock_warehouse sw ON sm.warehouse_id = sw.id
JOIN product_product pp ON svl.product_id = pp.id
JOIN product_template pt ON pp.product_tmpl_id = pt.id
WHERE sm.location_dest_id IN (
    SELECT id FROM stock_location WHERE usage = 'customer'
)
GROUP BY 1,2,3,4,5;

This sums the value of every outbound move (sales, customer returns reversed) per warehouse, per month, per product. Verification: SELECT SUM(cogs) FROM bi.cogs_by_warehouse_month WHERE month = '2026-04-01' matches your P&L COGS for April 2026.

2. Build the average-inventory view

CREATE OR REPLACE VIEW bi.avg_inventory_by_warehouse_month AS
WITH eom AS (
    SELECT
        (DATE_TRUNC('month', d) + INTERVAL '1 month' - INTERVAL '1 day')::date AS eom_date
    FROM generate_series(
        (SELECT MIN(create_date) FROM stock_valuation_layer)::date,
        CURRENT_DATE,
        '1 month'::interval
    ) AS d
),
inventory_at_eom AS (
    SELECT
        eom.eom_date,
        sm.warehouse_id,
        SUM(svl.value) FILTER (WHERE svl.create_date <= eom.eom_date) AS inv_value
    FROM eom
    CROSS JOIN stock_valuation_layer svl
    JOIN stock_move sm ON svl.stock_move_id = sm.id
    GROUP BY eom.eom_date, sm.warehouse_id
)
SELECT
    DATE_TRUNC('month', eom_date)::date AS month,
    warehouse_id,
    AVG(inv_value) OVER (PARTITION BY warehouse_id ORDER BY eom_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg_inventory
FROM inventory_at_eom;

This computes average of beginning + ending inventory for the standard turnover formula. Verification: average matches a manual calculation from the inventory valuation report.

3. Compute the turnover ratio

CREATE OR REPLACE VIEW bi.inventory_turnover AS
SELECT
    cogs.month,
    cogs.warehouse_id,
    cogs.warehouse_name,
    SUM(cogs.cogs) AS monthly_cogs,
    inv.avg_inventory,
    CASE WHEN inv.avg_inventory > 0
         THEN SUM(cogs.cogs) / inv.avg_inventory
         ELSE NULL END AS turnover_ratio,
    CASE WHEN SUM(cogs.cogs) > 0
         THEN inv.avg_inventory * 30.0 / SUM(cogs.cogs)
         ELSE NULL END AS days_on_hand
FROM bi.cogs_by_warehouse_month cogs
JOIN bi.avg_inventory_by_warehouse_month inv
  USING (month, warehouse_id)
GROUP BY cogs.month, cogs.warehouse_id, cogs.warehouse_name, inv.avg_inventory;

For annualized turnover, multiply monthly ratio by 12. Days on hand is the inverse expressed in days.

Verification: a high-velocity warehouse shows turnover of 6 to 12, days_on_hand of 30 to 60. A slow warehouse shows turnover under 2.

4. Visualize in Power BI

Connect Power BI to bi.inventory_turnover. Create:

• Card visual: Turnover Ratio (current month) with comparison to prior period.
• Line chart: Turnover trend over 24 months by warehouse.
• Heatmap: Days on Hand by product category × warehouse.

Verification: warehouses with high turnover show green, slow ones show red.

5. Build slow-mover alerts

A SKU with days_on_hand > 180 is a slow mover. Tag it for clearance pricing or write-down review:

SELECT
    pp.id AS product_id,
    pt.name AS product_name,
    sw.name AS warehouse_name,
    SUM(sq.quantity * sq.product_value) AS inventory_value,
    LAST_VALUE(latest_move.date) OVER (PARTITION BY pp.id, sw.id ORDER BY latest_move.date) AS last_movement_date,
    CURRENT_DATE - latest_move.date::date AS days_since_last_movement
FROM stock_quant sq
JOIN product_product pp ON sq.product_id = pp.id
JOIN product_template pt ON pp.product_tmpl_id = pt.id
JOIN stock_warehouse sw ON sq.location_id IN (
    SELECT id FROM stock_location WHERE warehouse_id = sw.id
)
LEFT JOIN LATERAL (
    SELECT MAX(create_date) AS date FROM stock_move sm
    WHERE sm.product_id = pp.id AND sm.warehouse_id = sw.id
      AND sm.state = 'done' AND sm.location_dest_id IN (SELECT id FROM stock_location WHERE usage = 'customer')
) latest_move ON true
WHERE sq.quantity > 0
HAVING CURRENT_DATE - latest_move.date::date > 180
ORDER BY inventory_value DESC;

Verification: the slow-mover list matches the warehouse manager's intuition.

6. Surface in Odoo

Build an Odoo dashboard tile (see how to build an Odoo dashboard tile) showing the count of slow-mover SKUs and total locked-up capital.

@api.model
def _get_slow_mover_kpi(self):
    self.env.cr.execute("""
        SELECT COUNT(*), SUM(inventory_value)
        FROM bi.slow_movers
        WHERE days_since_last_movement > 180
    """)
    count, value = self.env.cr.fetchone()
    return {'count': count or 0, 'value': value or 0}

Verification: the tile shows a meaningful count for warehouses with stale stock.

7. Set up an Odoo automated action

Trigger an email to the warehouse manager when a SKU crosses 180 days of no movement:

class ProductSlowMoverCron(models.Model):
    _inherit = 'product.product'

    @api.model
    def _cron_check_slow_movers(self):
        self.env.cr.execute("SELECT product_id FROM bi.slow_movers WHERE days_since_last_movement = 181")
        for (pid,) in self.env.cr.fetchall():
            self.browse(pid).activity_schedule(
                'mail.mail_activity_data_todo',
                summary='SKU has not moved in 6 months — review for clearance',
                user_id=self.env.ref('stock.group_stock_manager').users[0].id,
            )

Verification: the activity appears on the slow-mover product as soon as it crosses the threshold.

8. Add product-category drill-down

Pivot the turnover view by categ_id. Categories with low turnover (cost categories like spare parts) are normal; sales categories with low turnover need investigation.

Verification: drill-down works in Power BI matrix.

Common mistakes

• Using qty_available instead of valuation. Different costing methods give different valuations for the same physical quantity.
• Annualizing monthly turnover by ×12 in volatile businesses. Seasonal businesses should use trailing 12 months instead.
• Including non-stockable products. Filter out services and digital products from inventory views.
• Counting backorders as inventory. Use quantity from stock.quant, not virtual_available from product.product.
• Ignoring scrap/write-off moves. They reduce inventory without flowing through COGS — handle separately.

Going further

ABC analysis: classify SKUs as A (top 80% revenue), B (next 15%), C (last 5%). Different turnover targets per class. A-items: target turnover 12+, daily count cycle. B-items: 6-8, weekly count. C-items: 2-4, monthly count. The classification updates monthly via a dedicated cron.

FEFO for perishables: pair turnover analysis with expiry-date tracking — slow + expiring = urgent clearance. Build a "Risk Stock" view that sums value of products with both days_on_hand > 90 AND expiry_date < today + 30 days. The result is the dollar value of inventory you'll write off if you don't act this week.

Multi-channel attribution: split COGS by sales channel (online, retail, wholesale) to see which channel drives turnover. A-items might turn 18x via online and 4x via wholesale, suggesting channel-specific stocking strategies. Add team_id or a custom channel_id to the COGS view's GROUP BY.

Stockout cost modeling: pair turnover with stockout incidents to find the right safety-stock level. For each SKU, compute the cost of stockout (lost sale + customer dissatisfaction proxy) vs the cost of carry (capital cost + storage + obsolescence risk). The optimal safety stock balances them.

Reorder point optimization: feed turnover and demand variability into a (s, S) inventory model that recommends reorder points per SKU. Update monthly. ECOSIRE has built this for distributor clients with 5,000+ SKUs.

Dead stock auction: products with days_on_hand > 365 are typically write-off candidates. Build a workflow that exports the list weekly to a B2B liquidation channel. Recover 10-30% of book value instead of zero.

Velocity-based slotting: pair turnover with warehouse layout — high-turnover items go in golden zones (waist height, near pick conveyor), slow items in deep storage. Periodic re-slotting based on the latest turnover data drops pick-line travel time 20-40%.

Demand forecasting integration: pair historical turnover with seasonality decomposition (statsmodels in Python) to forecast next-quarter demand. Drives purchase-order volume decisions and reorder timing.

For full inventory analytics setup including ABC analysis, demand forecasting, and reorder-point automation, ECOSIRE custom Odoo development builds the entire stack. Pair this with how to configure Odoo warehouse routes.

Frequently Asked Questions

What is a healthy turnover ratio?

Industry-dependent. Grocery: 12-30. Apparel: 4-6. Industrial parts: 2-4. Compare against your industry benchmark, not absolute numbers.

How often should I refresh?

Daily for operational alerts. Weekly for trend analysis. Monthly for board reporting.

How do I handle stock transfers between warehouses?

The valuation views above filter on location_dest_id being a customer location. Internal transfers don't count as COGS — they're captured separately.

Can I split by lot or serial number?

Yes — join stock.lot to the move lines. Useful for perishables or batch-tracked SKUs.

For complex inventory analytics including ML demand forecasting and dynamic safety-stock, ECOSIRE Power BI services build the entire stack. Or read how to calculate COGS by product category.