About 'Merge Events into Sessions (GA4 BigQuery Export)' template

Overview

A 'Merge Events into Sessions (GA4 BigQuery Export)' template is used to create a session table, which is constructed using SQL queries (Operations). These Operations are provided in the template and are designed to be easily customizable by inserting the required variables. The actual SQL code of each Operation is automatically generated when the Transformation Template is set up in your OWOX BI Project.

Data source of this template is the data, received using BigQuery Export feature of Google.  

This algorithm has little differences with sessionization based on the OWOX BI webstream data.
Differences are next: 

• Different data sources: for 'Merge Events into Sessions (GA4 BigQuery Export)' template it is the "events_*" table, and for OWOX BI webstream data it is the "events_intraday_*" table. 
• There is no gclid parsing in this template because Google gives already parsed data. 
• Different source / medium parsing: Google could set referral as the event source in some cases, so it should be checked additionally.

Note:Letters at the beginning of the name of each operation define, in all, what this operation does: 
D (DELETE) - operation with the main task of data deletion; 
O (OVERWRITE) - operation, the result of which is overwriting of some table; 
U (UPDATE) - operation, that updates some fields in some table or tables; 
A (APPEND) - operation, that adds new data to the table.

Below, you'll find a handy list of operations in the transformation template, each briefly described for your convenience. However, if you're eager to get started, feel free to jump into the "How to start" article.

O - Create sessions from events

During this stage, we perform the initial selection, transformation, and preparation of data from the `events_*` table. Additionally, we prepare session parameters such as source, medium, campaign, session start, and session end.

O - Make aggregated data

O - Make aggregated data (initial)

At this stage, we prepare the dimensions of the trafficSource field, taking them from temporary table: trafficSource.source,trafficSource.medium,trafficSource.campaign and other.

Important: This step should be chosen, if this is the first run of transformation. 

O - Make aggregated data

At this stage, we select data from the temporary table {{t1_table_id}} and combine it with the data from the main table {{s_table_id}}. 

U - Update sessions by LNDC model

Reproducing the Last Non-Direct Click attribution model. Replacing the fields of the previously generated trafficSource object with the fields of the trafficSource object of the first session, counting from the current session, that does not contain (direct) / (none) values in its source/medium fields.

D - Data cleansing

Clears all the data in the table {{s_table_id}}.

A - Append final data

Transferring data from intermediate tables {{t1_table_id}}, {{t2_table_id}}to the final table {{s_table_id}}.

After this operation, you will get a very convenient table with events, merged into sessions, suitable for further analysis, reporting, and transformations. 🎉