Hands-on: Predictive Analytics mit Excel

Welcher Umsatz wird nächsten Monat erzielt? Welche potenziellen Kunden lohnt es, verstärkt zu kontaktieren? Zahlt der Kunde die Rechnung oder kann er nur per Vorkasse beliefert werden? Diese und ähnliche Fragen stellen sich Händler permanent. Eine Antwort abseits vom Bauchgefühl liefert eine statistisch valide Prognose. Diese wird im Zeitverlauf immer zuverlässiger, wenn die Abweichungen anschließend analysiert werden und darauf fußend eine permanente Überprüfung bzw. Modifizierung des zugrunde liegenden Prognosemodells stattfindet. Den statistischen Modellen ist gemein, dass basierend auf historischen Daten nach Einflussfaktoren bzw. Ähnlichkeiten gesucht wird, welche die Zukunft möglichst exakt vorhersagen. Google kann bspw. eine regionale Grippewelle oder Denguefieber aufgrund bereits ermittelter Abhängigkeiten zwischen Suchanfragen und Ereignissen gut prognostizieren (siehe www.google.org/flutrends/).

Korrelationen – Wechselwirkungen als Prognosehelfer

Die Suche nach Abhängigkeiten von Ereignissen steht in einem engen Kontext mit dem Zusammenhang von Merkmalen. Ein Zusammenhang wird mit statistischen Maßen wie bspw. dem Korrelationskoeffizienten bewertet. Dieser hat einen Wert von -1 bis 1 und besagt, ob sich zwei Merkmale positiv (1) bzw. negativ (-1) zueinander verhalten oder überhaupt in keinem Zusammenhang stehen (0). Google correlate (www.google.com/trends/correlate) zeigt bspw. grafisch an, wie stark Suchphrasen in einem Zeitraum miteinander in Beziehung stehen (siehe Abb. 1). So steht der zeitliche Verlauf der Suche nach einem Kinderwagen in einem starken Zusammenhang mit den Suchphrasen Reihenhaus (r = 0,8208) oder 30. Geburtstag (r = 0,8229). Eigene Werte können entweder über die Zeichnen-Funktion („Search by Drawing“) oder direkt als Werte („Enter your own data“) eingegeben werden. Aus diesen Eingaben berechnet Google correlate entsprechende Suchphrasen, welche einen vergleichbaren Suchverlauf haben. Dies kann ggf. für Kooperationen oder Komplementärprodukte sehr sinnvoll sein. Über die Möglichkeit, die Suchphrasen zeitlich (wochen- bzw. monatsweise) zu verschieben („Shift series“) können vor- bzw. nachgelagerte Zusammenhänge offenbart werden.

Exploration möglicher Einflussfaktoren

Korrelationen helfen, Trends zu erkennen und Prognosen für die Zukunft zu beschreiben. Gern wird von Webseitenbetreibern der Einfluss des Wetters auf die wechselnden Besucher- und Umsatzzahlen aufgeführt. Ob dies für die eigene Webseite zutrifft, kann durch die Kombination von Wetterdaten und Besucherkennzahlen der jeweiligen Orte verifiziert werden. Sehr detaillierte Wetterinformationen von über 40.000 Stationen können kostenlos über die API von openweathermap.org abgefragt werden. Alternativ stellt Yahoo ebenfalls per API Wetterinformationen zur Verfügung (http://developer.yahoo.com/weather). Mithilfe des Add-ins SeoTools (ausführlicher Bericht in der Website Boosting Ausgabe 20) können über die Funktion

=XPathOnUrl

aktuelle Wetterinformation oder detaillierte Wettervorhersagen für die nächsten Tage abgefragt werden (Abb. 2). Historische Daten je Station auf Stundenbasis sind ebenfalls über die API abrufbar.

Mittels der Excel-Funktion =KORREL kann festgestellt werden, ob Werte miteinander korrelieren, d. h. in einem Zusammenhang stehen. Im fiktiven Beispiel in Abb. 3 wurden die Luftfeuchtigkeit in einer Stadt sowie die Webseitenbesucher aus dieser Stadt gemessen. Je stärker die Luftfeuchtigkeit war (100 % = Regen), desto mehr Besucher verzeichnete die Webseite. Die Korrelation beträgt 0,95 und repräsentiert diesen starken Zusammenhang. Über die Summe der einzelnen Regionen kann damit die erwartete Gesamtbesucherzahl der Webseite prognostiziert werden.

Prognosen mit linearen Regressionen

Predictive Analytics bedeutet, dass die Zukunft basierend auf Modellen bzw. eines Modells vorhergesagt wird. Diese Modelle werden mittels Analyse historischer und aktueller Daten aufgestellt und kontinuierlich weiterentwickelt. Das heißt, das Modell korrigiert bzw. optimiert sich im Idealfall durch die kontinuierliche Berücksichtigung weiterer Daten. Eine einfache Möglichkeit, die Zukunft zu prognostizieren, sind Regressionen. Regressionen versuchen, abhängige Variable (bspw. Besucher) durch unabhängige Variablen (bspw. Luftfeuchtigkeit) abzubilden. Dies spiegelt sich in einer Gleichung der Form y = mx + c wider. Über die Excel-Funktionen

=ACHSENABSCHNITT(Y-Werte;X-Werte) sowie
=STEIGUNG(Y-Werte;X-Werte)

kann c bzw. m berechnet werden. Im Beispiel in Abb. 3 können Besucher über die Funktion y = 42,07x + 545,64 prognostiziert werden. Das heißt, bei einer vorhergesagten Luftfeuchtigkeit von 83 % (= x) werden 4.038 Besucher (= y) erwartet. Alternativ können mittels der Funktion

=TREND(historische Y-Werte; historische X-Werte; neue X-Werte)

die Besucher direkt prognostiziert werden. Grafisch ist die Ermittlung der Regressionsgeraden durch die Funktion „Trendlinie“ im Untermenü Layout der Diagrammtools lösbar (Ergebnis siehe Abb. 3).

Berücksichtigung mehrere Einflüsse – multiple Regressionen

Meist ist nicht nur ein Faktor vorhanden, der ein Ereignis beeinflusst, sondern es ist die Kombination unterschiedlicher Einflüsse. TREND kann ebenfalls für die Prognose unter Bezugnahme mehrerer Faktoren herangezogen werden (multiple Regression). Soll nicht nur die Luftfeuchtigkeit, sondern bspw. auch die Temperatur als unabhängiger Faktor in die Analyse einfließen, so liefert die Funktion TREND die entsprechenden Prognosewerte (siehe Abb. 4). Als Alternative können über die Matrix-Funktion RGP sowohl die Koeffizienten, die Konstante als auch deren Güte gleichzeitig berechnet werden. Bei Matrix-Funktionen muss die Formeleingabe in Excel immer mit Umschalt- + STRG + Eingabetaste abgeschlossen werden. Sie sind durch die eingabebedingt erzeugte Klammersetzung, bspw. {=RGP(Attribute)}, erkennbar. Matrix-Funktionen sind dadurch gekennzeichnet, dass sie mehrere Rechenoperationen simultan durchführen und deren Lösung, ein sogenanntes Array, mehrere Ergebnisse enthält. Sofern vor der Formeleingabe mehrere Zellen markiert wurden, liefert dieser Funktionstyp die entsprechenden Kennzahlen bzw. Ergebnisse unmittelbar zurück (siehe Abb. 4).

Logistische Regression – Möglichkeit der Ja/Nein-Prognose

Fragestellungen wie die Wahrscheinlichkeit eines Kaufes oder der Bezahlung der Rechnung, d. h. Ja/Nein-Antworten, sind durch lineare Regression unzureichend abbildbar. Lineare Regressionen hätten nicht nur mögliche Wahrscheinlichkeiten von über 100 % bzw. unter 0 %, sondern unterstellen einen kontinuierlichen gleichmäßigen Zusammenhang. Damit hätte bspw. eine Änderung des Jahreseinkommens von 50.000 auf 55.000 € eine identische Auswirkung auf die Kaufwahrscheinlichkeit eines Gutes wie die Änderung des Einkommens von 95.000 auf 100.000 €. Es ist offensichtlich, dass dies voraussichtlich nicht der Realität entspricht. Diesen Nachteil können statistische Verfahren wie bspw. eine logistische Regression lösen, da diese sich einer dichotomen Variablen (0 bzw. 1 für Nichtkauf bzw. Kauf) langsam annähert.

Bei einer logistischen Regression wird ein Modell in mehreren Iterationen optimiert. Vereinfacht gesagt werden die einzelnen Koeffizienten bzw. die Konstante geschätzt und anschließend die Passung des Modells mit den zugrunde liegenden historischen Daten ermittelt. Die Güte des Modells wird durch die Summe des Log-Likelihood (Logarithmus der Prognosewahrscheinlichkeit für Kauf/Nichtkauf) gemessen. Dies geschieht so lange, bis das Gütekriterium nicht mehr verbessert werden kann. Diese Simulation bzw. erweiterte Zielwertsuche übernimmt der Solver. Der Solver kann unter Beachtung von Bedingungen mittels diverser Lösungsalgorithmen einen Zielwert optimieren, in dem die dem Modell zugrundliegenden Variablen permanent modifiziert werden („Was-wäre-wenn“-Analysen).

Die Besonderheit bei logistischen Regressionen ist die Nutzung von Odds (Chancen). Aus diesen Odds lassen sich die Wahrscheinlichkeiten ermitteln (Chance 1:1 entspricht bspw. 50 %). Der natürliche Logarithmus aus diesen Odds ist der Logit. In der logistischen Regression wird der Logit durch die unabhängigen Variablen sowie die Konstante bestimmt, d. h., im folgenden Beispiel bestimmen Einkommen, Alter und die Region die Kaufwahrscheinlichkeit (siehe Abb. 7).

Die Funktion

=EXP(Zahl)

ermittelt aus dem Logit die Odds. Rechnerisch verbirgt sich hinter der Funktion die Potenz der eulerschen Zahl und damit die Umkehrung des natürlichen Logarithmus. Mittels der Formel

=Odds/(1+Odds)

ergibt sich die Kaufwahrscheinlichkeit basierend auf diesem Modell. Das Modell ist präzise, sofern es einen Kauf oder einen Nichtkauf möglichst präzise vorhersagt, d. h., die Chance eines Nichtkaufs ist damit 1 – Kaufwahrscheinlichkeit (in Abb. 7: =WENN(A6=1;G6;1-G6)). Das Modell ist dann optimal, wenn diese Kauf-/Nichtkaufwahrscheinlichkeit  1 ist. Die Güte des gesamten Modells ergibt sich durch das Produkt der einzelnen Wahrscheinlichkeiten. Da dieses Produkt bei einer großen Anzahl an historischen Daten sehr klein wird (bspw. 0,2*0,2*0,2 = 0,008), empfiehlt es sich, diese Wahrscheinlichkeit mit der Funktion

= LN(Zahl)

in den natürlichen Logarithmus umzuwandeln. Dies hat den Vorteil, dass diese logarithmierte Wahrscheinlichkeit summiert werden kann.

Solver – umfassende „Was-wäre-wenn“-Analysen

Im Modell wurde die Konstante zum Start auf den Faktor 1 und die einzelnen Koeffizienten auf 0 gesetzt. Es müssen nun die Werte gefunden werden, die zum Modell mit der höchsten Güte führen. Dazu könnten manuell Werte eingesetzt und diese so lange modifiziert werden, bis die Summe des Log-Likelihood das Ideal von 0 hätte. Diese Aufgabe übernimmt der Solver, welcher im Menüpunkt Daten aufgerufen werden kann (siehe Abb. 8). Das Ziel der Simulation durch den Solver ist das Erreichen des Maximums der Zelle H2 (Summe Log-Likelihood; in Abb. 7: =SUMME(I:I)). Dabei sollen und können die Variablen A3 bis D3 modifiziert werden. Weitere Nebenbedingungen sind nicht zu beachten. Damit die logistische Regression gelöst werden kann, muss das vorbelegte Feld „Nicht eingeschränkte Variablen …“ deaktiviert sowie in der Lösungsmethode GRG-Nichtlinear die Ableitungen „Zentral“ vorgenommen werden (Einstellung unter „Optionen“ neben der Wahl der Lösungsmethode). Ein Klick auf „Lösen“ leitet die Simulation/Berechnung ein.

Tipp: Der Solver ist ein hervorragendes Instrument, um Datensimulationen vorzunehmen. Im Internet gibt es hierzu umfassende Tutorials und Videos.

Basierend auf den historischen Daten, die das aktuelle Modell determinieren, ergibt sich der Logit aus 0,26*Einkommen+0,27*Alter-2,32*Region (siehe Abb. 9). Über die beschriebene Umwandlung kann damit für unbekannte Daten eine Wahrscheinlichkeit prognostiziert werden. Hat ein neuer Kunde ein Einkommen von 45.500 €, ist 63 Jahre alt und wohnt in der Region 1, hat er eine Kaufwahrscheinlichkeit von 83,9 %. Diese Daten könnten herangezogen werden, wenn ein Unternehmen vor der Entscheidung steht, ob der Kunde bei knappen Ressourcen weiter bearbeitet werden soll oder nicht. Sind in der Datenbank 5.000 Kunden und es sollen nur die 1.000 Kunden per Post angeschrieben werden, welche wahrscheinlich konvertieren, so kann ein solches Modell die statistisch relevantesten Personen offenbaren.

Wie immer gilt: Je größer die zugrunde liegende Datenbasis, desto besser ist das Modell und desto genauer sind die Prognosen. Weiterhin ist empfehlenswert, dass dem Modell kontinuierlich die tatsächlichen Daten zur Verfügung gestellt werden und es damit weiterentwickelt bzw. angepasst wird.

VBA – freie Erweiterung von Excel durch Programmierung

Mittels VBA (Visual Basic for Applications) können sehr viele Schritte in Excel automatisiert werden. Komplett eigene Funktionen, Prognoseverfahren mit den entsprechenden notwendigen Abläufen sind damit denkbar. Über die Tastenkombination Alt + F11 ist die Oberfläche von VBA erreichbar. Das Beispiel in Abb. 12 zeigt den Einsatz von VBA, um automatisiert Produktinformationen von Amazon zu laden und damit Prognosen über Abverkauf von Nischenprodukten geben zu können. Sofern bspw. die Überlegung besteht, das mit über 800 Kundenrezensionen beliebte Produkt des Universal-Schweizer-Messers in das Sortiment aufzunehmen, kann dessen Verkaufsrate über den Amazon-Beststeller-Rang prognostiziert werden (siehe Abb. 10).

Damit dies nicht manuell stattfinden muss, kann eine einfache VBA-Prozedur eingesetzt werden. Über die Excel-Funktion

„Externe Daten abrufen – Aus dem Web“

im Menü Daten kann ein Bereich bzw. eine ganze Seite in Excel importiert werden. Hierzu müssen die gewünschten Bereiche mittels der gelben Pfeiltasten aktiviert werden (Abb. 10). Sofern dieser Import bereits durchgeführt wurde (bspw. mit Start des Imports in Zelle A1), können die Werte über die Schaltfläche „aktualisieren“ im Menü Daten oder über die rechte Maustaste immer wieder aktualisiert werden. Hierzu wird eine Verbindung zur Zielseite geöffnet und die entsprechenden Bereiche werden neu importiert (Abb. 11).

Eine einmalig geschriebene VBA-Prozedur kann die Datenaktualisierung per Code aufrufen, die Position des aktuellen Verkaufsranges ermitteln, in eine Tabelle schreiben und bei einer positiven Veränderung mindestens einen Kauf prognostizieren. Dieser Code könnte über eine weitere Prozedur zeitgesteuert ablaufen und damit die Daten bspw. jede Stunde automatisch aktualisieren. Durch ein sog. Error-Handling kann gesteuert werden, was die Prozedur tun soll, wenn ein Fehler auftritt. Dies ist bei einer zeitgesteuerten Durchführung auf jeden Fall empfehlenswert.

Diese Prozedur liefert den aktuellen Rang und lässt bei einer Erhöhung auf einen Kauf zurückschließen.