Mikä on BigQuery?
BigQuery on Googlen datavarastointipalvelu, jota hyödyntämällä voidaan pyöritellä isoja datamassoja, joko palvelun käyttöliittymässä, tai yhdistämällä palvelu johonkin ulkopuoliseen järjestelmään.
Ilmainen pääsy GA dataan BigQueryssä voimaannuttaa GA:n käyttäjät miettimään verkkoliikenneseurantansa uudelleen. Mitä jos Googlen tarjoamat yleiset mallinnukset eivät palvelekaan liiketoimintaamme? Mitä jos käyttäytyminen sivustollamme vaatiikin datan uudelleenmääritystä? GA4:n myötä, jopa pienemmänkin skaalan käyttäjät pystyvät itse vastaamaan näiden asioiden toteutuksesta.
BigQueryssä on monta hienoa ominaisuutta, joilla voidaan melko saumattomasti rikastaa dataa. Tämän blogisarjan tarkoitus on esitellä muutama näistä ominaisuuksista käytännön esimerkkien kautta, käyttäen koodia, jota voi kopioida omiin BigQuery-projekteihin. Tässä blogissa tutustutaan GA4-datamalliin, ja määritetään oma sessiotunniste. Lopuksi lasketaan sessiokohtaisia avainlukuja, joita tullaan hyödyntämään seuraavassa osassa.
BigQuery-dataa kysellään BQ:n omalla SQL-kieliversiolla. Se seuraa melko tarkasti muita suosittuja SQL-kieliversioita, mutta poikkeaa syntaksissa joissain asioissa. Kuitenkin vähimmäisvaatimus BigQueryn käyttöön on minkä tahansa SQL:n perusosaaminen – erikoisuudet opitaan helpoiten kantapään kautta googlaamalla. Tämän blogin oletuksena on, että lukija osaa itse navigoida omaan BQ:nsa Googlen Cloud Portalissa, ja että osaa jollain tasolla lukea SQL-kieltä.
GA4-data BigQueryssä
Yksi suuri ero UA:n ja GA4:n välillä on se, että GA4:n data exportataan eventti-tasolla, kun taas UA-datan exportin alin taso on sessio. Muutenkin GA4:n datamalli on kompleksisempi, sillä se sisältää paljon ryhmitettyjä kenttiä (array tai BQ-kielellä repeated record), joita joudutaan purkamaan, jos niiden sisällä olevat tiedot halutaan käsitellä.
Ryhmitetyllä kentällä tarkoitetaan periaatteessa sitä, että sen sijaan, että yhdessä sarakkeessa on yhdenlaista tietoa, on meillä yhdessä sarakkeessa erilaisia avain-arvo pareja – esimerkkinä avain voi olla merkkisarja ”ga_session_id ” (GA4:n sessiotunniste) ja arvo voi olla kyseinen tunniste.
Tämä hankaloittaa datan käsittelyä, koska jotta näitä avain-arvo pareja voitaisiin hyödyntää, joudutaan ensin pilkkomaan ne erillisiksi kentiksi käyttämällä UNNEST komentoa. Esimerkiksi jos halutaan kyseinen ga_session_id käyttöön, joudutaan ensin purkamaan event_params ryhmitetty kenttä erillisiksi sarakkeiksi, ja sen jälkeen valitsemaan ainoastaan näistä sarakkeista tarvittavat tiedot.
SELECT
*,
(SELECT
value.int_value
FROM UNNEST(event_params) WHERE key = "ga_session_id") AS ga_session_id
FROM `[project_id.dataset_id].events_20220829`
Tämä tarkoittaa sitä, että jotta dataa pystyy analysoimaan sessiotasolla GA4:n datamallissa, täytyy ensin muotoilla data oikeanmalliseksi, ja sen jälkeen laskemaan relevantit tiedot analysoitavalla tasolla. Vaikka tämä menetelmä on hankalampi, sisältää se kuitenkin myös hyviä puolia. Esimerkiksi, jos mielestämme Googlen määrittely sessiolle on jostain syystä riittämätön, voimme yhtä hyvin rakentaa oman määrityksen.
Oman sessiotunnisteen luominen
Luodaksemme oman sessiotunnisteen joudumme ensin miettimään kyseisen tunnisteen logiikkaa. Sessio määritetään saman käyttäjän eventtien perusteella, niin että sessio päättyy, kun jotkut ehdot täyttyvät. Google käyttää esimerkiksi määrittelyssään session eventtien välisenä maksimi aikana 30 minuuttia, jonka lyhentämistä voisimme harkita.
Ensimmäinen askel on laskea aikaleimaperusteinen indeksi käyttäjäkohtaisille eventeille, sekä laskea samalla tavalla eventtien välisen minuuttimäärän. Sen jälkeen valitaan näistä eventeistä ne, jotka täyttävät session aloitusehdot – tässä tapauksessa valitaan eventit joiden indeksi on 1 tai joiden eventtien välinen aika on yli 15 minuuttia. Kun tehdään tälle listalle uusi käyttäjäkohtainen indeksointi, saadaan sessionumerointi, jota voidaan liittää käyttäjätunnisteeseen, josta muodostuu sessiotunniste. Tätä näkymää voidaan sitten liittää eventtitauluun, jotta pystymme jatkossa hyödyntämään uutta sessiotunnistetta.
SELECT
user_pseudo_id,
minutes_since_previous_event,
event_id AS min_event,
event_timestamp AS min_timestamp,
LEAD(event_id) OVER(PARTITION BY user_pseudo_id ORDER BY event_id) AS max_event,
LEAD(event_timestamp) OVER(PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS max_timestamp,
CONCAT(user_pseudo_id, "-", ROW_NUMBER() OVER(PARTITION BY user_pseudo_id)) AS session_id,
ROW_NUMBER() OVER(PARTITION BY user_pseudo_id) AS session_number
FROM(
SELECT
user_pseudo_id,
event_timestamp,
ROW_NUMBER() OVER(PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS event_id,
(event_timestamp - LAG(event_timestamp) OVER(PARTITION BY user_pseudo_id ORDER BY event_timestamp))/1000000/60 AS minutes_since_previous_event
FROM `[project_id.dataset_id].events_2022*`
WHERE user_pseudo_id IS NOT NULL
AND user_pseudo_id <> "null"
) x
WHERE event_id = 1 OR minutes_since_previous_event >= 15
Kätevin tapa järjestää tämä kokonaisuus, on luoda siitä näkymä, joka sisältää yllä olevan logiikan. Kun se on luotu, voidaan aina kysellä näkymää, kun haluamme hyödyntää uuden sessiotunnisteemme.
CREATE OR REPLACE VIEW `[project_id.dataset_id].events_with_session2022` AS
WITH sessions AS (
SELECT
user_pseudo_id,
minutes_since_previous_event,
event_id AS min_event,
event_timestamp AS min_timestamp,
LEAD(event_id) OVER(PARTITION BY user_pseudo_id ORDER BY event_id) AS max_event,
LEAD(event_timestamp) OVER(PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS max_timestamp,
CONCAT(user_pseudo_id, "-", ROW_NUMBER() OVER(PARTITION BY user_pseudo_id)) AS session_id,
ROW_NUMBER() OVER(PARTITION BY user_pseudo_id) AS session_number
FROM(
SELECT
user_pseudo_id,
event_timestamp,
ROW_NUMBER() OVER(PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS event_id,
(event_timestamp - LAG(event_timestamp) OVER(PARTITION BY user_pseudo_id ORDER BY event_timestamp))/1000000/60 AS minutes_since_previous_event
FROM `[project_id.dataset_id].events_2022*`
WHERE user_pseudo_id IS NOT NULL
AND user_pseudo_id <> "null"
) x
WHERE event_id = 1 OR minutes_since_previous_event >= 15),
events AS(
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS event_id
FROM `[project_id.dataset.id].events_2022*`
WHERE user_pseudo_id IS NOT NULL
AND user_pseudo_id <> "null")
SELECT
e.*,
s.session_id,
s.min_event,
s.max_event,
s.min_timestamp,
s.max_timestamp,
s.session_number,
FROM events AS e
LEFT JOIN sessions AS s
ON s.user_pseudo_id = e.user_pseudo_id
AND e.event_id >= s.min_event
AND e.event_id < IFNULL(s.max_event, 99999999999999)
Kun tämä näkymä on luotuna, voidaan käyttää sitä laskemaan sessiokohtaisesti lukuja. Lasketaan käyttäjien klikkimäärät, sivustolla vietettyä aika minuuteissa, sekä uniikit eventtikokonaisuudet.
SELECT
session_id,
SUM(CASE WHEN event_name = “click” THEN 1 ELSE 0 END) AS clicks,
(MAX(event_timestamp) - MIN(event_timestamp))/1000000/60 AS minutes_on_site,
COUNT(DISTINCT event_bundle_sequence_id) AS events
FROM `[project_id.dataset.id].events_with_session2022`
GROUP BY session_id
Tässä blogissa on käsitelty GA4:n datamallia, luotu oma sessiotunniste ja hyödynnetty tunnistetta laskemaan sessiokohtaisia lukuja käyttäjistämme. Seuraavassa osassa otetaan käyttöön BigQueryn mallintamistyökalut, ja tehdään käyttäjädatan perusteella asiakassegmentointia.
Onko BigQueryn ja GA4:n käytön aloitus ajankohtainen?
Lue lisää data- ja analytiikka-palveluistamme ja ota yhteyttä!
