Ciężko wyobrazić sobie pracę Specjalisty SEO bez odpowiednich narzędzi wspomagających raportowanie i pracę z danymi. Najlepszym sposobem na szybkie sortowanie, kategoryzowanie i analizowanie danych jest skorzystanie z arkuszy kalkulacyjnych. 

Bardzo często w codziennej pracy wykorzystujemy Excela – najpopularniejsze narzędzie, które umożliwia łatwą pracę z danymi. Aczkolwiek, w pracy Specjalisty SEO bardzo często pojawia się potrzeba pracy zespołowej i korzystania z narzędzi, które pozwalają na szybkie udostępnianie danych oraz edycję jednego dokumentu przez kilku użytkowników jednocześnie. Tutaj z pomocą przychodzi Google, ze swoim odpowiednikiem Excela w wersji online. Arkusze kalkulacyjne Google mogą być używane do pobierania danych ze stron internetowych oraz innych narzędzi, znacznie ułatwiając przy tym pracę. W poniższym wpisie przedstawiamy najbardziej pomocne funkcje Google Sheets, które powinien znać każdy specjalista SEO.

WYSZUKAJ.PIONOWO – porównanie dwóch baz danych

Nie można w artykule dotyczącym arkuszy kalkulacyjnych pominąć opisania funkcji  WYSZUKAJ.PIONOWO. Jest to jedna z popularniejszych i bardzo często używanych funkcji podczas pracy z danymi. Korzystamy z niej gdy chcemy porównać dwie bazy danych. Przykładowo, jeżeli posiadamy dane z dwóch różnych źródeł, np. tabelę z danymi dotyczącymi liczby użytkowników odwiedzających konkretne podstrony w witrynie (zaciągnięte z Google Analytics):

oraz dane dotyczące wyświetleń i kliknięć dla tych samych podstron (pobrane z Google Search Console):

i chcemy te dane połączyć, w tym celu powinniśmy skorzystać z następującej formuły (wpisanej np. w tabeli z danymi z GSC w drugiej komórce kolumny D):

=WYSZUKAJ.PIONOWO(A2;Analytics!$A$1:$B$100;2;0)

gdzie:

  • A2 – to komórka do której dopasowujemy dane
  • Analytics!$A$1:$B$100 – odwołanie do arkusza z danymi z Analytics, a dokładnie do konkretnego zakresu danych mieszczących się w kolumnie A i B w wierszach od 1-100.
  • Symbol $ blokuje dane, dzięki czemu podczas przeciągania formuły wzdłuż danych będą zaciągane informacje tylko i wyłącznie wybranego zakresu.
  • 2 – jest to numer kolumny z danymi, które formuła przeszukuje (kolumna B „użytkownicy”). W naszej tabeli z danymi Majestic mamy cztery kolumny, które formuła może przeszukać:
  • 0 – to element opcjonalny:

Tak wpisaną formułę należy przeciągnąć w dół wzdłuż danych z GSC. 

SPLIT – podział ciągu znaków

SPLIT to funkcja której używamy gdy chcemy przy pomocy ustalonego separatora podzielić jakiś ciąg znaków i umieścić je w osobnych komórkach. Przykładowo, gdy mamy dane dotyczące różnych adresów URL i chcemy z nich wyciągnąć tylko domeny, możemy to zrobić przy pomocy właśnie funkcji SPLIT:

=SPLIT(A2;„/”)

Bardzo uproszczony przykład, gdzie separatorem jest znak „/”:

dane na których nam zależy znajdują się w kolumnie C:

Funkcję warto także wykorzystać gdy posiadamy dane zapisane w formacie .csv oddzielonego przecinkami. Wtedy separatorem, który należy użyć w formule będzie przecinek.

W powyższym przykładzie zostały zaprezentowane bardzo proste adresy URL, które są stworzone wg podobnego schematu – wszystkie zaczynają się od https. Dla bardziej zróżnicowanych adresów URL radzimy wykorzystać zupełnie inną formułę, o której więcej informacji znajduje się poniżej.

REGEXTRACT – wyodrębnianie nazwy domen z listy adresów URL

Gdy posiadamy listę bardziej zróżnicowanych adresów ULR (np. zaczynających się od http, https, www, itd.), z których chcemy wyciągnąć tylko nazwę domen z pomocą przychodzi nam funkcja REGEXTRACT z dość skomplikowaną zawartością wyrażeń regularnych. Na całe szczęście nie trzeba jej rozumieć, aby móc z niej skorzystać.

Oto ona:

=REGEXEXTRACT(A2;„^(?:https?:\/\/)?(?:[^@\n][email protected])?(?:www\.)?([^:\/\n]+)”)

Wpisując ją w kolumnie obok adresów url dostajemy gotowy wynik w postaci czystych domen:

Proste, prawda?

IMPORTXML – importowanie danych z adresu URL

Funkcja IMPORTXML ułatwia pracę z analizą metadanych w witrynie. Dzięki niej możemy importować do arkusza takie dane jak np. tagi tytułowe, meta opis, nagłówki H1. W tym celu wystarczy posiadać listę adresów URL, dla których chcemy te dane „wyciągnąć”. W pustej komórce obok adresu URL dla którego chcemy zaciągnąć tag tytułowy, należy wpisać taką formułę:

=IMPORTXML(A2;„//title/text()”)

Przykład:

a następnie przeciągnąć ją wzdłuż całej kolumny A.

Jak już było wspomniane wcześniej, funkcję IMPORTXML możemy także wykorzystać do pobrania meta opisów oraz nagłówków H. Oto formuły, których należy użyć:

  • Wyciąganie meta opisu:

=IMPORTXML(„https://domena.pl”;„//meta[@name=’description’]/@content”)

  • Wyciąganie H1 z danej strony:

=IMPORTXML(„https://domena.pl”;„//h1”)

DŁ – ile znaków znajduje się w komórce

Jest to bardzo prosta funkcja, która przyda się np. podczas tworzenia tagów tytułowych, które powinny mieścić się w określonej liczbie znaków. Aby dowiedzieć się ile znaków zawiera tytuł danej podstrony, należy umieścić go np. w komórce A1, następnie w komórce obok umieścić formułę:

=DŁ(A1)

Dla większego zbioru danych warto połączyć tę funkcję z formatowaniem warunkowym – np. zaznaczenie na czerwono wartości większych niż 60 i mniejszych od 30, na zielono wartości z przedziału 50-60, a na żółto znajdujących się pomiędzy 30 a 50 . Dzięki takiemu „pokolorowaniu” możemy szybko zweryfikować które metadane są za długie lub za krótkie. Wystarczy zaznaczyć kolumnę z danymi i wybrać w menu:

Formatuj 🡪 Formatowanie warunkowe 

Następnie utworzyć reguły:

IMPORTRANGE – importowanie danych z innego arkusza

Dzięki funkcji IMPORTRANGE możemy pobrać do swojego raportu dane z dowolnego arkusza kalkulacyjnego, pod warunkiem że mamy pozwolenie na dostęp do jego zawartości.

Aby skorzystać z tej funkcji potrzebujemy adresu URL arkusza kalkulacyjnego, z którego będą zaciągane dane oraz zakres komórek, które chcemy zaimportować. Oto formuła:

=IMPORTRANGE(„https://docs.google.com/spreadsheets/d/przykladowy-adres-url”;„Arkusz1!A1:B2”)

UWAGA: Dane nie zaciągną się od razu jeśli nie zezwoliliśmy na dostęp do arkusza:

Funkcję można wykorzystać także w innej formule np. podczas korzystania z WYSZUKAJ.PIONOWO dla dwóch różnych Arkuszów.

LICZ.JEŻELI  – liczbę elementów, które spełniają określone kryteria

Dzięki formule LICZ.JEŻELI możemy uzyskać liczbę komórek, które spełniają określone przez nas kryteria. 

Załóżmy, że posiadamy tabelę z danymi dotyczącymi fraz kluczowych, które wygenerowały największą liczbę kliknięć danego dnia:

Chcielibyśmy sprawdzić, która fraza występuje w tym zestawieniu najczęściej.

W tym celu, powinniśmy wyodrębnić unikatowe frazy kluczowe, np. przy wykorzystaniu funkcji UNIQUE – w dowolnej kolumnie poza tabelą należy wpisać taką formułę:

=UNIQUE(B:B)

Następnie w kolumnie obok wyodrębnionych fraz umieścić formułę:

=LICZ.JEŻELI(zakres; kryterium)

Dla naszego przykładu będzie to:

=LICZ.JEŻELI(B:B;F2)

Po przeciągnięciu formuły wzdłuż wszystkich unikatowych fraz, dostaniemy informację ile razy wystąpiły one w naszych danych dotyczących największej liczby kliknięć:

GOOGLEFINANCE „Currency:” – aktualne dane nt. walut

GOOGLEFINANCE ułatwia nam tworzenie raportów dotyczących np. kosztów danej usługi, które wypisane są w różnych walutach. Funkcja pobiera aktualne informacje na temat kursów walut. Przykład wykorzystania:

=GOOGLEFINANCE(„Currency:EURPLN”) – formuła, która podaje nam aktualny kurs euro.

=GOOGLEFINANCE(„Currency:USDPLN”) – formuła, która podaje nam aktualny kurs dolara.

Jeżeli np. posiadamy zestawienie artykułów opublikowanych na różnych międzynarodowych portalach, wraz z kosztami publikacji w różnych walutach, które chcemy ujednolicić, możemy umieścić tablicę kursów w naszym arkuszu:

i wykorzystać ją w przeliczeniu kwot w obcych walutach na polskie złotówki:

IMAGE – wstawianie obrazu do komórki

Jest to funkcja, która nieco uatrakcyjni wizualnie nasze raporty. Korzystając z niej mamy możliwość wstawienia do raportu np. logo strony lub innej, dowolnej grafiki. Możemy skorzystać z niej także gdy chcemy przedstawić grafiki w witrynie, które nie mają uzupełnionego atrybutu alt.

Wystarczy użyć formułę:

=IMAGE(url; [tryb]; [wysokość]; [szerokość])

Gdzie:

  • url – adres URL obrazu wraz z protokołem – musi być umieszczony w cudzysłowach 
  • tryb – (domyślnie 1), do wyboru:
    • 1 – powoduje zmianę rozmiaru aby dopasować go do komórki, przy zachowaniu współczynnika proporcji.
    • 2 – rozciąga lub zwęża obraz w celu dopasowania go do komórki
    • 3 – zachowuje oryginalny rozmiar obrazu
    • 4 – umożliwia określenie rozmiaru niestandardowego.
  • wysokość – (opcjonalnie) – aby ustawić niestandardową wysokość należy ustawić tryb na 4 
  • szerokość – (opcjonalnie) – aby ustawić niestandardową szerokość należy ustawić tryb na 4 

GOOGLETRANSLATE – Tłumaczenie na inny język

Funkcja GOOGLETRANSLATE umożliwia nam tłumaczenie słów z języków obcych w Arkuszach Google. Jeżeli posiadamy listę fraz kluczowych w języku polskim i chcemy przetłumaczyć je np. na język angielski:

Wystarczy w kolumnie obok (w naszym przykładzie kolumna B) wpisać:

=GOOGLETRANSLATE(A2;„pl”;„en”)

I przeciągnąć wzdłuż kolumny A:

DETECTLANGUAGE – wykrywanie języka

Podobnie działa funkcja DETECTLANGUAGE, która wykrywa język na podstawie danego słowa, np. po wpisaniu formuły:

=DETECTLANGUAGE(B2)

obok kolumny z frazami, Google podpowiada nam w jakim języku dane słowo jest zapisane:

Podsumowanie

Google Sheet to rewelacyjne narzędzie, które od lat ułatwia pracę Specjalistów na całym Świecie. Daje nam nie tylko możliwość współtworzenia arkusza przez kilku użytkowników w jednym czasie, ale także udostępnia funkcje, których Excel nie posiada. Te z nich, które zostały omówione w tym artykule to tylko kropla w morzu możliwości jakie daje nam Google Sheets. Im więcej funkcji arkuszy poznamy, tym nasza praca stanie się łatwiejsza i przyjemniejsza. 

 

Autorka: Anna Jurczyk

SEO Specialist. W DevaGroup odpowiada za prowadzenie kampanii SEO, przeprowadzanie audytów i optymalizację stron internetowych klientów. Prywatnie miłośniczka podróży, aktywnego stylu życia oraz psów rasy Border Collie.

Linkhouse - platforma Content Marketingu i Link Buildingu

ZAŁÓŻ KONTO
Kinga Edwards

Subskrybuj

OTRZYMASZ POWIADOMIENIA O NASZYCH NOWYCH ARTYKUŁACH DROGĄ MAILOWĄ
Administratorem Twoich danych osobowych jest Grupa KBR Spółka z ograniczoną odpowiedzialnością sp.k. z siedzibą w Katowicach. Zapoznaj się z pełną informacją o przetwarzaniu danych klikając tutaj

Zgodnie z art. 13 ust. 1 i ust. 2 rozporządzenia Parlamentu Europejskiego i Rady (UE) 2016/679 z 27.04.2016 r. w sprawie ochrony osób fizycznych w związku z przetwarzaniem danych osobowych i w sprawie swobodnego przepływu takich danych oraz uchylenia dyrektywy 95/46/WE (ogólne rozporządzenie o ochronie danych) (Dz. Urz. UE L 119, s.1), dalej jako : „RODO” informujemy:

  1. Administratorem Twoich danych osobowych jest Grupa KBR sp. z o.o. sp. k. z siedzibą w Katowicach, al. Korfantego 2 lok. 840 Katowice, wpisana do rejestru przedsiębiorców Krajowego Rejestru Sądowego prowadzonego przez Sąd Rejonowy Katowice – Wschód w Katowicach, VIII Wydział Gospodarczy Krajowego Rejestru Sądowego pod numerem KRS: 0000634991, NIP: 6342871729, REGON: 365310812 (dalej: „Administrator”),
  2. Kontakt bezpośrednio z Administratorem możliwy jest pod wskazanym w pkt. 1 powyżej adresem Administratora lub pod adresem e-mail:[email protected]
  3. Twoje dane osobowe będą przetwarzane w celu:
    • przeprowadzenia kontaktu zainicjowanego przez Ciebie poprzez wysłanie formularza tj. na podstawie art. 6 ust. 1 lit. b RODO),
    • zawarcia, wykonania i zakończenia umowy zawartej z Administratorem, tj. na podstawie art. 6 ust. 1 lit. b RODO – w przypadku, gdy dokonałeś pełnego i prawidłowego zarejestrowania w serwisie Linkhouse,
    • wykonywania obowiązków wynikających z przepisów prawa, w tym obowiązków finansowych i podatkowych, tj. na podstawie art. 6 ust. 1 lit.c RODO,
    • prowadzenia działań promocyjnych lub marketingowych, w tym marketingu bezpośredniego, w szczególności kierowania do Ciebie nowych ofert i propozycji współpracy, realizując tym samym prawnie uzasadniony interes Administratora, tj. na podstawie art. 6 ust. 1 lit. f RODO, a w przypadku braku zawarcia umowy z Administratorem na podstawie Twojej zgody tj. na podstawie art. 6 ust. 1 lit. a RODO,
    • utrzymywanie i poprawy funkcjonowania witryny linkhouse.co, za pośrednictwem której wykonywana jest zawarta z Tobą umowa, w tym administrowanie naszą witryną, wykonywanie operacji wewnętrznych, w tym rozwiązywanie problemów, analizy danych, badań, testów, celów statystycznych i badawczych realizując tym samym prawnie uzasadniony interes Administratora, tj. na podstawie art. 6 ust. 1 lit. f RODO,
  4. Twoje dane osobowe będą przetwarzane przez okres trwania zawartej z Administratorem umowy, a po jej wygaśnięciu przez okres przedawnienia roszczeń cywilnoprawnych wynikających z zawartej umowy oraz przez okres wymagany przez prawo, w szczególności przez regulacje podatkowe – przez okres przedawnienia zobowiązań podatkowych. W przypadku braku zawarcia umowy z Administratorem, Twoje dane osobowe będą przetwarzane przez okres 12 miesięcy oraz czas niezbędny do wywiązania się z obowiązków nałożonych przez przepisy prawa.
  5. Twoje dane osobowe mogą zostać udostępnione innym osobom posiadającym aktywne konto użytkownika Linkhouse, wyłącznie po zalogowaniu i w ograniczonym zakresie, a ponadto podmiotom wspierającym Administratora w prowadzonej działalności, w szczególności wszelkim podmiotom świadczącym usługi księgowe, marketingowe, dostawcom usług i narzędzi informatycznych, hostingowych, bankom, dostawcom usług płatniczych, podmiotom zapewniającym usługę prawną, współpracownikom Administratora, przy czym możliwe jest przekazanie danych do państwa trzeciego, w sytuacji gdy, którykolwiek z ww. podmiotów dokonuje przetwarzania danych w państwie trzecim oraz wyłącznie w sytuacji, gdy Komisja Europejska dokonała stwierdzenia odpowiedniego stopnia ochrony lub z wykorzystaniem klauzul umownych zatwierdzonych przez Komisję Europejską lub w przypadku posiadania właściwych zabezpieczeń, o których informacja może zostać Pani/Panu udostępniona.
  6. Podanie danych osobowych jest dobrowolnej, jednak niezbędne dla realizacji zawartej z Tobą umowy cywilnoprawnej, a w przypadku braku zawarcia umowy z Administratorem niezbędne do otrzymywania materiałów reklamowych i marketingowych – w takiej sytuacji przetwarzamy Twoje dane na podstawie zgody, która jest dobrowolna i w każdym momencie może być przez Ciebie wycofana.
  7. Przysługuje Ci prawo żądania dostępu do treści danych osobowych, ich sprostowania, usunięcia oraz prawo do ograniczenia ich przetwarzania. Ponadto także prawo do przenoszenia danych oraz prawo do wniesienia sprzeciwu wobec przetwarzania danych osobowych, Posiadasz prawo wniesienia skargi do Prezesa Urzędu Ochrony Danych Osobowych. Dane osobowe będą przekazywane wyłącznie naszym zaufanym podwykonawcom, tj. dostawcom usług informatycznych, firmie księgowej, administracji.