BigQuery-Berichte mit Google Analytics Rohdaten (UA + GA4)

Im folgenden habe ich einige einfache Analytics-Query zusammengestellt. So richten Sie die BigQuery-Verknüpfung in Ihrer Google Analytics 4-Property(GA4) ein.

Universal Analytics

Google Analytics 4

Wie man die Vorhersagegenauigkeit seines Machine Learning-Modells verbessert

Es gehört zu den frustrierenden Dingen des Jobs, wenn man viele Stunden damit verbracht hat Daten zusammenzuführen, zu transformieren, zu bereinigen und zu labeln etc. und nach mehreren Trainingsstunden des ML Modells haben wir eine geringe Genauigkeit oder einen großen Fehlerbereich.

Je nach Kontext kann es passieren, dass wir ein Modell bei zu geringer Genauigkeit einfach nicht verwenden können.

Beispiel aus BQ: Abweichung: der durchschnittliche Abstand zwischen jedem Punkt und dem arithmetischen Mittel. Fehler: beinhaltet sowohl die Varianz (die Streuung der vorhergesagten Werte zueinander) als auch die Verzerrung (der Abstand des vorhergesagten Wertes von seinem wahren Wert). R-Quadrat: ist ein statistisches Maß dafür, wie dicht die Daten an der angepassten Regressionslinie liegen (1 wäre perfekt).

Wie können wir die Genauigkeit unseres maschinellen Lernmodells verbessern? Es gibt verschiedene Möglichkeiten, wie die Genauigkeit eines Modells verbessert werden kann:

Mehr Daten als Lern-Grundlage

Daten sind nur dann aussagekräftig, wenn man genügend davon hat. Jede Datenprobe liefert einen gewissen Input und eine Perspektive für die Gesamtgeschichte, die Ihre Daten erzählen wollen. Eine Methode, die Leistung Ihres Modells zu verbessern und seine Genauigkeit zu erhöhen, besteht darin, mehr Beispieldaten zu dem Trainingsdatensatz hinzuzufügen.

Die ML-Modelle von BigQuery verlangen grundsätzlich mindestens eine Stichprobe von 1.000.

Auf diese Weise erhalten Sie mehr Details zu Ihren Daten und können Ihr Modell einfacher fintunen, was zu einer besseren Genauigkeit führen kann. Denken Sie daran: Je mehr Informationen Sie Ihrem Modell zur Verfügung stellen, desto mehr wird es lernen und desto mehr Fälle wird es korrekt identifizieren können.

Man muss hier aber ehrlicherweise sagen, dass eine Vergrößerung der Beispieldaten auch zu einer Verschlechterung der Aussagekraft des Modells führen kann. Das heißt dann aber nicht, dass ich weniger Daten nehmen sollte, sondern, dass meine ursprüngliche Datenbasis erst recht zu kleine war! Eben weil das ja in dem Fall (leider) nochmal unterstreicht, dass das Modell noch Anpassungen bedarf.

Betrachten Sie das Problem mit anderen Augen

Vielleicht stellen Sie einfach die falschen Fragen oder versuchen, die falsche Geschichte zu verstehen. Die Betrachtung des Problems aus einer neuen Perspektive kann Ihrem Modell wertvolle Informationen hinzufügen und Ihnen helfen, verborgene Beziehungen zwischen den Variablen der Geschichte aufzudecken. Wenn Sie andere Fragen stellen, kann dies zu besseren Ergebnissen und schließlich zu einer höheren Genauigkeit führen.

Dieser Hinweis klingt sehr allgemein ist aber eine sehr häufige Lösung des Problems.

Fügen Sie Ihren Daten etwas Kontext hinzu.

Kontext ist in jeder Situation wichtig, und das gilt auch für das Training eines Modells für maschinelles Lernen. Manchmal kann ein Punkt der Daten keine Geschichte erzählen, so dass Sie mehr Kontext hinzufügen müssen, damit ein Algorithmus, den wir auf diese Daten anwenden wollen, eine gute Leistung erbringt.

Mehr Kontext kann immer zu einem besseren Verständnis des Problems und schließlich zu einer besseren Leistung des Modells führen. Stellen Sie sich vor, ich erzähle Ihnen, dass ich ein Auto verkaufe, einen Audi. Das allein gibt Ihnen nicht viel Aufschluss über das Auto. Wenn ich aber die Farbe, das Modell und die gefahrene Strecke hinzufüge, können Sie sich ein besseres Bild von dem Auto und seinem möglichen Wert machen.

In einem Datensatz können das zum Beispiel weitere Variablen sein, die Sie dem Lernprozess hinzufügen. Nehmen wir mein Beispiel und Sie wollen anhand von vergangen Autoverkäufen errechnen, wie wertvoll ein Auto ist. Dann können neben der Marke mit Sicherheit noch, die gefahrenen Kilometer oder eben das Zulassungsjahr spannend sein.

Trainieren Sie Ihr Modell mit Kreuzvalidierung

Beim maschinellen Lernen ist die Kreuzvalidierung eine Technik, die zur Verbesserung des Modelltrainings verwendet wird, indem die gesamte Trainingsmenge in kleinere Teile aufgeteilt wird und dann jeder Teil zum Trainieren des Modells verwendet wird.

Diagramm der k-fachen Kreuzvalidierung.

Mit diesem Ansatz können wir den Trainingsprozess des Algorithmus verbessern, indem wir ihn mit den verschiedenen Chunks trainieren und den Durchschnitt über das Ergebnis bilden. Die Kreuzvalidierung wird verwendet, um die Leistung des Modells zu optimieren. Dieser Ansatz ist sehr beliebt, weil er so einfach und leicht zu implementieren ist.

Experimentieren Sie mit unterschiedlichen Algorithmen

Was ist, wenn Sie alle bisher besprochenen Ansätze ausprobiert haben und Ihr Modell immer noch zu einer niedrigen oder nur durchschnittlichen Genauigkeit führt?

Manchmal wählen wir einen Algorithmus für die Implementierung, der nicht wirklich auf unsere Daten anwendbar ist, so dass wir nicht die erwarteten Ergebnisse erhalten. Ändern Sie den Algorithmus, den Sie zur Implementierung Ihrer Lösung verwenden. Das Ausprobieren verschiedener Algorithmen führt dazu, dass Sie mehr Details über Ihre Daten und die Geschichte, die sie zu erzählen versuchen, herausfinden.

Sinnvoller Umgang mit fehlenden Werten und Ausreißern

Das unerwünschte Vorhandensein von fehlenden Werten und Ausreißern in den Trainingsdaten verringert oft die Genauigkeit eines Modells oder führt zu einem verzerrten Modell. Dies führt zu ungenauen Vorhersagen. Der Grund dafür ist, dass wir das Verhalten und die Beziehung zu anderen Variablen nicht richtig analysieren. Es ist also wichtig, sich mit fehlenden Werten und Ausreißern auseinanderzusetzen.

Fehlende Werte: Bei kontinuierlichen Variablen können Sie die fehlenden Werte durch Mittelwert, Median und Modus ersetzen. Bei kategorialen Variablen können Sie die Variablen als eine separate Klasse behandeln. Sie können auch ein Modell erstellen, um die fehlenden Werte vorherzusagen

Ausreißer: Sie können die Beobachtungen löschen, eine Transformation durchführen oder auch Ausreißerwerte separat behandeln. 

Schlusswort

Die Verbesserung der Genauigkeit eines Modells für maschinelles Lernen ist eine Fähigkeit, die nur durch Übung verbessert werden kann. Je mehr Projekte Sie erstellen, desto besser wird Ihr Gespür dafür, welchen Ansatz Sie beim nächsten Mal verwenden sollten, um die Genauigkeit Ihres Modells zu verbessern.

ETL vs. ELT: was ist der Unterschied?

Bei der Betrachtung von ETL- und ELT-Prozessen ist es wichtig, die Hauptunterschiede und die spezifischen Anwendungsfälle für zu berücksichtigen.

ETL (Extrahieren, Transformieren, Laden) und ELT (Extrahieren, Laden, Transformieren) sind beides Datenintegrationsmethoden, die Daten von einer Quelle in ein Data Warehouse übertragen. Trotz ihrer Ähnlichkeiten unterscheiden sich ETL und ELT in wesentlichen Punkten.

ETL- Prozess – Überblick

Extrahieren, Transformieren und Laden (ETL) ist eine Methode der Datenintegration, bei der Rohdaten aus Quellen extrahiert, auf einem sekundären Verarbeitungsserver transformiert und dann in eine Zieldatenbank geladen werden.

ETL wird eingesetzt, wenn Daten so umgewandelt werden müssen, dass sie mit dem Datenregime einer Zieldatenbank übereinstimmen. Diese Methode ist vor allem bei der Nutzung von eigenen Servern mit begrenztem Speicher und begrenzter Verarbeitungsleistung weit verbreitet.

Online Analytical Processing (OLAP) Data Warehouses akzeptieren nur relationale SQL-basierte Datenstrukturen.

Bei dieser Art von Data Warehouse stellt ein Protokoll mit ETL-Prozess die Konformität sicher, indem es die extrahierten Daten an einen Verarbeitungsserver weiterleitet und dann die nicht konformen Daten in SQL-basierte Daten umwandelt.

Die extrahierten Daten gelangen erst dann vom Verarbeitungsserver in das Data Warehouse, wenn sie erfolgreich umgewandelt wurden.

ELT-Prozess – Überblick

Im Gegensatz zu ETL-Prozessen müssen beim Extrahieren, Laden und Transformieren (ELT) vor dem Ladevorgang keine Datenumwandlungen vorgenommen werden.

ELT lädt Rohdaten direkt in ein Ziel-Data-Warehouse, anstatt sie zur Transformation auf einen Verarbeitungsserver zu verschieben.

Mit ELT finden Datenbereinigung, -anreicherung und -umwandlung innerhalb des Data Warehouse selbst statt. Die Rohdaten werden auf unbestimmte Zeit im Data Warehouse gespeichert, so dass sie mehrfach umgewandelt werden können.

ELT ist eine relativ neue Entwicklung, die durch die Erfindung skalierbarer Cloud-basierter Data Warehouses ermöglicht wurde.

Cloud-Data-Warehouses wie Snowflake, Amazon Redshift, Google BigQuery und Microsoft Azure verfügen alle über die digitale Infrastruktur in Bezug auf Speicher- und Verarbeitungsleistung, um Rohdaten-Repositories und In-App-Transformationen zu erleichtern.

Obwohl ELT nicht universell eingesetzt wird, wird die Methode immer beliebter.

ETL vs. ELT: Wie unterscheidet sich der ETL-Prozess vom ELT-Prozess?

ETL und ELT unterscheiden sich vor allem in zwei Punkten. Ein Unterschied besteht darin, wo die Daten umgewandelt werden, und der andere darin, wie Data Warehouses die Daten aufbewahren.

  • ETL wandelt Daten auf einem separaten Verarbeitungsserver um, während ELT Daten innerhalb des Data Warehouse selbst umwandelt.
  • ETL überträgt keine Rohdaten in das Data Warehouse, während ELT Rohdaten direkt an das Data Warehouse sendet.

Bei ETL wird der Prozess der Datenaufnahme dadurch verlangsamt, dass die Daten vor dem Ladevorgang auf einem separaten Server transformiert werden.

ELT hingegen ermöglicht eine schnellere Datenaufnahme, da die Daten nicht zur Umstrukturierung an einen zweiten Server geschickt werden. Mit ELT können die Daten sogar gleichzeitig geladen und umgewandelt werden.

Die Rohdatenaufbewahrung von ELT schafft ein umfangreiches historisches Archiv für die Erstellung von Business Intelligence. Wenn sich Ziele und Strategien ändern, können BI-Teams die Rohdaten erneut abrufen, um neue Transformationen unter Verwendung umfassender Datensätze zu entwickeln. ETL hingegen erzeugt keine vollständigen Rohdatensätze, die endlos abgefragt werden können.

Diese Faktoren machen ELT flexibler, effizienter und skalierbarer, insbesondere für die Aufnahme großer Datenmengen, die Verarbeitung von Datensätzen, die sowohl strukturierte als auch unstrukturierte Daten enthalten, und die Entwicklung vielfältiger Business Intelligence.

Auf der anderen Seite ist ETL ideal für rechenintensive Transformationen, Systeme mit Legacy-Architekturen oder Daten-Workflows, die vor der Eingabe in ein Zielsystem manipuliert werden müssen, wie z. B. die Löschung von personenbezogenen Daten.

Fazit

Cloud Data Warehouses haben eine neue Dimension der Datenintegration eröffnet, aber die Entscheidung zwischen ETL und ELT hängt von den Bedürfnissen eines Teams ab.

Obwohl ELT Vorteile bietet, werden einige Teams bei ETL bleiben, weil die Methode für ihre spezielle Bereitstellung sinnvoll ist, ob mit oder ohne Legacy-Infrastruktur.

BI Analyse mit Hilfe von BigQuery und Data Studio für Onlineshops

Business Intelligence (BI) ist ein technologiegestützter Prozess für die Analyse von Daten und die daraus resultierende Bereitstellung von hilfreichen Informationen, die Führungskräften und Mitarbeitern helfen, fundierte Geschäftsentscheidungen zu treffen.

Mit Hilfe eines Beispieldatensatzes eines Onlineshops wollen wir verschiedene Basis-BI-Kennzahlen ermitteln und auswerten.

Als Analysewerkzeuge werden wir BigQuery in Kombination mit Data Studio verwenden.

Für unsere Analyse schauen wir uns folgende KPI an:

Kurzer Blick auf den Beispieldatensatz

Unser Beispieldatensatz ist öffentlich unter folgendem Link abrufbar: https://martin-grellmann.de/wp-content/uploads/2022/02/Beispieldatensatz-Onlineshop-2.xlsx

Mit Hilfe des Datensatzes können alle hier durchgeführten Berechnungen nachvollzogen werden.

Der Datensatz ist eine Exceltabelle mit 2 Tabs:

Käufe
Newsletter-Anmeldungen

Im ersten Tab findet man über 68.000 Zeilen. Jede Zeile entspricht einem Kauf. In der Tabelle finden wir das Datum des Kaufes, eine Kundennummer, eine Mailadresse des Kunden, den Warenkorb (mit Produkten), den Umsatz des Kaufes und den Affiliate über den der Kunde kam. Der Affiliate wird der Kundennummer fest zugeordnet. Das heißt, dass sobald ein Kunde über einen Affiliate kam, erscheint er bei jedem Kauf in der Tabelle.

Im zweiten Tab finden wir eine Tabelle mit 83.000 Zeilen. jede Zeile entspricht einer Mailadresse in unserem Newsletter-Verteiler. Der einfachheithalber gibt es in dem Newsletter-Beispieldatensatz zwar E-Mailadressen, die noch keinen Kauf getätigt haben, aber umgedreht ist jeder Käufer auch im Newsletter. Das ist in der Regel nicht der Fall, soll uns bei unserer beispielhaften BI-Analyse aber nicht stören.

Eine einmalige Analyse in dieser Größenordnung lässt sich problemlos in Excel durchführen. Wenn der Datensatz größer wird, die Berechnungen komplexer, oder wir einfach die Analysen automatisieren wollen, bietet es sich an mit Data Warehouses zu arbeiten. Ich habe den Datensatz hier exemplarisch in dem Data Warehouse von Google – BigQuery – gespeichert um die Transformationen dort durchführen zu können. Im Gegensatz zu Excel, wo ich die Datentransformation und die Visualisierung im selben Tool durchführe, brauche ich bei einem Data Warehouse, wie BigQuery, noch ein Visualisierungs-Tool. Wir nehmen hier ebenfalls die kostenlose Version von Google: Data Studio.

Fangen wir an, die KPI zu berechnen und auszuwerten.

Durchschnittlicher Bestellwert pro Warenkorb

Der durchschnittliche Warenkorb-Wert ist eine häufig verwendete E-Commerce KPI, die angibt, wie hoch der Durchschnittsbestellwert über einen bestimmten Zeitraum ist.

Oft kaufen sich Onlineshops über bezahlte Anzeigen Besucher teuer ein, die dann ein bestimmtes Produkt kaufen wollen. Wenn man es über verschiedene Methoden schafft, dass der Käufer zusätzliche Produkte kauft, steigert dass nicht nur den durchschnittlichen Warenkorb-Wert, sondern gleichzeitig auch die Profitabilität der Anzeigen und damit des gesamten Onlineshops.

Uns interessiert der Warenkorb-Wert lediglich auf Jahres-Basis.

In BigQuery brauchen wir für den ⌀ Warenkorbs-Wert keine Transformation durchführen, sondern können direkt in Data Studio das Diagramm erstellen:

Da wir keinen branchenspezifischen Benchmark für den durchschnittlichen Warenkorbwert haben, können wir nicht sagen ob die Höhe eher gut oder eher schlecht ist.

Alles was wir sagen können ist, dass die Höhe über die letzten Jahre auf dem gleichen Niveau geblieben ist.

Darauf aufbauend, könnte jetzt das Marketing-Team einen Katalog mit Maßnahmen zusammenstellen, wie man versuchen könnte, den durchschnittlichen Warenkorbwert zu erhöhen.

Aufgabe der Business Intelligence Einheit ist es, während der Umsetzung der Maßnahmen auszuwerten, wie gut diese funktionieren.

Beispiele für Maßnahmen für die Warenkorb-Wert-Steigerung:

  • Kostenloser Versand ab Mindestbestellwert
  • Bündeln von komplementären Produkten (Produkt Bundle)
  • Personalisieren Sie die Produktauswahl
  • In-Cart-Upsells

Customer Lifetime Value

Der Customer Lifetime Value (CLV) gibt an, wie viel ein Unternehmen mit einem durchschnittlichen Kunden im Laufe der Geschäftsbeziehung verdient.

Mit Hilfe des CLV von verschiedenen Zielgruppen (z. B. von Facebook) können wir besser einschätzen, wie viel wir für die Neukundenakquise ausgeben können.

Ein Kunde, der den ersten Kauf bereits vor 5 Jahren getätigt hat, wird in der Regel einen höheren CLV haben, als ein Kunde, der gestern das erste Mal etwas gekauft hat. Deshalb sollte man den CLV mindestens nach Jahr des Erstkaufes segmentieren.

In BigQuery habe ich jetzt ein neues Data Set “basic_kpi” erstellt. Darauf aufbauend habe ich folgende Query für die CLV-Tabelle geschrieben:

CREATE OR REPLACE TABLE basic_kpi.clv

AS

with kaeufer AS (SELECT 
    Kundennummer,
    MIN(EXTRACT(YEAR FROM Datum)) AS Erstkauf,
    sum(Umsatz) AS Umsatz,

FROM `beispieldatensatz-341222.rohdaten.Kaeufe`
GROUP BY Kundennummer
)

SELECT
    Erstkauf,
    SAFE_DIVIDE(sum(Umsatz), Count(Kundennummer)) AS CLV,

FROM kaeufer
GROUP BY Erstkauf

In Data Studio ist das zugehörige Diagramm schnell erstellt:

Wie zu vermuten war, ist der CLV bei Kunden, die schon mehrere Jahre regelmäßig in unserem Beispiel-Onlineshop kaufen größer als bei Neukunden. Zusätzlich fällt aber ebenfalls auf, dass der CLV nicht immer weiter steigt, sondern aktuell liegt die Grenze (im Bezug auf den Jahresdurchschnitt) grob bei 230 Euro.

Jetzt haben wir ein besseres Gefühl dafür, was man für Neukunden ausgeben kann, als ohne die Auswertung.

Neben der Betrachtung der Rohertragsmarge über die Lebenszeit und den Akquisitionskosten für Neukundenakquise wäre zusätzlich noch wichtig zu wissen, was für die regelmäßige Reaktivierung von Bestandskunden zu kalkulieren ist.

Wenn man den CLV regelmäßig monitort, ist es möglich Maßnahmen zur Steigerung dieser wichtigen KPI besser zu bewerten.

Maßnahmen zur Verbesserung des CLV könnten sein:

  • Remarketing-Kampagnen zur Reaktivierung
  • Newsletter (Für Up- und Cross-Sells sowie Reaktivierungsgutscheine)
  • Guter Kundenservice
  • Beziehungsmarketing über Social Media

Wert Kunde nach Tagen

Bei der Betrachtung des CLV sind wir bereits darauf eingegangen, dass es hilfreich ist, zu wissen, wie viel Wert die eigenen Kunden sind, weil man dadurch unter anderem besser einschätzen kann, wie viel man für die Neuakquise von Kunden ausgeben kann.

Da sich der CLV teilweise aber über Jahre aufbaut, ist es neben dem durchschnittlichen Gesamtumsatz von Kunden oft auch hilfreich zu wissen, wie viel Umsatz ein Neukunde nach X Tagen generiert.

Wir werden uns anschauen, wie viel ein durchschnittlicher Kunde nach

  • 90 Tagen
  • 180 Tagen
  • 360 Tagen

an Umsatz generiert.

In BigQuery dauert die Berechnung nur wenige Sekunden. Die SQL-Query hierzu sieht folgendermaßen aus:

CREATE OR REPLACE TABLE basic_kpi.umsatz_nach_tagen

AS

with kaeufer AS (SELECT 
    Kundennummer AS Kundennummer_kaeufer,
    MIN(Datum) AS Erstkauf,

FROM `beispieldatensatz-341222.rohdaten.Kaeufe`
GROUP BY Kundennummer
),

kaeufe_date_diff AS(    SELECT  
        Datum,
        Kundennummer,
        Umsatz,
        Erstkauf,
        DATE_DIFF(Datum, Erstkauf, DAY) AS Tage_seit_erstkauf,

FROM `beispieldatensatz-341222.rohdaten.Kaeufe` INNER JOIN kaeufer ON Kundennummer = Kundennummer_kaeufer),

Umsatz_nach_tagen AS (
SELECT 
    Kundennummer,
    Extract(Year FROM Erstkauf) AS Erstkauf,
    CASE 
        WHEN Tage_seit_erstkauf >= 0 AND Tage_seit_erstkauf <= 90 THEN Umsatz
        ELSE null
    END
    AS Umsatz_0_90,

    CASE 
        WHEN Tage_seit_erstkauf > 90 AND Tage_seit_erstkauf <= 180 THEN Umsatz
        ELSE null
    END
    AS Umsatz_90_180,
    CASE 
        WHEN Tage_seit_erstkauf > 180 AND Tage_seit_erstkauf <= 360 THEN Umsatz
        ELSE null
    END
    AS Umsatz_180_360,

FROM kaeufe_date_diff),

Kundennr_group_by AS (Select 
    Kundennummer,
    Any_Value(Erstkauf) AS Erstkauf,
    Sum(Umsatz_0_90) AS Umsatz_0_90,
    SUM(Umsatz_90_180) AS Umsatz_90_180,
    Sum(Umsatz_180_360) AS Umsatz_180_360,

FROM Umsatz_nach_tagen
GROUP BY Kundennummer)

SELECT 
    Erstkauf,
    SAFE_DIVIDE(Sum(Umsatz_0_90), Count(Kundennummer)) AS CLV_0_90,
    SAFE_DIVIDE(Sum(Umsatz_90_180), Count(Kundennummer)) AS CLV_90_180,
    SAFE_DIVIDE(Sum(Umsatz_180_360), Count(Kundennummer)) AS CLV_180_360,
From Kundennr_group_by
Group By Erstkauf

In Data Studio könnte man den Wert von Kunden nach Tagen zum Beispiel mit Hilfe einer Tabelle visualisieren:

Die Optimierung des Kundenwertes, zum Beispiel innerhalb der ersten 90 Tage, kann durch die selben Maßnahmen optimiert werden, wie der CLV. Zusätzlich kann man sich aber auch die Maßnahmen zur Warenkorbwert-Steigerung anschauen. Diese hätten ebenfalls einen positiven Impact auf diese wichtige KPI.

“Kunden die, diesen Artikel gekauft haben…”

Eine gute Möglichkeit Cross-Sell-Potentiale aufzudecken, ist es, Kunden, die Produkte X gekauft haben, Produkte vorzuschlagen, die andere Kunden zusammen mit dem Produkt gekauft haben.

Hier bietet es sich an mit maschinellen Lernalgorithmen zu arbeiten.

Wem das nicht möglich ist, kann dies in etwas abgeschwächter Form auch händisch umsetzen.

Hier habe ich ein Skript geschrieben mit dem wir innerhalb kurzer Zeit für jedes je verkaufte Produkte (dynamisch) alle Top-Cross-Selling-Produkte in einer Tabelle aufzeigen können. Hier ist das Skript:

-- Variablen deklarieren
DECLARE z STRING; 
DECLARE l INT64;
DECLARE i INT64 DEFAULT 0;
DECLARE p ARRAY<STRING>;
DECLARE k ARRAY<STRING>;

-- Alle Produkte Array erstellen
CREATE OR REPLACE TABLE basic_kpi.All_Products

AS

With All_Products_1 AS (SELECT 
    SPLIT(Warenkorb) AS Warenkorb_array,
FROM `beispieldatensatz-341222.rohdaten.Kaeufe`),

All_Products_2 AS (SELECT 
        Warenkorb_array AS Alle_Produkte,
FROM All_Products_1
CROSS JOIN UNNEST(All_Products_1.Warenkorb_array) AS Warenkorb_array
GROUP BY Warenkorb_array)

SELECT 
    ARRAY(SELECT Alle_Produkte FROM All_Products_2) AS Alle_Produkte_array,
FROM All_Products_2 LIMIT 1;

SET p = (Select Alle_Produkte_array FROM basic_kpi.All_Products);

-- Variablen belegen
SET l = ARRAY_LENGTH(p); --Index letzes Produkt--

Create or Replace Table basic_kpi.Also_Bought_Products(Produkt STRING, Warenkorb_array_string STRING ,Count_purchases_string STRING);

-- Schleife
WHILE i <l DO
SET z = p[Offset(i)];

CREATE OR REPLACE temp TABLE temp_Also_Bought_Products

AS

With AB_Products_1 AS (SELECT 
    SPLIT(Warenkorb) AS Warenkorb_array,
    CASE 
        WHEN CONTAINS_SUBSTR(Warenkorb, z) THEN z
    END
    AS Produkt,
FROM `beispieldatensatz-341222.rohdaten.Kaeufe`),

AB_Products_2  AS (
    SELECT 
        Produkt,
        Warenkorb_array,
    FROM AB_Products_1
    Where Produkt is not null),

AB_Products_3  AS (SELECT 
        Produkt,
        Warenkorb_array,
FROM AB_Products_2
CROSS JOIN UNNEST(AB_Products_2.Warenkorb_array) AS Warenkorb_array
Where not CONTAINS_SUBSTR(Warenkorb_array, z)),

AB_Products_4  AS (SELECT 
    Warenkorb_array,
    Count(Warenkorb_array) AS Count_purchases,
    Any_value(Produkt) AS Produkt,
FROM AB_Products_3
GROUP BY Warenkorb_array
ORDER BY Count_purchases DESC),

AB_Products_5  AS (SELECT 
    Produkt,
    Warenkorb_array,
    Cast(Count_purchases AS STRING) AS Count_purchases_String,
FROM AB_Products_4),

AB_Products_6  AS (SELECT 
    Any_Value(Produkt) AS Produkt,
    ARRAY(SELECT Warenkorb_array FROM AB_Products_5) AS Warenkorb_array_string,
    ARRAY(SELECT Count_purchases_String FROM AB_Products_5) AS Count_purchases_String,

FROM AB_Products_5),

AB_Products_7 AS (SELECT 
    Produkt,
    ARRAY_TO_STRING(Warenkorb_array_string, ",") AS Warenkorb_array_string,
    ARRAY_TO_STRING(Count_purchases_string, ",") AS Count_purchases_string,

FROM AB_Products_6)

SELECT 
    Produkt,
    Warenkorb_array_string,
    Count_purchases_string,
FROM AB_Products_7;

INSERT basic_kpi.Also_Bought_Products(Produkt, Warenkorb_array_string,Count_purchases_string)
VALUES((SELECT Produkt FROM temp_Also_Bought_Products),(SELECT Warenkorb_array_string FROM temp_Also_Bought_Products), (SELECT Count_purchases_string FROM temp_Also_Bought_Products));

SET i = i +1;
END WHILE;

Hier die Tabelle in BigQuery:

In Data Studio könnte man das dann zum Beispiel folgendermaßen umsetzen:

Einstiegsprodukte

Vor allem für die Neukundenakquise ist es oft hilfreich mehr Informationen über Einstiegsprodukte von bisherigen Kunden zu haben.

Neben der Häufigkeit, wie oft ein Produkt bzw. ein Produkt-Bundle Einstieg für Neukunden war, kann auch der CLV auf dieser Basis spannend sein.

Für BigQuery würde das zugehörige Skript so aussehen:

CREATE OR REPLACE TABLE basic_kpi.Einstiegsprodukte

AS

with Einstieg_1 AS (SELECT  
    Kundennummer AS Kd_nr,
    MIN(Datum) AS Kunde_seit,
    SUM(Umsatz) AS CLV,
FROM `beispieldatensatz-341222.rohdaten.Kaeufe`
GROUP BY Kundennummer
),

Einstieg_2 AS (SELECT 
    Kd_nr,
    ANY_VALUE(CLV) AS CLV,
    ANY_VALUE(IF(Kunde_seit = Datum,Warenkorb,null)) AS Warenkorb_Einstieg,
FROM Einstieg_1 LEFT OUTER JOIN `beispieldatensatz-341222.rohdaten.Kaeufe` ON Kunde_seit = Datum
GROUP BY Kd_nr)

SELECT 
    Warenkorb_Einstieg,
    COUNT(Warenkorb_Einstieg) AS Anzahl_kunden,
    SUM(CLV)/COUNT(Warenkorb_Einstieg) AS CLV_pro_Einstieg,

FROM Einstieg_2
GROUP BY Warenkorb_Einstieg

In Data Studio könnte man es grundsätzlich so darstellen, wie auch bei den “Kunden die, diesen Artikel gekauft haben…”.

Anzahl aktiver Kunden pro Jahr (sowie Neu- und Bestandskunden)

Neben Basis-KPI, wie dem Umsatz und dem Gewinn ist es sinnvoll zusätzlich KPI, wie aktive Kunden, Neu- und Bestands-Kunden zu erfassen.

Aktive Kunden

Aktive Kunden mit BigQuery und Data Studio: Hier brauchen wir tatsächlich nicht erst eine Transformation in BigQuery vornehmen sondern können direkt mit den Rohdaten ( der Käufe) arbeiten:

Neukunden

Neukunden in BigQuery/Data Studio:

#BigQuery Script
Create or Replace table basic_kpi.Neukunden

AS

with Neukunden AS (SELECT 
Kundennummer,
Extract(YEAR FROM Min(Datum)) AS Erstkaufjahr,

FROM `beispieldatensatz-341222.rohdaten.Kaeufe`
GROUP BY Kundennummer)

SELECT 
    Erstkaufjahr,
    Count(Erstkaufjahr) AS Anzahl_Neukunden,
FROM Neukunden
GROUP BY Erstkaufjahr

Bestandskunden

Bestandskunden in BigQuery/Data Studio: In unserem Skript arbeiten wir hier mit Tabellen, die wir vorher schon erstellt haben:

with bestandskd_1 AS (SELECT 
    EXTRACT(YEAR FROM Datum) AS Kauf_Jahr,
    Count(DIstinct Kundennummer) AS Count_aktive_Kunden,
FROM `beispieldatensatz-341222.rohdaten.Kaeufe`
GROUP BY EXTRACT(YEAR FROM Datum))

SELECT 
    Kauf_jahr,
    SAFE_SUBTRACT(Count_aktive_Kunden, Anzahl_Neukunden) AS Count_bestandskunden,
FROM bestandskd_1 join basic_kpi.Neukunden ON Kauf_jahr = Erstkaufjahr
ORDER BY Kauf_Jahr

Pivot Umsatz nach Kundenerstkauf-Jahr

Um herauszubekommen, welche (Kunden-)Jahrgänge wie viel Umsatz in einem jeweiligen Jahr generieren, muss man eine Pivot-Tabelle nach Umsatz und Kundenerstkauf-Jahr aufstellen.

In BigQuery bekommt man die Grundlage für Data Studio mit folgendem Skript:

CREATE OR REPLACE Table basic_kpi.pivot_base

AS

with pivot_1 AS (SELECT 
    Kundennummer AS Kd_nr,
    Min(Datum) AS Erstkauf_Datum,
FROM `beispieldatensatz-341222.rohdaten.Kaeufe`
GROUP BY Kundennummer)

SELECT 
    Kd_nr,
    Extract(YEAR FROM Erstkauf_Datum) AS Erstkauf_jahr,
    Extract(YEAR FROM Datum) AS Kauf_jahr,
    Umsatz,
FROM pivot_1 JOIN `beispieldatensatz-341222.rohdaten.Kaeufe` ON Kd_nr = Kundennummer

Data Studio:

In unserem Beispiel kann man zum Beispiel sagen, dass 2019 14.992,52 Euro Umsatz über die Kunden mit Erstkauf-Datum 2015 kamen.

Churn Rate

Die Churn Rate gibt an, wie viel % der Kunden eines Jahres im Folgejahr keine Kunden mehr sind. Ziel sollte sein, die Churn Rate möglichst gering zu halten.

Das Skript in SQL lautet:

CREATE OR REPLACE Table basic_kpi.churn_rates

AS

with aktive_kunden_anzahl AS (SELECT 
    Extract(Year FROM Datum) AS Kauf_jahr,
    Count(Distinct Kundennummer) AS Aktive_Kunden,
FROM `beispieldatensatz-341222.rohdaten.Kaeufe`
GROUP BY Extract(Year FROM Datum)),

neukunden AS (SELECT 
    Kundennummer,
    Min(Extract(Year FROM Datum)) AS Erstkauf_jahr,
FROM `beispieldatensatz-341222.rohdaten.Kaeufe`
GROUP BY Kundennummer),

neukunden_anzahl AS (SELECT 
    Erstkauf_jahr,
    COUNT(Kundennummer) As neukunden_count,
FROM neukunden
group by Erstkauf_jahr),

churn_rate_1 AS (SELECT 
    SAFE_SUBTRACT(Kauf_jahr, 1) AS Kauf_jahr_minus_1,
    SAFE_SUBTRACT(Aktive_Kunden, neukunden_count) AS Bestandskunden_folgejahr,
FROM neukunden_anzahl JOIN aktive_kunden_anzahl ON Erstkauf_jahr = Kauf_jahr)

SELECT 
    Kauf_jahr,
    SAFE_SUBTRACT(1,Safe_DIvide(Bestandskunden_folgejahr,Aktive_Kunden)) AS Churn_Rate,
FROM churn_rate_1 JOIN aktive_kunden_anzahl ON Kauf_jahr_minus_1 = Kauf_jahr

In Data Studio könnte man das folgendermaßen visualisieren:

Umwandlungsquote Newsletter zu Kunde

Im Sales-Funnel ist der Newsletter oft der erste Step hin zum Kunden. Vor allem bei erklärungsbedürftigeren Produkten und Dienstleistungen mit längeren Customer Journeys bis zum ersten Kauf. Ziel eines effizienten Sales Fundings sollte es sein, eine möglichst geringe Umwandlungsquote zwischen Newsletter-Registrierungen zu Kunden zu erzielen.

Auf der Grundlage unseres Beispieldatensatzes könnte das Skript in BigQuery so aussehen:

CREATE OR REPLACE Table basic_kpi.umw_nl_reg

AS

with NL AS (SELECT 
    Extract(Year From Kunde_erfasst_am) AS Reg_jahr,
    Count(*) AS Anzahl_nl,

FROM `beispieldatensatz-341222.rohdaten.Newsletter`
GROUP BY Reg_jahr),

Kunden_1 AS (SELECT 
    Kundennummer,
    Extract(Year From Min(Datum)) AS Erstkauf,
FROM `beispieldatensatz-341222.rohdaten.Kaeufe`
GROUP BY Kundennummer),

Kunden_2 AS (SELECT 
    Erstkauf,
    Count(Kundennummer) AS Anzahl_Erstkaeufer,
FROM Kunden_1
GROUP BY Erstkauf)

SELECT 
    Reg_jahr,
    Safe_Divide(Anzahl_nl,Anzahl_Erstkaeufer) AS Umwandlgs_quote,
FROM NL JOIN Kunden_2 ON Reg_jahr = Erstkauf
ORDER BY Reg_jahr

Data Studio:

SQL Überblick

Was ist SQL?

  • SQL steht für Structured Query Language (strukturierte Abfragesprache).
  • Mit SQL können Sie auf Datenbanken zugreifen und diese bearbeiten.
  • SQL wurde 1986 ein Standard des American National Standards Institute (ANSI) und 1987 der International Organization for Standardization (ISO).

Was kann SQL?

  • SQL kann Abfragen an eine Datenbank stellen
  • SQL kann Daten aus einer Datenbank abrufen
  • SQL kann Datensätze in eine Datenbank einfügen
  • SQL kann Datensätze in einer Datenbank aktualisieren
  • SQL kann Datensätze aus einer Datenbank löschen
  • SQL kann neue Datenbanken erstellen
  • SQL kann neue Tabellen in einer Datenbank erstellen
  • SQL kann gespeicherte Prozeduren in einer Datenbank erstellen
  • SQL kann Ansichten in einer Datenbank erstellen
  • SQL kann Berechtigungen für Tabellen, Prozeduren und Ansichten festlegen

SQL ist ein Standard mit vielen Versionen

Obwohl SQL ein ANSI/ISO-Standard ist, gibt es verschiedene Versionen der SQL-Sprache.

Um jedoch dem ANSI-Standard zu entsprechen, unterstützen sie alle zumindest die wichtigsten Befehle (wie SELECT, UPDATE, DELETE, INSERT, WHERE) auf ähnliche Weise.

Hinweis: Die meisten SQL-Datenbankprogramme haben neben dem SQL-Standard auch ihre eigenen proprietären Erweiterungen!

Überblick von ausgewählten SQL-Anweisungen, Schlüsselwörter, Funktionen,Klauseln, Operatoren für BigQuery

 SQL select distinct Statement
 SQL where Klausel
 SQL Operatoren and or und not
 SQL order by schluesselwort
die  SQL insert into Anweisung
die  SQL update Anweisung
 SQL delete Anweisung
die  SQL select limit Klausel
die  SQL Funktionen min und max
die  SQL Funktionen count avg und sum
der  SQL like Operator
der  SQL Operator in
der  SQL between Operator
 SQL group by Anweisung
die  SQL having Klausel
der  SQL exists Operator
die  SQL Anweisung case

Die SQL-Anweisung CASE

Die CASE-Anweisung durchläuft die Bedingungen und gibt einen Wert zurück, wenn die erste Bedingung erfüllt ist (wie eine if-then-else-Anweisung). Sobald also eine Bedingung erfüllt ist, wird das Lesen beendet und das Ergebnis zurückgegeben. Wenn keine Bedingung erfüllt ist, wird der Wert aus der ELSE-Klausel zurückgegeben.

Wenn es keinen ELSE-Teil gibt und keine Bedingung erfüllt ist, wird NULL zurückgegeben.

Ausgangstabelle:

Abfrage

Inhalt der Abfrage: Wir wollen eine weitere Spalte „Großstadt“ hinzufügen in der bei Städten, wie München oder Berlin, der Wert „Großstadt“ in die selbe Zeile kommt.

SELECT 
    event_date_user,
    user_city,
        CASE user_city 
            WHEN 'Munich' THEN 'big city'
            WHEN 'Berlin' THEN 'big city'
            ELSE 'Others'
        END
        AS big_city,

FROM `privat-327611.analytics_266663932.user_city`

Der SQL EXISTS-Operator

Der EXISTS-Operator wird verwendet, um zu prüfen, ob ein Datensatz in einer Unterabfrage vorhanden ist.

Der EXISTS-Operator gibt TRUE zurück, wenn die Unterabfrage einen oder mehrere Datensätze zurückgibt.

Beispiel-Abfrage

Ausgangstabellen:

Wir haben eine Tabelle mit den Pageviews und eine mit den Anzahl pro Nutzer pro Tag:

Inhalt der Abfrage: Wir wolllen jetzt nur die Tage Anzeigen lassen bei denen auch mindestens 60 Pageviews erzielt wurden:
SELECT 
    event_date_user,
    user,
FROM `privat-327611.analytics_266663932.user_basic`
WHERE EXISTS (SELECT event_date FROM `privat-327611.analytics_266663932.pageviews_basic` WHERE event_date = event_date_user AND page_views_pageview > 60)

Die SQL HAVING-Klausel

Die HAVING-Klausel wurde zu SQL hinzugefügt, weil das Schlüsselwort WHERE nicht mit Aggregatfunktionen verwendet werden kann.

Beispiel-Abfrage

Ausgangstabelle:

Inhalt der Beispielabfrage: Wir haben untereinander mehrere User-IDs, zu denen jeweils ein Datum zugeordnet ist. Wir wollen mit der Abfrage herausfinden, wie viele Nutzer wir pro Tag hatten. Wir gruppieren, deshalb anhand des Datums und zählen die Anzahl der Nutzer. ZUSÄTZLICH wollen wir aber ausschließlich die Tage angezeigt bekommen, die mindestens 8 Nutzer hatten:

SELECT 
    event_date_user,
    COUNT(user_pseudo_id) AS count_user,
FROM `privat-327611.analytics_266663932.base`
GROUP BY event_date_user
HAVING count_user >= 8

SQL GROUP BY-Anweisung

Die GROUP BY-Anweisung gruppiert Zeilen mit gleichen Werten zu zusammenfassenden Zeilen, z. B. “Anzahl Nutzer pro Browser”.

Die GROUP BY-Anweisung wird häufig mit Aggregatfunktionen (COUNT(), MAX(), MIN(), SUM(), AVG()) verwendet, um die Ergebnismenge nach einer oder mehreren Spalten zu gruppieren.

Beispiel-Abfrage

Ausgangstabelle:

Inhalt der Beispielabfrage: Wir haben untereinander mehrere User-IDs, zu denen jeweils ein Datum zugeordnet ist. Wir wollen mit der Abfrage herausfinden, wie viele Nutzer wir pro Tag hatten. Wir gruppieren, deshalb anhand des Datums und zählen die Anzahl der Nutzer:

SELECT 
    event_date_user,
    COUNT(user_pseudo_id) AS count_user,
FROM `privat-327611.analytics_266663932.base`
GROUP BY event_date_user

Der SQL BETWEEN-Operator

Der Operator BETWEEN wählt Werte innerhalb eines bestimmten Bereichs aus. Die Werte können Zahlen, Text oder Daten sein.

Der Operator BETWEEN ist „inclusive“: Anfangs- und Endwerte sind eingeschlossen.

Beispiel-Abfrage

Ausgangstabelle:

Inhalt der Abfrage: Abfrage der Zeilen zwischen dem 22. und 24.12.
SELECT * 
FROM `privat-327611.analytics_266663932.user_browser`
WHERE event_date_user between '2021-12-22' AND '2021-12-24'