Overview
A 'Sessionization - 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 'Sessionization - GA4 export data' 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.
O - Sessionization Step 1
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.
At this step, bot-like users and user_engagement events that may generate unnecessary sessions without being interactive are removed from the sessionization data.
O - Sessionization Step 2
At this stage, we prepare the dimensions of the session level, session attributes, users, and events. Specifically, we create a sessionId and fill the session with events and hits. We also gather information about trafficSource and geoNetwork objects and use it to create the final source, medium, campaign, and other relevant details.
O - Sessionization Step 3 (initial)
At this stage, we select data from the temporary table {{t2_table_id}} and combine it with the data from the main table {{s_table_id}}.
Important: This step should be chosen, if this is the first run of transformation.
O - Sessionization Step 3
At this stage, we select data from the temporary table {{t2_table_id}} and combine it with the data from the main table {{s_table_id}}.
U - Sessionization (LNDC)
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 - Sessionization
Clears all the data in the table {{s_table_id}}.
A - Sessionization Step 4
Transferring data from intermediate tables {{t2_table_id}}, {{t3_table_id}}to the final table {{s_table_id}}.
After this operation, you will get a very convenient table with sessionized events, suitable for further analysis, reporting, and transformations. 🎉
0 Comments