Excel ist noch immer das mächtigste Werkzeug bei der Analyse und Aufbereitung von Daten im E-Commerce, da einfach und schnell Daten aus verschiedensten Quellsystemen zusammengeführt werden können. Die Automatisierung eines komplexen Reportings mit Excel ist mithilfe Pivot-Tabellen auch ohne komplizierte VBA-Makros oder teure Data Warehouse Integration einfach zu bewerkstelligen. So lassen sich wiederkehrende Datenpflegeaufwände minimieren, Fehler vermeiden und ein ganzheitlicher Blick auf die Performance von Marketing und Webshop wird möglich.

Automatisiertes Excel-Dashboard** mithilfe von Pivot-Tabellen. Beobachtungs- und Vergleichszeitraum lassen sich einfach konfigurieren. Neue Daten sind schnell und einfach eingebunden.

Ein brauchbares Reporting bedarf Daten aus verschiedensten Quellsystemen: Die Daten der Performance des Onlineshops kommen aus Google Analytics oder einem vergleichbaren Webtracking-Tool. Social Media Aktivitäten werden im Facebook Werbeanzeigenmanager, Facebook selbst oder einem Tool wie Falcon verwaltet, über das man auch Kanäle wie Instagram und Pinterest steuern kann. Alle Tools liefern eine Menge Statistiken, aber selten genau diejenigen und in der Form, in der man sie gerade braucht. Kanalübergreifende Werte liefert kein Tool. Eine geschickte Konzeption und Excels‘ vermutlich mächtigstes Feature – Pivot-Tabellen – machen es möglich, komplexe Reportings aufzubauen, die tiefgreifende Analysen des Business erlauben und jede Woche mit nur ein paar Handgriffen aktualisiert werden können.

Wir haben ein Schritt für Schritt Guide dazu geschrieben:

Schritt 1: Datenexporte aus den verschiedenen Quellsystemen konfigurieren

Die meisten Quellsysteme, in denen Daten originär entstehen und erfasst werden, bieten die Möglichkeit des Exports aller relevanten Kennzahlen als Excel oder CSV-Datei. Hierbei ist es wichtig, die Exporte so zu konfigurieren, dass sie die Daten auf der kleinstmöglichen Aggregationsebene ausgeben, die für spätere Analysen benötigt wird, und dann so abzuspeichern, dass sie jede Woche im selben Format exportiert werden können. In der Regel bedeutet das, Exporte zu konfigurieren, die die Performance eines Kanals pro Tag und Aktion ausgeben. Das geht z.B. bei Google Analytics, Social Media Management Software Lösungen wie Falcon und allen gängigen Webtracking- und Newslettertools mit wenigen Handgriffen. Diese Exporte können dann mit den meisten Tools auch per Email versendet werden.

Um die Daten verschiedener Kanäle nicht nur auf Tagesbasis zusammenzuführen, sondern auch einzelne Aktionen gezielt auswerten zu können, muss natürlich noch sichergestellt werden, dass z.B. Aktionen, die auf den Webshop verlinken, schon bei der Anlage mit einem eindeutigem URL-Parameter ausgestattet werden, so dass der darüber generierte Traffic vom Webtracking-Tool der Wahl auch eindeutig zugeordnet werden kann.

Schritt 2: Quelldaten in Excel anreichern und als Tabelle formatieren

Die so generierten Exporte können dann in Excel kopiert und angereichert werden. In der Regel enthält jeder Export die Performance verschiedener KPI je Tagesdatum. Ein Reporting aggregiert in der Regel die Performance auf Wochen-, Monats- und Jahresbasis. Daher müssen zunächst zusätzliche Spalten hinzugefügt werden, die über die Befehle KALENDERWOCHE(), MONAT() und JAHR() die Dimensionen des späteren Reportings bilden. Außerdem können aus den exportierten Kennzahlen weitere KPI errechnet werden, z.B. im Bereich Social Media aus den absoluten Fanzahlen das Netto-Fanwachstum oder aus Interaktionen und Reichweite die sog. Engagement Rate.

Der entscheidende Kniff ist dann, die Quelldaten über EINFÜGEN – TABELLE als Tabelle zu formatieren. So erkennen die Pivot-Tabellen später automatisch, wenn neue Daten hinzugefügt werde und alle Anreicherungen werden automatisch auf neue Quelldaten angewendet.

Schritt 3: Aufbereitung der Daten mithilfe von Pivot-Tabellen und berechneten Feldern

Vorbereitung der Daten mit Pivot-Tabellen

Das mächtigste Werkzeug innerhalb von Excel sind zweifellos Pivot-Tabellen. Sie erlauben es, Quelldaten entlang beliebiger Dimensionen zu schneiden, zu sortieren und auszuwerten, quasi auf Knopfdruck. Indem wir die jeweiligen Tabellen mit den Quelldaten markieren und über die Auswahl EINFÜGEN – PIVOT-TABELLE erzeugen wir Pivot-Tabellen zur Auswertung der Daten des jeweiligen Kanals. Wir machen das für eine bessere Übersicht pro Kanal in einem eigenen Datenblatt. Dabei ist es durchaus möglich, mehrere Pivot-Tabellen über die gleichen Quelldaten zu legen, wenn im späteren Reporting z.B. sowohl auf Wochen- als auch auf Monatsbasis Kennzahlen ausgewertet werden sollen.

(Kalenderwochen und Monate sind nicht deckungsgleich und dieselbe Kalenderwoche kann sowohl im Januar als auch im Februar auftauchen, mit jeweils nur einem Teil von 7 Tagen. Das würde später zu Problemen führen, wenn wir sowohl Monate als auch Kalenderwochen als Dimensionen der gleichen Pivot-Tabelle verwenden.)

Alle Quelldaten werden nun auf diese Weise geordnet, so dass jede Kennzahl in jeder gewünschten Aggregationsebene vorliegt.

Gewichtete Mittelwerte durch Berechnete Felder

Ein Fehler, der dabei häufig gemacht wird, ist es zu übersehen, dass z.B. das Bilden eines Mittelwertes einer Kennzahl mithilfe der Pivot-Tabelle in der Regel nur den ungewichteten Mittelwert der Tagesdaten ausgibt. Abhilfe schaffen die sog. berechneten Felder, die über

PIVOT-TOOLS - ANALYSIEREN - FELDER, ELEMENTE UND GRUPPEN - BERECHNETES FELD

aufgerufen werden können. Auf diese Weise kann z.B. das gewichtete Mittel der Engagement Rate errechnet werden, in dem die Summe der Interaktionen durch die Summe der Reichweite für die jeweilige Kalenderwoche und den jeweiligen Monat berechnet wird. Wer es ausprobieren möchte, kann diese Kennzahl gerne in den Quelldaten auf Tagesbasis berechnen lassen und in der auswertenden Pivot-Tabelle den Mittelwert der Tagesdaten mit dem jeweiligen Wert des gerade neu hinzugefügten berechneten Feldes vergleichen. Hierbei wird es für die aggregierten Wochen, Monate und Jahre zu unterschiedlichen Werten kommen. Das Verwenden des ungewichteten Mittels ist in vielen Fällen schlichtweg falsch und kann im schlechtesten Fall zu falschen Interpretationen und Entscheidungen führen, ist aber wie hier gezeigt einfach zu vermeiden.

Schritt 4: Die Funktion PIVOTDATENZUORDNEN() – Ja: wirklich.

Sobald man in einer gewöhnlichen Zelle auf Daten aus einer Pivot-Tabelle verweist und die Funktion GETPIVOTDATA() aktiviert ist, erzeugt Excel unter Windows zunächst einen – zugegebenerweise ziemlich unübersichtlichen – String.  In unserem Fall zum Beispiel:

PIVOTDATENZUORDNEN("Total page likes";'FB Pivot'!$A$1;"Monat";Februar;"Jahr";2016)

Ein Großteil der Kollegen aus dem Controlling oder der BI-Abteilung, die als Power User normalerweise virtuos mit Excel umgehen können, deaktivieren* die Funktion deshalb oft zuallererst, obwohl sie tatsächlich fantastische Dienste leistet. Auf diese Weise wird die obige Formel nicht erzeugt und ein einfacher Verweis auf die explizite Zelle wird möglich. Dadurch verliert man aber die großartige Flexibilität, die die Funktion bietet.

Obige Zeile sagt Excel nämlich, aus der Pivot-Tabelle, die im Blatt ‚FB Pivot‘ liegt und der Zelle A1 beginnt, genau den Wert der Variable Total page likes zu extrahieren, der für den Monat Februar und das Jahr 2016 vorliegt. Und darin liegt die ganze Schönheit des nächsten Schritts.

Schritt 5: Entwicklung eines übersichtlichen Designs & dynamisch aufgebauter Charts

Durch die Verwendung von PIVOTDATENZUORDNEN() können wir jetzt nämlich vollkommen unabhängig von der Anordnung der Daten in der Pivot-Tabelle ein Design entwerfen, das ganz auf die eigenen Bedürfnisse zugeschnitten ist, Auszüge der Daten hervorhebt und sogar Charts dynamisch zusammenbaut. Dabei sind uns keine Grenzen gesetzt. Wenn man dann die Werte Februar und 2016 noch dynamisch über einen expliziten Bezug ansteuert und die Bezüge ein wenig geschickt aufbaut, kann man das komplette Dashboard konfigurierbar machen: Wir wählen zum Beispiel in zwei Feldern zur Konfiguration den Beobachtungszeitraum und einen Vergleichzeitraum aus, und alle Werte des Dashboards aktualisieren sich vollautomatisch, genauso wie die Charts, die jetzt auf den immer gleichen Datenbereich verweisen. Der bleibt jetzt nämlich mithilfe des obigen Trick gleich, nur die Werte werden aktualisiert.

Der Aufwand zur z.B. wöchentlichen Aktualisierung des Reportings reduziert sich auf wenige Minuten, da nur die vorkonfigurierten und im besten Fall per Email automatisch zugesendeten Quelldatenexporte kopiert werden müssen und die Pivot-Tabellen dann über einmaliges DATEN – ALLE AKTUALISEREN auf den neuesten Stand gebracht werden. Alle Felder werden innerhalb von Milisekunden neu berechnet und das Reporting, Vergleichswerte und Charts aktualisieren sich ohne weiteres Zutun und ohne ein einziges, kompliziertes Makro.***

So wird es möglich, neue Daten mit minimalem Aufwand zu integrieren, schnell beliebige Zeiträume miteinander zu vergleichen, und die frei gewordenen Zeit zu nutzen, um unabgelenkt und kanalübergreifend Erkenntnisse zu gewinnen und Optimierungspotenziale einfach zu erkennen. Denn darum geht es ja bei all dem Reporting tatsächlich.

Auf dem Weg dahin helfen wir übrigens gerne.

Get my reporting shit done fast:

*P.S.: GETPIVOTDATA() lässt sich steuern über PIVOTTABLE-TOOLS – ANALYSIEREN – OPTIONEN.

*P.P.S.: Beispiel mit simulierten Daten.

*P.P.P.S.: All das funktioniert auch in einer perfekten Welt, in der auf das Data Warehouse aus Excel heraus mithilfe von Pivot-Tabellen zugegriffen werden kann. Dann reicht tatsächlich ein Klick und das neueste Reporting steht bereit.