About 'Sessionization - APP' template

Overview

A 'Sessionization - APP' template is used to additionally fill a session table, which is constructed using SQL queries (Operations), with APP data. 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.

It takes data from the 'events' table of the standard GA4 export and transforms it into sessions based on the logic used in all OWOX BI sessionizations. The results can be written to the table either as owoxbi_ga4_sessions (if you are performing web sessionization using GA4 Event Based Streaming data) or export_ga4_sessions (if you are performing sessionization on GA4 Export data).

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 - 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 - 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 table, depending on your choice, filled with APP sessions data. 🎉

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.