Jak analizować GSC za pomocą BigQuery

Analiza danych z Search Console w środowisku BigQuery pozwala przejść z poziomu zgrubnych raportów do precyzyjnych odpowiedzi na pytania techniczne. Łącząc surowe wiersze zapytań, adresów i urządzeń z kontekstem architektury serwisu, wykryjesz błędy, straty widoczności i realne dźwignie wzrostu. Poniżej znajdziesz praktyczny przewodnik: od konfiguracji eksportu GSC, przez model danych i optymalizację kosztów, po gotowe zapytania SQL zorientowane na diagnozy typowe dla technicznego SEO.

Konfiguracja eksportu Search Console do BigQuery

Wymagania i uprawnienia

Aby rozpocząć, potrzebujesz: (1) roli właściciela w Search Console dla danej właściwości, (2) dostępu do projektu Google Cloud z uprawnieniami tworzenia datasetów w BigQuery oraz (3) włączonego rozliczania w projekcie. Na potrzeby zespołowe warto wydzielić dedykowany projekt GCP dla danych Search Console, a następnie nadać role o najmniejszych niezbędnych uprawnieniach: BigQuery Data Viewer dla analityków i BigQuery Job User dla wykonywania zapytań. W kontekście SEO technicznego polecam również konto serwisowe do harmonogramów i automatyzacji (bez dostępu interaktywnego).

Włączenie Bulk Data Export w Search Console

Eksport zbiorczy (Bulk Data Export) to mechanizm, który codziennie publikuje pełne dane wydajności do BigQuery. W Search Console przejdź: Ustawienia → Bulk data export → Connect to BigQuery → wybierz projekt, lokalizację i schemat datasetu. Dla dużych serwisów rozważ oddzielne eksporty dla poszczególnych typów wyszukiwania (Web / Image / Video / News), bo każdy eksport jest przypisany do jednego typu i tworzy własne tabele. Pamiętaj: eksport działa „od teraz” – nie ma wstecznego zasilenia; im szybciej uruchomisz, tym dłuższy horyzont analityczny zbudujesz.

Struktura projektu i koszty

Dane są dostarczane dziennie, jako partycje datowe. Koszt w BigQuery dzieli się na składowanie i przetwarzanie zapytań. Główne zasady oszczędności: używaj filtrów po kolumnie partycji (date), ograniczaj pobierane kolumny i preferuj agregacje na widokach/materializacjach. W ramach projektu GCP warto rozdzielić dataset „raw” (oryginał eksportu) od datasetu „analytics” (przetworzone, znormalizowane i zindeksowane tabele do raportowania) – to sprzyja porządkowi i zmniejsza koszty zapytań ad-hoc.

Bezpieczeństwo i zgodność

Zadbaj o politykę dostępu opartą na grupach, etykiety danych (labels) oraz polityki retencji. Dane z zapytań mogą zawierać frazy brandowe i wrażliwe wzorce zachowań, dlatego dostęp do surowych tabel ogranicz do minimum. Jeżeli budujesz automatyczne eksporty do narzędzi BI, twórz pośrednie widoki z anonimizacją zapytań (np. hashowanie) dla odbiorców zewnętrznych.

Schemat danych i przygotowanie warstwy analitycznej

Tabele i kolumny: co faktycznie dostajesz

Eksport Search Console tworzy dwie kluczowe tabele: searchdata_site_impressions (zagregowane na poziomie witryny) oraz searchdata_url_impressions (na poziomie URL). W obu znajdziesz: date (partycja), site, url (w tabeli URL), query, country, device, searchAppearance (czasem lista wartości), a także metryki: clicks, impressions, ctr, position. Wymiar searchAppearance jest kluczowy do analizy wyników rozszerzonych (rich results), ale pamiętaj, że bywa pusty; na produkcji dodaj zawsze warunek zabezpieczający przed null/empty.

Ważna decyzja architektoniczna: nie mieszaj typów wyszukiwania w jednej tabeli. Każdy eksport dotyczy jednego typu (np. Web). Chcesz analizować obrazki? Utwórz osobny eksport i dataset lub przynajmniej prefiks tabel, aby uniknąć konfliktów semantyki.

Wymiary, metryki i pułapki interpretacyjne

Podstawowe metryki to clicks, impressions, ctr i position. W GSC CTR i Position są liczone w oparciu o średnie ważone; w BigQuery łatwo odtworzyć CTR jako SUM(clicks)/SUM(impressions), ale pozycja wymaga ostrożności, bo średnia z pozycji bywa myląca przy mieszaniu kontekstów (kraj, urządzenie, zapytanie). Dobrym nawykiem jest agregowanie per jeden wymiar więcej (np. device) i dopiero potem uśrednianie wyników, albo prezentacja percentyli (P50, P90) pozycji zamiast samej średniej.

Z kolei query może zawierać różne warianty literowe i znaki specjalne. W praktyce do analiz technicznych częściej interesują nas grupy zapytań (np. brand vs non-brand), a nie każde pojedyncze hasło. Stosuj słowniki i reguły, a wyniki będą stabilniejsze.

Partycjonowanie i klastrowanie: wydajność i koszty

Tabele z eksportu są partycjonowane po kolumnie date – to Twój najważniejszy selektor kosztów. Zawsze filtruj po zakresie dat, np. WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE(). Dodatkowo rozważ klastrowanie tabel analitycznych (nie surowych) po kolumnach device, country i np. pierwszym katalogu URL. Klastrowanie sprawi, że BigQuery będzie czytał mniej segmentów pamięci przy typowych zapytaniach segmentacyjnych.

Przykład: tworzysz tabelę analytics.url_daily z kolumną directory (pierwszy katalog) i device; klastrowanie po (directory, device) przyspieszy analizy sekcji serwisu i problemów mobilnych vs desktopowych.

Normalizacja i wzbogacanie danych

GSC nie ma wszystkiego. Aby wnioskować o stanie technicznym, dołącz:

• Sitemapy: tabela sitemap_urls(url, lastmod) – pozwala wykryć strony, które powinny rankować, ale nie mają wyświetleń.
• Dane o statusach HTTP z crawlów (np. Screaming Frog) – pozwala korelować widoczność z błędami 4xx/5xx.
• Publiczny CrUX (Chrome UX Report) – do oceny warstwy wydajnościowej i Core Web Vitals.
• logi serwera lub narzędzi edge/CDN – jako proxyna budżetu crawl i problemów z dostępnością.

Twórz minimalne, dobrze opisane słowniki (np. mapowanie URL → typ strony), by móc grupować wyniki w sposób biznesowo sensowny.

Zapytania BigQuery dla technicznego SEO

Wykrywanie kanibalizacja i duplikacji intencji

Jeśli wiele adresów konkuruje o to samo zapytanie, rozpraszasz autorytet i marnujesz crawl. Wykryjesz to następująco:

WITH base AS (
  SELECT date, url, query, clicks, impressions
  FROM `project.dataset.searchdata_url_impressions`
  WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
), q AS (
  SELECT query,
    COUNT(DISTINCT url) AS url_cnt,
    SUM(clicks) AS clicks,
    SUM(impressions) AS impressions,
    ARRAY_AGG(STRUCT(url, SUM(impressions) AS imp, SUM(clicks) AS clk)
      ORDER BY SUM(impressions) DESC LIMIT 5) AS top_urls
  FROM base
  GROUP BY query
)
SELECT * FROM q WHERE url_cnt > 1 ORDER BY url_cnt DESC, impressions DESC;

Lista top_urls wskaże, które adresy kanibalizują się na największym wolumenie. Dalsza diagnoza to grupowanie po katalogach i typach stron, by ocenić, czy problem wynika z architektury (duplikaty kategorii), szablonu (filtrowanie/tagi) czy treści (zbyt szeroki dobór słów kluczowych).

Segmentacja katalogów i typów stron

Techniczne wąskie gardła bywają lokalne: konkretny katalog, paginacja, strony z parametrami. Zbuduj kolumnę directory i slug bez parametrów:

WITH d AS (
  SELECT date, url, REGEXP_EXTRACT(url, r’^https?://[^/]+/([^/?#]+)/?’) AS directory,
    REGEXP_EXTRACT(url, r’^[^?]+’) AS url_clean,
  clicks, impressions
  FROM `project.dataset.searchdata_url_impressions`
  WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 60 DAY)
)
SELECT directory, SUM(clicks) AS clicks, SUM(impressions) AS impressions,
  SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr
FROM d GROUP BY directory ORDER BY impressions DESC;

Warianty analizy:

• Parametry: wykryj nadreprezentację URL-i z „?” i oceń ich udział w wyświetleniach. Jeśli parametrów jest dużo, to znak, że trzeba lepiej zarządzać crawl (noindex, canonicale, blokady).
• Paginacja: wykryj „/page/” lub „?page=”. Spadający CTR i pozycja na dalszych stronach paginacji to częsty problem, który rozwiążesz poprzez lepsze linkowanie i konsolidację sygnałów.
• Canonical: gdy te same treści mają wiele ścieżek, rozważ konsolidację i przekierowania 301.

Optymalizacja snippetów: CTR, zapytania i urządzenia

Skup się na stronach z wysokimi wyświetleniami i niskim CTR – to szybkie wygrane. Przykładowe zapytanie:

SELECT url, SUM(impressions) AS imp, SUM(clicks) AS clk,
  SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr,
  APPROX_TOP_COUNT(query, 5) AS top_queries
FROM `project.dataset.searchdata_url_impressions`
WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
GROUP BY url HAVING imp > 1000 AND ctr < 0.02
ORDER BY imp DESC;

Następnie rozbij wynik po device, by zweryfikować, czy problem dotyczy mobile (często tytuły przycięte, brak istotnej informacji w pierwszych znakach) czy desktop. Dodaj też wymiar searchAppearance: jeśli URL ma rich result, a CTR nie rośnie, sprawdź poprawność znaczników strukturalnych i wyników rozszerzonych.

Międzynarodowe i sygnały indeksacja

W ujęciu międzynarodowym zacznij od przeglądu krajów vs języków/katalogów. Jeżeli katalog /pl/ zbiera dużo ruchu z US, to znak, że hreflangi lub geotargetowanie wymagają uwagi. Przykład:

SELECT country, REGEXP_EXTRACT(url, r’^https?://[^/]+/([^/]+)/’) AS lang_dir,
  SUM(impressions) AS imp, SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr
FROM `project.dataset.searchdata_url_impressions`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY country, lang_dir ORDER BY imp DESC;

Sygnały stanu indeksacji pośrednio widać przez nagłe spadki wyświetleń oraz znikanie grup URL-i z wyników. Połącz dane z sitemapą: które adresy ważne dla biznesu (lastmod w ostatnich 30 dniach) mają 0 wyświetleń? To kandydaci do audytu renderowania, blokad robots.txt lub problemów z canonicalem.

WITH s AS (
  SELECT url FROM `project.analytics.sitemap_urls` WHERE lastmod >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
), g AS (
  SELECT url, SUM(impressions) AS imp FROM `project.dataset.searchdata_url_impressions`
  WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) GROUP BY url
)
SELECT s.url FROM s LEFT JOIN g USING(url) WHERE IFNULL(g.imp, 0) = 0;

Automatyzacja, monitoring i wizualizacja

Harmonogramy, widoki materializowane i kontrola kosztów

Utwórz warstwę pośrednią (analytics) z widokami materializowanymi dla najczęstszych pytań: dzienna widoczność per katalog, per device, per kraj. Widoki materializowane w BigQuery przyspieszą odczyt i obniżą koszty, bo przechowują preagregaty. Harmonogramy uruchamiaj raz dziennie po dostarczeniu nowej partycji z GSC (zwykle w ciągu 48h). Dodatkowo możesz włączyć limity kosztów zapytań na poziomie projektu, aby chronić budżet.

Alerty anomalii i regresji jakości

Prosty detektor anomalii z użyciem Z-score’ów wystarczy, by złapać regresję po wdrożeniu. Przykład: monitoruj dzienne SUM(clicks) w największych katalogach i sygnalizuj odchylenie większe niż 3σ.

WITH series AS (
  SELECT date, REGEXP_EXTRACT(url, r’^https?://[^/]+/([^/?#]+)/?’) AS directory,
  SUM(clicks) AS clicks
  FROM `project.dataset.searchdata_url_impressions`
  WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 120 DAY)
  GROUP BY date, directory
), stats AS (
  SELECT directory, AVG(clicks) AS avg_c, STDDEV_SAMP(clicks) AS sd_c FROM series GROUP BY directory
)
SELECT s.date, s.directory, s.clicks,
  (s.clicks – st.avg_c) / NULLIF(st.sd_c, 0) AS z
FROM series s JOIN stats st USING(directory)
WHERE ABS((s.clicks – st.avg_c) / NULLIF(st.sd_c, 0)) >= 3
ORDER BY s.date DESC;

Analogicznie monitoruj CTR, pozycję, udział brand/non-brand oraz obecność searchAppearance. Po wdrożeniach kluczowych komponentów frontu połącz wyniki z danymi o wydajności – CrUX – aby wykryć skutki uboczne.

Dashboardy i notatki kontekstowe

Po stronie BI (np. Looker Studio) stosuj proste modele danych: jedna tabela dzienna z kluczami (date, directory, device, country, page_type) i metrykami (clicks, impressions, ctr, position). Dodaj tabelę „notes” z wydarzeniami (wdrożenia, migracje, komunikaty w GSC), aby wszystkie anomalie dało się powiązać z kontekstem. Pamiętaj, by w raportach zawsze filtrować po dacie – to nie tylko oszczędność, ale i poprawna interpretacja sezonowości.

Łączenie z wydajnością i ruchem płatnym

Warstwa techniczna rzadko działa w izolacji. Łącz dane GSC z CrUX, by ocenić wpływ zmian frontendu na widoczność organiczną, i z danymi płatnymi (np. kosztami brand), aby podejmować decyzje o priorytetach. Przykładowo: jeśli wdrożenie poprawiło LCP i FID w grupie URL-i, a mimo to CTR nie poszedł w górę, problem może tkwić w meta tagach lub konkurencyjności SERP (np. nowe wyniki rozszerzone). W takich miejscach testuj mikro-zmiany tytułów i opisów.

Na koniec praktyczna checklista dla trwałej wartości:

• Stabilny pipeline: monitoruj, czy codzienna partycja dotarła; brak nowej daty to czerwona flaga.
• Kontrola jakości: słowniki i reguły (brand/non-brand, mapowanie typów stron) trzymaj w repozytorium Git; zmiany rób przez merge requesty.
• Reprodukowalność wniosków: zapisuj zapytania jako widoki lub procedury, nie tylko jako notatki w IDE.
• Higiena danych: usuwaj stare, nieużywane tabele tymczasowe, taguj zasoby i dokumentuj kluczowe pola.

Dzięki temu BigQuery staje się nie tylko magazynem, ale fundamentalnym silnikiem diagnoz i optymalizacji, który wspiera pracę zespołu technicznego i kontentowego przez cały cykl życia zmian.