Je bent al aan de slag gegaan met Google Analytics 4 (GA4), maar de standaard rapporten tonen slechts het topje van de ijsberg. De échte, diepgaande inzichten zitten verborgen in de onbewerkte data. Door je GA4-property te koppelen aan BigQuery, krijg je toegang tot een krachtig data warehouse en de mogelijkheid om met SQL complexe analyses uit te voeren. Zo verander je ruwe data in bruikbare informatie.

In deze blogpost deel ik 5 praktische en geavanceerde SQL-queries om je te helpen die verborgen inzichten te ontdekken. Ik geef je concrete voorbeelden en use-cases, zodat je direct aan de slag kunt.

1. Het onthullen van de customer journey met sessie-gebaseerde attributie

Attributie is een van de grootste uitdagingen in webanalyse. De standaard ‘last-click’ modellen geven een vertekend beeld. Deze query analyseert de gehele sessie en onthult welke verkeersbronnen daadwerkelijk de conversie hebben ingeluid.

WITH
SessionAttribution AS (
SELECT
user_pseudo_id,
session_id,
MIN(event_timestamp) OVER (PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp ASC) AS session_start_timestamp,
FIRST_VALUE(traffic_source.medium IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp ASC) AS first_medium,
FIRST_VALUE(traffic_source.source IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp ASC) AS first_source,
MAX(CASE WHEN event_name = 'conversion_event_name' THEN 1 ELSE 0 END) OVER (PARTITION BY user_pseudo_id, session_id) AS converted
FROM
`your_project_id.your_dataset_id.events_*`
CROSS JOIN UNNEST(event_params) AS event_param
LEFT JOIN UNNEST(event_params) AS traffic_source
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
AND traffic_source.key = 'session_start'
AND event_param.key = 'session_id'
)
SELECT
first_medium,
first_source,
COUNT(DISTINCT session_id) AS total_sessions,
SUM(converted) AS total_conversions,
SAFE_DIVIDE(SUM(converted), COUNT(DISTINCT session_id)) AS conversion_rate
FROM
SessionAttribution
GROUP BY
first_medium,
first_source
ORDER BY
total_conversions DESC;

Gebruiksdoel

Identificeer welke marketingkanalen aan het begin van een sessie het meest effectief zijn in het genereren van conversies. Dit helpt je om je budget slimmer te verdelen en je acquisitiestrategie te optimaliseren.

2. High-value klanten herkennen met lifetime value (LTV)

Waar komen je beste klanten vandaan? Met een LTV-berekening segmenteer je gebruikers en ontdek je welke groepen op de lange termijn het meest waardevol zijn voor je bedrijf.

WITH
UserPurchaseData AS (
SELECT
user_pseudo_id,
SUM(CAST(event_params.value.int_value AS BIGNUMERIC)) AS total_revenue,
COUNT(DISTINCT event_timestamp) AS number_of_days
FROM
`your_project_id.your_dataset_id.events_*`,
UNNEST(event_params) AS event_params
WHERE
event_name = 'purchase'
AND event_params.key = 'value'
AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY
user_pseudo_id
),
UserDemographics AS (
SELECT
user_pseudo_id,
MAX(CASE WHEN event_params.key = 'user_property_name' THEN event_params.value.string_value ELSE NULL END) AS user_segment
FROM
`your_project_id.your_dataset_id.events_*`,
UNNEST(event_params) AS event_params
WHERE
event_name = 'user_engagement' -- Or relevant event containing user properties
AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY
user_pseudo_id
)
SELECT
d.user_segment,
AVG(p.total_revenue) AS average_lifetime_value,
AVG(p.number_of_days) AS average_purchase_frequency
FROM
UserPurchaseData p
LEFT JOIN
UserDemographics d ON p.user_pseudo_id = d.user_pseudo_id
GROUP BY
d.user_segment
ORDER BY
average_lifetime_value DESC;

Gebruiksdoel

Ontdek welke gebruikersegmenten de meeste omzet genereren. Zo kun je je marketing inspanningen richten op het werven en behouden van de meest waardevolle gebruikers.

3. Conversie funnels optimaliseren met padanalyse

Begrijpen hoe gebruikers zich door je website bewegen, is cruciaal om bottlenecks te vinden. Met deze query breng je de meest voorkomende paden in kaart.

WITH
SessionEvents AS (
SELECT
user_pseudo_id,
event_name,
event_timestamp,
ROW_NUMBER() OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp ASC) AS event_order
FROM
`your_project_id.your_dataset_id.events_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
),
SessionPaths AS (
SELECT
user_pseudo_id,
STRING_AGG(event_name ORDER BY event_order ASC LIMIT 5) AS session_path
FROM
SessionEvents
GROUP BY
user_pseudo_id
)
SELECT
session_path,
COUNT(*) AS path_count
FROM
SessionPaths
GROUP BY
session_path
ORDER BY
path_count DESC
LIMIT 10;

Gebruiksdoel

Identificeer de meest afgelegde paden die leiden tot een conversie – of juist tot een afhaker. Zo ontdek je waar je website, webshop of app verbetering nodig heeft.

4. Engagement meten met cohortanalyse

Cohortanalyse helpt je te begrijpen hoe groepen gebruikers zich over tijd gedragen. Deze query volgt het gedrag van gebruikers die in dezelfde periode zijn geworven.

WITH
UserAcquisition AS (
SELECT
user_pseudo_id,
MIN(event_date) AS acquisition_date
FROM
`your_project_id.your_dataset_id.events_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY
user_pseudo_id
),
UserActivity AS (
SELECT
user_pseudo_id,
event_date,
COUNT(DISTINCT event_name) AS event_count
FROM
`your_project_id.your_dataset_id.events_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY
user_pseudo_id,
event_date
)
SELECT
a.acquisition_date,
DATE_DIFF(ua.event_date, a.acquisition_date, DAY) AS days_since_acquisition,
COUNT(DISTINCT ua.user_pseudo_id) AS active_users,
AVG(ua.event_count) AS average_events_per_user
FROM
UserAcquisition a
JOIN
UserActivity ua ON a.user_pseudo_id = ua.user_pseudo_id
GROUP BY
a.acquisition_date,
days_since_acquisition
ORDER BY
a.acquisition_date,
days_since_acquisition;

Gebruiksdoel

Monitor het retentie- en engagement-gedrag van verschillende gebruikersgroepen. Dit is essentieel om de impact van productupdates of marketingcampagnes te meten.

Anomalieën detecteren met voortschrijdende gemiddelden

Een onverwachte daling of stijging in je data kan een kritiek probleem of een onverwachte kans zijn. Deze query signaleert afwijkingen ten opzichte van het normale patroon.

WITH
DailySessions AS (
SELECT
event_date,
COUNT(DISTINCT user_pseudo_id) AS daily_sessions
FROM
`your_project_id.your_dataset_id.events_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 60 DAY)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY
event_date
),
RollingAverage AS (
SELECT
event_date,
daily_sessions,
AVG(daily_sessions) OVER (ORDER BY event_date ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_average
FROM
DailySessions
)
SELECT
event_date,
daily_sessions,
rolling_average,
(daily_sessions - rolling_average) / rolling_average AS percentage_deviation
FROM
RollingAverage
WHERE
ABS((daily_sessions - rolling_average) / rolling_average) > 0.2 -- Adjust the threshold as needed
ORDER BY
event_date DESC;

Gebruiksdoel

Vind direct ongebruikelijke pieken of dalen in je websiteverkeer. Zo kun je snel reageren op technische problemen of succesvolle marketingacties.

De volgende stap

Deze 5 geavanceerde queries zijn een goed begin. Door de kracht van SQL en BigQuery te benutten, kun je een schat aan inzichten ontsluiten en je webanalyse naar een hoger niveau tillen. Ben je nog niet toe aan deze geavanceerde analyses en wil je eerst de basis goed hebben staan? Bekijk dan eens mijn blog voor het opzetten van BigQuery en de jouw 1e query opzetten.

Wil je de volledige potentie van je GA4-data benutten, maar heb je hier zelf de tijd of kennis niet voor? Ik help je graag met het opzetten van BigQuery, het bouwen van op maat gemaakte dashboards of het uitvoeren van diepgaande analyses. Neem vrijblijvend contact met me op en laten we kijken hoe ik jouw data inzichtelijk kan maken.

Neem contact op