Einfache Warenkorbanalyse in Google BigQuery

Die Warenkorbanalyse erlaubt es mithilfe von Verkaufszahlen Einblicke in die Beziehungen zwischen zwei oder mehr Produkten zu erhalten. So können vielfältige Informationen generiert werden, welche wiederum zur Umsetzung relevanter Fragen genutzt werden können: Preisgestaltung, Rabatt-Aktionen, Cross- & Up-Selling, Kundensegmentierung und vielem mehr. Diese Informationen können über verschiedene Wege generiert werden. Von beschreibenden bis hin zu statistisch erklärenden Erkenntnissen bietet das Thema der Warenkorbanalyse eine Vielzahl solcher Ergebnisse.

Dieser Blogartikel stellt dabei den ersten Schritt zum Erkenntnisgewinn dar, da mit lediglich einem übersichtlichen SQL Skript erste beschreibende Erkenntnisse gewonnen werden. Im Folgenden erläutern wir Schritt für Schritt das Vorgehen mithilfe von Google BigQuery, um die wichtigsten Zusammenhänge der Produktverkäufe zu erlangen und anschließend auch zu interpretieren.

Warenkorbanalyse in Google BigQuery

Wir können mithilfe von BigQuery zeigen, wie häufig ein Produkt zusammen mit einem anderen Produkt gekauft wurde. Hierbei handelt es sich um eine 1:1 Warenkorbanalyse, d.h. es werden immer nur zwei Produkte gleichzeitig betrachtet.

Beispiele:

• Bei einer Bestellung der Produkte A und B werden die Kombinationen (A,B) und (B,A) und
• bei einer Bestellung der Produkte (a,b,c) werden die Kombinationen (a,b), (b,a), (b,c), (c,b), (c,a), (a,c) betrachtet.

Anforderungen für eine Warenkorbanalyse in BigQuery

Um die Warenkorbanalyse in BigQuery durchführen zu können, werden folgenden Daten benötigt:

• Auftrags- / Bestellnummer (order_id)
• Artikel- / Produknummer (article_id)
• optional: Artikel- / Produktname (article_name)
• optional: Bestelldatum (order_time)

Hinweis: Für die Erstellung der Warenkorbanalyse ist im ersten Schritt der Artikelname nicht relevant, da die Artikelnummern miteinander kombiniert werden. Die Artikelnamen könnten auch erst in einem späteren Schritt mit einem JOIN hinzugefügt werden. Zusätzlich ist beim Bestelldatum das reine Datum ausreichend und die Uhrzeit nur optional.

Die Datentabelle in der Ausgangssituation sollte folgenden Aufbau haben:

Erstellen der Datengrundlage

Wenn die Daten in dem oben erläuterten Format vorliegen, müssen zunächst die fehlenden Informationen zur Tabelle hinzugefügt werden. Das ist u.a. die Angabe, ob es sich um eine Bestellung mit = 1 Produkt handelt oder um eine Bestellung mit > 1 Produkt. Hierfür wird eine neue Variable order_type erstellt. Diese zeigt für Einzelbestellungen den Wert "single order" und für Bestellungen mit mehreren Produkten "bundle order" an.

1WITH cte_order_type AS (
2SELECT 
3  order_id, 
4  CASE 
5    WHEN count(order_id) > 1 THEN "bundle order" 
6    WHEN count(order_id) = 1 THEN "single order" 
7  END AS order_type
8FROM `project_name.dataset_name.table_name`
9GROUP BY order_id
10)
11  
12SELECT 
13  items.order_id, 
14  items.order_time,
15  items.article_id,
16  items.article_name, 
17  cte_order_type.order_type
18FROM `project_name.dataset_name.table_name`
19LEFT JOIN cte_order_type 
20  ON items.order_id = cte_order_type.order_id

Die Ausgangstabelle, ergänzt um den order_type:

Da es sich um eine 1:1 Warenkorbanalyse handelt, betrachten wir immer zwei Produkte (article und bundle_article) aus derselben Bestellung (order_id) in unserer Analyse. Eine weitere CTE cte_bundle wird erstellt und benennt die Bestelldaten lediglich um und gruppiert diese. Dadurch können die Bestelldaten anschließend an die Ausgangstabelle gejoined werden. Die Abfrage sieht dann wie folgt aus:

1WITH cte_order_type AS (
2SELECT 
3  order_id, 
4  CASE WHEN count(order_id) > 1 THEN "bundle order" 
5  WHEN count(order_id) = 1 THEN "single order" 
6  END AS order_type
7FROM `project_name.dataset_name.table_name`
8  GROUP BY order_id
9),
10cte_bundle AS (
11SELECT 
12  order_id, 
13  order_time, 
14  article_id AS bundle_article_id, 
15  article_name AS bundle_article_name
16FROM `project_name.dataset_name.table_name`
17GROUP BY 
18  order_id,
19  order_date,
20  bundle_article_id,
21  bundle_article_name
22)
23
24SELECT
25  items.order_id,
26  items.order_time,
27  items.article_id,
28  items.article_name,
29  bundle.bundle_article_id,
30  bundle.bundle_article_name,
31  cte_order_type.order_type
32FROM
33`project_name.dataset_name.table_name` AS items
34LEFT JOIN cte_order_type
35  ON items.order_id = cte_order_type.order_id 
36LEFT JOIN cte_bundle 
37  ON item.order_id = cte_bundle.order_id
38  AND items.product_name != cte_budle.bundle_article_name
39ORDER BY order_time

Die ausgeführte Query gibt folgende Tabelle aus:

Zusammenfassend zeigt die Tabelle nun alle Kombinationen an, die innerhalb einer Bestellung enthalten sind. Zusätzlich erhält der User die Information, ob es sich um eine 1-Artikel-Bestellung handelt (single order) oder ob mehrere Artikel in der Bestellung enthalten waren (bundle order). Dieser Datensatz kann als Ausgangspunkt für relevanten Analysen und Visualisierungen genutzt werden.