Variables list for transformation template 'Blended Adspend + GA4 Cost Data Import'

Note: The template has all 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

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
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.