{"id":20774,"date":"2025-05-27T11:58:13","date_gmt":"2025-05-27T09:58:13","guid":{"rendered":"https:\/\/www.dase-analytics.com\/blog\/?p=20774\/"},"modified":"2025-05-27T14:25:31","modified_gmt":"2025-05-27T12:25:31","slug":"bigquery-ga4-sql-minimum-pre-marketerov","status":"publish","type":"post","link":"https:\/\/www.dase-analytics.com\/blog\/sk\/bigquery-ga4-sql-minimum-pre-marketerov\/","title":{"rendered":"BigQuery + GA4: SQL minimum pre market\u00e9rov"},"content":{"rendered":"

Pracujete s Google Analytics a ob\u010das potrebujete z\u00edska\u0165 \u201craw\u201d \u00fadaje z BigQuery<\/strong> \u2014 napr\u00edklad ko\u013eko kr\u00e1t sa spustil ur\u010dit\u00fd event alebo odkia\u013e pri\u0161li va\u0161i n\u00e1v\u0161tevn\u00edci \u2014 no mus\u00edte zaka\u017ed\u00fdm \u010daka\u0165, k\u00fdm v\u00e1m niekto z t\u00edmu vytiahne d\u00e1ta z BigQuery, preto\u017ee neovl\u00e1date SQL?\u00a0<\/span><\/p>\n

Tento \u010dl\u00e1nok je ur\u010den\u00fd pr\u00e1ve v\u00e1m. Uk\u00e1\u017eeme si v \u0148om z\u00e1klady, ktor\u00e9 v\u00e1m umo\u017enia samostatne z\u00edska\u0165 d\u00e1ta z BigQuery. Teda za predpokladu, \u017ee u\u017e BigQuery m\u00e1te<\/strong> 😀\u00a0<\/span><\/p>\n

Ak nie, tento \u010dl\u00e1nok <\/span>v\u00e1m pom\u00f4\u017ee vytvori\u0165 Google Cloud \u00fa\u010det – nevyhnutn\u00fd predpoklad k prepojeniu Google Analytics a BigQuery.<\/span><\/p>\n\n\t

\n\t
\n\t\t\n\t\t
<\/div>\n\t\t<\/a>\n\t\t
\n\t\t
\n\t\t\t\n \t\t Vytvorenie Google Cloud (GCP) \u00fa\u010dtu krok za krokom<\/span>\t\n\t\t\t<\/a>\n\t\t\t
\n\t\t\t Z\u00e1klady analytiky<\/a> | \n\t\t\t J\u00falius Selnekovi\u010d<\/a> |\n\t\t\t \n\t\t\t \t
\n\t\t \t

Google Cloud Platform (GCP) pon\u00faka \u0161irok\u00fa \u0161k\u00e1lu n\u00e1strojov a slu\u017eieb, ktor\u00e9 umo\u017e\u0148uj\u00fa firm\u00e1m aj analytikom efekt\u00edvne spravova\u0165 cloudov\u00e9...<\/p>\n\t\t <\/article>\n\t\t<\/div>\n\t <\/div>\n\t<\/section>\n\t\n

Jednou z v\u00fdhod integr\u00e1cie BigQuery s GA4 je pr\u00edstup k surov\u00fdm, nevzorkovan\u00fdm d\u00e1tam.<\/strong> GA4 export do BigQuery obsahuje \u0161ir\u0161iu \u0161k\u00e1lu d\u00e1t, vr\u00e1tane t\u00fdch, ktor\u00e9 s\u00fa generovan\u00e9 aj bez s\u00fahlasu pou\u017e\u00edvate\u013eov, tzv. anonymizovan\u00e9 cookieless pingy.<\/span><\/p>\n

Ak BigQuery v\u00f4bec nepozn\u00e1te, jedna z vec\u00ed na \u010do je BiqQuery u\u017eito\u010dn\u00e9 si m\u00f4\u017eete pre\u010d\u00edta\u0165 tu.<\/strong> 👇<\/p>\n\n\t

\n\t
\n\t\t\n\t\t
<\/div>\n\t\t<\/a>\n\t\t
\n\t\t
\n\t\t\t\n \t\t BigQuery – \u017eiadne cookies, \u017eiadny probl\u00e9m… skoro<\/span>\t\n\t\t\t<\/a>\n\t\t\t
\n\t\t\t Analytika a biznis<\/a> | \n\t\t\t Sab\u00edna Fa\u010dkovcov\u00e1<\/a> |\n\t\t\t \n\t\t\t \t
\n\t\t \t

Cookie Banner, Consent mode \u010di GDPR – to s\u00fa stra\u0161iaky modern\u00fdch webov. Ka\u017ed\u00fd z n\u00e1s d\u00f4verne pozn\u00e1 r\u00f4zne...<\/p>\n\t\t <\/article>\n\t\t<\/div>\n\t <\/div>\n\t<\/section>\n\t\n

Ako sa dosta\u0165 do BigQuery<\/b><\/h2>\n
    \n
  1. Otvorte<\/span> Google Cloud Console<\/span><\/a> a prihl\u00e1ste sa do svojho Google \u00fa\u010dtu<\/span><\/li>\n
  2. Vyberte projekt, v ktorom sa nach\u00e1dzaj\u00fa va\u0161e d\u00e1ta<\/span><\/li>\n
  3. Do vyh\u013ead\u00e1vacieho po\u013ea za\u010dnite p\u00edsa\u0165 <\/span>BigQuery<\/b><\/li>\n
  4. Z v\u00fdsledkov vyberte <\/span>BigQuery (Data warehouse\/analytics)<\/b><\/li>\n<\/ol>\n

    \"BigQuery<\/a><\/p>\n

    V rozhran\u00ed BigQuery na \u013eavej strane kliknite na \u0161\u00edpku ved\u013ea n\u00e1zvu po\u017eadovan\u00e9ho datasetu. Zobrazia sa v\u00e1m v\u0161etky dostupn\u00e9 tabu\u013eky.\u00a0<\/span><\/p>\n

    Kliknut\u00edm na n\u00e1zov tabu\u013eky si zobraz\u00edte jej sch\u00e9mu \u2014 teda zoznam v\u0161etk\u00fdch dostupn\u00fdch st\u013apcov a typov d\u00e1t.\u00a0<\/span><\/p>\n

    Nad sch\u00e9mou n\u00e1jdete tla\u010didlo <\/span>„Query“<\/b> \u2013 kliknite na\u0148 a otvor\u00ed sa v\u00e1m nov\u00e9 okno, kde m\u00f4\u017eete za\u010da\u0165 p\u00edsa\u0165 SQL dopyty.<\/span><\/p>\n

    \"BigQuery<\/a><\/p>\n

    Pre \u00fa\u010dely tohto \u010dl\u00e1nku budeme pou\u017e\u00edva\u0165 verejne dostupn\u00fd dataset <\/span>ga4_obfuscated_sample_ecommerce<\/b>.<\/b>N\u00e1zov v\u00e1\u0161ho vlastn\u00e9ho GA4 datasetu bude za\u010d\u00edna\u0165 predponou <\/span>analytics_<\/b>.<\/span><\/p>\n

    Pozn\u00e1mka:<\/strong>
    \nGA4 export do BigQuery funguje ako tzv. „sharded table“ \u2013 teda tabu\u013eka rozdelen\u00e1 po d\u0148och. To znamen\u00e1, \u017ee miesto jednej ve\u013ekej tabu\u013eky m\u00e1te viacero tabuliek, ktor\u00e9 maj\u00fa n\u00e1zov vo form\u00e1te:events_YYYYMMDD<\/code> (napr. events_20250101<\/code> pre 1. janu\u00e1r 2025).Ak vid\u00edte n\u00e1zov tabu\u013eky vo form\u00e1te events_(10)<\/code>, znamen\u00e1 to, \u017ee ide o zjednodu\u0161en\u00e9 zobrazenie, ktor\u00e9 zah\u0155\u0148a 10 denn\u00fdch tabuliek.<\/div>\n

    Z\u00e1kladn\u00e1 \u0161trukt\u00fara dopytu (query)<\/b><\/h2>\n

    Aby ste mohli z\u00edska\u0165 konkr\u00e9tne d\u00e1ta z BigQuery, potrebujete nap\u00edsa\u0165 <\/span>SQL dopyt<\/b>. Ten sa sklad\u00e1 z viacer\u00fdch \u010dast\u00ed. Po\u010fme si vysvetli\u0165, \u010do ka\u017ed\u00e1 z nich znamen\u00e1 \u2013 a rovno si to uk\u00e1\u017eeme na jednoduchom pr\u00edklade:<\/span><\/p>\n

    SELECT<\/strong><\/p>\n

    \u00a0<\/span>event_date<\/span>,<\/span><\/p>\n

    \u00a0<\/span>device.category<\/span>,<\/span><\/p>\n

    \u00a0<\/span>COUNT<\/span>(*)<\/span> AS<\/span> devices_count<\/span><\/p>\n

    FROM<\/strong><\/p>\n

    \u00a0<\/span>bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`<\/span><\/p>\n

    WHERE<\/strong><\/p>\n

    \u00a0<\/span>event_name<\/span> = <\/span>‚page_view‘<\/span><\/p>\n

    GROUP BY ALL<\/strong><\/p>\n

    ORDER<\/span> BY<\/span> event_date<\/span><\/p>\n

    \"BigQuery<\/a><\/p>\n

    Vysvetlenie jednotliv\u00fdch \u010dast\u00ed:<\/b><\/h3>\n