Automatisierte SEO-Analysen und Reportings aus verschiedenen Datenquellen mithilfe einer Google-Tabellenerweiterung

Google Sheets – das bessere Excel?

Als Grundlage, um Zahlen aus diversen Tools übersichtlich zu präsentieren, dient Google Sheets. Mit der Freeware lassen sich Auswertungen in ansprechenden Tabellen, Diagrammen und Grafiken erstellen. Alles, was hierfür benötigt wird, sind ein Google-Konto und der Link zu docs.google.com/spreadsheets.

Es handelt sich bei der Online-Applikation um eine vereinfachte Version von Microsoft Excel, die ein gemeinsames, zeitgleiches Arbeiten mehrerer Personen in dem Programm ermöglicht. Durch vordefinierte Formeln, Pivot-Tabellen und verschiedene Formatierungsoptionen gehen Routinearbeiten zügig von der Hand. Da es sich um ein Online-Tool handelt, kann man jederzeit und überall auf seine Tabellen zugreifen und sie entweder am Smartphone, Tablet oder Computer bearbeiten. Das Ganze ist mit Excel kompatibel, sodass dort erstellte Inhalte in Google Sheets konvertiert werden können und umgekehrt.

Um eine SEO-Analyse zu starten, klickt man auf der Startseite von Google Sheets auf „Neue Tabelle anlegen“ und wählt anschließend in der Navigationsleiste unter „Add-ons“ aus: „Add-ons aufrufen.“ Hier gibt man „Supermetrics“ ins Suchfeld ein und lädt die Erweiterung mit einem Klick auf den Button herunter. Nun ist die Erweiterung integriert und die verschiedenen Datenquellen, die von Interesse sind, können angebunden werden. Es stehen zahlreiche zur Verfügung, z. B. Adobe Analytics, Google Analytics, Bing Webmaster Tools, Search Console, Searchmetrics, Moz, Yandex Metrica, YouTube, Twitter, Facebook Insights und viele mehr.

Supermetrics – das Marketing-Add-on für Google Sheets

Das Besondere an der Supermetrics-Erweiterung ist das automatisierte Ziehen und Aktualisieren von Daten aus verschiedenen Datenquellen, was die Durchführung von SEO-Analysen stark vereinfacht und viel Zeit beim Aufstellen und Aktualisieren von Reports spart. Beliebige Metriken werden mit verschiedenen Dimensionen aus unterschiedlichen Tools kombiniert und individuell visualisiert. Das heißt, man kann z. B. die organische Sichtbarkeit einer Website aus Searchmetrics neben Besucherzahlen aus Google Analytics legen, mit einem vorherigen Zeitpunkt vergleichen und automatisiert im gewünschten Turnus aktualisieren lassen. Wie das genau im Einzelnen funktioniert, wird im Folgenden aufgezeigt.

Datenquellen anzapfen am Beispiel von Searchmetrics

Um die Daten aus einer externen Quelle in eine Google-Tabelle einzubinden, klickt man in der Navigation unter „Add-ons“ auf „Supermetrics“ und dann auf „Launch sidebar“: Hier sind alle Tools untereinander aufgelistet, z. B. Searchmetrics. Um Supermetrics mit Searchmetrics zu verknüpfen, müssen „API key“ und „API secret“ (geheime Zeichenfolgen) des Accounts angegeben werden, um unbefugte Zugriffe zu vermeiden und sich beim Server zu authentifizieren. Diese findet man im Reiter „Einstellungen“ bei Searchmetrics unter „Meine API“.

Nach der Verknüpfung der beiden Programme wählt man unter „Query type“ (Datenabfrage-Typ) die Metrik aus Searchmetrics aus, die man in Google Sheets importieren möchte, z. B. SEO Visibility, Anzahl an Backlinks oder Keyword Performance für Domains bzw. URLs. Im nächsten Reiter „Select dates“ (Datumsauswahl) wird der untersuchte Zeitraum für die Abfrage ausgewählt (z. B. die letzten zwölf Monate) und bei Bedarf ein Vergleichszeitraum definiert. Im Tab „Select fields“ (Feldauswahl) wird festgelegt, in welcher Spalte der Tabelle der jeweilige Wert angezeigt werden soll. Um die Daten zu importieren, klickt man nun auf „get data to table“ und hat so auf einfache Weise einen neuen Query (Datenabfrage) erzeugt.

Datentabellen erstellen und automatisiert aktualisieren in separatem Tab

Möchte man mehrere Queries aus unterschiedlichen Quellen in seine Auswertung importieren, ist es ratsam, einen separaten Tab (Reiter) in Google Sheets für die erstellten Datentabellen zu nutzen, diese mit entsprechenden Überschriften zu markieren, farblich abzugrenzen und nebeneinander anzuordnen. Dieser Reiter wird ausschließlich für die Erstellung und Aktualisierung der Daten genutzt. Die Aktualisierung kann manuell oder automatisiert vorgenommen werden. Möchte man beispielsweise jeden Montagmorgen aktualisierte Daten vorfinden, terminiert man das Daten-Update dementsprechend unter „Add-ons“, „Supermetrics“ und „Schedule refresh & emailing“.

Automatisierte Dashboards für SEO-Reportings erstellen

In der Gestaltung eines Dashboards ist man vollkommen frei. Jede relevante KPI (Leistungskennzahl) aus den vielen mit Supermetrics verbundenen Datenquellen lässt sich in beliebiger Reihenfolge und Darstellungsweise in einem Dashboard abbilden. Es können z. B. Tabellen, Linien-, Säulen-, Flächen-, Balken- oder Kreisdiagramme verwendet werden, je nach jeweiligem individuellem Anwendungsfall. Die Daten werden in einem separaten Reiter aus der Datentabelle gezogen und automatisch aktualisiert, ohne manuell eingreifen zu müssen.

Möchte man beispielsweise die Searchmetrics-Sichtbarkeitsdaten einer Domain neben die Anzahl der Besucher, Bestellungen und den erzielten Umsatz aus Adobe Analytics für einen bestimmten Zeitraum (beispielsweise Vergleich letzter Monat mit Vormonat) legen und in einer Tabelle darstellen, ist das folgendermaßen umsetzbar:

1. Schritt: Einrichtung Searchmetrics-Query in Datenblatt-Tab

Auswahl der folgenden Elemente im Supermetrics Add-on:

• Data source: Searchmetrics
• Query type: Organic Searchmetrics SEO Visibility Historic List in Daten-Tab
• Type URL: Domain.de
• Select dates: Last 1 year (including this year)
• Select fields: Date, Domain, SEO Visibility
• „get data to table“

2. Schritt: Einrichtung Analytics-Query in Datenblatt-Tab

Auswahl der folgenden Elemente im Supermetrics Add-on:

• Data source: Adobe Analytics
• Select account: Domain.de
• Select dates: Last month, compared to previous period
• Select metrics: Visits, Revenue, Orders
• „get data to table“

3. Schritt: Zusammenführen der Daten in einer Tabelle im Dashboard-Tab

Da zwei unterschiedliche Abfragen erzeugt wurden, befinden sich zwei voneinander unabhängige Tabellen im Datenblatt-Tab. Diese müssen im nächsten Schritt in den Dashboard-Tab übertragen und zusammengefügt werden. Hierzu werden die Werte „Sichtbarkeit“, „Visits“, „Orders“ und „Revenue“ in die Tabelle eingetragen. Die jeweiligen Adobe-Analytics-Werte aus der Tabelle können mit dem Befehl „entsprechende Zelle aus dem Datenblatt-Tab auswählen“ (z. B. „=Datenblatt!G5“ für die Visits und „=Datenblatt!H6“ für die prozentuale Veränderung der Visits) verknüpft werden. In diesem Fall ist die Verknüpfung mit dem Dashboard-Tab einfach, da die Zellbezüge bei jeder Aktualisierung der Daten jeweils identisch bleiben (statischer Zellbezug).

Bei der automatisierten Verknüpfung von Searchmetrics-Sichtbarkeitsdaten mit dem Dashboard steht man vor der Herausforderung, dass bei der wöchentlichen Datenaktualisierung jeweils eine neue Zeile zur bestehenden Tabelle hinzugefügt wird. Es ist also nicht möglich, mit einem statischen Zellbezug zu arbeiten, weil sich die Zelle, in der sich der aktuelle Sichtbarkeitswert befindet, und der Vergleichswert von vor einem Monat jeweils verändern. Es können folgende Formeln verwendet werden, um jeweils die aktualisierten Daten zu ermitteln:

Für den aktuellen Sichtbarkeitswert wird eine Formel verwendet, die den letzten Wert der Spalte D in der Tabelle ermittelt: =INDEX(D6:D17;ANZAHL(D6:D17)).

Für den Sichtbarkeitswert von letztem Monat wird die gleiche Formel verwendet, nur mit einem kleinen Zusatz, damit der fünftletzte Wert der Spalte D (=Vormonat) in der Tabelle ermittelt wird: =INDEX(D6:D17;ANZAHL(D6:D17)-4).

Der aktuelle Sichtbarkeitswert und der von letztem Monat werden nun in Zelle H10 und H11 bei jeder Aktualisierung der Daten ebenfalls im gewünschten Format automatisch upgedatet. Diese beiden Zellen werden mit dem Dashboard-Tab mittels =Datenblatt!H10 bzw. H11 verknüpft und in der neuen Tabelle zusammen mit den Analytics-Daten übersichtlich in Reporting-Form untereinander dargestellt.

Die prozentuale Veränderung zum Vormonat kann mithilfe bedingter Formatierung unter dem Menüpunkt „Format“ farblich angepasst werden. Fertig ist die Verknüpfung von Searchmetrics und Analytics in einer gemeinsamen Tabelle, deren Daten sich ab sofort automatisch aktualisieren.

Automatisiertes Content Intelligence Center/SEO-Analysen erstellen

Supermetrics lässt sich auch dafür einsetzen, SEO-Analysen aus verschiedenen Tools in Google-Tabellen abzubilden und beispielsweise ein Content Intelligence Center (CIC) daraus zu erstellen. Darunter ist zu verstehen, dass die URLs einer Domain bestimmt werden können, die als Nächstes inhaltlich optimiert werden sollen. Die Analyse kann also dazu dienen, eine redaktionelle Roadmap anhand verschiedener KPIs zu erstellen. Hierzu werden die einzelnen URLs einer Domain neben Haupt-Keyword, Suchvolumen für das Keyword, Wortanzahl der URL, Impressions bei Google, Klicks bei Google, Click-Through-Rate (= Klicks/Impressions), Visits, Bounce-Rate (Absprungrate), Status-Code der URL und Page Authority gelegt. Die jeweiligen Informationen werden aus den Tools „Adobe/Google Analytics“, „Searchmetrics“, „Screaming Frog“, „Search Console“ und „Moz“ gezogen.

1. Schritt: Einrichtung der Queries im Datenblatt-Tab

Zunächst werden die einzelnen Queries für die zu untersuchenden URLs im Datenblatt-Tab erstellt. Bei kleineren Domains kann man problemlos alle URLs crawlen lassen, bei größeren Domains ab 10.000 URLs ist es empfehlenswert, die Domain auf einzelne Verzeichnisse aufzuteilen, damit die gleichzeitig berechnete Datenmenge nicht zu groß wird und die einzelnen Schnittstellen überlastet.

Haupt-Keyword definieren

Als Erstes wird untersucht, ob das Hauptthema der URL überhaupt ein relevantes Suchvolumen bei Google hat. Hierzu wird das wichtigste Keyword definiert, was das Thema der jeweiligen Seite am besten beschreibt. Bei einer SEO-optimierten URL-Struktur lässt sich das Haupt-Keyword mit wenigen Handgriffen aus der URL extrahieren (z. B. www.domain.de/kw) und in der dafür vorgesehenen Spalte im CIC darstellen. Um es in einer separaten Spalte abzubilden, wählt man unter dem Menüpunkt „Daten“ in Google Sheets „Text in Spalten aufteilen“ sowie „/“ als Trennzeichen aus. Das Suchvolumen für das jeweilige Keyword wird mittels Searchmetrics Query type „Keyword Performance Metrics List“ ermittelt und in der darauffolgenden Spalte dargestellt.

Wortanzahl bestimmen

Als Nächstes soll herausgefunden werden, wie viel Text eine URL beinhaltet. Hierzu wird auf das Tool Screaming Frog zurückgegriffen und der Wert „Word Count“ in die Tabelle importiert. Liegt die Wortanzahl unter einem bestimmten Schwellenwert, ist das ein Zeichen dafür, dass kein Beschreibungstext auf der jeweiligen Seite vorhanden ist. Ohne relevanten Text ist es selbstverständlich schwierig, zu dem gewünschten Thema/Keyword bei Google zu ranken.

Impressions, Klicks und CTR abbilden

Die folgenden drei Spalten liefern Impressions, Klicks und Click-Through-Rate bei Google (prozentualer Anteil an Klicks auf ein Suchergebnis im Verhältnis zu den Impressionen eines Suchergebnisses). Sie werden aus der Google Search Console gezogen und geben einen Hinweis darauf, ob das gewünschte Keyword bereits bei Google auf der ersten Seite rankt und Besucher für die Website liefert.

Visits und Bounce-Rates

Die obigen Daten werden durch die tatsächlichen Besucher-Daten aus „Adobe bzw. Google Analytics“ verifiziert. Außerdem wird die Bounce-Rate (Absprungrate) der URLs untersucht, um herauszufinden, bei welchen Themen die User überdurchschnittlich oft die Seite direkt nach dem Seitenaufruf wieder verlassen. Ist das der Fall, ist es ein starkes Anzeichen dafür, dass die Inhalte auf der Seite optimiert werden sollten oder das Thema nicht zum übergeordneten Hauptthema der Domain passt.

Status-Codes und Page Authority

Die letzten beiden Spalten greifen auf Status-Codes und Page Authority von „Moz“ zurück, um zu überprüfen, ob ein technisches Problem bei der jeweiligen Seite vorliegt und wie hoch die eingestufte Autorität der Seite ist, die sich aus der Backlink-Wertigkeit, Relevanz und der Vertrauenswürdigkeit der URL zusammensetzt. Je höher der Wert, desto höher die Chance, mit der jeweiligen URL gute Rankings bei Google zu erzielen.

2. Schritt: Zusammenführen der Daten im CIC-Tab

Nachdem die Verknüpfungen zu den einzelnen Tools hergestellt wurden, können die Informationen anhand der untersuchten URLs sortiert und jeweils in einer Spalte neben der anderen dargestellt werden. Das kann mithilfe eines SVERWEIS geschehen. SVERWEIS bedeutet „Look-up“ im Englischen und kann mit „Suchen“ oder „Nachschlagen“ übersetzt werden. Es handelt sich um einen senkrechten Verweis, der von der ersten Spalte eines Bereiches abwärts nach einem Schlüssel (URLs) sucht und den Wert einer angegebenen Zelle in der Zeile zurückgibt, die gefunden wurde (KPIs). Die einzelnen Spalten werden mithilfe bedingter Formatierung farblich markiert, um auf einen Blick feststellen zu können, bei welcher URL Handlungsbedarf besteht.

Die Auswertung der einzelnen Seiten kann auch hilfreich für die Betrachtung sein, welche URLs entfernt werden sollen. Weisen einzelne Pages weder ein relevantes Suchvolumen noch Rankings oder Visits auf, kann darüber nachgedacht werden, diese Seiten zu entfernen, um den Google Crawling- und Indexierungsfokus auf die wichtigsten Seiten der Domain zu lenken und somit deren Rankings zu verbessern.

3. Schritt: Ableitung redaktionelle Roadmap anhand CIC

Die Auswertung gibt Aufschluss darüber, ob es sich lohnt, eine bestimmte URL zu optimieren, und wenn ja, ob die Inhalte auf der Seite erweitert oder optimiert werden sollten. Hieraus lässt sich wunderbar z. B. unter Zurückgreifen auf das Suchvolumen für ein Keyword plus Wortanzahl und der Bounce-Rates eine zeitliche Abfolge ermitteln, für welche Seiten als Nächstes neuer Content bereitgestellt werden sollte und welche Seiten überarbeitet werden können.