Magento Sales-Tabellen korrekt auswerten: Orders, Invoices, Shipments, Credits

Warum Magento Sales SQL oft falsch ausgewertet wird

Magento-Umsatzberichte per SQL zu erstellen klingt einfach: einfach alle Bestellungen summieren. In der Praxis führt dieser naive Ansatz zu massiv falschen Zahlen. Stornierte Bestellungen werden als Umsatz gezählt, Rückerstattungen fehlen, Währungsunterschiede bei Multi-Store-Setups werden ignoriert, und Teilrechnungen werden doppelt erfasst.

Dieser Artikel erklärt die Magento Sales SQL-Tabellen im Detail: Was bedeuten die wichtigen Spalten in sales_order? Welche Quantity-Felder in sales_order_item sind für die Umsatzberechnung relevant? Wie müssen Rechnungen, Sendungen und Rückerstattungen korrekt einbezogen werden? Am Ende steht eine vollständige Revenue-Report-Query, die diese Fallstricke alle vermeidet.

1. sales_order: Wichtige Spalten im Detail

Die Tabelle sales_order ist das Herzstück jedes Magento Revenue-Reports. Sie enthält nicht nur den Bestellbetrag, sondern auch Informationen über den aktuellen Stand der Verarbeitung.

-- Überblick über die wichtigsten Spalten in sales_order
SELECT
    entity_id,
    increment_id,            -- sichtbare Bestellnummer '100012345'
    store_id,
    customer_id,             -- NULL = Gast
    customer_email,
    state,                   -- interner Systemzustand (IMMER für Filter nutzen!)
    status,                  -- konfigurierbares Label (nie für Aggregation nutzen!)

    -- Beträge in Basiswährung (für Multi-Store-Reports nutzen!)
    base_grand_total,        -- Gesamtbetrag inkl. Steuer, Versand, abzgl. Rabatt
    base_subtotal,           -- Artikelsumme ohne Steuer und Versand
    base_tax_amount,         -- Steuerbetrag
    base_shipping_amount,    -- Versandkosten
    base_discount_amount,    -- Rabattbetrag (negativ)
    base_total_paid,         -- tatsächlich bezahlter Betrag
    base_total_refunded,     -- bereits rückerstatteter Betrag
    base_total_due,          -- noch offener Betrag

    -- Beträge in Store-Währung (nur für Single-Currency-Reports)
    grand_total,
    subtotal,
    tax_amount,
    shipping_amount,
    discount_amount,

    -- Währungsinfo
    base_currency_code,      -- z.B. 'EUR'
    order_currency_code,     -- z.B. 'USD' (kann abweichen bei Multi-Currency)
    base_to_order_rate,      -- Wechselkurs zum Zeitpunkt der Bestellung

    -- Mengen
    total_qty_ordered,
    total_item_count,

    -- Zeitstempel
    created_at,
    updated_at
FROM sales_order
WHERE increment_id = '100012345';

2. state vs. status: Der häufigste Denkfehler

Der meistgemachte Fehler bei Magento Revenue-Reports ist, auf status statt auf state zu filtern. state ist der interne, unveränderliche Systemzustand. status ist ein shopbetreiber-konfigurierbares Label, das sich ändern kann und je nach Installation unterschiedlich heißt.

-- Alle möglichen state-Werte und ihre Bedeutung
SELECT DISTINCT state, status, COUNT(*) AS order_count
FROM sales_order
GROUP BY state, status
ORDER BY state, COUNT(*) DESC;

-- state-Werte in Magento:
-- 'new'            → frisch eingegangen, noch nicht verarbeitet
-- 'pending_payment'→ wartet auf Zahlungsbestätigung
-- 'processing'     → Zahlung bestätigt, wird bearbeitet
-- 'complete'       → vollständig versendet und abgeschlossen
-- 'closed'         → vollständig rückerstattet (Credit Memo erstellt)
-- 'canceled'       → storniert
-- 'holded'         → manuell gesperrt
-- 'payment_review' → Zahlung in manueller Prüfung

-- FALSCH: Auf status filtern (inkonsistent, shop-abhängig)
SELECT SUM(base_grand_total) AS revenue
FROM sales_order
WHERE status = 'complete';  -- Kann in einem Shop 'complete' heißen, im anderen 'done'!

-- RICHTIG: Auf state filtern
SELECT SUM(base_grand_total) AS revenue
FROM sales_order
WHERE state IN ('processing', 'complete')
  AND created_at >= '2025-01-01'
  AND created_at < '2025-02-01';

3. base_* vs. store-Währungsspalten

In Magento gibt es für jeden Geldbetrag zwei Varianten: den Betrag in der Basiswährung (base_*-Präfix) und den Betrag in der Store-Währung (ohne Präfix). Bei Shops mit mehreren Währungen führt die Verwendung der falschen Spalten zu falschen Summen.

-- Warnung: Währungsunterschiede in einem Multi-Currency-Shop sichtbar machen
SELECT
    order_currency_code,
    base_currency_code,
    base_to_order_rate,
    COUNT(*) AS order_count,
    SUM(grand_total)      AS sum_store_currency,
    SUM(base_grand_total) AS sum_base_currency
FROM sales_order
WHERE state IN ('processing', 'complete')
  AND created_at >= '2025-01-01'
GROUP BY order_currency_code, base_currency_code, base_to_order_rate
ORDER BY order_count DESC;

-- Fazit: Wenn order_currency_code != base_currency_code gilt:
-- Immer SUM(base_grand_total) nutzen für vergleichbare Summen!

4. sales_order_item: qty-Spalten richtig verstehen

Die Mengen-Spalten in sales_order_item sind entscheidend für die korrekte Umsatzberechnung auf Positionsebene. Alle Mengen müssen sich zu qty_ordered addieren.

-- Mengen-Aufschlüsselung für alle Positionen einer Bestellung
SELECT
    sku,
    name,
    product_type,
    qty_ordered,
    qty_invoiced,
    qty_shipped,
    qty_refunded,
    qty_canceled,
    -- Konsistenzcheck: Summe muss qty_ordered ergeben
    (qty_invoiced + qty_refunded + qty_canceled +
     (qty_ordered - qty_invoiced - qty_canceled)) AS check_sum,
    -- Netto-umsatzrelevante Menge:
    (qty_invoiced - qty_refunded)                AS net_revenue_qty,
    price,
    row_total,
    base_row_total,
    -- Netto-Umsatz dieser Position:
    (qty_invoiced - qty_refunded) * price        AS net_row_revenue
FROM sales_order_item
WHERE order_id = (SELECT entity_id FROM sales_order WHERE increment_id = '100012345')
  AND parent_item_id IS NULL   -- keine untergeordneten Varianten
ORDER BY item_id;

5. sales_invoice: Mehrere Rechnungen pro Bestellung

Eine Bestellung kann in Magento mehrere Rechnungen haben. Wer Umsatz aus Rechnungen berechnet, muss aufpassen, keine Bestellung doppelt zu zählen.

-- Rechnungsübersicht mit Bestellverknüpfung
SELECT
    si.entity_id       AS invoice_id,
    si.increment_id    AS invoice_number,
    so.increment_id    AS order_number,
    so.state           AS order_state,
    si.state           AS invoice_state,  -- 1=pending, 2=paid, 3=canceled
    si.base_grand_total,
    si.grand_total,
    si.total_qty,
    si.created_at
FROM sales_invoice si
JOIN sales_order so ON so.entity_id = si.order_id
WHERE si.created_at >= '2025-01-01'
  AND si.created_at < '2025-02-01'
  AND si.state = 2   -- nur bezahlte Rechnungen
ORDER BY si.created_at DESC;

-- ACHTUNG: Falscher Ansatz - summiert Rechnungen direkt
-- Bei einer Bestellung mit 2 Teilrechnungen wird die Bestellung doppelt gewichtet!
SELECT SUM(base_grand_total) AS FALSCH_revenue
FROM sales_invoice
WHERE state = 2;   -- FALSCH!

-- RICHTIG: Bestellungen summieren, nicht Rechnungen
SELECT SUM(base_grand_total) AS revenue
FROM sales_order
WHERE state IN ('processing', 'complete');

6. sales_creditmemo: Rückerstattungen korrekt einbeziehen

Rückerstattungen (Credit Memos) müssen beim Nettoumsatz abgezogen werden. Magento speichert den bereits rückerstatteten Betrag auch direkt in sales_order.base_total_refunded, sodass man nicht zwingend die sales_creditmemo-Tabelle joinen muss.

-- Rückerstattungsübersicht für einen Zeitraum
SELECT
    sc.entity_id,
    sc.increment_id    AS creditmemo_number,
    so.increment_id    AS order_number,
    so.state           AS order_state,
    sc.state           AS creditmemo_state,  -- 1=pending, 2=refunded, 3=canceled
    sc.base_grand_total,
    sc.base_subtotal,
    sc.base_tax_amount,
    sc.base_shipping_amount,
    sc.base_adjustment_positive,  -- zusätzliche Erstattung (z.B. als Kulanz)
    sc.base_adjustment_negative,  -- Abzug von der Erstattung
    sc.created_at
FROM sales_creditmemo sc
JOIN sales_order so ON so.entity_id = sc.order_id
WHERE sc.created_at >= '2025-01-01'
  AND sc.created_at < '2025-02-01'
  AND sc.state = 2   -- nur abgeschlossene Rückerstattungen
ORDER BY sc.created_at DESC;

-- Nettoumsatz mit korrektem Rückerstattungsabzug aus sales_order
-- base_total_refunded enthält die Summe ALLER Credit Memos zur Bestellung
SELECT
    DATE_FORMAT(created_at, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(base_grand_total)            AS gross_revenue,
    SUM(COALESCE(base_total_refunded, 0)) AS total_refunded,
    SUM(base_grand_total - COALESCE(base_total_refunded, 0)) AS net_revenue
FROM sales_order
WHERE state IN ('processing', 'complete', 'closed')
  AND created_at >= '2025-01-01'
  AND created_at < '2026-01-01'
GROUP BY month
ORDER BY month;

7. Die 3 häufigsten Fehler bei Revenue-Reports

Basierend auf der Praxis sind diese drei Fehler in nahezu jedem ersten Magento Revenue-Report anzutreffen:

Fehler 1: Stornierte Bestellungen nicht ausschließen

-- FALSCH: Alle Bestellungen summieren (inkl. Stornos!)
SELECT SUM(base_grand_total) FROM sales_order
WHERE created_at >= '2025-01-01';   -- Zählt auch canceled, closed, holded!

-- RICHTIG: Nur umsatzrelevante States
SELECT SUM(base_grand_total) FROM sales_order
WHERE state IN ('processing', 'complete')
  AND created_at >= '2025-01-01';

Fehler 2: Falsche Währungsspalte in Multi-Store-Setup

-- FALSCH: grand_total in EUR-Shop + USD-Bestellungen summiert falsche Werte
SELECT SUM(grand_total) FROM sales_order WHERE state = 'complete';

-- RICHTIG: Immer Basisspalte (base_grand_total) für Multi-Store
SELECT SUM(base_grand_total) FROM sales_order WHERE state = 'complete';

Fehler 3: Rechnungen statt Bestellungen für Umsatz summieren

-- FALSCH: Rechnungen summieren (Bestellungen mit 3 Teilrechnungen zählen 3x)
SELECT SUM(base_grand_total)
FROM sales_invoice
WHERE state = 2;

-- RICHTIG: Bestellungen summieren, nicht Rechnungen
SELECT SUM(so.base_grand_total)
FROM sales_order so
WHERE so.state IN ('processing', 'complete');

8. Vollständige Nettoumsatz-Abfrage

Die folgende Abfrage berücksichtigt alle besprochenen Aspekte und liefert einen korrekten Monatsbericht für Magento. Sie ist direkt produktionsreif einsetzbar.

-- Vollständiger Magento Revenue-Report nach Monat
-- Korrekt: base_grand_total, state-Filter, Rückerstattungen einbezogen
SELECT
    DATE_FORMAT(so.created_at, '%Y-%m')           AS month,
    so.store_id,
    COUNT(DISTINCT so.entity_id)                  AS orders,
    COUNT(DISTINCT so.customer_id)                AS unique_customers,

    -- Bruttoumsatz (vor Rückerstattungen)
    SUM(so.base_grand_total)                      AS gross_revenue,

    -- Rückerstattungen
    SUM(COALESCE(so.base_total_refunded, 0))      AS refunded,

    -- Nettoumsatz
    SUM(so.base_grand_total
        - COALESCE(so.base_total_refunded, 0))    AS net_revenue,

    -- Aufschlüsselung
    SUM(so.base_subtotal)                         AS subtotal,
    SUM(so.base_tax_amount)                       AS tax_total,
    SUM(so.base_shipping_amount)                  AS shipping_total,
    SUM(ABS(COALESCE(so.base_discount_amount,0))) AS discount_total,

    -- Durchschnittlicher Bestellwert
    ROUND(SUM(so.base_grand_total) / COUNT(*), 2) AS avg_order_value,

    -- Stornoquote
    ROUND(
        SUM(COALESCE(so.base_total_refunded, 0))
        / NULLIF(SUM(so.base_grand_total), 0) * 100,
        1
    )                                              AS refund_rate_pct

FROM sales_order so
WHERE so.state IN ('processing', 'complete', 'closed')
  AND so.created_at >= '2025-01-01 00:00:00'
  AND so.created_at <  '2026-01-01 00:00:00'
  AND so.store_id != 0   -- Admin-Bestellungen ausschließen
GROUP BY DATE_FORMAT(so.created_at, '%Y-%m'), so.store_id
ORDER BY month, so.store_id;

-- Ergänzung: Umsatz nach Produktkategorie (Top 10)
SELECT
    soi.sku,
    soi.name,
    COUNT(DISTINCT soi.order_id)               AS order_count,
    SUM(soi.qty_ordered - soi.qty_refunded)    AS net_qty_sold,
    SUM(soi.base_row_total
        - COALESCE(soi.base_amount_refunded, 0)) AS net_revenue
FROM sales_order_item soi
JOIN sales_order so ON so.entity_id = soi.order_id
WHERE so.state IN ('processing', 'complete')
  AND so.created_at >= '2025-01-01'
  AND so.created_at <  '2026-01-01'
  AND soi.parent_item_id IS NULL   -- keine Varianten
GROUP BY soi.sku, soi.name
ORDER BY net_revenue DESC
LIMIT 10;

9. Zusammenfassung

Magento Sales SQL korrekt auszuwerten erfordert vier Grundregeln: Auf state (nicht status) filtern, base_grand_total (nicht grand_total) für Multi-Store-Reports nutzen, Rückerstattungen via base_total_refunded einbeziehen und Bestellungen (nicht Rechnungen) als Basis für Umsatzsummen verwenden.

10. FAQ: Magento Sales SQL