GA4 Sitzungstransformation: Rohdaten in BigQuery umwandeln

Mit GA4 ist der Zugang zu Analytics Rohdaten in BigQuery kostenlos möglich. Die Marketing Teams und Analytics Manager stellt das zum einen vor eine Bandbreite an neuen Möglichkeiten, die vorher nur in der 360 Version möglich waren, aber zum anderen auch vor die Herausforderung, dass man Kenntnisse in BigQuery und SQL benötigt.

In diesem Artikel zeigen wir, wie wir Google Analytics 4 Rohdaten in BigQuery in eine Sitzungslogik transformieren, die wir als Basis für sehr viele Reportings oder weitere Analysen nutzen. Wir benutzen dafür SQL Code aus einer Looker Library von Google, den wir für viele kundenspezifische Anforderungen angepasst und erweitert haben.

1. Von den Rohdaten zum Dashboard in Lookerstudio

Warum sollte ich die Daten überhaupt transformieren und nicht einfach die Rohdatentabellen für ein Looker Studio Board als Datenquelle benutzen? Hier gibt es vor allem 2 Gründe: zum einen sind das die Abfragekosten, die bei direkter Verbindung der Rohdaten viel höher sind als bei vorprozessierten Daten, und zum anderen sind viele Auswertungen und Kombinationen von Auswertungen nicht möglich ohne Vorprozessierung.

Das Ziel ist, dass aus den Rohdaten eine Tabelle transformiert wird, die z.B. für ein Looker Studio Dashboard als Datenquelle dient. Dafür muss eine SQL Query die neu ankommenden Rohdaten in die gewünschte Struktur bringen und an die Zieltabelle anfügen.

Rahmenbedingungen für eine Session Transformation:

In welcher Umgebung soll die Query laufen?
In unserer Arbeit haben wir bisher mit grob 3 verschiedenen Arten gearbeitet, um ein SQL Statement auszuführen.

1. Scheduled Query in BigQuery ist die einfachste Methode. Wir bereiten das SQL in BigQuery vor und können es dann als geplante Abfrage speichern und zeitlich auslösen.
2. Externe Auslösung zum Beispiel über eine Cloud Function kann sinnvoll sein, wenn ein Auslösen an die Erstellung der Rohdaten in BigQuery gekoppelt werden soll. Man bereitet das SQL in BigQuery vor und bindet es dann in ein Python Script ein.
3. Auslösen mit Dataform erfordert die Anpassung des SQL Scripts an die Dataform Bedingungen. Ein Vorteil von Dataform ist, dass man inkrementelle Tabellen (Tabellen, an die neue Daten angehängt werden) erweitern kann und gleichzeitig Daten aus der Vergangenheit nochmals überschreibt.

Sollen auch intraday Tabellen einbezogen werden?
In manchen Fällen kann die Einschränkung, dass Google die Rohdaten mit mehr als einem Tag Verzug bereitstellt, mit den Anforderungen an das Dashboard kollidieren. Dann wäre es nötig, die intraday Tabellen in die Transformation einzubeziehen.
Mit den Intraday Tabellen könnte man die Vorprozessierung z.B. stündlich aktuell halten oder theoretisch auch mit nur wenigen Minuten Verzug neuen Traffic anzeigen.

In der Umsetzung mit scheduled Query hat es sich sinnvoll gezeigt, die den Intraday-Tabellen in einer eigenen Query in separate Tabellen zu prozessieren und die Sessions aus den Intradaytabellen dann immer wieder zu ersetzen. Mit den inkrementellen Tabellen von Dataform könnte man aber auch alles in einer prozessierten Tabelle erledigen.

Sollen sich vergangene Daten ändern?
Das Ergebnis soll eine prozessierte Tabelle sein. Man hat die Möglichkeit, die letzten prozessierten Daten einfach nur der Tabelle hinzuzufügen. Die Daten vom letzten Tag werden an die Session Tabelle gehängt. Es gibt jedoch Use Cases, wo man einen Teil der schon fertigen Session Tabelle lieber überschreiben würde. Zum Beispiel gibt es Sitzungen, die 23.45Uhr anfangen und am nächsten Tag 2.00 erst aufhören. Diese Sitzung würde zuerst von 23.45Uhr bis 23.59Uhr analysiert werden und am nächsten Tag in den neuen Rohdaten wäre ersichtlich, dass es noch weiter ging.

Mit den inkrementellen Dataform ist die Berücksichtigung solcher Besonderheiten wesentlich einfacher, weil man anhand eines einzigartigen Schlüssels die gleiche Zeile immer wieder neu überschreiben kann.
Mit reinem SQL wäre eine solche Logik denkbar, aber ungleich komplexer umzusetzen.

Wann soll die SQL Query laufen?
Die Event-Tabellen von GA4 stehen im Regelfall am nächsten oder übernächsten Tag bereit. Man kann hier ein zeitlich gesteuertes Ausführen wählen oder eine Auslösung via Pub/Sub, die direkt auf das Anlegen der Rohdaten reagiert und danach folgt.

Wenn eine Query die Daten nur “von gestern” ziehen soll, wäre es möglich, dass noch keine Rohdaten vorhanden sind – Google benötigt meistens etwas länger. Wenn wir zeitgesteuert einmal am Tag auslösen, wäre es daher sicherer, die Daten nur von “vorgestern” zu ziehen. Die vorprozessierte Session Tabelle hat in diesem Fall also 2 Tage Verzug. Es gibt jedoch auch Fälle, wo die Daten von vorgestern noch nicht bereit stehen. Man kann die Dauer der Tabellenerstellung über die BigQuery Metadaten mit einer SQL Abfrage prüfen.

2. Sessionization anhand der Looker Library

Beim Erkunden von Looker Test Versionen sind wir auf einen Looker Block aufmerksam geworden, der von Google Mitarbeitern erstellt wurde und die GA4 auf ähnliche Weise nutzbar machen soll wie GA360 Rohdaten – nämlich mit 1 Zeile pro Session statt 1 Zeile pro Event.

https://github.com/llooker/ga_four_block_dev

Das Interessante an der Library ist der Aufbau in verschiedenen CTE (Common Table Expressions). Für verschiedene Arten von Feldern gibt es verschiedene kleine Abfragen (CTEs), die am Ende wieder zusammengeführt werden.

Es sind 3 verschiedene “Abfrageteile” in der Query:

“Session_list_with_event_history” – Anreichern der Rohdaten Events mit zusätzlichen Feldern, die spätere Gruppierung ermöglichen. Am wichtigsten ist hier der sl_key, der als SessionID eindeutig für eine Sitzung sein muss.
Verschiedene benötigte Felder wie z.B. “device_geo”, “session_conversions” oder “session_tags” sind eigene CTEs, die sich alle auf “Session_list_with_event_history” beziehen und nebeneinander aufgebaut werden. In jeder der CTE ist pro sl_key (Session ID) ein Eintrag mit der benötigten Information (z.B. Quelle, Medium usw.), die auf die SessionID aggregiert werden muss.
Im finalen Statement werden alle Teiltabellen aus Schritt 2 zusammen gejoint.

Dieser modulare Aufbau vereinfacht die Nutzung, Anpassung und Erweiterung in der Praxis. Zur Veranschaulichung dient folgende Abbildung (Darstellung aus Dataform, wo CTEs durch einzelne Views ersetzt wurden):

Abbildung 1: Flowchart der Session Transformation bei einer Umsetzung in Dataform

2.1 Step 1 – Anreichern der Rohdaten Events

Im ersten Schritt werden die Eventdaten angereichert mit Session ID und einigen event-bezogenen Informationen.

Zusätzlich eingebaut ist hier eine “dates” Tabelle mit dem Start- und Enddatum. Dies dient dazu, die Datumsangaben schnell zu ändern und ist bei komplexeren Abfragen mit mehreren Datum-Inputs für mich mittlerweile Best Practice.
In der vorletzten Zeile dieses Query Abschnitts sollte man `project.dataset.events_*` durch sein Dataset ersetzen.

with

dates as (Select 
  FORMAT_DATE("%Y%m%d",DATE_SUB(CURRENT_DATE("+1"), INTERVAL 2 DAY)) as start,
  FORMAT_DATE("%Y%m%d",DATE_SUB(CURRENT_DATE("+1"), INTERVAL 2 DAY)) as ende
),
-- obtains a list of sessions, uniquely identified by the table date, ga_session_id event parameter, ga_session_number event parameter, and the user_pseudo_id.
session_list_with_event_history as (
  select timestamp(PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(_TABLE_SUFFIX,r'[0-9]+'))) session_date
      ,  (select value.int_value from UNNEST(events.event_params) where key = "ga_session_id") ga_session_id
      ,  (select value.int_value from UNNEST(events.event_params) where key = "ga_session_number") ga_session_number
      ,  events.user_pseudo_id
      -- unique key for session:
      ,  timestamp(PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(_TABLE_SUFFIX,r'[0-9]+')))||(select value.int_value from UNNEST(events.event_params) where key = "ga_session_id")||(select value.int_value from UNNEST(events.event_params) where key = "ga_session_number")||events.user_pseudo_id sl_key
      ,  row_number() over (partition by (timestamp(PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(_TABLE_SUFFIX,r'[0-9]+')))||(select value.int_value from UNNEST(events.event_params) where key = "ga_session_id")||(select value.int_value from UNNEST(events.event_params) where key = "ga_session_number")||events.user_pseudo_id) order by events.event_timestamp) event_rank
      ,  (TIMESTAMP_DIFF(TIMESTAMP_MICROS(LEAD(events.event_timestamp) OVER (PARTITION BY timestamp(PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(_TABLE_SUFFIX,r'[0-9]+')))||(select value.int_value from UNNEST(events.event_params) where key = "ga_session_id")||(select value.int_value from UNNEST(events.event_params) where key = "ga_session_number")||events.user_pseudo_id ORDER BY events.event_timestamp asc))
         ,TIMESTAMP_MICROS(events.event_timestamp),second)/86400.0) time_to_next_event
      , case when events.event_name = 'page_view' then row_number() over (partition by (timestamp(PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(_TABLE_SUFFIX,r'[0-9]+')))||(select value.int_value from UNNEST(events.event_params) where key = "ga_session_id")||(select value.int_value from UNNEST(events.event_params) where key = "ga_session_number")||events.user_pseudo_id), case when events.event_name = 'page_view' then true else false end order by events.event_timestamp)
        else 0 end as page_view_rank
      , case when events.event_name = 'page_view' then row_number() over (partition by (timestamp(PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(_TABLE_SUFFIX,r'[0-9]+')))||(select value.int_value from UNNEST(events.event_params) where key = "ga_session_id")||(select value.int_value from UNNEST(events.event_params) where key = "ga_session_number")||events.user_pseudo_id), case when events.event_name = 'page_view' then true else false end order by events.event_timestamp desc)
        else 0 end as page_view_reverse_rank
      , case when events.event_name = 'page_view' then (TIMESTAMP_DIFF(TIMESTAMP_MICROS(LEAD(events.event_timestamp) OVER (PARTITION BY timestamp(PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(_TABLE_SUFFIX,r'[0-9]+')))||(select value.int_value from UNNEST(events.event_params) where key = "ga_session_id")||(select value.int_value from UNNEST(events.event_params) where key = "ga_session_number")||events.user_pseudo_id , case when events.event_name = 'page_view' then true else false end ORDER BY events.event_timestamp asc))
         ,TIMESTAMP_MICROS(events.event_timestamp),second)/86400.0) else null end as time_to_next_page -- this window function yields 0 duration results when session page_view count = 1.
      -- raw event data:
      , events.event_date
      , events.event_timestamp
      , events.event_name
      , events.event_params
      , events.event_previous_timestamp
      , events.event_value_in_usd
      , events.event_bundle_sequence_id
      , events.event_server_timestamp_offset
      , events.user_id
      -- , events.user_pseudo_id
      , events.user_properties
      , events.user_first_touch_timestamp
      , events.user_ltv
      , events.device
      , events.geo
      , events.app_info
      , events.traffic_source
      , events.stream_id
      , events.platform
      , events.event_dimensions
      , events.ecommerce
      , events.items
        from `project.dataset.events_*` events , dates
        where _TABLE_SUFFIX BETWEEN start AND ende

)

Das wichtigste Feld in dieser CTE ist sl_key. Der sl_key ist die eindeutige Session ID, auf die jede Aggregation in den weiteren Tabellen stattfindet. Sl_key sollte unique sein. In der Version von Google ist der SL_key wie folgt aufgebaut:
Tabellensuffix der Event Tabelle (also Datum) als Timestamp + ga_session_id Parameter + ga_session_number + user_pseudo_id.

In SQL sieht das so aus:

timestamp(PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(_TABLE_SUFFIX,r'[0-9]+')))||(select value.int_value from UNNEST(events.event_params) where key = "ga_session_id")||(select value.int_value from UNNEST(events.event_params) where key = "ga_session_number")||events.user_pseudo_id as sl_key

Dadurch, dass das Datum in die sl_key Definition mit eingeschlossen wird, kommt es dazu, dass durch die Transformation eine Sitzung am Ende des Tages enden und am nächsten Tag eine neue beginnen würde.

Wenn man Sitzungen über 2 Tage transformieren will, reicht es nicht aus, das Datum einfach aus der sl_key Definition herauszunehmen. Dafür müsste ein u.a. Lookback von einem Tag für die Session eingerichtet werden, um festzustellen, welche Sessions schon vor dem Analysezeitraum begonnen wurden. Dies ist in diesem Beitrag aber nicht thematisiert.

In den weiteren berechneten Feldern (event_rank, time_to_next_event, page_view_rank, page_view_reverse_rank etc.) ist die sl_key Definition ebenfalls zu finden, um in den Window Functions die Partitionierung festzulegen.

Abbildung 2: In Step 1 werden die GA4 Rohdaten angereichert mit IDs wie sl_key, SessionID und ClientID

2.2 Step 2 – Die Features erstellen

Auf Basis der Anreicherung mit sl_key kann nun in mehreren Feature Tabellen alles benötigte für die Transformation erstellt werden.

Die “Session Facts” beinhalten Basis Informationen wie Anzahl Pageviews, Events, Start und Ende der Session, Dauer und neu vs wiederkehrend.

, -- Session-Level Facts, session start, end, duration
session_facts as (
select sl.sl_key
      ,  COUNT(sl.event_timestamp) session_event_count
      ,  SUM(case when sl.event_name = 'page_view' then 1 else 0 end) session_page_view_count
      ,  COALESCE(SUM((select value.int_value from UNNEST(sl.event_params) where key = "engaged_session_event")),0) engaged_events
      ,  case when (COALESCE(SUM((select value.int_value from UNNEST(sl.event_params) where key = "engaged_session_event")),0) = 0
               and COALESCE(SUM((select coalesce(cast(value.string_value as INT64),value.int_value) from UNNEST(sl.event_params) where key = "session_engaged"))) = 0)
              then false else true end as is_engaged_session
            , case when countif(event_name = 'first_visit') = 0 then false else true end as is_first_visit_session
            , MAX(TIMESTAMP_MICROS(sl.event_timestamp)) as session_end
            , MIN(TIMESTAMP_MICROS(sl.event_timestamp)) as session_start
            , (MAX(sl.event_timestamp) - MIN(sl.event_timestamp))/(60 * 1000 * 1000) AS session_length_minutes
  from session_list_with_event_history sl
  group by 1)

Abbildung 3: Session Facts mit Anzahl Pageviews, Start, Ende und weiteren Informationen

In den “Session Tags” werden Kampagnen Informationen wiedergegeben. Die Vorlage von Google enthält hier immer die letzten Kampagnen Informationen einer Session. Es erschien jedoch sinnvoller, im Sessionisation Kontext die erste Kampagne/Quelle etc. der Sitzung zu verwenden.

, -- Retrieves the first non-direct medium, source, and campaign from the session's page_view events.
session_tags as (
select distinct sl.sl_key
      ,  first_value((select value.string_value from unnest(sl.event_params) where key = 'medium')) over (partition by sl.sl_key order by sl.event_timestamp asc) medium
      ,  first_value((select value.string_value from unnest(sl.event_params) where key = 'source')) over (partition by sl.sl_key order by sl.event_timestamp asc) source
      ,  first_value((select value.string_value from unnest(sl.event_params) where key = 'campaign')) over (partition by sl.sl_key order by sl.event_timestamp asc) campaign
      ,  first_value((select value.string_value from unnest(sl.event_params) where key = 'page_referrer')) over (partition by sl.sl_key order by sl.event_timestamp asc) page_referrer
  from session_list_with_event_history sl
  where sl.event_name in ('page_view')
    and (select value.string_value from unnest(sl.event_params) where key = 'medium') is not null -- NULL medium is direct, filtering out nulls to ensure last non-direct.
  )

Bei der Benutzung dieser Session-Tags gab es bisher die meisten Probleme mit den Rohdaten und auch mit der Logik in der Abfrage. Zum Beispiel gibt Google das “Medium” als “Organic” an, auch wenn eine “GCLID” enthalten ist. Außerdem kann es dazu kommen, dass wenn wir hier später weitere UTM Parameter(z.b. utm_content) hinzufügen, wäre es möglich, dass diese falsch zugeordnet werden. Wenn in der Session utm_content erst mitten in der Session auftaucht, wäre mit der first_value Funktion der Parameter zusammen mit der ersten Quelle der Sitzung vereint. Somit hätten wir eventuell nicht existente utm Kombinationen.

Das Thema Attribution soll im am Ende des Artikels noch einmal genauer beleuchtet werden.

Device Geo hat alle Device und GEO Informationen des Session Start Events.

, -- Device and Geo Columns from 'Session Start' event.
device_geo as (
  select sl.sl_key
      ,  sl.device.category device__category
      ,  sl.device.mobile_brand_name device__mobile_brand_name
      ,  sl.device.mobile_model_name device__mobile_model_name
      ,  sl.device.mobile_brand_name||' '||device.mobile_model_name device__mobile_device_info
      ,  sl.device.mobile_marketing_name device__mobile_marketing_name
      ,  sl.device.mobile_os_hardware_model device__mobile_os_hardware_model
      ,  sl.device.operating_system device__operating_system
      ,  sl.device.operating_system_version device__operating_system_version
      ,  sl.device.vendor_id device__vendor_id
      ,  sl.device.advertising_id device__advertising_id
      ,  sl.device.language device__language
      ,  sl.device.time_zone_offset_seconds device__time_zone_offset_seconds
      ,  sl.device.is_limited_ad_tracking device__is_limited_ad_tracking
      ,  sl.device.web_info.browser device__web_info_browser
      ,  sl.device.web_info.browser_version device__web_info_browser_version
      ,  sl.device.web_info.hostname device__web_info_hostname
      ,  case when sl.device.category = 'mobile' then true else false end as device__is_mobile
      ,  sl.geo.continent geo__continent
      ,  sl.geo.country geo__country
      ,  sl.geo.city geo__city
      ,  sl.geo.metro geo__metro
      ,  sl.geo.sub_continent geo__sub_continent
      ,  sl.geo.region geo__region
  from session_list_with_event_history sl
  where sl.event_name = 'session_start'
  )

Session Event Packing hat nochmal alle Event Daten pro Session als Array gespeichert. Dies ist zu empfehlen, wenn man die Sessionisation nicht nur als Dashboard Grundlage benutzen möchte, sondern auch weitere Analysen auf den transformierten Daten startet.

, -- Packs the event-level data into an array of structs, leaving a session-level row.
session_event_packing as (
  select sl.session_date session_date
      ,  sl.ga_session_id ga_session_id
      ,  sl.ga_session_number ga_session_number
      ,  sl.user_pseudo_id user_pseudo_id
      ,  sl.sl_key
      ,  ARRAY_AGG(STRUCT(  sl.sl_key
                          , sl.event_rank
                          , sl.page_view_rank
                          , sl.page_view_reverse_rank
                          , sl.time_to_next_event
                          , sl.time_to_next_page
                          , sl.event_date
                          , sl.event_timestamp
                          , sl.event_name
                          , sl.event_params
                          , sl.event_previous_timestamp
                          , sl.event_value_in_usd
                          , sl.event_bundle_sequence_id
                          , sl.event_server_timestamp_offset
                          , sl.user_id
                          , sl.user_pseudo_id
                          , sl.user_properties
                          , sl.user_first_touch_timestamp
                          , sl.user_ltv
                          , sl.device
                          , sl.geo
                          , sl.app_info
                          , sl.traffic_source
                          , sl.stream_id
                          , sl.platform
                          , sl.event_dimensions
                          , sl.ecommerce
                          , sl.items)) event_data
  from session_list_with_event_history sl
  group by 1,2,3,4,5
  )

Zusätzliche Features, die wir hinzugefügt haben:
Session Conversions, wo wir verschiedene Conversions bauen können, hier Namen der Events und Felder wechseln.

, session_conversions as (
select sl.sl_key
      -- Bestimmte Events aufsummieren
      ,  SUM(case when sl.event_name = 'generate_lead' then 1 else 0 end) conversion_generate_leads
      
      -- Bei bestimmten Events Sitzung als konvertiert zählen (max 1)
      , case when countif(event_name = 'generate_lead') = 0 then 0 else 1 end as conversion_is_lead
      
      -- Integer Werte aus bestimmten Event Parametern aufsummieren
      ,  COALESCE(SUM((select value.int_value from UNNEST(sl.event_params) where key = "engaged_session_event")),0) engaged_events
      
      --Conversion bei Aufruf einer Seite (Regex match)
     ,  COALESCE(MAX((select 1 from UNNEST(sl.event_params) where key = "page_location" and REGEXP_CONTAINS(value.string_value,"(url1/pfad1/)|(url2/pfad2/)"))),0) has_lead_url
      
  from session_list_with_event_history sl
  group by 1)

Session Dimensions, die Sitzungsdimensionen nach verschiedenen Kriterien bilden können.
Beispiele sind im SQL Code kommentiert.

, session_dimensions as (
    select sl.sl_key
  --Beispiel: Purpose Parameter aus dem ersten generate Lead Event der Session 
  ,ARRAY_AGG((select distinct value.string_value from UNNEST(sl.event_params) where key = "purpose" and event_name = "generate_lead" ) IGNORE NULLS ORDER BY event_timestamp asc )[offset(0)] first_lead_purpose
  --Beispiel: erster Purpose Parameter der Session 
  ,ARRAY_AGG((select distinct value.string_value from UNNEST(sl.event_params) where key = "purpose" ) IGNORE NULLS ORDER BY event_timestamp asc )[offset(0)] first_purpose
  --Beispiel: Purpose Parameter aus dem letzten generate Lead Event der Session 
  ,ARRAY_AGG((select distinct value.string_value from UNNEST(sl.event_params) where key = "purpose" and event_name = "generate_lead") IGNORE NULLS ORDER BY event_timestamp desc )[offset(0)] last_lead_purpose
 
--Landing Page Path
,ARRAY_AGG((select distinct value.string_value from UNNEST(sl.event_params) where key = "page_location" and event_name = "page_view" ) IGNORE NULLS ORDER BY event_timestamp asc )[offset(0)] landing_page_path

  from session_list_with_event_history sl 
group by 1
)

Ecommerce Totals, wo Umsätze & Transaktionsnummern aus einer oder mehreren Transaktionen pro Session aufsummiert werden.

, ecommerce_totals as (
 select  sl.sl_key,
--first_value((Select ecommerce.transaction_id )) over (partition by sl.sl_key  order by event_timestamp asc) transaction_id,
ARRAY_LENGTH(ARRAY_AGG(ecommerce.transaction_id)) as transactions,
STRING_AGG(ecommerce.transaction_id, ", " ORDER BY ecommerce.transaction_id)  as transaction_ids,

--ARRAY_AGG(ecommerce) as transaction_details, 
sum(ecommerce.total_item_quantity) as total_item_quantity,
sum(ecommerce.tax_value_in_usd) as tax_value_in_usd,
sum(ecommerce.purchase_revenue_in_usd) as purchase_revenue_in_usd,
sum(ecommerce.shipping_value_in_usd) as shipping_value_in_usd,
sum(ecommerce.unique_items) as unique_items,

  from session_list_with_event_history sl 
where sl.event_name in ('purchase')
group by 1
)

Session Pageviews – Alle besuchten Seiten mit Häufigkeiten als Array gespeichert, um Berichte mit Seite, Pageviews, Unique Pageviews etc. erstellen zu können.

, session_pageviews as (
  Select sl_key,
ARRAY_AGG((SELECT AS STRUCT page, pageviews)) views
  from (
Select sl_key, 
page, 
count(page) as pageviews,
from (
  select sl.sl_key
 ,(select value.string_value from UNNEST(sl.event_params) where key = "page_path" and event_name = "page_view" ) page
  from session_list_with_event_history sl 

)
Where page is not null
group by 1,2
)
group by 1
)

Abbildung 4: Pageview Array mit Page und Anzahl Views für jede Session

2.3 Step 3 – Alles in finalem Statement zusammenfügen

Um jetzt alle Features, die man in Schritt 2 definiert hat, in eine Tabelle zusammenzufügen, macht man einen Left Join auf Basis des sl_key, Das “session_date” dient zur Partitionierung der finalen Tabelle.
Als elegant erweisen sich auch die Struct Befehle der einzelnen Features. So können Metriken und Dimensionen gruppiert werden, was das Wiederfinden z.B. in Looker Studio erleichtert.

-- Final Select Statement:
select se.session_date session_date
    ,  se.ga_session_id ga_session_id
    ,  se.ga_session_number ga_session_number
    ,  se.user_pseudo_id user_pseudo_id
    ,  se.sl_key
    -- packing session-level data into structs by category
    ,  (SELECT AS STRUCT coalesce(sa.medium,'(none)') medium -- sessions missing last-non-direct are direct
                      ,  coalesce(sa.source,'(direct)') source
                      ,  coalesce(sa.campaign,'(direct)') campaign
                      ,  sa.page_referrer) session_attribution
    ,  (SELECT AS STRUCT sf.session_event_count
                      ,  sf.engaged_events
                      ,  sf.session_page_view_count
                      ,  sf.is_engaged_session
                      ,  sf.is_first_visit_session
                      ,  sf.session_end
                      ,  sf.session_start
                      ,  sf.session_length_minutes) session_data
    ,  (SELECT AS STRUCT d.device__category
                      ,  d.device__mobile_brand_name
                      ,  d.device__mobile_model_name
                      ,  d.device__mobile_device_info
                      ,  d.device__mobile_marketing_name
                      ,  d.device__mobile_os_hardware_model
                      ,  d.device__operating_system
                      ,  d.device__operating_system_version
                      ,  d.device__vendor_id
                      ,  d.device__advertising_id
                      ,  d.device__language
                      ,  d.device__time_zone_offset_seconds
                      ,  d.device__is_limited_ad_tracking
                      ,  d.device__web_info_browser
                      ,  d.device__web_info_browser_version
                      ,  d.device__web_info_hostname
                      ,  d.device__is_mobile) device_data
    ,  (SELECT AS STRUCT d.geo__continent
                      ,  d.geo__country
                      ,  d.geo__city
                      ,  d.geo__metro
                      ,  d.geo__sub_continent
                      ,  d.geo__region) geo_data
    
    ,  (SELECT AS STRUCT conv.* EXCEPT(sl_key)) conversions
    ,  (SELECT AS STRUCT dim.* EXCEPT(sl_key)) session_dimensions
         
      
    ,   (SELECT AS STRUCT ecom.* EXCEPT(sl_key)) ecommerce_totals
    
    --Event Daten / eventuell zu speicherintensiv                  
    ,  se.event_data event_data
from session_event_packing se
left join session_tags sa
  on  se.sl_key = sa.sl_key
left join session_facts sf
  on  se.sl_key = sf.sl_key
left join device_geo d
  on  se.sl_key = d.sl_key
left join session_conversions conv
  on  se.sl_key = conv.sl_key
left join session_dimensions  dim
  on  se.sl_key = dim.sl_key
left join ecommerce_totals  ecom
  on  se.sl_key = ecom.sl_key

3. Attribution von Channel, Quelle, Medium etc.

Eine besondere Herausforderung ist die Attribution der Kampagnen und Quellen Informationen an die Session. Die Logik der Library https://github.com/llooker/ga_four_block_dev ist hier in meinen Augen nicht ideal, da nur die letzte “nondirect” Quelle innerhalb der Sitzung gesucht wird. Das ist nicht das, was last-non-direct in Universal Analytics bedeutet.

Bei der Attribution kann man sich mehrere Fragen stellen:

In den Rohdaten selbst ist neben den “traffic_source” Feldern, die die erste Nutzerquelle speichern, lediglich Last Click Attribution ohne weiteres möglich. Reicht es vielleicht sogar aus, die Daten nach “Last-Click” und “First Click” auszuwerten. Dann wäre das Thema der Attribution viel weniger komplex.
Zu welcher Kampagne gehört die Sitzung, wenn mehrere Kampagnen in derselben Sitzung auftreten? Die Sitzungsquelle in GA4 bedeutet die erste Quelle der Session. Es erscheint sinnvoll, dieser Logik zu folgen und nicht der letzten Quelle die Credits zu geben.
Wie kann man Last-Non-Direct umsetzen? Wenn keine Quelle in der Sitzung ist, müsste man in die Vergangenheit des Nutzers sehen, um dort nach der letzten Quelle zu suchen. Das Kampagnen-Timeout von standardmäßig 6 Monaten in Universal Analytics legte fest, wie weit in die Vergangenheit geblickt werden sollte. Jetzt müsste man für last-non-direct ebenfalls ein Lookback-Window einbauen.
Soll die Last-Non-Direct Quelle bei direkten Sitzungen die Session Source der vorherigen Quelle sein oder wie im GA4 Interface die letzte gesehene Quelle der vorherigen Sitzung. Google hat hierzu eine Dokumentation, die zeigt, wie es berechnet wird: https://support.google.com/analytics/answer/11080067?hl=en#zippy=%2Cin-this-article
Je nach Website kann es aber sinnvoller erscheinen, als last-non-direct die Session Source der vorigen Sitzung mit Quelle zu benutzen.
Wie bindet man ein Channelgrouping in die Transformation ein?
Channel Gruppierungen findet man in den Rohdaten nicht wieder und muss sich über Case When Statements nachbauen. Das kann direkt in der Session Transformation passieren oder als BigQuery Funktion, die nach der Session Transformation angewandt wird. Verändert sich das Grouping eher selten, kann man das Grouping direkt in der Session Transformation einbauen und hat alle Informationen in einer Tabelle.

Eine Funktion hat den Vorteil, dass sie flexibel veränderbar ist und dass sich Änderungen ohne Neuberechnung auf die historischen Daten auswirken. Außerdem kann eine Funktion auch in mehreren Queries genutzt werden.

Wie man die Sessions attribuiert, muss wohl jeder für sich entscheiden. In Projekten haben wir bereits Last Click, Last-Nondirect und auch Linear über die Transformation umgesetzt.

Thomas Langnau

Thomas Langnau ist Senior Consultant Marketing Technology bei mohrstade. Mit seinem Fokus auf die Google Marketing Platform unterstützt er unsere Kunden in allen Fragen rund um Google Analytics 4 und Looker Studio.