Bei aller Begeisterung für moderne und mächtige Tools wie die Searchmetrics Essentials und die Searchmetrics Suite: Microsofts Excel bleibt immer ein guter Freund des SEOs, weil nur darin wirklich alle Daten so zusammen gemixt werden können, wie sie zusammen gehören. Weil aber nicht jedem Funktionen wie SVERWEIS, SUMMEWENNS und Pivot-Tabellen geläufig sind, stelle ich hier einige Tabellen aus der Daten-Krabbelkiste vor.
Also, los geht’s. Ihr braucht:
- die Ranking-Daten von Searchmetrics. Die gibt es entweder in den Essentials (Rankings / Longtail) oder in der Searchmetrics Suite bei den Performance-Daten.
- die Backlink-Daten von Searchmetrics. Die gibt es im Link-Bereich der Suite (Wichtig: Alle Linkdaten exportieren und diese Tabelle verwenden). Die sind deshalb wichtig, weil sich darin für alle Links die Quell- und die Ziel-URL in einer Tabelle befinden. Allerdings sind die “Verlinkten Seiten” in den Essentials für viele Bedürfnisse auch schon ein guter Anlaufpunkt.
- Die 404-Fehler aus den Google Webmaster Tools oder gerne auch eigene Crawler-Daten nach 404-Seiten.
- Google Analytics Daten wie etwa die Auflistung der stärksten SEO-Verzeichnisse oder Seiten.
- Oder natürlich auch alle anderen Datenquellen…
- Die Beispiel-Datei, die ihr hier als Excel-Tabelle downloaden könnt.
Bemerkung: Alles hier sind nur Beispiele. Da jede Analyse ihren eigenen Anforderungen folgt, müsst ihr alles jeweils anpassen. Ich möchte euch hiermit nur das Prinzip erklären – und kann natürlich eh nicht alles zeigen, weil das nur mit Kundendaten sinnvoll wäre. Und die darf ich euch leider nicht zeigen. Aber mit der Tabelle, die ihr downloaden könnt, habt ihr beispielhaft und kommentiert eine Menge Formeln, die ihr nur übertragen müsst. Ich habe das alles mit echten Daten von unserem SEO-Book durchgerechnet. Die Daten sind zwar nicht unbedingt typisch – aber echt. Und damit analysiert es sich besser als mit fiktiven Mond-Zahlen.
Beispiel 1: Linkmanagement mit GWT und Backlink-Daten
Wer auch immer sich mit Linkaufbau beschäftigt, sollte auch über das Linkmanagement vorhandener Backlinks nachdenken. Hierfür gibt es zwei sehr gute Quellen – die aber noch zusammen gebracht werden müssen: Erstens gibt es bei Searchmetrics die Liste der Links (mit Quell- und Ziel-URL) und aus den Google Webmaster Tools stammt eine Liste der 404-Fehlerseiten. Auch gut ist, wenn ihr die 404-Daten von einem eigenen Crawler habt. Aber da fehlen euch dann möglicherweise 404-Seiten, die nur von extern angelinkt werden. Liegen also beide Tabellen in Excel vor, kann man für jeden Link nachschauen, ob die dazu gehörige Seite auch in der 404-Liste vorkommt – oder anders herum. Da wir im SEO-Book ziemlich wenige Fehler haben, haben wir das so herum gemacht.
Die Funktion in der Spalte “F” heißt:
=ZÄHLENWENN(‘Backlink Daten aus der Suite’!I:I;’Crawling Fehler aus GWT’!A2)
Diese Funktion ist recht einfach: Hier wird in einer anderen Spalte lediglich gesucht und bei jedem Fund mitgezählt. Das lässt sich auch für andere Analysen anwenden. Ihr findet in dem beigefügten Excel einige Beispiele.
Beispiel 2: Das detaillierteste SERPs-Spreading
Das SERPs-Spreading, also die Verteilung der Suchergebnisse über die Ergebnisseiten von Google gehört zu einem der wichtigsten Standard-Reports – vor allem wenn es um Penality-Erkennung geht. Mit den Performance-Daten kann man sich ein solches SERPs-Spreading auch für jede einzelne Position zusammen klicken:
Das kann man natürlich noch fleißig ausbauen. Sehr interessant ist bei Affiliate-Seiten z.B. die Verteilung der Summe der CPCs über die Positionen. Oder für Publisher die Summe des Traffic-Potentials pro SERPs-Position. Oder bei Shops die Positionierung mit den Conversions aus Google Analytics. Und, und, und. Da haben wir schon eine Menge großer Überraschungen gefunden…
Beispiel 3: Google Analytics Daten mit Searchmetrics-Daten erweitern
Der Report aus Google Analytics, welche Seiten am meisten SEO-Beitrag bringen, ist ja schon an sich interessant. Wenn wir jetzt noch wüssten, für wie viele Keywords die Seite jeweils rankt – und auch für welche – das wäre super, gell? Hierbei lernen wir den SVERWEIS kennen.
Die Funktion in der Spalte “F” lautet:
=WENNFEHLER(SVERWEIS(A2;’Rankings aus den Essentials’!B:G;6;0);””)
Der SVERWEIS bekommt zunächst eine Matrix in der Ranking-Tabelle zugewiesen und die Index-Zahl, in welcher Spalte der Wert steht, der ausgegeben werden soll. Dann wird nach dem zu suchenden Wert in der Zelle geschaut und der Inhalt der Zelle mit der entsprechenden Index-Zahl eingetragen.
Und mit dem WENNFEHLER kann man die unschönen Fehler eliminieren, die sich zeigen, wenn ein Verweis nicht gefunden wurde. Die Logik hierfür ist: “=WENNFEHLER(DIE FORMEL UM DIE ES GEHT;””). Die beiden Anführungszeichen zeigen, dass dann halt nix drin stehen soll, wenn es einen Fehler gibt.
Und warum macht man das nicht über eine Pivot-Tabelle mit den Google Analytics Daten? Das kann man natürlich auch machen. Aber das Charmante an dieser Lösung: So bekommt man bei Bedarf neben der Position für jede Seite auch noch die Summe der CPCs oder das Traffic-Potential und kann damit schöne Prioritätenlisten zur Abarbeitung definieren. Aber alle Geheimnisse unserer Arbeit möchte ich hier ja auch nicht verraten…
Beispiel 4: Die Backlink-Spinne
Das folgende Beispiel zeigt eindrücklich, dass Verlinkung in den Verzeichnissen nicht immer gleich der Sichtbarkeit nutzt. Schaut euch mal in dem Schaubild diese Verteilung an:
Zugegeben, das muss nicht unbedingt eine “Spinne” sein, ein einfaches Balkendiagramm hätte es auch getan. Aber: Ihr wollt doch mit euren Grafiken auch mal überraschen. Und nach dem tollen Vortrag von Markus Uhl “Excel für SEOs” auf der SEO Campixx habe ich gedacht, ich schlage euch mal ‘ne Spinne vor. Dann werden sich die Empfänger eurer Reports freuen.
Die Spinne ist eigentlich immer dann sinnvoll, wenn man das Profil verschiedener Daten vergleichen möchte. Ideal also für einen Wettbewerbs-Vergleich zweier Seiten anhand ihrer Link-Daten. Wenn ihr euch also die prozentuale Menge von “nofollow”-Links, Bilder-Links, News-Links u.s.w. zweier Seiten in einem solchen Spinnen-Diagramm anschaut, ist das meist sehr eindrücklich. Gehört aber inhaltlich nicht hierher – deshalb das Beispiel hier oben.
Beispiel 4: Linkstärke pro Seite checken
Der SPS von Searchmetrics ist ja so etwas wie ein Pagerank – aber ohne Google-Nebel. Ihr könnt ihn in der Suite und den Essentials für jede einzelne Seite abrufen. Streng genommen bräuchte man also diese Tabelle gar nicht:
Aber halt nur streng genommen: Denn diese Funktion ist natürlich für jede beliebige URL-Liste sinnvoll und ermöglicht eine Menge Erweiterungen. Hier wird mit den Funktionen ZÄHLENWENN und SUMMEWENN gearbeitet. Schaut euch das in der Tabelle genauer an. Das Ergebnis ist jedenfalls in der ersten gelben Spalte die Summe der SPS-Werte aller URLs, die auf diese URL zeigen. Und in der zweiten gelben Spalte steht die Summe der Links. Ist etwa der SPS bei wenigen Links recht hoch (erste Zeile), sind das wohl starke Links – habe ich dagegen wenig SPS bei vielen Links, tja, dann sind die wohl nicht sooooo super…
Kombinationen möglich
Und nun stellt euch vor, ihr kombiniert nun diese SPS-Linkdaten mit der Conversion der URLs plus vielleicht den internen Links (die es auch in den Essentials gibt), kann man sofort sehr konkrete Tasks ableiten. Oder schnappt euch die Ranking-Daten der Universal Search und schaut, wie sich Links auf die Verwendung eurer Bilder und Videos in den One-Boxen auswirken.
Ach, euch fallen sicher ganz viele Datenkombinations-Möglichkeiten ein. Ihr seid ja SEOs ;-)
Stolpersteine wegräumen
Vor dem Spaß mit dem Kombinieren der Daten in Excel liegen allerdings einige Steine im Weg. Hier ein paar Tipps, wie man diese wegräumen kann:
- Jedenfalls mit meiner Apple-Umgebung gibt es immer eine Menge Ärger beim Kopieren der Daten. Häufig kommen die Umlaute und Sonderzeichen durch mehrfache UTF8-Codierung oder einen anderen Kram falsch an. Ich löse das, indem ich erstens mit “TextWrangler” die jeweiligen CSV Datei einmal öffne und die Daten dann per Copy & Paste ins Excel kopiere. Dann klappt es meist sehr einfach.
- Zwischen der Darstellung der URLs von Searchmetrics und Google besteht meist der Unterschied, dass in dem einen “http://www.domain.de” mitgeliefert wird und im anderen nicht. Wenn ihr aber z.B. für den SVERWEIS Felder miteinander vergleichen wollt, müsst ihr diese Daten anpassen. Löscht einfach per “Ersetzen” die Zeichenkette “http://www.domain.de” mit “” (also leer).
- Immer, wenn mit einer Matrix gearbeitet wird (also auch wieder der SVERWEIS), muss die Spalte, in der sich der Wert befindet, der übertragen werden soll, rechts von der Spalte befinden, in der verglichen bzw. gesucht wird. Das bedeutet für einige Tabellen, dass ihr vor dem Vergleich diese Spalte noch einmal kopieren solltet.
- Löscht keine Daten! Ja, leider crasht Excel manchmal oder man verliert die Übersicht über seine Tabellen. Deshalb achte ich darauf, dass in irgend einem Tabellenblatt die Urspurngsdaten vorhanden bleiben und in allen Funktionen darauf zugegriffen wird.
- Etwa als Inhouse-SEO oder Agentur, die einen Kunden längere Zeit begleitet, solltet ihr die CSV-Daten jeden Monat einmal auf Vorrat exportieren. Dann könnt ihr auch gerne mal ein paar Monate zurück gehen und schauen, wie es sich damals verhalten hat. Das könnte wichtig sein. Bei Searchmetrics habt ihr das recht einfach: Denn etwa die Performance-Daten könnt ihr auch historisch abrufen.
Noch weitere Tipps zu den Searchmetrics Exporten?
Ich könnte mir vorstellen, dass ich manche Funktionen etwas umständlich anwende – denn ich bin ja auch nur SEO und kein Excel-Master. Deshalb würde ich mich sehr über eure Ergänzungen und über weitere Tipps freuen.