Projekt: Datenanalyse

In diesem Abschlussprojekt wirst du zum Datenanalysten. Du nutzt alle SQL-Kenntnisse, um echte Business-Fragen zu beantworten: Wie entwickelt sich der Umsatz? Welche Kunden sind am wertvollsten? Wo gibt es Verbesserungspotenzial? Willkommen in der Welt der Datenanalyse!

Projektueberblick

Wir arbeiten mit einer erweiterten Version unserer Shop-Datenbank und beantworten Fragen, die ein Unternehmen wirklich stellt:

• Umsatz-Trends: Wie entwickelt sich das Geschaeft?
• Kundenanalyse: Wer sind unsere besten Kunden?
• Produktanalyse: Welche Produkte performen am besten?
• Kohortenanalyse: Wie verhalten sich Kunden ueber Zeit?
• Funnel-Analyse: Wo verlieren wir Kunden?

Erweiterte Testdaten erstellen

Fuer aussagekraeftige Analysen brauchen wir mehr Daten. Erstelle zuerst die erweiterten Tabellen:

-- Webseiten-Besuche (fuer Funnel-Analyse)
CREATE TABLE seitenaufrufe (
    id INTEGER PRIMARY KEY,
    kunden_id INTEGER REFERENCES kunden(id),
    session_id VARCHAR(50) NOT NULL,
    seite VARCHAR(200) NOT NULL,
    zeitpunkt TIMESTAMP NOT NULL,
    verweildauer_sek INTEGER
);

-- Marketing-Kanaele
CREATE TABLE marketing_kanaele (
    id INTEGER PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    typ VARCHAR(30) CHECK (typ IN ('organisch', 'bezahlt', 'social', 'email', 'direkt'))
);

-- Kunden-Akquise
CREATE TABLE kunden_akquise (
    kunden_id INTEGER PRIMARY KEY REFERENCES kunden(id),
    kanal_id INTEGER REFERENCES marketing_kanaele(id),
    kampagne VARCHAR(100),
    akquise_datum DATE NOT NULL,
    akquise_kosten DECIMAL(10,2) DEFAULT 0
);

-- Marketing-Kanaele einfuegen
INSERT INTO marketing_kanaele (id, name, typ) VALUES
(1, 'Google Ads', 'bezahlt'),
(2, 'Facebook Ads', 'bezahlt'),
(3, 'Instagram', 'social'),
(4, 'Newsletter', 'email'),
(5, 'Google Suche', 'organisch'),
(6, 'Direktzugriff', 'direkt');

-- Kunden-Akquise-Daten
INSERT INTO kunden_akquise (kunden_id, kanal_id, kampagne, akquise_datum, akquise_kosten) VALUES
(1, 1, 'Sommer-Kampagne', '2025-06-15', 12.50),
(2, 5, NULL, '2025-08-20', 0),
(3, 2, 'Herbst-Sale', '2025-10-01', 8.75),
(4, 4, 'Newsletter Jan26', '2026-01-10', 0.50),
(5, 3, 'Insta-Promo', '2026-02-01', 5.00);

-- Weitere Bestellungen fuer bessere Analysen
INSERT INTO bestellungen (id, kunden_id, bestellnummer, status, versandkosten, zwischensumme, mwst_betrag, gesamtbetrag, bestellt_am) VALUES
(7, 1, 'TM-2026-0007', 'zugestellt', 0, 89.98, 17.10, 107.08, '2025-08-10 10:00:00'),
(8, 2, 'TM-2026-0008', 'zugestellt', 0, 39.99, 7.60, 47.59, '2025-11-15 14:30:00'),
(9, 1, 'TM-2026-0009', 'zugestellt', 0, 149.99, 28.50, 178.49, '2025-12-20 09:00:00'),
(10, 3, 'TM-2026-0010', 'zugestellt', 4.99, 59.99, 11.40, 76.38, '2025-12-24 18:00:00'),
(11, 2, 'TM-2026-0011', 'zugestellt', 0, 69.99, 13.30, 83.29, '2026-01-05 11:00:00'),
(12, 4, 'TM-2026-0012', 'zugestellt', 0, 29.99, 5.70, 35.69, '2026-02-14 15:00:00');

-- Bestellpositionen fuer die neuen Bestellungen
INSERT INTO bestellpositionen (id, bestell_id, produkt_id, produktname, einzelpreis, menge, gesamt) VALUES
(10, 7, 3, 'Wireless Kopfhoerer BT500', 149.99, 1, 149.99),
(11, 8, 8, 'Schnellladegeraet 65W', 39.99, 1, 39.99),
(12, 9, 3, 'Wireless Kopfhoerer BT500', 149.99, 1, 149.99),
(13, 10, 7, 'Bluetooth Lautsprecher Mini', 59.99, 1, 59.99),
(14, 11, 5, 'Laptop-Rucksack Urban', 69.99, 1, 69.99),
(15, 12, 6, 'Handyhuelle Premium', 29.99, 1, 29.99);

-- Seitenaufrufe (fuer Funnel-Analyse)
INSERT INTO seitenaufrufe (id, kunden_id, session_id, seite, zeitpunkt, verweildauer_sek) VALUES
(1, 1, 'sess-001', '/startseite', '2026-03-01 10:00:00', 45),
(2, 1, 'sess-001', '/produkte', '2026-03-01 10:01:00', 120),
(3, 1, 'sess-001', '/produkt/1', '2026-03-01 10:03:00', 90),
(4, 1, 'sess-001', '/warenkorb', '2026-03-01 10:05:00', 30),
(5, 1, 'sess-001', '/kasse', '2026-03-01 10:06:00', 60),
(6, NULL, 'sess-002', '/startseite', '2026-03-01 11:00:00', 20),
(7, NULL, 'sess-002', '/produkte', '2026-03-01 11:01:00', 15),
(8, 3, 'sess-003', '/startseite', '2026-03-02 09:00:00', 30),
(9, 3, 'sess-003', '/produkte', '2026-03-02 09:01:00', 60),
(10, 3, 'sess-003', '/produkt/2', '2026-03-02 09:02:00', 120),
(11, 3, 'sess-003', '/warenkorb', '2026-03-02 09:04:00', 45),
(12, 3, 'sess-003', '/kasse', '2026-03-02 09:05:00', 90),
(13, NULL, 'sess-004', '/startseite', '2026-03-03 14:00:00', 10),
(14, 5, 'sess-005', '/startseite', '2026-03-05 16:00:00', 25),
(15, 5, 'sess-005', '/produkte', '2026-03-05 16:01:00', 80),
(16, 5, 'sess-005', '/produkt/3', '2026-03-05 16:02:00', 60);

Analyse 1: Umsatz-KPIs

Monatlicher Umsatz mit Wachstum

SELECT
    monat,
    umsatz,
    bestellungen,
    avg_bestellwert,
    LAG(umsatz) OVER (ORDER BY monat) AS vormonat,
    CASE
        WHEN LAG(umsatz) OVER (ORDER BY monat) IS NOT NULL
        THEN ROUND(
            (umsatz - LAG(umsatz) OVER (ORDER BY monat)) /
            LAG(umsatz) OVER (ORDER BY monat) * 100, 1
        )
        ELSE NULL
    END AS wachstum_pct
FROM (
    SELECT
        strftime('%Y-%m', bestellt_am) AS monat,
        ROUND(SUM(gesamtbetrag), 2) AS umsatz,
        COUNT(*) AS bestellungen,
        ROUND(AVG(gesamtbetrag), 2) AS avg_bestellwert
    FROM bestellungen
    WHERE status != 'storniert'
    GROUP BY strftime('%Y-%m', bestellt_am)
) AS monatsdaten
ORDER BY monat;

Laufender Jahresumsatz (YTD)

SELECT
    strftime('%Y-%m', bestellt_am) AS monat,
    ROUND(SUM(gesamtbetrag), 2) AS monatsumsatz,
    ROUND(SUM(SUM(gesamtbetrag)) OVER (
        ORDER BY strftime('%Y-%m', bestellt_am)
    ), 2) AS ytd_umsatz
FROM bestellungen
WHERE status != 'storniert'
    AND bestellt_am >= '2026-01-01'
GROUP BY strftime('%Y-%m', bestellt_am)
ORDER BY monat;

Analyse 2: Kundenanalyse

RFM-Analyse (Recency, Frequency, Monetary)

Die RFM-Analyse bewertet Kunden nach drei Kriterien:

• Recency: Wie kuerzlich hat der Kunde bestellt?
• Frequency: Wie oft bestellt der Kunde?
• Monetary: Wie viel gibt der Kunde aus?

SELECT
    k.vorname || ' ' || k.nachname AS kunde,
    -- Recency: Tage seit letzter Bestellung
    CAST(julianday('now') - julianday(MAX(b.bestellt_am)) AS INTEGER) AS tage_seit_bestellung,
    -- Frequency: Anzahl Bestellungen
    COUNT(b.id) AS anzahl_bestellungen,
    -- Monetary: Gesamtumsatz
    ROUND(SUM(b.gesamtbetrag), 2) AS gesamtumsatz,
    -- Kundensegment
    CASE
        WHEN COUNT(b.id) >= 3 AND SUM(b.gesamtbetrag) > 500 THEN 'VIP'
        WHEN COUNT(b.id) >= 2 THEN 'Stammkunde'
        WHEN COUNT(b.id) = 1 THEN 'Neukunde'
        ELSE 'Inaktiv'
    END AS segment
FROM kunden k
LEFT JOIN bestellungen b ON k.id = b.kunden_id AND b.status != 'storniert'
GROUP BY k.id, k.vorname, k.nachname
ORDER BY gesamtumsatz DESC;

Customer Lifetime Value (CLV)

SELECT
    k.vorname || ' ' || k.nachname AS kunde,
    ROUND(SUM(b.gesamtbetrag), 2) AS bisheriger_umsatz,
    COUNT(b.id) AS bestellungen,
    ROUND(AVG(b.gesamtbetrag), 2) AS avg_bestellwert,
    -- Vereinfachte CLV-Schaetzung:
    -- Durchschnittlicher Bestellwert * Geschaetzte Bestellungen pro Jahr
    ROUND(AVG(b.gesamtbetrag) * 4, 2) AS geschaetzter_jahres_clv
FROM kunden k
JOIN bestellungen b ON k.id = b.kunden_id
WHERE b.status != 'storniert'
GROUP BY k.id, k.vorname, k.nachname
ORDER BY geschaetzter_jahres_clv DESC;

Analyse 3: Produktanalyse

Produktperformance-Matrix

SELECT
    p.name,
    SUM(bp.menge) AS verkauft,
    ROUND(SUM(bp.gesamt), 2) AS umsatz,
    ROUND(AVG(bw.sterne), 1) AS bewertung,
    p.lagerbestand,
    -- Kategorisierung
    CASE
        WHEN SUM(bp.menge) >= 3 AND COALESCE(AVG(bw.sterne), 0) >= 4 THEN 'Star'
        WHEN SUM(bp.menge) >= 2 THEN 'Cash Cow'
        WHEN COALESCE(AVG(bw.sterne), 0) >= 4 THEN 'Potential'
        ELSE 'Beobachten'
    END AS status
FROM produkte p
LEFT JOIN bestellpositionen bp ON p.id = bp.produkt_id
LEFT JOIN bestellungen b ON bp.bestell_id = b.id AND b.status != 'storniert'
LEFT JOIN bewertungen bw ON p.id = bw.produkt_id
GROUP BY p.id, p.name, p.lagerbestand
ORDER BY umsatz DESC;

Preiselastizitaet (vereinfacht)

-- Produkte mit und ohne Rabatt vergleichen
SELECT
    p.name,
    p.preis AS aktueller_preis,
    p.streichpreis AS alter_preis,
    CASE WHEN p.streichpreis IS NOT NULL
        THEN ROUND((1 - p.preis / p.streichpreis) * 100, 0)
        ELSE 0
    END AS rabatt_pct,
    COALESCE(SUM(bp.menge), 0) AS verkauft
FROM produkte p
LEFT JOIN bestellpositionen bp ON p.id = bp.produkt_id
LEFT JOIN bestellungen b ON bp.bestell_id = b.id AND b.status != 'storniert'
GROUP BY p.id, p.name, p.preis, p.streichpreis
ORDER BY rabatt_pct DESC;

Analyse 4: Kohortenanalyse

Eine Kohortenanalyse gruppiert Kunden nach ihrem Registrierungszeitraum und verfolgt ihr Verhalten ueber die Zeit.

-- Kunden-Kohorten nach Registrierungsmonat
SELECT
    kohorte,
    monate_seit_registrierung,
    anzahl_kunden,
    bestellende_kunden,
    ROUND(bestellende_kunden * 100.0 / anzahl_kunden, 1) AS retention_rate
FROM (
    SELECT
        strftime('%Y-%m', k.registriert_am) AS kohorte,
        CAST((julianday(b.bestellt_am) - julianday(k.registriert_am)) / 30 AS INTEGER) AS monate_seit_registrierung,
        COUNT(DISTINCT k.id) AS anzahl_kunden,
        COUNT(DISTINCT CASE WHEN b.id IS NOT NULL THEN k.id END) AS bestellende_kunden
    FROM kunden k
    LEFT JOIN bestellungen b ON k.id = b.kunden_id AND b.status != 'storniert'
    GROUP BY strftime('%Y-%m', k.registriert_am),
             CAST((julianday(b.bestellt_am) - julianday(k.registriert_am)) / 30 AS INTEGER)
) AS kohorten
WHERE monate_seit_registrierung >= 0
ORDER BY kohorte, monate_seit_registrierung;

Analyse 5: Marketing-ROI

SELECT
    mk.name AS kanal,
    mk.typ,
    COUNT(DISTINCT ka.kunden_id) AS gewonnene_kunden,
    ROUND(SUM(ka.akquise_kosten), 2) AS marketing_kosten,
    ROUND(COALESCE(SUM(b.gesamtbetrag), 0), 2) AS generierter_umsatz,
    CASE
        WHEN SUM(ka.akquise_kosten) > 0
        THEN ROUND(SUM(b.gesamtbetrag) / SUM(ka.akquise_kosten), 1)
        ELSE NULL
    END AS roi,
    ROUND(AVG(ka.akquise_kosten), 2) AS kosten_pro_kunde
FROM marketing_kanaele mk
JOIN kunden_akquise ka ON mk.id = ka.kanal_id
LEFT JOIN bestellungen b ON ka.kunden_id = b.kunden_id AND b.status != 'storniert'
GROUP BY mk.id, mk.name, mk.typ
ORDER BY generierter_umsatz DESC;

Analyse 6: Funnel-Analyse

-- Conversion Funnel: Wie viele Besucher erreichen jede Stufe?
SELECT
    seite,
    anzahl_besucher,
    ROUND(anzahl_besucher * 100.0 / FIRST_VALUE(anzahl_besucher) OVER (ORDER BY stufe), 1) AS conversion_rate
FROM (
    SELECT
        CASE
            WHEN seite = '/startseite' THEN 1
            WHEN seite = '/produkte' THEN 2
            WHEN seite LIKE '/produkt/%' THEN 3
            WHEN seite = '/warenkorb' THEN 4
            WHEN seite = '/kasse' THEN 5
        END AS stufe,
        seite,
        COUNT(DISTINCT session_id) AS anzahl_besucher
    FROM seitenaufrufe
    WHERE seite IN ('/startseite', '/produkte', '/warenkorb', '/kasse')
        OR seite LIKE '/produkt/%'
    GROUP BY CASE
            WHEN seite = '/startseite' THEN 1
            WHEN seite = '/produkte' THEN 2
            WHEN seite LIKE '/produkt/%' THEN 3
            WHEN seite = '/warenkorb' THEN 4
            WHEN seite = '/kasse' THEN 5
        END,
        CASE
            WHEN seite LIKE '/produkt/%' THEN 'Produktseite'
            ELSE seite
        END
) AS funnel
ORDER BY stufe;

Analyse 7: Umfassender Business-Report

-- Dashboard-View mit allen wichtigen KPIs
CREATE VIEW v_business_dashboard AS
SELECT
    'Gesamtumsatz' AS kpi,
    CAST(ROUND(SUM(CASE WHEN status != 'storniert' THEN gesamtbetrag ELSE 0 END), 2) AS TEXT) AS wert
FROM bestellungen
UNION ALL
SELECT
    'Anzahl Bestellungen',
    CAST(COUNT(CASE WHEN status != 'storniert' THEN 1 END) AS TEXT)
FROM bestellungen
UNION ALL
SELECT
    'Avg. Bestellwert',
    CAST(ROUND(AVG(CASE WHEN status != 'storniert' THEN gesamtbetrag END), 2) AS TEXT)
FROM bestellungen
UNION ALL
SELECT
    'Aktive Kunden',
    CAST(COUNT(DISTINCT kunden_id) AS TEXT)
FROM bestellungen WHERE status != 'storniert'
UNION ALL
SELECT
    'Stornierungsrate',
    CAST(ROUND(
        COUNT(CASE WHEN status = 'storniert' THEN 1 END) * 100.0 / COUNT(*), 1
    ) AS TEXT) || '%'
FROM bestellungen;

SELECT * FROM v_business_dashboard;

Was kommt als Naechstes?

Herzlichen Glueckwunsch - du hast den gesamten SQL-Kurs abgeschlossen! Du beherrschst jetzt:

• Grundlegende bis fortgeschrittene SQL-Abfragen
• Tabellendesign und Datenbankarchitektur
• Datenanalyse mit echten Business-Fragestellungen

Moegliche naechste Schritte:

• Python + SQL: Verbinde SQL mit Python fuer automatisierte Analysen
• PostgreSQL vertiefen: Stored Procedures, Trigger, Partitionierung
• Data Engineering: ETL-Pipelines, Data Warehousing
• Business Intelligence: Tools wie Metabase, Grafana oder Tableau

Zusammenfassung

• Umsatz-KPIs: Monatstrends, YTD, Wachstumsraten mit Window Functions
• RFM-Analyse: Kundensegmentierung nach Recency, Frequency, Monetary
• Customer Lifetime Value: Vorhersage des Kundenwerts
• Kohortenanalyse: Kundenverhalten ueber Zeit verfolgen
• Marketing-ROI: Effektivitaet der Akquise-Kanaele messen
• Funnel-Analyse: Conversion Rates im Kaufprozess
• Business-Dashboards: Alle KPIs auf einen Blick

Uebungen

1. Wochenreport: Erstelle einen woechentlichen Umsatzreport mit Vergleich zur Vorwoche.
2. Churn-Analyse: Finde Kunden, die seit mehr als 90 Tagen nicht bestellt haben.
3. ABC-Analyse: Teile Produkte in A (Top 20% Umsatz), B (naechste 30%) und C (Rest) ein.
4. Warenkorbanalyse: Welche Produkte werden haeufig zusammen bestellt?
5. Eigenes Dashboard: Erstelle einen umfassenden View, der die 10 wichtigsten KPIs deines Shops zeigt.

-- Loesung zu Uebung 2:
SELECT
    k.vorname || ' ' || k.nachname AS kunde,
    k.email,
    MAX(b.bestellt_am) AS letzte_bestellung,
    CAST(julianday('now') - julianday(MAX(b.bestellt_am)) AS INTEGER) AS tage_inaktiv
FROM kunden k
JOIN bestellungen b ON k.id = b.kunden_id
WHERE b.status != 'storniert'
GROUP BY k.id, k.vorname, k.nachname, k.email
HAVING CAST(julianday('now') - julianday(MAX(b.bestellt_am)) AS INTEGER) > 90;