Integracja Google Search Console z bazą danych Google BigQuery. Poradnik krok po kroku
03.2023 | Google BigQuery | Google Search Console | Vertex AI
Wyraźnie widać, że Google kładzie duży nacisk na rozwój platformy Google Cloud m.in. poprzez integrację różnych usług, wokół tej właśnie platformy. Wyrazem tego było udostępnienie integracji na linii Google Analytics 4 oraz BigQuery, a obecnie kolejnym krokiem jest zapowiedziana integracja na linii Google Search Console i wspomniane BigQuery.
Google Search Console, to usługa wspierająca pracę osób związanych z marketingiem internetowym oraz optymalizacją serwisów internetowych, w kontekście pozycji serwisu w wynikach wyszukiwania. Search Console pomaga nam poznać to, na jakie frazy nasz serwis pojawia się w wyszukiwarce Google, na jakiej pozycji się pokazuje i jaki odsetek użytkowników decyduje się na kliknięcie w nasz link => tym samym przejście na naszą stronę internetową.
Do obecnego momentu, każda większa i cykliczna analiza wymagała wykorzystania Google Search Console API do pobrania danych, tak aby mozliwa była analiza prowadzona w zewnętrznym oprogramowaniu np. Looker Studio.
To jest już jednak przeszłość, bowiem właśnie dostaliśmy dostęp do zrzutu danych z Search Console, bezpośrednio do bazy BigQuery. A jeżeli dane są już w BigQuery, to możemy bardzo łatwo połączyć się do niej narzędziami BI typu Microsoft Power BI czy Tableau.
Zaczynamy zatem integrację.
Integracja składa się z dwóch warstw:
- Google Cloud Platform => tutaj musimy przygotować projekt, w którym będzie usytuowana nasza baza BigQuery
- Google Search Console => tutaj musimy wykonać integrację, czyli zainicjować proces eksportu danych
W kolejnych punktach przejdziemy przez następujące zagadnienia:
- Google Cloud Platform
- Google Search Console
- Google BigQuery
Google Cloud Platform
1/ Logowanie do Google Cloud Platform i utworzenie nowego projektu
Zacznijmy od zalogowania się do Google Cloud Platform i od utworzenia nowego projektu.
Logujemy się zatem na stronie => https://cloud.google.com/
Następnie wybieramy okienko projektów i klikamy opcję: NEW PROJECT.
Wpisujemy nazwę projektu - Project name - pamiętajmy, żeby nazwa była opisowa i pozwoliła na łatwą identyfikację przeznaczenia projektu.
Wybieramy również organizację, do której ma należeć nowo utworzony projekt.
2/ Projekt gotowy - utworzony
Po kliknięciu wyżej widocznego przycisku CREATE nasz projekt zostaje utworzony, a my przekierowani na stronę startową projektu.
Ważne: zapisujemy sobie identyfikator widoczny pod nazwą Project ID. Będzie nam jeszcze potrzebny w dalszej konfiguracji, w interfejsie Search Console.
3/ Aktywacja BigQuery API
W wyszukiwarce, w górnej części strony, wpisujemy frazę "API", tak aby przejść do strony:
=> APIs & Services
Możemy tę stronę również wybrać bezpośrednio z nawigacji po lewej stronie.
Na stronie API & Services, wpierw przewijamy do dolnej części strony, gdzie widzimy aktywowane usługi API. Sprawdzamy, czy jest tam widoczne BigQuery API.
Jeżeli jest widoczne to przechodzimy dalej, czyli do punktu 4 (dodanie bilingu do projektu) niniejszej instrukcji.
Jeżeli jednak nie ma na liście aktywnych "BigQuery API", to przewijamy stronę do górnej części i tam klikamy opcję ENABLE APIS AND SERVICES.
Po kliknięciu, zostajemy przekierowani do strony API Library, gdzie musimy odnaleźć interesującą nas pozycję. Dlatego też, w widocznej wyszukiwarce, wpisujemy frazę:
=> BigQuery API
I klikamy enter, tak aby zobaczyć wyniki wyszukiwania.
W wynikach wyszukiwania klikamy widoczną poniżej pozycję - BigQuery API:
I dalej aktywujemy API klikając przycisk ENABLE
Podsumowując: mamy już założony projekt i aktywowane BigQuery API.
Pozostają jeszcze dwa punkty, do realizacji, w ramach Cloud Console:
- Weryfikacja / dopisanie bilingu
- Dopisanie Service Account
4/ Dodajemy biling do projektu
Przypisanie bilingu oznacza, że musimy podać dane karty płatniczej/kredytowej, z której będą pobierane opłaty na rzecz Google. BigQuery jest usługą płatną i należy o tym pamiętać, że uruchomienie integracji pociągnie za sobą koszty finansowe.
Dygresja => Ile może kosztować integracja BigQuery i Search Console?
Nie jest to pytanie, na które można odpowiedzieć w sposób "prosty", gdyż koszt cennik BigQuery jest skomponowany z dwóch składowych (koszt składowania danych + koszt wykonanych zapytań), a także ściśle skorelowany z wolumenem danych.
Pewnym przybliżeniem wielkości kosztów może być artykuł, w którym opisuję, ile może kosztować integracja na linii Google Analytics 4 i BigQuery. Jeżeli jesteś zainteresowany, to sprawdź tutaj.
Aby dodać biling do naszego projektu, to z lewego menu wybieramy pozycję Biling.
Jeżeli nasz projekt nie ma przypisanego bilingu, to wybieramy opcję "LINK A BILLING ACCOUNT".
Po kliknięciu wyżej pokazanego linku, będziemy mieli dalej opcję wyboru lub utworzenia konta bilingowego. W poniższym przykładzie konto bilingowe jest już gotowe, a zatem należy je tylko przypisać do projektu. W Twoim przypadku, może być jednak konieczne przejście przez proces dodania karty kredytowej.
5/ Dodanie użytkownika typu Service Account
Zgodnie z dokumentacją Google, do naszego projektu musimy dopisać użytkownika, typu service account:
- search-console-data-export@system.gserviceaccount.com
W tym celu przechodzimy sekcji:
=> IAM & Admin => IAM
W sekcji tej klikamy przycisk +GRANT ACCESS
I dopisujemy użytkownika search-console-data-export@system.gserviceaccount.com z uprawnieniami:
- BigQuery Job User
- BigQuery Data Editor
Po kliknięciu SAVE, konto pojawia się na liście użytkowników.
Całość konfiguracji po stronie Google Cloud mamy zatem zakończoną. Teraz przechodzimy do dalszej konfiguracji, po stronie Search Console.
Search Console
1/ Inicjacja eksportu danych
Logujemy się do Search Console, korzystając z adresu:
Następnie przechodzimy do sekcji Settings, znajdującej się w nawigacji głównej (lewa strona serwisu):
W dostępnych opcjach, odszukujemy Bulk data export.
Po kliknięciu Bulk data export, przechodzimy do konfiguracji naszego eksportu.
Cloud mamy już skonfigurowany, dlatego też nie klikamy w przycisk Review instructions.
Wypełniamy natomiast kolejne pola tzn.
- Cloud project ID - numer (ID) naszego projektu z Google Cloud Console (zapisany wcześniej)
- Dataset location - region, w którym zostanie uruchomiona nasza instancja BigQuery. Rekomenduję np. Europe-west-3 czyli Frankfurt DE.
Uwaga: cena usługi BigQuery jest różna w zależności od wybranego regionu. Nie są to jednostkowo duże różnice, ale przy ilościach hurtowych, w długim okresie czasu mogą być widoczne.
Przykład, dla kosztu składowania 1GB danych (cena dla typu active local storage):
- Madrid europe-southwest1 - $0.029 per GB
- Finland europe-north - $0.02 per GB
- London europe-west2 - $0.023 per GB
- Frankfurt europe-west3 - $0.023 per GB
- Zurich europe - west6 $0.025 per GB
Po kliknięciu przycisku Continue, zobaczymy ekran ostrzeżenia, aby ponownie sprawdzić dane, gdyż po zainicjowaniu eksportu, jedyna możliwość zmiany docelowego projektu / strefy, to będzie konieczność wykonania go od nowa.
Klikamy Set up export. Rozpoczyna się etap inicjacji. Może to trwać kilka minut.
Zakończenie konfiguracji powinno być zasygnalizowane komunikatem widzianym poniżej => Setup completed successfully.
Po zamknięciu okna (klikamy Done) widzimy link Go to project. Kliknijmy.
2/ Przejście (ponowne) do Google Cloud
Link Go to project przekieruje nas bezpośrednio do Google Cloud z aktywnym naszym projektem i otwartą kartą BigQuery. Na widocznej liście zobaczymy naszą instancję bazy BigQuery.
W trakcie mojej instalacji, zauważyłem pewną ciekawostkę - chwilę po zainicjowaniu bazy, pojawiła się tabela z przedrostkiem temp_ i dwoma polami question i answer. Było to widoczne, przez chwilę po czym zniknęło.
Mamy więc pustą bazę i czekamy na pojawienie się niej pierwszych danych.
Google BigQuery
Po około 5 godzinach oczekiwania, utworzyły się dwie tabele, w których pojawiły się dane.
1/ Struktura (schema) tabel
Przyjrzyjmy się strukturze tabel, bo to nam pokaże jakich danych możemy się spodziewać. Mamy dwie tabele:
- searchconsole.searchdata_site_impression
- searchconsole.searchdata_url_impression
Tabela searchconsole.searchdata_site_impression
Dla tabeli searchconsole.searchdata_site_impression schemat danych wygląda następująco:
| Field name | Type | Mode |
| data_date | DATE | NULLABLE |
|
site_url |
STRING |
NULLABLE |
|
query |
STRING |
NULLABLE |
|
is_anonymized_query |
BOOLEAN |
NULLABLE |
|
country |
STRING |
NULLABLE |
|
search_type |
STRING |
NULLABLE |
|
device |
STRING |
NULLABLE |
|
impressions |
INTEGER |
NULLABLE |
|
clicks |
INTEGER | NULLABLE |
| sum_top_position | INTEGER | NULLABLE |
Tabela searchconsole.searchdata_url_impression
Dla tabeli searchconsole.searchdata_url_impression schemat danych wygląda następująco:
| Field name | Type | Mode |
| data_date | DATE | NULLABLE |
| site_url | STRING | NULLABLE |
| url | STRING | NULLABLE |
| query | STRING | NULLABLE |
| is_anonymized_query | BOOLEAN | NULLABLE |
| is_anonymized_discover | BOOLEAN | NULLABLE |
| country | STRING | NULLABLE |
| search_type | STRING | NULLABLE |
| device | STRING | NULLABLE |
| is_amp_top_stories | BOOLEAN | NULLABLE |
| is_amp_blue_link | BOOLEAN | NULLABLE |
| is_job_listing | BOOLEAN | NULLABLE |
| is_job_details | BOOLEAN | NULLABLE |
| is_tpf_qa | BOOLEAN | NULLABLE |
| is_tpf_faq | BOOLEAN | NULLABLE |
| is_tpf_howto | BOOLEAN | NULLABLE |
| is_weblite | BOOLEAN | NULLABLE |
| is_action | BOOLEAN | NULLABLE |
| is_events_listing | BOOLEAN | NULLABLE |
| is_events_details | BOOLEAN | NULLABLE |
| is_search_appearance_android_app | BOOLEAN | NULLABLE |
| is_amp_story | BOOLEAN | NULLABLE |
| is_amp_image_result | BOOLEAN | NULLABLE |
| is_video | BOOLEAN | NULLABLE |
| is_organic_shopping | BOOLEAN | NULLABLE |
| is_review_snippet | BOOLEAN | NULLABLE |
| is_special_announcement | BOOLEAN | NULLABLE |
| is_recipe_feature | BOOLEAN | NULLABLE |
| is_recipe_rich_snippet | BOOLEAN | NULLABLE |
| is_subscribed_content | BOOLEAN | NULLABLE |
| is_page_experience | BOOLEAN | NULLABLE |
| is_practice_problems | BOOLEAN | NULLABLE |
| is_math_solvers | BOOLEAN | NULLABLE |
| is_translated_result | BOOLEAN | NULLABLE |
| is_edu_q_and_a | BOOLEAN | NULLABLE |
| impressions | INTEGER | NULLABLE |
| clicks | INTEGER | NULLABLE |
| sum_position | INTEGER | NULLABLE |
2/ Przegląd danych
Zobaczmy jakie dane odkładają się w obu wymienionych tabelach.
=> po wspomnianych 5 godzinach zauważyłem dane za okres 2 dni wstecz. Czyli od 01.03 (początku miesiąca). A zatem dane są zbierane tylko od momentu integracji.
Ok. Ale zapewne pojawia się pytania => co z danymi historycznymi?
Odpowiedź daje Google, w swojej dokumentacji, która mówi:
If you want to see historical data that precedes your initial setup, use the Search Console API or the reports.
A zatem, jeżeli potrzebujemy danych historycznych, to możemy je dograć np. z wykorzystaniem API.
2.3/ Pobranie danych z wykorzystaniem prostego zapytania
Przykład prostego zapytania, pobierającego wszystkie rezultaty z każdej z wymienionych tabel:
Uwaga: w realnych warunkach produkcyjnych, przy dużych tabelach nigdy nie wykonuj zapytań typu "*" i nie uważaj klauzuli LIMIT za dobre rozwiązanie, do ograniczenia kosztów zapytania. Tak nie jest - zapytania powinny być precyzyjne, tylko o ten fragment danych który potrzebujesz i tylko z okresu, który Cię interesuje.
SELECT * FROM `neural-qwerty.searchconsole.searchdata_url_impression` WHERE data_date = "2023-03-01" LIMIT 10
Wyniki, jakie otrzymałem dla tabeli searchconsole.searchdata_site_impression wyglądają następująco (wyniki są pokazane po zmianie miejsc wierszy z kolumnami, dla większej czytelności):
| data_date | 2023-03-01 | 2023-03-01 | 2023-03-01 | 2023-03-01 |
| site_url | https://www.surowiecki.org/ | https://www.surowiecki.org/ | https://www.surowiecki.org/ | https://www.surowiecki.org/ |
| query | macierz jednostkowa | macierz jednostkowa | ||
| is_anonymized_query | TRUE | TRUE | FALSE | FALSE |
| country | pol | pol | ind | tur |
| search_type | IMAGE | WEB | WEB | WEB |
| device | MOBILE | MOBILE | MOBILE | MOBILE |
| impressions | 1 | 2 | 1 | 1 |
| clicks | 0 | 0 | 0 | 0 |
| sum_top_position | 82 | 11 | 41 | 40 |
Wyniki, jakie otrzymałem dla tabeli searchconsole.searchdata_url_impression wyglądają następująco (wyniki są pokazane po zmianie miejsc wierszy z kolumnami, dla większej czytelności):
| data_date | 2023-03-01 | 2023-03-01 | 2023-03-01 | 2023-03-01 |
| site_url | https://www.surowiecki.org/ | https://www.surowiecki.org/ | https://www.surowiecki.org/ | https://www.surowiecki.org/ |
| url | https://www.surowiecki.org/blog/analytics-data-api-r-czesc-2/ | https://www.surowiecki.org/blog/python-clickup-integracja/ | https://www.surowiecki.org/blog/flesz/django-tips-podmiana-bazy-danych/ | https://www.surowiecki.org/oferta/implementacja-bi |
| query | google analytics 4 api | clickup python api | django baza danych | kokpit zarządczy |
| is_anonymized_query | FALSE | FALSE | FALSE | FALSE |
| is_anonymized_discover | FALSE | FALSE | FALSE | FALSE |
| country | can | dnk | fin | fra |
| search_type | WEB | WEB | WEB | WEB |
| device | MOBILE | DESKTOP | DESKTOP | DESKTOP |
| is_amp_top_stories | FALSE | FALSE | FALSE | FALSE |
| is_amp_blue_link | FALSE | FALSE | FALSE | FALSE |
| is_job_listing | FALSE | FALSE | FALSE | FALSE |
| is_job_details | FALSE | FALSE | FALSE | FALSE |
| is_tpf_qa | FALSE | FALSE | FALSE | FALSE |
| is_tpf_faq | FALSE | FALSE | FALSE | FALSE |
| is_tpf_howto | FALSE | FALSE | FALSE | FALSE |
| is_weblite | FALSE | FALSE | FALSE | FALSE |
| is_action | FALSE | FALSE | FALSE | FALSE |
| is_events_listing | FALSE | FALSE | FALSE | FALSE |
| is_events_details | FALSE | FALSE | FALSE | FALSE |
| is_search_appearance_android_app | FALSE | FALSE | FALSE | FALSE |
| is_amp_story | FALSE | FALSE | FALSE | FALSE |
| is_amp_image_result | FALSE | FALSE | FALSE | FALSE |
| is_video | FALSE | FALSE | FALSE | FALSE |
| is_organic_shopping | FALSE | FALSE | FALSE | FALSE |
| is_review_snippet | FALSE | FALSE | FALSE | FALSE |
| is_special_announcement | FALSE | FALSE | FALSE | FALSE |
| is_recipe_feature | FALSE | FALSE | FALSE | FALSE |
| is_recipe_rich_snippet | FALSE | FALSE | FALSE | FALSE |
| is_subscribed_content | FALSE | FALSE | FALSE | FALSE |
| is_page_experience | FALSE | FALSE | FALSE | FALSE |
| is_practice_problems | FALSE | FALSE | FALSE | FALSE |
| is_math_solvers | FALSE | FALSE | FALSE | FALSE |
| is_translated_result | FALSE | FALSE | FALSE | FALSE |
| is_edu_q_and_a | FALSE | FALSE | FALSE | FALSE |
| impressions | 1 | 1 | 1 | 1 |
| clicks | 0 | 0 | 0 | 0 |
| sum_position | 99 | 20 | 9 | 59 |
3/ Jak dostać się do danych w Google BigQuery?
Do danych zgromadzonych w BigQuery można dostać się np. korzystając z narzędzi BI. Poniżej znajduje się krótki przykład, jak wykorzystać Tableau do połączenia się z tabelami Search Console.
Krok 1. Ustanowienie połączenia
Uruchamiamy Tableau, wybieramy konektor Google BigQuery i z listy dostępnych metod autentykacji, wybieramy tę właściwą dla naszego użytkownika (jeżeli nie wiesz jaką, to wybierz Sign in using Oauth - jest większe prawdopodobieństwo, że właśnie tak masz się łączyć).
Krok 2. Wybór tabeli
Po poprawnym połączeniu, Tableau wyświetli nam "konfigurator", w którym musimy kolejno doprecyzować skąd chcemy dane pobierać.
W tym celu przechodzimy przez kolejne punkty:
- (1) Wybieramy projekt bilingowy - projekt do którego jest podpięty biling oraz nasza instancja BigQuery
- (2) Wybieramy projekt z danymi BigQuery - z dużą pewnością jest to ten sam projekt, co wybrany wyżej
- (3) Wybieramy konkretny dataset, czyli bazę w której są nasze tabele. Domyślnie jest to searchconsole
- (4) Wybieramy konkretną tabelę do analizy
Krok 3. Przestrzeń robocza Tableau i wykonanie konkretnego raportu
Po wybraniu tabeli - w naszym przypadku, to searchconsole.searchdata_url_impression, zobaczymy dimensions and metrics, na bazie których można budować dowolne raporty.
3.2/ Wykorzystanie Vertex AI i języka R
Do danych zgromadzonych w BigQuery można również dostać się programistycznie. Poniżej pokazujemy przykład dostepu do danych z poziomu Jupyter Notebook, uruchomionego w ramach usługi Vertex AI. Wykorzystujemy język R.
Przechodzimy do produktu Vertex AI:
Po kliknięciu przycisku NEW NOTEBOOK, musimy wybrać ustawienia dla naszego notebooka m.in. parametry techniczne środowiska czy język programowania.
Po otwarciu notebooka, wprowadzamy do niego przykładowy kod, który pobiera ogólne informacje z Google BigQuery:
# załadowanie niezbędnych bibliotek
library(bigrquery)
# ustaw ID projektu
projectid = "id_projektu"
# Zapytanie do bazy BigQuery
sql <- "SELECT * FROM `nazwa_tabeli` WHERE data_date = '2023-03-03' LIMIT 10"
# wykonanie zapytania
tb <- bq_project_query(projectid, sql)
# zapisanie danych do zmiennej result
result <- bq_table_download(tb, n_max = 10)
# zapisanie danych do zmiennej result
result <- bq_table_download(tb, n_max = 10)
Wynik, jaki otrzymamy to znana nam już tabela z danymi opisanymi wyżej.
Poniższy screen obrazuje skrypt, który został właśnie uruchomiony.
Tym akcentem kończymy nasz opis integracji Google Search Console z BigQuery.
W kolejnych postach przyjrzymy się dokładniej różnym sposobom połączenia się z usługą Google BigQuery. Aby nic nie przegapić, dodaj niniejszy blog do ulubionych :)
Przeczytaj również
Wykorzystanie API Piwik PRO do pobrania i wizualizacji danych w Microsoft Power BI
Piwik PRO. Integracja z Google Ads
Piwik PRO. Integracja z Google Search Console
Piwik PRO. Wbudowany Consent Manager. Konfiguracja
Google Tag Manager Server Side. Proces implementacji krok po kroku
Integracja Google Analytics 4 i BigQuery. Ile to kosztuje? Kalkulator.
Analytics Data API. Wykorzystanie biblioteki googleAnalyticsR i pobieranie danych z GA4 (część 2)
Analytics Data API. Wykorzystanie biblioteki googleAnalyticsR do pobierania danych z GA4 (część 1)