Onpage Analyse mit Excel - Campixx 2014

SEO Onpage Optimierung mit Excel

Stephan Walcher

• Seit 2006 im Bereich SEO aktiv

• SEO in Südtirol mit Schwerpunkt Tourimus

• Ex-Bing und Inhouse SEO für MSN.de

• Seit 2011 bei Catbird Seat / Head of SEO Consulting

#Geek #Nerd #WindowsPhone #Windows8

Links:

Blog http://www.prometeo.de

Twitter http://twitter.com/stephanw

Follow me on Twitter: @StephanW

FIONA!

Mehr von Fiona auf:

http://instagram.com/fiona_cbs

SEO Onpage Optimierung mit Excel

Daten, Daten, Daten und … ach ja DATEN!

Daten sammeln

• Crawler

– SEO Screaming Frog

– Xenu

• Online Tools

– Google Analytics

– Google Webmaster Tools

– Bing Webmaster Tools

– Link Research Tools Reports

– Searchmetrics

– Sistrix

– Onpage.org

– SEOratio

– Strucr

– Ahrefs

– Majestic SEO

– Advanced Web Ranking Cloud

Nützliche URL basierte Datenquellen

Egal ob Social Interactions, Backlinks, interner PageRank, DomainPop usw. alles basiert auf URL.

URLs sind DER gemeinsame Nenner aller Tool Anbieter

Daten sammeln

• Umsonst

• Aktuelle Version vom September 2010

• Sehr schnell, auch bei großen Seiten

• Wenig Zusätzliche Informationen

• Generiert einfache URL Listen

• Ist im Grunde „nur“ ein 404 Broken Link Tool

Xenu‘s Link Sleuth

Daten sammeln

SEO Screaming Frog

• Für Windows, Mac oder Ubuntu verfügbar

• Basiert auf Java

• Gratisversion erlaubt max. 500 URLs zu crawlen - £99/Jahr ohne VAT

• Beliebig viele Crawls

• User-Agent frei definierbar

• Liefert zahlreiche Fehlerlisten

– 30X redirects

– 404/50X

– Duplicate Pages (Hash Wert)

– Meta Description (nicht vorhanden, zu lang, usw)

– H1/H2 (nicht vorhanden, zu lange, multiple)

– Canonicals (nicht vorhanden)

– Meta Robots (noindex, nofollow usw.)

– In- und Outlinks

– Anchor Text

– Weiterleitungsketten

– Usw.

Probe Crawl

• SessionIDs

• Tracking

• Custom Code

Vollständiger Scan

Data Matching

Analyse & Report

Daten sammeln

Crawling richtig gemacht

Experten TippBeim Crawl am besten immer prüfen ob Tracking

Codes oder IVW Pixel vorhanden sind

10Follow me on Twitter: @StephanW

Martin TauberContent Inventur mit dem SEO Screaming Frog

Heute: 11:45 Raum:

Daten sammeln - Export

EXPORT

Daten sammeln - Import

Beim Import immer UTF-8 verwenden

Daten sammeln - Output

Bei eine zu hohen Anzahl von URLs (max 1. Mio):

Nur HTMLs URLs exportieren

Data Matching

15Follow me on Twitter: @StephanW

Data Matching

• Ursprünglicher Name Microsoft Multiplan (Mac) - 1985

• Seit 1988 auch für Windows 2.x Runtime

• Aktuelle Version Excel 2013 (Windows) und Excel 2011 (Mac)

• Excel kann seit Excel 2007 mit mehr als 65.000 Zeilen umgehen

• Zahlreiche Erweiterungen und AddIns

– Power Pivot

– Bing Ads Intelligence

– SEO Toosl Niels Bosma

– ASAP Utilities

Microsoft Excel

http://de.excel-translator.de/infos/

Keine englischen Formeln im deutschen Excel

Data Matching – Excel Funktionen

• Wenn()– Gibt einen bestimmten Wert zurück, wenn die angegebene Bedingung WAHR ist

z.B. bei Ranking Positionen >> Top10 Ja/Nein

• SummWenn()– Summiert die Werte einer Spalte, wenn eine Bedingung eintrifft

z.B. Anzahl von Backlinks für ein bestimmtes Verzeichnis

• SummeWenns()– Summiert die Werte einer Spalte, wenn mehre Bedingungen eintreffen

• Sortieren– Sortiert die Werte in einer Spalte Auf- und Abwärts

z.B. sehr nützlich beim SVERWEIS()

• Gleichheitszeichen– Vergleicht zwei Zellen miteinander ob diese identisch sind

z.B. Canonical-tag Überprüfung

• UrlProperty()– Extrahiert Eigenschaften aus einer URL (SEO Tools for Excel)

z.B. Host oder Domain

Weitere wichtige und nützliche Excel Funktionen

SVERWEIS() vs. INDEX()

=SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich_Verweis)

• Sucht in der äußersten linken Spalte einer Tabelle nach einem Wert und gibt den entsprechenden Wert in einer anderen Spalte wieder

• Sverweis = SpaltenVERWEIS

• z.B. Crawl Daten (Tabelle1) mit Google Analytics (Tabelle2) matchen#– Suchkriterium: URL

– Matrix: Tabelle2

– SpaltenIndex: In Welcher Spalte stehen die Visits?

– Bereich:Verweis: „Falsch“ = Genauer Treffer

=INDEX(Matrix;Zeile;Spalte) + =VERGLEICH()

• Kann einen Wert in einer beliebigen Spalte suchen und einen Wert aus einer anderen Spalte wiedergeben.

• Sehr Rechenintensiv

• Daten matching ohne Spaltenverschiebung

=Wennfehler() vermeidet das hässliche #NA

Sverweis() vs. Index()

=SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich_Verweis)

=INDEX(Matrix;Zeile;Spalte) + =VERGLEICH()

=INDEX($A$1:$B$11;VERGLEICH(D2;$B$1:$B$11;0);VERGLEICH($E$1;$1:$1;0))

=WENNFEHLER(SVERWEIS(D2;A1:B6;2;FALSCH);"")

Sverweis – Gibt immer den ersten Wert wieder, der gefunden wird

=Verketten() s. Google Analytics Content Export

Excel Formeln im EinsatzLevel: Basic

Excel Formeln im Einsatz

Gleichheitszeichen

Gleichheitszeichen + Wenn-Funktion

=WENN(S2="";"kein Canonical";WENN(A2=S2;"Canonical auf sich selbst (unique)";"Canonical auf andere Seite (DC)"))

Wenn Canonical-Feld (S2) leer, dann schreib „Kein Canonical“. Wenn URL(A2)=Canonical(S2) dann schreib „Canonical auf sich selbst“.

Wenn nichts zutrifft „Canonical auf andere Seite“

UrlProperty()*

=UrlProperty(D2;"Domain")

*SEO Tools for Excel

Wörter zählen

=WENN(LÄNGE(GLÄTTEN(K6))=0;0;LÄNGE(GLÄTTEN(K6))-LÄNGE(WECHSELN(K6;" ";""))+1)

Excel Formeln im EinsatzLevel: Advanced

Excel Formeln im Einsatz - Advanced

Back to school - Mengenlehre

1012 2

A ∩ B

A B A B

Theorie: Man nehme zwei Listen - Alle URLs die durch Suchmaschinen Traffic erzeugt haben und URLs die durch interne Verlinkung (Crawl) gefunden worden sind. Die Differenz sind intern verwaiste URLs mit organischem Traffic-Potential.

Die Zutaten:

• 1x Google Analytics Export „organic Traffic“

• 1x Screaming Frog Export

• 1x Excel Arbeitsmappe

• 2x Excel Arbeitsmappe

• 1x Sverweis

Nun noch den Filter auf #NA setzen und die übrig gebliebenen URLs in die LRTs hauen und dort mit dem Link Juice Report nach Backlinks und SocialSignals auswerten.

Verwaiste Seiten finden

Tipp: Google Analytics URLs vorher noch bereinigen

Theorie: Alle URLs die organischen Traffic über Google Analytics vorweisen sind im Google Index enthalten. Matched man diese Liste von URLs mit einer XML-Sitemap, sind die URLs, die nicht in der Google Analytics Listen zu finden sind vermutlich auch nicht im Index und können überprüft werden.

Die Zutaten:

• 1x Google Analyse URL Export (Content)

• 1x Vollständige XML-Sitemap

• 1x Sverweis

Nicht indexierte URLs finden

Die Zutaten

- http://wordpress.org/plugins/wp-bots-analytics/

- Google Analyitcs Besucher

- 1x Sverweis

Korrelation zwischen Googlebot und Traffic

Googlebot Traffic kann auch durch „andere“ generiert werden

Die Zutaten

- 1x SEO Screaming Frog Export

- 1x Link Research Tool Link Juice Report

Mehr interne oder externe Links?

Die Anzahl der Backlinks ist nicht entscheidend, aber hilfreich

Excel Formeln im EinsatzLevel: Expert

Excel Formeln im Einsatz – Expert

Daten Analyse aus einem großen Sheet

1. Kontrolliere ob alle Sverweise() und Index() Formeln in Datenwerte umgewandelt sind.

a. Falls nicht: STRG + A (alles markieren) STRG + C (alles kopieren)

2. Achtet darauf, dass alle Spalten einzigartige Überschriften haben

3. Drückt nun STRG + A Tab „Einfügen“ PivotTable

4. Spalten Überschriften in die Felder ziehen

PivotTable erstellen

• Filter: hier können direkte Filter angelegt werden, die nicht in den PivotTabellenangezeigt werden.

• Legende: Sind die Spaltenwerte, achtet dabei immer auf die Anzahl sonst wird die Tabelle zu lang

• Achse: Zeilenbeschriftung

• ∑ Werte: Welche Werte sollen angezeigt werden. Anzahl, Summe, Produkt usw.

Pivot Felder

PivotChart

Liste doppelter <title> für Index relevante URLs

Klicklevel Verteilung für Index relevante URLs

Bei korrelierenden Daten:Zwei Achsen Darstellung

1. Keyword Recherche

2. Abfrage über Advanced Web Ranking (Proxie!?)

3. Sverweis

4. Sortieren nach Suchvolumen & Filter nach Pos. >10

Ranking Potentiale identifizieren

Seiten Netzwerk mit Cross Canonicals

Der Unterschied: Ein Feld mehr

Wichtige SEO Excel Regeln

1. URLs müssen 100% übereinstimmen

2. Bei multiplen Quellen entscheidet euch für eine Quelle Beim Aggregieren „Duplicate Entfernen“

3. Deaktiviert die automatische Berechnung und startet diese manuell (F9)

4. Vor JEDER Berechnung (F9) speichert vorher immer euer Sheet erhöhte Absturzgefahr

5. Transformiert einmal berechnete Daten in statische Daten „Als Daten einfügen“

6. Pivot ist entweder eine gute Grafik oder eine gute Tabelle

7. Listen für die IT

8. Grafiken für den Rest

9. Archiviert eure Daten Dumps Vergleichswerte

10. Sverweis ist immer ein EXACT Match, eine kleine Änderung kann viele #NA erzeugen

11. Dokumentiert jede Liste und jede Pivot was sie darstellt, in 1 Jahr werdet ihr dafür dankbar sein.

12. Wenn ihr verschachtelte Formeln erstellt, baut sie von Zelle zu Zelle auf schweres Bugfixing

13. Bereiche als Tabelle zu formatieren hat den Vorteil/Nachteil, dass neue Formeln automatisch in alle Zellen einer Spalte geschrieben werden

Und Tschüss!

Slide Download unter:

… oder auch direkt aufhttp://de.slideshare.net/StephanWalcher/

Bonus Runde

Google Suggest Scraping

1. Keyword in C2, Suchmaschine in C3

2. Markiere Zelle C5-C14

3. Trage die folgende Formel per copy&paste ein

=XPathOnUrl("http://"&$C$3&"/complete/search?output=toolbar&q="&UrlEncode($C$2);"/toplevel/completesuggestion/suggestion";"data")

4. Drücke STRG + SHIFT + Enter

5. Ob alles richtig steht, siehst du wenn die geschweifte Klammer steht

Bonus Runde

Google Index Checker*

=WENN(ISTFEHLER(IDENTISCH(TEIL(XPathOnUrl("http://www.google.de/search?gcx=c&sourceid=chrome&ie=UTF-8&q="&("site:"&(A8))&"&pws=0";"//li[@class='g']//h3[@class='r']//a";"href");8;LÄNGE(A8));A8));" ";"Indexiert")

*Benötigt: Excel SEO Tools - http://nielsbosma.se/projects/seotools/

Bonus Runde

1. Die Gewünschten Anzahl an Zellen markieren

2. Die folgende Formel per Copy & Paste eintragen (Ersetzt vorher die Blauen Bereiche durch euren API-Key und der gewünschten Domain)

3. =XPathOnUrl("http://api.sistrix.net/domain.sichtbarkeitsindex?api_key=[insert-API-key]&domain=http://spiegel.de&num=15";"response/answer/sichtbarkeitsindex";„value")

4. Drückt nun STRG + SHIFT + ENTER

5. Das Ganze sieht nun wie folgt aus: =XPathOnUrl("http://api.sistrix.net/domain.sichtbarkeitsindex?api_key=[insert-API-key]&domain=http://spiegel.de&num=15"; "response/answer/sichtbarkeitsindex";„value")}

Sistrix Sichtbarkeitskurve via API anrufen

Zwei identische Abfragen bei der einmal „Date“ und einmal „Value“ abgefragt wird

Sources

Onpage Analyse mit Excel - Campixx 2014

Technology

Transcript of Onpage Analyse mit Excel - Campixx 2014

SEO Campixx 2012 Local SEO Best Practices

Bilder Seo Trickkiste (Seo Campixx 2011)

SEO Campixx 2013 - Searchmetrics SEO & Content Marketing Blog€¦ · Interne Verlinkung SEO Campixx 2013 Holger Etzel 17.03.2013 16:00 – 16:45 Raum Seokratie

SEO Campixx Session - Sven Deutschländer & Michael Schöttler

SEO Campixx 2013 Workshop - Geschäftsmodelle im Internet

Content Audit | Campixx 2015 | Manuel Stürkat & Gustav Kemter

SEO Campixx - Word Press Professional

Onpage Optimierung - 10 unschlagbare Profi-Tipps

interne Verlinkung - SEO-Campixx 2013

SEO Onpage Optimierung mit Excel (SEO Campixx 2014)

OnPage SEO - Technik Grundlagen - Danny Linden

Campixx 2013: Linkbuilding - Wie misst man eigentlich Qualität?

Onpage SEO Analyse für · 2019-08-27 · Onpage SEO Analyse für Onpage Bereich: Gesamtexport Crawling vom: 28.10.2018 Report erstellt am: 28.10.2018 Gecrawlte Seiten: 175 Optimierungsverlauf

Leitfaden zur Offpage-Optimierung im Rahmen der ... · Suchmaschinenoptimierung in zwei Kategorien einteilen: Die Onpage-Optimierung und die Offpage-Optimierung. Die Onpage-Optimierung

Critical Rendering Path SEO Campixx 2015

Tool- und Mindset für große Seiten - Campixx 2012

Facebook vs. Google Trafficanalyse - Campixx 2011

Duplicate Content - SEO-Campixx Vortrag 2011

Präsentation campixx 2013

Trg mobile-performance-tuning-msi campixx-22022012_pub