Overview
A ‘Blended Adspend + GA4 Cost Data Import’ template creates a BlendedAdSpend table and a GA4_CostData_Import table. You’ll get all cost data in an automatically updated, unified, and user-friendly table without unnecessary fields.
This Transformation is available for:
- OWOX BI fully-managed pipelines from Ad services (Facebook, Bing, Criteo, LinkedIn, Twitter) to Google BigQuery
- Google Ads data from OWOX BI fully managed pipeline or Data Transfer
- custom costs via OWOX BI BigQuery Reports Extension.
All costs received as a result of Blended Adspend are exclusive of taxes. Learn more about taxes in Pipeline in this article.
Getting started requirements
- You created a unified dataset OWOXBI_BlendedAdSpend as a destination for pipelines and custom data. The link provides instructions about creating and sharing datasets with OWOX BI.
Note: Otherwise, you must modify the default variables in the transformation template and replace the existing dataset variable names OWOXBI_BlendedAdSpend with the ones specific to your dataset.
- You created Ad Services — Google BigQuery pipelines in the OWOX BI interface and already have tables with cost data from ad services in your OWOXBI_BlendedAdSpend dataset. Follow these instructions to create fully-managed Ad Services — Google BigQuery pipelines.
Transformation process
The transformation process inputs the raw data stored in the OWOX BI pipelines’ tables. The output is a table BlendedAdSpend with cost data in the following structure and a table GA4_CostData_Import which contains prepared data in a specific structure.
Operations are provided in the template and are designed to be easily customizable by inserting the required variables. The template has all the essential variables by default, so there is no need to enter all the values from the beginning:
The actual SQL code of each Operation is automatically generated when the Transformation Template is set up in your OWOX BI Project.
Note: Letters at the beginning of the name of each operation define, in general, what this operation does:
D (DELETE) – an operation with the main task of data deletion;
O (OVERWRITE) – an operation the result of which is overwriting of some table;
U (UPDATE) – an operation that updates some fields in some table or tables;
A (APPEND) – an 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.
D – Data cleansing
Initial stage – creates a new transformation from the ‘Blended Adspend + GA4 Cost Data Import’’ template in the OWOX BI interface. Then delete the previous {{table_id_blended}}
table for dates between {{start_suffix}}
and {{end_suffix}}
, if it exists.
A – Facebook
The operation selects data from the table facebook_AdCostData generated in Google BigQuery after setting up Facebook → Google BigQuery pipeline. You can create this pipeline by following the provided instructions.
A – Bing
The operation selects data from the table bing_OWOXAdCostData generated in Google BigQuery after setting up Bing → Google BigQuery pipeline. You can create this pipeline by following the provided instructions.
A – Criteo
The operation selects data from the table criteo_OWOXAdCostData generated in Google BigQuery after setting up Criteo → Google BigQuery pipeline. You can create this pipeline by following the provided instructions.
A – Twitter
The operation selects data from the table twitter_OWOXAdCostData generated in Google BigQuery after setting up Twitter → Google BigQuery pipeline. You can create this pipeline by following the provided instructions.
A – LinkedIn
The operation selects data from the table linkedin_AdCostData generated in Google BigQuery after setting up LinkedIn → Google BigQuery pipeline. You can create this pipeline by following the provided instructions.
A – AdRoll
The operation selects data from the table adroll_OWOXAdCostData generated in Google BigQuery after setting up AdRoll → Google BigQuery pipeline. You can create this pipeline by following the provided instructions.
A – Hotline
The operation selects data from the table hotline_OWOXAdCostData generated in Google BigQuery after setting up Hotline → Google BigQuery pipeline. You can create this pipeline by following the provided instructions.
A – TikTok
The operation selects data from several tables, made by TikTok → Google BigQuery pipeline and generates a temporary tiktok_OWOXAdCostData table. You can create this pipeline by following the provided instructions.
A – GoogleAds by OWOX
The operation selects information from tables generated in Google BigQuery after setting the Google Ads → Google BigQuery pipeline. You can create this pipeline by following the provided instructions.
A – GoogleAds DataTransfer
The operation selects data from tables generated in Google BigQuery after setting up automatic data transfer from Google Ads using BigQuery Data Transfer Service. It’s a native integration.
IMPORTANT: If you are planning to add this type of Google Ads export, please, set the refresh window(p. 8-e) with the same value, as the period of Blended AdSpend table refreshing. If there is a refresh window set to 7 days, it should be the same in DataTransfer, to avoid data discrepancies.
The default value of the refresh window is set to one(1) day.
Please, follow our instructions to import Google Ads Data into Google BigQuery.
IMPORTANT: You'll need to use only one operation of the above for Google Ads Data. If you have both sources from Google Ads, please, turn on 'Ignore for running' for one of the operations to prevent duplicating the data in the output tables. You can pause or unpause the operation by following the steps in the pictures below:
A – Custom
The operation selects data from the custom table generated in Google BigQuery. You can import your own cost data from Google Sheets to the Google BigQuery and use it in ‘Blended Adspend + GA4 Cost Data Import’ template.
Step 1. You can find the Google Sheets template for manual adding costs by the link. Please, copy the template to your Google Drive:
Step 2. Fill the values in the template with your cost data.
- Please, note, that date ranges should be filled with date_start and date_end values:
- In rows 1-3 of the template, there is an example of filling in data by days.
- In row 4 of the template, there is an example of filling in data for a month.
- In row 5 of the template, there is an example of filling in data for 2 months.
- In row 6 of the template, there is an example of filling in data for 1 week.
Step 3. Follow the instructions for using the Google Sheets document as a data source for the BigQuery table.
Step 4. Change the variable {{table_id_custom}}
in the 'Blended Adspend + GA4 Cost Data Import' transformation template to the table's name. For example, if the table has the name 'cute-gbq-project.OWOXBI_BlendedAdSpend.custom_data', specify 'custom_data' in the variable:
Step 5. Don't forget to enable operation A — Custom in the transformation:
If you have questions about importing custom data to Google BigQuery, you can contact us via email at bi@owox.com.
O – GA4 CostData Import
The operation prepares data for import to Google Analytics 4 and creates the table GA4_CostData_Import. Please, use this table during the setup of the Google BigQuery → Google Analytics 4 (via SFTP) pipeline if you want to import cost data to Google Analytics 4.
Need help with this? Read Import non-Google costs into Google Analytics 4 solution.
IMPORTANT: Please, disable all unnecessary operations. You must select only operations for existing pipelines in your OWOX BI project. For example, if you have Facebook → Google BigQuery pipeline and LinkedIn → Google BigQuery pipeline, the first run of transformation must look like in the screenshot below:
After the last operation, you will get tables with data ready for further analysis and reporting 🎉
0 Comments