Overview
A 'Sessionization - OWOX BI Events Streaming' 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.
The transformation process utilizes the raw data stored in the `events_intraday_*` table, generated by OWOX BI Streaming, as its input. The output is a table with session data in the following structure. The session collection algorithm is similar to the standard GA UA algorithm.
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_intraday_*` 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 - gclid processing
Gclids from Google Ads by OWOX
This operation is disabled by default.
When using Google Ads auto-tagging, you need to use additional data saved in the "gclid" field in events (check our article on auto-tagging for more details).
Autotagging priority contains two values. 0 — manual tagging is given priority when collecting session data. 1 — auto-tagging (gclid) is given priority when collecting session data. In this case, you must have the value of the variable {{autotag_priority}}set to 1.
If you are using the OWOX BI Google Ads -> Google BigQuery pipeline, at this stage, previously tagged sessions for paid traffic with source/medium "google/cpc" are edited. In this case, the Google Ads -> Google BigQuery pipeline table is used as the source data.
Note: We recommend running only one instance of Step 4 to optimize the use of credits. In this case, the first processing of gclid data will be performed correctly, and the second processing will not change anything.
Alternative variant: U - GA4 Sessionization (gclids from DT)
Autotagging priority contains two values. 0 — manual tagging is given priority when collecting session data. 1 — auto-tagging (gclid) is given priority when collecting session data. You must have the value of the variable {{autotag_priority}}set to 1.
If you are using Data Transfer files, at this stage, previously tagged sessions for paid traffic with source/medium "google/cpc" are edited. In this case, the new Data Transfer tables that store information from Google Ads would be used as source date.
Note: We recommend running only one instance of Step 4 to optimize the use of credits. In this case, the first processing of gclid data will be performed correctly, and the second processing will not change anything.
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