This solution is for you if you use advertising services and platforms to promote your products, and you want automated data import into Google Analytics 4 for non-Google ad platforms such as Facebook or Instagram Ads, Bing, LinkedIn Ads, or Twitter. By following the steps below, you will get an 'Acquisition — Non-Google cost' report in the Google Analytics 4 interface. You can use this report to decide if your channels pay off and reallocate your budget by comparing Views, Ad costs, CTR, ROAS, or other metrics across all traffic sources.
If this solution meets your needs then move on. Watch our Webinar with step-by-step instructions and explanations.
Before you start
Prior to implementing the solution steps, it's crucial to address the boundaries and limitations pertaining to the data format required for GA4, currencies in your cost data, and storage where you will merge and prepare all this data.
Data format required GA4
For cost data type, Google Analytics requires the following dimensions and metrics:
|Field name in GA4||Requirement||Description|
|Campaign ID||Required||This field contains the value of the tag
Critical important: In GA4 'Campaign ID' field is used as a key to blending the data. So, we recommend duplicating the 'Campaign Name' value in
|Campaign source||Required||This field contains the value of the tag
|Campaign medium||Required||This field contains the value of the tag
|Campaign name||Optional, but recommended||This field contains the value of the tag
|Date||Required||Date in YYYY-MM-DD format|
|Daily cost||Optional, but expected||Daily value|
|Daily clicks||Optional||Daily value|
|Daily impressions||Optional||Daily value|
You can be sure that you will get these fields with our fully-managed pipelines ‘Data sources → Google BigQuery’. And even more, with just a few clicks, this raw data will be automatically prepared into the correct format with our ready-made ‘Blended Adspend + GA4 Cost Data Import’ transformation template. And the transformation process will continue running daily without any additional manual effort required on your part.
Initial UTM markup for your ads
Critical important: If you have never heard of the
utm_id tag, now is the time to get to know it better! Because without this tag, you won't be able to import costs, impressions, and clicks for non-Google campaigns into Google Analytics 4. Read the article about 'utm_id' in our blog
Check the UTM tagging of your ads so that in GA4 your traffic data is automatically and correctly mapped to the cost data for advertising campaigns.
Once users click on links to your site or app, the values from the UTM tags are sent to Google Analytics 4. Please, make sure that you use the proper UTM codes for all of your advertising campaign links.
One currency or multi currencies
Your advertising costs can be in one or multiple currencies.
Let's consider the first scenario, where you need to work with only one currency. By default, the global currency in Google Analytics 4 property is USD. However, suppose your business transacts in a single currency other than USD. In that case, you can configure a property or reporting view to use any of the supported currencies in the Property Settings or View Settings page. If the same currency is selected in both your advertising accounts and the Google Analytics 4 Property settings, our ‘Blended Adspend + GA4 Cost Data Import’ transformation template will be suitable for you. It will help you prepare the data in the required format with just a few clicks. So, you can move forward to build this solution in the OWOX BI interface.
In the second scenario, if you have multiple currencies in your advertising accounts and one of them is selected in the Google Analytics 4 property settings, an additional step is required for the automatic conversion of cost data to one currency. Please email us at email@example.com, and we will suggest a solution for implementing this step, taking into account the specific features of your business.
Collect and merge all cost data in one data warehouse
As you may have noticed, data needs to be collected and prepared before importing it into Google Analytics 4. We suggest using a convenient data warehouse such as Google BigQuery for this purpose. Why? Google BigQuery offers an efficient, scalable, and cost-effective solution for collecting and analyzing your marketing data, providing real-time insights that can help you optimize your campaigns and drive business growth.
Additionally, to supplement your dataset, you may find it helpful to collect some data in Google Sheets and then integrate it with BigQuery. This can be easily achieved by using OWOX BI products.
With OWOX BI, you do not need to manually prepare CSV files, create your own SFTP server. You will need to set up data collection from different advertising systems, use pre-built templates to transform them into a single structure, and finally set up automated imports into GA4.
|1||Set up BigQuery if you don't already have it||Google Cloud||≈ 2 minutes|
|2||Creating a project in OWOX BI and setting up an automatic daily cost data collection from all your Advertising services and accounts to BigQuery||OWOX BI Pipeline||≈ 5-10 minutes|
|3||Automate the process of merging daily cost data, converting it to the required format||OWOX BI Transformation with Templates Gallery||≈ 5-10 minutes|
|4||Set up a new Data source in the Google Analytics 4 interface. And in parallel, set up a new pipeline 'Google BigQuery → Google Analytics 4 (via SFTP)' in OWOX BI||OWOX BI Pipeline and Google Analytics 4||≈ 5-10 minutes|
|5||Check the 'Acquisition — Non-Google cost' report in Google Analytics. And then, in OWOX BI Workspace you can review this solution to easily control and troubleshoot any issues that may arise||Google Analytics 4 and OWOX BI Workspace|
Note: If you have already collected cost data in the BigQuery table in the required format, then you can skip the 1, 2, and 3 steps and jump to step 4.
Step 1. Set up Google BigQuery
If you don't use BigQuery, follow this instruction to create a Google Cloud Platform project and grant the necessary permissions (BigQuery Admin or BigQuery Data Owner role) to the individuals who will work with your data in OWOX BI.
👀 How to check the result: If all is done correctly, the user’s emails will appear in the list of Principals on the IAM & Admin page. Proceed to the next step.
Step 2. Set up cost data collection to BigQuery with OWOX BI Pipelines
2.1. Go to bi.owox.com, sign in with your Google account, and create your OWOX BI project with a free 7-days trial subscription (no card required).
2.2. To create and share an 'OWOXBI_BlendedAdSpend' dataset quickly and easily, please follow these instructions. We recommend collecting data in this dataset for speedy setup in the following steps and using it in all subsequent setups.
2.3. On the Workspace page, create as many Pipelines as you need from each Ad account in Ads platforms to import cost data daily into Google BigQuery. List of most popular integrations:
- Facebook Ads & Instagram Ads → Google BigQuery
- LinkedIn Ads → Google BigQuery
- Bing Ads → Google BigQuery
- Twitter Ads → Google BigQuery
- Criteo → Google BigQuery
By clicking on the provided links, you will find step-by-step instructions on how to set up each integration. When setting up, pay attention to two important points:
- Specify the 'OWOXBI_BlendedAdSpend' dataset you created earlier as a destination for each pipeline.
- You can set the start date of the initial data import for a maximum of up to 2 months. The cost data will be uploaded to BigQuery on the next day at the scheduled time.
👀 How to check the result:
- In the OWOX BI interface (on the Workspace page), you will have a diagram with your pipelines and destination datasets/tables.
- On the next day, in the BigQuery interface, you will get a bunch of tables with raw cost data.
Proceed to the next step.
Step 3. Merge cost data and prepare it in the required format
3.1. Open the Workspace page, click on the ‘New’ button, and select the ‘Transformation’ item.
3.2. On the next screen, you will see a bunch of prebuilt Templates. For the ‘Import non-Google costs into GA4’ solution, we prepare a ‘Blended Adspend + GA4 Cost Data Import’ template.
Choose this template and you will get a BigQuery table with the required data format for Google Analytics 4. Click the ‘Create transformation’ button.
3.4. The ‘Blended Adspend + GA4 Cost Data Import’ template is ready to transform the data stored in the ‘OWOXBI_BlendedAdSpend’ dataset (which is our recommendation in the 2.2 steps of this solution). If your data is collected in other datasets, please share it with OWOX BI by following this instruction.
3.5. Specify values for other variables in the template by following this instruction.
3.6. Run the Transformation manually. Click on the 'Run' button to start the sequential execution of the Operations in this Transformation.
3.7. If the Transformation run is completed successfully, on the ‘Run history‘ tab, you will see the status ‘Done’. If the status is ‘Failed’, please read the error message and try to fix your Transformation. Contact firstname.lastname@example.org if you have questions.
3.8. Open the ‘Triggers’ tab and add a Time trigger by following this instruction. We recommend selecting the 'Start everyday' option and specifying the hours and minutes between 8 AM and 9 AM.
3.9. (Optional) If you have additional costs collected in another Google Sheet document, then follow this guide to use it in your Transformation as a new BigQuery table. Contact email@example.com if you have questions.
👀 How to check the result: In the BigQuery interface, reload the page, and you will get the ‘GA4_CostData_Import’ table with cost data prepared for Google Analytics 4. Proceed to the next step.
Step 4. Set up data import into Google Analytics 4
4.1. In an adjacent browser tab, open the Google Analytics 4 interface, and go to the Admin page. In the Property column, click Data Import, and then create a new Data source. You will set up a new Data source in parallel with the OWOX BI pipeline (in step 4.2).
Please, note that we recommend you schedule the import between 9 AM and 10 AM every day. This way, you will receive daily updates of cost data in your GA4 reports.
4.2. In the OWOX BI interface, create a new pipeline ‘Google BigQuery → Google Analytics (via SFTP)’, following this instruction. Choose your new ‘GA4_CostData_Import’ BigQuery table as Source data.
👀 How to check the result:
- On the Workspace page, you will see your new 'Google BigQuery → Google Analytics 4 (via SFTP)' pipeline.
- In the Google Analytics 4 interface, you will find details about your new Data source.
Proceed to the next step.
Step 5. Final
If all steps are completed correctly (including setting up
utm_id), then you will be able to see the ‘Reports — Acquisition — Non-Google cost’ report in the Google Analytics interface, containing ‘Non-Google clicks’, ‘Non-Google costs’, ‘Non-Google impressions’, and other metrics.
You can also create a custom report in the Explore section and add campaign parameters as well as cost, impression, click, cost-per-click, and return on spend metrics that are not related to Google.
Take your time to analyze the data and gain valuable insights.
Solution on the Workspace
With our brand-new OWOX BI Workspace, you will get access to a comprehensive overview of your solution, allowing you to easily control and troubleshoot any issues that may arise. Moreover, the diagram view presents an interactive data flow visualization that you can expand by adding new Pipelines, Streaming, and Transformations with just a few clicks using our Templates gallery.