V tomto článku nebudeme řešit napojení GA4 nebo nastavení BQ, ale podíváme se na představení a hlavní výhody používání BigQuery, včetně ukázek konkrétních dotazů na data z GA4 a Google Ads.
Co je BigQuery?
BigQuery je cloudová databáze od Google, kterou lze využívat v rámci Google Cloud Platform. Funguje jako datový sklad a je ideální pro ukládání a zpracování velkých objemů dat, včetně těch z Google Analytics 4. Pokud potřebujete spolehlivý nástroj na zpracování dat a reporting, BigQuery je skvělou volbou.
Velkou výhodou BigQuery je její flexibilita. Data z GA4 můžete propojit s dalšími zdroji, jako jsou CRM systémy, reklamní platformy nebo jiné databáze. To vám umožní vytvářet komplexní pohledy na výkon vaší firmy, webu nebo kampaní.
Získejte úplnou kontrolu nad vašimi daty
Jednou z největších výhod používání BigQuery v kombinaci s GA4 je absolutní kontrola nad vašimi daty. Veškerá data, která generujete a ukládáte, jsou pod vaší správou – bez omezení. Navíc můžete data ukládat v rámci EU, což je skvělá zpráva pro ty, kteří musí dodržovat přísné legislativní požadavky na ochranu dat.
Bližší informace o vlastnictví dat.
Bližší informace o bezpečnosti dat.
Bližší informace o umístění dat.
Ukládejte historická data bez limitů
GA4 standardně uchovává plnohodnotná data pouze po dobu až 14 měsíců (u GA4 360 až 50 měsíců). Pokud uživatel nenavštíví web déle než 14 měsíců, GA4 jeho data promaže. Z historických dat tak zůstávají k dispozici pouze agregovaná data v základních reportech, což znemožňuje provádět komplexnější analýzy nad plnohodnotnými historickými daty přímo v GA4.
S BigQuery však můžete data ukládat neomezeně dlouho. To vám umožňuje vracet se k historickým datům kdykoli potřebujete a provádět analýzy na základě dlouhodobějších trendů.
Analýza dat v souvislosti s Google Consent Mode v GA4 vs. BQ
BigQuery dokáže zpracovávat data z GA4 i s funkcí Google Consent Mode, což je důležité pro správnou analýzu chování uživatelů na vašem webu. Google Consent Mode bere v úvahu souhlas uživatelů s ukládáním cookies a umožňuje vám analyzovat chování návštěvníků, kteří statistické cookies buď přijali, nebo odmítli.
• Google Analytics 4 a Google Consent Mode
Consent Mode však není jen o tom, kdo cookies přijal a kdo ne. V rozhraní GA4 umožňuje za splnění určitých podmínek modelování dat od uživatelů, kteří statistické cookies odmítnou (parametr v BQ analytics_storage="denied"), na základě dat od uživatelů, kteří statistické cookies akceptují (parametr v BQ analytics_storage="granted"). Tento přístup umožňuje získat smysluplné informace i z návštěv, které by jinak zůstaly skryté, a přitom plně respektujete soukromí uživatelů.
Aby GA4 mohla správně modelovat chování uživatelů bez cookies, je potřeba splnit několik kritérií:
• Minimálně 1000 událostí denně s parametrem analytics_storage="denied" po dobu alespoň 7 dní.
• Minimálně 1000 uživatelů denně s parametrem analytics_storage="granted" po dobu alespoň 7 dní z posledních 28 dní.
Správné nastavení Google Consent Mode je klíčové pro přesné odhady chování uživatelů, kteří statistické cookies odmítnou. GA4 pak tato domodelovaná data integruje do rozhraní a poskytuje vám lepší přehled o celkovém chování návštěvníků vašeho webu, aniž by došlo k narušení soukromí.
Podívejte se blíže na informace o Google Consent Mode a modelování dat v GA4.
• Výhody BigQuery s daty z GA4 a Google Consent Mode
Zatímco v GA4 musíte pro aktivaci modelování splnit výše uvedené podmínky, BigQuery toto omezení nemá. V BigQuery totiž máte k dispozici kompletní dataset a můžete provádět modelování na základě dostupných anonymizovaných dat. To zahrnuje události jako návštěvy, transakce, tržby, zobrazení stránek nebo scrolling.
Dále můžete analyzovat data napříč dimenzemi, jako jsou zdroj návštěvnosti, médium, kampaň, typ zařízení nebo stránky. Tato data lze dále obohatit atribucí založenou na modelu first click, last click, last click non-direct nebo vlastní atribucí.
Při používání GA4 konektoru pro Looker Studio jste omezeni kvótami na dotazy, což může bránit složitějšímu reportingu. S BigQuery žádné takové limity nemáte. Navíc data z GA4, která máte v BigQuery, nejsou zkreslená ani vzorkovaná (cardinality, thresholding nebo sampling), takže máte k dispozici kompletní nedomodelovaná a neagregovaná data, což umožňuje přesné a velmi detailní analýzy nebo modelace dat.
BigQuery navíc umožňuje získat až o 80 % více dat pro reportování ve srovnání s GA4, a to v závislosti na nastavení Consent Mode a interakci uživatelů s cookie lištou. To vám poskytuje výrazně větší množství dat, která mohou být základem pro přesnější analýzy.
Obrázek č.1 - Ukázka porovnání návštěv na webu, který nesplňuje podmínky pro modelování dat v GA4.
Článek o Consent mode najdete na našem blogu, nebo se o něm dočtete více také v na stránkách podpory od Google: Google Consent Mode a jeho fungování.
Flexibilní cenový model pro BigQuery
BigQuery nabízí až 10 GB úložiště zdarma, a navíc vám měsíčně umožní zpracovat až 1 TB dat bez poplatků. Co je také důležité – své náklady můžete sledovat v reálném čase, což vám pomůže udržet si přehled a kontrolu nad rozpočtem.
• Jak využít bezplatné limity?
Pokud například generujete méně než 30 000 událostí denně (např. návštěvy, zobrazení stránek, transakce atd.), měl by objem vašich dat spadat do bezplatného limitu 10 GB úložiště během prvních několika měsíců používání.
Představte si, že průměrná velikost jedné události je zhruba 1 KB (hrubý odhad – velikost se může lišit podle typu události a jejich parametrů). To znamená, že byste mohli zaznamenat až 10 milionů událostí, než překročíte bezplatnou kapacitu úložiště. Pokud generujete přibližně 30 000 událostí denně, pak vám bezplatná kapacita úložiště vystačí přibližně na 333 dní.
• Co se stane po překročení bezplatného limitu?
Jakmile překročíte prvních 10 GB úložiště, BigQuery si účtuje 0,020 USD za každý GB měsíčně, což odpovídá přibližně 0,46 CZK za GB (v závislosti na aktuálním kurzu pro servery v EU – Active logical strorage). I když překročíte tento limit, náklady na úložiště by měly zůstat relativně nízké.
Pokud překročíte první 1 TB zpracování dotazů měsíčně, každý další 1 TB vás bude stát 6,25 USD (přibližně 145 CZK pro servery v EU). I s velkými datovými sadami jsou tedy náklady udržitelné a BigQuery nabízí transparentní přehled o vašich výdajích.
Co se stane, pokud překročíte denní limit na počet importovaných událostí z GA4 do BQ?
Může se stát, že překročíte denní limit 1 milion událostí na počet exportovaných událostí z GA4 a bude vám zasláno upozornění na snížení počtu událostí odesílaných do BigQuery. V rámci exportu událostí z GA4 do BQ můžete pro snížení objemu počtu událostí vyloučit události, které nebudete pro analýzu v BQ používat.
Pokud nesnížíte objem počtu odesílaných událostí z GA4 do BQ do 7 dnů od upozornění, dojde k přerušení exportu dat do BQ. Případně můžete využít placenou verzi GA4 360, kde je denní limit miliardy událostí denně.
Proto doporučujeme mít propracovanou strukturu událostí včetně jejich parametrů (počet a délka parametrů v události).
Pro detailní informace o cenách můžete navštívit oficiální ceník BigQuery.
Podívejte se také na porovnání kvót a limitů mezi GA4 a GA4 360.
Nastavení pro optimalizovaný a automatizovaný reporting
Pro reporting doporučujeme nastavit dotazy tak, aby minimalizovaly náklady a zároveň poskytnuly všechny potřebné informace. Můžete vytvořit hlavní zdrojovou tabulku v BigQuery a nastavit její pravidelnou aktualizaci. Tuto tabulku můžete následně propojit s vizualizačními nástroji, jako je například Looker Studio. Velkou výhodou tohoto postupu je, že šetříte své finance, protože data čerpáte z předem vytvořené tabulky a nikoliv při každém dotazu přímo z GA4 datasetu (tabulky) v BigQuery. Pokud by se data reportovala přímo z BigQuery ve vizualizačních nástrojích bez použití zdrojové BigQuery tabulky, vždy by se při jakékoliv změně nebo načtení reportu čerpal limit, který je určen pro dotazy (1TB měsíčně) a tento limit byste velmi rychle vyčerpali.
Praktická ukázka dotazů v BigQuery na data z GA4 a Google Ads
Níže najdete ukázky dotazů pro vytvoření finální tabulky pro reporting s last click atribucí pro návštěvy se souhlasem se statistickými cookies. Součástí je také oprava chyby v označení zdroje/média relace pro kampaně Google Ads (změna z "google / organic" na "google / cpc") a doplnění názvu relace kampaně podle gclid (Google Click ID) z Google Ads pomocí Data Transfer.
Od cca 26.6.2024 jste se mohli setkat s tím, že se vám názvy kampaní (nebo i další dimenze) z Google Ads zobrazovaly jako "(organic)" apod. Tato situace byla způsobena aktualizací GA4 UI, kdy u uživatelů, kteří neposkytli souhlas s uložením reklamních cookies (ad_storage, ad_user_data, personalization), není identifikován název kampaně a další dimenze. Proto vám přinášíme praktickou ukázku možného řešení.
Obrázek č.2 - Ukázka porovnání poměru identifikace názvu kampaní z Google Ads v BigQuery oproti GA4.
DISCLAIMER! – Dotazy jsou plně funkční v případě správného nastavení webové analytiky, BigQuery a Google Ads. Dotazy byly vyvinuty pro jednoduché získání dat z BigQuery pro standardní objem dat, včetně identifikace názvů kampaní a sestav z Google Ads podle gclid. To umožňuje základní reporting v Looker Studiu bez potřeby používat orchestraci a další techniky zpracování dat. Obdobným způsobem lze s určitými modifikacemi dotazu získat data pro návštěvy bez souhlasu se statistickými cookies (např. data bez souhlasu neobsahují hodnoty pro user_pseudo_id apod.).
1. Dotaz na data z GA4 v BigQuery s last click atribucí
Před spuštěním dotazu je nutné mít vytvořený dataset do kterého se má tabulka vytvořit.
1. 1. Dotaz na data z GA4 v BigQuery s last click atribucí
CREATE TABLE `project-name.dataset-name.last-click_storage_yes` AS
-- Definujeme první dočasnou CTE tabulku 'prep' pro předzpracování dat
WITH prep AS (
SELECT
parse_date('%Y%m%d', event_date) AS date, -- Převede 'event_date' na typ DATE
stream_id,
device.web_info.hostname AS hostname,
-- Extrahuje hodnotu 'traffic_type' z pole 'event_params'
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'traffic_type') AS traffic_type,
device.category AS device_category,
user_pseudo_id,
-- Nastaví 'storage' na NULL, pokud je prázdný řetězec
NULLIF(privacy_info.analytics_storage, '') AS storage,
-- Extrahuje 'ga_session_id' z 'event_params'
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
event_timestamp,
event_name,
-- Kombinuje zdroj, médium, kampaň, sestavu a gclid do jednoho řetězce odděleného '#'
CONCAT(
IFNULL((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source'), ''),
'#',
IFNULL((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium'), ''),
'#',
IFNULL((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign'), ''),
'#',
IFNULL((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'content'), ''),
'#',
IFNULL((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'gclid'), '')
) AS session_source,
-- Extrahuje 'engagement_time_msec' z 'event_params'
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') AS engagement_time_msec,
-- Extrahuje 'session_engaged' z 'event_params'
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged') AS session_engaged,
-- Získá 'purchase_revenue' pokud je 'event_name' rovno 'purchase'
CASE WHEN event_name = 'purchase' THEN ecommerce.purchase_revenue END AS purchase_revenue,
-- Přepočítá 'engagement_time_msec' na sekundy
((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec')) / 1000 AS time_on_page_sec
FROM
`project-name.analytics_XXXXXXXXX.events_*` -- Změň 'XXXXXXX' na ID tvého GA4 účtu, získá data ze všech tabulek events_ (standardní a intraday tabulka)
WHERE
_TABLE_SUFFIX BETWEEN '20240701' AND '20240831' -- Filtruje data od 1.7.2024 do 31.8.2024
AND privacy_info.analytics_storage = 'Yes' -- Filtruje pouze data s povoleným ukládáním analytických údajů
),
-- Druhá dočasná tabulka 'prep2' pro čištění dat
prep2 AS (
SELECT
date,
stream_id,
hostname,
traffic_type,
device_category,
user_pseudo_id,
storage,
session_id,
event_timestamp,
event_name,
-- Nastaví 'session_source' na NULL, pokud je prázdný (tj. '####')
CASE WHEN session_source = '####' THEN NULL ELSE session_source END AS session_source,
engagement_time_msec,
session_engaged,
purchase_revenue,
time_on_page_sec
FROM
prep
),
-- Třetí dočasná tabulka 'prep3' pro získání prvního nenulového 'session_source' pro každou session
prep3 AS (
SELECT
date,
stream_id,
hostname,
traffic_type,
device_category,
user_pseudo_id,
storage,
event_name,
session_id,
-- Rozdělí 'session_source' na pole hodnot pomocí '#'
SPLIT(IFNULL(
FIRST_VALUE(session_source IGNORE NULLS) OVER (
PARTITION BY user_pseudo_id, session_id
ORDER BY event_timestamp
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
),
'(direct)#(none)###'
), '#') AS session_source,
engagement_time_msec,
session_engaged,
purchase_revenue,
time_on_page_sec
FROM
prep2
)
-- Finální SELECT dotaz, který agreguje data a opravuje zdroj/médium relace podle gclid pro Google Ads kampaně
SELECT
date,
stream_id,
hostname,
traffic_type,
device_category,
storage,
-- Určuje 'source' na základě 'gclid'; pokud je 'gclid' přítomen, nastaví 'google', jinak použije původní hodnotu
CASE WHEN REGEXP_CONTAINS(session_source[OFFSET(4)], '[0-9a-zA-Z_-]+') THEN 'google' ELSE session_source[OFFSET(0)] END AS source,
-- Určuje 'medium'; pokud je 'gclid' přítomen, nastaví 'cpc', jinak použije původní hodnotu
CASE WHEN REGEXP_CONTAINS(session_source[OFFSET(4)], '[0-9a-zA-Z_-]+') THEN 'cpc' ELSE session_source[OFFSET(1)] END AS medium,
-- Určuje 'campaign'; pokud je 'gclid' přítomen, nastaví '(cpc)', jinak použije původní hodnotu
CASE WHEN REGEXP_CONTAINS(session_source[OFFSET(4)], '[0-9a-zA-Z_-]+') THEN '(cpc)' ELSE session_source[OFFSET(2)] END AS campaign,
-- Určuje 'content'; pokud je 'gclid' přítomen, nastaví '(cpc)', jinak použije původní hodnotu
CASE WHEN REGEXP_CONTAINS(session_source[OFFSET(4)], '[0-9a-zA-Z_-]+') THEN '(cpc)' ELSE session_source[OFFSET(3)] END AS content,
-- Přiřadí 'gclid' z pátého prvku pole 'session_source'
session_source[OFFSET(4)] AS gclid,
-- Počet unikátních návštěv
COUNT(DISTINCT CONCAT(user_pseudo_id, session_id)) AS sessions,
-- Počet zapojených návštěv
COUNT(DISTINCT CASE WHEN session_engaged = '1' THEN CONCAT(user_pseudo_id, session_id) END) AS engaged_sessions,
-- Počet unikátních uživatelů
COUNT(DISTINCT user_pseudo_id) AS users,
-- Počet aktivních uživatelů (s engagement time větším než 0)
COUNT(DISTINCT CASE WHEN engagement_time_msec > 0 THEN user_pseudo_id END) AS active_users,
-- Počet zobrazení stránek
COUNTIF(event_name = 'page_view') AS page_view,
-- Počet zobrazení položek
COUNTIF(event_name = 'view_item') AS view_item,
-- Počet nákupů
COUNTIF(event_name = 'purchase') AS purchase,
-- Celkové tržby z nákupů
SUM(CASE WHEN event_name = 'purchase' THEN purchase_revenue ELSE NULL END) AS purchase_revenue,
-- Celkový čas strávený na stránkách v sekundách
SUM(time_on_page_sec) AS time_on_page_sec
FROM
prep3
-- Skupina dat podle klíčových metrik
GROUP BY
1,2,3,4,5,6,7,8,9,10,11
1. 2. Aktualizace tabulky s daty z GA4 se souhlasem se statistickými cookies
Trigger pro aktualizaci této tabulky bude 1. v pořadí (příklad: denně ve 4:00 hod.)
DELETE FROM `project-name.dataset-name.last-click_storage_yes`
WHERE date >= DATE_SUB(CURRENT_DATE, INTERVAL 3 DAY)
;
-- Vloží nová data do tabulky 'last-click_storage_yes'
INSERT INTO `project-name.dataset-name.last-click_storage_yes`
-- Začínáme s dočasnými tabulkami (CTEs) pro zpracování dat
(WITH prep AS (
SELECT
PARSE_DATE('%Y%m%d', event_date) AS date,
stream_id,
device.web_info.hostname AS hostname,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'traffic_type') AS traffic_type,
device.category AS device_category,
user_pseudo_id,
NULLIF(privacy_info.analytics_storage, '') AS storage,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
event_timestamp,
event_name,
CONCAT(
IFNULL((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source'), ''),
'#',
IFNULL((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium'), ''),
'#',
IFNULL((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign'), ''),
'#',
IFNULL((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'content'), ''),
'#',
IFNULL((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'gclid'), '')
) AS session_source,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') AS engagement_time_msec,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged') AS session_engaged,
CASE WHEN event_name = 'purchase' THEN ecommerce.purchase_revenue END AS purchase_revenue,
((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec')) / 1000 AS time_on_page_sec
FROM
`project-name.analytics_XXXXXXX.events_*` -- Nahraď 'XXXXXXX' ID svého GA4 účtu, získá data ze všech tabulek events_ (standardní a intraday tabulka)
WHERE
_TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE, INTERVAL 3 DAY)) -- Načte data za poslední 3 dny
AND privacy_info.analytics_storage = "Yes"
),
-- Druhá dočasná tabulka 'prep2' pro čištění dat
prep2 AS (
SELECT
date,
stream_id,
hostname,
traffic_type,
device_category,
user_pseudo_id,
storage,
session_id,
event_timestamp,
event_name,
CASE WHEN session_source = '####' THEN NULL ELSE session_source END AS session_source,
engagement_time_msec,
session_engaged,
purchase_revenue,
time_on_page_sec
FROM
prep
),
-- Třetí dočasná tabulka 'prep3' pro získání prvního nenulového 'session_source' pro každou session
prep3 AS (
SELECT
date,
stream_id,
hostname,
traffic_type,
device_category,
user_pseudo_id,
storage,
event_name,
session_id,
SPLIT(IFNULL(
FIRST_VALUE(session_source IGNORE NULLS) OVER (
PARTITION BY user_pseudo_id, session_id
ORDER BY event_timestamp
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
),
'(direct)#(none)###'
), '#') AS session_source,
engagement_time_msec,
session_engaged,
purchase_revenue,
time_on_page_sec
FROM
prep2
)
-- Finální SELECT dotaz pro agregaci dat
SELECT
date,
stream_id,
hostname,
traffic_type,
device_category,
storage,
CASE WHEN REGEXP_CONTAINS(session_source[OFFSET(4)], '[0-9a-zA-Z_-]+') THEN 'google' ELSE session_source[OFFSET(0)] END AS source,
CASE WHEN REGEXP_CONTAINS(session_source[OFFSET(4)], '[0-9a-zA-Z_-]+') THEN 'cpc' ELSE session_source[OFFSET(1)] END AS medium,
CASE WHEN REGEXP_CONTAINS(session_source[OFFSET(4)], '[0-9a-zA-Z_-]+') THEN '(cpc)' ELSE session_source[OFFSET(2)] END AS campaign,
CASE WHEN REGEXP_CONTAINS(session_source[OFFSET(4)], '[0-9a-zA-Z_-]+') THEN '(cpc)' ELSE session_source[OFFSET(3)] END AS content,
session_source[OFFSET(4)] AS gclid,
COUNT(DISTINCT CONCAT(user_pseudo_id, session_id)) AS sessions,
COUNT(DISTINCT CASE WHEN session_engaged = '1' THEN CONCAT(user_pseudo_id, session_id) END) AS engaged_sessions,
COUNT(DISTINCT user_pseudo_id) AS users,
COUNT(DISTINCT CASE WHEN engagement_time_msec > 0 THEN user_pseudo_id END) AS active_users,
COUNTIF(event_name = 'page_view') AS page_view,
COUNTIF(event_name = 'view_item') AS view_item,
COUNTIF(event_name = 'purchase') AS purchase,
SUM(CASE WHEN event_name = 'purchase' THEN purchase_revenue ELSE NULL END) AS purchase_revenue,
SUM(time_on_page_sec) AS time_on_page_sec
FROM
prep3
-- Skupina dat podle klíčových metrik (sloupce 1 až 11)
GROUP BY
1,2,3,4,5,6,7,8,9,10,11
ORDER BY
date ASC
);
2. Dotaz na data z Google Ads v BigQuery
Řešení vlastnosti BigQuery s nesprávným pojmenováním kampaní z Google Ads (XXXXXXXXX – označuje Google Ads ID)
1. Vytvoření datasetu ve vašem projektu v BigQuery, doporučený název: ads_XXXXXXXXXX
2. Aktivace Data Transfer API a vytvoření zdroje Google Ads, doporučený název: Google Ads gclid (ID XXXXXXXXXX)
3. Nastavení aktualizace (ideálně denně ve 4:00 hod. před aktualizací tabulky v kroku 2.2)
4. Výběr datasetu v BigQuery, viz. 1. krok
5. Uvedení Google Ads ID
6. Nejdůležitější část – filtrování tabulek, které v tomto case potřebujete „Campaign,AdGroup,ClickStats“
7. Aktivace e-mail notifikací
8. Po uložení se data načtou téměř ihned (během pár minut) nebo při další nastavené aktualizaci (cca do 24 hodin.)
9. Data pro identifikaci gclid se načtou od data aktivace transferu pro Google Ads, historická data je možné doplnit manuálně (spojením tabulek) za max. 90 dnů zpětně
2.1. Vytvoření tabulky pro základní data z Google Ads podle gclid
CREATE TABLE `project-name.ads_XXXXXXXXXX.ads_gclid_data` AS
-- Vybere data pro vložení
SELECT
-- Získá 'gclid' (Google Click ID); pokud je NULL, nahradí hodnotou 'N/A'
IFNULL(CAST(a.click_view_gclid AS STRING), 'N/A') AS gclid,
-- Získá 'campaign_id'; pokud je NULL, nahradí hodnotou 'N/A'
IFNULL(CAST(a.campaign_id AS STRING), 'N/A') AS campaign_id,
-- Získá 'campaign_name'; pokud je NULL, nahradí hodnotou 'N/A'
IFNULL(campaign_name, 'N/A') AS campaign_name,
-- Získá 'ad_group_id'; pokud je NULL, nahradí hodnotou 'N/A'
IFNULL(CAST(a.ad_group_id AS STRING), 'N/A') AS ad_group_id,
-- Získá 'ad_group_name'; pokud je NULL, nahradí hodnotou 'N/A'
IFNULL(ad_group_name, 'N/A') AS ad_group_name
FROM
-- Hlavní tabulka s kliknutími z Google Ads
`project-name.ads_XXXXXXXXXX.p_ads_ClickStats_XXXXXXXXXX` AS a
LEFT JOIN
-- Připojí tabulku s informacemi o kampaních
`project-name.ads_XXXXXXXXXX.p_ads_Campaign_XXXXXXXXXX` AS b
ON
-- Spojí tabulky na základě 'campaign_id'
a.campaign_id = b.campaign_id
LEFT JOIN
-- Připojí tabulku s informacemi o reklamních sestavách
`project-name.ads_XXXXXXXXXX.p_ads_AdGroup_XXXXXXXXXX` AS c
ON
-- Spojí tabulky na základě 'ad_group_id'
a.ad_group_id = c.ad_group_id
-- Skupina dat podle všech vybraných sloupců (1 až 5)
GROUP BY 1,2,3,4,5
2.2. Aktualizace tabulky pro základní data z Google Ads podle gclid
Trigger pro aktualizaci této tabulky bude 2. v pořadí (příklad: denně ve 4:30 hod.)
INSERT INTO `project-name.ads_XXXXXXXXXX.ads_gclid_data` (gclid, campaign_id, campaign_name, ad_group_id, ad_group_name)
-- Vybere data pro vložení
SELECT
IFNULL(CAST(a.click_view_gclid AS STRING), 'N/A') AS gclid,
IFNULL(CAST(a.campaign_id AS STRING), 'N/A') AS campaign_id,
IFNULL(b.campaign_name, 'N/A') AS campaign_name,
IFNULL(CAST(a.ad_group_id AS STRING), 'N/A') AS ad_group_id,
IFNULL(c.ad_group_name, 'N/A') AS ad_group_name
FROM
`project-name.ads_XXXXXXXXXX.p_ads_ClickStats_XXXXXXXXXX` AS a
LEFT JOIN
`project-name.ads_XXXXXXXXXX.p_ads_Campaign_XXXXXXXXXX` AS b
ON
a.campaign_id = b.campaign_id
LEFT JOIN
`project-name.ads_XXXXXXXXXX.p_ads_AdGroup_XXXXXXXXXX` AS c
ON
a.ad_group_id = c.ad_group_id
WHERE
-- Filtruje záznamy, které již nejsou v tabulce 'ads_gclid_data' (aby nedošlo k duplicitám)
NOT EXISTS (
SELECT 1
FROM `project-name.ads_XXXXXXXXXX.ads_gclid_data` AS existing
WHERE existing.gclid = IFNULL(CAST(a.click_view_gclid AS STRING), 'N/A')
)
-- Skupina dat podle všech vybraných sloupců (1 až 5)
GROUP BY 1, 2, 3, 4, 5;
3. Dotaz pro spojení dat z Google Analytics 4 a Google Ads v BigQuery
Před spuštěním dotazu je nutné mít vytvořený dataset do kterého se má tabulka vytvořit. Doporučujeme použít stejný dataset, který byl vytvořen v 1. kapitole.
3.1. Vytvoření tabulky pro spojení dat z GA4 a Google Ads pro identifikaci názvu kampaní a sestav podle gclid
CREATE TABLE `project-name.dataset-name.last-click_storage_yes_gclid` AS -- Vytvoří novou tabulku s názvem 'last-click_storage_yes_gclid'
SELECT
t1.date, -- Datum
t1.stream_id, -- ID streamu (datového toku)
t1.hostname, -- Název hostitele
t1.traffic_type, -- Typ provozu (interní nebo null)
t1.device_category, -- Kategorie zařízení (desktop, mobil, tablet)
t1.storage, -- Informace o ukládání dat (Yes nebo No)
t1.source, -- Zdroj relace (např. google, direct)
t1.medium, -- Médium relace (např. organic, cpc)
-- Určení názvu kampaně relace:
CASE
WHEN t2.campaign_name IS NOT NULL THEN t2.campaign_name -- Pokud je k dispozici 'campaign_name' z Google Ads, použijeme ho
ELSE
t1.campaign -- Jinak ponecháme původní název kampaně z GA4
END AS campaign,
-- Určení obsahu relace (např. reklamní sestavy):
CASE
WHEN t2.ad_group_name IS NOT NULL THEN t2.ad_group_name -- Pokud je k dispozici 'ad_group_name' z Google Ads, použijeme ho
ELSE
t1.content -- Jinak ponecháme původní obsah z GA4
END AS content,
t1.gclid, -- Google Click ID, identifikátor kliknutí z Google Ads
t1.sessions, -- Počet návštěv
t1.engaged_sessions, -- Počet zapojených návštěv (návštěvy s interakcí)
t1.users, -- Počet unikátních uživatelů
t1.active_users, -- Počet aktivních uživatelů
t1.page_view, -- Počet zobrazení stránek
t1.view_item, -- Počet zobrazení položek (produktů)
t1.purchase, -- Počet nákupů
t1.purchase_revenue, -- Celkové tržby
t1.time_on_page_sec -- Celkový čas strávený na stránce v sekundách
FROM
`project-name.dataset-name.last-click_storage_yes` AS t1 -- Hlavní tabulka s daty z GA4 (Google Analytics 4)
LEFT JOIN
`project-name.ads_XXXXXXXXXX.ads_gclid_data` AS t2 -- Tabulka s daty z Google Ads obsahující informace o kampaních a sestavách podle 'gclid'
ON
t1.gclid = t2.gclid -- Spojení tabulek na základě shody 'gclid'
WHERE
t1.date BETWEEN '2024-07-01' AND '2024-08-31'; -- Filtruje data za období od 1.7.2024 do 31.8.2024
3.2. Aktualizace tabulky pro spojení dat z GA4 a Google Ads pro identifikaci názvu kampaní a sestav podle gclid
Trigger pro aktualizaci této tabulky bude 3. v pořadí (příklad: denně ve 5:00 hod.)
DELETE FROM `project-name.dataset-name.last-click_storage_yes_gclid`
WHERE
date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);
-- Vloží aktualizovaná data do tabulky 'last-click_storage_yes_gclid'
INSERT INTO `project-name.dataset-name.last-click_storage_yes_gclid`
SELECT
t1.date,
t1.stream_id,
t1.hostname,
t1.traffic_type,
t1.device_category,
t1.storage,
t1.source,
t1.medium,
CASE
WHEN t2.campaign_name IS NOT NULL THEN t2.campaign_name
ELSE t1.campaign
END AS campaign,
CASE
WHEN t2.ad_group_name IS NOT NULL THEN t2.ad_group_name
ELSE t1.content
END AS content,
t1.gclid,
t1.sessions,
t1.engaged_sessions,
t1.users,
t1.active_users,
t1.page_view,
t1.view_item,
t1.purchase,
t1.purchase_revenue,
t1.time_on_page_sec
FROM
`project-name.dataset-name.last-click_storage_yes` AS t1
LEFT JOIN
`project-name.ads_XXXXXXXXXX.ads_gclid_data` AS t2
ON
t1.gclid = t2.gclid
WHERE
t1.date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);
4. Výsledná tabulka pro reporting v Looker Studiu
Výslednou tabulku last-click_storage_yes_gclid, kterou budete mít denně aktualizovanou, můžete napojit do Looker Studia a plnohodnotně reportovat data z GA4 pomocí BigQuery.
Závěrem
BigQuery je mocný nástroj pro maximální využití dat nejen z Google Analytics 4. Nabízí neomezenou kontrolu, ukládání historických dat bez limitů a flexibilní analýzy překonávající omezení GA4. Umožňuje také splňovat legislativní požadavky díky ukládání dat v rámci EU.
Pokud chcete posunout práci s daty na vyšší úroveň a získat hlubší vhled do výkonu vašeho webu či aplikace, je čas začít využívat BigQuery!
