BigQuery + Google Ads data pipeline 2026: vybudujte si reportingový data warehouse

Každá vážná Google Ads operace eventuálně přerůstá platformové UI a exportované spreadsheety. Reporting otázky, které záleží nejvíc — jaká je má skutečná cena za closed-won deal, které kampaně pohánějí sessions, které konvertují 60 dní později, jak Google, GA4 a CRM rekonciliují — nelze odpovědět uvnitř Google Ads, protože data žijí ve třech různých systémech. BigQuery reporting warehouse je způsob, jak analytické týmy toto v roce 2026 řeší, a Google dramaticky usnadnil on-ramp s managed Data Transfer Service.

Toto je hands-on tutoriál pro analytiky a inženýry. Vybudujeme pipeline end to end: provisionujeme Cloud projekt, konfigurujeme Data Transfer Service, povolíme GA4 export, navrhneme vrstevnaté schéma, spojíme CRM revenue na GCLID, naplánujeme denní refresh, položíme Looker Studio na vrchu a budeme držet BigQuery náklady pod kontrolou. Předpokládáme SQL plynulost a základní Google Cloud familiaritu. Pokud přicházíte z tracking-first základu, náš průvodce GA4 setup a conversion import je správným předpokladem.

Proč budovat BigQuery pipeline pro Google Ads

Google Ads UI je výjimečné pro správu kampaní a slabé pro cross-system analýzu. Tři strukturální limity tlačí týmy směrem k warehouse.

Za prvé, revenue mezera. Google Ads ví, co utratil a kolik konverzí napočítal, ale neví váš closed-won pipeline, vaši délku sales-cyklu nebo vaši refund rate. Lead, který Google počítá jako 0 € konverzní událost, se může stát 40 000 € enterprise dealem o devět měsíců později. Bez spojení CRM revenue zpět s klikem optimalizujete na konverzní objem místo zisku — nejčastější a nejdražší reporting selhání v B2B PPC.

Za druhé, cross-channel mix. Moderní akvizice běží napříč Google, Meta, LinkedIn a víc. Každá platforma hlásí ve vlastní walled garden s vlastní atribucí. Warehouse je jediné místo k vybudování jediného blended pohledu, kde utrácení a výsledky z každého kanálu sedí ve stejné tabulce s konzistentními definicemi. Náš průvodce cross-channel atribucí pokrývá metodiku; BigQuery je tam, kde ji implementujete.

Za třetí, analytický strop. Incrementality testing, customer lifetime value modelování, cohort retention a marketing mix modeling všechny vyžadují event-level a historická data, která UI prostě neodhaluje. Warehouse s dva až tři roky čisté historie je substrát, na kterém každá pokročilá měřicí technika sedí. Vlastní open-source MMM framework Googlu, pokrytý v našem Meridian průvodci, čte přímo z BigQuery.

Ekonomika je příznivá. Data Transfer Service je zdarma. Prvních 10 GB storage BigQuery a první 1 TB query processingu každý měsíc jsou zdarma a nad to storage běží asi 0,02 € na GB za měsíc a dotazy asi 5-6 € na TB skenovaných. Dobře-postavený single-account warehouse málokdy přesahuje 150 €/měsíc a často běží pod 50 €. Proti nákladům špatně přerozděleného ad utrácení je to zaokrouhlovací chyba. Investice je inženýrský čas, ne cloud účty.

Výplata je trvanlivá. Jakmile pipeline existuje, každá nová otázka se stává SQL dotazem místo manuálního export-and-pivot cvičení. Reporty se obnovují samy. Nové kanály se zapojují do stejného schématu. A datový základ kumuluje: čím déle warehouse běží, tím hodnotnější jeho historie pro modelování se stává.

Přehled architektury a tři zdroje dat

Referenční architektura je vrstevnatý warehouse krmený třemi zdroji, transformovaný naplánovaným SQL a povrchovaný v Looker Studio.

Tři zdroje dat:

Vrstevnatá dataset konvence drží warehouse udržovatelný a cost-efficient:

• raw_google_ads — netknuté DTS exporty. Nikdy přímo dotazované reporty. Berte jako neměnnou landing zónu.
• raw GA4 export — tabulky events_, které GA4 přistává ve svém vlastním datasetu, partitionované podle event date.
• staging — čisté, standardizované views a tabulky. Micros castnuté na měnu, sloupce přejmenované, refresh-window duplikáty odstraněny, date spine a account mapping spojeny.
• reporting — kurátorované, denormalizované tabulky purpose-built pro dashboardy. Toto je jediná vrstva, které se Looker Studio dotýká.

Toto oddělení záleží ze tří důvodů: izoluje surová data, aby transformační bug nikdy nekorumpoval zdroj, koncentruje drahé joins do naplánovaných buildů spíše než per-dashboard-refresh a dává vám čistou permissions hranici — analytici dostávají read přístup jen do reporting.

Orkestrace je uvědoměle jednoduchá. Data Transfer Service a GA4 export běží na rozpise Googlu, přistává čerstvá data každé ráno. Pár hodin později BigQuery scheduled queries přestavují staging a reporting tabulky. Looker Studio čte výsledek. Žádný externí orkestrátor, žádné servery, žádné kontejnery. Můžete promovat na dbt a Cloud Composer později, ale nepotřebujete je k začátku a jejich předčasné přidání je over-engineering.

Poznámka o regionech: vyberte jednu BigQuery lokaci (EU multi-region pro evropskou data residency) a použijte ji pro každý dataset. Cross-region dotazy nejsou dovoleny, takže location nesoulad mezi vaším DTS datasetem a CRM datasetem rozbije joins. Rozhodněte jednou, dopředu.

Nastavení Google Ads Data Transfer Service

Data Transfer Service (DTS) je základ a je to skutečně pár kliků konfigurace plus čekání na backfill.

Předpoklady:

• Google Cloud projekt s povoleným billingem
• BigQuery API a BigQuery Data Transfer API povoleny
• Google účet s read přístupem k cílovému Google Ads účtu (nebo MCC)
• Customer ID Google Ads účtu (10 číslic, bez pomlček)

Kroky konfigurace:

1. V BigQuery konzoli otevřete Data transfers a klikněte Create transfer.
2. Vyberte Google Ads jako zdroj.
3. Pojmenujte transfer, nastavte rozpis na denní a vyberte čas běhu brzy ráno.
4. Nastavte destination dataset na raw_google_ads.
5. Zadejte Customer ID — použijte MCC ID k tažení všech child účtů v jednom transferu.
6. Konfigurujte refresh okno (trailing počet dní, které DTS znovu-tahá každý běh) k zachycení conversion backfill a pozdní atribuce.
7. Autentizujte s účtem, který má nezbytný Google Ads přístup, a uložte.

DTS vytvoří sadu tabulek v raw_google_ads — campaign, ad group, ad group criteria (klíčová slova), conversions a další — každá příponou date nebo partitionovaná, podle tabulky. Pojmenování následuje dokumentované schéma Googlu; držte tu dokumentaci otevřenou jako referenci, protože názvy sloupců jsou vermózní a stat tabulky oddělují metriky od atributů.

Spusťte backfill okamžitě. Čerstvý transfer táhne jen dopředu od dneška. K získání historie spusťte manuální backfill za posledních 12 měsíců (nebo tak daleko zpět, jak potřebujete a účet podporuje). Backfilly běží jako série denních úloh a mohou nějakou dobu trvat pro dlouhé rozsahy, ale musí běžet jen jednou.

Validujte první load. Po dokončení iniciálního běhu zdravě-zkontrolujte: sečtěte cost (v micros, pak dělte 1 000 000) za nedávný týden a porovnejte proti Google Ads UI pro stejné okno. Malé nesoulady jsou normální kvůli attribution refresh oknu a time-zone hranicím, ale součty by měly být blízko. Pokud jsou divoce odlišné, zkontrolujte, že jste vybrali správné customer ID a time zone.

MCC úvahy. MCC transfer taguje každý řádek customer ID. Toto je silné pro agentury, ale násobí objem dat. S mnoha účty se partitionování podle data a clusterování podle customer ID ve vaší staging vrstvě přestává být nice-to-have a stává se rozdílem mezi 40 a 400 € měsíčním účtem. Plánujte pro to od první staging tabulky.

Návrh BigQuery schématu a staging vrstvy

Surové DTS tabulky jsou přesné, ale nepohodlné: náklady v micros, kryptické názvy sloupců, separátní stats a attribute tabulky a refresh-window překryv. Staging vrstva opravuje vše jednou, aby downstream dotazy zůstaly čisté.

Hlavní staging transformace:

-- staging.campaign_performance_daily
CREATE OR REPLACE TABLE staging.campaign_performance_daily
PARTITION BY date
CLUSTER BY customer_id, campaign_id AS
SELECT
  _DATA_DATE                         AS date,
  customer_id,
  campaign_id,
  campaign_name,
  metrics_cost_micros / 1000000      AS cost,
  metrics_impressions                AS impressions,
  metrics_clicks                     AS clicks,
  metrics_conversions                AS conversions,
  metrics_conversions_value          AS conversion_value
FROM `raw_google_ads.ads_CampaignBasicStats_*` s
JOIN `raw_google_ads.ads_Campaign_*` c USING (campaign_id)
WHERE _DATA_DATE = c._DATA_DATE;

Specifika názvů tabulek se liší s verzí DTS schématu, ale vzor drží: castněte micros, přejmenujte na čitelné sloupce, spojte stats s attributy, partitionujte podle data, clusterujte podle sloupců, na které filtrujete nejvíc.

Deduplikace refresh okna. Protože DTS znovu-tahá trailing dny, stejné datum se může objevit ve více snapshot loadech. Vyberte nejnovější snapshot na logické datum s použitím window funkce nebo čtením partition, kterou DTS označuje aktuální. Přeskočení tohoto kroku dvojnásobně počítá utrácení ve vašich nejnovějších dnech — subtilní bug, který erodi důvěru v warehouse.

Podpůrné tabulky, které budete potřebovat:

Partitionování a clusterování nejsou volitelné. Partitionujte každou materializovanou staging a reporting tabulku podle date. Clusterujte podle customer_id a campaign_id (nebo cokoli vaše reporty filtrují na). Toto je páka, která kontroluje náklady: dotaz na poslední měsíc proti date-partitionované tabulce skenuje jen byty posledního měsíce, ne tři roky historie. Rozdíl je často 30× na vyspělém warehouse.

Views versus tabulky. Pro lehké transformace jsou views v pořádku a nevyvolávají storage náklady — ale znovu-skenují source data při každém čtení. Pro cokoli dotazované opakovaně dashboardy materializujte partitionovanou tabulku přes scheduled query. Pravidlo palce: levná transformace čtená jednou rovná se view; drahá transformace čtená mnohokrát rovná se materializovaná tabulka.

Držte staging vrstvu nudnou a deterministickou. Měla by dělat přesně jednu práci — proměňovat surové DTS a GA4 exporty v čisté, well-typed, partitionované stavební bloky — aby se reporting vrstva mohla soustředit na zajímavé joins.

Spojování GA4, Google Ads a CRM dat

Toto je tam, kde si warehouse zaslouží své peníze. Tři zdroje, správně spojené, odpovídají otázkám, na které žádná samostatná platforma nemůže.

GCLID je páteř atribuce revenue. Když někdo klikne na Google reklamu s auto-taggingem zapnutým, Google připojuje GCLID k landing URL. Zachyťte ho ve skrytém form poli, perzistujte ho do vašeho CRM proti leadu a stane se join klíčem mezi Google Ads kliky a closed-won revenue.

-- reporting.campaign_to_revenue
SELECT
  ga.date,
  ga.campaign_name,
  ga.cost,
  ga.conversions,
  COUNT(DISTINCT crm.deal_id)        AS deals_closed,
  SUM(crm.closed_won_amount)         AS crm_revenue,
  SAFE_DIVIDE(ga.cost, SUM(crm.closed_won_amount)) AS cost_to_revenue_ratio
FROM staging.click_with_gclid ga
LEFT JOIN crm.deals crm
  ON ga.gclid = crm.gclid
GROUP BY ga.date, ga.campaign_name, ga.cost, ga.conversions;

LEFT JOIN je uvědomělý: neshodnuté utrácení musí zůstat viditelné. Pokud inner-joinete, každý klik bez shodnutého dealu tiše mizí a vaše cost-per-revenue matematika si lichotí. Udržte veškeré utrácení ve výsledku a berte match rate jako vlastní health metriku.

Spojení GA4 pro behaviorální hloubku. GA4 export přistává event-level řádky s vnořenými event_params. Unnest je k obnovení kampaně, kvality session a landing stránky, pak agregujte na granularitě, kterou potřebujete.

-- staging.ga4_sessions (campaign and landing page recovered from event_params)
SELECT
  PARSE_DATE('%Y%m%d', event_date) AS date,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign') AS campaign,
  COUNTIF(event_name = 'session_start') AS sessions,
  COUNTIF(event_name = 'sign_up')       AS signups
FROM `analytics_XXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20261231'
GROUP BY date, campaign;

Vždy omezujte GA4 _TABLE_SUFFIX na rozsah data — events tabulky jsou velké a unbounded wildcard sken je klasická GA4 nákladová past.

Načítání CRM. Dostaňte dealy do BigQuery cokoli, co sedí do vašeho stacku: managed konektor jako Fivetran, naplánovaná Cloud Function tepající API vašeho CRM nebo noční CSV load. Minimální sloupce jsou deal ID, GCLID, closed-won amount, close date a stage. Obnovujte denně, aby revenue zůstávala aktuální.

Když je GCLID pokrytí špatné, match rate trpí a revenue vypadá podhodnoceně. Upstream opravy jsou enhanced conversions a offline conversion import, oba zlepšují, jak spolehlivě kliky vážou zpět k výsledkům — viz našeho průvodce nastavení enhanced conversions. Jako záloha ve warehouse UTM-based join získává některé neshodnuté řádky, ale berte to jako lower-confidence doplněk, ne náhradu za GCLID.

Výstupem této sekce je jediná sjednocená tabulka nesoucí utrácení, Google konverze, GA4 engagement a CRM revenue vedle sebe — tabulka, na které je každý smysluplný PPC report postaven.

Scheduled queries a denní refresh

Se staging a reporting logikou napsanou automatizujte denní build, aby se warehouse udržoval sám.

Nativní scheduled queries jsou správný startovní nástroj. BigQuery vám umožňuje naplánovat jakýkoli SQL příkaz na cron-like kadenci bez extra nákladů nad query processing, který konzumuje. Naplánujte staging build první, pak reporting build, oba načasované pár hodin po typickém dokončení DTS a GA4 exportu ráno. To pořadí zajišťuje, že každá vrstva čte čerstvá upstream data.

Použijte správnou write sémantiku:

Pro většinu PPC reportingu je přestavění trailing N partitions s WRITE_TRUNCATE scoped na ty partitions nejjednodušším správným přístupem — přirozeně absorbuje DTS refresh-window backfill bez duplikování starších dat.

Přidejte freshness check. Malý scheduled query, který srovnává max date v každé source tabulce proti dnešku a zapisuje alert (nebo selže hlasitě), chytá tichý mód selhání, kdy DTS nebo GA4 export přeskočí den a vaše dashboardy tiše ukazují zastaralá čísla. Tento jediný strážce předchází nejtrapnější třídě reporting incidentu.

-- monitoring.freshness_check
SELECT
  'google_ads' AS source,
  MAX(date)    AS latest_date,
  DATE_DIFF(CURRENT_DATE(), MAX(date), DAY) AS days_behind
FROM staging.campaign_performance_daily
HAVING days_behind > 1;

Pokud ten dotaz vrátí řádky, něco upstream je zastaralé a potřebuje pozornost.

Kdy promovat na dbt. Scheduled queries pohodlně zvládají single-account warehouse dlouhou dobu. Přesuňte se na dbt, když transformační logika přesahuje zhruba 10-15 vzájemně závislých modelů, když chcete automatizované testy a column-level dokumentaci nebo když několik analytiků edituje stejný SQL a potřebují version control a lineage. dbt orkestrovaný Cloud Composerem (managed Airflow) je běžný další krok — ale adoptujte ho, protože složitost vyžaduje, ne protože je módní.

Backfill disciplína. Když změníte transformaci, znovu-spusťte ji napříč historií, aby staré partitions reflektovaly novou logiku. Parametrizujte své scheduled queries datem, aby stejný SQL sloužil jak dennímu incremental běhu, tak manuálnímu full backfillu.

Looker Studio reporting a správa nákladů

Warehouse je užitečný jen tehdy, když ho lidé mohou číst, a Looker Studio je přirozený front end pro BigQuery.

Propojte jen s kurátorovanými tabulkami. Namiřte každý Looker Studio data source na reporting dataset, nikdy na surové exporty nebo široké staging tabulky. Toto je nejdůležitější nákladové rozhodnutí v celém pipeline. Looker Studio obnovuje dotazy při interakci a na cache rozpisu; pokud populární dashboard dotazuje full-history nepartitionovanou tabulku, skenované byty — a účet — rychle kumulují.

Praktická startovní sada dashboardů:

• Executive overview — blended utrácení, konverze a CRM revenue napříč kanály, trendované v čase.
• Account overview — per-account výkon pro MCC nastavení s použitím account-map friendly jmen.
• Campaign drill-down — utrácení, CPA, cost-per-revenue a GA4 engagement podle kampaně a reklamní skupiny.

Nákladové kontroly, které skutečně záleží:

Monitorujte nejdražší dotazy. INFORMATION_SCHEMA.JOBS view odhaluje každý dotaz, kdo ho spustil a kolik bytů zpracoval. Týdenní pohled na top konzumenty povrchuje jeden report nebo scheduled query, který tiše dominuje vašemu účtu, takže ho můžete optimalizovat — obvykle přidáním partition filtru nebo materializací joinu.

SELECT
  user_email,
  query,
  total_bytes_processed / POW(10, 12) AS tb_processed
FROM `region-eu`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
ORDER BY total_bytes_processed DESC
LIMIT 20;

Nastavte billing rozpočet a alert na Cloud projekt bez ohledu na to, jak opatrní jste. Je to backstop, který proměňuje runaway dotaz z překvapení na konci měsíce na notifikaci stejný den.

S kurátorovanými tabulkami, partition filtry, BI Engine pro horké dashboardy a týdenním nákladovým monitoringem single-account warehouse pohodlně zůstává v rozsahu 30-150 €/měsíc a slouží rychlé, důvěryhodné reporty.

SQL vzory pro incrementalitu a základy LTV

Warehouse není jen hezčí reporting — je to substrát pro měřicí práci, která pohání skutečná rozpočtová rozhodnutí. Tady jsou základní vzory.

Geo-holdout incrementalita. Incrementality testování ptá se, které konverze by se staly bez reklam. Warehouse dělá analýzu triviální, jakmile běží geo experiment: otagujte regiony jako test nebo control, pak srovnejte konverzní rate napříč dvěma skupinami pro test okno.

-- incrementality lift by region group
SELECT
  geo_group,
  SUM(conversions) / SUM(sessions) AS conversion_rate
FROM reporting.geo_experiment
WHERE date BETWEEN @test_start AND @test_end
GROUP BY geo_group;

Lift mezi testem a controlem, normalizovaný na velikost skupiny, odhaduje inkrementální příspěvek. Metodologie a design testu žijí v našem průvodci incrementality testováním; BigQuery je tam, kde to počítáte ve škále a znovu-spouštíte každé čtvrtletí bez manuálního úsilí.

Cohort retention a LTV. Lifetime value modelování začíná cohortingem zákazníků podle akvizičního měsíce a sledováním revenue dopředu.

-- monthly acquisition cohorts with cumulative revenue
SELECT
  DATE_TRUNC(first_close_date, MONTH) AS cohort_month,
  DATE_DIFF(revenue_month, first_close_date, MONTH) AS month_index,
  SUM(revenue) AS cohort_revenue
FROM reporting.customer_revenue_monthly
GROUP BY cohort_month, month_index
ORDER BY cohort_month, month_index;

Tato cohort matice je surovým materiálem pro LTV křivky, payback-period analýzu a CAC-to-LTV poměry podle akvizičního kanálu — metriky, které by měly řídit rozpočtovou alokaci. Spojené zpět s kampaní, která vyprodukovala GCLID každého zákazníka, můžete vypočítat LTV podle kampaně, ne jen CPA podle kampaně, což je daleko lepší optimalizační cíl. Naše analýza CAC payback podle vertikály ukazuje benchmarky, které tyto dotazy krmí.

Blended cross-channel reporting. Jakmile Meta, LinkedIn a další kanály přistávají ve stejném warehouse s konzistentními definicemi, jediný UNION ALL denní tabulky každého kanálu následovaný GROUP BY channel s SAFE_DIVIDE(SUM(revenue), SUM(cost)) pro blended ROAS produkuje cross-channel pohled, který žádné platformové UI nemůže. Těžká část jsou konzistentní definice, ne SQL.

Vstup marketing mix modelu. Pokročilé týmy krmí warehouse přímo do MMM. Open-source Meridian framework od Googlu čte týdně agregované utrácení a výsledky — přesně tvar, který vaše reporting tabulky již produkují. Jediný scheduled query pivotuje denní data do týdenní channel matice, kterou Meridian očekává, uzavírajíc smyčku od surových click dat ke statistickému budget modelování.

Tyto vzory jsou základy, ne hotové modely, ale jsou těžkou částí strukturálně správně. S čistými, spojenými, partitionovanými daty v BigQuery se vrstvení incrementality, LTV a MMM na vrchu stává analytickým cvičením spíše než data-plumbing noční můrou.

Pro širší kontext o privacy a tracking posunech, které dělají first-party warehouse stále esenciálnější, viz našeho průvodce first-party data strategií a analýzu cookieless budoucnosti.

Reporting warehouse vám říká, kam peníze šly; optimalizační vrstva rozhoduje, kam jdou další. Pokud chcete AI-driven Google Ads optimalizaci, která může jednat na cost-per-revenue signály, které váš BigQuery pipeline povrchuje, SteerAds běží zdarma 14denní audit na Google a Microsoft Ads účtech.

Zdroje

• cloud.google.com/bigquery/docs/google-ads-transfer — Google Ads Data Transfer Service dokumentace
• cloud.google.com/bigquery/docs — BigQuery dokumentace
• support.google.com/analytics — GA4 BigQuery export dokumentace
• cloud.google.com/looker/docs/studio — Looker Studio dokumentace
• cloud.google.com/blog/products/data-analytics — Google Cloud data analytics blog

FAQ