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