So berechnen Sie die COGS nach Produktkategorie in Odoo

By the end of this recipe, your Odoo 19 instance will produce a Cost of Goods Sold report split by product category, customer segment, sales channel, and time period — letting you instantly answer "what's the gross margin on Category X across last quarter?" without exporting to Excel. Skill required: SQL fluency + intermediate Odoo accounting. Time required: 2 hours setup, 15 minutes per refresh. ECOSIRE has built this for retail, manufacturing, and distribution clients managing multi-thousand-SKU catalogs, and the recipe below is the playbook.

The trap that most teams fall into: pulling COGS from account.move.line filtered by account code instead of from stock.valuation.layer. Account-based COGS works for companies with simple chart of accounts but breaks when the same SKU posts to different COGS accounts depending on context. Valuation-layer COGS is always correct because it's the source of truth in Odoo's costing engine.

What you will need

• Odoo version: 17, 18, or 19 with stock_account (auto-installed).
• Costing method: AVCO or FIFO at minimum (standard cost works but introduces variance line items).
• Categorization: every product belongs to a product.category with a meaningful hierarchy.
• Postgres read access for SQL views.
• Time: 2 hours setup.

Step-by-step

1. Build the COGS-by-category view

CREATE OR REPLACE VIEW bi.cogs_by_category AS
SELECT
    DATE_TRUNC('month', svl.create_date)::date AS month,
    pc.id AS category_id,
    pc.complete_name AS category_path,
    sm.warehouse_id,
    so.team_id AS sales_team_id,
    so.user_id AS salesperson_id,
    so.partner_id AS customer_id,
    SUM(svl.value) * -1 AS cogs,
    COUNT(DISTINCT svl.stock_move_id) AS move_count
FROM stock_valuation_layer svl
JOIN stock_move sm ON svl.stock_move_id = sm.id
JOIN product_product pp ON svl.product_id = pp.id
JOIN product_template pt ON pp.product_tmpl_id = pt.id
JOIN product_category pc ON pt.categ_id = pc.id
LEFT JOIN sale_order_line sol ON sm.sale_line_id = sol.id
LEFT JOIN sale_order so ON sol.order_id = so.id
WHERE sm.location_dest_id IN (
    SELECT id FROM stock_location WHERE usage = 'customer'
)
AND svl.value < 0   -- outgoing moves have negative value
GROUP BY 1,2,3,4,5,6,7;

Verification: SELECT SUM(cogs) FROM bi.cogs_by_category WHERE month = '2026-04-01' matches the P&L's COGS line for April 2026 within rounding tolerance.

2. Pair with revenue for gross margin

CREATE OR REPLACE VIEW bi.revenue_by_category AS
SELECT
    DATE_TRUNC('month', am.invoice_date)::date AS month,
    pc.id AS category_id,
    pc.complete_name AS category_path,
    am.team_id,
    am.invoice_user_id,
    am.partner_id,
    SUM(aml.price_subtotal) AS revenue,
    SUM(aml.quantity) AS qty_sold
FROM account_move am
JOIN account_move_line aml ON am.id = aml.move_id
JOIN product_product pp ON aml.product_id = pp.id
JOIN product_template pt ON pp.product_tmpl_id = pt.id
JOIN product_category pc ON pt.categ_id = pc.id
WHERE am.state = 'posted'
  AND am.move_type IN ('out_invoice', 'out_refund')
  AND aml.product_id IS NOT NULL
GROUP BY 1,2,3,4,5,6;

CREATE OR REPLACE VIEW bi.gross_margin_by_category AS
SELECT
    r.month,
    r.category_id,
    r.category_path,
    r.revenue,
    COALESCE(c.cogs, 0) AS cogs,
    r.revenue - COALESCE(c.cogs, 0) AS gross_margin,
    CASE WHEN r.revenue > 0
         THEN ROUND(100.0 * (r.revenue - COALESCE(c.cogs, 0)) / r.revenue, 2)
         ELSE NULL END AS margin_pct
FROM bi.revenue_by_category r
LEFT JOIN bi.cogs_by_category c USING (month, category_id);

Verification: gross margin per category looks reasonable (e.g., software = 80%+, hardware = 25-40%, services = 60-80%).

3. Build an Odoo Pivot view

Create a custom view on bi.gross_margin_by_category. Expose it via a small custom module:

from odoo import models, fields

class GrossMarginByCategory(models.Model):
    _name = 'bi.gross.margin.category'
    _auto = False
    _description = 'Gross Margin by Category (read-only view)'

    month = fields.Date()
    category_id = fields.Many2one('product.category')
    category_path = fields.Char()
    revenue = fields.Float()
    cogs = fields.Float()
    gross_margin = fields.Float()
    margin_pct = fields.Float()

    def init(self):
        self.env.cr.execute("""
            CREATE OR REPLACE VIEW bi_gross_margin_category AS
            SELECT row_number() OVER () AS id, * FROM bi.gross_margin_by_category
        """)

In XML, add a Pivot view with category_path as row, month as column, gross_margin as measure. Verification: the pivot renders correctly and totals match the SQL output.

4. Filter by sales team / channel

Add a slicer for team_id to compare margins across channels (online, retail, wholesale, etc.):

SELECT category_path, SUM(revenue) AS rev, SUM(cogs) AS cogs,
       ROUND(100.0 * (SUM(revenue) - SUM(cogs)) / SUM(revenue), 2) AS margin_pct
FROM bi.gross_margin_by_category gm
JOIN crm_team t ON gm.team_id = t.id
WHERE t.name = 'Online'
  AND month >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY category_path
ORDER BY rev DESC;

Verification: online channel typically has different margin profile than retail (lower for some categories, higher for others).

5. Identify margin leakage

Find products where actual margin is well below expected:

SELECT
    pt.name,
    pt.list_price AS expected_price,
    AVG(aml.price_unit) AS avg_actual_price,
    pt.standard_price AS expected_cost,
    AVG(svl.value * -1.0 / svl.quantity) AS avg_actual_cost,
    ROUND(100.0 * (1 - pt.standard_price / pt.list_price), 1) AS expected_margin_pct,
    ROUND(100.0 * (1 - AVG(svl.value * -1.0 / svl.quantity) / AVG(aml.price_unit)), 1) AS actual_margin_pct
FROM product_template pt
JOIN product_product pp ON pp.product_tmpl_id = pt.id
JOIN account_move_line aml ON aml.product_id = pp.id
JOIN stock_valuation_layer svl ON svl.product_id = pp.id
JOIN account_move am ON aml.move_id = am.id
WHERE am.state = 'posted' AND am.invoice_date >= CURRENT_DATE - INTERVAL '90 days'
  AND svl.create_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY pt.id
HAVING ROUND(100.0 * (1 - pt.standard_price / pt.list_price), 1) -
       ROUND(100.0 * (1 - AVG(svl.value * -1.0 / svl.quantity) / AVG(aml.price_unit)), 1) > 5;

This shows products where the actual margin is 5+ points below expected. Verification: these are products to investigate (excessive discounting? cost increased?).

6. Schedule a monthly Slack report

Cron job that pushes top-5 best and worst margin categories:

@api.model
def _cron_post_margin_report(self):
    import requests
    self.env.cr.execute("""
        SELECT category_path, ROUND(100.0 * SUM(gross_margin) / NULLIF(SUM(revenue),0), 2)
        FROM bi.gross_margin_by_category
        WHERE month = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
        GROUP BY category_path
        ORDER BY 2 DESC LIMIT 5
    """)
    best = self.env.cr.fetchall()
    requests.post(slack_webhook, json={'text': f'Top margins last month: {best}'})

Verification: the Slack message arrives on the 5th with meaningful data.

7. Drill from Odoo dashboard

Add an Odoo dashboard tile linking to the Pivot view. Click the tile, see the matrix.

8. Audit the COGS line

For audit, every COGS aggregate must drill to constituent moves. Use the Odoo Stock Valuation layer drill-down: Inventory > Reporting > Stock Valuation > group by category.

Common mistakes

• Filtering COGS by account code instead of valuation layer. Account codes can change; valuation layers are immutable.
• Forgetting refunds. Customer returns reduce COGS; missing them overstates COGS by the return percentage.
• Mixing fiscal years. Inventory valuation rules can change at year-end (LIFO reserves in US-GAAP). Pin the report to consistent rules.
• Ignoring stock losses. Scrap moves don't flow through COGS by default — they're a separate "Inventory Loss" expense.
• Pulling list_price instead of actual sold price. Discounts and pricelists change actuals.

Going further

Channel margin comparison: matrix of category × channel showing margin by sales rep / store / region. Reveals which combinations are most profitable. Often surprises sales leaders — the "high-volume" channel can have lower margin than the "boutique" one.

Customer segment margin: tag customers (VIP, B2B, B2C) and compute margin per segment. B2B customers typically have lower margin per transaction but higher LTV; B2C is opposite. Pair with CLV to make full economic comparisons.

Product-level margin trend: month-over-month margin per SKU to flag deteriorating products. Build a control chart showing 12-week margin moving average plus 2-sigma bands. Products outside the bands are anomalies needing investigation.

Predictive margin: pair with cost-trend models to forecast next-quarter margin under various scenarios. Use a vector autoregression on historical component costs to project future costs, then propagate through the BoM rollup.

Mix-vs-margin variance analysis: when overall margin moves, decompose into mix variance (product mix changed) vs price variance (margin per unit changed). Standard finance technique that surfaces actionable causes.

Promotion ROI: tag invoices with promo codes and compare margin during vs outside promotion periods. Often reveals that "successful" promotions actually destroyed margin without growing volume.

Vendor cost benchmarking: when a single component is sourced from multiple vendors, compute the per-vendor margin contribution. Lets you negotiate harder with the higher-cost vendor or pivot volume.

Currency hedging impact: for businesses sourcing in foreign currencies, separate FX impact from underlying cost changes. Hedging program effectiveness becomes visible in the margin numbers.

Activity-based costing layer: most COGS reports use direct cost only. Layer in fully-loaded cost (allocated overhead, customer service cost, returns processing) for "true" margin. The truth is often that some "high-margin" segments are actually unprofitable after full costs.

For full margin analytics setup including channel attribution and predictive models, ECOSIRE Power BI services build the entire stack. Pair this with how to build a balance sheet template.

Frequently Asked Questions

Why does my COGS not equal sum of (qty × cost)?

The stock.valuation.layer records actual cost at move time. product.standard_price is the current cost, which has changed since some moves happened. Always use SVL for COGS, not standard_price × qty.

How do I handle bundle/kit products?

Kits explode into their component moves at delivery. Sum the component COGS to get the kit COGS.

Can I do this in DirectQuery mode in Power BI?

Yes — but performance is slow on tables with millions of SVL rows. Materialize the view in Postgres or use Import mode.

How do I split landed costs into COGS?

If you use Odoo's Landed Costs feature, the landed cost adjustments hit stock.valuation.layer automatically. They flow into the queries above without extra work.

For full margin analytics with channel attribution, ECOSIRE Power BI services ship pre-built dashboards. Or read how to track inventory turnover by warehouse.