Tweet

Excel pro SEO

Úvodem jak využít Excel pro SEO

Článek je z důvodu rozsáhlosti rozdělen do dvou části:

Část první

• Teoretické základy - Rychlý teoretický úvod nejen do SEO. A odpověď na otázku proč analýza v Excelu
• Kde a jak získat data
• Načtení dat - pokud již data máte jak je načíst
• Upravení dat pro další zpracování - teorie co budeme chtít z adresy získat
• Jak rozdělit webovou adresu - použití textových funkcí

Část druhá

Druhá část je již k dispozici Excel pro SEO (2) v té se mimo jiné obsahuje:

• Pokročilá úprava webových adres - pokud je tvar vašich adres složitější, nebo chcete souhrny
• Zpracovaní upravených dat - vyhledávací funkce (SVYHLEDAT, POSUN, ...) - podklady pro grafy
• Pokročilejší analýza pro SEO - kontingenční tabulky,
• Tvorba grafů - převedení zpracovaných dat do grafů - graf řekne mnohdy více než rozsáhlá tabulka - aneb to co bude zajímat šéfy, zákazníky
• Závěry doporučení

Na internetu existují články, jak využít Excel pro SEO. Pěkný článek Microsoft Excel for SEOs. Bohužel je v angličtině (což by nemusel být problém, základy angličtina snad umí všichni). Problémem je, že autor používají anglický Excel (tj. anglické názvy funkcí), které se liší od českých názvů. Dále existují odlišnosti v zápisu argumentu (středník - čárka, atd.). Což vidím jako větší problém k pochopení (navíc mi to potvrzují dotazy). Takže český Excel a české názvy funkcí.

Teoretické základy SEO

SEO (Search Engine Optimization - optimalizace internetových stránek pro internetové vyhledávače) je dnes skloňováno velice často. Působí jako magická formulka na šéfy firem (ředitelé, jednatelé). Každý kdo se kolem internetu trochu motá určitě ví o co jde. Pokud si nejste jisti doporučuji například: Wikipedii SEO.

Stejně tak lze Excel využít pro kontrolu PPC kampaní, správu sociálních síti (facebook), ...

Proč analyzovat a proč Excel na SEO?

Proč mimo webové rozhraní služby?

V Excelu se můžete do analýz a přehledu provést daleko více než umožňuje webová služba. Navíc i takové analýzy, které nejsou ve webovém prostředí k disozici.

Proč Excel?

Jednak jej většina ho má na svém PC nainstalován. Navíc se Microsoft Excel k analýzám přímo nabízí (jehož praktické využití bude dále popsáno) díky mnoha funkcím, které lze jednoduše použít.

Stejné operace můžete provádět i v OpenOffice nebo LibraOffice, kde by měli příklad bez problému fungovat (garanci ale dávat nemohu, testuji v Microsoft Excelu).

V článku je uvedeno jak zdrojová data získaná z různých služeb (majesticseo, google, ...), zpracovat do použitelné (stravitelné) podoby. Jaké strategie zvolit, jak v datech hledat a počítat klíčová slova, atd. Tento článek Vás naučí data zpracovat. Příprava správných otázek je na vás.

Co můžeme získat / vytvořit? Na co se ptát.

Co potřebujete z dat získat již musíte vědět na počátku, od toho se bude odvíjet použití (aplikace) dále uvedených funkcí. Například potřebujete zjistit:

• přehled o návštěvnosti na základě klíčových slov
• seznam stránek dle klíčových slov
• roztřídit stránky na základě tematických celků
• návštěvnost jednotlivých stránek, tematická návštěvnost (články o SEO, články o Excel, články o funkcích, ...)
• pozice (pagerank)
• z kontingenčních tabulek souhrnné přehledy (data podle zvolených kritérií - marketéři zajásají) - pokud přidáte náklady na jednotlivé kanály (PPC, affil., atd.) zjistíte další zajímavé informace
• jasné grafické přehledy - prezentovatelné zákazníkům, vedení, nadřízeným
• porovnávat data za jednotlivé měsíce (týdny) možná i dny (pokud neprovádíte)
• like, twitter, g+, porovnání zmínek na sociálních sítích
• porovnání měření několika služeb
• atd.

Poznámka: Google analytics zvládne hodně, ale pokud data "přetáhnete" do Excelu získáte další rozměr pohledu (dokud nezkusíte, neuvěříte). Například pro web o 100 stránkách už je vypovídací schopnost ohromná. Pro weby (eshopy), které mají 100.000 položek je analýza mimo webovské rozhraní nutností.

Klidně se vsadím, že Amazon, Mall, Azla podobné analýzu provádí (pokud ne oni tak firma, která se jim o SEO, PPC, stará).

Teorii máte za sebou a jdeme získat data pro zpracování.

Zpět na seznam kapitol o využití Excelu pro SEO .

Kde a jak získat data

Něco málo teorie máte za sebou a jdeme získávat data, které následně zpracujeme. Data můžete získat z různých služeb ať už on-line nebo off-line. V neposlední řadě lze využít logy.

On-line Webu zabývajících se měřením je celé řada:

• http://www.majesticseo.com/
• http://www.google.com/analytics/
• http://www.opensiteexplorer.org/
• http://www.google.com/webmasters/
• http://www.bing.com/webmaster/diagnostics/
• http://raventools.com/
• http://www.oncollabim.com
• atd.

Off-line (měření na vlastní doméně) možností je opět celá řada (včetně skriptu zdarma):

• http://www.php.jonweb.cz/skripty/statistika/
• http://awstats.sourceforge.net/
• http://www.webalizer.org/
• http://www.w3perl.com/
• atd.

Cílem tohoto článku není popisovat, jak jednotlivé služby fungují (registrace, nastavení, exporty). Určitě některou (některé) z nich používáte a předpokládám, že umíte vyexportovat naměřená data do *.xls nebo *.csv souboru (*.pdf, atd. jsou Vám k další analýze k nepotřebě), pokud nevíte jak doporučuji si přečíst nápovědu služby, kterou využíváte, pohledat na internetu jak z této služby získáte data v požadovaném formátu.

Pokud máte patřičný soubor (ve formátu *.csv nebo *.xls) můžete přejít na další kapitolu. Pokud ne doporučuji se podívat do nastavení a tento soubor si vyexportovat.

V další kapitole ukáži, jak tento soubor načtete do Excelu.

Zpět na seznam kapitol o využití Excelu pro SEO .

Načtení dat

Předpokládám, že předchozí krok se povedl a máte požadovaný soubor např. 2012-01-15-vas-web.xls nebo 2012-01-15-vas-web.csv

Trocha teorie o importu

Záleží na nastavení služby, jak data exportuje. Používají se různé oddělovače. Většinou středník (;), nebo čárka (,). Toto je vhodné vědět pro načítání. Což uvidíte/zjistíte dále.

Import

Z menu Soubor - Otevřít vyberete adresář, ve kterém je *.csv soubor umístěn.

Pokus *.csv soubor(y) nevidíte, musíte vybrat typ zobrazovaných souboru. Zvolíte Zobrazit *.csv. vyberete soubor a klik na Otevřít. Zobrazí se Průvodce importem textů (1/3) V prvním okně stačí klik na Další >.

V dalším okně Průvodce importem textů (2/3) nastavíte oddělovače. Většina statistických služeb je v angličtině proto jako oddělovač používá čárku (,) pokud budete mít vlastní službu na svém serveru může být oddělovač středník (;). Po zvolení již vidíte v Náhled dat jak data budou rozdělena. Pokud jste spokojeni, stačí klik na ikonu Další >.

V posledním okně Průvodce importem textů (3/3) je možno nastavit pro jednotlivé sloupce patřičný formát. Máte-li hotovo, stačí klik na Dokončit.

Pokud správně provedete, data se načtou a tabulka bude vypadat podobně.

Data máme načtena a teď můžeme k jejich úpravě pro následnou analýzu.

Zpět na seznam kapitol o využití Excelu pro SEO .

Upravení dat pro další zpracování

V této kapitole s dozvíte jak načtená data (internetové adresy) upravit pro další analýzy (získat požadované údaje). Jelikož existují různé tvary internetových adres na příkladech, ukáží jak importovanou adresu rozdělit na jednotlivé části. Nejprve se podíváme na možný tvar těchto adres:

Tvar internetových adres:

Jelikož nemohu znát všechny služby, co můžete používat na získaní statistiky návštěvnosti (a nejen návštěvnosti, třeba zpětné odkyzy, pozice na klíčová slova, pagerank, sranr, atd.) ani jaký tvar mají Vaše webové stránky, zde jsou možnosti různých tvarů webových adres. Vaše stránky do některé určitě zapadnou:

Domovská adresa:

• http://office.lasakovi.com/
• http://www.lasakovi.com/
• http://lasakovi.com/
• office.lasakovi.com/
• www.lasakovi.com/
• excel.lasakovi.com/
• word.lasakovi.com/
• lasakovi.com/

Stránky a podstránky - jak jsou tyto stránky, podstránky, produkty odkazovány:

• excel/funkce/svyhledat/ - tj. celá adresa např. http://office.lasakovi.com/excel/funkce/svyhledat/
• index.php?abcd=ddd
• index.html?clanek=PokusnyClanek
• o-nas.asp
• pokus/funkce.php
• pokus/funkce.php?clanek=PokusnyClanek
• view.php?cisloclanku=2013011303
• zlata-ryze-fry-/veda.aspx?c=A130111_185632_veda_mla
• kurzovni-listek.aspx?typ=banky&mena=EUR
• ultrabook-dell-inspiron-14z-stribrna_19/11507/produkt?q-category- id=23c6vhcneoij4acpii0gi7fp8

Co od adres potřebujeme

Jak webové adrsy vypadají víte, ale co dále. Protože o SEO něco víte, potřebujete rozdělit adresu na jednotlivé sloupce, kde každý bude obsahovat patřičné údaje. Viz následující ukázka:

Adresu: http://office.lasakovi.com/excel/funkce/svyhledat/ - budete chtít rozdělit na:

• office
• excel
• funkce
• svyhledat

Adresu: http://zpravy.idnes.cz/zlata-ryze-fry-/veda.aspx? c=A130111_185632_veda_mla - budete chtít rozdělit na:

• zpravy
• -fry-
• veda
• A130111_185632
• veda_mla

Adresu: http://www.novinky.cz/krimi/290063-ridic-vystoupil.html - budete chtít rozdělit na:

• krimi
• 290063
• ridic-vystoupil

Adresu: http://seo.seo-seo-seo-seo.cz/seo-expert.html - budete chtít zjistit počet výskytu klíčového slova. Tj. kolikrát se v ukázce objevuje seo.

Zpět na seznam kapitol o využití Excelu pro SEO .

Jak rozdělit webovou adresu

Prakticky - text do sloupců

Toto je nejjednodušší možnost. Z karty Data sekce Datové nástroje ikona Text do sloupců. Bohužel pro naši práci se příliš nehodí, proto se touto možností nebudu v dalším textu zabívat.

Textové funkce

V následujícím textu předpokládám teoretickou znalost textových funkcí. Chcete-li se seznámit se syntaxí textových funkcí doporučuji článek: Funkce textové přehled + praktické příklady.

Pokud máte teoretické základy můžeme dále.

Praktický úkol + řešení (jednoduchý)

Cíl adresu: http://seo.example.com/umime - budete chtít rozdělit na:

• seo
• umime

Použijeme funkce ZPRAVA, ZLEVA, HLEDAT, DÉLKA

=ZPRAVA(A12;DÉLKA(A12)-7) =ZLEVA(B12;HLEDAT(".";B12;1)-1) =ZPRAVA(B12;DÉLKA(B12)-HLEDAT("/";B12;1))

Soubor SEO - pro Excel příklady (1) ke stažení zdarma pro verzi Excelu 2010 (2007). Nebo verze pro Excel 2003 (97) SEO - pro Excel příklady (1)

Počet výskytu klíčového slova

Kolikrát se určité slovo vyskytuje ve webové adrese. Určitě bude hodit.

Máte například tuto adresu: seo.seo-super-expert-seo-seo.cz/seo-expert-seo.html a potřebujete zjistit počet výskytu těchto slov:

• seo
• expert
• super

Použijeme funkce DOSADIT, DÉLKA

=(DÉLKA(A9)-DÉLKA(DOSADIT(A9;B9;"")))/DÉLKA(B9)

Princip - Funkce DOSADIT nalezené slovo nahradí prázdným znakem. Sečtou se znaky DÉLKA původní a nové webové adresy. Rozdíl se vydělí počtem znaku hledaného slova a máte výsledek.

Poznámka: Kdo má zájem může si vytvořit vlastní funkcí ve VBA, která počet slov spočte automaticky.

Soubor SEO - pro Excel příklady (2) ke stažení zdarma pro verzi Excelu 2010 (2007). Nebo verze pro Excel 2003 (97) SEO - pro Excel příklady (2)

Složitější příklady

Máte adresu: http://zpravy.idnes.cz/zlata-ryze-fry-/veda.aspx?c=A130111_185632_veda_mla a potřebujete jí rozdělit na:

• zpravy
• -fry-
• veda
• A130111_185632
• veda_mla

Toto jsou již trochu delší vzorečky...

Jeden:

=ZLEVA(ZPRAVA(ZPRAVA(ZPRAVA(A44;DÉLKA(A44)-7);DÉLKA(ZPRAVA(A44;DÉLKA(A44)-7))-DÉLKA(ZLEVA(A44;HLEDAT("-/";ZPRAVA(A44;DÉLKA(A44)-7);1)))-1);DÉLKA(ZPRAVA(ZPRAVA(A44;DÉLKA(A44)-7);DÉLKA(ZPRAVA(A44;DÉLKA(A44)-7))-DÉLKA(ZLEVA(A44;HLEDAT("-/";ZPRAVA(A44;DÉLKA(A44)-7);1)))-1))-HLEDAT("=";ZPRAVA(ZPRAVA(A44;DÉLKA(A44)-7);DÉLKA(ZPRAVA(A44;DÉLKA(A44)-7))-DÉLKA(ZLEVA(A44;HLEDAT("-/";ZPRAVA(A44;DÉLKA(A44)-7);1)))-1);1));14)

Druhý:

=ZLEVA(ZLEVA(ZPRAVA(A44;DÉLKA(A44)-7);HLEDAT("/";ZPRAVA(A44;DÉLKA(A44)-7);1)-1);HLEDAT(".";ZLEVA(ZPRAVA(A44;DÉLKA(A44)-7);HLEDAT("/";ZPRAVA(A44;DÉLKA(A44)-7);1)-1);1)-1)

Klidně využijte sloupce na "meziadresy!. Tím se zápis zjednodužší, například...

Pomocné mezisloupce:

=ZPRAVA(A13;DÉLKA(A13)-7) =ZLEVA(P13;HLEDAT("/";P13;1)-1) =ZLEVA(P13;HLEDAT("-/";P13;1)) =ZPRAVA(R13;DÉLKA(R13)-DÉLKA(Q13)-1)

Vlastní sloupce:

=ZLEVA(Q13;HLEDAT(".";Q13;1)-1) =ZPRAVA(R13;5) =ZLEVA(T13;HLEDAT(".";T13;1)-1) =ZLEVA(U13;14) =ZPRAVA(U13;DÉLKA(U13)-DÉLKA(Z13)-1)

Ke stažení

Než zdlouhavý popis, doporučuji si stáhnout ukázku, kde si vše můžete prohlédnout živě přímo v Microsoft Excelu.

Soubor SEO - pro Excel příklady (3) ke stažení zdarma pro verzi Excelu 2010 (2007). Nebo verze pro Excel 2003 (97) SEO - pro Excel příklady (3)

Doporučuji v případě rozdělování složitější webové adresy vytvořit pomocné sloupce. Zpřehlední (zjednodužší) se vzorce/funkce a následná úprava či hledání chyb bude snadnější a rychlejší.

Jednotlivá slova pak získáte takto:

Potřebuji dostat text z html odkazu

Mám k dispozivi toto:

<a href="http://nejakyweb.cz?ID=123456" target="_blank">123456</a>

Potřebuji získat text mezi > < tj. číslo 123456.

Pomocí funkce NAJÍT zjistit pozici prvního znaku lomítka =NAJÍT(">";A1), a taktéž druhého lomítka (pozor toto lomítko je v textu 2x, tak ať vám funkce nenejde to první ;) ) NAJÍT("<";A1;2), pak jen pomocí funkce ČÁST odmažete nepotřebné (to že se doplní +1 a -1 je jasné, text potřebujete be těch znamének ;) =ČÁST(A1;NAJÍT(">";A1)+1;NAJÍT("<";A1;2)-NAJÍT(">";A1)-1)

Poznámka ke kapitole

Další příklady doplním v případě dotazů.

Závěrem

Pokud jste dočetli až můžete pokročovat na druhou část Excel pro SEO (2). Případně se v komentářích zmínit, zda k analýze SEO využívate Microsoft Excel, případně vám postačí jen výstupy příslušných webových služeb.

Článek byl aktualizován: 19.09.2020 11:02

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.