R4SEO: Automatisierte Reports mit R generieren (Teil 3)

Im letzten Artikel haben Sie die Daten in den DataFrames gsa_dim_date sowie ga_sessions transformiert. Damit fehlen noch die DataFrames für die URL- und Query-Performance-Daten (gsa_dim_date_page, gsa_dim_date_query) aus der Google Search Console (GSC) sowie den beiden DataFrames für die Desktop- resp. Mobil-Sichtbarkeitsdaten (si_desktop, si_mobile) aus SISTRIX.

Zum Start legen Sie wie bisher ein neues R-Skript an, benennen es mit data_tidying_02.R und kopieren den Inhalt des Skripts data_tidying_01.R hinein. Sie müssen dieses Mal keine neuen Packages installieren, sondern verwenden erneut jene, die Sie bereits aus dem vorherigen Artikel kennen. Führen Sie das neue Skript aus, damit wieder alle Daten zur Verfügung stehen.

Top-Seiten aus der Google Search Console ermitteln

Für den zu erstellenden Report wollen Sie zwei Tabellen generieren, die die Top-10-Seiten bzw. -Suchanfragen nach Clicks der Vorwoche darstellen. Die dafür benötigten Daten liegen jedoch aktuell noch auf Tagesbasis für den gesamten Berichtszeitraum in den DataFrames gsa_dim_date_page und gsa_dim_date_query.

Kopieren Sie daher den Code aus Abbildung 1 ans Ende des zuvor von Ihnen neu erstellten Skripts. Keine Sorge, Sie werden diese Monsterabfrage gleich Schritt für Schritt durchgehen, um im Detail zu verstehen, wie die Daten transformiert werden. Die Absätze zwischen den einzelnen Abschnitten sind nur der Lesbarkeit geschuldet und können von Ihnen weggelassen werden.

Sie möchten für Ihren Report die Vorwoche betrachten. Daher müssen Sie zunächst ermitteln, auf welches Datum der Montag bzw. der Sonntag gefallen ist. Dazu dienen die ersten beiden Code-Zeilen. Anhand dieser können Sie dynamisch, ausgehend vom aktuellen Datum, die beiden Datumspunkte berechnen. Dabei gibt es eine Besonderheit: In den USA beginnt die Woche am Sonntag. Wenn Sie mit Zeitreihen/-funktionen arbeiten, sollten Sie dies stets im Hinterkopf behalten. So wird bspw. auch in Google Analytics (GA) für die Diagrammdarstellung die amerikanische Woche genutzt. Wählen Sie im Kalender hingegen „Letzte Woche", gilt wieder die deutsche Rechnung von Montag bis Sonntag.

Zurück zur Berechnung des Montags und Sonntags der Vorwoche. In Abbildung 2 sehen Sie die einzelnen Schritte im Detail. Die Funktion floor_date() aus dem Package lubridate kennen Sie bereits aus dem ersten Artikel. Dort haben Sie damit den Beginn des abzufragenden Berichtszeitraums berechnet. Mit dem Argument week wird der Funktion angezeigt, dass sie das Datum des Wochenbeginns, ausgehend vom heutigen Datum (today()), finden soll. Anschließend werden sieben Tage subtrahiert, um zum Montag der Vorwoche zu gelangen. Durch die Addition von sechs Tagen finden Sie dann den Sonntag. Die berechneten Datumspunkte schreiben Sie in die Variablen MONDAY_PREVIOUS_WEEK und SUNDAY_PREVIOUS_WEEK, um sie nachfolgend zum Filtern der DataFrames zu verwenden.

Im Anschluss führen Sie die Berechnung der Top-10-Seiten aus. Ihr DataFrame gsa_dim_date_page mit den Performance-Daten sieht wie in Abbildung 3 aus. Für jedes Datum des gesamten Berichtszeitraums, an dem die jeweilige URL in der Spalte page mindestens eine Impression generiert hat, liegen Ihnen die Metriken clicks, impressions, ctr und position vor.

Zunächst müssen Sie den DataFrame auf den Datumsbereich der Vorwoche filtern (Abbildung 1; 1). Dazu geben Sie gsa_dim_date_page an die Funktion filter(). Diese nimmt wiederum die Funktion between() auf. In dieser definieren Sie, dass die Spalte date dahingehend geprüft werden soll, ob die Werte darin zwischen den beiden Datumspunkten MONDAY_PREVIOUS_WEEK und SUNDAY_PREVIOUS_WEEK liegen. Übrig bleiben somit nur noch die Zeilen, welche Daten der Vorwoche enthalten.

Darauf folgt ein group_by() (2), denn Sie wollen die Metriken für jede URL separat aggregieren. Wie die einzelnen Metriken aggregiert werden sollen, definieren Sie mit dem anschließenden summarise(). Die Operation ist etwas abstrakt, daher finden Sie in Abbildung 4 eine schematische Darstellung anhand von drei URLs. Der group_by() teilt den DataFrame in einzelne Untergruppen basierend auf den URLs auf. Dies erfolgt nur intern, d. h., würden Sie den Code nur bis zum group_by() ausführen, sähen Sie keinen Unterschied in der Darstellung zum initialen DataFrame.

Innerhalb des summarise() definieren Sie dann, welche neuen Spalten gebildet werden sollen und wie die Werte dieser Spalten berechnet werden. Für die Spalten clicks und impressions berechnen Sie die Summe (sum()) jeder URL-Gruppe und schreiben das Ergebnis in die Spalten sum_clicks resp. sum_impressions. Die ctr berechnen Sie hier neu, indem Sie die Werte der neuen Spalte sum_clicks durch sum_impressions teilen. Für die position können Sie nicht einfach den Mittelwert berechnen, da die Wertigkeit einer Ranking-Position von den Impressions abhängig ist. Sie können auf Ihrer Website eine Seite haben, die zu einer sehr seltenen Suchanfrage auf Position 1 rankt. Hat diese Seite jedoch nur eine Impression generiert, sollte sie in Relation zu Ihren anderen Seiten mit mehr Impressions und ggf. schlechterem Ranking nicht als Ihre Top-Seite qualifiziert werden. Daher gewichten Sie für die Spalte weight_position die durchschnittliche Position anhand der Impressions. Als Resultat erhalten Sie eine reduzierte Tabelle, in der je URL nur noch eine Zeile vorhanden ist. Die Datumsspalte entfällt automatisch.

Nach dem summarise() folgt noch ein ungroup(), welches die internen Gruppen wieder auflöst.

Der DataFrame sieht nun wie in Abbildung 5 aus. Für die Beispiel-Website sind insgesamt 15 URLs übrig geblieben, die in der vergangenen Woche Impressions generiert haben. Sie wollen in Ihrem Report aber nur die Top-10-URLs nach Clicks anzeigen. Zum Glück gibt es dafür eine top_n()-Funktion (3). Das Argument n nimmt die Anzahl der zu behaltenden Zeilen an. Mit dem Argument wt definieren Sie, anhand welcher Spalte die Auswahl der Zeilen erfolgen soll. Standardmäßig werden somit die zehn Zeilen mit den höchsten sum_clicks-Werten ausgewählt. Wollen Sie die URLs mit den schlechtesten Click-Werten, können Sie ein Minus vor die Spalte schreiben (wt = -sum_clicks). Weil einige URLs die gleiche Anzahl an Clicks erhalten haben, finden sich in der Tabelle allerdings zwölf statt zehn Zeilen.

Ihre Tabelle sieht zum jetzigen Zeitpunkt aus, wie in Abbildung 6 zu sehen. Da Sie die Tabelle in Ihrem Report gerne absteigend nach Clicks sortieren wollen, führen Sie im Anschluss die Funktion arrange() aus (4). In dieser geben Sie die zu sortierende Spalte an. Das desc() (descending) zeigt die absteigende Sortierung an. Lassen Sie es weg, erfolgt die Sortierung automatisch absteigend.

Nachdem Sie den DataFrame in Form gebracht haben, widmen Sie sich der Bearbeitung der Werte (5). Mit der Funktion mutate() können Sie bestehende Zeilen bearbeiten oder neue hinzufügen. Im vorliegenden Fall wollen Sie die Spalten page, ctr und weight_position bearbeiten. Zuerst schneiden Sie die Domain aus den URLs ab. In der Tabelle des finalen Reports nimmt sie nur Platz weg. Sie bedienen sich dazu der Funktion str_replace(). Als erstes Argument nimmt sie die Spalte auf, in der ein String ersetzt werden soll. Das zweite Argument pattern ist das Muster, das ersetzt werden soll. Hier geben Sie die Variable GSC_PROP an, die Sie bereits im ersten Artikel dazu verwendet haben, die GSC-Daten von der API abzufragen; replacement nimmt dann das einzusetzende Zeichen an. Konkret ersetzen Sie dadurch bei allen URLs "https://dein-trueffel.de/" durch "/". Danach runden Sie die Werte der Spalten ctr und weight_position auf drei bzw. zwei Nachkommastellen.

Zum Schluss bringen Sie noch etwas Hochglanz auf. Die Spaltennamen sind aktuell noch normalisiert, sprich, kleingeschrieben und mit Unterstrichen. Da das in Reports nicht unbedingt schön aussieht, benennen Sie sie mittels rename() um (6). Ihre finale Tabelle sehen Sie in Abbildung 7.

Top-Queries aus der Google Search Console ermitteln

Geschafft! Nachdem Sie diese Tour de Force überstanden haben, lernen Sie einen der wesentlichen Vorzüge der Programmierung mit R kennen. Sie können Code-Abschnitte einfach wiederverwenden. Denn für die Berechnung der Top-10-Queries ist der Ablauf identisch (Abbildung 8). Sie müssen nur den anfänglichen DataFrame zu gsa_dim_date_query ändern. Dann noch eine kleine Anpassung im group_by(), denn jetzt aggregieren Sie ja auf Query- und nicht mehr auf URL-Basis. Zu guter Letzt ändern Sie die Benennung des DataFrames, in den die Tabelle geschrieben werden soll, zu gsa_top_10_queries. Die Tabelle mit den Top-Queries sehen Sie in Abbildung 9.

S-DataFrames zusammenführen

Nun können Sie sich dem Zusammenführen der DataFrames mit den Sichtbarkeitswerten (SI) widmen. Fügen Sie dazu den Code aus Abbildung 10 in Ihr Skript ein. Der DataFrame si_desktop, der die Sichtbarkeitsdaten der Desktop-Variante enthält, sieht aktuell aus, wie in Abbildung 11 zu sehen. Bevor Sie diesen DataFrame mit si_mobile zusammenführen, gilt es noch drei Transformationen vorzunehmen (Abbildung 10; 1): Zunächst fällt auf, dass die date-Spalte das Datum mit Zeitstempel enthält. Diese sind für den späteren Report überflüssig und werden daher zu einem reinen Datumswert reduziert. Dazu greifen Sie erneut auf die Funktion mutate() zurück. In dieser wenden Sie die Funktion date(), die das Datum extrahiert, auf die Spalte date an. Den so gewonnenen Wert schreiben Sie in dieselbe Spalte zurück. Sie überschreiben somit die bestehenden Werte einfach. Anschließend entfernen Sie mittels select() die Spalte domain. Das vorangestellte Minus (-domain) zeigt eine negative Auswahl an, sprich, die Funktion soll alle Spalten außer domain auswählen. Schließlich benennen Sie durch rename() die Spalte value zu Desktop um. Dadurch machen Sie explizit, dass es sich bei den Werten dieser Spalte um die Desktop-SI handelt. Würden Sie die Spalte nicht umbenennen und im nächsten Schritt die Mobile-Werte an die Tabelle schreiben, hätten Sie zwei Spalten value – und könnten folglich nicht ohne Weiteres sagen, welche Spalte die Desktop- resp. die Mobile-Werte enthält.

Und damit kommen Sie auch schon zur Funktion, die die beiden DataFrames miteinander verbindet (2). Konkret wollen Sie die Spalte mit den mobilen SI aus dem DataFrame si_mobile als zusätzliche Spalte an den DataFrame si_desktop schreiben, sodass eine Zeile je Datumswert den Desktop- und den Mobile-SI listet. Vergleichbar ist diese Operation, die man als Join bezeichnet, mit einem SVERWEIS in Excel. Sie ist allerdings etwas komplexer, denn die beiden DataFrames weisen unterschiedliche Datumsbereiche auf. Der Desktop-SI wird seit 2008, der Mobile-SI erst seit 2015 reportet. Für jeden Datumspunkt des Desktop-SI gibt es somit nicht unbedingt eine entsprechende Zeile im Mobile-SI. Da Sie im Rahmen von Analysen sehr häufig Tabellen miteinander verbinden werden, finden Sie in Abbildung 12 eine Übersicht der gängigsten Joins.

Im vorliegenden Beispiel wissen Sie, dass der DataFrame si_desktop mehr Zeilen enthält als si_mobile. Sie wenden daher einen left_join() an, denn Datumspunkte in si_desktop, die keine Entsprechung si_mobile aufweisen, sollen nicht entfallen (dies wäre bei einem inner_join() der Fall), sondern zunächst mit NAs aufgefüllt werden und damit erhalten bleiben. Der left_join() ist nun wie folgt aufgebaut: Zunächst nimmt die Funktion als erstes Argument den DataFrame auf, der mit der si_desktop zusammengeführt werden soll. Dies ist der si_mobile. Verschachtelt, innerhalb des inner_join(), transformieren Sie diese Tabelle analog zu si_desktop, mit dem Unterschied, dass Sie die Spalte value nun zu Mobile umbenennen. Das Argument by gibt an, welche Spalte als Schlüssel (Key) dienen soll, anhand dessen die beiden DataFrames miteinander gejoint werden. Hier wählen Sie die Spalte date, denn Sie wollen, dass für jedes Datum der Desktop- und der Mobile-SI in einer Zeile stehen. Die zunächst mit NA aufgefüllten Werte in der Mobile-Spalte ersetzen Sie anschließend durch die bereits bekannte Funktion replace_na() mit 0 (Abbildung 10; 3).

Für Ihren Report haben Sie definiert, dass Sie nur einen Berichtszeitraum von rückwirkend sechs Monaten verwenden möchten (START_DATE und END_DATE aus Teil 1 stehen am Anfang des Skripts). Über die Filterfunktion schränken Sie den DataFrame auf diesen Bereich ein (4). Damit machen Sie im vorliegenden Beispiel den vorherigen Schritt des Auffüllens der mobilen SI-Werte natürlich im Grunde überflüssig, da Sie die Zeilen direkt wieder verwerfen. Dass Sie diesen Schritt jedoch durchgeführt haben, bietet den Vorteil, dass Sie fortan den Berichtszeitraum nach Belieben in die Vergangenheit vergrößern können.

Zum Schluss überführen Sie den nun weiten DataFrame mittels gather() in einen langen (5), damit er eine zum Plotting geeignete Struktur hat. Der finale DataFrame si sieht nun aus, wie in Abbildung 13 zu sehen.