ETL proces pro SEO data: integrace BigQuery, dbt a Looker

ETL pro SEO jako datový produkt

V moderním SEO již nestačí ad-hoc export z Google Search Console nebo jednorázový crawl. Potřebujeme opakovatelný, auditovatelný a škálovatelný ETL (Extract–Transform–Load) proces, který sjednotí signály z vyhledávačů, analytiky, serverových logů a nástrojů na crawling do jednoho modelu pravdy. Tato architektura stojí na třech pilířích: BigQuery (datové jezero/warehouse a výpočetní engine), dbt (transformace, testy a dokumentace) a Looker (semantická vrstva a vizualizace pro rozhodování). Cílem je učinit ze SEO dat produkt s jasnými SLA, metrikami a CI/CD procesem, nikoliv jen „dataset na požádání“.

Architektura: logická vrstva a datové zóny

Doporučená architektura rozděluje pipeline na zóny s jasnou odpovědností a politikou změn:

• Landing/Raw: surová data ze zdrojů (GSC, GA4, logy, SERP API, crawl). Bez zásahu, pouze minimální normalizace typů.
• Staging: lehké čištění, deduplikace, unifikace názvů polí, primární klíče a indexy. Zde začíná dbt.
• Core: business logika – mapování entit (URL → kanonická stránka → topic cluster), výpočty metrik (imprese, CTR, share of voice), propojení s obsahovým CMS a CRM.
• Marts: účelové datové martiny pro persony (SEO stratég, technický SEO, content lead, product manager) a use-case (programmatic SEO, interní linkování, monitoring regresí).

Zdroj SEO dat a jejich specifika

• Google Search Console: metriky na úrovni dotazu a stránky (imprese, kliknutí, pozice). Omezené samplingem a zpožděním; vyžaduje agregaci a denní snapshoty.
• GA4: session/user eventy pro organický provoz; nutné filtrovat zdroj/medium a definovat vlastní dimenze (landing page kanonická, obsahový typ, clustery).
• Serverové logy: hity crawlerů (Googlebot, Bingbot), HTTP kódy, latence, velikost odpovědi; klíčové pro crawl budget a technické anomálie.
• Crawl data: on-page signály (status, title, H1, canonical, robots, meta robots, schema.org), hluboké paginace a facety.
• SERP a konkurence: pozice, pixel features (People Also Ask, Top Stories), odhad viditelnosti, entity extrahované z výsledků.
• CMS/produkt: datové dimenze (kategorie, autor, jazyk, datum publikace), šablony a komponenty pro programmatic SEO.

Ingestion do BigQuery: spolehlivost, schéma a idempotence

• Batch vs. streaming: GSC/GA4 postačuje batch (denně/hodiny); logy a SERP mohou jít streamingem přes Pub/Sub → BigQuery.
• Particionování: podle data události (_PARTITIONDATE) nebo timestampu; snižuje náklady a zlepšuje průtok.
• Klastrování: podle url_host, canonical_id, query_hash; urychluje dotazy ve velkých tabulkách.
• Idempotence: deduplikace klíče (např. date, country, device, query_hash, url_hash) a MERGE operace, aby opakované nahrávání nezduplikovalo data.
• Data Contracts: popis schémat (typy, povinná pole, povolené hodnoty), verzování a zpětná kompatibilita.

dbt jako srdce transformací a kvality dat

dbt překládá SQL logiku na version-controlled modely s testy, dokumentací a lineage. Klíčové praktiky:

• Modelová vrstva: stg_* (staging), int_* (intermediate joiny), dim_*/fct_* (dimenze a fakty), mart_* (spotřební modely).
• Inkremetální modely: insert_overwrite podle datového partitionu pro GSC/GA4; výrazně šetří výpočetní výkon.
• SCD a snapshoty: sledování změn kanonických URL, meta tagů a šablon (SCD2), historizace pro audit regresí.
• Testy: unique, not_null, accepted_values, relationships; vlastní testy (např. „CTR <= 1“, „status_code ∈ {200,301,302,404,410,500}“).
• Makra: normalizace URL, parsování parametrických stránek, extrakce domény, UTM sanitace, hashování dlouhých klíčů.
• Exposures a sources freshness: definujte závislosti pro Looker dashboardy a nastavte čerstvost (SLA) pro landing data.

Modelování SEO metrik v Core vrstvě

• Dimenze: dim_url (kanonická URL, šablona, jazyk), dim_query (lemmatizovaná fráze, intent, entita), dim_content (autor, obsahový typ), dim_serp_feature.
• Fakty: fct_gsc_daily (kliknutí, impresie, pozice), fct_log_hits (hity botů, kódy), fct_crawl (statusy elementů), fct_serp (pozice, presence feature), fct_ga4_sessions.
• Odvozené metriky: visibility_index (vážené impresie/podíl), health_score (kombinace technických signálů), content_score (kompletnost a čitelnost), internal_link_rank (PageRank-like metrika na grafu interních odkazů).

BigQuery výkonnost a náklady

• Dotazy s průnikem: vždy filtrovat na _PARTITIONTIME/date a relevantní klustry.
• Materializované pohledy: na agregace GSC a GA4 pro 7/28-denní okna; úspora nákladů při Looker exploracích.
• Storage třídy: time-travel na 7 dní, dlouhodobé archívy přesunout do levnější třídy; snapshoty v samostatných datasetech.
• Kvóty a cost guardrails: limity na sloty, upozornění při překročení skenovaných GB a cache-friendly dotazy.

Looker jako semantická vrstva a rozhodovací panel

• LookML modely: definujte dimenze, measures a drill fields tak, aby skryly SQL složitost a zachovaly konzistentní definici metrik.
• Explores: podle person („SEO Health“, „Content Performance“, „Crawl Budget“, „SERP Visibility“), v pozadí mart tabulky.
• PDT a cache: perzistentní odvozené tabulky (na BigQuery) pro náročné agregace; plánování obnovy podle SLA.
• Řízení přístupu: row-level security (jazyk, země, brand), tagování polí s citlivými údaji.
• Distribuce insightů: naplánované „Looks“ do e-mailu/Slack, alerty na pokles CTR, nárůst 5xx, změnu kanonizace.

Automatizace a orchestrácia: plánovače a CI/CD

• dbt Cloud / Airflow (Cloud Composer): denní/job-based plánování, paralelizace podle zdrojů, retry politiky, SLA alerty.
• CI/CD: git flow, pull requesty s automatickými dbt build a testy na sandbox datasete, schvalování schémat (data contracts).
• Observabilita: Lineage graf (dbt docs), metriky úspěšnosti jobů, sledování délky a ceny dotazů, anomálie v počtech řádků.

Kvalita dat: testy, validace a anomálie

• Syntaxové testy: not_null/unique/relationships v dbt; povinná pole date, canonical_id, query_hash.
• Semantické testy: CTR mezi 0–1, position > 0, status_code povolené hodnoty, robot directives v doméně pravidel.
• Čerstvost: source freshness na GSC/GA4/logy; alarmy při zpoždění > X hodin.
• Detekce anomálií: pohyblivé prahy a robustní percentily (např. MAD) na impresie, 404, 5xx, změny kanonizace, velké skoky ve vnořených facetech.

Programmatic SEO: od dat ke stránkám

• Datové šablony: připravené modely s parametry (entita, atributy, porovnání), které Looker/BI tým ověří a produktový systém použije při generování stránek.
• Skóre příležitostí: kombinace poptávky (imprese/volume), konkurenceschopnosti (share of voice), technického zdraví a obsahových mezer.
• Interní linkování: graf interních odkazů, identifikace „orphan“ a „bridge“ uzlů, návrhy odkazů na základě tematické blízkosti.
• Validace po nasazení: zpětná vazba z GSC/GA4/logů v 7/28-denních oknech, A/B holdout segmenty a obsahové regresní testy.

Mapování entit a intentů

Pro SEO je klíčové přiřadit dotazy k entitám a intentům. V Core vrstvě udržujte slovník entit (produkty, kategorie, lokality) s aliasy a lemmatizací. V dbt modelech vytvořte mapu query → entity → topic cluster a „intent flags“ (informational, navigational, transactional). Tyto dimenze následně využívá Looker při kalkulaci visibility a při obsahové prioritizaci.

Standardy pojmenování a verzování

• Datasety: raw_*, stg_*, core_*, mart_*.
• Sloupce: canonical_id, url_hash, query_hash, event_date, country_code, device_type.
• SemVer pro modely: významné změny schémat jako major/minor/patch; migrační kroky MERGE/CREATE OR REPLACE s dočasným paralelním během.

Bezpečnost a governance

• Přístupové role: čtení vs. zápis, oddělení produkce a sandboxu; omezení na úrovni tabulek a řádků (RLS).
• Citlivá pole: hashování nebo odstranění PII, tagování v Looker/BigQuery, DLP skeny při ingestion.
• Audit a lineage: dbt docs a Looker usage logs pro sledování dopadů změn na dashboardy.

KPI a SLA pro SEO datový produkt

• SLA čerstvosti: GSC do 12 hodin, logy do 1 hodiny, crawl do 24 hodin.
• Dostupnost dashboardů: > 99,5 % v pracovních hodinách, plánovaná okna deployů.
• Přesnost metrik: odchylka mezi GSC/GA4 surovými exporty a mart vrstvou < 1 %.
• Time-to-Insight: nový obsah → první metriky v marte do 24 hodin.

Příklad end-to-end workflow (den D)

1. Ingestion: raw GSC, GA4, logy a crawl do raw_* s partitioningem podle data.
2. dbt stg_*: typy, dedupe, normalizace URL, výpočet hashů.
3. dbt core_*: join na dim_url, dim_query, výpočet visibility a health skóre.
4. dbt mart_*: person-orientované tabulky (SEO Health, Content Performance, Crawl Budget).
5. Looker: naplánovaná obnova PDT, alerty na outliery (5xx, CTR drop, orphan nárůst).
6. CI/CD: merge pull request, automatické testy, publikace dokumentace (dbt docs) a release poznámky.

Kontrolní seznam implementace

• Particionované a klastrované tabulky v BigQuery; MERGE pro idempotentní načítání.
• dbt modely se staging/core/marts, inkrementální materializace, snapshoty pro SCD.
• Testy kvality (unique, not_null, accepted_values) a source freshness alarmy.
• LookML semantika, RLS, PDT pro náročné agregace, plánované dashboardy a alerty.
• Orchestrace (dbt Cloud/Composer), CI/CD s automatickým dbt build na sandboxu.
• Governance: data contracts, dokumentace, lineage, monitoring nákladů.

Rizika a mitigace

• Sampling a zpoždění: definujte oficiální reportovací okna (T-1, T-7), agregujte na stabilní periody.
• Nekonzistentní URL: přísná normalizace, kanonizace, mapování parametrů; testy na duplicitu kanonických ID.
• Náklady: materializované view, cache, průnik podle partition; pravidelné revize klastrovaní.
• Změny schémat zdrojů: kontrakty a „canary“ joby; fallback na poslední úspěšný build.

Od ETL k rozhodování v reálném čase

BigQuery, dbt a Looker společně vytvářejí robustní rámec, ve kterém jsou SEO data konzistentní, auditovatelná a okamžitě použitelná pro obsahová i technická rozhodnutí. K