Variables list for transformation template 'Blended AdSpend + GA4 Cost Import (With Currency Convert)' – OWOX Help Center

Note: The template has most of the essential variables by default, so there is no need to enter all the values from the beginning.

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

Ads Data Pipelines

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.

Specify the project, dataset, and table where data from pipelines from ad services to Google BigQuery is located.

Variable name	Type	Description	Example of value
`{{gcp_project_id}}`	STRING	The project ID where the data tables are located for which the current transformation is configured.	cute-gbq-project
`{{dataset_id_fb}}`	STRING	The dataset ID where Facebook data is located. We recommend to use unified dataset OWOXBI_BlendedAdSpend	OWOXBI_BlendedAdSpend
`{{table_id_fb}}`	STRING	The table ID where Facebook data is located. It is constant value for OWOX BI Facebook → Google BigQuery pipeline: facebook_AdCostData	facebook_AdCostData
`{{dataset_id_criteo}}`	STRING	The dataset ID where Criteo data is located. We recommend to use unified dataset OWOXBI_BlendedAdSpend	OWOXBI_BlendedAdSpend
`{{table_id_criteo}}`	STRING	The table ID where Criteo data is located. It is constant value for OWOX BI Criteo → Google BigQuery pipeline: criteo_OWOXAdCostData	criteo_OWOXAdCostData
`{{dataset_id_bing}}`	STRING	The dataset ID where Bing data is located. We recommend to use unified dataset OWOXBI_BlendedAdSpend	OWOXBI_BlendedAdSpend
`{{table_id_bing}}`	STRING	The table ID where Bing data is located. It is constant value for OWOX BI Bing → Google BigQuery pipeline: bing_OWOXAdCostData	bing_OWOXAdCostData
`{{dataset_id_twitter}}`	STRING	The dataset ID where Twitter data is located. We recommend to use unified dataset OWOXBI_BlendedAdSpend	OWOXBI_BlendedAdSpend
`{{table_id_twitter}}`	STRING	The table ID where Twitter data is located. It is constant value for OWOX BI Twitter → Google BigQuery pipeline: twitter_OWOXAdCostData	twitter_OWOXAdCostData
`{{dataset_id_linkedIn}}`	STRING	The dataset ID where LinkedIn data is located. We recommend to use unified dataset OWOXBI_BlendedAdSpend	OWOXBI_BlendedAdSpend
`{{table_id_linkedIn}}`	STRING	The table ID where LinkedIn data is located. It is constant value for OWOX BI LinkedIn → Google BigQuery pipeline: linkedin_AdCostData	linkedin_AdCostData
`{{dataset_id_adroll}}`	STRING	The dataset ID where AdRoll data is located. We recommend to use unified dataset OWOXBI_BlendedAdSpend	OWOXBI_BlendedAdSpend
`{{table_id_adroll}}`	STRING	The table ID where AdRoll data is located. It is constant value for OWOX BI AdRoll → Google BigQuery pipeline: adroll_OWOXAdCostData	adroll_OWOXAdCostData
`{{dataset_id_tiktok}}`	STRING	The dataset ID where TikTok data is located. We recommend to create separated dataset, due to the amount of tables in this dataset.	adroll_OWOXAdCostData
`{{dataset_id_hotline}}`	STRING	The dataset ID where Hotline data is located. We recommend to use unified dataset OWOXBI_BlendedAdSpend	OWOXBI_BlendedAdSpend
`{{table_id_hotline}}`	STRING	The table ID where Hotline data is located. It is constant value for OWOX BI Hotline → Google BigQuery pipeline: hotline_OWOXAdCostData	hotline_OWOXAdCostData
`{{dataset_id_google}}`	STRING	The dataset ID where Google Ads Data Transfer data is collected. In case of using Google Ads → Google BigQuery pipeline, the value should be a dataset where pipeline data is collected.	GoogleAds

Custom Table

Specify the dataset and table with custom cost data.

Variable name	Type	Description	Example of value
`{{dataset_id_custom}}`	STRING	The dataset ID where custom data is located. We recommend to use unified dataset OWOXBI_BlendedAdSpend	OWOXBI_BlendedAdSpend
`{{table_id_custom}}`	STRING	The table where custom data is located	custom_data

OpenExchangeRate table

Specify the dataset and a table with exchange rates data.

Variable name	Type	Description	Example of value
`{{dataset_id_currency}}`	STRING	The dataset ID where the result of Openexchangerate.org → Google BigQuery pipeline is located.	Openexchangerates
`{{table_id_currency}}`	STRING	The table where exchange rates are located. It is a constant value for OWOX BI Openexchangerate.org → Google BigQuery pipeline: exchange_rates	exchange_rates
`{{convert_currency}}`	STRING	The currency to which all the convertations would be made.	USD

Transformation results

Specify where to save transformation results.

Variable name	Type	Description	Example of value
`{{dataset_id_ga4_import}}`	STRING	The dataset ID where the prepared for import to GA4 table is located. Place, where results of transformation would be saved. We recommend to use unified dataset OWOXBI_BlendedAdSpend	OWOXBI_BlendedAdSpend
`{{table_id_ga4_import}}`	STRING	The table ID where prepared for import to GA4 data is located. It is constant value: GA4_CostData_Import	GA4_CostData_Import
`{{dataset_id_blended}}`	STRING	The dataset ID where the blended cost data from ad services is located. Place, where results of transformation would be saved. We recommend to use unified dataset OWOXBI_BlendedAdSpend	OWOXBI_BlendedAdSpend
`{{table_id_blended}}`	STRING	The table ID where where the blended cost data from ad services is located. It is constant value: BlendedAdSpend	BlendedAdSpend