Umsatzdaten analysieren mit dem Spickzettel für Datenanalysten

Haben Sie schon einmal das Data Analyst Cheat Sheet verwendet, um einen Geschäftsdatensatz zu analysieren? In diesem Artikel können Sie die Befehle des Spickzettels in Aktion sehen.

Ich werde einen realen Verkaufsdatensatz untersuchen, um 10 wichtige Geschäftsfragen mit SQL zu beantworten. Anhand der Anweisungen im Spickzettel SQL for Data Analysis zeige ich, wie SQL-Abfragen helfen können, Trends zu analysieren, die Leistung zu messen und aussagekräftige Erkenntnisse aus den Vertriebsdaten zu gewinnen.

Dazu verwenden wir ein offenes Dataset von Tableau, das Verkaufsdaten für Haushaltswaren wie Schreibtische und Schreibwaren enthält. Dieses Dataset bietet eine praktische Möglichkeit, geschäftsbezogene Berichtsfragen zu untersuchen und zu sehen, wie SQL verwendet werden kann, um nützliche Trends aufzudecken.

In diesem Artikel geht es nicht um eine formale Fallstudie, sondern um die Zuordnung von Geschäftsfragen zu allgemeinen SQL-Abfragen. Sie werden sehen, wie SQL-Techniken mit realen Datenanalyse-Herausforderungen übereinstimmen und wie verschiedene metrische Muster in der Berichterstattung auftauchen.

Am Ende werden Sie in der Lage sein, diese Muster zu erkennen und sie auf Ihre eigene SQL-Berichterstellung anzuwenden. Tauchen Sie ein!

Überblick

Diese Analyse gliedert sich in zwei Hauptteile. Zunächst untersuche ich allgemeine Trends, wie z. B. Gesamtumsatz, Gewinn und verkaufte Menge. Dann konzentriere ich mich auf die Verkaufstrends und ermittle Muster im Zeitverlauf, nach Kategorie und über verschiedene Geschäftsbereiche hinweg.

Zur Beantwortung dieser Fragen verwende ich SQL-Techniken wie Aggregationen, Verhältniszahlen, Fensterfunktionen und Unterabfragen. Gemeinsame metrische Muster wie die Gruppierung von Daten, die Verfolgung von Trends, die Berechnung von gleitenden Durchschnitten und die Erstellung von Ranglisten helfen, die Analyse zu strukturieren. Das SQL for Data Analysis Cheat Sheet dient als Referenz für wichtige Funktionen wie GROUP BY, Ranking, laufende Summen und Datumsextraktionen. Ich habe den Beispielen unten Tags hinzugefügt, um sie zu kategorisieren: Klammern für SQL-Techniken [SQL], Klammern für metrische Muster (METRIC) und geschweifte Klammern für Verweise auf das Cheatsheet {SHEET}. Diese Tags helfen, die Analyse für jedes Beispiel unten zu organisieren.

Die Beispiele sind nach Schwierigkeitsgrad geordnet, wobei mit einfachen Abfragen begonnen wird und nach und nach fortgeschrittenere Techniken einbezogen werden. Dies macht es einfach, den Beispielen zu folgen und ähnliche Ansätze auf andere Unternehmensdaten anzuwenden.

Aufbereitung der Daten

Um mit der Analyse zu beginnen, müssen wir den Datensatz vorbereiten. Ich verwende ein offenes Dataset von Tableau Public, das Verkaufsdaten für Haushaltswaren wie Schreibtische und Schreibwaren enthält. Da es in der Geschäftsberichterstattung weit verbreitet ist, eignet es sich hervorragend zum Üben von SQL-Abfragen. Im Folgenden finden Sie eine Schritt-für-Schritt-Anleitung zum Herunterladen des Datensatzes, zum Importieren in eine SQL-Umgebung und zum Vornehmen kleinerer Anpassungen zur Bereinigung der Daten.

Laden Sie das Dataset herunter von Tableau Öffentlich.

• Navigieren Sie zu Lernen → Beispieldaten → Business → Superstore Sales.
• Laden Sie das Superstore Sales-Dataset herunter.

Öffnen Sie das Dataset in Excel.

1. Die relevanten Daten befinden sich auf der Registerkarte "Orders".
2. .xls-Datei öffnen
3. Gehen Sie auf die Registerkarte "Bestellungen" in Ihrer Kalkulationstabelle

Herunterladen der SQL-IDE - optional für alle, die die Metriken nachvollziehen und nachbilden möchten. Ich verwende MySQL Workbench und MySQL Community Server. Wenn Sie dasselbe verwenden möchten, finden Sie die entsprechenden Downloads weiter unten. Ich werde die Schritte zum Importieren des Datensatzes in MySQL Workbench zeigen.

Links zum Herunterladen:

• MySQL Workbench: https://dev.mysql.com/downloads/workbench/
• MySQL Gemeinschaftsserver: https://dev.mysql.com/downloads/mysql/

Ich ziehe es vor, MySQL Workbench mit dem Community Server zu verwenden, daher sind die folgenden Schritte für dessen Einrichtung gedacht:

1. Zuerst bestätige ich, dass die Verbindung zur lokalen Instanz hergestellt ist.

2. Dann erstelle ich ein neues Schema, indem ich diese Anweisungen befolge:

• Klicken Sie auf die lokale Instanzverbindung.
• Wählen Sie Schemas aus dem oberen Menü.
• Klicken Sie mit der rechten Maustaste und wählen Sie Schema erstellen.
• Nennen Sie das Schema Tableau Superstore.
• Importieren Sie die .xls-Datei als .csv unter Tabellen mit dem Assistenten für den Import von Datentabellen (konvertieren Sie die .xls-Datei zuerst in .csv). Eine alternative Methode ist der Import als JSON mit Hilfe eines JSON-Konverters (Link zur Anleitung, diese Methode habe ich verwendet).
• Konfigurieren Sie die Importeinstellungen und wählen Sie den Standardfeldtyp für verschiedene Quellspalten. Lassen Sie die letzte leere Zeile weg!

Nehmen Sie vor dem letzten Schritt manuelle Änderungen in Alter Table vor: Entfernen Sie Leerzeichen aus den Spaltennamen und ersetzen Sie sie durch Unterstriche (_).

Der letzte Schritt besteht darin, mit der rechten Maustaste zu klicken und Schema erstellen zu wählen. Na bitte! Herzlichen Glückwunsch, der komplizierteste Teil liegt hinter Ihnen.

3. Benennen Sie Ihr Schema: Tableau Superstore sollte ausreichen. Verwenden Sie den Assistenten für den Tabellendatenimport, um die Datei hinzuzufügen, wie im Screenshot gezeigt.

4. Laden Sie die .csv-Datei hoch.

Oder laden Sie bei der Konvertierung in JSON stattdessen die JSON-Datei hoch. Dazu müssen Sie einen .csv nach .json Konverter verwenden, wie im Bild gezeigt.

Lassen Sie alle Felder ausgewählt, aber schließen Sie das "leere" Feld aus und setzen Sie alle anderen als "Text"-Felder.

Danach müssen Sie nur noch mit der rechten Maustaste auf die orders Tabelle und wählen Sie Alter Table.

Die Datenvorbereitung ist nun abgeschlossen, und wir können mit der Analyse fortfahren. Angenehmer Bonus: Sie können alle Anfragen und Codeteile aus dem Artikel jederzeit auf der entsprechenden GitHub-Seite finden.

Auffinden der allgemeinen Trends

Fangen wir mit der einfachen Ebene an! Zu Beginn der Analyse beginnen wir mit einigen Key Performance Indicators (KPIs), um einen Überblick über Umsatz, Gewinn und verkaufte Mengen zu erhalten. Diese Abfragen helfen dabei, den Datensatz zusammenzufassen und einen schnellen Überblick über die Gesamtleistung des Unternehmens zu erhalten.

KPI-Metriken: Gesamtumsatz, Gewinn und verkaufte Menge.

Frage: Wie hoch ist der Gesamtumsatz, der Gewinn und die verkaufte Menge?

[AGGREGATION] (INSGESAMT) {AGGREGATFUNKTIONEN}

SELECT 
  ROUND(SUM(Sales), 0) AS sales_total,
  ROUND(SUM(Profit), 0) AS profit_total,
  ROUND(SUM(Quantity), 0) AS quantity_total
FROM `Tableau Superstore`.orders;

Ergebnis: Diese Abfrage berechnet den Gesamtumsatz, den Gewinn und die verkaufte Menge über den gesamten Datensatz. Sie bietet einen Gesamtüberblick über die gesamte Unternehmensleistung.

KPI-Metriken: Monatliche und jährliche Trends

Frage: Wie hoch waren der monatliche/jährliche Umsatz, Gewinn und die verkaufte Menge?

[AGGREGATION] (TREND) {AGGREGATFUNKTIONEN | EXTRAHIEREN VON TEILEN DES DATUMS}

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,
  ROUND(SUM(Sales), 0) AS sales_month,
  ROUND(SUM(Profit), 0) AS profit_month,
  ROUND(SUM(Quantity), 0) AS quantity_month
FROM `Tableau Superstore`.orders
GROUP BY 
  1, 2
ORDER BY 
  1, 2;

Ergebnis: Diese Abfrage schlüsselt Umsatz, Gewinn und Menge nach Jahr und Monat auf und hilft uns, Trends im Laufe der Zeit zu erkennen. Sie ist nützlich, um saisonale Muster oder ein Wachstum von Jahr zu Jahr zu erkennen.

KPI-Metriken: Durchschnittlicher Umsatz, Gewinn und verkaufte Menge

Frage: Wie hoch waren der durchschnittliche Umsatz, der Gewinn und die verkaufte Menge?

[AGGREGATION] (INSGESAMT) {AGGREGATFUNKTIONEN}

SELECT 
  ROUND(AVG(Sales), 0) AS sales_average,
  ROUND(AVG(Profit), 0) AS profit_average,
  ROUND(AVG(Quantity), 0) AS quantity_average
FROM `Tableau Superstore`.orders;

Ergebnis: Anstelle von Gesamtwerten berechnet diese Abfrage den durchschnittlichen Umsatz, Gewinn und die verkaufte Menge pro Auftrag. Sie ist nützlich, um die typische Transaktionsgröße zu verstehen und sie mit verschiedenen Zeiträumen oder Geschäftssegmenten zu vergleichen.

Ich hoffe, Sie haben die erste Ebene der Analyse durchlaufen! Lassen Sie uns nun einen Schritt weiter gehen und fortgeschrittenere Abfragen durchführen. Die mittlere Ebene beginnt mit einer Berechnung des gleitenden Durchschnitts, mit der sich kurzfristige Schwankungen ausgleichen und Umsatztrends im Zeitverlauf erkennen lassen. Mit diesem Ansatz können wir zukünftige Verkäufe prognostizieren, indem wir die Muster der vergangenen Monate analysieren.

Frage: Wie können wir die Verkäufe nach Monat prognostizieren?
[FENSTERFUNKTION] (TREND | GLEITENDES MITTEL) {AGGREGATFUNKTIONEN | EXTRAHIEREN VON DATUMSTEILEN | GLEITENDES MITTEL}

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,
  ROUND(SUM(Sales), 0) AS sales_total,
  ROUND(AVG(SUM(sales)) OVER(ORDER BY EXTRACT(MONTH FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 0) AS moving_average
FROM `Tableau Superstore`.orders
GROUP BY 1, 2
ORDER BY 1, 2;

Ergebnis: Diese Abfrage berechnet die monatlichen Umsatzsummen und wendet einen gleitenden Durchschnitt über die letzten drei Monate (einschließlich des aktuellen) an. Dies hilft bei der Ermittlung von Umsatztrends im Laufe der Zeit, indem kurzfristige Schwankungen geglättet werden, was die Vorhersage künftiger Leistungen erleichtert.

Ich hoffe, Sie sind auf dem Laufenden! Kommen wir nun zu einer fortgeschritteneren Abfrage. Die harte Ebene ist die nächste, bei der wir eine kumulative Summe der Verkäufe für 2017 berechnen. Diese laufende Summe hilft dabei, die Umsatzentwicklung Monat für Monat zu verfolgen, und vermittelt ein klares Bild der Gesamtleistung während des Jahres.

Frage: Wie hoch ist die kumulierte Summe der Verkäufe für 2017? Nach Monat anzeigen.
[FENSTERFUNKTION | CTE] (TREND | KUMULATIV) {AGGREGATFUNKTIONEN | EXTRAHIEREN VON DATUMSTEILEN | LAUFENDE SUMME | CTE}

WITH monthly_report AS (
  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,
    ROUND(SUM(Sales), 0) AS monthly_sales_total
  FROM `Tableau Superstore`.orders
  WHERE EXTRACT(YEAR FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) = 2017
  GROUP BY 1, 2
)
SELECT
  order_month,
  yearly_sales_total,
  SUM(yearly_sales_total) OVER(ORDER BY order_Year) AS running_sales_total
FROM monthly_report
ORDER BY 1, 3 DESC;

Ergebnis: Diese Abfrage berechnet zunächst die monatlichen Umsatzsummen für 2017 mithilfe eines Common Table Expression (CTE). Dann wendet sie eine laufende Summe an, die eine Fensterfunktion verwendet, um die Umsätze progressiv über die Monate zu summieren. Dies hilft bei der Analyse des kumulativen Wachstums und der Erkennung von Umsatztrends innerhalb des Jahres.

Bestimmung von Umsatztrends

Beginnen wir wieder mit der einfachen Ebene, aber diesmal konzentrieren wir uns auf regionale und kategoriebezogene Erkenntnisse. Diese Abfragen helfen dabei, wichtige Kennzahlen nach verschiedenen Segmenten des Unternehmens aufzuschlüsseln und bieten ein klareres Bild als die Betrachtung der Gesamtsummen.

Frage: Wie hoch sind der Gesamtumsatz, der Gewinn und die Menge für jede Region?

[AGGREGATION] (GRUPPE) {AGGREGATFUNKTIONEN}

SELECT 
  Region,
  ROUND(SUM(Sales), 0) AS sales_total,
  ROUND(SUM(Profit), 0) AS profit_total,
  ROUND(SUM(Quantity), 0) AS quantity_total
FROM `Tableau Superstore`.orders
GROUP BY 1
ORDER BY 2 DESC;

Ergebnis: Diese Abfrage gruppiert Umsatz, Gewinn und Menge nach Region und ermöglicht einen Leistungsvergleich zwischen verschiedenen geografischen Gebieten. Sie hilft bei der Identifizierung der leistungsstärksten Regionen und derjenigen, die möglicherweise einer weiteren Analyse bedürfen.

Frage: Wie hoch ist die Gewinnspanne für jede Produktkategorie?

[AGGREGATION | VERHÄLTNIS] (GRUPPE) {AGGREGATFUNKTIONEN | GANZZAHLIGE DIVISION}

SELECT
  Category,
  ROUND(SUM(Sales), 0) AS sales_total,
  ROUND(SUM(Profit), 0) AS profit_total,
  ROUND(SUM(Profit)/SUM(Sales), 2) AS profit_margin
FROM `Tableau Superstore`.orders
GROUP BY 1
ORDER BY 4 DESC;

Ergebnis: Diese Abfrage berechnet den Umsatz, den Gewinn und die Gewinnspanne für jede Produktkategorie. Indem wir den Gewinn durch den Umsatz dividieren, erhalten wir eine Rentabilitätskennzahl, mit deren Hilfe wir feststellen können, welche Kategorien die höchsten Erträge erzielen.

Gehen wir nun auf die mittlere Ebene über, wo wir die Umsatzveränderungen im Jahresvergleich analysieren und den prozentualen Anteil am Gesamtumsatz für jede Kategorie berechnen.

Frage: Erstellen Sie einen Bericht, der den Umsatz, den durchschnittlichen Umsatz und die Veränderung von Jahr zu Jahr (Delta) nach Jahr zeigt.
[FENSTERFUNKTION] (TREND | DELTA) {AGGREGATFUNKTIONEN | EXTRAHIEREN VON TEILEN DES DATUMS | DIFFERENZ ZWISCHEN ZWEI ZEILEN (DELTA)}

SELECT DISTINCT
    EXTRACT(YEAR FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_Year,
    ROUND(SUM(sales), 0) AS sales_total,
    ROUND(LAG(SUM(SALES)) OVER(ORDER BY EXTRACT(YEAR FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y')))), 0) AS sales_prev_year,
    (ROUND(SUM(sales), 0)) - (ROUND(LAG(SUM(SALES)) OVER(ORDER BY EXTRACT(YEAR FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y')))), 0)) AS sales_yoy_difference
FROM `Tableau Superstore`.orders
GROUP BY 1
ORDER BY 1;

Ergebnis: Diese Abfrage berechnet den Gesamtumsatz pro Jahr und vergleicht ihn mit dem Vorjahr unter Verwendung der Fensterfunktion LAG(). Das Ergebnis ist eine Jahresdifferenz, die hilft, das jährliche Umsatzwachstum oder den Umsatzrückgang auf einen Blick zu verfolgen.

Frage: Wie hoch ist der prozentuale Anteil der einzelnen Kategorien am Gesamtumsatz?
[SUBQUERY] (GROUP | RATIO) {AGGREGATE FUNCTIONS | COMPUTING THE PERCENT OF TOTAL WITHIN A GROUP}

SELECT DISTINCT
    category,
    ROUND(SUM(sales), 0) AS sales_total,
    ROUND((SUM(sales) / (SELECT SUM(sales) FROM `Tableau Superstore`.orders)) * 100, 0) AS percent_oftotal
FROM `Tableau Superstore`.orders
GROUP BY 1;

Ergebnis: Diese Abfrage berechnet den Umsatz jeder Kategorie als Prozentsatz des Gesamtumsatzes. Die Unterabfrage stellt sicher, dass der Nenner fest bleibt, was einen genauen Vergleich der Leistung der Kategorien im Verhältnis zum gesamten Datensatz ermöglicht.

Wir haben den letzten Teil der Analyse, die harte Ebene, erreicht und stellen uns nun einer letzten Herausforderung. Diesmal werden wir die beiden umsatzstärksten Unterkategorien innerhalb jeder Produktkategorie ermitteln, um zu sehen, welche Artikel den größten Umsatz bringen.

Frage: Welches sind die beiden umsatzstärksten Unterkategorien innerhalb jeder Gruppe?
[FENSTERFUNKTION | CTE] (RANK) {AGGREGATE FUNKTIONEN | RANK | CTE}

WITH category_ranking AS (
  SELECT
    Category,
    Sub_Category,
    ROUND(SUM(Sales), 0) AS sales_total,
    DENSE_RANK() OVER(PARTITION BY Category ORDER BY SUM(SALES) DESC) AS sub_category_rank
  FROM `Tableau Superstore`.orders
  GROUP BY 1, 2
)
SELECT
  Category,
  Sub_Category,
  sales_total
FROM category_ranking
WHERE sub_category_rank <= 2
ORDER BY 1, 3 DESC;

Ergebnis: Diese Abfrage ordnet die Unterkategorien innerhalb jeder Kategorie auf der Grundlage des Gesamtumsatzes unter Verwendung der Fensterfunktion DENSE_RANK() ein. Da wir nach Kategorien gruppieren, können wir keine einfache Aggregatfunktion für das Ranking verwenden. Stattdessen berechnet der Common Table Expression (CTE) zunächst die Rangfolge, und die endgültige Auswahl filtert nur die beiden besten Unterkategorien pro Kategorie heraus.

Nachbereitung

In diesem Artikel haben wir uns mit 10 wichtigen SQL-Abfragen beschäftigt, die bei der Beantwortung von Fragen im Zusammenhang mit der Berichterstattung helfen. Wir begannen mit grundlegenden KPI-Metriken, gingen über zu Trendanalysen und Prognosen und schlossen mit fortgeschrittenen Ranglisten- und kumulativen Berechnungen.

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!