About 'Blended AdSpend + GA4 Cost Import (With Currency Convert)' template

Overview

A ‘Blended AdSpend + GA4 Cost Import (With Currency Convert)’ 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, even if different sources use different currencies. 

This Transformation is available for:

Note: If all of your advertisement systems and calculations use the same currency, please, use the 'Blended AdSpend + GA4 Cost Data Import' template for one currency.

All costs received as a result of Blended Adspend are exclusive of taxes. Learn more about taxes in Pipeline in this article.

  • 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.  
  • You created OpenExchangeRates — Google BigQuery pipeline, and you have a table with currency rates for the needed period. 

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 most of 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;
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 Import (With Currency Convert)’’ 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 – Google By Request

The operation selects information from tables generated in Google BigQuery after setting up by-request Google Ads → Google BigQuery pipeline. You can create this pipeline by following the provided instructions. If you have questions regarding pipelines by-request, you can contact us via email at bi@owox.com.

A – Google 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: google_ads_data_operations.png google_ads_data_operations_.png

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 Import (With Currency Convert)’ 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:

manual_costs.png

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:

custom_data.png

Step 5. Don't forget to enable operation A — Custom in the transformation:

custom.png

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: operations.png

After the last operation, you will get tables with data ready for further analysis and reporting 🎉

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.