About 'Merge Events into Sessions (App Data)' template


A 'Merge Events into Sessions (App Data)' 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 merging events. The results can be written to the table either as owoxbi_ga4_sessions (if you are performing merging of sessions using GA4 Event Based Streaming data) or export_ga4_sessions (if you are performing merging of sessions 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 - Create sessions from events

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.

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 - 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}}.


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


Please sign in to leave a comment.