Warenkorbanalyse / market basket analysis in PHP / MySQL

Schritt 1

Ausgabe des kompletten Datensatzes aus der Datenbank, mit den Bestellnummern und den zugehörigen Artikeln. Diese Tabelle könnte z.b. aus dem Warenwirtschaftssystem bzw dem Bestellwesen exportiert und in dieser komprimierten Form in einem MySQL-Datenbank eingelesen werden .

Indem Beispiel heißt die Tabelle einfach warenkorbanalyse und die Sortierung soll nach der Bestellnummer aufsteigend erfolgen.

In der Ausgabe wird dann pro Zeile einfach die Bestellnummer und eine Artikelnummer gegenübergestellt, so dass die Bestellnummer zwar öfters auftauchen kann, aber in Verbindung mit dem Artikel immer nur einmal.

//Step1: Show complete Result Set from Database
echo "<h3>Step1: Show complete Result Set from Database</h3>";
$result = mysql_query("SELECT * FROM warenkorbanalyse ORDER BY orderid ASC");
echo "<table border= '1'>";
echo "<tr><td>ID</td><td>Order ID</td><td>Article ID</td></tr>";
while ($row = mysql_fetch_assoc($result)) {
echo "<tr><td>".$row["id"]."</td><td>".$row["orderid"]."</td><td>".$row["articleid"]."</td></tr>";
};
echo "</table>";
Warenkorbanalyse / market basket analysis in PHP / MySQL

Schritt 2

Aus dieser komplette Liste lassen wir uns jetzt nur die Bestellnummern ausgeben, ohne doppelte Einträge, so dass jede Bestellnummer nur einmal in der Liste auftaucht.

Dies lässt sich mit dem Befehl distinct gut lösen .

//Step 2: Show only the DISTINCT OrderIDs
echo "<h3>Step 2: Show only the DISTINCT OrderIDs</h3>";
$resultOrderIDs = mysql_query("SELECT DISTINCT orderid FROM warenkorbanalyse ORDER BY orderid ASC");
echo "<table border= '1'>";
echo "<tr><td>Order ID</td></tr>";
while ($row = mysql_fetch_assoc($resultOrderIDs)) {
echo "<tr><td>".$row["orderid"]."</td></tr>";
};
echo "</table>";

Schritt 3

Auf Basis der Liste der eindeutigen Bestellnummern, wird jetzt in einer ersten Schleife jeweils eine Bestellnummer gegriffen, und mit einer zweiten Schleife mit entsprechender Datenbankabfrage werden alle Artikel zu dieser Bestellnummer eingelesen.

//Step 3: Show all ArticleIDs in one OrderID
echo "<h3>Step 3: Show all ArticleIDs in one OrderID</h3>";
$resultOrderIDs = mysql_query("SELECT DISTINCT orderid FROM warenkorbanalyse ORDER BY orderid ASC");
echo "<table border= '1'>";
echo "<tr><td>Order ID</td><td>Article IDs</td></tr>";
while ($row = mysql_fetch_assoc($resultOrderIDs)) {
$orderid = $row["orderid"];
echo "<tr><td>".$orderid."</td><td>";
$resultArticlesInOrder = mysql_query("SELECT * FROM warenkorbanalyse WHERE orderid = '$orderid' ORDER BY articleid ASC");
while ($row2 = mysql_fetch_assoc($resultArticlesInOrder)) {
echo $row2["articleid"]." ";
};
echo "</td></tr>";
};
echo "</table>";

Schritt 4

Jetzt wird die eigentlich relevante neue Tabelle erstellt, bzw jetzt werden die Daten ausgelesen und entsprechend für die Ausgabe vorbereitet.

Zuerst wird wieder mit distinct die Liste der eindeutigen Bestellnummern geladen, die dann in der ersten Schleife durchlaufen werden .

Danach für aus der Datenbank gelesen und schrittweise durchlaufen, wobei in jedem Schritt einen Artikel als Artikel 1 separat gespeichert wird .

Denn in der dritten Schleife werden wieder alle Artikel dieser Bestellnummer durchlaufen und der Artikel 2 aus der dritten Schleife wird dem Artikel 1 aus der zweiten Schleife gegenübergestellt , da sie beide in derselben Bestellnummer bestellt wurden .

Damit keine doppelten Pärchen kommen, wird vorher geprüft ob Artikel 1 und Artikel 2 identisch sind, ein neues Pärchen wird nur angelegt wenn es unterschiedliche Artikel aus derselben Bestellung sind .

In der dritten Schleife wird ein neues Ray gefüllt Komma bei dem der erste Schlüssel die Artikelnummer 1 ist, der zweite Schlüssel die Artikelnummer 2, und der Wert entsprechend die Anzahl der gemeinsamen Käufe.

//Step 4: Create new Array with ArticleID combinations per OrderID
echo "<h3>Step 4: Create new Array with ArticleID combinations per OrderID</h3>";
$resultOrderIDs = mysql_query("SELECT DISTINCT orderid FROM warenkorbanalyse ORDER BY orderid ASC");
$ArticleCorrelations = array();
$PreviousOrderid = "";
while ($row = mysql_fetch_assoc($resultOrderIDs)) { // Pro Schritt 1 OrderID
$orderid = $row["orderid"];

$resultArticlesInOrder = mysql_query("SELECT * FROM warenkorbanalyse WHERE orderid = '$orderid' ORDER BY articleid ASC");
while ($row2 = mysql_fetch_assoc($resultArticlesInOrder)) { // Pro Schritt 1 Article ID
$Article1 = $row2["articleid"];

while ($row3 = mysql_fetch_assoc($resultArticlesInOrder)) { // Pro Schritt eine weitere ArticleID aus der Order
$Article2 = $row3["articleid"];
if ($Article1 != $Article2) {$ArticleCorrelations[$Article1][$Article2] = $ArticleCorrelations[$Article1][$Article2]+1;};

};
};
};

echo "<pre>";
print_r($ArticleCorrelations);
echo "</pre>";

Schritt 5

Jetzt können wir das eben erstellte neue Array als Tabelle ausgeben , bei der die Artikelnummer 1, die Artikelnummer 2 und die Anzahl der gemeinsamen Käufe aufgelistet werden.

Je größer die Datenbasis ist, also je mehr einzelne Bestellungen vorliegen, wird es immer größere Summen an gemeinsamen Käufen geben.

//Step 5: Show 2 dimensional Array
echo "<h3>Step 5: Show the Array from Step 4 more beautiful</h3>";
echo "<table border= '1'>";
echo "<tr><td>Article</td><td>with Article</td><td>bought together</td></tr>";
foreach($ArticleCorrelations as $key => $value){
foreach($value as $key2 => $value2){
echo "<tr><td>".$key."</td><td>".$key2."</td><td>".$value2." x</td></tr>";
};
};
echo "</table>";

Schritt 6

Jetzt wird das eben erstellte Array in eine Datenbank geschrieben, die dann von dem entsprechenden Onlineshop genutzt werden kann Punkt

Die Datenbank ist entsprechend einfach aufgebaut und besteht nur aus Artikel 1, Artikel 2, und der entsprechend der Anzahl der gemeinsamen Käufe.

//Step 6: Insert all Combinations in a second Database
echo "<h3>Step 6: Insert the Table (from Step 5) in a second Database</h3>";
mysql_query("TRUNCATE TABLE `warenkorbanalyse2` ");
foreach($ArticleCorrelations as $key => $value){
foreach($value as $key2 => $value2){
mysql_query("INSERT INTO warenkorbanalyse2 (article1, article2, combinations) VALUES ('$key', '$key2', '$value2')");
};
};
Warenkorbanalyse / market basket analysis in PHP / MySQL

Schritt 7

Im letzten Schritt muss von dem Onlineshop nur noch in der neu angelegten Datenbank nach der gerade aufgerufenen Artikelnummer gesucht werden, und es werden alle mit dieser Artikelnummer verbundenen Artikel als Empfehlung zurückgegeben.

Damit die Zahl der empfohlenen Artikel nicht zu groß wird, bietet es sich an ein Limit einzubauen, z.b. auf maximal fünf oder zehn Empfehlungen.

Auch wird bei der Abfrage nach der Anzahl der gemeinsamen Bestellung sortiert, absteigend, sodass die Artikel die besonders häufig gemeinsam gekauft wurden zuerst empfohlen werden, und die Artikel die nur gelegentlich gemeinsam gekauft wurden erst später bzw nachrangig aufgeführt werden.

//Step 7: Recommend 1 Article, that had the most combinations
echo "<h3>Step 7: Show recommended Articles ordered by Relevance, selected from second Database (limited to 10)</h3>";
$ArticleBought = "2";
echo "<table border= '1'>";
echo "<tr><td>If you buy Article</td><td>recommended Articles ordered by Relevance:</td></tr>";
$resultRecommendations = mysql_query("SELECT * FROM warenkorbanalyse2 WHERE article1 = '$ArticleBought' OR article2 = '$ArticleBought' ORDER BY combinations DESC LIMIT 10 ");
while ($row = mysql_fetch_assoc($resultRecommendations)) {
if ($row["article1"] == $ArticleBought) {echo "<tr><td>".$row["article1"]."</td><td>".$row["article2"]."</td></tr>";};
if ($row["article2"] == $ArticleBought) {echo "<tr><td>".$row["article2"]."</td><td>".$row["article1"]."</td></tr>";};
};
echo "</table>";