About 'Sessionization' template – OWOX Help Center

Overview

A 'Sessionization' 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.

D - GA4 Sessionization

Initial stage – creates a new transformation from the 'Sessionization' template in the OWOX BI interface. Then delete the previous {{ga4s_table_id}} table for dates between {{start_suffix}} and {{end_suffix}}, if it exists.

Important:If this is the first time running the operation and no tables have been collected before, this operation is disabled. You can pause or unpause the operation by following the steps shown in the pictures below:

O - GA4 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 - GA4 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 - GA4 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 - GA4 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

Default variant: U - GA4 Sessionization (gclids from old DT) (waiting to be deprecated)

This operation is disabled by default.
Enable it if Google Ads auto-tagging is used. This will parse 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 Data Transfer files, at this stage, previously tagged sessions for paid traffic with source/medium "google/cpc" are edited according to the Data Transfer tables that store information from Google Ads.

Note: Please, note, that default Data Transfer would be deprecated soon, so we recommend using our pipeline or new Data Transfer.

Alternative variant: 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 new 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.

U - GA4 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.

U - GA4 Sessionization Step 4

Calculating the fields UTM, AttributedAdCost, AttributedAdImpressions, and AttributedAdClicks, updating the fields according to LNDC in the intermediate table {{t2_table_id}}.

A - GA4 Sessionization Step 5

Transferring data from the intermediate table {{t2_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. 🎉