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

✍️ Jakub Kašparů
📅 07. 12. 2014

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”.

6 comments on “Seriál Marketingová strategie | Vytváříme přehledy skupin pomocí Excelu”

  1. Dobrý den,

    výborný seriál, můžu jen chválit. Vím, že se blíží svátky, nicméně bych se rád zeptal kdy se můžeme těšit na pokračování?

  2. Dobrý den,

    jsem začátečník, a proto možná položím špatnou otázku.
    Pokud budu postupovat dle tohoto manuálu, dostanu ve finále klíčová slova, která budu moci použít do sestavy přímo v skliku? Hodí se pro toto použití tato analýza?

    Díky za odpověď.

  3. Tomáš Kopecký
    Děkuji. Ano mám připravené poslední 2 části, které se týkají samotné analýzy pomocí Open Refine a jejího vyhodnocení. Jakmile moje články projde editor, zveřejníme je.

  4. Stanislav:
    Dobrý den. Ano jakmile doděláte analýzu a opravdu roztřídíte data do skupin, získáte sestavičky do PPC kampaní. Je samozřejmě nutné rozhodnout, které sestavy spustíte v PPC kampaních.

    SEO a PPC má rozdílný přístup ke klíčových slovům. U PPC za každý proklik platíte, nechcete se tedy zobrazovat na některé nekomerční fráze. V našem případě se může jednat o dotazy týkající se adres na jednotlivé soudy.

    Naopak v SEO za proklik neplatíte vyhledávači. Naopak návštěvu na nekomerční téma vítáte. Daný návštěvník může od Vás služby objednat později, případně alespoň prezentujete Vaši značku.

    Při návrhu kampaně by měl primární roli hrát rozpočet. Díky analýze můžete zjistit průměrné ceny za proklik (a tedy i konkurenci v daných dotazech).

    Jestli mohu doporučit, tak hodnoťte hlavně obchodní potenciál dotazu. Pokud někdo dotazem mezi řádky říká, teď hned potřebuji právníka, klidně může být cena za proklik dražší. Úspěšnost obchodu u takového slova může být několikrát vyšší.

  5. Dobrý den, mám dotaz ohledně počtu slov dotazu. Při zadání vzorce: value.split(",").length(), se mi u všech výrazů vypíše 1. Můžete mi poradit?
    Děkuji,

  6. Iveta: Vím, že to už není aktuální, ale přidávám odpověď, pro ty, kteří na tento tutorial narazí později.

    Ve vzorci je chyba, nemá tam být jako oddělovač čárka, ale mezera.
    Takže správný výraz je:
    value.split(" ").length()

Napsat komentář: Jakub Kašparů Zrušit odpověď na komentář

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *


Lynt services s.r.o

Již 11 let vytváříme efektivnější kampaně, zrychlujeme weby a řešíme jejich bezpečnost. Kombinujeme marketing, vývoj a automatizaci.
poptávka služeb