Zurück zur Artikelliste Artikel
11 Leseminuten

Analyse des Umsatzwachstums mit dem Cheatsheet für Datenanalysten: Teil 2

Author's photo
Scott Davis

Inhaltsverzeichnis

Willkommen zu Teil 2 unserer Reise zur Verkaufsdatenanalyse. Mithilfe des Spickzettels "SQL for Data Analysis" vertiefen wir die wichtigsten Kennzahlen und wenden SQL-Abfragen an, um Erkenntnisse über Kunden und Produkte zu gewinnen. Lassen Sie uns Ihre SQL-Kenntnisse verbessern!

Aufbauend auf meinem vorherigen Artikel Erkundung des Datensatzes zum Umsatzwachstum - Verwendung des Spickzettels für Datenanalysten bei realen Umsatzdaten werden in diesem Beitrag weitere Umsatzkennzahlen anhand desselben Datensatzes untersucht. Wir werden uns auf verschiedene Anwendungsfälle konzentrieren, die Verbraucher und Produkte einbeziehen, während wir weiterhin den Anleitungen aus LearnSQL.de's SQL for Data Analysis Cheat Sheet folgen.

In diesem Artikel gehen wir auf drei Schlüsselelemente ein: SQL-Kernthemen, Identifizierung von Metrikmustern und Verweis auf relevante Abschnitte des SQL for Data Analysis Cheat Sheet zum schnellen Nachschlagen. Anhand eines Tableau-Datensatzes, der Verkaufsdaten für Haushaltswaren zeigt, werden wir geschäftliche Fragen in Bezug auf Kunden und Produkte beantworten.

Ziel ist es, das Erkennen von Berichtsmustern und die Anwendung von SQL-Abfragen zur Lösung dieser Fragen zu üben. Während sich der Datensatz weiterentwickeln kann, sollte der Fokus auf dem Prozess und den logischen Schritten bei der Erstellung von Abfragen liegen.

Was Sie lernen werden:

  • Erkennen von Mustern in SQL-Abfragen, metrischen Analysen und Cheatsheet-Referenzen
  • Anwenden dieser Muster auf Ihre tägliche Arbeit mit Berichten und SQL

Überblick

Dieser Artikel gliedert sich in zwei Abschnitte: Kundenanalyse und Produktanalyse, wobei KPIs, Trends und verschiedene Segmentierungsmethoden behandelt werden.

Zur Beantwortung der Fragen wende ich SQL-Techniken aus dem vorherigen Artikel an, darunter Aggregationen, Kennzahlen und Common Table Expressions (CTEs). Die Analyse konzentriert sich auf Metriken wie KPIs, Gruppierung, Trendverfolgung, Ranking und Segmentierung. Zu den Verweisen auf das SQL for Data Analysis Cheat Sheet gehören GROUP BY, Ranking, Extraktionen von Datumsteilen, Verhältniszahlen und Ganzzahldivision. In jedem Beispiel werden relevante SQL-Techniken, metrische Muster und Verweise auf das Cheatsheet deutlich gekennzeichnet.

Die Beispiele sind nach Schwierigkeitsgrad geordnet, beginnend mit einfachen Abfragen bis hin zu fortgeschritteneren Techniken, so dass es leicht ist, diese Methoden auf andere Geschäftsdaten anzuwenden.

Wie werden die Daten aufbereitet?

Wie im vorherigen Artikel verwende ich einen offenen Datensatz von Tableau Public, der Verkaufsdaten für Haushaltswaren wie Schreibtische und Schreibwaren enthält. Dieser Datensatz wird häufig für Geschäftsberichte verwendet und ist eine hervorragende Ressource zum Üben von SQL-Abfragen. Hier sind die Schritte zur Vorbereitung der Daten.

Laden Sie das Dataset von Tableau Public herunter.

  • Navigieren Sie zu Lernen → Beispieldaten → Business → Superstore Sales.
  • Laden Sie das Superstore Sales-Dataset herunter.
Analyse des Umsatzwachstums mit dem Cheatsheet für Datenanalysten: Teil 2
  1. Die relevanten Daten befinden sich auf der Registerkarte "Orders".
  2. .xls-Datei öffnen
  3. Öffnen Sie die Registerkarte "Bestellungen" aus der .xls-Datei und exportieren Sie sie in eine CSV-Datei.

Für eine SQL-IDE empfehle ich die Verwendung von MySQL Workbench und MySQL Community Server. Laden Sie die CSV-Datei in Ihre MySQL-Datenbank hoch, um die Abfrage zu starten.

Wie im vorherigen Artikel können Sie den in diesem Artikel verwendeten Code von meinem Github-Konto herunterladen.

Analysieren von Kunden

Beginnen wir auf der einfachen Ebene der Kunden-Subdomäne. Wir werden verschiedene Möglichkeiten der Kundenanalyse für Finanz-, Bestell- und Segmentierungszwecke zusammenfassen.

Frage 1: Wie viele Kunden gibt es pro Zeitabschnitt?

SELECT
    EXTRACT(YEAR FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_year,
    EXTRACT(MONTH FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_month,
    COUNT(DISTINCT(Customer_ID)) AS active_customers
FROM `Tableau Superstore`.orders
GROUP BY 1, 2
ORDER BY 1, 2;

Ergebnis: Die Abfrage berechnet die Gesamtzahl der Kunden nach Zeiträumen über den gesamten Datensatz. Sie liefert einen Trend, wie viele Kunden Käufe nach Jahr und Monat getätigt haben. Der Benutzer kann sehen, wie sich der Kundenstamm im Laufe der Geschichte des Unternehmens verändert hat.

Um zu lernen, wie man SQL-Berichte wie diesen schreibt, empfehle ich den Erstellen einfacher SQL-Berichte Kurs. Er enthält fast 100 Übungen, in denen Sie lernen, wie man komplexe Berichte in SQL schreibt.

Frage 2: Erstellen Sie kundenbasierte Finanzmetriken pro Produktkategorie und Unterkategorie für den durchschnittlichen Umsatz pro Nutzer (ARPU) und den durchschnittlichen Gewinn pro Nutzer (APPU) nach Standort

SELECT
      Region,
      State,
      City,
      ROUND(SUM(Sales) / COUNT(DISTINCT `Customer_ID`), 1) AS ARPU,
      ROUND(SUM(Profit) / COUNT(DISTINCT `Customer_ID`), 1) AS APPU
FROM `Tableau Superstore`.orders
GROUP BY 1, 2, 3
ORDER BY 4 DESC;

Ergebnis: Diese Abfrage berechnet ARPU und APPU für eine Kombination aus Region, Bundesland und Stadt und ordnet die Städte nach dem ARPU in absteigender Reihenfolge. Die Ergebnisse zeigen die Städte mit dem höchsten ARPU und auch deren APPU. Diese Abfrage kann verwendet werden, um Städte mit hohem Wert zu identifizieren, die die meisten Einnahmen pro Kunde für strategische Marketing- und/oder Vertriebsstrategien generieren.

Frage 3: Wie hoch ist die durchschnittliche Anzahl der Bestellungen pro Kunde? Wie hoch ist der durchschnittliche Bestellwert (AOV) pro Kunde?

SELECT
    ROUND(SUM(Quantity) / COUNT(DISTINCT `Customer_ID`), 1) AS AQPU,
    ROUND(SUM(Sales)/COUNT(order_id), 2) as average_order_value
FROM `Tableau Superstore`.orders

Ergebnis: Diese Abfrage berechnet zwei auftragsbezogene KPIs (im Gegensatz zu den finanzbezogenen aus der letzten Abfrage): Durchschnittliche Menge pro Benutzer (AQPU), die die durchschnittliche Menge der pro Kunde gekauften Produkte misst, und durchschnittlicher Bestellwert (AOV), der den durchschnittlichen Verkaufsbetrag pro Bestellung misst. Anhand der Bestellungen kann ein Interessenvertreter mehr über das Kaufverhalten der Kunden erfahren.

Hier sind ein paar schwierige Fragen, die sich beide mit Kundensegmentierungen nach Rangfolge befassen. Bei der ersten Frage werden die Kunden anhand von 4 verschiedenen Kennzahlen betrachtet, während bei der letzten Frage Kombinationen von Kennzahlen kombiniert werden, um das richtige Segment zu finden.

Frage 4: Welches sind die 3 wichtigsten Konten/Kunden nach Gewinnspanne, Gesamtgewinn, Gesamtumsatz und Umsatzhäufigkeit?

WITH customer_metrics AS (
    SELECT 
         customer_name, 
         ROUND(SUM(Sales), 1) AS sales_total,
         ROUND(SUM(Profit), 1) AS profit_total,
         ROUND(SUM(Profit)/SUM(Sales), 1) as profit_margin,
         COUNT(order_id) AS sales_frequency
    FROM  `Tableau Superstore`.orders
    GROUP BY 1
),
ranked_customers AS (
    SELECT 
        customer_name, 
        'Profit Margin' AS metric, 
        profit_margin AS amount,
        DENSE_RANK() OVER (ORDER BY profit_margin DESC) AS customer_rank
    FROM customer_metrics

    UNION ALL

    SELECT 
        customer_name, 
        'Total Profit' AS metric, 
        profit_total AS amount,
        DENSE_RANK() OVER (ORDER BY profit_total DESC) AS customer_rank
    FROM customer_metrics

    UNION ALL

    SELECT 
        customer_name, 
        'Total Sales' AS metric, 
        sales_total AS amount,
        DENSE_RANK() OVER (ORDER BY sales_total DESC) AS customer_rank
    FROM customer_metrics

    UNION ALL

    SELECT 
        customer_name, 
        'Sales Frequency' AS metric, 
        sales_frequency AS amount,
        DENSE_RANK() OVER (ORDER BY sales_frequency DESC) AS customer_rank
    FROM customer_metrics
)
SELECT 
    customer_name, 
    metric, 
    amount,
    customer_rank
FROM ranked_customers
WHERE customer_rank <= 3
ORDER BY 2, 4

Ergebnis: Es handelt sich eher um einen Bottom-up-Bericht, der mehrere Kennzahlen (Gesamtumsatz, Gesamtgewinn, Gewinnspanne und Umsatzhäufigkeit) auf granularer Ebene, d. h. für einzelne Kunden, darstellt. Die Ergebnisse zeigen die 3 wichtigsten Kunden in jeder der 4 Kategorien. Diese Abfrage kann verwendet werden, um Kunden mit hohem Wert für gezielte Marketing- und Vertriebsstrategien zu priorisieren.

Haftungsausschluss - Diese Methode eignet sich zur Segmentierung von Kunden, aber Sie müssen entscheiden, welche Kennzahl Sie zuerst filtern möchten (Umsatz, Gewinn oder Gewinnspanne). Die Reihenfolge wirkt sich auf Ihre Ergebnisse aus und ist nicht immer offensichtlich. Später sehen wir uns eine Abfrage an, bei der die Kunden anhand aller Metriken zusammen eingestuft werden.

Wenn Sie mehr über die Verwendung von WITH in SQL-Abfragen erfahren möchten, besuchen Sie den Kurs Rekursive Abfragen.

Frage 5: Identifizieren Sie Kunden mit hoher Priorität anhand von Kennzahlen wie Gewinn, Gewinnspanne, Kaufhäufigkeit und verkaufter Menge. Segmentieren Sie die Kunden in Gruppen mit niedriger, mittlerer und hoher Priorität, wobei "Hoch" für die Kunden mit dem höchsten Gewinn und der höchsten Kaufhäufigkeit steht.

WITH customer_metrics AS (
    SELECT 
        customer_name, 
        ROUND(SUM(Sales), 1) AS sales_total,
        ROUND(SUM(Profit), 1) AS profit_total,
        ROUND(SUM(Profit)/SUM(Sales), 1) as profit_margin,
        COUNT(order_id) AS sales_frequency
    FROM `Tableau Superstore`.orders
    GROUP BY 1
),
ranked_customers AS (
    SELECT 
        customer_name, 
        sales_total, 
        profit_total, 
        profit_margin,
        sales_frequency, 
        PERCENT_RANK() OVER (ORDER BY profit_margin DESC) AS profit_rank,
        PERCENT_RANK() OVER (ORDER BY sales_frequency DESC) AS frequency_rank
    FROM customer_metrics
),
segmented_customers AS (
 SELECT 
    customer_name, 
    sales_total, 
    profit_total, 
    profit_margin,
    sales_frequency,
    CASE 
        WHEN profit_rank <= 0.1 AND frequency_rank <= 0.1 THEN 'High Profit & High Frequency'
        WHEN profit_rank <= 0.1 THEN 'High Profit'
        WHEN frequency_rank <= 0.1 THEN 'High Frequency'
        ELSE 'Low Profit & Low Frequency'
    END AS customer_segment
 FROM ranked_customers
)
SELECT 
    customer_name, 
    sales_total, 
    profit_total, 
    profit_margin,
    sales_frequency,
    customer_segment
FROM segmented_customers
WHERE customer_segment = 'High Profit & High Frequency'
ORDER BY 3 DESC;

Ergebnis: Diese Abfrage segmentiert Kunden auf der Grundlage von Gewinnspanne und Verkaufshäufigkeit und identifiziert die besten 10 % in beiden Kategorien. Die Ergebnisse zeigen die wertvollsten Kunden als "Hoher Gewinn und hohe Frequenz". Dies steht im Gegensatz zur letzten Abfrage, bei der die 4 Kategorien getrennt waren und es schwierig war, die Ergebnisse zu kombinieren, um die wertvollsten Kunden zu ermitteln. Diese Abfrage kann verwendet werden, um Kunden zu priorisieren und sie dann für Marketing- oder Verkaufskampagnen anzusprechen.

Um mehr über Fensterfunktionen in SQL zu erfahren, empfehle ich unseren interaktiven Kurs Fensterfunktionen.

Analysieren von Produkten

Hier sind einige weitere einfache Fragen, die sich dieses Mal auf Produkte konzentrieren. Die ersten beiden Fragen sind linear zu einigen der Kundenfragen und erfordern eine Trendanalyse, aber die letzte Frage ist eine andere Art von Segmentierungsproblem.

Frage 6: Wie viele verschiedene Produkte werden in jedem Zeitraum bestellt?

SELECT 
    EXTRACT(YEAR FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_Year,
    EXTRACT(MONTH FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_Month,
    COUNT(DISTINCT product_id) AS unique_items
FROM 
    `Tableau Superstore`.orders
GROUP BY 1, 2
ORDER BY 1, 2 DESC

Ergebnis: Diese Abfrage zeigt einen Trend auf der Produktseite für einzelne verkaufte Artikel. Diese Metrik kann als erste Überprüfung der Art des Bestands der verkauften Produkte verwendet werden.

Frage 7: Erstellen Sie einen Bericht über den Umsatz, den Gewinn und die Gewinnspanne für jedes Produkt

SELECT 
    product_id,
    category,
    sub_category,
    ROUND(SUM(sales), 1) AS sales_total,
    ROUND(SUM(profit), 1) AS profit_total,
    ROUND(SUM(profit)/SUM(sales), 1) as profit_margin
FROM `Tableau Superstore`.orders
GROUP BY 1, 2, 3
ORDER BY 4 DESC

Ergebnis:

Diese Abfrage berechnet den Gesamtumsatz, den Gesamtgewinn und die Gewinnspanne für jedes Produkt und zeigt die Produkt-ID, die Kategorie und die Unterkategorie für jedes Produkt an. Die Ergebnisse zeigen die Leistung auf Produktebene, wobei die umsatzstärksten Produkte und die Gewinnspannen für jedes Produkt hervorgehoben werden. Die Abfrage kann verwendet werden, um die umsatzstärksten Produkte zu finden und ihre Rentabilität aufzuzeigen - es handelt sich um einen allgemeinen Bericht, der für die Preisgestaltung und den Bestand verwendet werden kann.

Hier handelt es sich um eine andere Art von Fragen auf harter Ebene, bei der es um Segmentierung geht, die sich jedoch von den ersten beiden Fragen nach Kunden unterscheidet. Hier geht es um die Segmentierung eines gruppierten Ergebnisses - eine Kennzahl nach einer Kategorie.

Frage 8: Welches sind die 3 Artikel mit der niedrigsten Gewinnspanne für jeden Standort, unter Berücksichtigung von Artikel-Standort-Kombinationen. Wenn es Produkte mit Gleichstand gibt, geben Sie diese ebenfalls an.

WITH product_metrics AS (
    SELECT 
        product_id, 
        state,
        city,
        ROUND(SUM(Sales), 1) AS sales_total,
        ROUND(SUM(Profit), 1) AS profit_total,
        ROUND(SUM(Profit)/SUM(Sales), 2) as profit_margin
    FROM `Tableau Superstore`.orders
    GROUP BY 1, 2, 3
),
ranked_products AS (
    SELECT 
        product_id,
        state,
        city, 
        sales_total, 
        profit_total,
        profit_margin,
        DENSE_RANK() OVER (ORDER BY profit_margin ASC) AS profit_rank
    FROM product_metrics
   WHERE profit_margin IS NOT NULL
)
SELECT 
    product_id,
    state,
    city,
    sales_total, 
    profit_margin,
    profit_rank
FROM ranked_products
WHERE profit_rank <= 3
ORDER BY 5 ASC;

Ergebnis: Diese Abfrage berechnet die Produktleistungskennzahlen nach Stadt-Staat-Produkt-ID-Kombinationen und zeigt die niedrigsten Gewinnspannen. Die Ergebnisse zeigen die am wenigsten profitablen Kombinationen und können verwendet werden, um Bereiche mit unterdurchschnittlicher Leistung aufzuzeigen - entweder um bestimmte Produkte in diesen Bereichen zu verbessern oder zu streichen.

Nachlese

Die 8 Beispiele in diesem Artikel setzen die Diskussion aus dem vorangegangenen Artikel über die Beantwortung von Berichtsfragen in einem geschäftlichen Kontext fort. Wir haben mit grundlegenden KPI-Metriken begonnen, sind dann zur Trendanalyse übergegangen und haben mit fortgeschrittenen Ranking- und Segmentierungsberechnungen abgeschlossen.

Beim Durcharbeiten dieser Abfragen haben Sie gängige SQL-Muster kennengelernt, die in der Geschäftsberichterstattung verwendet werden, darunter Aggregationen, Fensterfunktionen und Unterabfragen. Unabhängig davon, ob Sie Schritt für Schritt vorgegangen sind oder sich die Beispiele nur angesehen haben, können diese Techniken als praktische Referenz für die Analyse von Verkaufsdaten in SQL dienen.

Ich hoffe, dass diese Analyse dazu beigetragen hat, zu verdeutlichen, wie man das Spickzettel für Datenanalysten in der Praxis anzuwenden. Wenn etwas unklar ist, wenn Sie Fehler entdecken oder Fragen zum Artikel oder zu einem Teil des Codes haben, lassen Sie uns darüber diskutieren und sich gemeinsam verbessern! Ich würde mich freuen, wenn wir uns auf LinkedInund weitere Beispiele und Einblicke finden Sie auf meinem Tableau Öffentliches Profil oder meinem Blog. Ich freue mich auf Ihre Gedanken!

Tags:

Das könnte Sie auch interessieren