ETL pro SEO data: integrace BigQuery, dbt a Lookeru

SEO a SEO optimalizace
Dalimil
ETL pre SEO dáta: BigQuery, dbt a Looker integrácia

ETL pro SEO jako datový produkt

V moderním SEO už nestačí ad hoc export z Google Search Console nebo jednorázový crawl. Potřebujeme opakovatelný, auditovatelný a škálovatelný proces ETL (Extract–Transform–Load), který sjednotí signály z vyhledávačů, analytiky, serverových logů a nástrojů pro 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 (sémantická vrstva a vizualizace pro rozhodování). Cílem je vytvořit ze SEO dat produkt s jasnými SLA, metrikami a procesem CI/CD, nikoli jen „dataset na vyžádání“.

Architektura: logická vrstva a datové zóny

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

  • Landing/Raw: surová data ze zdrojů (GSC, GA4, logy, SERP API, crawl). Bez zásahů, pouze s minimální normalizací typů.
  • Staging: základní čištění, deduplikace, sjednocení názvů polí, primární klíče a indexy. Zde začíná dbt.
  • Core: business logika – mapování entit (URL → kanonická stránka → tematický cluster), výpočty metrik (impressions, CTR, share of voice), propojení s obsahovým CMS a CRM.
  • Marts: účelové datové marty pro persony (SEO stratég, technický SEO specialista, content lead, produktový manažer) a případy použití (programmatic SEO, interní prolinkování, monitoring regresí).

Zdroje SEO dat a jejich specifika

  • Google Search Console: metriky na úrovni dotazů a stránek (impressions, clicks, position). Omezeno vzorkováním a zpožděním; vyžaduje agregaci a denní snapshoty.
  • GA4: události relací/uživatelů pro organickou návštěvnost; je nutné filtrovat zdroj/medium a definovat vlastní dimenze (kanonická vstupní stránka, typ obsahu, clustery).
  • Serverové logy: požadavky crawlerů (Googlebot, Bingbot), HTTP kódy, latence, velikost odpovědi; klíčový zdroj pro crawl budget a technické anomálie.
  • Crawl data: signály on-page (status, title, H1, canonical, robots, meta robots, schema.org), hluboká stránkování a facety.
  • SERP a konkurence: pozice, prvky ve výsledcích (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: pro GSC/GA4 stačí dávkové zpracování (denně/hodinově); logy a SERP mohou být zpracovávány streamově přes Pub/Sub → BigQuery.
  • Partitioning: podle data události (_PARTITIONDATE) nebo timestampu; snižuje náklady a zlepšuje ořezávání partitionů.
  • Clustering: podle url_host, canonical_id, query_hash; urychluje dotazy ve velkých tabulkách.
  • Idempotence: deduplikační klíče (např. date, country, device, query_hash, url_hash) a operace MERGE, aby opakované načítání nezdvojovalo data.
  • Datové kontrakty: popis schémat (typy, povinná pole, povolené hodnoty), verzování a zpětná kompatibilita.

dbt jako srdce transformací a kvality dat

dbt převádí logiku SQL na verzované modely s testy, dokumentací a lineage. Klíčové postupy:

  • Modelová vrstva: stg_* (staging), int_* (mezilehlé joiny), dim_*/fct_* (dimenze a fakta), mart_* (modely pro spotřebu).
  • Inkrementá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), ukládání historie 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, sanitizace UTM, hashování dlouhých klíčů.
  • Exposures a aktuálnost zdrojů: definujte závislosti pro dashboardy Looker a nastavte aktuálnost (SLA) pro landing data.

Modelování SEO metrik ve vrstvě Core

  • Dimenze: dim_url (kanonická URL, šablona, jazyk), dim_query (lemmatizovaná fráze, intent, entita), dim_content (autor, typ obsahu), dim_serp_feature.
  • Fakta: fct_gsc_daily (kliknutí, imprese, pozice), fct_log_hits (požadavky botů, kódy), fct_crawl (stavy prvků), fct_serp (pozice, přítomnost prvků), fct_ga4_sessions.
  • Odvozené metriky: visibility_index (vážené imprese/podíl), health_score (kombinace technických signálů), content_score (úplnost a čitelnost), internal_link_rank (metrika podobná PageRanku založená na grafu interních odkazů).

Výkon a náklady BigQuery

  • Dotazy s ořezáváním partitionů: vždy filtrovat podle _PARTITIONTIME/date a relevantních clusterů.
  • Materializované pohledy: pro agregace GSC a GA4 v 7/28denních oknech; úspora nákladů při explorování v Looker.
  • Třídy úložiště: time travel na 7 dní, dlouhodobé archivy přesunout do levnější třídy; snapshoty ukládat do samostatných datasetů.
  • Kvóty a ochranné limity nákladů: limity slotů, upozornění při překročení skenovaných GB a dotazy optimalizované pro cache.

Looker jako sémantická vrstva a panel pro rozhodování

  • Modely LookML: definujte dimenze, measures a drill fields tak, aby skryly složitost SQL a zachovaly konzistentní definice metrik.
  • Explores: podle person („SEO Health“, „Content Performance“, „Crawl Budget“, „SERP Visibility“), v pozadí jsou tabulky martů.
  • PDT a cache: perzistentní odvozené tabulky (v BigQuery) pro náročné agregace; plánování aktualizace podle SLA.
  • Řízení přístupu: zabezpečení na úrovni řádků (jazyk, země, značka), označování polí s citlivými údaji.
  • Distribuce insightů: plánované „Looks“ zasílané e-mailem/do Slacku, upozornění na pokles CTR, nárůst 5xx, změnu kanonizace.

Automatizace a orchestrace: plánovače a CI/CD

  • dbt Cloud / Airflow (Cloud Composer): denní/plánování podle úloh, paralelizace podle zdrojů, pravidla opakování, upozornění na porušení SLA.
  • CI/CD: git flow, pull requesty s automatickým dbt build a testy v sandboxovém datasetu, schvalování schémat (datové kontrakty).
  • Observabilita: graf lineage (dbt docs), metriky úspěšnosti úloh, 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.
  • Sémantické testy: CTR mezi 0–1, position > 0, povolené hodnoty status_code, direktivy robotů v rámci definovaných pravidel.
  • Aktuálnost: source freshness pro GSC/GA4/logy; upozornění při zpoždění > X hodin.
  • Detekce anomálií: pohyblivé prahové hodnoty a robustní percentily (např. MAD) pro imprese, 404, 5xx, změny kanonizace, velké skoky u vnořených facetů.

Programmatic SEO: od dat ke stránkám

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

Mapování entit a intentů

Pro SEO je klíčové přiřazovat dotazy k entitám a intentům. Ve vrstvě Core udržujte slovník entit (produkty, kategorie, lokality) s aliasy a lemmatizací. V modelech dbt vytvořte mapu query → entity → topic cluster a „intent flags“ (informational, navigational, transactional). Tyto dimenze pak Looker využije při výpočtu viditelnosti a při určování priorit obsahu.

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, označování v Looker/BigQuery, DLP kontroly při ingestion.
  • Audit a lineage: dbt docs a protokoly používání Looker pro sledování vlivu změn na dashboardy.

KPI a SLA pro datový produkt SEO

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

Příklad workflow od začátku do konce (den D)

  1. Ingestion: raw GSC, GA4, logy a crawl do raw_* s partitioningem podle data.
  2. dbt stg_*: typy, deduplikace, normalizace URL, výpočet hashů.
  3. dbt core_*: join na dim_url, dim_query, výpočet skóre viditelnosti a zdraví.
  4. dbt mart_*: tabulky zaměřené na persony (SEO Health, Content Performance, Crawl Budget).
  5. Looker: plánovaná aktualizace PDT, upozornění na odlehlé hodnoty (5xx, pokles CTR, nárůst orphan).
  6. CI/CD: sloučení pull requestu, automatické testy, publikace dokumentace (dbt docs) a poznámek k vydání.

Kontrolní seznam implementace

  • Partitionované a clusterované tabulky v BigQuery; MERGE pro idempotentní načítání.
  • Modely dbt s vrstvami staging/core/marts, inkrementální materializací, snapshoty pro SCD.
  • Testy kvality (unique, not_null, accepted_values) a upozornění na aktuálnost zdrojů.
  • Sémantika LookML, RLS, PDT pro náročné agregace, plánované dashboardy a upozornění.
  • Orchestrace (dbt Cloud/Composer), CI/CD s automatickým dbt build v sandboxu.
  • Governance: datové kontrakty, dokumentace, lineage, monitoring nákladů.

Rizika a jejich zmírnění

  • Vzorkování a zpoždění: definujte oficiální reportovací okna (T-1, T-7), agregujte do stabilních období.
  • Nekonzistentní URL: důsledná normalizace, kanonizace, mapování parametrů; testy duplicity kanonických ID.
  • Náklady: materializované pohledy, cache, ořezávání podle partitionů; pravidelné revize clusteringu.
  • Změny schémat zdrojů: kontrakty a „canary“ úlohy; návrat k poslednímu úspěšnému buildu.

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

BigQuery, dbt a Looker společně vytvářejí robustní rámec, v němž jsou SEO data konzistentní, auditovatelná a okamžitě použitelná pro obsahová i technická rozhodnutí. Když pipeline doplníte o jasná SLA, CI/CD, testy kvality a sémantickou vrstvu, stanou se SEO data spolehlivou infrastrukturou pro programmatic SEO, určování priorit backlogu a operativní zásahy při regresích. Výsledkem je rychlejší „time-to-insight“, menší riziko chyb a vyšší dopad každého nasazení na organický výkon.