Seriál Marketingová strategie | Vytváříme přehledy skupin pomocí Excelu

Nástroje používané v tomto díle

  1. Open Refine
  2. Microsoft Excel (kontingeční tabulky jistě půjdou vytvářet i v Google Docs, případně v Open Office)

V minulém díle seriálu jsme se naučili, jak je možné rozdělit slova do širších skupin. Následně budeme pracovat se skupinami samostatně a dále je třídit.

Představení kontingečních tabulek a export projektu z Open Refine

Ještě před dalšími operacemi si ukážeme, jak data souhrně zobrazit pomocí kontingenční tabulky (Pivot table) v Excelu. Pomocí kontingeční tabulky chceme porovnat součet hledanosti námi vytvořených skupin, zprůměrovat ceny za proklik v rámci skupiny a zobrazit počet slov ve skupině. Díky tomu budeme mít představu, jak silný je zájem o různé typy dotazů, jaká je jejich konkurence a jak dlouhý je “long tail”.

Export našeho projektu

  1. Pomocí tlačítka “Reset All” u našich Facetů vypneme veškeré filtry aplikované na naše data. Open Refine exportuje pouze řádky, s kterými pracujeme (je možné vidět v hlavičce). V případě aplikace filtru bychom exportovali pouze část tabulky.
  2. V pravém hornímu rohu rozbalíme tlačítko Export > Excel.

Aplikace kontingenční tabulky na vytvořené skupiny

Zde popsaný postup vytvoření kontingeční tabulky platí pro Microsoft Excel 2013. K vytvoření tabulky je samozřejmě možné použít i starší verze programu, proces se však může mírně lišit.

  1. Najdeme stažený soubor a otevřeme jej v Excelu.
  2. Pomocí klávesové zkratky CTR + A si označíme celou naši tabulku (případně označíme myší).
  3. Přejdeme na kartu Vložení > Kontingenční tabulka.
  4. Dialogové okno se nás zeptá, z jaké oblasti chceme tabulku vytvořit. V případně správně označené oblasti stačí okno pouze potvrdit tlačítkem “OK”.
  5. Na novém listu se objeví náhled naší (prozatím prázdné) kontingenční tabulky spolu s postranním panelem pro její vytvoření.
  6. Z “Pole kontingenční tabulky” přesuneme “Co” do oblasti “Řádky”.
  7. Hledanost si z “Pole kontingenční tabulky” přesuneme do vedlejší oblasti “Hodnoty”. Kliknutím na “Hledanost” rozbalíme “Nastavení polí hodnot”, kde v oblasti “Kritéria pro shrnutí hodnot pole” zvolíme  “Součet”.
  8. “CPC” přesuneme do stejné oblasti jako “Hledanost”. Opět klikem rozbalíme nabídku a zvolíme “Průměr”. Pro přehlednější hodnoty je možné nastavit formát sloupečku na jedno desetinné místo.
  9. Proces opakujeme pro “Klíčové slovo”, které rovněž přesuneme do oblasti “Hodnoty”. V Nastavení polí hodnot zvolíme funkci Počet. Excel nám tedy sečte počet slov v námi vytvořených skupinách.
  10. Tabulku máme nyní řazenou podle sloupce “Co”. Po rozbalení voleb u prvního sloupečku můžeme zvolit “Další možnosti řazení” > Seřadit sestupně podle > Vybereme sloupeček “Hledanost”.

Tabulka hledanosti jednotlivých skupin

Video s přípravou kontingenční tabulky

Co získáme kontingenční tabulkou

V případě správné aplikace postupu máme nyní k dispozici zajímavý přehled. Víme, jak hledané jsou námi vytvořené skupiny dotazů, jak velká je konkurence v oboru (odvodíme pomocí průměrné CPC) a kolik klíčových slov skupiny obsahují.

Vizualizace dat

Počet slov v dotazu

V naší tabulce se některých případech objevil velmi zajímavý údaj o počtu slov v hledaném výrazu. Můžeme tak pochopit, jak se chovají uživatelé při hledání v našem odvětví.

Jak na to

  1. Vzoreček aplikujeme v prostředí Open Refine přes volbu Edit column > Add column based on this column.
  2. Vložíme pod jazykem GREL
  3. value.split(“,”).length()
  4. Sloupec si nazveme “Počet slov”.
  5. Projekt stáhneme z Open Refine pomocí již popsaného postupu.
  6. Vytvoříme si kontingenční tabulku, kde v oblasti “Řádky” bude “Počet slov”.
  7. Do oblasti “Hodnoty” si přesuneme rovněž “Počet slov”, klikem rozbalíme “Nastavení polí hodnot” a změníme kritérium na “Počet”.
  8. Můžeme si pro zajímavost zobrazit i hledanost jednotlivých délek dotazů. Uděláme to tak, že do sekce “Hodnoty” přetáhneme “Hledanost”. Je důležité nastavit kritérium na “Součet”.

Z tabulky je zřejmé, že uživatelé nejvíce zadávají dotazy obsahující 4 slova. Čtyřslovné výrazy mají zároveň největší hledanost. Pokud bychom svoji práci v SEO soustředili na krátké vyhledávací dotazy (například maximálně tříslovné), přišli bychom o 200 000 možných zobrazení. Zároveň vidíme, že dotazy obsahující 6 slov jsou velmi vzácné.

Shrnutí

V tomto díle naší série jsme se společně naučili, jak najít v našich skupinách užitečné informace o jejich hledanosti, konkurenci v PPC kampaních a síle “long tailu”.

V příštím článku se podíváme na další rozdělení klíčových slov do podskupin. Ukážeme si další zajímavé triky, například jak rychle vybrat klíčová slova obsahující města, jak třídit vylučující slova a jak dále pracovat s daty.

Výsledkem dalšího článku bude již hotová analýza klíčových slov. Poté se společně podíváme na její aplikaci pro náš web z pohledu struktury, obsahu, linkbuildingu, přípravy PPC kampaní a aplikace frameworku “See, think, Do, Care”.