Variables list for transformation template 'CMO Dashboard Model Based' – OWOX Help Center

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

LinkedIn Data

Specify the parameters of the OWOX linkedin_AdCostData table.

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 ID of your project in Google Big Query, where the BlendedAdSpend table is stored.	cute-gbq-project
`{{l_dataset_id}}`	STRING	The ID of your dataset in Google Big Query, where the linkedin_AdCostData table is stored.	BlendedAdSpend
`{{l_table_id}}`	STRING	ID of the LinkedIn table. Has a standard value: linkedin_AdCostData	linkedin_AdCostData

Path for Ecom Data Model template results

Specify the dataset, where the results of the Ecom Data Model template are stored.

Variable name	Type	Description	Example of value
`{{m_dataset_id}}`	STRING	The dataset ID where the Ecom Data Model template table is located for which the current transformation is configured.	OWOXBI_DataModel

Path for saving results

Specify the path, where you want to save the result.

Variable name	Type	Description	Example of value
`{{r_dataset_id}}`	STRING	The dataset ID where the datamart table would be located.	OWOXBI_Reports
`{{r_table_id}}`	STRING	The ID of the table in which results of current transformations would be saved, and which should be connected to the dashboard.	CMO_dashboard_model_table

Special transaction event statuses

Please, specify statuses of the transaction events.

Variable name	Type	Description	Example of value
`{{s1_event}}`	STRING	The event, that shows start of the transaction.	add_to_cart
`{{s2_event}}`	STRING	Second event before transaction.	begin_checkout
`{{order_refunded}}`	STRING	Event, that shows, that transaction was completed.	Refunded