Daten mixen: Excel-Spaß mit den Searchmetrics Exporten

3. April 2012 | Analysen 9 Kommentare

Daten mixen: Excel-Spaß mit den Searchmetrics ExportenBei 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.

Beispiel 1: Linkmanagement mit GWT und Backlink-Daten

Von der Tabelle der 404-Fehler schaue ich rüber in die Backlink-Tabelle und zähle, wie viele Backlinks jeder Fehler hat.

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:

Beispiel 2: Das detaillierteste SERPs-Spreading

In den zwei Spalten rechts suche ich nach den jeweiligen Treffern für jede einzelne Position – links ist der Graph dafür.

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.

Beispiel 3: Google Analytics Daten mit Searchmetrics-Daten erweitern

Schaut euch das genauer im Tabellenblatt an…

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:

Beispiel 4: Die Backlink-Spinne

Eine eindrückliche Darstellung von zwei Werten in Bezug auf ähnliche Kategorien. Hier Visibility vs. Links

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:

Beispiel 4: Linkstärke pro Seite checken

Egal, woher die URL-Liste kommt: So kann man sehr schnell die Linkstärke eigener Seiten berechnen.

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.

Dies ist nur ein Gravatar

Wer schreibt denn hier? Eric Kubitz ist Gründer und Geschäftsführer der CONTENTmanufaktur GmbH, die für hohe SEO-Kompetenz und High-Quality-Content steht. Der gelernte Journalist und (Fast-)Wirtschaftswissenschaftler berät große und kleine Portale, gibt sein Wissen als Dozent an Hochschulen und inhouse weiter und spricht regelmäßig auf SEO-Konferenzen. Zu erreichen ist er per mail (ek@contentmanufaktur.net) oder per Google+ oder Xing.
Alle Artikel von anzeigen.

Kommentare (7)

  1. 03. Apr. 2012 14.48 Uhr

    Interessanter Beitrag mit einigen spannenden Ideen. Vieles lässt sich auch mit den SEO Tools for Excel von Niels Bosma (http://nielsbosma.se/projects/seotools/) umsetzen und erweitern.

  2. Jan
    18. Apr. 2012 13.16 Uhr

    Die SEO Tools for Excel sind wirklich TOP, leider gibt es noch keine Lösung für Mac, was wirklich schade ist, da die Macs gerade im Online Marketing ja in den Agenturen weit verbreitet sind…

  3. 19. Apr. 2012 21.45 Uhr

    Danke – ein toller Beitrag. War mir nicht bewusst, was mit Excel im Hinblick auf SEO so alles möglich ist.

  4. 20. Apr. 2012 14.08 Uhr

    Vielen Dank für den Beitrag und die Excel! Das Kodierungsproblem gibt es auch leider immer wieder bei Windows Rechnern. Ich wünschte Excel würde da mehr können!

  5. 30. Jul. 2012 15.49 Uhr

    Gute Software wird bei der Steigerung von Projekten und zunehmender Kundschaft immer wichtiger und sollte auch eingesetzt werden. Einige Programme können einem die Überwachung der SEO Aktivitäten schon sehr erleichtern.

  6. 18. Aug. 2012 08.36 Uhr

    Sehr schöner Beitrag, das werde ich doch mal ausprobieren – hätte nicht gedacht, mal Excel für SEO zu verwenden.

  7. 30. Okt. 2012 20.34 Uhr

    Wie gut, dass es für solche Themen SEO-Experten gibt. Kein Shopbetreiber kann sich doch noch auf den Verkauf konzentrieren, wenn man all das beachten muss.

Trackbacks (2)

  1. 03. Apr. 2012 18.25 Uhr

    […] var addthis_config={"data_track_clickback":true};/**/ Mit beschilderter Beispiel-Datei…Drüben im Searchmetrics Blog habe ich einen Beitrag veröffentlicht, in dem die Verarbeitung von größeren Datenmengen in Excel […]

  2. 05. Apr. 2012 11.28 Uhr

    […] 18. Hallo! Da geht noch mehr… Auf dem Searchmetrics-Blog hatte ich die Möglichkeit, mal ein paar Excel-Spielereien rund um SVERWEIS & Co zu veröffentlichen. Raus gekommen ist dabei eine Datei, mit der die Searchmetrics Daten schnell […]

Hinterlasse eine Antwort

Hinweis: Wenn Du hier keinen Namen einträgst (sondern ein Keyword) oder mir Dein Eintrag zu werbelastig erscheint, behalte ich mir das Recht vor Deinen Kommentar zu löschen oder zu editieren. Spart euch sowas hier also bitte!