ETL pre SEO dáta

ETL pre SEO ako dátový produkt

V modernom SEO už nestačí ad-hoc export z Google Search Console alebo jednorazový crawl. Potrebujeme opakovateľný, auditovateľný a škálovateľný ETL (Extract–Transform–Load) proces, ktorý zjednotí signály z vyhľadávačov, analytiky, serverových logov a nástrojov na crawling do jedného modelu pravdy. Táto architektúra stojí na troch pilieroch: BigQuery (dátové jazero/warehouse a výpočtový engine), dbt (transformácie, testy a dokumentácia) a Looker (semantická vrstva a vizualizácie pre rozhodovanie). Cieľom je urobiť zo SEO dát produkt s jasnými SLA, metríkami a CI/CD procesom, nie len „dataset na požiadanie“.

Architektúra: logická vrstva a dátové zóny

Odporúčaná architektúra rozdeľuje pipeline na zóny s jasnou zodpovednosťou a politikou zmeny:

• Landing/Raw: surové dáta zo zdrojov (GSC, GA4, logy, SERP API, crawl). Bez zásahu, len minimálna normalizácia typov.
• Staging: ľahké čistenie, deduplikácia, unifikácia názvov polí, primárne kľúče a indexy. Tu začína dbt.
• Core: business logika – mapovanie entít (URL → kanonická stránka → topic cluster), výpočty metrík (impressions, CTR, share of voice), spojenie s obsahovým CMS a CRM.
• Marts: účelové dátové martíny pre persony (SEO stratég, technický SEO, content lead, product manager) a use-case (programmatic SEO, interné linkovanie, monitoring regresií).

Zdroje SEO dát a ich špecifiká

• Google Search Console: query-level a page-level metriky (impressions, clicks, position). Limitované samplingom a oneskorením; potrebuje agregáciu a denné snapshoty.
• GA4: session/user eventy pre organik; nutné filtrovať zdroj/medium a definovať vlastné dimenzie (landing page canonical, obsahový typ, clustery).
• Serverové logy: hity crawlerov (Googlebot, Bingbot), HTTP kódy, latencia, veľkosť odpovede; kľúč pre crawl budget a technické anomálie.
• Crawl dáta: on-page signály (status, title, H1, canonical, robots, meta robots, schema.org), hlboké paginácie a facety.
• SERP a konkurencia: pozície, pixel features (People Also Ask, Top Stories), odhad viditeľnosti, entity extrahované z výsledkov.
• CMS/produkt: dátové dimenzie (kategória, autor, jazyk, dátum publikácie), šablóny a komponenty pre programmatic SEO.

Ingestion do BigQuery: spoľahlivosť, schéma a idempotencia

• Batch vs. streaming: GSC/GA4 stačí batch (denne/hodiny); logy a SERP môžu ísť streamingom cez Pub/Sub → BigQuery.
• Particionovanie: podľa dátumu udalosti (_PARTITIONDATE) alebo timestamp; znižuje náklady a zlepšuje prúning.
• Klustrovanie: podľa url_host, canonical_id, query_hash; urýchľuje dotazy vo veľkých tabuľkách.
• Idempotencia: dedupe kľúče (napr. date, country, device, query_hash, url_hash) a MERGE operácie, aby opakované načítania nezdvojovali dáta.
• Data Contracts: popis schém (typy, povinné polia, povolené hodnoty), verzovanie a spätná kompatibilita.

dbt ako srdce transformácií a kvality dát

dbt prekladá SQL logiku na version-controlled modely s testami, dokumentáciou a lineage. Kľúčové praktiky:

• Modelová vrstva: stg_* (staging), int_* (intermediate joiny), dim_*/fct_* (dimenzie a fakty), mart_* (spotrebné modely).
• Inkremetálne modely: insert_overwrite podľa dátového partitionu pre GSC/GA4; výrazne šetrí compute.
• SCD a snapshoty: sledovanie zmien kanonických URL, meta tagov a šablón (SCD2), historizácia pre audit regresií.
• Testy: unique, not_null, accepted_values, relationships; vlastné testy (napr. „CTR <= 1“, „status_code ∈ {200,301,302,404,410,500}“).
• Makrá: normalizácia URL, parsovanie parametrických stránok, extrakcia domény, UTM sanitácia, hashovanie dlhých kľúčov.
• Exposures a sources freshness: definujte závislosti pre Looker dashboardy a nastavte čerstvosť (SLA) pre landing data.

Modelovanie SEO metrík v Core vrstve

• Dimenzie: dim_url (kanonická URL, šablóna, jazyk), dim_query (lemmatizovaná fráza, intent, entita), dim_content (autor, obsahový typ), dim_serp_feature.
• Fakty: fct_gsc_daily (kliknutia, impresie, pozícia), fct_log_hits (hity botov, kódy), fct_crawl (statusy elementov), fct_serp (pozície, feature presence), fct_ga4_sessions.
• Derivované metriky: visibility_index (vážené impresie/podiel), health_score (kombinácia technických signálov), content_score (kompletizmus a čitateľnosť), internal_link_rank (PageRank-like metrika na grafe interných odkazov).

BigQuery výkonnosť a náklady

• Dotazy s prúningom: vždy filtrovať na _PARTITIONTIME/date a relevantné clustre.
• Materializované pohľady: na agregácie GSC a GA4 pre 7/28-dňové okná; šetrenie nákladov pri Looker exploroch.
• Storage triedy: time-travel na 7 dní, dlhodobé archívy presunúť do lacnejšej triedy; snapshoty v separátnych datasetoch.
• Kvóty a cost guardrails: limity na sloty, upozornenia pri prekročení skenovaných GB a cache-friendly dotazy.

Looker ako semantická vrstva a rozhodovací panel

• LookML modely: definujte dimenzie, measures a drill fields tak, aby skryli SQL zložitosť a zachovali konzistentnú definíciu metrík.
• Explores: podľa person („SEO Health“, „Content Performance“, „Crawl Budget“, „SERP Visibility“), v pozadí mart tabuľky.
• PDT a cache: perzistentné odvodené tabuľky (na BigQuery) pre náročné agregácie; plánovanie obnovy podľa SLA.
• Riadenie prístupu: row-level security (jazyk, krajina, brand), tagovanie polí s citlivými údajmi.
• Distribúcia insightov: naplánované „Looks“ do e-mailu/Slack, alerty na pokles CTR, nárast 5xx, zmenu kanonizácie.

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

• dbt Cloud / Airflow (Cloud Composer): denné/job-based plánovanie, paralelizácia podľa zdrojov, retry politiky, SLA alerts.
• CI/CD: git flow, pull requesty s automatickými dbt build a testami na sandbox datasete, schvaľovanie schém (data contracts).
• Observabilita: Lineage graf (dbt docs), metriky úspešnosti jobov, sledovanie dĺžky a ceny dotazov, anomálie v počtoch riadkov.

Kvalita dát: testy, validácie a anomálie

• Syntaktické testy: not_null/unique/relationships v dbt; povinné polia date, canonical_id, query_hash.
• Semantické testy: CTR medzi 0–1, position > 0, status_code povolené hodnoty, robot directives v doméne pravidiel.
• Čerstvosť: source freshness na GSC/GA4/logy; alarmy pri oneskorení > X hodín.
• Detekcia anomálií: pohyblivé prahy a robustné percentily (napr. MAD) na impresie, 404, 5xx, zmeny kanonizácie, veľké skoky vo vnorených facetoch.

Programmatic SEO: od dát k stránkam

• Datové šablóny: pripravené modely s parametrami (entita, atribúty, porovnania), ktoré Looker/BI tím overí a produktový systém použije pri generovaní stránok.
• Skóre príležitostí: kombinácia dopytu (impressions/volume), konkurenčnosti (share of voice), technického zdravia a obsahových medzier.
• Interné linkovanie: graf interných odkazov, identifikácia „orphan“ a „bridge“ uzlov, návrhy linkov na základe tematickej blízkosti.
• Validácia po nasadení: spätná väzba z GSC/GA4/logov v 7/28-dňových oknách, A/B holdout segmenty a obsahové regresné testy.

Mapovanie entít a intentov

Pre SEO je kľúčové priradiť dotazy k entitám a intentom. V Core vrstve udržiavajte slovník entít (produkty, kategórie, lokality) s aliasmi a lemmatizáciou. V dbt modeloch vytvorte mapu query → entity → topic cluster a „intent flags“ (informational, navigational, transactional). Tieto dimenzie následne využije Looker pri kalkulácii visibility a pri content prioritizácii.

Štandardy pomenovania a verzovanie

• Datasety: raw_*, stg_*, core_*, mart_*.
• Stĺpce: canonical_id, url_hash, query_hash, event_date, country_code, device_type.
• SemVer pre modely: významné zmeny schém ako major/minor/patch; migračné kroky MERGE/CREATE OR REPLACE s dočasným paralelným behom.

Bezpečnosť a governance

• Prístupové role: čítanie vs. zápis, oddelenie produkcie a sandboxu; obmedzenia na úrovni tabuliek a riadkov (RLS).
• Citlivé polia: hashovanie alebo odstránenie PII, tagovanie v Looker/BigQuery, DLP skeny pri ingestion.
• Audit a lineage: dbt docs a Looker usage logs pre sledovanie vplyvu zmien na dashboardy.

KPI a SLA pre SEO dátový produkt

• SLA čerstvosti: GSC do 12 hodín, logy do 1 hodiny, crawl do 24 hodín.
• Dostupnosť dashboardov: > 99,5 % v pracovných hodinách, plánované okná deployov.
• Presnosť metrík: odchýlka medzi GSC/GA4 surovými exportmi a mart vrstvou < 1 %.
• Time-to-Insight: nový obsah → prvé metriky v martoch do 24 hodín.

Príklad end-to-end workflow (deň D)

1. Ingestion: raw GSC, GA4, logy a crawl do raw_* s partiticioningom podľa dátumu.
2. dbt stg_*: typy, dedupe, normalizácia URL, výpočet hashov.
3. dbt core_*: join na dim_url, dim_query, výpočet visibility a health skóre.
4. dbt mart_*: person-orientované tabuľky (SEO Health, Content Performance, Crawl Budget).
5. Looker: naplánovaná obnova PDT, alerty na outliery (5xx, CTR drop, orphan nárast).
6. CI/CD: merge pull request, automatické testy, publikácia dokumentácie (dbt docs) a release poznámky.

Kontrolný zoznam implementácie

• Particionované a klastrované tabuľky v BigQuery; MERGE pre idempotentné načítanie.
• dbt modely so staging/core/marts, inkrementálna materializácia, snapshoty pre SCD.
• Testy kvality (unique, not_null, accepted_values) a source freshness alarms.
• LookML semantika, RLS, PDT pre ťažké agregácie, plánované dashboardy a alerty.
• Orchestrácia (dbt Cloud/Composer), CI/CD s automatickým dbt build na sandboxe.
• Governance: data contracts, dokumentácia, lineage, monitoring nákladov.

Riziká a mitigácie

• Sampling a oneskorenia: definujte oficiálne reportovacie okná (T-1, T-7), agregujte na stabilné periódy.
• Nekonzistentné URL: prísna normalizácia, kanonizácia, mapovanie parametrov; testy na duplicitu kanonických ID.
• Náklady: materializované view, cache, prúning podľa partition; pravidelné revízie klastrovania.
• Zmeny schém zdrojov: kontrakty a „canary“ joby; fallback na posledný úspešný build.

Z ETL na rozhodovanie v reálnom čase

BigQuery, dbt a Looker spoločne vytvárajú robustný rámec, v ktorom sú SEO dáta konzistentné, auditovateľné a okamžite použiteľné pre obsahové i technické rozhodnutia. Keď pipeline doplníte o jasné SLA, CI/CD, testy kvality a semantickú vrstvu, stáva sa z SEO dát spoľahlivá infraštruktúra pre programmatic SEO, prioritizáciu backlogu a operatívne zásahy pri regresiách. Výsledkom je rýchlejší „time-to-insight“, menšie riziko chýb a vyšší dopad každého nasadenia na organický výkon.