Excel für SEOs

...is still a fool!A (F)Uhl With A Tool...

Wer spricht hier?

Markus UhlSEOdiot

Programmieren (seit 1984)

SEO (seit 2002)

Web-Entwickler (6 Jahre)

PHPPerl

Death Metal

E-Gitarre

Bass

8 Jahre SEO-Agenturen

SEM

Condé Nast Verlag

VOGUE

GLAMOUR

GQ

Zocken (PS3)

FUS RO DAH!!!

Linux-Server Admin

Inhouse SEO

Warum seid Ihr hier?

Neugier!

Daten beschaffenKeyword Recherche

Site Crawls

Backlinks

Besucher

Daten auswertenExcel-Funktionen

Pivot-Tabellen

Daten präsentierenDiagramme

?

Was könnt Ihr in 45 Minuten?

Beliebige CSV-Datenquellen sinnvoll miteinander verknüpfen

Aussagekräftige Listen und Tabellen erstellen (Reporting & Analyse)

Beeindruckend simple Grafiken (Management View) erstellen

Interaktives „Data Mining“

Daten beschaffenMan kann sinnvollere Sachen mit seinen Händen machen als Zahlen abzutippen

Keyword-RechercheWenn man mal wieder nach Worten ringt

Mein Keyword-Tool der Wahl

Erlaubt rekursive KW-Recherche ohne Dubletten

„Platzt“ auch bei 5.000 Keywords noch nicht

Kann taggen – später in Excel sehr nützlich

Ermittelt Suchvolumen, Wettbewerb, KEI, Platzierungen

Importiert Google Analytics Zahlen

Kann mit Proxies (Tor!) und Anticaptcha

Frei definierbare Ansichten (Spalten)

Einfacher Export jeder Ansicht per Copy&Paste

Tipp:Tool-ServerWindows-Server (ab 10€/Monat) mieten und dort die Tools per Remote Desktop bedienen.

Schützt Euer LAN vor Google-Blockaden.

Tipp 2: Dropbox!

Keywords kategorisierenUndankbare Handarbeit, aber mit Autofiltern auch bei großen Mengen relativ flott zu meistern.

Tipp: es muss nicht bei einer Kategorie bleiben. Denkt in Tags, z.B. Suchbegriffe nach Bildern, nach News, nach Infoblättern, nach Kaufabsicht, nach Preisinfos – in so vielen Spalten wie ihr möchtet.

Pivot-Tabelle erstellen

Alles auswählen (STRG-A)

Einfügen -> Pivot-Tabelle

Dialog einfach bestätigen

Über den Generator die gewünschte Ansicht zusammenstellen

Rumspielen!

ÜbersichtlichSchnelles Erfassen möglicher Themen für Artikel.

Kein Zerstören der Rohdaten!

Interaktiv – auf/zuklappen einzelner Bereiche: Fokus statt Reizüberflutung!

Same Data – Different ViewAuf einen Blick wird hier klar, für welche Themenbereiche die Website bereits rankt und für welche noch nicht.

(Sogar!) für Redakteure und Vorgesetzte intuitiv verständlich – weil ohne Zahlen...

Website-CrawlsAuch Du kannst sehen, was Googlebot sieht

Zwei Tools der Wahl

Screaming Frog SEO Spider

XENU Linksleuth

XENU LinksleuthKostenlos!

Ausreichende Features

Kann nicht mit Session-Ids etc. (kann aber Cookies -> Reiter „Advanced“)

CSV-Export über File -> Export to TAB separated File

Kann URL-Listen importieren (Wichtig: Maximum Depth auf 1 setzen, sonst rennt er weiter!)

Screaming Frog

Kostet was

Beherscht RegExes* zum Ausschluss von Parametern etc.(*RegExes: Reguläre Ausdrücke – siehe SEOdiotischer Campixx-Workshop 2013)

Verzeichnisweises crawlen

Zieht H1 und H2 mit raus

Zeigt canonicals

Zeigt index/follow bzw. noindex/follow etc.

Hat Custom Filter (z.B. „sind überall die GA-Codes verbaut?“ – leider noch nicht RegEx-fähig)

Bildet Hashes der Seiten -> DC-Erkennung

Sauberes Handling von URL-Listen

„Export“ per Copy&Paste in jeder Ansicht möglich – aber ohne Header

Problem: SpeicherhungerSowohl Xenu als auch Screaming Frog platzen gerne bei richtig großen Websites (>100-120k Seiten).

Abhilfe: Screaming Frog kann Unterverzeichnisse einzeln crawlen, die man anschließend zusammenfügen kann (Bedenke: Page Level +1!)

CSV Annoyances

Achtung: Umlaute!Bei Problemen: in Texteditor öffnen und Copy&Paste

KEIN Programm schert sich um die in CSV vorgesehenen Anführungszeichen, v.a. bei URLs -> Kommas in URLs trennen die Spalten falsch!(Nachbearbeitung über RegEx-fähigen Editor, z.B. Notepad++)

Evtl. werden unnötige Zeilen exportiert

Excel-Grundfunktion „Text in Spalten“

Text in Spalten

Gesamte Spalte markieren

Auf „Daten“-Ribbon wechseln

„Text in Spalten“ auswählen

Im folgenden Wizard das Trennzeichen auswählen

Vorschau kontrollieren

Fertig

Schnelle Pivot-Auswertungen

DC

Schnelle Pivot-Auswertungen

BacklinksEin Link ist ein Link ist ein Link...

Tool: Majestic SEOFür eigene Websites kostenlose Reports

„URL Level Advanced Report“ enthält alles, was man braucht

Saubere CSV-Exporte (im Report, „Download Options“ im Hauptmenü -> „Download Backlinks“

Metrik vorhanden (AC-Rank)

Schnelle Backlink-Pivots

BesucherWas hinten rauskommt...

Nutzt Analytics-Segmente!Jedes Feld ist komplett RegEx-fähig.Sehr nützlich z.B. bei falschen Schreibweisen des BrandsProblem: Sampling der Daten (auf Hinweis achten)

Nervig: Export-Limit

Im neuen Analytics wird genau das exportiert, was zu sehen ist, also max. 500 Zeilen

Trick: im alten Analytics den Link zum CSV-Export kopieren

In Adresszeile einfügen und &limit=20000 anfügen – Go!

Wozu Analytics-Daten in Excel?Weil die Ansicht Zielseiten / Keywords echt nichts aussagt...Ein Pivot dagegen schon!

Ähm - merkt Ihr was?

Der Typ labert schon 28 Folien lang...

...und noch KEINE EINZIGE Excel-Formel?!

Also:Dyskalkulie und Formelphobie sind keine Ausreden, langweilige Excels

abzuliefern!

...baut abgefahrene Tabellen und Diagramme

Excel-FormelnUm alles noch viel, viel geiler zu machen!

Formel-Basics

Formeln starten immer mit der Eingabe eines „=“

Formeln können direkt im Feld oder in der Eingabezeile eingegeben / editiert werden

Formeln sind immer in GROSSBUCHSTABEN

Deutsches Excel = deutsche Formeln; Englisches Excel = englische Formeln :o(

Während des Tippens macht Excel schon Vorschläge und zeigt Hilfe zu den Formel-Parametern an

Parameter werden mit „;“ voneinander getrennt

Formel-Basics

Manche Funktionen benötigen Bereiche, z.B. A1:A2, A1:E27, $A$1:$E$3... Kann wiederum eingegeben, mit dem Cursor (Shift-Pfeiltasten) oder der Maus ausgewählt werden. Man kann auch ganze Spalten oder Zeilen markieren.

Felder werden mit Ihren „X/Y-Koordinaten“ angegeben, z.B. A1 oder D5. Das nennt man auch einen „Bezug“

Diese Art nennt man „relative Bezüge“. Wenn die Formel um eine Zeile nach unten verschoben wird, ändern sich diese Bezüge automatisch zu A2 bzw. D6.

Feldnamen können eingetippt, mit den Cursortasten oder der Maus ausgewählt werden.

Für einen fixierten Bezug setzt man ein „$“-Zeichen vor X oder Y, z.B. $A1 (Buchstabe bleibt gleich), A$1 (Zahl bleibt gleich) oder $D$5 (ratet mal...)

Excel-GrundfunktionenAddition (=A1+B1), Subtraktion (=A1-B1), Multiplikation (=A1*B1), Division (=A1/B1)Datumsrechnen: „4.3.2012“ - „10.1.2012“ = 54 (Tage)Summieren von Bereichen: =SUMME(D5:D13)Zählen der Werte: =ANZAHL(D5:D13)Mittelwerte bilden: =MITTELWERT(D5:D13)Unschöne N/A Fehlermeldung: =WENNFEHLER(...;“Schöner Text“)

VERKETTENHängt beliebig viele Zeichenketten oder Zahlen aneinander

Kann Feldinhalte und vorgegebene Texte/Zahlen verketten

Praktisch z.B. zum Erzeugen von URLs mit Parameter

Einfach den variablen Teil durch Feldbezeichnung ersetzen

=VERKETTEN(„http://www.domain.com“; A1)

=VERKETTEN(„http://www.domain.com/suche/q=“; A1)

HYPERLINK

=HYPERLINK( Link_Adresse ; [Freundlicher_Name])

Verlinkt zu der als Argument angegebenen URL

Ein „Linktext“ kann optional als zweiter Parameter angegeben werden – damit der Link sprechend wird statt nur die URL anzuzeigen.

Verschachtelte Funktionen

Gerade HYPERLINK macht meist nur Sinn in Kombination mit VERKETTEN

Funktionen können beliebig tief ineinander verschachtelt werden

Nicht schön: wenn ein Fehler auftritt...

Tipp: Schritt für Schritt innere Funktionen erst in eigene Felder schreiben, bis sie das gewünschte Ergebnis zeigen und diese Felder dann in den äußeren verwenden. Erst wenn alles klappt, die einzelne Mörderformel per Copy&Paste zusammenbasteln (auf Bezüge achten!).

=HYPERLINK(VERKETTEN(„http://www.domain.de/suche?q=“ ; A1);„Website nach “ ; A1 ; „ durchsuchen“

)

TEIL: Gegenteil von Verketten

Zerlegen von Zeichenfolgen

Nützlich, um z.B. URLs aus dem Sitecrawl in Domain, Verzeichnisse und Endknoten zu zerlegen

=TEIL(Text ; Erstes_Zeichen ; Anzahl_Zeichen)

=TEIL(„http://www.domain.de“ ; 8 ; 13) => www.domain.de

=TEIL(„http://www.domain.de/index.html“ ; 21 ; 10)=> index.html

LÄNGE

Gibt die Länge einer Zeichenkette zurück.

Wird z.B. oft in Verbindung mit TEIL benötigt

=LÄNGE(Text)

=LÄNGE(„http://www.domain.de/index.html“) => 31

A1:= http://www.domain.de/index.html

=TEIL(A1 ; 8 ; LÄNGE(A1) – 8) => www.domain.de/index.html (http:// abgeschn.)

=TEIL(A1 ; 22 ; LÄNGE(A1) – 22) => index.html

FINDENGibt die erste Position eines gesuchten Zeichens (oder Zeichenkette) innerhalb einer Zeichenkette zurück

SEHR nützlich in Verbindung mit TEIL=FINDEN(Suchtext ; Text ; [Erstes_Zeichen] )

=FINDEN(„www“ ; „http://www.domain.de/index.html“) => 8

=FINDEN(„/“ ; A1 ; 8) => 21 (!)

=TEIL( A1;FINDEN("/“ ; A1 ; 8) + 1;LÄNGE( A1 ) - FINDEN("/“ ; A1 ; 8) – 1 (!)

) => index.html

Letztes Vorkommen finden

Leider keine einfache Funktion hierfür eingebaut

Google is your friend!

Nicht verzweifeln – Copy&Paste...

Beispiel: letztes „/“ in einer URL

http://www.domain.de/cat/subcat/index.html

=FINDEN("§";WECHSELN(A1;"/";"§";LÄNGE(A1)-LÄNGE(WECHSELN(A1;"/";))))

=> 32

=RECHTS(A1;LÄNGE(A1)-FINDEN(„§";WECHSELN(A1;“/";“§";LÄNGE(A1)-LÄNGE(WECHSELN(A1;“/";)))))

=> index.html

1. Verzeichnisebene ermitteln

Rekursion des FINDEN-Befehls, um die Anzahl auszuschneidender Zeichen für TEIL zu ermitteln

Praxisnutzen: mit Pivot-Tabelle Übersicht über die Verteilung der Inhalte und/oder schneller Vergleich mit Ergebnis von site:-Abfragen (Indexierungsstatus)

Geht natürlich auch mit zweiter, dritter etc. Ebene

http://www.domain.de/cat/subcat/index.html

=FINDEN("/“ ; A1 ; FINDEN( "/“ ; A1 ;8 ) + 1

) => 25

=TEIL(A1;FINDEN("/";A1;8);FINDEN("/";A1;FINDEN("/";A1;8)+1) - FINDEN("/";A1;8)

) => /cat

SUCHENWie FINDEN, aber unabhängig von Groß-/Kleinschreibung

Kann auch mit Platzhaltern arbeiten

„?“ ersetzt ein einzelnes Zeichen

„*“ ersetzt mehrere Zeichen

Markus Andreas Uhl

=FINDEN(„A“ ; A1) => 8=SUCHEN(„A“ ; A1) => 2=SUCHEN(„a?d“ ; A1) => 8=SUCHEN(„a*d“ ; A1) => 2=SUCHEN(„s*m“ ; A1) => #WERT! (Fehler -> WENNFEHLER!)

WENNIst eine Bedingung erfüllt, wird ein Wert ausgegeben

Ist eine Bedingung NICHT erfüllt, wird ein anderer Wert ausgegeben

=WENN( Prüfung ; Dann_Wert ; Sonst_Wert )

Automatisch Kategorisieren

Rekursion von WENN und FINDEN/SUCHEN erlaubt eine automatische Kategorisierung von Listen, z.B. Keywords

Sinnvoll allerdings nur, wenn wenige Werte abgefragt werden

Bier kaufen => KaufabsichtBier Preis => PreisinfoPreise Bier => Preisinfo

=WENN(SUCHEN(„kaufen“;A1);“Kaufabsicht“;WENN(SUCHEN(„preis“;A1);“Preisinfo“))

Profi-Methode: SVERWEIS

Mit SVERWEIS werden zwei Tabellen miteinander verknüpft

„Such den Wert in Zelle X innerhalb von Tabelle Y und gib mir dort den Wert, der daneben in Spalte Z steht“

Praxisbeispiel: ich habe aus der KW-Recherche eine Tabelle mit Keywords, deren Suchvolumen und Platzierung – und eine Tabelle aus Analytics mit den Keywords, deren Traffic und die platzierte Seite.Ich hätte aber gerne eine Übersicht, welche Seite mit welchen Keywords rankt, wie deren Suchvolumen ist und welchen Traffic ich darüber tatsächlich bekommen habe.

Ausgangstabellen

SVERWEIS einfügenDie Tabelle, in der man nachsieht, sollte aufsteigend sortiert sein, sonst kanns Fehler geben

Verweis auf andere Tabellenblätter:Tabellenname!ZeileSpalteoder: einfach mit der Maus auswählen...

=SVERWEIS( „onpage optimierung“ ; Analytics!A:B ; 2 ; FALSCH) => 169

=SVERWEIS( „hermes sendungsverfolgung“ ; Analytics!A:B ; 2 ; FALSCH) => 99

Tipp: gut benannt Tabellenblätter helfen, sich zurechtzufinden. Also keine „Blatt 1“, „Blatt 2“ etc. mehr stehen lassen!

Ergebnistabelle

Kombinierte Tabelle mit Werten aus beiden Quellen

„Zerstörungsfrei“ – Änderungen in der Nachschlagetabelle ändern sofort die Werte in Zieltabelle

#N/V heißt: Wert in Tabelle nicht gefunden – WENNFEHLER nutzen!

Und jetzt das geilste: daraus ein Pivot!

TabellenWir sprechen zwar immer von „Tabellen“ bei Excel, aber eigentlich sind echte Tabellen in Excel noch was ganz anderes...

Anlegen: Bereich markieren – Tabellen – Neu

Vorteile:Schönere Ansicht

Autofilter (Sortieren / Filtern) auf den Headern

Erste Zeile bleibt beim Scrollen stehen

Spalten (Formeln!) werden automatisch ausgefüllt – kein Aufziehen mit der Maus oder reinkopieren in alle Zeilen mehr

Kommen neue Zeilen hinzu, werden alle berechneten Spalten automatisch befüllt

Vorsicht: Speicherhungrig! Also Tabellenbereich so klein wie möglich halten. Nicht ganze Spalten auswählen sondern nur STRG-A drücken.

ZusammenfassungAllein mit Pivot-Tabellen ist schon Erstaunliches möglich

Zusammen mit SVERWEIS und einigen Hilfsformeln seid Ihr eine WELTMACHT!

Wir konnten nur die Oberfläche der Möglichkeiten von Excel ankratzen

Bei Fragen: Google ist Dein Freund!

Tipp für Agentur-SEOs: derartige Auswertungen und Visualisierungen sind bei Pitches DER BURNER!

Letzte WorteA (F)Uhl with a tool......still a fool?