Variables list for transformation template 'Merge Events into Sessions'

Period

Specify the period for which you want to perform the transformation.

Variable name Type Description Example of value
{{start_suffix}} STRING

The start variable is responsible for the period's start date for which transformation should be performed. Example value is an SQL code that takes one day less than the current date and turns it into a string that is understandable for Google BigQuery.

In other words, this is the beginning of the period during which the transformation needs to be performed.

We recommend using a data refresh interval of 1 day to save resources, but you can use any interval you prefer.

Additionally, instead of SQL code, you can specify a specific date in the date format here, for example, CAST ('20230101' AS STRING).

FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY))
{{end_suffix}} STRING

The end variable is responsible for the period's end date for which transformation should be performed. Example value is an SQL code that takes one day less than the current date and turns it into a string that is understandable for Google BigQuery. Thus, this transformation would take dates between start_suffix and end_suffix.

This is the end of the period during which the transformation needs to be performed. We recommend using a data refresh interval of 1 day to save resources, but you can use any interval you prefer. Additionally, instead of SQL code, you can specify an end date in the date format, for example, CAST ('20230101' AS STRING).

FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

 

OWOX GA4 streaming table

Specify the parameters of the OWOX GA4 streaming table. In the Table ID, specify the name before the underscore at the end, and without a suffix β€” calculated suffixes will be attached to this name in the future.

Important: Using this template assumes that all your data is located within one GCP (Google Cloud Platform) project.
If you store data in more than one project, please contact us via email at bi@owox.com before proceeding with the setup.

Variable name Type Description Example of value
{{gcp_project_id}} STRING

The project ID where the events table is located for which the current transformation is configured.

cute-gbq-project
{{ga4s_dataset_id}} STRING

The dataset ID where the events table is located for which the current transformation is configured.

OWOXBI_GA4_Streaming
{{ga4s_table_id}} STRING

ID of your table with events. Has a standard value: events_intraday_

events_intraday_

 

Additional parameters

Specify additional parameters used for merging events into the sessions. 

Variable name Type Description Example of value
{{s_timeout}} STRING

Session timeout is the duration after which one session is interrupted and the next begins. Specified in minutes.

The default session expiration time in Google Analytics is 30 minutes. However, you can specify any time depending on your website settings.

30
{{c_timeout}} STRING

The waiting time for the campaign in days. When preparing the LNDC model, the specified number of days ago will be checked to determine the user's last indirect source.

90
{{timezone}} STRING

Timezone in which sessions will be collected. Specified for:

  1. Session interruption at midnight.
  2. Correct determination of the time when the session was performed.

List of timezones

Europe/Kiev
{{autotag_priority}} STRING

Autotagging priority. Contains two values. 0 - manual tagging is given priority when collecting session data. 1 - autotagging (gclid) is given priority when collecting session data.

More details about this parameter can be found in our article on auto-tagging.

1
{{r_exclusion_list}} STRING

Referral exclusion list.A list of domains that, when transitioned from, will not start a new session and users will not be counted as referral traffic.

Please, notice, that at the end of the exclusion list must not be any symbols, such as "-", "|" etc. 

yourwebsite.com|
onemoreyourwebsite.com|
anothersite.com|verygoodsite.com
{{pr_identity}} STRING

User ID of the website visitor. It can take one of two values:

  • user_pseudo_id β€” a generated by GA4 alias identifier intended for the user, such as an application instance identifier.
  • owox.user_id β€” more details in our article.
user_pseudo_id
{{gclid_window}} STRING

This refers to how many days ago the operation searched for gclid in Data Transfer tables / By Request pipeline.

There are cases where a user clicks on an advertisement and saves the link. Then, after a few days, he revisit by the saved link. However, since the visit is technically through the link and not through the advertisement, the corresponding gclid is not present in dataTransfer, and it needs to be searched for in the history.

7

 

 

DataTransfer table

Specify the parameters of the DataTransfer table for parsing gclid. In the Table ID, specify the identifier present at the end of the names of all tables in the DataTransfer dataset.

Variable name Type Description Example of value
{{dt_dataset_id}} STRING

ID of dataset, in which Adwords Data Transfer data is collected.

This path is good for both new and old Data Transfer. In case of using Google Ads -> Google Big Query pipeline, the value should be a dataset where pipeline data is collected. 

AdwordsTransfer

 

Intermediate tables

Specify the names of the intermediate tables used for merging events into the sessions.

Variable name Type Description Example of value
t1_table_id STRING

Intermediate temporary table with event data. This table will be created automatically, choose a descriptive name to make sure the purpose of this table is clear in the future.

temporary_events
t2_table_id STRING

Intermediate temporary table with event data. This table will be created automatically, choose a descriptive name to make sure the purpose of this table is clear in the future.

temporary_ga4_sessions

 
Results of merging events

Specify where to save merging results.

Variable name Type Description Example of value
{{s_dataset_id}} STRING

The dataset ID where the session table is located for which the current transformation is configured.

Place, in which results of merging events would be saved.

OWOXBI_GA4_Streaming
{{s_table_id}} STRING

The ID of the table with sessions. Place in which results of merging events would be saved. We recommend a standard value: owoxbi_ga4_sessions_

owoxbi_ga4_sessions

 

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

0 Comments

Please sign in to leave a comment.