GA4-datan mallinnus BigQueryssa dbt:n avulla

Mitä dbt tekee GA4-datalle

GA4:n BigQuery-vienti tuo havaittua tapahtumadataa sisäkkäisessä rakenteessa. Se ei sisällä kaikkea käyttöliittymän rikastettua tai mallinnettua tietoa. Raakadataa voi kysellä suoraan, mutta yhteinen mallinnuskerros vähentää toistuvaa purkutyötä ja mittarien määrittelyeroja.

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.

Kun istunnon tai liidin määritelmä muuttuu, versionhallinnasta voidaan tarkistaa muutos ja sen vaikutukset. dbt tarjoaa tähän valmiita rakenteita; vastaavat ylläpitoperiaatteet ovat hyödyllisiä myös muilla välineillä toteutetuissa SQL-projekteissa.

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

Päivittäinen vienti luo events_YYYYMMDD-taulut. events_intraday_YYYYMMDD syntyy vain, jos streaming-vienti on käytössä. 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ä.

Yksittäisiä kenttiä voi valita suoraan, mutta toistuvan parametrirakenteen purkamiseen tarvitaan esimerkiksi UNNEST. Seuraava esimerkki lukee yhden parametrin:

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 sama purku toistuu useissa kyselyissä, yhteinen malli vähentää toistoa ja helpottaa muutosten tekemistä.

Kerrosmalli: staging, välimallit, raportointitaulut

Seuraava kerrosjako erottaa lähteiden käsittelyn liiketoiminnan laskennasta ja raportoinnin tarpeista. Mallien määrä ja rajat sovitetaan projektin laajuuteen.

• Staging: lähteen kenttien purku, nimeäminen ja tietotyyppien yhdenmukaistaminen. Mahdolliset suodatukset dokumentoidaan.
• Välimallit: esimerkiksi tapahtumien kokoaminen istunnoiksi, kanavaryhmittely ja lähteiden yhdistäminen.
• Raportointitaulut eli martit: käyttötarpeeseen sopivalla tarkkuustasolla olevat tiedot, joita raportit voivat hyödyntää yhdessä.

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 %}

{% set partitions_to_replace = [
    "date_sub(current_date('Europe/Helsinki'), interval 1 day)",
    "date_sub(current_date('Europe/Helsinki'), interval 2 day)",
    "date_sub(current_date('Europe/Helsinki'), interval 3 day)"
] %}

{{ config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',
    partitions=partitions_to_replace,
    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,
  collected_traffic_source.manual_source       as source,
  collected_traffic_source.manual_medium       as medium,
  collected_traffic_source.manual_campaign_name 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') }}
-- source-taulun identifier on events_*; sulje intraday pois myös full-refreshissä.
where regexp_contains(_table_suffix, r'^[0-9]{8}$')

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

Lähteen _TABLE_SUFFIX-rajaus vähentää luettavia päivätauluja. Kohteen partitiointi auttaa myöhempiä raporttikyselyitä, mutta ei yksin rajaa lähdevientiä. insert_overwrite korvaa kolme erikseen listattua päivää myös silloin, kun jonkin päivän tulos on tyhjä. Varmista ensin kaikkien korvattavien lähdepäivien saatavuus ja vaihda Europe/Helsinki omaisuuden aikavyöhykkeeksi.

Google täydentää päivätauluja normaalisti enintään kolmen päivän ajan tapahtumapäivän jälkeen. Poikkeuksellinen uudelleenkäsittely voi muuttaa vanhempaakin dataa, joten mahdollista myös erilliset historian korjausajot.

Tapahtumista istunnoiksi

BigQueryn tapahtumadatasta istunnot rakennetaan valitun laskentasäännön mukaan. Kirjatkaa myös, miten rajatapaukset ja puuttuvat tunnisteet käsitellään, jotta raportin käyttäjät ymmärtävät mahdolliset erot GA4:n käyttöliittymään.

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,
      sum(coalesce(engagement_time_msec, 0)) / 1000   as engaged_seconds,
      -- Yksinkertaistettu lähde: ensimmäinen kerätty arvo, ei valmis GA4-attribuutio
      array_agg(if(source is not null or medium is not null,
                   struct(source as source, medium as medium), null)
                ignore nulls order by event_at limit 1)[safe_offset(0)] as acquisition,
      array_agg(if(event_name = 'page_view', page_location, null)
                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 and user_pseudo_id is not null
    group by session_key, user_pseudo_id
)

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

Esimerkin lähde ja medium ovat ensimmäisiä kerättyjä arvoja, eivät GA4:n valmis istuntoattribuutio. Tarkkaan vertailuun käytä session_traffic_source_last_click-rakennetta sen saatavuuden rajoissa. channel_group on oma, erikseen toteutettava makro. Tunnisteettomat tapahtumat jäävät tämän istuntomallin ulkopuolelle; seuraa niiden määrää erikseen.

Datan yhdistäminen muista lähteistä

Tietovarastossa verkkosivuston tapahtumia voidaan verrata tilausten lopulliseen tilaan, katteeseen ja asiakkuuden kehitykseen. Nämä tiedot tulevat tyypillisesti verkkokaupasta, taloushallinnosta tai CRM:stä.

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

• Google Adsin, GA4:n ja CRM:n yhdistäminen raportointiin

• transaction_id: tilaustunnus toimii liitosavaimena, jos sen arvo ja muoto vastaavat toisiaan molemmissa lähteissä. Tarkista myös tunnuksen yksilöllisyys ja peruutusten käsittely.
• user_id: suunniteltu sisäinen tunniste voi yhdistää kirjautuneen käyttäjän tapahtumia muihin tietoihin. GA4:ään ei lähetetä sähköpostia, nimeä tai muuta suoraan tunnistavaa henkilötietoa.
• Päivä ja kampanja: mainoskulujen liittämiseen. Kustannusdata tulee omana lähteenään ja liitetään päivä- ja kampanjatasolla, ei tapahtumatasolla.

with order_sessions as (
  -- Yksi istuntoliitos per tilaus, myös usean ostoksen istunnoissa.
  select
    transaction_id,
    concat(user_pseudo_id, '-', cast(ga_session_id as string)) as session_key
  from {{ ref('stg_ga4__events') }}
  where event_name = 'purchase'
    and transaction_id is not null and transaction_id != ''
    and user_pseudo_id is not null and ga_session_id is not null
  qualify row_number() over (
    partition by transaction_id order by event_at, session_key
  ) = 1
)
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 order_sessions os on o.order_id = os.transaction_id
left join {{ ref('fct_sessions') }} s on os.session_key = s.session_key

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

Full outer join säilyttää sekä kulupäivät ilman istuntoja että liikenteen ilman kuluja. Tässä esimerkissä mainoskululähde on rajattu Google-haun mainoksiin; älä luokittele kaikkia Google Ads -kampanjoita google/cpc-arvoilla Paid Search -kanavaan. Shopping-, Display-, Video- ja Performance Max -kampanjat tarvitsevat oman luokittelunsa.

Aggregointi Data Studiota varten

Valmiiksi lasketut raportointitaulut vähentävät raskaiden lähdekyselyiden tarvetta. Jokainen avaus ei kuitenkaan lue raakadataa uudelleen: Data Studio ja BigQuery voivat käyttää välimuistia, kun kysely ja sen käyttöehdot sen sallivat.

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,
  sum(engaged_seconds)                as engaged_seconds
from {{ ref('fct_sessions') }}
group by 1, 2, 3, 4

Tarkkuustaso määrittää, mihin taulu vastaa. Sitoutumisajan keskiarvo lasketaan raportilla SUM(engaged_seconds) / SUM(sessions): ryhmäkeskiarvojen tavallinen keskiarvo painottaisi pientä ja suurta ryhmää yhtä paljon.

Yksilöllisten käyttäjien määriä ei voi summata kanavien tai päivien yli, koska sama käyttäjä voi esiintyä useassa ryhmässä. Jos raportointitaulussa on vain ryhmäkohtaiset count(distinct)-luvut, niistä ei voi palauttaa tarkkaa kokonaiskäyttäjämäärää. Laske kokonaisluku tarvitulle tarkkuustasolle erikseen ja estä virheellinen summaus raportilla.

Missä dbt ajetaan

dbt Core on avoimen lähdekoodin komentorivityökalu. Ajot voi järjestää omassa ympäristössä tai hyödyntää hallittuja palveluita. Valintaan vaikuttavat ajastuksen lisäksi kehittäjien yhteistyö, käyttöoikeudet, dokumentaatio ja ylläpito.

Google Cloud -ympäristössä seuraavat vaihtoehdot sopivat erilaisiin tarpeisiin:

• Cloud Run Jobs ja Cloud Scheduler: konttiin pakattu dbt-projekti suoritetaan ajastettuna. Kustannus riippuu ajon kestosta, resursseista, tiheydestä ja BigQuery-kyselyistä.
• Orkestraattori, kuten Dagster tai Cloud Composer: hyödyllinen, kun dbt-ajo liittyy useisiin lähteisiin ja muuhun ajoketjuun. Arvioi myös palvelun ja ylläpidon kustannus.
• GitHub Actions: luonteva paikka muutosten tarkistuksille ja testeille. Tuotannon ajastuksessa on arvioitava ajankohtien tarkkuus, suoritusrajat ja häiriöiden valvonta.

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

Cloud Runissa ajolle liitetään rajattu palvelutili. Muista ympäristöistä voidaan käyttää esimerkiksi tuettua identiteettifederaatiota. Näin tunnistautuminen voidaan toteuttaa ilman versionhallintaan tai konttiin tallennettua yksityistä avainta.

Milloin ajo kannattaa käynnistää

GA4:n päivittäiselle viennille ei ole taattua kellonaikaa. Googlen mukaan vienti valmistuu tyypillisesti iltapäivällä omaisuuden aikavyöhykkeellä ja voi viivästyä seuraavaan päivään. Kiinteä aamuyön ajo tarvitsee lähteen saatavuustarkistuksen ja uudelleenyritykset.

Puuttuva lähdepäivä voi tuottaa teknisesti onnistuneen mutta puutteellisen mallin. Saatavuustarkistuksen on siksi pysäytettävä julkaisu ennen kuin keskeneräinen aineisto korvaa raportoinnin käytössä olevan version.

Sopikaa lähteen odottamisesta, uudelleenyrityksistä ja hälytyksistä esimerkiksi näin:

• 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.
• Suorita tarvittaessa dbt source freshness erikseen. Tuoreustarkistus täydentää taulun olemassaolon ja sisällön tarkistuksia, eikä yksin todista aineiston täydellisyyttä.
• Mitoita ajotiheys raportoinnin tarpeeseen. Käytä myöhästyvien tapahtumien uudelleenlaskentaikkunaa ja mahdollista myös sitä vanhemman historian korjaus.
• 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 ja tarkistettu raportoinnin julkaisu

dbt:n testit tarkistavat mallinnuksen oletuksia. dbt run ei aja testejä: käytä dbt build -komentoa mallien ja testien ajamiseen riippuvuusjärjestyksessä tai aja dbt test erikseen. source freshness on oma komentonsa. Testit eivät palauta jo kirjoitettua taulua vanhaan tilaan; julkaise raportointikerros vasta hyväksytyn tarkistuksen jälkeen.

• dbt build: mallien ja testien suoritusjärjestys
• BigQuery- ja dbt-asiantuntija datamallien kehitykseen

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

Lisätkää tarkistus, joka vertaa mallinnuksen ja raakadatan tapahtumamääriä sovituilla rajauksilla. Se voi paljastaa purussa tai liitoksissa kadonneita ja monistuneita rivejä. Testatkaa lisäksi liiketoiminnan laskentasäännöt tunnetuilla esimerkeillä.

Yleisimmät virheet

• Lähteen purku ja useiden raporttien laskentalogiikka kietoutuvat yhteen ilman selkeitä mallirajoja.
• Koko historia lasketaan toistuvasti, vaikka rajattu päivitys riittäisi.
• Myöhästyviä tapahtumia tai lähteen puuttuvia päiviä ei huomioida.
• Raportti suorittaa käyttötarpeeseen nähden liian raskaita lähdekyselyitä.
• Istuntoavain käyttää vain ga_session_id:tä ilman käyttäjätunnistetta.
• Ryhmäkohtaiset yksilöllisten käyttäjien määrät summataan kokonaisluvuksi.
• Testit eivät kata liitosten monistumista tai liiketoiminnan laskentasääntöjä.