Skip to content

Digital Analytics Blog

von Marcus Stade

Menu
Menu
produkttracking_bigquery_table

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

Posted on 28. Juli 201928. Juli 2019 by mstade

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:

Big Query Schema des Produkttracking Tables
Big Query Schema des Produkttracking Tables

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.

Produkttracking_cloudfunction_get
Produkttracking mit GET Parametern

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.

ProduktID in cloud function holen
GET Paremter in der Cloud Function auslesen

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

CloudFunktion für BigQuery Streaming
Modifizierter Code der Cloud Function zum Schreiben der Produktdaten nach Big Query

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

produkttracking_bigquery_table
Daten in Big Query für eine OrderID

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:

produkttracking bigquery auswertung
Ergebnis der Auswertung in Big Query
  • 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.

Schreibe einen Kommentar Antworten abbrechen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Keep in Touch

Supporter: InPignus GmbH

ImPignus

mohrstade

mohrstade.de

Analytics Pioneers

Schlagwörter

App+Web App-Tracking Basic Big Query Customer Data Platform DataQuality DataStudio Events Google Analytics für Firebase ITP Machine Learning Property PWA Quick-Tipp User-Journey Wordpress Überblick

Podcast Empfehlung:

beyond pageview

Neueste Beiträge

  • Tag Management Platform: Commanders Act Platform X – DataCollection (Teil 2)
  • Tag Management Platform: Übersicht (Teil 1)
  • IP und User Agent Identifier: Nachteile
  • GA4 Recipes: Machine Learning Features für Predictive Audiences in anderen Tools nutzen, am Beispiel von Tealium AudienceStream
  • Quick Tipp: GA4 Configuration Tag vs. Universal Analytics Setting Variable – Sequence matters

Kategorien

  • Allgemein
  • Cloud
  • Dashboard
  • Firebase Analytics
  • Google Analytics
  • Google Analytics 4 (App+Web)
  • Google Optimize
  • Google Tag Manager
  • Machine Learning
  • Matomo
  • Tag Management

Links

  • Datenschutzerklärung
  • Hear Me Speak
  • Impressum
  • Meet & Eat
  • Über den Blog
©2023 Digital Analytics Blog | Built using WordPress and Responsive Blogily theme by Superb