Hoe haal je de exit rate op uit BigQuery?

Ter voorbereiding op het uitfaseren van Universal Analytics per 1 juli 2023, zijn wij bij Online Dialogue hard aan de slag gegaan om ervoor te zorgen dat we alle analyses die we eerst deden in Universal Analytics voortaan ook in GA4 kunnen doen. In ons dagelijks werk maakten we voorheen altijd veel gebruik van de Google Analytics add-on voor Google Sheets. Deze tool maakte het mogelijk om op een snelle en toegankelijke manier custom analysesheets te bouwen om bijvoorbeeld een eerste indruk te krijgen van de websiteperformance van een nieuwe klant, geavanceerde funnelanalyses te maken en uitgebreide A/B-testanalyses te standaardiseren. Met de overstap naar GA4 doen we nu dezelfde analyses met BigQuery-data.

In het afgelopen jaar hebben we veel ervaring opgedaan met het schrijven van query’s om onze analysevragen te beantwoorden met GA4-data. We hebben een uitgebreide set aan query’s gemaakt voor de verschillende metrics die veelvuldig worden gebruikt in A/B-testprogramma’s van onze klanten. In deze blog lichten we een van deze metrics voor je uit: de exit rate (het uitstappercentage), en leggen we stap voor stap uit hoe deze query werkt. We maken hier gebruik van de ruwe GA4 data die beschikbaar wordt gemaakt in BigQuery. Daarbij gaan we er dus van uit dat GA4 al aan BigQuery gekoppeld is.

GA4-data en API-data versus BigQuery-data

Voordat we de query induiken, eerst een paar woorden over het verschil tussen data uit GA4, de API en BigQuery. Want waarom gebruiken we niet gewoon de nieuwe Analytics API van Google? De belangrijkste reden daarvoor is dat de data uit de GA4-interface en de API niet 100% accuraat is. In plaats van de daadwerkelijke aantallen geeft GA4 namelijk een schatting op basis van een HyperLogLog++-algoritme weer. Voor veel doeleinden is deze schatting prima, bijvoorbeeld als je de algemene trend in je bezoekersaantallen wil bekijken. Voor een A/B-test ligt dat echter anders. Slechts 1% verschil tussen het weergegeven aantal en het daadwerkelijke aantal conversies of gebruikers kan ertoe leiden dat bijvoorbeeld een test die eigenlijk een winnaar is inconclusive lijkt. Voor een gedegen analyse van A/B-testen hebben we dus zeer accurate data nodig en deze is alleen beschikbaar via BigQuery. 

Wat betekent ‘exit rate’?

Daarnaast is het belangrijk om te definiëren wat de term ‘exit rate’ of ‘uitstappercentage’ betekent. Google beschrijft het als “Voor alle paginaweergaven op de pagina is het uitstappercentage het percentage pagina’s dat de laatste in de sessie was.”. Met andere woorden: deel het aantal paginaweergaven op een specifieke pagina dat de laatste paginaweergave van een sessie is door het totaal aantal paginaweergaven op die pagina, en vermenigvuldig dat met 100%. Of korter: exits / pageviews * 100%.

Exit Rate ophalen uit BigQuery

Dan is het nu tijd voor de query! De query bestaat uit zeven subquery’s die uiteindelijk één tabel als resultaat hebben, met daarin de algemene exit rate van een pagina. Om het onderscheid te maken tussen het percentage voor de twee varianten van een A/B-test, zijn nog aanvullende subquery’s nodig om op te halen welke pageviews bij welke testvariant horen. Omdat de precieze opzet daarvan voor iedereen zal verschillen, laten we dat gedeelte hier buiten beschouwing. 

Variabelen

Bovenaan de query declareren we een aantal variabelen zodat we op verschillende plekken in de query dezelfde informatie kunnen gebruiken, of om die informatie gemakkelijk bovenaan aan te kunnen passen. In dit geval declareren we de begindatum, einddatum en de pagina waarvoor je de exit rate wil bepalen.

DECLARE STARTDATE STRING DEFAULT '20230601';
DECLARE ENDDATE STRING DEFAULT '20230628';
DECLARE PAGE STRING DEFAULT 'http://example.com/product/nice-product';

De begin- en einddatum worden gebruikt om de juiste tabellen op te halen. In BigQuery staat namelijk per dag 1 tabel met alle Analytics-data van die dag. Elke tabelnaam eindigt met de datum in het yyyymmdd-formaat. Dat is dan ook het datumformaat dat in de variabelen gebruikt moet worden. In het bovenstaande voorbeeld is dat bijvoorbeeld ‘20230601’ voor 1 juni 2023. 

De pagina die je als waarde aan de variabele meegeeft, zal in de query behandeld worden als een reguliere expressie (regex). Je kunt er een specifieke URL ingeven, of een met een regex een hele groep pagina’s (bijv. alle productpagina’s) tegelijk bekijken.

Paginaweergaven en gebruikers

In de eerste subquery halen we alle pageviews op, met bijbehorend tijdstip, pseudo-id van de gebruiker, sessienummer en url. We hebben de pageviews van alle pagina’s nodig, om later te kunnen bepalen wat de laatste pageview van een sessie is. Vervang in de onderstaande query <jouw-projectnaam> en <jouw-datasetnaam> door de juiste namen. De laatste regel van deze subquery zorgt ervoor dat alleen data opgehaald wordt uit de tabellen die horen bij een datum tussen de opgegeven startdatum en einddatum.

WITH users_and_page_views AS (
 SELECT
   event_timestamp,
   user_pseudo_id,
   value.int_value AS session_number,
   (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_url
 FROM `<jouw-projectnaam>.<jouw-datasetnaam>.events_*`, UNNEST(event_params)
 WHERE event_name = 'page_view'
 AND key = 'ga_session_number'
 AND _table_suffix BETWEEN STARTDATE AND ENDDATE
),

Timestamp van de laatste pageview per sessie

Vervolgens bekijken we op basis van het resultaat van de vorige subquery wat de timestamp van de laatste pageview van elke sessie is. Het resultaat van deze query is een lijst met timestamps en hun bijbehorende gebruiker en sessie. Zo weten we op welk tijdstip de laatste pageview-actie van een gebruiker binnen een sessie is geweest. Omdat een gebruiker meerdere sessies kan hebben, kan die ook meerdere exits hebben. Daarom is het belangrijk om deze stap uit te voeren op sessieniveau.

timestamp_of_last_url_visited_per_session AS (
 SELECT
   MAX(event_timestamp) AS last_timestamp,
   user_pseudo_id,
   session_number,
 FROM users_and_page_views
 GROUP BY user_pseudo_id, session_number
),

Laatste URL per sessie

In de volgende stap is het van belang de informatie over het tijdstip van de laatste actie te koppelen met de locatie van de laatste actie. In de volgende subquery maken we daarom een overzicht van niet alleen de timestamp van de laatste pageview per sessie, maar ook de bijbehorende URL. Daarvoor combineren we de twee voorgaande tabellen, waarbij we de rijen aan elkaar koppelen op basis van de timestamp en user_pseudo_id. Op die manier weten we zeker dat we de juiste URL aan de juiste timestamp koppelen, ook als twee gebruikers toevallig op dezelfde tijd een pagina bekeken.

last_visited_url_per_session AS (
 SELECT DISTINCT
   timestamp_of_last_url_visited_per_session.last_timestamp,
   users_and_page_views.user_pseudo_id,
   users_and_page_views.session_number,
   users_and_page_views.page_url
 FROM timestamp_of_last_url_visited_per_session LEFT JOIN users_and_page_views
 ON timestamp_of_last_url_visited_per_session.user_pseudo_id = users_and_page_views.user_pseudo_id
   AND timestamp_of_last_url_visited_per_session.last_timestamp = users_and_page_views.event_timestamp
),

Exits per URL

In de voorgaande subquery’s hebben we alle informatie opgehaald die we nodig hebben voor het invullen van de exit rate-formule ( exits / pageviews * 100%). In de volgende subquery’s bepalen we de juiste aantallen van de pagina waarvan we de exit rate willen weten.

Nu we van elke gebruiker weten wat de laatste pageview van elk van hun sessies was, kunnen we bepalen hoeveel exits er op een bepaalde pagina waren. Hiervoor gebruiken we de PAGE die we bovenaan als variabele gedefinieerd hebben. Voor pageviews waarvan de URL voldoet aan de regex die gedefinieerd is in de PAGE-variabele, wordt geteld hoe vaak die voorkomen in de resultaattabel van de voorgaande subquery.

In het huidige voorbeeld hebben we ‘http://example.com/product/nice-product’ helemaal bovenaan als waarde meegegeven aan de PAGE-variabele. Dus dan telt de subquery hoe vaak die URL de laatste pageview in een sessie van een gebruiker was. Dit is de eerste waarde voor de berekening van de exit rate: exits / pageviews * 100%.

exits_on_page_per_url AS (
 SELECT
   page_url,
   COUNT(*) AS exit_count,
 FROM last_visited_url_per_session
 WHERE REGEXP_CONTAINS(page_url, PAGE)
 GROUP BY page_url
 ORDER BY page_url
),

Pageviews per URL

Om de tweede waarde uit de berekening te verkrijgen, telt de volgende subquery het totaal aantal pageviews op de gedefinieerde pagina. Daarvoor wordt het aantal pageviews geteld in de eerder gemaakte ‘users_and_page_views’-tabel die voldoet aan de regex uit de PAGE-variabele. Daarmee is de tweede waarde voor de berekening van de exit rate (exits / page views * 100%) ook gevonden. 

pageviews_per_url AS (
 SELECT
   page_url,
   COUNT(*) AS page_views,
 FROM users_and_page_views
 WHERE REGEXP_CONTAINS(page_url, PAGE)
 GROUP BY page_url
 ORDER BY page_url
)

De exit rate berekenen

De laatste stap is de daadwerkelijke berekening van de exit rate. Daarvoor combineren we de data uit de twee voorgaande subquery’s. Die tabellen worden gecombineerd op basis van de URL. Op basis van de data uit die twee tabellen wordt de volgende som uitgerekend: exits / pageviews * 100%.

​​SELECT
 pageviews_per_url.page_url,
 exits_on_page_per_url.exit_count / pageviews_per_url.page_views * 100 AS exit_rate
FROM pageviews_per_url INNER JOIN exits_on_page_per_url
 ON pageviews_per_url.page_url = exits_on_page_per_url.page_url

Het resultaat is een tabel met daarin de URL en de bijbehorende exit rate. 

Gelukt! Je hebt nu de exit rate van de gewenste pagina berekend!