[Starter] Automatically import non-Google ad costs into GA4: fully automated solution – OWOX Help Center

Overview

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 `utm_id`. 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 `utm_id` tag, to get a Match rate of 100% in GA4. Read more
Campaign source	Required	This field contains the value of the tag `utm_source`
Campaign medium	Required	This field contains the value of the tag `utm_medium`
Campaign name	Optional, but recommended	This field contains the value of the tag `utm_campaign`
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.

Required tags: utm_id, utm_source, utm_medium, utm_campaign.

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, involving multiple currencies in your advertising accounts, our 'Openexchangerates.org → Google BigQuery' pipeline offers a straightforward solution. This pipeline automatically imports daily currency exchange rates into Google BigQuery. Therefore, all cost data in your Google Analytics 4 settings is converted to your chosen currency efficiently, streamlining the process and enhancing user convenience.

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.

Solution steps

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.

Step	Task	Interface	Timing
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-day trial subscription (no card required).

2.2. 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:

By clicking on the provided links, you will find step-by-step instructions on how to set up each integration.

Critical important: We recommend using the default dataset, 'OWOXBI_BlendedAdSpend', for importing cost data in all your Pipelines.
If you have specific requirements for the data location in Google BigQuery, such as legal or performance-related considerations, please create a new dataset in the desired region/location and select it during the Pipeline setup.

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.

2.3. Select the appropriate action based on your specific scenario:

Single currency scenario	Multiple currencies scenario
—	If you have costs in multiple currencies, then you need to create the 'Openexchangerates.org → Google BigQuery' pipeline by following these instructions.

👀 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.
Please go ahead and 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.

On the next screen, you will see a variety of prebuilt Templates. For the ‘Import non-Google costs into GA4’ solution, we have prepared two separate templates: one for the single currency case and another for the multiple currencies case.

3.2. Select the appropriate action based on your specific scenario:

Single currency scenario	Multiple currencies scenario
Choose the ‘Blended Adspend + GA4 Cost Data Import’ template and you will get a BigQuery table with the required data format for Google Analytics 4. Click the ‘Create transformation’ button.	Choose the 'Blended AdSpend + GA4 Cost Import (With Currency Convert)' template. Click the ‘Create transformation’ button.

3.3. (Optional) Read this guide to learn how Transformations works. To run Transformations after the trial period ends, contact our manager via bi@owox.com.

3.4. The 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. Select the appropriate action based on your specific scenario:

Single currency scenario	Multiple currencies scenario
Specify values for other variables in the template by following this instruction.	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 bi@owox.com 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 bi@owox.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.