Heute zeige ich euch mal wie man das Google Index Checker Sheet für Excel weiter ausbauen kann, denn wenn man erst mal eine URL Liste hat kann man diese mit anderen Quellen noch um viele wichtige Informationen, wie zum Beispiel Top Keyword für die jeweilige URL, anreichern kann.

Als Vorbereitung nehmen wir dazu einfach unser Google Index Checker Sheet für Excel und wechseln in ein neues Arbeitsblatt. Anschließen öffnen wir Google Analytics, oder ähnliche Statistik Programme die uns wichtige Informationen pro URL liefern können. Die bekannten SEO Tools die uns jede Woche mit Ranking Daten versorgen können im Notfall hier natürlich auch helfen, da sie uns zwar nicht zeigen wie viel Traffic über das jeweilige Keyword erzielt worden ist, aber dank dem Suchvolumen einen guten Hinweis für die Priorisierung geben können.

Wieder zurück zu Google Analytics – wechselt dazu in die Ansicht Content >> Website-Content >> Alle Seiten.

Nun wählt ihr als Sekundäre Dimension das Feld Keyword aus.


Mit diesem Setup bekommt ihr nun alle URL/Keyword Kombinationen die es gibt. Eventuell lohnt es sich die (not provided) und die (not set) auszufiltern und so die Gesamtanzahl zu reduzieren, denn mehr als 50.000 Zeilen lässt die gratis Version von Google Analytics als Export nicht zu.

Google Analytics Daten importieren

Beim Exportieren und Importieren von Google Analytics Daten gibt es ein paar Tricks, die man beachten sollte.

Nachdem wir unsere Daten aus Google Analytics importiert haben, fügen wir eine neue Spalte ein, das ist sehr wichtig, da sonst unser Sverweis nicht funktioniert, denn der Sverweis funktioniert zwischen zwei Tabellen nur wenn die Daten 1:1 identisch sind. Google Analytics liefert uns leider die URLs ohne Domain, unser Google Index Checker Sheet baut hingegen auf vollständigen URLs auf, also müssen wir die Daten aus Analytics erweitern. Wir gehen dazu in die Spalte A die wir neu hinzugefügt haben und fügen dazu folgende Formel ein: =VERKETTEN(“http://www.domain.tld”;B2)

Damit verketten wir unsere Domain mit der Zelle B2 in der die Ziel URL aus Analytics steht. Im Screenshot hier oben seht ihr das Ganze für die Zelle A2.

Als nächstes sortieren wir das Google Analytics Sheet nach den Pageviews und nutzen wir einen Nachteil im Sverweis zu unserem Vorteil. Der Sverweis sucht nämlich nur so lange bis er das erste passende Ergebnis gefunden hat.

Nun wechseln wir wieder zurück in unser Index Sheet und fügen eine neue Spalte hinzu und nutzen dazu die folgende Formel: =WENNFEHLER(SVERWEIS(A8;Tabelle1!A1:J22;3;FALSCH);” “)

Der Sverweis sucht in der Tabelle1 nach dem Suchbegriff in Zeile A8. Damit wir keine unnötige Fehlermeldungen (#NA) erhalten, kombinieren wir es noch mit einer Wennformel, die im Falle eines Fehlers einfach die Zelle leer lassen.

Weitere Formeln

Nun können wir unser Sheet noch mit weiteren Formeln erweitern

Anzahl Wörter: Mit wie vielen Wörtern kommen User auf meine Seite? Mit der Formel =WENN(LÄNGE(GLÄTTEN(J8))=0;0;LÄNGE(GLÄTTEN(J8))-LÄNGE(WECHSELN(J8;” “;””))+1) erhaltet ihr die Antwort.

Eine weitere praktische Information ist es herauszufinden, wie viele Besucher die URL erzielt hat und wie groß der Anteil für das jeweilige Keyword ist. Dazu legen wir folgende Spalten an:

=SUMMEWENN(Tabelle1!A:J;A8;Tabelle1!D:D)

=WENNFEHLER(SVERWEIS(A8;Tabelle1!A1:J22;4;FALSCH);” “)

img src=”https://www.prometeo.de/wp-content/uploads/2013/02/clip_image011.png”>

Nun das Ganze in einer dritten Spalte durcheinander Teilen:

=WENNFEHLER(M8/L8;” “)