Anleitung: Anpassen der Standard-Sitzungskonfiguration für "engaged sessions" (GA4)

Die obige Berechnung für”engaged sessions” basiert auf dem Event user_engagement. Genauer gesagt: wenn der string.value für den Parameter key session_engaged = ‘1’. Die Definition von “engaged sessions” in GA4 lautet wie folgt: Die Anzahl der Sitzungen, die länger als 10 Sekunden dauerten, ein Konversionsereignis hatten oder 2 oder mehr Bildschirm- oder Seitenaufrufe hatten.

Sie können ein Konvertierungsereignis in der Benutzeroberfläche festlegen, aber die Definitionen der Sitzungsdauer oder der Anzahl der Seitenaufrufe können nicht geändert werden, wenn Sie das Ereignis user_engagement verwenden. Was aber, wenn wir die Definition einer “engaged sessions” anpassen wollen? In diesem Tutorial zeige ich Ihnen, wie Sie Ihre eigene Definition einer “engaged sessions” erstellen, die auch auf Ihre historischen Daten angewendet werden kann.

Datumsbereich

Wir beginnen mit dem Rahmen für unsere Abfrage. Lassen Sie uns zunächst einen Zeitrahmen definieren. In diesem Fall nehme ich einen Bereich mit einem statischen Start- und einem dynamischen Enddatum.

-- subquery to define static and/or dynamic start and end date
with date_range as (
select
    '20210101' as start_date,
    format_date('%Y%m%d',date_sub(current_date(), interval 1 day)) as end_date)

GA4- wiederholen

Wenn Sie sich die obige Definition einer “engaged sessions” in GA4 ansehen, werden Sie feststellen, dass wir Sitzungen zählen, die mindestens eine der folgenden drei Bedingungen erfüllen:

Sitzungsdauer größer als 10 Sekunden, oder
2 oder mehr Bildschirm-/Seitenaufrufe, oder
Konvertierungsereignis

Wir bauen unsere Abfrage in der gleichen Struktur auf, mit einer Basis-Unterabfrage und drei weiteren Unterabfragen, die jeweils eine der oben genannten Bedingungen überprüfen. Dann sammeln wir alle Sitzungen in einer weiteren Unterabfrage und deduplizieren sie. Schließlich zählen wir die Sitzungen nach Datum in unserer Hauptabfrage.

Übersetzt in SQL sieht der Rahmen unserer Abfrage wie folgt aus:

-- subquery to define static and/or dynamic start and end date for the whole query
with date_range as (
select
    '20210101' as start_date,
    format_date('%Y%m%d',date_sub(current_date(), interval 1 day)) as end_date),
    
-- base query to pull in all raw data
base as (  
select
    'loremimpsum'
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`,
    date_range
where
    _table_suffix between date_range.start_date and date_range.end_date),
    
-- subquery to get all sessions with a length > 10 seconds
session_length as (
select
    'loremimpsum'
from
    base),
    
-- subquery to get all sessions with 2 or more (unique) page views
multiple_pageviews as (
 select
    'loremimpsum'
from
    base),
    
-- subquery to get all sessions with a conversion event (in this example 'first_visit')
conversion_event as (
select
    'loremimpsum'
from
    base),

-- subquery to combine and deduplicate all subqueries generated earlier
dedup as (
select
    *
from
    session_length
union distinct 
select
    *
from
    multiple_pageviews 
union distinct
select
    *
from
    conversion_event)

-- main query to count unique engaged sessions by date in descending order
select
    *
from
    dedup

Jetzt haben wir unseren Rahmen, es geht nur noch darum, die Details auszufüllen. Danach können Sie die Bedingungen leicht anpassen, um Ihre eigene Definition einer besuchten Sitzung in GA4 zu erstellen.

Basisabfrage

Ich werde hier nur die Basisabfrage bereitstellen. Sie macht vielleicht noch nicht viel Sinn, aber wir brauchen sie, um loszulegen. Dies ist die einzige Abfrage, die Daten aus dem event_ schema von GA4 bezieht, also stellen Sie sicher, dass Sie den Verweis auf Ihren Exportort ändern.

-- base query to pull in all raw data
base as (
select
    user_pseudo_id,
    (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id,
    event_name,
    event_timestamp,
    (select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location') as pageview_location
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`,
    date_range
where
    _table_suffix between date_range.start_date and date_range.end_date)

Sitzungslänge

Beginnen wir mit unserer ersten sinnvollen Unterabfrage, die für jede Sitzung in unserem Datumsbereich prüft, ob die Sitzungslänge größer als 10 Sekunden ist. Für diese Bedingung benötigen wir:

die User-ID (Benutzerkennung)
die Session-ID (Sitzungsnummer)
den Zeitstempel des ersten Ereignisses der Sitzung
eine Berechnung der Zeit in Sekunden zwischen dem ersten und dem letzten Ereigniszeitstempel der Sitzung

Dann gruppieren wir alles nach Benutzer-ID und Sitzungsnummer. Die 10-Sekunden-Bedingung wird in der Having-Klausel konfiguriert.

-- subquery to get all sessions with a length > 10 seconds
session_length as (
select
    user_pseudo_id,
    session_id,
    timestamp_micros(min(event_timestamp)) as session_start_time,
    (max(event_timestamp)-min(event_timestamp))/1000000 as session_length_seconds
from
    base
group by
    user_pseudo_id,
    session_id
having
    -- change this number to adjust the desired session length
    session_length_seconds > 10
order by
    user_pseudo_id,
    session_id)

Mehrere Seitenaufrufe

Als Nächstes: die Unterabfrage, die jede Sitzung in unserem Datumsbereich nach der Bedingung “mehrere Seitenaufrufe” durchsucht. Damit dies funktioniert, benötigen wir:

die User-ID (Benutzerkennung)
die Session-ID (Sitzungsnummer)
den Zeitstempel des ersten Ereignisses der Sitzung
die Anzahl der Seitenaufrufe in der Sitzung

Um viele doppelte Zeilen zu vermeiden, verwende ich eine Fensterfunktion. Dann gruppieren wir alles nach Benutzer-ID und Sitzungs-ID. Die Bedingung für die Anzahl der Seitenaufrufe wird in der Where-Klausel konfiguriert.

-- subquery to get all sessions with 2 or more (unique) page views
multiple_pageviews as (
select
    user_pseudo_id,
    session_id,
    session_start_time,
    pageviews
from (
    select
        user_pseudo_id,
        session_id,
        timestamp_micros(min(event_timestamp)) as session_start_time,
        count(pageview_location) over (partition by user_pseudo_id, session_id) as pageviews,
        row_number() over (partition by user_pseudo_id, session_id) as row_number
    from
        base
    group by
        user_pseudo_id,
        session_id,
        pageview_location)
where
    row_number = 1
    -- change this number to adjust the desired amount of page views
    and pageviews >= 2)

Ereignis der Konvertierung
Der nächste Teil unserer Reise ist relativ einfach. Wir müssen nur die Sitzungen auswählen, die ein Konvertierungsereignis enthalten. Auch hier wird die Konfiguration in der Where-Klausel vorgenommen.

Außerdem greifen wir auf die Basisabfrage zurück:

die User-ID
die Session-ID
den Zeitstempel des ersten Ereignisses der Sitzung

-- subquery to get all sessions with a conversion event (in this example 'first_visit')
conversion_event as (
select
    user_pseudo_id,
    session_id,
    timestamp_micros(min(event_timestamp)) as session_start_time
from
    base
where
    -- change this event_name to adjust the desired conversion event
    event_name = 'first_visit'
group by
    user_pseudo_id,
    session_id)

Ergebnisse trennen

Da sich die ausgewählten Sitzungen, die in den vorherigen Unterabfragen definiert wurden, überschneiden können, müssen wir die Ergebnisse trennen, damit wir später in unserer Hauptabfrage eindeutige Sitzungen zählen können. Um dies zu erreichen, verwenden wir union distinct.

-- subquery to combine and deduplicate all subqueries generated earlier
dedup as (
select
    user_pseudo_id,
    session_id,
    session_start_time
from
    session_length
union distinct 
select
    user_pseudo_id,
    session_id,
    session_start_time
from
    multiple_pageviews 
union distinct
select
    user_pseudo_id,
    session_id,
    session_start_time
from
    conversion_event)

Sitzungen zählen

Die dedup-Abfrage erzeugt eine Liste eindeutiger Sitzungen, die wir nur noch zählen, nach Datum gruppieren und in absteigender Reihenfolge präsentieren müssen.

-- main query to count unique engaged sessions by date in descending order
select
    date(session_start_time) as date,
    count(distinct concat(user_pseudo_id,session_id)) as engaged_sessions
from
    dedup
group by
    date
order by
    date desc

Finale Abfrage

Jetzt haben wir unsere Bausteine, und wir können alles zusammenfügen, indem wir dem Rahmen folgen, den wir zuvor in entworfen haben.

-- subquery to define static and/or dynamic start and end date
with date_range as (
select
    '20211222' as start_date,
    format_date('%Y%m%d',date_sub(current_date(), interval 1 day)) as end_date),

-- base query to pull in all raw data
base as (
select
    user_pseudo_id,
    (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id,
    event_name,
    event_timestamp,
    (select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location') as pageview_location
from
    -- change this to your google analytics 4 export location in bigquery
    `privat-327611.analytics_266663932.events_*`,
    date_range
where
    _table_suffix between date_range.start_date and date_range.end_date),

-- subquery to get all sessions with a length > 10 seconds
session_length as (
select
    user_pseudo_id,
    session_id,
    timestamp_micros(min(event_timestamp)) as session_start_time,
    (max(event_timestamp)-min(event_timestamp))/1000000 as session_length_seconds
from
    base
group by
    user_pseudo_id,
    session_id
having
    -- change this number to adjust the desired session length
    session_length_seconds > 10
order by
    user_pseudo_id,
    session_id),
    
-- subquery to get all sessions with 2 or more (unique) page views
multiple_pageviews as (
select
    user_pseudo_id,
    session_id,
    session_start_time,
    pageviews
from (
    select
        user_pseudo_id,
        session_id,
        timestamp_micros(min(event_timestamp)) as session_start_time,
        count(pageview_location) over (partition by user_pseudo_id, session_id) as pageviews,
        row_number() over (partition by user_pseudo_id, session_id) as row_number
    from
        base
    group by
        user_pseudo_id,
        session_id,
        pageview_location)
where
    row_number = 1
    -- change this number to adjust the desired amount of page views
    and pageviews >= 2),
    
-- subquery to get all sessions with a conversion event (in this example 'first_visit')
conversion_event as (
select
    user_pseudo_id,
    session_id,
    timestamp_micros(min(event_timestamp)) as session_start_time
from
    base
where
    -- change this event_name to adjust the desired conversion event
    event_name = 'first_visit'
group by
    user_pseudo_id,
    session_id),

-- subquery to combine and deduplicate all subqueries generated earlier
dedup as (
select
    user_pseudo_id,
    session_id,
    session_start_time
from
    session_length
union distinct 
select
    user_pseudo_id,
    session_id,
    session_start_time
from
    multiple_pageviews 
union distinct
select
    user_pseudo_id,
    session_id,
    session_start_time
from
    conversion_event)

-- main query to count unique engaged sessions by date in descending order
select
    date(session_start_time) as date,
    count(distinct concat(user_pseudo_id,session_id)) as engaged_sessions
from
    dedup
group by
    date
order by
    date desc