Power Query für SEO: Daten aus Google Search Console visualisieren
Es gibt viele BI- und Visualisierungstools, jedes mit unterschiedlichen Funktionen und Eigenheiten. Microsoft Excel ist jedoch nach wie vor das am weitesten verbreitete Tool, das Benutzern mit unterschiedlichen Fähigkeiten und auf verschiedenen Ebenen der Unternehmenshierarchie zur Verfügung steht. Aus diesem Grund mag ich die Power Query von Excel, die jede Excel-Datei auf fast jedem Computer in ein leistungsstarkes Tool verwandeln kann, mit dem sich Daten ähnlich wie mit Power BI analysieren und visualisieren lassen (der ETL-Prozess ist in der Tat fast derselbe!).
In diesem Artikel werden wir einige grundlegende Schritte abdecken, um Ihre Google Search Console (GSC) Daten mit Power Query in ein SEO-Dashboard zu verwandeln.
Schritt 1: Daten aus der GSC exportieren
Wenn Sie SEO-Projekte durchführen, dann möchten Sie den Fortschritt im Laufe der Zeit verfolgen und die Möglichkeit haben, die Leistung verschiedener Keywords, Themen und Seiten zu analysieren. Google bietet die GSC-API für eine bessere Automatisierung solcher Prozesse an. Dennoch werde ich in diesem Artikel die sehr einfache und benutzerfreundliche Option zum Extrahieren der Daten verwenden - XLSX-Exporte.
Als Erstes müssen Sie alle diese Exporte abrufen. Nehmen wir an, wir wollen den Fortschritt der Suchanfragen wöchentlich verfolgen. Dazu müssen wir in GSC den Bericht "Leistung" öffnen, die entsprechenden Daten für Woche 1 des gewünschten Zeitraums festlegen und auf die Schaltfläche "Exportieren" in der oberen rechten Ecke klicken. Dann wiederholen Sie diese Schritte mehrmals und ändern nur die Daten.
Wenn Sie mit dem Exportieren fertig sind, haben Sie eine Reihe von Dateien mit demselben Namen in Ihrem Download-Ordner:
Wir werden das ein wenig beheben, indem wir die Endung des Dateinamens so ändern, dass wir wissen, welche wöchentlichen Daten in jeder Datei enthalten sind. Achten Sie auf ein einheitliches Benennungsmuster. Im folgenden Beispiel habe ich das Muster [Leerzeichen]-[Bindestrich]-[Leerzeichen]-"Woche"-[Bindestrich]-[zweistellige Wochennummer] verwendet. Das wird auch hilfreich sein, wenn wir diese Daten in Excel importieren.
Schritt 2: GSC-Daten in Excel importieren
Als Nächstes öffnen Sie Excel, erstellen eine neue Datei, gehen auf die Registerkarte "Daten", klicken auf "Daten abrufen" > "Aus Datei" > "Aus Ordner" und wählen den Ordner aus, der die .XLSX-Exporte enthält (wenn Sie das noch nicht getan haben, ist jetzt der richtige Zeitpunkt, um die heruntergeladenen Dateien in einen separaten Ordner zu verschieben).
Im Dialogfenster sehen Sie die Liste Ihrer Dateien. Klicken Sie auf den Dropdown-Pfeil auf den Button "Kombinieren" und wählen Sie "Daten kombinieren und transformieren".
Im nächsten Dialogfenster sehen Sie die Liste der Registerkarten, die in Ihren XLSX-Dateien verfügbar sind. Klicken Sie auf die Registerkarte "Abfragen" und dann auf "OK".
Danach befinden Sie sich im Fenster Query-Editor. Was mir an Power Query gefällt, ist die Tatsache, dass es viele vordefinierte Prozesse gibt, so dass Sie sich nicht erst in die Materie hineinarbeiten müssen. Lassen Sie stattdessen Power Query alles automatisch erledigen. In unserem Beispiel wurden also mehrere zusätzliche Abfragen erstellt, um alle Ihre Dateien zu einem einzigen Datensatz zusammenzufassen. Wir werden sie vorerst nicht benötigen. Als Ergebnis dieser automatischen Schritte brauchen Sie nur noch die Query, die Sie im Ordner "Andere Queries" auf der linken Registerkarte finden. In meinem Fall heißt sie "gsc data". Klicken Sie darauf, um ihren Inhalt im Hauptteil des Abfrage-Editor-Fensters zu sehen.
Als Nächstes werden wir die unnötigen Dateinamen entfernen und nur die Wochennummer als Referenz für unseren Bericht verwenden. Dazu teilen wir die Spalte zunächst nach Trennzeichen auf.
Erinnern Sie sich, dass ich bereits erwähnt habe, wie wichtig konsistente Dateinamensmuster sind? Jetzt ist es an der Zeit, diese Konsistenz zu nutzen. Ich wähle die Zeichenkette " - Woche-" (Leerzeichen-Strich-Leerzeichen-Woche-Strich) als benutzerdefiniertes Trennzeichen, um die Spalte Quelle.Name zu trennen.
Das Ergebnis sind zwei Spalten: eine mit einem langen Dateinamen und die andere mit Zeichenfolgen wie "01.xlsx", "02.xlsx" und so weiter. Um das loszuwerden, können wir entweder diese Spalte erneut aufteilen, indem wir "." (Punkt) als Begrenzungszeichen verwenden, oder wir können den Wert ".xlsx" ersetzen. Das bleibt Ihnen überlassen.
Vergessen Sie nicht, den Typ der Spalte so zu ändern, dass die Wochennummern als echte Zahlen und nicht als Textstrings definiert sind. Am Ende dieses Schritts sollten Sie etwas wie dieses auf Ihrem Bildschirm haben:
Im Grunde genommen würden diese Manipulationen ausreichen, um die für ein einfaches Dashboard benötigten Daten zu erhalten. Man müsste nur auf die Schaltfläche "Schließen und laden nach..." klicken und "Nur eine Verbindung erstellen" und "Diese Daten zum Datenmodell hinzufügen" wählen.
Fügen wir unserer Datei jedoch einige Funktionen hinzu.
Schritt 3: Keyword-Gruppierung hinzufügen
Wir haben eine Liste von Ziel-Keywords, die nach Themen gruppiert sind, als Ergebnis einer Keyword-Recherche, die wir für SEO durchgeführt haben. Wir können diese Liste als zusätzlichen Filter oder als Slicer für unsere Berichte verwenden. Ich habe sie bereits in ein Tabellenobjekt umgewandelt (markieren Sie den Bereich, den Sie haben, und drücken Sie STRG+T).
Um diese Daten zu unserem Bericht hinzuzufügen, müssen wir eine weitere Abfrage erstellen. Klicken Sie auf eine beliebige Zelle in Ihrer Tabelle, gehen Sie auf die Registerkarte "Daten" und klicken Sie auf "Aus Tabelle/Bereich".
Das war's. Es sind keine weiteren Änderungen an dieser Abfrage erforderlich.
Klicken Sie dann auf die erste Abfrage, die Sie erstellt haben (in meinem Fall "gsc data"), gehen Sie zur Registerkarte "Home" und klicken Sie auf die Schaltfläche "Abfragen zusammenführen". Auf diese Weise werden wir die Daten aus unseren beiden Abfragen zusammenführen und einen Slicer oder Filter erstellen.
Das Dialogfenster "Zusammenführen" besteht aus drei Hauptteilen:
- Der obere Teil zeigt die erste Abfrage, die wir zusammenführen wollen (und ihren Inhalt),
- Der untere Teil, in dem eine zweite Abfrage aus einer Dropdown-Liste ausgewählt werden kann,
- Die Dropdown-Liste "Join Art", die es ermöglicht, verschiedene Arten der Zusammenführungslogik zu verwenden.
Als Erstes müssen Sie die zweite Abfrage mit unseren Ziel-Keywords auswählen. Als nächstes müssen Sie Power Query mitteilen, welche Spalten verwendet werden sollen, um beide Abfragen abzugleichen. Klicken Sie dazu einmal auf die Spalte "Abfrage" in der oberen Abfrage und dann auf die Spalte "Ziel-KW" (in meinem Fall) in der unteren Abfrage. Lassen Sie die Verknüpfungsart "Left Outer" ausgewählt und klicken Sie auf "OK":
Bei diesem Vorgang wird die erste Abfrage Zeile für Zeile durchlaufen und die Werte in der Spalte "Abfrage" mit den Werten in der Spalte "Ziel-KW" der zweiten Abfrage verglichen, und bei Übereinstimmung der Werte wird der Inhalt der zweiten Abfrage zurückgegeben. Nachdem wir auf "OK" geklickt haben, sehen wir eine neue Spalte mit dem Namen der zweiten Abfrage, mit der wir sie zusammengeführt haben. Um die Ergebnisse des Abgleichs zu sehen, müssen wir diese Spalte erweitern, indem wir auf die Schaltfläche mit den zwei Pfeilen in der oberen rechten Ecke der Spaltenüberschrift klicken.
Es erscheint ein Pop-up-Fenster, das anzeigt, welche Spalten wir aus der zweiten Abfrage sehen wollen. Behalten wir beide bei und deaktivieren wir die Option "Originalspaltennamen verwenden".
Sie sollten nun zwei neue Spalten sehen, die unsere Ziel-Schlüsselwörter und die entsprechenden Themen in jeder Zeile enthalten, in der die Schlüsselwörter aus zwei Abfragen übereinstimmen.
Schritt 4: Keyword-Ranking-Gruppen hinzufügen
Zur besseren Visualisierung können wir auch eine zusätzliche Gruppierung der Schlüsselwörter nach dem durchschnittlichen Ranking vornehmen. Lassen Sie uns die folgenden Gruppen erstellen: "Top-3", "4-10", "11-20", "21-100".
Dazu können wir eine bedingte Spalte hinzufügen. Gehen Sie im Menü auf die Registerkarte "Spalte hinzufügen" und klicken Sie auf "Bedingte Spalte". Fügen Sie dann mehrere Klauseln hinzu, wie im Screenshot unten:
Nachdem wir auf "OK" geklickt haben, werden wir sehen, dass einige der Ranggruppen von Power Query fälschlicherweise als Datumsangaben interpretiert wurden. Wenn Sie versuchen, einen Schritt zurück zu gehen und diese Gruppennamen in Anführungszeichen zu setzen, wird das nicht besonders gut aussehen.
Um das zu beheben, nehmen wir die Änderungen direkt im Formelfeld über unserer Tabelle vor. Ersetzen Sie einfach die Zeichenfolge #date(yyyy,mm,dd) durch die Werte "4-10" und "11-20" und drücken Sie ENTER.
Das war's. Jetzt können wir diese Query-Abfrage in das Datenmodell laden, da wir sie in keiner anderen Form benötigen. Außerdem hilft dies normalerweise, die Dateigröße erheblich zu reduzieren.
5. Schritt: Ein Dashboard erstellen
Der nächste Schritt besteht darin, das Dashboard aus mehreren Pivot-Tabellen und Pivot-Diagrammen zu erstellen. Da wir die Abfragen zum Datenmodell hinzugefügt haben, erstellen Sie einfach ein neues Blatt oder verwenden Sie eines der vorhandenen leeren Blätter, gehen Sie auf die Registerkarte "Einfügen", wählen Sie "Pivot-Tabelle" und wählen Sie im Popup-Fenster die Option "Das Datenmodell dieser Arbeitsmappe verwenden" ("Use this workbook`s Data Model").
Wir werden die folgenden Schritte nicht im Detail besprechen, da es hier um individuelle Anforderungen und persönliche Präferenzen geht.
In dem Beispiel unten habe ich eine Pivot-Tabelle verwendet, die Sie in der Mitte sehen, und zwei obere Diagramme, die diese Pivot-Tabelle als Datenquelle verwenden. Drei weitere Pivot-Tabellen befinden sich auf einem separaten Blatt als Quelle für die Diagramme im unteren Teil des Dashboards.
Das obere linke Diagramm zeigt die wöchentlichen Klicks nach Keywords. Das obere rechte Diagramm zeigt so ziemlich dasselbe, aber da wir den Diagrammtyp "Stacked Column" gewählt haben, können wir die Gesamtklicks nach dem gewählten Thema sehen.
Erinnern Sie sich, dass wir die Abfrage "Ziel-KW" hinzugefügt haben? Das haben wir getan, um den Slicer zu erhalten, den Sie im mittleren rechten Bereich des Dashboards sehen.
Und die beiden unteren Diagramme zeigen die Gesamtzahl der Klicks und Keywords aus unserem GSC-Konto, gruppiert nach Rankings.
Möchten Sie die Kirsche auf dem Sahnehäubchen? Das Beste an Power Query, das Ordner als Datenquelle verwendet, ist, dass Sie jetzt einfach neue wöchentliche GSC-Exporte zu dem Ordner hinzufügen können, den Sie bereits haben, und die Datenverbindung einfach aktualisieren!
So, eine kurze Zusammenfassung, wie wir ein SEO-Dashboard in Excel erstellt haben:
- Wir haben wöchentliche Google Search Console-Statistiken im .XLSX-Format exportiert;
- Wir haben die Option "Aus Ordner importieren" von Power Query verwendet, um mehrere Dateien mit identischer Struktur zu importieren;
- Wir haben eine weitere Abfrage mit nach Themen gruppierten Keywords hinzugefügt und sie mit der ersten Abfrage zusammengeführt;
- Wir haben auch eine bedingte Spalte verwendet, um Ranggruppen hinzuzufügen;
- Schließlich haben wir ein Dashboard aus Pivot-Tabellen, Diagrammen und einem Slicer erstellt.
Vielen Dank fürs Lesen!
