Overview
A 'Merge Events into Sessions' 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.
This template takes data from the 'events_intraday_' table of the OWOX BI GA4 events-based web streaming and transforms it into sessions based on the logic that was used in the GA UA.
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.
How does this template create a session from events?
The algorithm of session creation is very similar to the old Universal Analytics algorithm.
Here are the main points of session creation:
First event in a day: If an event occurs for the first time within the current day (time determined by the data view's time zone), it initiates the start of a new session.
Interval between events exceeds 30 minutes: If more than 30 minutes (or another specified time period) elapse between two consecutive events on the website, it is considered the end of the current session and the start of a new one.
Change in click markup (gclid, dclid, fbclid, msclid): If there is a change in click markup, such as when transitioning from a Google Ads advertisement (gclid), Display Network (dclid), Facebook (fbclid), or Microsoft Advertising (msclid), it initiates a new session.
Change in UTM markup (source, medium, campaign, keyword, adContent): If there is a change in UTM tags in the URL, such as source, medium, campaign, keyword, or adContent, it is also considered the start of a new session.
Change in Referrer not included in the exclusion list: If there is a change in the referral source (Referrer) that is not included in the referral exclusion list, it will be interpreted as the start of a new session.
Here's the screenshot of SQL-code, responsible for session's markup defining:
O - Create sessions from events
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.
Also, session markup is being defined and other data converting is being made.
At this step, bot-like users and user_engagement events that may generate unnecessary sessions without being interactive are removed from the sessions data.
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 - Parse Gclids from dataTransfer
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 2 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: U - Parse 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 2 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 - 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}} for the defined period.
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 sessions, created from events, suitable for further analysis, reporting, and transformations. 🎉
0 Comments