{"id":20156,"date":"2024-10-09T12:11:52","date_gmt":"2024-10-09T10:11:52","guid":{"rendered":"https:\/\/www.dase-analytics.com\/blog\/?p=20156\/"},"modified":"2024-10-14T11:05:59","modified_gmt":"2024-10-14T09:05:59","slug":"big-query-a-ga4-ziskajte-viac-dat-a-presnejsie-analyzy","status":"publish","type":"post","link":"https:\/\/www.dase-analytics.com\/blog\/sk\/big-query-a-ga4-ziskajte-viac-dat-a-presnejsie-analyzy\/","title":{"rendered":"BigQuery + GA4: Z\u00edskajte viac d\u00e1t a presnej\u0161ie anal\u00fdzy"},"content":{"rendered":"

Pohybujete sa v divokej d\u017eungli digit\u00e1lneho marketingu, kde s\u00fa d\u00e1ta z Google Analytics 4 (GA4) va\u0161\u00edm najlep\u0161\u00edm priate\u013eom a rozhodnutia riaden\u00e9 d\u00e1tami k\u013e\u00fa\u010dom k \u00faspechu?<\/strong><\/p>\n

Aby ste mohli naplno vyu\u017ei\u0165 potenci\u00e1l GA4, potrebujete v\u0161ak n\u00e1stroj na spr\u00e1vu, transform\u00e1ciu a anal\u00fdzu marketingov\u00fdch d\u00e1t, ktor\u00fd v\u00e1m to umo\u017en\u00ed.\u00a0 <\/span>Rie\u0161enie je jednoduch\u00e9<\/strong> – Google BigQuery, v\u00e1\u0161 univerz\u00e1lny n\u00e1stroj na pokro\u010dil\u00fa d\u00e1tov\u00fa analytiku (a mnoho \u010fal\u0161ieho).\u00a0<\/span><\/p>\n

Ak ste sa s Google BigQuery<\/a> e\u0161te nestretli, je to d\u00e1tov\u00fd sklad, ktor\u00fd umo\u017e\u0148uje efekt\u00edvne pracova\u0165 s ve\u013ek\u00fdm mno\u017estvom \u00fadajov v r\u00e1mci platformy Google Cloud.<\/strong><\/span><\/p>\n

V tomto \u010dl\u00e1nku sa pozrieme na mo\u017enosti vyu\u017eitia BigQuery pre digit\u00e1lnu analytiku<\/strong> – od surov\u00fdch d\u00e1t z GA4, cez ich transform\u00e1cie, a\u017e po obohacovanie d\u00e1t z r\u00f4znych zdrojov alebo odosielanie vlastn\u00fdch d\u00e1t cez server-side Google Tag Manager (GTM).<\/strong><\/span><\/p>\n

Raw d\u00e1ta z GA4<\/b><\/h2>\n

Nie v\u0161etky webov\u00e9 d\u00e1ta, ktor\u00e9 zozbierate pomocou Google Tag Managera s\u00fa v GA4 dostupn\u00e9. D\u00f4vodom je re\u017eimu s\u00fahlasu alias consent mode<\/strong><\/a> 👻. Ke\u010f pou\u017e\u00edvatelia neudelia s\u00fahlas so sledovan\u00edm, Google obmedz\u00ed pou\u017eitie a ukladanie ich \u00fadajov, \u010do vedie k ne\u00fapln\u00fdm d\u00e1tam v reportoch GA4.\u00a0<\/span><\/p>\n

Jednou z v\u00fdhod integr\u00e1cie BigQuery s GA4 je pr\u00edstup k surov\u00fdm, nevzorkovan\u00fdm d\u00e1tam. 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.\u00a0<\/span><\/p>\n

Zauj\u00edmaj\u00fa v\u00e1s \u010d\u00edsla?<\/strong> Urobili sme jednoduch\u00e9 porovnanie po\u010dtu zobrazen\u00ed str\u00e1nok u troch na\u0161ich klientov. V GA4 sme pou\u017eili \u201cExplorations\u201d report<\/strong>, v BigQuery export d\u00e1t z GA4.<\/strong> <\/span><\/p>\n

Ilustrat\u00edvne sme vybrali klientov z troch r\u00f4znych oblast\u00ed, s r\u00f4znym objemom d\u00e1t.\u00a0<\/span><\/p>\n

 <\/p>\n\n\n\n\n\n\n
<\/td>\nGA4<\/strong><\/td>\nBigQuery<\/strong><\/td>\nrozdiel v %<\/strong><\/td>\n<\/tr>\n
blog<\/strong><\/td>\n5 383<\/span><\/td>\n8 864<\/span><\/td>\n39.27<\/span><\/td>\n<\/tr>\n
slu\u017eby<\/strong><\/td>\n296 421<\/span><\/td>\n540 364<\/span><\/td>\n45.14<\/span><\/td>\n<\/tr>\n
ecommerce<\/strong><\/td>\n1 611 035<\/span><\/td>\n2 330 819<\/span><\/td>\n30.89<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
*Porovnanie po\u010dtu zobrazen\u00ed str\u00e1nok (page view) za m\u00e1j 2024.<\/span><\/h6>\n

Ako vid\u00edte, v BigQuery bolo v priemere o 38 percent viac d\u00e1t ako v GA4.<\/strong> Toto \u010d\u00edslo je individu\u00e1lne pre ka\u017ed\u00fa str\u00e1nku, ale be\u017ene sa stret\u00e1vame s hodnotami od 30 do 50 percent<\/strong>. Z\u00e1vis\u00ed to od konkr\u00e9tneho webu, segmentu, ale aj nastavenia cookie li\u0161ty.\u00a0<\/span><\/p>\n

V\u00fdrazn\u00fd rozdiel, \u010do poviete? 🤔<\/strong><\/p>\n

\u010eal\u0161\u00edm benefitom integr\u00e1cie je pr\u00edstup ku v\u0161etk\u00fdm dimenzi\u00e1m, ktor\u00e9 do GA4 posielate. Nielen t\u00fdm, ktor\u00e9 ste si zaregistrovali.\u00a0<\/span><\/p>\n

V GA4 toti\u017e m\u00f4\u017ee by\u0165 probl\u00e9m s kardinalitou.<\/strong> To znamen\u00e1, \u017ee ve\u013ek\u00fd po\u010det jedine\u010dn\u00fdch hodn\u00f4t pre ur\u010dit\u00e9 dimenzie, ako s\u00fa r\u00f4zne jedine\u010dn\u00e9 identifik\u00e1tory (napr. user id), m\u00f4\u017ee sp\u00f4sobi\u0165 nepresnosti v reportoch. Ke\u010f je kardinalita vysok\u00e1, GA4 m\u00f4\u017ee d\u00e1ta agregova\u0165, aby sa zmestili do reportov. V\u00fdsledkom je men\u0161ia detailnosti a presnos\u0165 reportov.\u00a0<\/strong><\/span><\/p>\n

Transform\u00e1cie d\u00e1t z GA4 Exportu<\/b>\u00a0<\/span><\/h2>\n

Ak pou\u017e\u00edvate na reporting v Looker Studiu raw d\u00e1ta z GA4 exportu, ur\u010dite ste sa stretli s dvoma, nazvime to nepr\u00edjemnos\u0165ami – pomal\u00e9 na\u010d\u00edtavanie<\/strong> a zvl\u00e1\u0161tne v\u00fdsledky pri sp\u00e1jan\u00ed<\/strong> (blendovan\u00ed) d\u00e1t<\/strong>. Niekedy dok\u00e1\u017eu poriadne potr\u00e1pi\u0165.\u00a0<\/span><\/p>\n

Raw d\u00e1ta z GA4 exportu je dobr\u00e9 pred pou\u017eit\u00edm e\u0161te trochu \u201cu\u010desa\u0165\u201d, aby boli pripraven\u00e9 na anal\u00fdzu a reportovanie.<\/span><\/p>\n

I ke\u010f nie ste d\u00e1tov\u00fd \u0161pecialisti a SQL experti, s pomocou internetu alebo AI si vytvor\u00edte z\u00e1kladn\u00e9 reportovacie tabu\u013eky vcelku jednoducho.<\/strong> \u010ci u\u017e ich chcete event scope, session, user, item alebo inak.\u00a0<\/span><\/p>\n

V\u00fdsledkom m\u00f4\u017ee by\u0165 napr\u00edklad tak\u00e1to custom reportovacia tabu\u013eka. 👇<\/span><\/p>\n

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

Nastav\u00edte si automatick\u00e9 aktualiz\u00e1cie, prepoj\u00edte s Looker Studiom a m\u00e1te hotovo.\u00a0<\/strong><\/p>\n

Dobre, mo\u017eno trochu preh\u00e1\u0148ame. Nie je to a\u017e tak\u00e9 jednoduch\u00e9. Ak by ste si nevedeli rady, m\u00f4\u017eete sa na n\u00e1s kedyko\u013evek obr\u00e1ti\u0165. 😅<\/span><\/p>\n

Niekedy sa t\u00fdchto transform\u00e1ci\u00ed nazbiera viac a za\u010d\u00edna by\u0165 zlo\u017eit\u00e9 ich mana\u017eova\u0165. Tu prich\u00e1dza na rad Google Dataform.<\/strong> Je to n\u00e1stroj, ktor\u00fd pom\u00e1ha spravova\u0165 a sp\u00fa\u0161\u0165a\u0165 transform\u00e1cie \u00fadajov ulo\u017een\u00fdch v BigQuery. Pr\u00e1ca s n\u00edm v\u0161ak vy\u017eaduje znalos\u0165 SQL a JavaScript, \u010do m\u00f4\u017ee predstavova\u0165 pre mnoh\u00fdch probl\u00e9m.\u00a0\u00a0<\/strong><\/span><\/p>\n

Obohacovanie d\u00e1t<\/b>\u00a0<\/span><\/h2>\n

Aby sme z\u00edskali skuto\u010dne komplexn\u00fd poh\u013ead na marketingov\u00fd v\u00fdkon, nesta\u010d\u00ed sa spolieha\u0165 len na d\u00e1ta z GA4. Je potrebn\u00e9 ich kombinova\u0165 s \u00fadajmi z Google Ads<\/strong>, Meta Ads,<\/strong> Google Search Console<\/strong> a \u010fal\u0161\u00edch zdrojov.\u00a0<\/span><\/p>\n

V BigQuery to nie je probl\u00e9m. Existuje ve\u013ek\u00e9 mno\u017estvo d\u00e1tov\u00fdch konektorov, ktor\u00fdmi prepoj\u00edte marketingov\u00e9 platformy. alebo v\u00e1\u0161 CRM syst\u00e9m s BigQuery. Niektor\u00e9 s\u00fa platen\u00e9, ale tie z\u00e1kladn\u00e9 s\u00fa zdarma.\u00a0<\/span><\/p>\n

Integr\u00e1cia r\u00f4znych d\u00e1tov\u00fdch zdrojov potom umo\u017e\u0148uje vykon\u00e1va\u0165 anal\u00fdzu naprie\u010d kan\u00e1lmi, \u010do je neocenite\u013en\u00e9 pri pochopen\u00ed, ako tieto marketingov\u00e9 kan\u00e1ly spolupracuj\u00fa. Napr\u00edklad,<\/strong> kombin\u00e1cia d\u00e1t z GA4 a Google Ads m\u00f4\u017ee uk\u00e1za\u0165, ako platen\u00e9 kampane ovplyv\u0148uj\u00fa n\u00e1v\u0161tevnos\u0165 a konverzie na va\u0161ej str\u00e1nke za ostatn\u00fdch 7 dn\u00ed. <\/span><\/p>\n

M\u00f4\u017eete na to sk\u00fasi\u0165 pou\u017ei\u0165 tak\u00fato sympatick\u00fa query:\u00a0<\/strong><\/span><\/p>\n

 <\/p>\n

\r\nSELECT\r\n  ga.event_date,\r\n  ga.user_pseudo_id,\r\n  (SELECT value.int_value FROM UNNEST(ga.event_params) WHERE key = 'ga_session_id') AS ga_session_id,\r\n  ga.ecommerce.transaction_id,\r\n  ga.collected_traffic_source.gclid,\r\n  ga.collected_traffic_source.manual_campaign_id,\r\n  ga.collected_traffic_source.manual_campaign_name,\r\n  ga.collected_traffic_source.manual_source,\r\n  ga.collected_traffic_source.manual_medium,\r\n  MAX(ads.ad_group_id) AS ad_group_id,\r\n  MAX(ads.campaign_id) AS campaign_id,\r\n  MAX(ads.segments_click_type) AS segments_click_type,\r\n  MAX(ads.segments_slot) AS segments_slot,\r\n  MAX(cmp.campaign_name) AS campaign_name\r\nFROM `project.dataset.table_*` AS ga\r\nJOIN\r\n `project.gads_data.p_ads_ClickStats_6464890505` AS ads\r\nON\r\n ga.collected_traffic_source.gclid = ads.click_view_gclid\r\n AND _PARTITIONTIME BETWEEN DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 8 DAY) AND DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)\r\nJOIN\r\n `project.gads_data.p_ads_Campaign_6464890505` AS cmp\r\nON\r\n ads.campaign_id = cmp.campaign_id\r\nWHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY)) AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))\r\nAND ga.event_name = 'purchase'\r\nGROUP BY ALL;\r\n<\/code><\/pre>\n
 <\/p>\n
Vlastn\u00e9 d\u00e1tov\u00e9 zdroje<\/b><\/h2>\n
S obohacovan\u00edm d\u00e1t sa sp\u00e1ja aj tvorba vlastn\u00fdch d\u00e1tov\u00fdch zdrojov. Okrem prepojenia syst\u00e9mov pomocou d\u00e1tov\u00fdh konektorov, m\u00f4\u017eete do BigQuery streamova\u0165 d\u00e1ta z backendu,<\/strong> alebo zo serverov\u00e9ho GTM.<\/strong> Hlavn\u00fdm d\u00f4vodom je, \u017ee tieto d\u00e1ta nie s\u00fa inak dostupn\u00e9 alebo chcete maximalizova\u0165 ich mno\u017estvo a kvalitu.\u00a0<\/span><\/p>\n
Ke\u010f\u017ee posielania d\u00e1t z backendu je be\u017en\u00e9, uvediem dva menej tradi\u010dn\u00e9 sp\u00f4soby streamovania \u00fadajov do BigQuery cez serverov\u00e9 GTM.<\/span><\/p>\n
    \n
  1.  kvantita d\u00e1t<\/strong> – ak chcete maximalizova\u0165 meranie ur\u010ditej udalosti, m\u00f4\u017eete ob\u00eds\u0165 klientsk\u00e9 GTM a trackovac\u00ed k\u00f3d vlo\u017ei\u0165 priamo do skriptu webovej str\u00e1nky. Vyhnete sa tak pr\u00edpadom, kedy sa GTM nespustilo alebo bolo zablokovan\u00e9.\u00a0<\/span><\/li>\n<\/ol>\n
    Funguje to zhruba takto:<\/span><\/p>\n
    \"ako<\/a><\/p>\n
    Udalos\u0165 po\u0161lete z webu do serverov\u00e9ho GTM, ktor\u00e9 ju spracuje a odo\u0161le do BigQuery. Z\u00edskan\u00e9 \u00fadaje m\u00f4\u017eete zo serverov\u00e9 GTM posla\u0165 aj do marketingov\u00fdch platforiem ako Google Ads alebo Meta Ads. Zd\u00f4raz\u0148ujem, \u017ee v\u00a0 ka\u017edom pr\u00edpade treba ma\u0165 na zreteli ak\u00e9 d\u00e1ta zbierate, a \u010di m\u00e1te s\u00fahlas na ich pou\u017eitie!\u00a0<\/span><\/p>\n
     <\/p>\n
      \n
    1.  kvalita d\u00e1t<\/strong> – pekn\u00fdm pr\u00edkladom je kontrola odosielan\u00fdch d\u00e1t cez Facebook Conversion API. Mo\u017eno si poviete, ve\u010f vo fejsb\u00faku vid\u00edm event match quality. \u00c1no, ale nie detailn\u00e9 inform\u00e1cie a d\u00e1ta, ktor\u00e9 neboli spracovan\u00e9 kv\u00f4li nespr\u00e1vnemu form\u00e1tu alebo chybe.\u00a0<\/span><\/li>\n<\/ol>\n
       <\/p>\n
      V DASE pou\u017e\u00edvame modifikovan\u00fd FB Conversion API tag<\/strong>, ktor\u00fd uklad\u00e1 do BigQuery v\u0161etky d\u00e1ta odosielan\u00e9 do Facebooku aj s inform\u00e1ciou, \u010di boli spracovan\u00e9 alebo nie. Po prepojen\u00ed s Looker Studiom je v\u00fdsledkom Data Quality Report,<\/strong> ktor\u00fd pom\u00e1ha identifikova\u0165 priestor na zlep\u0161enie kvality d\u00e1t a r\u00fdchlej\u0161ie odhal\u00ed pr\u00edpadn\u00e9 probl\u00e9my.\u00a0<\/span><\/p>\n
      \"bigquery<\/p>\n
      Z\u00e1ver<\/b><\/h2>\n
      A to nie je zda\u013eeka v\u0161etko, \u010do m\u00f4\u017eete s pomocou BigQuery robi\u0165.<\/strong> Ak si chcete sk\u00fasi\u0165 \u00falohu d\u00e1tov\u00fdch vedcov a priatel\u00edte sa s Pythonom,<\/strong> BigQuery m\u00e1 priamu integr\u00e1ciu s Jupyter<\/strong> Notebook.<\/strong><\/span><\/p>\n
      Otv\u00e1raj\u00fa sa v\u00e1m tak mo\u017enosti explorat\u00edvnej anal\u00fdzy, pokro\u010dilej analytiky alebo aplik\u00e1ci\u00ed strojov\u00e9ho u\u010denia. Kv\u00f4li posledne spomenut\u00e9mu nemus\u00edte dokonca pou\u017ei\u0165 ani Jupyter Notebook. Niektor\u00e9 modely strojov\u00e9ho u\u010denia s\u00fa toti\u017e dostupn\u00e9 priamo v BigQuery prostredn\u00edctvom SQL. Ale to je u\u017e in\u00fd pr\u00edbeh.\u00a0<\/span><\/p>\n
      Ako sa vrav\u00ed, ni\u010d nie je zadarmo. Ani BigQuery.<\/strong> Bude v\u00e1s st\u00e1\u0165 minim\u00e1lne \u010das a \u00fasilie, ktor\u00e9 investujete. Odmenou v\u00e1m bude viac d\u00e1t, na z\u00e1klade ktor\u00fdch m\u00f4\u017eete robi\u0165 informovanej\u0161ie rozhodnutia.\u00a0<\/span><\/p>\n
      PS: o platb\u00e1ch za BigQuery sa viac do\u010d\u00edtate v \u010dl\u00e1nku Ko\u013eko stoj\u00ed Google Cloud.<\/a><\/span><\/p>\n
       <\/p>\n","protected":false},"excerpt":{"rendered":"
      Pohybujete sa v divokej d\u017eungli digit\u00e1lneho marketingu, kde s\u00fa d\u00e1ta z Google Analytics 4 (GA4) va\u0161\u00edm najlep\u0161\u00edm priate\u013eom…<\/p>\n","protected":false},"author":78,"featured_media":20169,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[640,639],"tags":[672,673,923,799,663,889,996],"_links":{"self":[{"href":"https:\/\/www.dase-analytics.com\/blog\/sk\/wp-json\/wp\/v2\/posts\/20156"}],"collection":[{"href":"https:\/\/www.dase-analytics.com\/blog\/sk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dase-analytics.com\/blog\/sk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dase-analytics.com\/blog\/sk\/wp-json\/wp\/v2\/users\/78"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dase-analytics.com\/blog\/sk\/wp-json\/wp\/v2\/comments?post=20156"}],"version-history":[{"count":6,"href":"https:\/\/www.dase-analytics.com\/blog\/sk\/wp-json\/wp\/v2\/posts\/20156\/revisions"}],"predecessor-version":[{"id":20167,"href":"https:\/\/www.dase-analytics.com\/blog\/sk\/wp-json\/wp\/v2\/posts\/20156\/revisions\/20167"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dase-analytics.com\/blog\/sk\/wp-json\/wp\/v2\/media\/20169"}],"wp:attachment":[{"href":"https:\/\/www.dase-analytics.com\/blog\/sk\/wp-json\/wp\/v2\/media?parent=20156"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dase-analytics.com\/blog\/sk\/wp-json\/wp\/v2\/categories?post=20156"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dase-analytics.com\/blog\/sk\/wp-json\/wp\/v2\/tags?post=20156"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}