BigQuery to zaawansowane narzędzie do analizy dużych zbiorów danych oferowane przez Google w ramach Google Cloud Platform. Jego potencjał w kontekście SEO jest ogromny, zwłaszcza gdy chodzi o analizę dużych ilości danych dotyczących ruchu na stronie, analizy słów kluczowych czy też zachowań użytkowników.

Wykorzystanie BigQuery do analizy danych z Google Search Console

Google Search Console to narzędzie, które dostarcza kluczowe dane dotyczące widoczności strony w wynikach wyszukiwania Google. Ale żeby nie było zbyt idealnie – w GSC istnieją pewne limity i ograniczenia, które w przypadku dużych stron internetowych mogą nam utrudniać analizę danych. Z pomocą przychodzi nam platforma Google Cloud, która udostępnia możliwość integracji usług, np. Google Search Console z bazą danych Google BigQuery. 

W Google Search Console istnieje kilka ograniczeń dotyczących danych, które nie występują w BigQuery. Po pierwsze, w interfejsie GSC dostępna jest ograniczona liczba zapytań. Jeśli jest ona bardzo duża, mogą one zostać skonsolidowane, co może wpływać na szczegółowość analiz. W BigQuery natomiast eksportowane są wszystkie zapytania, nawet te bardzo rzadkie. Kolejnym ograniczeniem jest liczba wierszy. W raportach GSC w interfejsie użytkownika można przeglądać maksymalnie 1000 wierszy na jedno zapytanie, natomiast w przypadku korzystania z API limit ten wynosi 25 000 wierszy na zapytanie. W BigQuery można natomiast przetwarzać znacznie większe zbiory danych bez takich limitów. 

Kluczowa w tym miejscu jest informacja, że w Google Search Console dane dostępne przez interfejs są ograniczone do maksymalnie 16 miesięcy wstecz, co uniemożliwia przeprowadzanie długoterminowych analiz. Natomiast w BigQuery przesyłane dane mogą być przechowywane bez ograniczeń czasowych, co umożliwia prowadzenie analiz na przestrzeni wielu miesięcy czy lat. 

Należy jednak wspomnieć, że za przechowywanie danych w BigQuery musimy ponieść pewien koszt. Jaki? Nie ma niestety jednoznacznej odpowiedzi na to pytanie – zależy to od ilości danych oraz regionu, w którym są one przechowywane. Google BigQuery oferuje dwa główne modele cenowe: płatność za dane przechowywane oraz płatność za zapytania do danych.

Dane, które są przechowywane i dostępne do natychmiastowego przetwarzania, kosztują około $0.02 za 1 GB miesięcznie. Opłata za przetwarzanie danych w zapytaniach do BigQuery wynosi około $5 za każdy przetworzony terabajt (TB) danych. Warto jednak zaznaczyć, że pierwszy 1 TB przetworzonych danych każdego miesiąca jest bezpłatny.

Połączenie BigQuery z Google Search Console

W tym miejscu przedstawiamy krok po kroku, jak zintegrować te dwa narzędzia oraz jak wykorzystać je do optymalizacji działań SEO.

1. Utworzenie nowego projektu w Google Cloud Console

Aby rozpocząć, musisz utworzyć nowy projekt w Google Cloud Console:

1. Zaloguj się do Google Cloud Console https://console.cloud.google.com/ 
2. Utwórz nowy projekt. W tym celu przejdź do Administracja -> Utwórz projekt (znajdziesz to w menu nawigacyjnym po lewej stronie).

3. Wprowadź nazwę projektu i, jeśli to konieczne, wybierz lokalizację. Po zakończeniu kliknij Utwórz.
4. Do nowego projektu automatycznie zostanie przypisany identyfikator, który posłuży Ci później w procesie eksportu danych z GSC.

2. Włączanie API w Google Cloud Console

Następnym krokiem jest włączenie niezbędnych API dla BigQuery i Google Search Console:

1. Przejdź do sekcji Interfejsy API i usługi w Google Cloud Console.
2. Kliknij Biblioteka i wyszukaj BigQuery API.
3. Wybierz BigQuery API i kliknij Włącz.
4. Następnie wyszukaj Search Console API.

5. Kliknij Włącz i upewnij się, że API zostało włączone.

3. Konfiguracja uprawnień

Aby Google Search Console mogło eksportować dane do BigQuery, konieczne jest przyznanie odpowiednich uprawnień:

1. W Google Cloud Console przejdź do sekcji Administracja -> Uprawnienia.

2. Kliknij PRZYZNAJ DOSTĘP. 
3. W polu Nowe podmioty zabezpieczeń wprowadź search-console-data-export@system.gserviceaccount.com z uprawnieniami BigQuery Job User oraz BigQuery Data Editor.

4. Eksport danych z Google Search Console

Po poprawnej konfiguracji dane z Google Search Console mogą być automatycznie eksportowane do BigQuery. W tym celu przejdź do GSC. Zaloguj się na to samo konto Google, na którym przed chwilą działałeś w BigQuery. Upewnij się, że jesteś właścicielem usługi GSC, którą chcesz połączyć z BQ.

Przejdź do ustawień. Wybierz Zbiorczy eksport danych.

Wypełnij pola: Identyfikator projektu z Google Cloud Console, Nazwa zbioru danych, która musi zaczynać się od searchconsole, a także Lokalizacja np. Stany Zjednoczone lub Europa. 

Pierwszy eksport danych odbywa się w ciągu 48 godzin od momentu konfiguracji w Search Console. Obejmuje on dane z dnia, w którym eksport został rozpoczęty. W przypadku napotkania przejściowego błędu Search Console podejmie próbę ponownego eksportu danych kolejnego dnia.

Analiza Danych SEO z Google Search Console

Po zaimportowaniu danych do BigQuery możesz rozpocząć ich analizę.

Praca z danymi SEO z Google Search Console (GSC) w BigQuery może dostarczyć wielu cennych wniosków na temat wydajności witryny w wynikach wyszukiwania Google. 

Poniżej znajduje się kilka przykładów, jak można pracować z tymi danymi. Zanim jednak przejdziemy do szczegółów, warto zapoznać się z podstawowymi zapytaniami SQL, które często wykorzystujemy w pracy z BigQuery.

SELECT – Służy do pobierania danych z jednej lub więcej tabel. Pozwala na wybieranie określonych kolumn lub całych wierszy, a także na stosowanie różnych warunków filtrowania.

FROM – Służy do określenia tabeli lub zestawu tabel, z których będą pobierane dane w zapytaniu SQL. Może to być pojedyncza tabela lub kilka z nich połączonych za pomocą JOIN.

JOIN – Umożliwia łączenie danych z dwóch lub więcej tabel na podstawie wspólnej kolumny.

WHERE – Używane do filtrowania wyników zapytania na podstawie określonych warunków. Tylko te wiersze, które spełniają warunek, zostaną zwrócone w wynikach.

GROUP BY – Używane do grupowania wyników na podstawie jednej lub więcej kolumn. Zwykle jest stosowane w połączeniu z funkcjami agregującymi takimi jak COUNT, SUM, AVG, MAX, MIN.

ORDER BY – Służy do sortowania wyników zapytania według jednej lub więcej kolumn w kolejności rosnącej (ASC) lub malejącej (DESC).

LIMIT – Ogranicza liczbę wierszy zwracanych przez zapytanie do określonej wartości.

SUM – Funkcja agregująca, która sumuje wartości określonej kolumny w zestawie danych.

HAVING – Działa po zastosowaniu GROUP BY, umożliwiając filtrowanie wyników na podstawie funkcji agregujących.

Przykłady pracy z danymi z Google Search Console

Za pomocą zapytań SQL BigQuery umożliwia wydobywanie cennych informacji z danych Google Search Console. Poniżej znajdziesz kilka przykładów użycia.

Identyfikacja najlepszych stron

Analiza stron, które generują najwięcej kliknięć, sprawdzenie liczby ich wyświetleń i średniej pozycji.

Na zrzucie ekranu widzimy interfejs Google Cloud BigQuery, Po lewej stronie ekranu znajduje się panel nawigacyjny, który pokazuje różne zestawy danych i tabele dostępne w środowisku BigQuery. Analizowany zestaw danych dotyczy tabeli searchdata_url_impression, której częściową zawartość (pola dostępne w tabeli) widać na środkowej części ekranu. W górnym prawym rogu widzimy edytor SQL z zapytaniem, które analizuje dane wyszukiwania. Oto treść zapytania:

SELECT
  url,
  SUM(clicks) AS total_clicks,
  SUM(impressions) AS total_impressions,
  ROUND(AVG(avg_position), 1) AS avg_position
FROM
  `project.dataset.searchdata_url_impression`
GROUP BY
  url
ORDER BY
  total_clicks DESC
LIMIT 10;

To zapytanie zbiera dane dla każdego URL-a, licząc łączną liczbę kliknięć, wyświetleń oraz średnią pozycję w wynikach wyszukiwania.

W prawej dolnej części ekranu widzimy wyniki zapytania, które zostały wyświetlone w formie tabeli. Tabela ta zawiera kolumny: url, total_clicks (całkowita liczba kliknięć), total_impressions (całkowita liczba wyświetleń) oraz avg_position (średnia pozycja). Wyniki są posortowane według liczby kliknięć (total_clicks) w kolejności malejącej i jest ich 10 (ponieważ taki limit wyników został określony).

Analiza zapytań i ich wpływu na widoczność stron

W ramach tej analizy możemy zidentyfikować, jakie zapytania są najbardziej skuteczne w przyciąganiu ruchu do poszczególnych stron (URL). Zestawienie liczby kliknięć i wyświetleń dla kombinacji URL i zapytania pozwala na dogłębne zrozumienie, które frazy kluczowe są najcenniejsze oraz które strony najlepiej odpowiadają na konkretne zapytania użytkowników.

Oto treść zapytania:

SELECT
  query,
  url,
  SUM(clicks) AS total_clicks,
  SUM(impressions) AS total_impressions
FROM
  `project.searchconsole.searchdata_url_impression`
WHERE
  query IS NOT NULL
GROUP BY
  query,
  url
ORDER BY
  total_clicks DESC;

Dodanie warunku WHERE query IS NOT NULL sprawi, że w wynikach zapytania zostaną uwzględnione tylko te wiersze, w których kolumna query nie ma wartości NULL. Dzięki temu uzyskujemy wykluczamy te URL-e, które nie mają przypisanego słowa kluczowego.

Te, tak jak i każde inne wyniki zapytania, możemy zapisać:

Identyfikacja stron o wysokiej pozycji, ale niskim zaangażowaniu

Identyfikacja stron o niskiej liczbie wyświetleń, lecz stosunkowo wysokiej średniej pozycji, co może sugerować problemy z indeksowaniem, oraz stron o niskiej liczbie kliknięć, mimo wysokiej pozycji, co może wskazywać na mało atrakcyjne wyniki w wyszukiwarce:

Przykład 1. Identyfikacja stron o niskiej liczbie wyświetleń

Treść zapytania SQL:

SELECT
  url,
  query,
  SUM(impressions) AS total_impressions,
  AVG(sum_position) AS avg_position
FROM
  `projekt.searchconsole.searchdata_url_impression`
  WHERE
  query IS NOT NULL
GROUP BY
  url,
  query
HAVING
  total_impressions < 100
  AND avg_position < 10
  AND avg_position >= 1
ORDER BY
  avg_position ASC;

Jeśli Twoja strona zajmuje wysoką pozycję w wynikach wyszukiwania, ale generuje mało wyświetleń, może to oznaczać problem techniczny lub związany z duplikacją treści, przez co użytkownicy rzadziej trafiają na stronę, mimo jej dobrej pozycji. Innym powodem może być mała popularność słów kluczowych, które, choć prowadzą do wysokiej pozycji w wynikach, generują niewielki ruch.

Warto jednak dokładnie przeanalizować te przypadki, zwłaszcza dla fraz kluczowych o wysokim wolumenie wyszukiwań. W pierwszej kolejności należy przeprowadzić szczegółową analizę adresu URL oraz zapytania w Google Search Console. Może się bowiem okazać, że dla bardzo popularnej frazy występuje kanibalizacja treści, czyli sytuacja, w której kilka podstron tej samej witryny konkuruje o pozycję dla tego samego słowa kluczowego.

Przykład 2. Identyfikacja stron o niskiej liczbie kliknięć 

Treść zapytania

SELECT
  url,
  query,
  SUM(clicks) AS total_clicks,
  SUM(impressions) AS total_impressions,
  AVG(sum_position) AS avg_position
FROM
  `projekt.searchconsole.searchdata_url_impression`
   WHERE
  query IS NOT NULL
GROUP BY
  url,
  query
HAVING
  total_clicks < 20
  AND total_impressions > 100
  AND avg_position < 10
  AND avg_position >= 1
ORDER BY
  avg_position ASC;

Jeśli strona zajmuje wysoką pozycję w wynikach wyszukiwania, ale generuje mało kliknięć, może to wskazywać na potrzebę optymalizacji elementów wpływających na CTR, takich jak tytuł strony lub meta opis. Problemem może być brak odpowiedniego dopasowania do intencji użytkownika, nieatrakcyjny tytuł strony, meta opis lub nawet sam URL.

Porównanie okresów

Zapytanie porównuje wyniki z różnych okresów, co pomaga ocenić skuteczność działań SEO w czasie:

Dla przykładu porównanie kliknięć dla zapytań dzień do dnia:

SELECT 
  query AS keyword,
  SUM(CASE WHEN data_date = '2024-08-30' THEN clicks ELSE 0 END) AS klikniecia_30_sierpien,
  SUM(CASE WHEN data_date = '2024-08-29' THEN clicks ELSE 0 END) AS klikniecia_29_sierpien
FROM 
  `indigo-winter-434008-e5.searchconsole.searchdata_url_impression`
GROUP BY 
  keyword
ORDER BY 
  klikniecia_30_sierpien DESC;

Tworzenie raportów danych z BigQuery w Looker Studio

Looker Studio (wcześniej znane jako Google Data Studio) to potężne narzędzie do tworzenia interaktywnych raportów i wizualizacji danych. 

Łatwa integracja z BigQuery

Looker Studio, będąc produktem Google, bardzo łatwo integruje się z innymi narzędziami Google, w tym z BigQuery. Proces podłączenia danych z BigQuery do Looker Studio jest bardzo prosty.

Aby Looker Studio mógł połączyć się z BigQuery, użytkownik musi mieć odpowiednie uprawnienia w BigQuery – co najmniej Viewer (czyli możliwość przeglądania) dla zbioru danych, który zamierzamy podłączyć do Looker Studio. 

Krótka instrukcja podłączenia danych

Po zalogowaniu się do Looker Studio utwórz nowy raport (kliknij przycisk + Utwórz)

Dodaj dane do raportu. W tym momencie łączymy BigQuery z Looker Studio:

Dodaj źródło danych. W tym przypadku będą to dane z Search Console:

Wybierz konkretny zbiór danych i tabelę, np. searchdata_url_impression. Po wybraniu odpowiedniej tabeli możesz zobaczyć podgląd danych. Looker Studio automatycznie wykryje kolumny z tabeli BigQuery i wyświetli je w formie pól raportu.

Przegląd danych umożliwi Ci sprawdzenie, które pola będą wykorzystywane w raportach (np. clicks, impressions, ctr, position):

Tworzenie tabel i wykresów  w Looker Studio

Po podłączeniu BigQuery jako źródła danych możesz skonfigurować różne typy wizualizacji, takie jak wykresy, tabele czy przeglądy statystyk.

Na pasku narzędzi Looker Studio wybierz typ wizualizacji, którą chcesz stworzyć (np. tabela, wykres liniowy, wykres kołowy itd.), a następnie przypisz odpowiednie pola z BigQuery do wybranej wizualizacji.

Przykładowe wizualizacje danych:

Możesz również dodawać bardziej zaawansowane obliczenia, filtry i segmenty, aby lepiej dostosować raport do swoich potrzeb.

Prezentacja danych w Looker Studio zamiast w BigQuery pozwala na pełną personalizację wyglądu raportów. Stają się one bardziej „przyjazne dla oka”. Możesz dostosować kolory, czcionki, nadawać tytuły itp. Największa zaleta to interaktywność raportu. Korzystając z filtrów, np. dat, możesz analizować wybrane okresy czasu.

Looker Studio umożliwia automatyczne odświeżanie danych z BigQuery, co oznacza, że nie musisz ręcznie aktualizować raportów, gdy nowe dane pojawiają się w BigQuery. 

W zależności od wielkości zbioru danych oraz częstotliwości aktualizacji danych w BigQuery możesz dostosować harmonogram odświeżania danych w Looker Studio. Opcję tę znajdziesz w ustawieniach edycji źródła danych (Zasób -> Zarządzaj dodanymi źródłami danych -> EDYTUJ)

Po kliknięciu w Częstotliwość aktualizacji danych masz możliwość wyboru odpowiedniego czasu.

Ponadto Looker Studio umożliwia wspólną pracę nad raportami w czasie rzeczywistym. Możesz współpracować z innymi osobami, aby wspólnie edytować i ulepszać raporty.

Podsumowanie

BigQuery to potężne narzędzie, które może znacząco usprawnić analizę danych SEO. Jego zdolność do szybkiego przetwarzania dużych zbiorów danych, zaawansowane możliwości analityczne i możliwość automatyzacji raportów sprawiają, że jest nieocenionym wsparciem dla każdej strategii SEO. Wykorzystanie BigQuery do analizy danych SEO pozwala na bardziej precyzyjne i efektywne działania, co w efekcie prowadzi do lepszych wyników w wyszukiwarkach i zwiększenia ruchu na stronie.

Natomiast integracja BigQuery z Looker Studio pozwala na tworzenie interaktywnych raportów, które łatwo dostosować do potrzeb biznesowych. Choć dane z Search Console można wizualizować w Looker Studio bezpośrednio, to korzystanie z BigQuery oferuje kluczową przewagę – dostęp do pełnej historii danych bez ograniczeń czasowych, w przeciwieństwie do 16-miesięcznego limitu w Search Console.

---

Autor artykułu

wróć