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:

Durchschnittlicher Bestellwert pro Warenkorb
Customer Lifetime Value
Wert Kunde nach Tagen
“Kunden die, diesen Artikel gekauft haben…”
Einstiegsprodukte
Anzahl aktiver Kunden pro Jahr (sowie Neu- und Bestandskunden)
Pivot Umsatz nach Kundenerstkauf-Jahr
Churn Rate
Umwandlungsquote Newsletter zu Kunde

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:

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:

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