DBT ja Google Analytics

Mitä DBT tekee GA4-datalle

GA4:n BigQuery-vienti tuo tapahtumadatan sellaisenaan: yksi rivi per tapahtuma, parametrit sisäkkäisinä rakenteina. Se on täydellinen arkisto ja kelvoton raportointilähde. Jokainen kysely joutuu purkamaan rakenteen uudelleen, ja jokainen purkaja tekee sen hieman eri tavalla — mikä on suora tie siihen, että kaksi raporttia näyttää eri luvut.

DBT ratkaisee tämän tekemällä purkamisesta yhden, versionhallitun määritelmän. Kirjoitat SQL:ää, DBT hoitaa riippuvuudet, materialisoinnin, testit ja dokumentaation. Lopputulos on joukko tauluja, joissa sarakkeet ovat ihmisen luettavia ja mittarit on laskettu kerran.

Käytännön ero näkyy siinä hetkessä, kun joku kysyy miten istunto on määritelty. DBT-projektissa vastaus on tiedosto. Ilman sitä vastaus on se, kuka ehti kirjoittaa kyselyn.

Lähtötilanne: miltä GA4:n raakadata näyttää

Vienti luo päiväkohtaiset taulut events_YYYYMMDD sekä kuluvan päivän events_intraday_YYYYMMDD. Tapahtuman parametrit eivät ole sarakkeita vaan toistuva rakenne, jossa jokaisella parametrilla on avain ja tyypitetty arvo. Sama koskee käyttäjäominaisuuksia ja verkkokaupan tuoterivejä.

Tämä tarkoittaa, ettei tuttu SELECT riitä. Yhden parametrin lukeminen vaatii alikyselyn UNNEST-purun yli:

select
  event_date,
  event_name,
  user_pseudo_id,
  (select value.string_value from unnest(event_params)
     where key = 'page_location') as page_location,
  (select value.int_value from unnest(event_params)
     where key = 'ga_session_id') as ga_session_id
from `projekti.analytics_123456789.events_*`
where _table_suffix = format_date('%Y%m%d', current_date() - 1)

Kun tarvittavia parametreja on kymmenen ja tauluja käyttää viisi eri raporttia, tämä lakkaa olemasta ylläpidettävää. Siitä alkaa mallinnuksen tarve.

Kerrosmalli: staging, välimallit, raportointitaulut

Toimiva GA4-projekti jakautuu kolmeen kerrokseen, ja jokaisella on yksi tehtävä. Kerrosten sekoittaminen on yleisin syy siihen, että projektista tulee vuodessa yhtä sotkuinen kuin käsin kirjoitetuista kyselyistä.

• Staging: raakadata puretaan litteäksi ja nimetään uudelleen. Ei liiketoimintalogiikkaa, ei suodatuksia — vain rakenne kuntoon. Yksi staging-malli per lähde.
• Välimallit: tapahtumat kootaan istunnoiksi ja käyttäjiksi, kanavaryhmittely lasketaan, muut lähteet liitetään mukaan. Täällä asuu liiketoimintalogiikka.
• Raportointitaulut (martit): valmiiksi aggregoidut taulut, joita Data Studio lukee. Yksi taulu per raportointitarve, ei yhtä yleistaulua kaikkeen.

GA4-tapahtumien purkaminen omiin tauluihin

Ensimmäinen askel on makro, joka poistaa parametrien lukemisen toiston. Se kirjoitetaan kerran ja käytetään kaikkialla:

{% macro ga4_param(key, value_type='string') %}
  (select value.{{ value_type }}_value
     from unnest(event_params)
    where key = '{{ key }}')
{% endmacro %}

{{ config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',
    partition_by={'field': 'event_date', 'data_type': 'date'},
    cluster_by=['event_name', 'user_pseudo_id']
) }}

select
  parse_date('%Y%m%d', event_date)              as event_date,
  timestamp_micros(event_timestamp)             as event_at,
  event_name,
  user_pseudo_id,
  user_id,
  {{ ga4_param('ga_session_id', 'int') }}       as ga_session_id,
  {{ ga4_param('page_location') }}              as page_location,
  {{ ga4_param('page_title') }}                 as page_title,
  {{ ga4_param('source') }}                     as source,
  {{ ga4_param('medium') }}                     as medium,
  {{ ga4_param('campaign') }}                   as campaign,
  {{ ga4_param('engagement_time_msec', 'int') }} as engagement_time_msec,
  ecommerce.transaction_id,
  ecommerce.purchase_revenue,
  device.category                               as device_category,
  geo.country
from {{ source('ga4', 'events') }}

{% if is_incremental() %}
  -- Kolmen vuorokauden ikkuna: GA4 täydentää ja korjaa vientiä
  -- jälkikäteen, ja insert_overwrite korvaa nämä partitiot kokonaan.
  where _table_suffix between
        format_date('%Y%m%d', current_date() - 3)
    and format_date('%Y%m%d', current_date())
{% endif %}

Kaksi asiaa tässä ratkaisee kustannukset ja luotettavuuden. Partitiointi event_date-kentän mukaan tarkoittaa, ettei kysely lue koko historiaa. insert_overwrite yhdessä kolmen vuorokauden ikkunan kanssa taas tarkoittaa, että myöhässä saapuvat ja korjatut tapahtumat päivittyvät automaattisesti — GA4:n vienti ei ole lopullinen sinä hetkenä kun se ilmestyy.

Ikkunan pituus on käytännön valinta, ei Googlen lupaus. Kolme vuorokautta riittää useimmille; jos data on kriittistä, tarkista oman propertyn käytös vertaamalla viikon takaisia lukuja uudelleenlaskennan jälkeen.

Tapahtumista istunnoiksi

GA4:n käyttöliittymä laskee istunnot puolestasi. BigQueryssa ne pitää rakentaa itse, mikä on samalla koko harjoituksen hyöty: määritelmä on sinun eikä työkalun.

Istunnon avain muodostetaan käyttäjätunnisteen ja istuntotunnuksen yhdistelmästä. Kumpikaan ei yksin riitä, koska ga_session_id on uniikki vain käyttäjän sisällä.

with events as (
    select * from {{ ref('stg_ga4__events') }}
),

sessions as (
    select
      concat(user_pseudo_id, '-', cast(ga_session_id as string)) as session_key,
      user_pseudo_id,
      max(user_id)                                    as user_id,
      min(event_date)                                 as session_date,
      min(event_at)                                   as session_start,
      max(event_at)                                   as session_end,
      countif(event_name = 'page_view')               as page_views,
      countif(event_name = 'purchase')                as purchases,
      sum(coalesce(purchase_revenue, 0))              as revenue,
      max(transaction_id)                             as transaction_id,
      sum(coalesce(engagement_time_msec, 0)) / 1000   as engaged_seconds,
      -- Istunnon lähde: ensimmäinen ei-tyhjä arvo aikajärjestyksessä
      array_agg(source ignore nulls order by event_at limit 1)[safe_offset(0)] as source,
      array_agg(medium ignore nulls order by event_at limit 1)[safe_offset(0)] as medium,
      array_agg(page_location ignore nulls order by event_at limit 1)[safe_offset(0)]
        as landing_page,
      max(device_category)                            as device_category,
      max(country)                                    as country
    from events
    where ga_session_id is not null
    group by session_key, user_pseudo_id
)

select
  *,
  {{ channel_group('source', 'medium') }} as channel_group,
  purchases > 0                           as converted
from sessions

Kanavaryhmittely kannattaa eristää omaksi makrokseen. Se on tyypillisin kohta, jossa määritelmä elää — ja kun se on yhdessä tiedostossa, muutos vaikuttaa kaikkiin raportteihin kerralla eikä yhteen kerrallaan.

Datan yhdistäminen muista lähteistä

Tässä on se kohta, jossa oma datavarasto maksaa itsensä takaisin. GA4 tietää mitä sivustolla tapahtui, muttei mitä kaupasta seurasi: peruuntuiko tilaus, mikä oli kate, palasiko asiakas kolmen kuukauden päästä. Nämä ovat muissa järjestelmissä.

Liitoskohtia on käytännössä kolme, ja ne kannattaa tunnistaa ennen kuin mallinnusta aloittaa.

• transaction_id — verkkokaupan tilaustunnus. Luotettavin liitos, koska se on sama arvo molemmissa järjestelmissä. Edellyttää että ostotapahtuma on mitattu oikein.
• user_id — jos kirjautuneille käyttäjille lähetetään sama tunniste GA4:ään kuin mitä CRM käyttää. Tämä pitää suunnitella mittaussuunnitelmassa; jälkikäteen sitä ei saa.
• Päivä ja kampanja — mainoskulujen liittämiseen. Kustannusdata tulee omana lähteenään ja liitetään päivä- ja kampanjatasolla, ei tapahtumatasolla.

select
  o.order_id,
  o.ordered_at,
  o.net_revenue,
  o.margin,
  o.is_cancelled,
  s.session_key,
  s.channel_group,
  s.landing_page,
  s.device_category
from {{ ref('stg_crm__orders') }} o
left join {{ ref('fct_sessions') }} s
  on o.order_id = s.transaction_id

with sessions as (
    select
      session_date  as date,
      channel_group,
      count(*)                          as sessions,
      countif(converted)                as conversions
    from {{ ref('fct_sessions') }}
    group by 1, 2
),

cost as (
    select
      date,
      {{ channel_group("'google'", "'cpc'") }} as channel_group,
      sum(cost)                                as ad_cost
    from {{ ref('stg_google_ads__campaign_stats') }}
    group by 1, 2
)

select
  coalesce(s.date, c.date)                   as date,
  coalesce(s.channel_group, c.channel_group) as channel_group,
  coalesce(s.sessions, 0)                    as sessions,
  coalesce(s.conversions, 0)                 as conversions,
  coalesce(c.ad_cost, 0)                     as ad_cost,
  safe_divide(c.ad_cost, s.conversions)      as cost_per_conversion
from sessions s
full outer join cost c
  on s.date = c.date and s.channel_group = c.channel_group

Huomaa full outer join. Kulua voi syntyä päivänä, jolta ei tule istuntoja, ja istuntoja tulee kanavista joilla ei ole kulua. Inner join hukkaisi molemmat hiljaisesti — ja hiljainen datan katoaminen on pahin vikatila, koska raportti näyttää edelleen uskottavalta.

Aggregointi Data Studiota varten

Tämä on se vaihe, joka ratkaisee raportin nopeuden. Jos Data Studio kytketään tapahtumatauluun ja aggregointi jätetään raportin tehtäväksi, jokainen sivunlataus ajaa raskaan kyselyn miljoonien rivien yli. Raportti on hidas, ja BigQuery-lasku kasvaa jokaisesta avauskerrasta.

Ratkaisu on laskea aggregaatit valmiiksi DBT:ssä. Raportille jää vain esitystehtävä, ja kysely osuu tauluun jossa on tuhansia rivejä miljoonien sijaan.

{{ config(
    materialized='table',
    partition_by={'field': 'date', 'data_type': 'date'},
    cluster_by=['channel_group']
) }}

select
  session_date                        as date,
  channel_group,
  device_category,
  country,
  count(*)                            as sessions,
  count(distinct user_pseudo_id)      as users,
  sum(page_views)                     as page_views,
  countif(converted)                  as conversions,
  sum(revenue)                        as revenue,
  avg(engaged_seconds)                as avg_engaged_seconds
from {{ ref('fct_sessions') }}
group by 1, 2, 3, 4

Tarkkuustason valinta on koko aggregoinnin ydin. Mitä useampi ulottuvuus, sitä enemmän rivejä ja sitä hitaampi raportti — mutta sitä useampaan kysymykseen taulu vastaa. Nyrkkisääntö: ota mukaan ne ulottuvuudet, joilla raportissa oikeasti suodatetaan, ja jätä loput pois. Uuden aggregaattitaulun lisääminen myöhemmin on halvempaa kuin yhden hitaan yleistaulun ylläpito.

Yksi sudenkuoppa kannattaa tietää etukäteen: count(distinct) ei summaudu. Jos taulussa on käyttäjämäärä per kanava, Data Studio ei osaa laskea niistä oikeaa kokonaiskäyttäjämäärää — se laskee summan, joka on liian suuri, koska sama käyttäjä esiintyy useassa kanavassa. Jos kokonaisluku tarvitaan, se lasketaan omaan tauluunsa oikealla tarkkuustasolla.

Missä DBT ajetaan

DBT on kahdessa muodossa. dbt Core on avoimen lähdekoodin komentorivityökalu, jonka ajat itse. dbt Cloud on maksullinen palvelu, joka tarjoaa ajastuksen, käyttöliittymän ja dokumentaation valmiina.

Google Cloudissa dbt Coren ajamiseen on kolme järkevää tapaa, ja valinta riippuu lähinnä siitä, mitä muuta ympäristössä on:

• Cloud Run -job ja Cloud Scheduler — kevein vaihtoehto ja useimmille pk-yrityksille riittävä. DBT-projekti pakataan konttiin, Scheduler käynnistää sen. Maksat vain ajon ajalta, tyypillisesti senttejä kuukaudessa.
• Cloud Composer (Airflow) — perusteltu silloin, kun DBT on osa isompaa putkea ja ajojen välillä on riippuvuuksia. Tuo mukanaan oman ylläpitokuormansa ja kiinteän kuukausikustannuksen, joten pelkkään DBT:n ajastamiseen se on ylimitoitettu.
• GitHub Actions — toimii, ja on luonteva jos CI on jo siellä. Ajastettu workflow on kuitenkin epätarkka ja pitkät ajot syövät minuutteja, joten se sopii paremmin testeihin ja pull requestien tarkistuksiin kuin tuotantoajoihin.

gcloud run jobs create dbt-ga4 \
  --image europe-north1-docker.pkg.dev/PROJEKTI/dbt/dbt-bigquery:1.8 \
  --region europe-north1 \
  --service-account dbt-runner@PROJEKTI.iam.gserviceaccount.com \
  --max-retries 2 \
  --task-timeout 30m \
  --command dbt \
  --args build,--target,prod

gcloud scheduler jobs create http dbt-ga4-daily \
  --location europe-north1 \
  --schedule "0 6 * * *" \
  --time-zone "Europe/Helsinki" \
  --uri "https://europe-north1-run.googleapis.com/apis/run.googleapis.com/v1/namespaces/PROJEKTI/jobs/dbt-ga4:run" \
  --oauth-service-account-email dbt-runner@PROJEKTI.iam.gserviceaccount.com

Käyttöoikeuksista yksi huomio: käytä palvelutiliä ja Workload Identityä, älä ladattua avaintiedostoa. Avaintiedosto päätyy ennemmin tai myöhemmin versionhallintaan tai jonkun läppärille, ja BigQueryyn pääsevä avain on huono paikka oppia tämä.

Milloin ajo kannattaa käynnistää

Tässä on kohta, joka menee useimmiten pieleen. GA4:n päivittäinen vienti valmistuu yleensä muutamassa tunnissa vuorokauden vaihtumisen jälkeen propertyn aikavyöhykkeellä, mutta Google ei takaa kelloaikaa. Kiinteään aikaan sidottu ajo onnistuu suurimman osan päivistä ja epäonnistuu hiljaa niinä päivinä, joina vienti myöhästyy.

Hiljaa, koska mitään ei kaadu: malli ajetaan, edellisen päivän taulua ei ole, ja tulos on päivä jolta puuttuu data. Virhe huomataan siinä vaiheessa kun joku katsoo raporttia — yleensä kokouksessa.

Korjaus on tehdä ajosta ehdollinen sen sijaan että se olisi ajastettu sokeasti:

• Tarkista ennen ajoa, että edellisen päivän events_-taulu on olemassa. Jos ei ole, älä aja vaan yritä myöhemmin uudelleen.
• Aseta uudelleenyritys riittävän monta kertaa muutaman tunnin välein sen sijaan, että ajaisit kerran ja toivoisit parasta.
• Käytä dbt:n source freshness -tarkistusta osana ajoa, jotta vanhentunut lähde tuottaa selkeän virheen eikä hiljaisen puuttuvan päivän.
• Aja mieluummin kerran vuorokaudessa oikein kuin neljä kertaa vuorokaudessa varmuuden vuoksi. Kolmen vuorokauden uudelleenlaskentaikkuna hoitaa myöhässä saapuvan datan joka tapauksessa.
• Jos raportointi tarvitsee kuluvan päivän luvut, käsittele events_intraday erillisenä mallina — älä sekoita sitä valmiiseen päivädataan, koska se korvautuu myöhemmin lopullisella versiolla.

Testit: se osa, joka erottaa putken viritelmästä

DBT:n testit ovat syy, miksi mallinnettuun dataan voi luottaa enemmän kuin käsin kirjoitettuun kyselyyn. Ne ajetaan jokaisella ajolla, ja rikkoutunut oletus näkyy ajossa eikä raportissa.

models:
  - name: fct_sessions
    columns:
      - name: session_key
        tests: [unique, not_null]
      - name: session_date
        tests: [not_null]
      - name: channel_group
        tests:
          - accepted_values:
              values: ['Direct', 'Organic Search', 'Paid Search',
                       'Social', 'Referral', 'Email', 'Other']
      - name: revenue
        tests:
          - dbt_utils.accepted_range:
              min_value: 0

Näiden lisäksi kannattaa kirjoittaa yksi oma testi, joka vertaa mallinnettua tapahtumamäärää raakadatan määrään päivätasolla. Se on ainoa testi, joka huomaa jos purkulogiikka pudottaa rivejä hiljaisesti — ja juuri se virhe on vaikein löytää jälkikäteen.

Yleisimmät virheet

• Kaikki yhdessä mallissa: raakadatan purku, liiketoimintalogiikka ja aggregointi samassa tiedostossa. Toimii kuukauden, sen jälkeen kukaan ei uskalla koskea siihen.
• Ei inkrementaalisuutta: koko historia lasketaan joka yö uudelleen. Toimii kunnes datamäärä kasvaa, ja sitten lasku kasvaa sen mukana.
• Ei uudelleenlaskentaikkunaa: myöhässä saapuva data ei koskaan päädy tauluihin, ja luvut jäävät pysyvästi liian pieniksi.
• Data Studio kytkettynä tapahtumatauluun aggregaattitaulun sijaan. Hidas raportti ja turha lasku joka avauskerralta.
• Istunnon avaimena pelkkä ga_session_id ilman käyttäjätunnistetta. Eri käyttäjien istunnot sulautuvat yhteen, ja luvut ovat hiljaisesti väärin.
• count(distinct) aggregaattitaulussa, jota summataan raportissa. Käyttäjämäärä näyttää suuremmalta kuin se on.
• Ei testejä. Putki toimii täsmälleen siihen asti, kunnes lähdejärjestelmä muuttuu ilman ilmoitusta.