Reporting-Queries auf Shop-Daten: Umsatz, Warenkorb, Conversion

1. Warum Shop Reporting SQL so fehleranfällig ist

Shop Reporting SQL klingt einfach: Man addiert grand_total aus sales_order und hat seinen Umsatz. In der Praxis ist diese Annahme falsch — und zwar auf mehreren Ebenen gleichzeitig. Erstens enthält sales_order Bestellungen in allen möglichen Status, darunter stornierte, abgelehnte und rückerstattete. Zweitens reduzieren Gutschriften (Credit Memos) den tatsächlichen Nettoumsatz, tauchen aber in einer separaten Tabelle auf. Drittens unterscheidet Magento zwischen quote-Einträgen und echten Bestellungen, was für die Warenkorbabbruchrate entscheidend ist.

Hinzu kommt die Frage der Vollständigkeit: Ein Report, der nur status = 'complete' berücksichtigt, schneidet aktive Bestellungen mit Status processing ab. Ein Report, der beide Status einschließt, aber Stornierungen nicht herausfiltert, überschätzt den Umsatz. Die richtige Antwort liegt in einer durchdachten Statusfilterung in Kombination mit einem JOIN auf die Creditmemo-Tabelle. Dieser Artikel zeigt Schritt für Schritt, wie korrektes Shop Reporting SQL aussieht.

2. Tages- und Monatsumsatz korrekt berechnen

Der wichtigste Grundsatz beim Umsatz-Reporting: Nur Bestellungen mit Status complete oder processing zählen als Umsatz. Stornierungen (canceled), Fehlschläge (failed) und offene Zahlungen (pending) dürfen nicht eingehen. Zusätzlich müssen Credit Memos (Rückerstattungen) vom Bruttobetrag abgezogen werden.

Tagesumsatz mit korrektem Statusfilter und Creditmemo-Abzug:

-- Daily revenue: only complete/processing orders minus credit memos
SELECT
    DATE(o.created_at)         AS day,
    COUNT(DISTINCT o.entity_id) AS order_count,
    SUM(o.grand_total)          AS gross_revenue,
    COALESCE(SUM(cm.creditmemo_total), 0) AS refunded,
    SUM(o.grand_total) - COALESCE(SUM(cm.creditmemo_total), 0) AS net_revenue
FROM sales_order o
LEFT JOIN (
    SELECT order_id, SUM(grand_total) AS creditmemo_total
    FROM sales_creditmemo
    WHERE state = 2  -- 2 = refunded
    GROUP BY order_id
) cm ON cm.order_id = o.entity_id
WHERE o.status IN ('complete', 'processing')
  AND o.created_at >= '2025-01-01'
  AND o.created_at <  '2025-02-01'
GROUP BY DATE(o.created_at)
ORDER BY day;

Für den Monatsumsatz reicht es, die GROUP BY-Klausel anzupassen. Statt DATE() wird DATE_FORMAT(o.created_at, '%Y-%m') verwendet — oder besser als SARGable-Variante ein vorberechnetes Feld via CTE (dazu später mehr).

-- Monthly revenue with average order value
SELECT
    DATE_FORMAT(o.created_at, '%Y-%m') AS month,
    COUNT(DISTINCT o.entity_id)         AS orders,
    ROUND(SUM(o.grand_total), 2)        AS gross_revenue,
    ROUND(AVG(o.grand_total), 2)        AS avg_order_value
FROM sales_order o
WHERE o.status IN ('complete', 'processing')
  AND o.created_at >= '2024-01-01'
GROUP BY DATE_FORMAT(o.created_at, '%Y-%m')
ORDER BY month DESC;

3. Top-10-Produkte nach Umsatz

Um die umsatzstärksten Produkte zu ermitteln, muss man sales_order_item mit dem Produktnamen verknüpfen und nach SKU gruppieren. Wichtig: Refundierte Bestellungen sollten hier ebenfalls herausgefiltert werden, indem nur Items aus Bestellungen mit gültigem Status berücksichtigt werden.

-- Top 10 products by revenue (joined with order status filter)
SELECT
    oi.sku,
    oi.name                              AS product_name,
    SUM(oi.qty_ordered)                  AS units_sold,
    ROUND(SUM(oi.row_total), 2)          AS total_revenue,
    ROUND(SUM(oi.row_total) / SUM(oi.qty_ordered), 2) AS avg_unit_price
FROM sales_order_item oi
INNER JOIN sales_order o ON o.entity_id = oi.order_id
WHERE o.status IN ('complete', 'processing')
  AND oi.parent_item_id IS NULL  -- exclude configurable sub-items
  AND o.created_at >= '2024-01-01'
GROUP BY oi.sku, oi.name
ORDER BY total_revenue DESC
LIMIT 10;

Der Filter oi.parent_item_id IS NULL ist bei Magento-konfigurierbaren Produkten entscheidend: Jede konfigurierbare Bestellung erzeugt zwei Items — das Parent (einfache SKU) und das Kind. Ohne diesen Filter werden Mengen und Umsatz doppelt gezählt.

4. Warenkorbabbruchrate ermitteln

Die Warenkorbabbruchrate ist eines der am häufigsten falsch berechneten Metriken im Shop Reporting SQL. Die richtige Formel lautet: (Quotes ohne zugehörige Bestellung) ÷ (alle aktiven Quotes) × 100. Magento legt für jede Sitzung, in der ein Produkt in den Warenkorb gelegt wird, einen Eintrag in quote an. Wird die Bestellung abgeschlossen, erhält die Quote eine is_active = 0 und ist über reserved_order_id mit sales_order.quote_id verbunden.

-- Cart abandonment rate for a given period
WITH period_quotes AS (
    SELECT
        COUNT(*) AS total_quotes,
        SUM(CASE WHEN is_active = 0 AND reserved_order_id IS NOT NULL THEN 1 ELSE 0 END) AS converted
    FROM quote
    WHERE items_count > 0
      AND created_at >= '2025-01-01'
      AND created_at <  '2025-02-01'
),
converted_orders AS (
    SELECT COUNT(DISTINCT quote_id) AS order_count
    FROM sales_order
    WHERE status IN ('complete', 'processing', 'pending')
      AND created_at >= '2025-01-01'
      AND created_at <  '2025-02-01'
)
SELECT
    pq.total_quotes,
    co.order_count                                         AS converted_quotes,
    pq.total_quotes - co.order_count                      AS abandoned_quotes,
    ROUND(
        (pq.total_quotes - co.order_count) / pq.total_quotes * 100, 2
    )                                                      AS abandonment_rate_pct
FROM period_quotes pq, converted_orders co;

5. Customer Lifetime Value und AOV-Trend

Der Customer Lifetime Value (CLV) gibt an, wie viel Umsatz ein Kunde über alle seine Bestellungen hinweg generiert hat. Im einfachsten Fall ist das die Summe aller grand_total-Werte pro Kunden-ID. Wichtig ist auch hier der Statusfilter, da sonst stornierte Bestellungen den CLV verfälschen.

-- Customer lifetime value: top 20 customers by total spend
SELECT
    o.customer_id,
    o.customer_email,
    COUNT(DISTINCT o.entity_id)   AS total_orders,
    ROUND(SUM(o.grand_total), 2)  AS lifetime_value,
    ROUND(AVG(o.grand_total), 2)  AS avg_order_value,
    MIN(o.created_at)             AS first_order,
    MAX(o.created_at)             AS last_order
FROM sales_order o
WHERE o.status IN ('complete', 'processing')
  AND o.customer_id IS NOT NULL  -- exclude guest orders
GROUP BY o.customer_id, o.customer_email
ORDER BY lifetime_value DESC
LIMIT 20;

Der Average Order Value (AOV) im Zeitverlauf zeigt, ob Kampagnen oder Sortimentsänderungen die Warenkorbgröße beeinflussen:

-- Monthly AOV trend
SELECT
    DATE_FORMAT(created_at, '%Y-%m') AS month,
    COUNT(*)                          AS orders,
    ROUND(AVG(grand_total), 2)        AS aov,
    ROUND(MIN(grand_total), 2)        AS min_order,
    ROUND(MAX(grand_total), 2)        AS max_order
FROM sales_order
WHERE status IN ('complete', 'processing')
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY month;

6. Neue vs. wiederkehrende Kunden

Für die Segmentierung in neue und wiederkehrende Kunden wird das Datum der ersten Bestellung pro Kunden-ID benötigt. Eine Bestellung gilt als "neu", wenn sie die erste Bestellung dieses Kunden ist — das heißt, wenn das Erstelldatum der Bestellung mit dem Erstbestelldatum übereinstimmt.

-- New vs returning customers per month
WITH first_orders AS (
    SELECT
        customer_id,
        MIN(created_at) AS first_order_date
    FROM sales_order
    WHERE status IN ('complete', 'processing')
      AND customer_id IS NOT NULL
    GROUP BY customer_id
)
SELECT
    DATE_FORMAT(o.created_at, '%Y-%m') AS month,
    COUNT(DISTINCT o.entity_id)         AS total_orders,
    COUNT(DISTINCT CASE
        WHEN DATE(o.created_at) = DATE(fo.first_order_date) THEN o.entity_id
    END)                                AS new_customer_orders,
    COUNT(DISTINCT CASE
        WHEN DATE(o.created_at) > DATE(fo.first_order_date) THEN o.entity_id
    END)                                AS returning_customer_orders
FROM sales_order o
INNER JOIN first_orders fo ON fo.customer_id = o.customer_id
WHERE o.status IN ('complete', 'processing')
GROUP BY DATE_FORMAT(o.created_at, '%Y-%m')
ORDER BY month;

7. CTEs für lesbare Reports

Komplexe Reporting-Queries werden schnell unübersichtlich, wenn alle JOINs, Subqueries und Berechnungen in einer einzigen Abfrage verschachtelt sind. Common Table Expressions (CTEs) mit dem WITH-Schlüsselwort lösen dieses Problem elegant: Jeder Rechenschritt wird als benannter CTE definiert und kann danach wie eine temporäre Tabelle referenziert werden.

Ein vollständiger Report, der Umsatz, Abbruchrate und CLV-Segmentierung in einem Durchgang verbindet:

-- Comprehensive monthly report using CTEs
WITH order_base AS (
    -- Base: only relevant statuses
    SELECT entity_id, customer_id, customer_email,
           grand_total, created_at
    FROM sales_order
    WHERE status IN ('complete', 'processing')
),
monthly_revenue AS (
    SELECT
        DATE_FORMAT(created_at, '%Y-%m') AS month,
        COUNT(*)                          AS orders,
        ROUND(SUM(grand_total), 2)        AS revenue,
        ROUND(AVG(grand_total), 2)        AS aov
    FROM order_base
    GROUP BY DATE_FORMAT(created_at, '%Y-%m')
),
refunds AS (
    SELECT
        DATE_FORMAT(o.created_at, '%Y-%m') AS month,
        ROUND(SUM(cm.grand_total), 2)       AS refund_total
    FROM sales_creditmemo cm
    INNER JOIN sales_order o ON o.entity_id = cm.order_id
    WHERE cm.state = 2
    GROUP BY DATE_FORMAT(o.created_at, '%Y-%m')
)
SELECT
    mr.month,
    mr.orders,
    mr.revenue                                      AS gross_revenue,
    COALESCE(r.refund_total, 0)                     AS refunds,
    mr.revenue - COALESCE(r.refund_total, 0)        AS net_revenue,
    mr.aov
FROM monthly_revenue mr
LEFT JOIN refunds r ON r.month = mr.month
ORDER BY mr.month DESC;

8. Magento-Aggregationstabellen als Alternative

Magento pflegt mehrere vorgefertigte Aggregationstabellen, die für Reporting-Queries erheblich schneller sind als direkte Abfragen auf sales_order. Diese werden vom Cron-Job befüllt und halten täglich und monatlich vorberechnete Werte bereit:

• sales_order_aggregated_created: Aggregierte Bestelldaten nach Erstelldatum, aufgeteilt nach Store-View.
• sales_order_aggregated_updated: Dieselben Daten, aber nach dem letzten Update-Datum — relevant, wenn Bestellstatus sich ändert.
• report_viewed_product_aggregated_daily/monthly: Produktaufrufe für Reichweitenanalysen.

-- Using Magento aggregation table for fast monthly revenue
SELECT
    period,
    store_id,
    orders_count,
    ROUND(total_revenue, 2)  AS revenue,
    ROUND(total_refunded, 2) AS refunded,
    ROUND(total_revenue - total_refunded, 2) AS net_revenue
FROM sales_order_aggregated_created
WHERE period >= '2024-01-01'
  AND store_id = 1
ORDER BY period DESC;

Der Nachteil dieser Tabellen: Sie werden durch den Aggregations-Cron befüllt und können je nach Cron-Konfiguration einige Stunden hinter dem tatsächlichen Stand zurückliegen. Für Echtzeit-Dashboards sind direkte Queries auf sales_order notwendig. Für historische Berichte oder BI-Exporte sind die Aggregationstabellen jedoch klar vorzuziehen, da sie deutlich weniger I/O erzeugen.

10. Zusammenfassung

Shop Reporting SQL ist in Magento deutlich komplexer als ein einfaches SUM(grand_total). Die häufigsten Fehler entstehen durch fehlende Statusfilter, ignorierte Rückerstattungen, falsch verstandene Quote-vs-Order-Logik und unnötig teure direkte Queries auf große Tabellen. Mit den in diesem Artikel gezeigten CTEs, korrekten Statusfiltern und der Kenntnis der Magento-Aggregationstabellen lassen sich Reporting-Queries erstellen, die sowohl korrekte Zahlen liefern als auch performant bleiben.

Die wichtigste Praxisregel: Immer erst den Statusfilter definieren, dann die Kreditmemo-Abzüge modellieren, und für komplexe Reports auf CTEs setzen — das macht Queries wartbar und für Kollegen nachvollziehbar.

11. FAQ: Shop Reporting SQL in Magento