Produktkauftracking mit BigQuery: Welche Produkte werden häufig zusammen gekauft?

Das Erkennen und Heben von Up- und Cross-Selling Potential bei Produkten sind für Online-Shops von großer Bedeutung. Die Auswertung von gekauften Warenkörben hilft dabei enorm. Michaela Linhart hat hierfür ein sehr hilfreiches Google Spreadsheet Plugin geschrieben, das auf Basis von Google Analytics Enhanced E-Commerce Korrelationen zwischen Produkten in Warenkörben errechnet. Das Plugin und die ausführliche Beschreibung findest sich auf Ihrem Blog: https://www.analyticskiste.blog/analytics/top-carts-and-related-products-plugin/. Dieses Plugin hat mich zu einem neuen Projekt inspiriert. Wir umgehen Google Analytics und senden die Daten direkt in Big Query um bei der Datenerfassung und Auswertung freier mit den Daten arbeiten zu können.

Gekaufte Produkte in einem Big Query Table speichern

Für die erste Stufe werden die OrderID, das Produkt, der Zeitpunkt des Kaufs und der Preis des Produktes erfasst:

Diese Tabelle wird in Big Query angelegt. Die Daten werden direkt auf der Bestellbestätigungsseite erfasst und per GTM Custom HTML als GET Parameter an eine Cloud Function gesendet, die die Daten nach Big Query schreibt. Auf der Bestellbestätigungsseite wird die Cloud Funtion mit den Parametern ti (=TransaktionsID), pr (ProduktID-Array getrennt mit |), und pp (= ProduktPreis-Array getrennt mit |) übergeben.

Simo Ahava hat in seinem Beitrag „How To Build A Google Tag Manager Monitor“ eine Python Cloud Function veröffentlicht, die leicht modifiziert werden kann, um diesen Fall abzubilden.

Dieses Funktion sollte für den Produktiveinsatz noch angepasste Überprüfungen der Parameter enthalten.

Jedes Produkt wird dabei in eine eigene Zeile mit der passenden OrderID geschrieben.

Datenauswertung mit SQL

Für die Fragestellung: „In wie vielen gekauften Warenkörben war Produkt Y vorhanden, wenn Produkt X gekauft wurde“. Aus der Fragestellung wird klar, dass die Beziehung X>Y ungleich der Beziehung Y>X ist, denn Warenkörbe mit Y können in unterschiedlicher Anzahl als Warenkörbe mit X auftreten.

Als Ergebnis der Datenauswertung möchten wir folgendes Schema erhalten:

• Produkt X: ProduktID des Produktes, das im Warenkorb war
• Produkt Y: ProduktID des Produktes, das zusammen mit Produkt X im Warenkorb war
• NumberOfCombinations: Wie häufig kommt die Kombination von Produkt X und Produkt Y in der Datenbank vor
• Product_X_Carts: Wieviele Warenkörbe mit Product X sind im Datenbestand
• Percentage_in_Cart: Wie hoch ist der prozentualer Anteil von Produkt Y in Warenkörben, in denen Produkt X vorkommt
• Price_X / Price_Y: Preis der Produkte X bzw. Y

Das SQL Statement sieht dann folgendermaßen aus:

SELECT c.PR1 Product_X, c.PR2 Product_Y, COUNT(c.PR1) AS NumberOfCombination, PRCart Product_X_Carts, ROUND(COUNT(c.PR1)/PRCart*100,2) AS Percentage_in_Cart, PP1 Price_X, PP2 Price_Y FROM (
SELECT DISTINCT a.OrderID, a.PR1, b.PR2, d.PRCart, a.PP1, b.PP2 FROM
(SELECT OrderID, Product PR1, Price PP1 FROM `Tracking.OrdersProducts` GROUP BY OrderID, Product, Price) a
JOIN 
(SELECT OrderID, Product PR2, Price PP2 FROM `Tracking.OrdersProducts`) b
ON a.OrderID=b.OrderID AND a.PR1 != b.PR2 
JOIN 
(SELECT Product PR3, COUNT(Product) PRCart FROM `Tracking.OrdersProducts` GROUP BY Product) d
ON d.PR3 = a.PR1
GROUP BY a.PR1, b.PR2, OrderID, PRCart, a.PP1, b.PP2) c GROUP BY c.PR1, c.PR2, PRCart, PP1, PP2  ORDER BY c.PR1

Im ersten Schritt werden die Produktpaare X und Y gebildet, indem der Table mit sich selbst anhand der OrderID verbunden wird. Ausgeschlossen werden dabei die Kombinationen bei denen Product X gleich dem Produkt Y ist:

(SELECT OrderID, Product PR1, Price PP1 FROM `spry-sensor-192011.Tracking.OrdersProducts` GROUP BY OrderID, Product, Price) a
JOIN 
(SELECT OrderID, Product PR2, Price PP2 FROM `Tracking.OrdersProducts`) b
ON a.OrderID=b.OrderID AND a.PR1 != b.PR2

Dann werden die Warenkörbe gezählt, in denen Produkt X vorkommt und mit den Daten verbunden.

JOIN 
(SELECT Product PR3, COUNT(Product) PRCart FROM `Tracking.OrdersProducts` GROUP BY Product) d
ON d.PR3 = a.PR1

Im nächsten Schritt werden aus die verbundenen Daten die Kombination OrderID, Produkt X, Produkt Y, Anzahl der Warenkörbe für Produkt X, Preis für Produkt X und Preis für Produkt Y dedupliziert.

SELECT DISTINCT a.OrderID, a.PR1, b.PR2, d.PRCart, a.PP1, b.PP2 FROM

Zum Abschluss werden dann noch zusätzlich die Anzahl der Kombinationen und der prozentuale Anteil von Produkt Y in Produkt X Warenkörben berechnet.

SELECT c.PR1 Product_X, c.PR2 Product_Y, COUNT(c.PR1) AS NumberOfCombination, PRCart Product_X_Carts, ROUND(COUNT(c.PR1)/PRCart*100,2) AS Percentage_in_Cart, PP1 Price_X, PP2 Price_Y FROM 

In die Berechnungen können weitere Faktoren (Preise, Zeitreihen, Produktvarianten, etc.) einbezogen werden. Verfeinern wir die Auswertung also im nächsten Teil.