Metody pracy z danymi w Excelu - ściąga dla SEOwca

Jak znaleźć igłę w stogu siana?

Uporządkowanie procesu zbierania i opracowywania statystyki SEO za pomocą ogólnodostępnych środków nie zawsze się udaje. Ponadto trudno określić analizę danych jako specjalnie ciekawe zajęcie: zazwyczaj to monotonna, męcząca i nudna praca, która przypomina poszukiwanie igły w stogu siana. Jednakże nie należy wpadać w depresję po pierwszym rzucie oka na kolumny niewiele mówiących cyfr: jeśli właściwie podejść do problemu, zadania związane z badaniem danych można łatwo automatyzować.

Jedną z takich możliwości, którą chcemy wam przedstawić, jest wykorzystywanie specjalnej formuły, która pozwala grupować dane w tablicach Excel w niezupełnie typowy sposób. Takie podejście pozwala na odnalezienie wariantów grupowania danych. Konieczny jest pewien czas, aby nauczyć się pracy z formułą i zapamiętać ją. Jednakże taki wysiłek z pewnością przyniesie rezultaty. Ogólny widok formuły z wykorzystaniem funkcji: JEŻELI (IF) i CZY.LICZBA (ISNUMBER) wygląda tak:

=if(isnumber(search(“string 1”, [beginning cell])),”Category 1”, if(isnumber(search(“string 2”, [beginning cell])),”Category 2”, “Other”)

Uwaga: funkcja CZY.LICZBA() w MS EXCEL umożliwia sprawdzenie, czy zawartość komórek to liczby, czy też nie. W naszym artykule będzie stosować warianty formuł w języku angielskim, jako że są one bardziej uniwersalne.

Przykład 1. Analiza słów kluczowych

Jeśli proces zbierania i analizy semantycznej dla serwisu jest przeprowadzany ręcznie, to specjalista jest zmuszony ręcznie przeglądać i sprawdzać listy, składające się z setek, a nawet tysięcy słów kluczowych. Formuła pozwala uprościć pracę z listą za pomocą grupowania słów kluczowych o wysokim stopniu podobieństwa. Dla zbierania danych semantycznych w tym przypadku można wykorzystać dowolne instrumenty. Na przykład, serwisy, takie jak SEMstorm czy nasze, SeoPilotowe narzędzie doboru fraz kluczowych. Nie należy także zapominać o planerze słów kluczowych w Google Adwords, z którego pomocą można ocenić ilość wyszukań dla każdego z słów kluczowych.

Po zebraniu semantyki, należy stworzyć plik CSV i załadować do niego rezultaty dla dalszego opracowywania. To pozwoli na ocenienie efektywności słów kluczowych z istniejącego spisu i przejrzeć dane dotyczące ilości wyszukań. Ponadto można połączyć frazy kluczowe w grupy. Osoby, które znają działanie planera słów kluczowych Google AdWords, wiedzą, że raport zawiera specjalną kolumnę - grupy ogłoszeń. Jednak nie jest to panaceum na wszystkie problemy: dane raportu bez dodatkowego opracowania nie zawsze są przydatne dla późniejszego użycia.

Problem w tym, że rezultaty często zależą od sensownego rozdzielenia słów kluczowych w grupach. O ile część słów kluczowych obowiązkowo powinny znajdować się w tworzonej frazie, o tyle inne służą jedynie jako swojego rodzaju “podpowiedzi” przy podborze nowych, bardziej aktualnych fraz. Na rysunku powyżej pokazaliśmy wybór fraz kluczowych dla zapytań, związanych z tematyką odżywek dla sportowców, w szczególności z frazą kluczową workout supplements.

W oryginalnym spisie ilość wierszy wynosi 681. Specjalista musi dowiedzieć się, jaka ilość zapytań zawiera słowo kluczowe supplement, a w jakich zapytaniach obecne jest słowo muscle.

Pierwsza czynność do wykonania, to usunięcie kolumny A (grupy ogłoszeń), jako że w tym wypadku jest ona dla nas absolutnie niepotrzebna. W kolejnym kroku na prawo od kolumny fraz kluczowych można dodać kolumnę Kategoria. W późniejszym czasie, wykorzystując formułę, można nadać frazom kategorie supplement i muscle. W tym celu do komórki C2 wpisujemy poniższą formułę:

=if(isnumber(search(“supplement”,A2)),”Supplement”, if(isnumber(search(“muscle”,A2)),”Muscle”,”Other”))

Słowny opis formuły będzie następujący: Jeśli w komórce A2 zostało znalezione słowo kluczowe “supplement”, to dla wiersza zapytania powinna zostać dodana kategoria Supplement. Jeśli słowo “supplement nie zostało znalezione, to należy szukać słowa kluczowego “muscle”, a w przypadku jego znalezienia - nadać wierszowi kategorię Muscle. Jeśli ani jedno ze wspomnianych słów nie zostało znalezione, wierszowi należy nadać kategorię Other.

Analogicznie, można dodawać do formuły również inne kategorie, według których chcemy rozdzielić zapytania. Jednocześnie, do kategorii Other (Inne) za każdym razem będą dodawane zapytania, które nie weszły do żadnej z określonych kategorii.

Zastosowanie formuły dla całego zbioru danych pozwala łatwo podzielić słowa kluczowe na kategorie, zwalniając specjalistę od konieczności wykonywania tej czynności ręcznie. Podwójne kliknięcie w prawym dolnym rogu komórki C2 pozwoli na zastosowanie formuły do wszystkich komórek w kolumnie C, którym nadano znaczenie liczbowe w komórce B. Na screenshocie poniżej pokazaliśmy rezultat zastosowania formuły w stosunku do wszystkich kategorii zawierających dane liczbowe. W tym wypadku należy zwrócić uwagę na to, jak użycie formuły zmieniło rezultaty w komórce C19. Na to wpłynęła analiza danych w komórkach A2 i A19.

Jak widać w tabeli, dla słowa muscle początkowo nie była nadana kategoria, jednakże w późniejszym czasie słowo kluczowe zostało dodane do kategorii Other. Jednocześnie kategoria nie jest nadawana pojedynczej komórce, a całemu wierszowi.

Jeśli jednak fraza kluczowa zajmuje w tablicy więcej, niż jedną linijkę, formuła nada kategorię dla każdego z tych wierszy. Jednakże specjalista powinien zwracać uwagę na ten wiersz, w którym zostanie wykryte pierwsze dopasowanie. Jako przykład - zwróćmy uwagę na wiersze 28 i 29 na rysunku. W tym przypadku dla frazy best pre workout (wiersz 28) i muscle supplements (wiersz 29) powinna zostać nadana kategoria Supplement.

Jeśli jednak w komórce z treścią frazy słowa supplement i muscle nie będą się pojawiać, formuła nada wierszowi kategorię Other.

W kolejnym kroku można za pomocą formuły dokonywać filtrowania wewnątrz kategorii Other, stopniowo wgłębiając się w badanie fraz:

Poniżej zamieszczamy screenshot tabeli, w której zawarto spis fraz kluczowych, które zawierają słowo kluczowe protein. Jak widać, procentowa ilość takich fraz w ogólnej ich liczbie jest wystarczające dla tego, aby wydzielić wszystkie podobne frazy w odrębną kategorię. Następnie można stworzyć tabelę zbiorczą i posegregować frazy na podstawie łącznej ilości wyszukań, oraz zastosowania w nich poszczególnych słów.

Analiza ostatecznych rezultatów pozwala na otrzymanie pełnej informacji o częstotliwości użycia słów kluczowych w interesujących słowach kluczowych. Możliwe jest opracowanie kompleksowej strategii pozycjonowania serwisu. Należy także podkreślić, że w tym przypadku grupa fraz kluczowych w kategorii Other, okaże się najliczniejsza. W późniejszym okresie można będzie ją podzielić na mniejsze podkategorie tematyczne, stosując opisaną wyżej metodykę.

Przykład 2. Wyszukiwanie linków niskiej jakości (spam)

Google nie przestaje straszyć webmasterów i właścicieli stron internetowych zbliżającym się, kolejnym updatem algorytmu Pingwin, który karze serwisy za linki o niskiej jakości, które mogą obniżać pozycje serwisów. Niżej przedstawiona formuła może zostać wykorzystana (między innymi) do oczyszczenia profilu linkowego serwisu. Efektywną metodą może być wyszukiwanie spamerskich linków dla ich późniejszego zgłoszenia za pomocą Disavow. W tym celu wartą uwagi ideą byłoby przeprowadzenie choćby pobieżnego audytu profilu linkowego serwisu. Jednakże należy brać pod uwagę realia - w warunkach ciągłego “niedoczasu” SEO specjalisty, wykorzystując formułę, może znacznie uprościć sobie proces poszukiwania stron, na których rozmieszczane są linki-spam.

W zależności od rozmiarów serwisu, jego profil może składać się z kilku tysięcy lub nawet kilku milionów linków, opublikowanych na całkowicie różnych domenach. Aby “przesiać” wątpliwe linki na poziomie domeny, można wykorzystać odpowiednie instrumenty, które oferują różne serwisy analityczne, takie jak Majestic czy Ahrefs. Można jednak wybrać inny sposób działania: znaleźć i posegregować linki, które zawierają w anchorach (tekstach linku) podejrzane słowa kluczowe, takie jak darmowy, katalog  itd  wykorzystując do tego formułę. Jej zastosowanie pozwoli na odnalezienie w spisie stron linkujących takiego rodzaju odnośników, które można później zneutralizować. W tym przypadku formuła będzie nieco bardziej złożona od poprzedniej, jednak zasada jej budowy jest taki sam, jak w przypadku fraz kluczowych.

=IF(ISNUMBER(SEARCH(«submit»,A2)),»Spam»,IF(ISNUMBER(SEARCH(«seo»,A2)),»Spam»,

IF(ISNUMBER(SEARCH(«directory»,A2)),»Spam»,IF(ISNUMBER(SEARCH(«free»,A2)),»Spam»,

IF(ISNUMBER(SEARCH(«drugs»,A2)),»Spam»,IF(ISNUMBER(SEARCH(«articles»,A2)),»Spam»,

IF(ISNUMBER(SEARCH(«.xyz»,A2)),»Spam»,»Other»

Na poniższej grafice pokazujemy jeden z wariantów wykorzystania tej formuły dla wyszukiwania spamerskich linków z następującymi słowami w tekście zakotwiczenia (anchorze) - submit, directory, free, drugs, articles, .xyz, którym w efekcie nadawana jest kategoria Spam.

Jak widać na tym przykładzie, w niektórych przypadkach oznaczenie Spam zostało przypisane do linków z całkowicie jakościowych domen. Oznacza to, że formuła nie daje stuprocentowej gwarancji w kwestii znajdowania takich  linków, a otrzymane rezultaty należy dodatkowo, ręcznie, sprawdzić. Pomimo tego jej zastosowanie znacznie ułatwia zadanie optymizatora w zakresie analizy linków SEO.

Przykład 3. Działania analityczne

Jeszcze jedna świetna opcja wykorzystania formuły - to praca z danymi z systemu Google Analytics. Przykładowo, można obrazowo przedstawić klientowi, z jakich źródeł na jego serwis przychodzi ruch organiczny. Formuła pozwala analizować dane dla dużej ilości podstron, co daje możliwość zrozumieć, na które z nich przychodzi największa ilość odwiedzających.

Poza odwiedzeniami i konwersją, za pomocą formuły można zbadać także wszelkie inne wskaźniki. Dla najbardziej efektywnego użycia formuły serwis powinien mieć taką strukturę: example.com/blog/article-1; example.com/supplements/product-1 lub example.com/toys/gadget-1. To pozwoli łatwo wybrać parametry dla wyszukiwania dowolnych, interesujących wartości. Na przykład: można przypisać komórkom różnorodne kategorie.

Na poniższym screenshocie pokazujemy, jak za pomocą formuły rozdzielić dane statystyczne z Google Search Console, związane z odwiedzinami na podstronach, na kategorie:

• «Porównanie towarów»;
• «Recenzje»;
• «Podobne towary»;
• «Inne».

Wykresy, stworzone na podstawie danych z skonsolidowanych tabel, będą zrozumiałe dla klientów i nie stworzą problemów przy obliczaniu.

Podsumowanie

Podział danych na kategorie raczej nie rozwiąże wszystkich problemów, związanych z analityką i pozycjonowaniem. Jednak zastosowanie takiego podejścia może znacznie przyśpieszyć działania i sprawić, że zbieranie informacji statystycznych będzie bardziej przejrzyste. Im szybciej zostanie zakończony wstępny etap opracowywania danych, tym więcej czasu specjalista będzie mógł poświęcić na bardziej skomplikowane i - nie ukrywajmy - strategicznie bardziej istotne zadania. Przedstawiona w artykule formuła jest na tyle uniwersalna, że można ją użyć dla nadania kategorii najróżniejszym typom danych.

Nie przegap kolejnego szkolenia

Już w najbliższy czwartek 18.08.2016 kolejny Webinar: Wybór fraz kluczowych i optymalizacja..
Nie przegap szkolenia! Udział w nim jest zupełnie darmowy i nie wymaga rejestracji.

Nowy sposób wypłaty środków

Pojawił się nowy sposób wypłaty dla Wydawców i Osób Polecających, którzy wykorzystują nasz system jako narzędzie do zarabiania w Internecie. Od teraz można wypłacać swoje zarobione pieniądze także na konto Paypal.