Search Console w BigQuery: masowy eksport i sześć zapytań SQL, które warto zapisać

Masowy eksport Search Console do BigQuery zdejmuje dwa limity interfejsu: tysiąc wierszy na raport i brak jednej tabeli, w której zapytanie stoi obok adresu URL. Konfiguracja zajmuje kwadrans, pierwsze dane przychodzą w ciągu 48 godzin, a na małym serwisie rachunek zamyka się w darmowym progu. Poniżej procedura, struktura tabel, koszt i sześć zapytań SQL na start.

W skrócie

• Masowy eksport (bulk data export) Google udostępnił w lutym 2023 roku: działa tylko do przodu, nie uzupełnia historii.
• Codziennie zapisuje trzy tabele w wybranym zbiorze danych, w tym jedną z parą zapytanie plus adres URL.
• Pozycji nie ma w żadnej kolumnie: liczy się ją jako sum_position / impressions + 1.
• Pierwsze 10 GiB składowania i 1 TiB przeskanowanych danych miesięcznie są darmowe, a tabele są partycjonowane po data_date.
• Zanonimizowane zapytania trafiają do eksportu, ale z pustym polem query i flagą is_anonymized_query.

Co daje masowy eksport, czego nie da interfejs

Interfejs pokazuje maksymalnie 1000 wierszy na raport i nie pozwala zestawić wymiaru zapytania z wymiarem strony w jednym widoku. Eksport usuwa oba ograniczenia: dostajecie surowe wiersze dzienne, a agregację robicie w SQL.

Drugi zysk to retencja. Interfejs i API trzymają 16 miesięcy, po czym dane znikają. W BigQuery zostają tak długo, jak płacicie za składowanie, więc po dwóch latach macie porównanie rok do roku, którego w panelu nie ma. Jeśli zaczynacie od podstaw, warto najpierw przejść przewodnik po raportach Search Console, bo te same metryki w SQL nazywają się inaczej.

Źródło	Limit wierszy	Retencja	Para zapytanie plus URL
Interfejs	1000 na raport	16 miesięcy	nie
Search Analytics API	25 000 na żądanie, stronicowanie	16 miesięcy	tak, kosztem wielu żądań
Masowy eksport	brak	dowolna	tak, w jednej tabeli

Czego eksport nie naprawia: nie odzyskuje zapytań ukrytych z powodów prywatności, nie zawiera danych o indeksowaniu ani Core Web Vitals i nie skraca zwłoki dwóch dni.

Konfiguracja projektu i uprawnień krok po kroku

1. Założyć projekt w Google Cloud i włączyć rozliczenia. Bez aktywnego konta rozliczeniowego eksport nie wystartuje, nawet w darmowym progu.
2. Włączyć BigQuery API w sekcji API i usługi.
3. W zakładce IAM dodać konto usługi search-console-data-export@system.gserviceaccount.com i nadać mu dwie role: BigQuery Job User oraz BigQuery Data Editor.
4. W Search Console wejść w Ustawienia, potem Masowy eksport danych, wpisać identyfikator projektu, nazwę zbioru danych (domyślnie searchconsole) i lokalizację.
5. Zatwierdzić. Pierwszy zrzut pojawia się w ciągu 48 godzin, a licznik danych startuje od dnia konfiguracji.

Lokalizację zbioru wybieracie raz: zmiana wymaga skasowania i ponownego podłączenia eksportu. Dla serwisu polskojęzycznego sensowny jest region UE. Szczegóły uruchomienia opisuje oryginalne ogłoszenie Google Search Central.

Struktura tabel i najważniejsze kolumny

Eksport tworzy trzy obiekty: dwie tabele faktów i dziennik, który mówi, czy dany dzień został domknięty.

• searchdata_site_impression: agregat na poziomie całej witryny z kolumnami query, country, device, search_type, impressions, clicks, sum_top_position.
• searchdata_url_impression: ten sam zestaw plus url i sum_position, plus kilkadziesiąt flag opisujących wygląd wyniku, od is_review_snippet do is_merchant_listings.
• ExportLog: po jednym wierszu na wyeksportowaną partycję, z kolumnami agenda, data_date i publish_time.

Najczęstsza pomyłka na start: nie ma tu kolumny z pozycją. Jest suma pozycji liczona od zera, więc średnią wylicza się jako SUM(sum_position) / SUM(impressions) + 1, a pominięcie jedynki zaniża wynik o jedno miejsce. Sama pozycja znaczy zresztą coraz mniej, odkąd Search Console spłaszczył AI Overviews do jednego bloku.

Realny koszt składowania i zapytań

Na modelu on demand BigQuery liczy 6,25 USD za każdy przeskanowany TiB, przy pierwszym TiB miesięcznie bezpłatnie. Składowanie aktywne to około 0,02 USD za GiB miesięcznie, z progiem 10 GiB za darmo.

Serwis generujący 50 tysięcy wierszy dziennie w tabeli URL zmieści się po roku w pojedynczych gigabajtach, czyli w darmowym progu. Rachunek rodzi się dopiero przy zapytaniach skanujących całą tabelę, dlatego każde filtrujcie po data_date: tabele są partycjonowane po tej kolumnie, a klauzula WHERE na partycji zmniejsza skan o rzędy wielkości. Dobre praktyki kosztowe zebrane są w dokumentacji BigQuery.

Sześć zapytań SQL na dobry początek

Przykłady zakładają zbiór searchconsole w projekcie twoj-projekt, podmieńcie identyfikator.

1. Najmocniejsze zapytania z ostatnich 28 dni. Punkt wyjścia do każdej dalszej analizy.

SELECT query, SUM(impressions) AS wyswietlenia, SUM(clicks) AS klikniecia,
       SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr
FROM `twoj-projekt.searchconsole.searchdata_site_impression`
WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
  AND is_anonymized_query = FALSE
GROUP BY query ORDER BY klikniecia DESC LIMIT 200;

2. Poprawnie liczona średnia pozycja na adres URL. Tutaj widać, czemu jedynka na końcu ma znaczenie.

SELECT url, SUM(impressions) AS wyswietlenia,
       SUM(sum_position) / SUM(impressions) + 1 AS srednia_pozycja
FROM `twoj-projekt.searchconsole.searchdata_url_impression`
WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
GROUP BY url HAVING wyswietlenia > 100
ORDER BY wyswietlenia DESC;

3. Luka skuteczności. Zapytania w pierwszej dziesiątce, które mają wyświetlenia i prawie nie mają kliknięć, to materiał na przepisanie tytułu i opisu.

SELECT query, SUM(impressions) AS wyswietlenia, SUM(clicks) AS klikniecia,
       SUM(sum_top_position) / SUM(impressions) + 1 AS pozycja
FROM `twoj-projekt.searchconsole.searchdata_site_impression`
WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
GROUP BY query
HAVING wyswietlenia > 200 AND pozycja < 11
   AND SAFE_DIVIDE(klikniecia, wyswietlenia) < 0.01
ORDER BY wyswietlenia DESC;

4. Kanibalizacja. Jedno zapytanie obsługiwane przez kilka adresów w tym samym okresie.

SELECT query, COUNT(DISTINCT url) AS liczba_url,
       STRING_AGG(DISTINCT url LIMIT 5) AS przyklady
FROM `twoj-projekt.searchconsole.searchdata_url_impression`
WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
  AND query IS NOT NULL
GROUP BY query HAVING liczba_url >= 3
ORDER BY liczba_url DESC;

5. Udział zapytań ukrytych. Jeśli wychodzi ponad połowa wyświetleń, każda analiza słów kluczowych na tym serwisie opiera się na mniejszości danych.

SELECT data_date,
       SUM(IF(is_anonymized_query, impressions, 0)) AS ukryte,
       SUM(impressions) AS razem
FROM `twoj-projekt.searchconsole.searchdata_site_impression`
WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY data_date ORDER BY data_date;

6. Spadki tydzień do tygodnia na poziomie strony. Zapytanie, które najczęściej trafia potem na pulpit zarządu.

SELECT url,
       SUM(IF(data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY), clicks, 0)) AS biezacy,
       SUM(IF(data_date < DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY), clicks, 0)) AS poprzedni
FROM `twoj-projekt.searchconsole.searchdata_url_impression`
WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY)
GROUP BY url HAVING poprzedni > 20 AND biezacy < poprzedni * 0.7
ORDER BY poprzedni DESC;

Każde z nich ma odpowiednik w filtrach panelu, tylko wolniejszy i obcięty do tysiąca wierszy. Podobne wzorce dopasowania opisuje tutorial o filtrach regex w Search Console.

Podłączenie wyniku pod Looker Studio

Nie podłączajcie tabel surowych wprost do raportu: każde odświeżenie wykresu uruchamia zapytanie, a przy tabeli URL to skan kilkuset megabajtów na jedno kliknięcie filtra.

Lepszy układ: tabela zbiorcza odświeżana raz na dobę zaplanowanym zapytaniem, z gotowymi kolumnami (data, URL, zapytanie, kliknięcia, wyświetlenia, pozycja), i dopiero ona jako źródło raportu. Pamięć podręczną w Looker Studio ustawcie na 12 godzin, skoro dane przychodzą raz dziennie. Gotowy schemat raportu na takiej warstwie pokazujemy w materiale o tym, jak zbudować raport widoczności w AI w Looker Studio.

Najczęstsze błędy przy pierwszym uruchomieniu

1. Oczekiwanie historii. Eksport nie uzupełnia przeszłości, więc włączcie go, zanim będzie potrzebny.
2. Odebranie uprawnień kontu usługi. Porządki w IAM zatrzymują eksport, a powiadomienie Google łatwo przeoczyć. Kontrolujcie ExportLog.
3. Zapytania bez filtra po data_date. Najszybszy sposób na przepalenie darmowego TiB w jeden wieczór.
4. Liczenie pozycji bez dodania jedynki. Raport wygląda wtedy o jedno miejsce lepiej niż rzeczywistość.
5. Mieszanie tabeli witryny z tabelą URL. Sumy wyświetleń w obu nie są równe, bo wiersze bez przypisanego adresu występują tylko w pierwszej.

FAQ

Czy masowy eksport jest darmowy?

Sam eksport Google udostępnia bez opłat, płacicie jedynie za BigQuery. Przy pierwszych 10 GiB składowania aktywnego i pierwszym TiB przeskanowanych danych w miesiącu rachunek wynosi zero, a mały lub średni serwis mieści się w tych progach latami. Konto rozliczeniowe w Google Cloud musi jednak być aktywne, bo bez niego konfiguracja nie przejdzie.

Czy w eksporcie zobaczę zapytania ukryte w interfejsie?

Nie. Zapytania pominięte z powodów prywatności trafiają do tabeli searchdata_site_impression, ale z pustym polem query i flagą is_anonymized_query ustawioną na prawdę. Zyskujecie więc wiedzę, ile wyświetleń kryje się w tej grupie, co pozwala uczciwie opisać zasięg analizy, ale samych fraz nie odzyskacie żadnym zapytaniem SQL.

Ile czekam na pierwsze dane?

Pierwsza partycja pojawia się zwykle w ciągu 48 godzin od zatwierdzenia konfiguracji. Dalej eksport działa raz na dobę, z typowym opóźnieniem dwóch dni względem dnia bieżącego, dokładnie takim samym jak w interfejsie. Stan domknięcia każdej daty sprawdzicie w tabeli ExportLog, zanim zaczniecie liczyć cokolwiek na świeżej partycji.

Czym to się różni od Search Analytics API?

API zwraca dane na żądanie, z limitem 25 tysięcy wierszy na zapytanie i retencją 16 miesięcy, więc pełną parę zapytanie plus URL trzeba składać z wielu stronicowanych wywołań. Eksport zapisuje te same wiersze raz na dobę do tabeli, którą pytacie dowolnie długo i dowolnie głęboko. API jest wygodniejsze do punktowych odczytów, eksport do analizy historycznej.

Mogę wyeksportować kilka witryn do jednego zbioru danych?

Tak, kilka usług może kierować eksport do tego samego projektu i zbioru. Wiersze rozróżnia kolumna site_url, dlatego w zapytaniach dla pojedynczego serwisu trzeba ją wtedy filtrować. Przy agencji z wieloma klientami czystsze bywa jednak jedno zbiór na usługę, bo upraszcza nadawanie dostępów i rozliczanie kosztów.

Co dalej

Kolejność jest prosta: włączyć eksport dziś, nawet jeśli pierwsze analizy zrobicie za kwartał, bo historia nie nadrobi się sama. Zanim zbudujecie własne zapytania, ustalcie, które raporty prowadzą do decyzji, a to najlepiej porządkuje przewodnik po raportach Google Search Console. Kolejny krok to spięcie danych z ruchem i konwersjami w jednym miejscu, czyli zestawienie eksportu z danymi analitycznymi po stronie serwisu.