SEO Optymalizacja

Newsy w serwisie

Jak analizować dane z Search Console w BigQuery

seoptimizer.pl

Wgląd w surowe dane z narzędzi SEO pozwala na wykonywanie precyzyjnej analizy i odkrywanie przyczyn fluktuacji ruchu organicznego. Połączenie Search Console z BigQuery otwiera możliwości przeprowadzania zaawansowanych zapytań, tworzenia własnych raportów oraz optymalizacji stron pod kątem performance. Poniższy artykuł przedstawia kolejne etapy integracji, przygotowania danych, definiowania zapytań SQL i wizualizacji wyników w kontekście projektów SEO.

Integracja Search Console z BigQuery

Pierwszym krokiem jest skonfigurowanie eksportu eksport danych z Search Console do usługi BigQuery. Dzięki bezpośredniemu połączeniu możliwe jest regularne zasilanie bazy danymi dotyczącymi zapytań, kliknięć, wyświetleń i pozycji średniej. Kroki do wykonania:

  • Zaloguj się do Search Console i wybierz właściwość, którą chcesz analizować.
  • Przejdź do sekcji “Ustawienia” i wskaż opcję “Eksport do BigQuery”.
  • Wybierz projekt w Google Cloud i skonfiguruj harmonogram – codzienne lub tygodniowe zasilanie.
  • Określ schematy tabel: nazwy kolumn, typy danych (STRING, INTEGER, DATE).
  • Autoryzuj połączenie – upewnij się, że konto ma uprawnienia do zapisu w zestawie danych.

Po zakończeniu konfiguracji w BigQuery pojawi się zestaw tabel o nazwach takich jak searchconsole_main i searchconsole_history, zawierające wszystkie niezbędne informacje do dalszej analizy.

Przygotowanie i przetwarzanie danych

Zanim rozpoczniesz pisanie skomplikowanych zapytań SQL, warto uporządkować dane. Proces obejmuje:

  • Usunięcie duplikatów – identyfikacja i odrzucenie powtarzających się wierszy.
  • Ujednolicenie formatów dat – skonwertowanie dat do standardu ISO (YYYY-MM-DD).
  • Tworzenie widoków – agregacja danych z różnych tabel w spójną strukturę.
  • Filtrowanie nieistotnych zapytań – wykluczenie wewnętrznych domen lub fraz brandowych.
  • Tworzenie dodatkowych kolumn – np. grupowanie tematów, wyodrębnienie podkategorii URL.

Przykład tworzenia widoku:

CREATE VIEW `project.dataset.vw_search_data` AS
SELECT
  PARSE_DATE('%Y%m%d', date) AS report_date,
  country,
  device,
  page,
  query,
  clicks,
  impressions,
  ROUND((clicks/impressions)*100, 2) AS ctr,
  position
FROM
  `project.dataset.searchconsole_main`
WHERE
  impressions > 0;

Taki widok pozwala na uproszczony dostęp do danych z wyliczonym wskaźnikiem CTR i gotową kolumną position.

Zaawansowane zapytania SQL

W tej części omówimy kilka przykładowych zapytań, które można wykorzystać do zrozumienia trendów w ruchu organicznym oraz identyfikacji słów kluczowych o największym potencjale.

Wykrywanie fraz o najwyższym wzroście wyświetleń

SELECT
  query,
  SUM(impressions) AS total_impressions,
  SUM(clicks) AS total_clicks,
  ROUND((SUM(clicks)/SUM(impressions))*100, 2) AS avg_ctr
FROM
  `project.dataset.vw_search_data`
WHERE
  report_date BETWEEN '2023-01-01' AND '2023-06-30'
GROUP BY
  query
HAVING
  total_impressions > 1000
ORDER BY
  total_impressions DESC
LIMIT 20;

Analiza pozycji średniej dla kluczowych podstron

SELECT
  page,
  AVG(position) AS avg_position,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions
FROM
  `project.dataset.vw_search_data`
WHERE
  query LIKE '%produkt%'
GROUP BY
  page
ORDER BY
  avg_position ASC
LIMIT 10;

Podział ruchu według urządzeń

SELECT
  device,
  SUM(clicks) AS total_clicks,
  SUM(impressions) AS total_impressions,
  ROUND((SUM(clicks)/SUM(impressions))*100, 2) AS ctr
FROM
  `project.dataset.vw_search_data`
GROUP BY
  device;

Dzięki tym przykładom można szybko zidentyfikować frazy, które warto promować lub strony wymagające optymalizacji pod konkretne urzędzenia.

Wizualizacja i raportowanie

Aby wyniki analiz były dostępne dla całego zespołu, warto podłączyć BigQuery do narzędzi BI. Najpopularniejsze połączenia to:

  • Google Data Studio – darmowe rozwiązanie z natywną integracją.
  • Looker – zaawansowane środowisko do tworzenia dashboardów.
  • Tableau – rozbudowane możliwości w zakresie wizualizacji.
  • Grafana – elastyczne panele monitorujące performance SEO w czasie rzeczywistym.

Przykładowy dashboard powinien zawierać:

  • Trend kliknięć i wyświetleń (wykres liniowy).
  • Mapa krajów generujących największy ruch.
  • Porównanie CTR i pozycji w podziale na urządzenia.
  • Lista top 10 fraz według wzrostu wyświetleń.

Wszystkie te wykresy pomagają w szybkim podejmowaniu decyzji o dalszej optymalizacji treści i kampanii SEO.

Praktyczne scenariusze wykorzystania

  • Monitorowanie efektów zmian na stronie – porównanie danych przed i po wdrożeniu nowego designu lub treści.
  • Identyfikacja sezonowych trendów – analiza danych historycznych w celu lepszego planowania kalendarza publikacji.
  • Optymalizacja treści pod długi ogon – selekcja fraz z niską konkurencją, ale rosnącymi wyświetleniami.
  • Automatyczne alarmy – wykorzystanie API BigQuery do generowania powiadomień, gdy CTR spada poniżej określonej wartości.
  • Segmentacja ruchu – analiza ruchu organicznego dla różnych języków lub regionów.

Wprowadzenie tych scenariuszy w życie wspiera proces ciągłej analizy i iteracyjnej optymalizacji witryny.

Narzędzia wspomagające pracę z BigQuery i Search Console

Aby usprawnić współpracę z danymi, warto korzystać z dodatkowych narzędzi:

  • Google Cloud SDK – zapewnia CLI do zarządzania Bazą BigQuery i przesyłania danych.
  • BigQuery Data Transfer Service – automatyczny transfer danych z innych źródeł, np. Google Analytics.
  • DBT (Data Build Tool) – narzędzie do zarządzania transformacjami SQL i wersjonowania kodu.
  • Airflow – orkiestracja zadań ETL, harmonogramowanie i monitoring zadań SQL.
  • GitHub – przechowywanie kodu zapytań i dokumentacja procesów ETL.

Stosowanie tych rozwiązań poprawia jakość zarządzania danymi, ułatwia tworzenie filtrów oraz zapewnia przejrzystość i powtarzalność analiz.

Powiązane treści