Why OWOX BI is the best solution for importing cost data to Google Analytics 4

Over the last 7+ years, thousands of businesses have used OWOX BI for importing their non-Google advertising cost data to Google Analytics Universal.

Now that Google Analytics Universal is scheduled for sunsetting on July 1, 2023, we are excited to show you the solution we’ve developed to automate the importing of non-Google ad cost data to Google Analytics 4 (GA4).

We want to import non-Google ad costs to GA4 to be able to analyze all our marketing efforts in one place! Why? To make better decisions that lead our business to higher profit and, to be frank, to feel better and smarter 😉. And we don’t want to do any monkey work on a daily basis to make it happen.

We want to be able to see this report in the GA4 interface (which most marketers and advertising specialists are familiar with):

And we want to access the insightful metrics that Google calculates based on both current and imported data:

• Return on non-Google ad spend
• Non-Google cost per click
• Non-Google cost per conversion

Also, we want to be able to build our own custom reports using this flexible constructor in the Explorations section of GA4:

Imagine that all your data on user behavior in GA Universal is in a single flat table. This table helps you understand where your users are coming from and how many users visit your platform.

But you also want to know how much it costs you to attract these users, what your ROI is, and so on.

Google Analytics doesn’t know anything about this data, so you need to import it separately.

In order to join these different kinds of data, you need a key. UTM (source, medium, campaign) plus date is the surrogate key that allows for blending data correctly.

In this example, you can see that only matching values are joined. This is how it works in good old GA Universal:

There is a small change in Google Analytics 4 that has a huge impact. Campaign ID has become a required field when importing cost data into GA4.

This is a screenshot from the official GA4 documentation:

What’s the problem? Even if you have a lot of data from your users in a particular period of time, here is what you can see in the GA4 interface if utm_id was not set for your campaigns.

You can see for yourself in the non-Google costs section of GA4 by choosing “Session campaign ID” as the dimension for your report. The result will be the same as on this slide.

Those of you who have already tried to import data into GA4 yourself have probably encountered this error: “Invalid empty value detected on row campaign_id. A non-empty value must be set.”

You may fill campaign_id in a CSV file with some random value to get through this error.

However, you will get a 0.0% Match rate.

This means you won’t get any non-Google campaigns appearing in GA4. Why? Because nothing has been matched.

Remember how this works in GA Universal? Well, in a simplified way, this is how it looks in GA4. There is behavioral data inside GA4 and cost data outside GA4.

Pay attention: Only matching data from the first row is joining the data. And notice that campaign_id must not be empty and also should match the data in the CSV file.

We strongly recommend you start adding utm_id in all your paid campaigns now. This will ensure availability of campaign data in GA4, which is required for importing cost data!

As a very universal approach, we recommend setting the campaignName to campaign ID (just duplication), as we are 100% sure how to retrieve this data from ad platform APIs.

To blend data outside GA4 with data inside GA4, we need a key for matching. And this key is different from how that works in old GA Universal.

utm_id is the missing gear that enables the matching of non-Google ad data outside GA4 with behavioral data that is already in GA4.

Here is what we get in GA4 Data import as a result if we add utm_id to our tracking in ad platforms — a 100% Match rate!

Now is a great moment to dive deep into the Data import functionality in the GA4 Admin panel and figure this out.

There are two options:

• Manually upload a CSV file
• Semi automatically data upload via an SFTP server

We are not going to focus here on the manual way. It’s quite simple, and we’ve already discussed the main trick for manual importing — utm_id.

The main challenge with manual importing is that every time you need a report, you must spend 30+ minutes to create it.

Let’s focus on a basic task we should handle whether uploading manually or automatically.

When it comes to preparing a CSV file, here is the list of things to handle:

1. Download an example of a CSV file with the predefined structure.
2. Find your ad data on different platforms (Facebook Ads, Bing Ads, Twitter Ads, LinkedIn Ads, etc.).
3. Gather all the data in one place. Note that all datasets have a different structure and data types.
4. Figure out how to clean the data, perform deduplication, and so on. In other words, transform all that data into one data structure that fits GA4 requirements.

A few more points on data preparation when creating a CSV file:

• You should prepare the data for a period of not more than 90 days.
• You should not use keyword- or adContent-level data.
• All costs should be in one currency (the same currency as in the GA4 property).
• There should be no empty fields.
• There should be no costs from Google services.

Secure File Transfer Protocol, or SFTP, is a network protocol that provides file access and file transfer over any reliable data stream. It’s an old but reliable technology that comes to us from the 1990s

We will not cover the process of creating your own SFTP server in this article.

You can find a bunch of great, very detailed articles on that topic on the web. Also, we provide some useful links in the article on our blog on how to import non-Google ad cost data.

Instead, we want to cover a more important topic.

Managing your own SFTP server for importing non-Google cost data to GA4 may not be the best option for you due to:

• Lack of technical expertise
• Lack of time and resource to invest
• Hidden total cost of ownership (TCO)

When deciding whether to set up a self-managed SFTP server, it’s essential to assess the total cost of ownership.

The initial costs of establishing a self-managed SFTP server on public cloud providers like Google Cloud, AWS, and Azure may seem low (potentially less than $50 per month), but it’s critical to recognize the hidden expenses associated with ongoing maintenance.

The real hidden costs of running a self-managed SFTP server are the time and effort required from the IT team to manage, monitor, and troubleshoot it.

There are three very basic steps we need to take to succeed.

1. Collect all data in one place (OWOX BI Pipeline will get this job done).
2. Transform the data into the needed format (OWOX BI Transformation will get this job done).
3. Make the transformed data available via SFTP as a CSV file (OWOX BI Pipeline will get this job done).

Next, all you have to do is analyze all your non-Google campaigns in the GA4 interface.

1. Alienability of the solution parts — no vendor lock. For example, just imagine that Google creates a native integration that allows you to load costs in GA4 from the GBQ table, or you want to manage your SFTP server on your own. Either way, you’ll still need to collect data and bring it into the required structure, so you can use only this part of our solution and not use the pipeline from BigQuery to the SFTP server.
2. Incredible customization possibility for your business. Yes, OWOX BI is an out-of-the-box solution. At the same time, you can always implement changes and customize data importing and transformation based on your specific business needs. OWOX BI is not a black box. It is easy to make changes taking into account the specifics of your business.
3. Add additional cost data from Google Sheets. If for any reason you are using a data source specific to your domain that is not available in OWOX BI, you can always add missing data by importing it from Google Sheets.
4. Same cost data format and accuracy. OWOX BI processes dynamic parameters and checks existing UTM tags in your campaigns for possible errors. Furthermore, it can even expand the short links in URLs.
5. Historical data and updates. You can download data from the ad service for the last two months and evaluate your past campaigns.
6. Manage the whole data flow in an analyst-friendly Workspace UI. With OWOX BI, you can control data processing and transformation in a convenient web interface with data lineage graphs.

What you should have for setting up cost data importing in OWOX BI:

• Access to your ad accounts on different platforms
• Access to your Google Analytics 4 property with Editor or higher role
• Google BigQuery account (10 minutes to create)
• An OWOX BI project (1 minute to create)

To create a data source in Google Analytics 4, go to Admin panel → Property → Data Import and select SFTP:

Then you’ll need to make sure that mapping is okay. Actually, you just need to tick the checkboxes here, as we’ve already prepared the data structure you need.

Now for the last step: GA4 provides you with a public key, which you should copy–paste into the OWOX BI tool. Then, click Done.

To set up the pipeline for collecting data into your BigQuery project, you need to choose the data source. Then, follow a couple of simple steps to finish setting up the pipeline.

Now you have pipelines that do the routine job of data collection. Here is how pipelines will look in the OWOX BI interface:

By the way, our pipelines also refresh historical data for the last 21 days. It’s a necessary feature that ensures the most accurate and reliable data, even if data is changed at the source.

For instance, platforms like Facebook Ads often change historical data, as they exclude bot traffic, recalculate prices, and so on.

We know how painful and time-consuming it can be to code using SQL to transform data from the raw format you get from ad platforms into the predefined structure required by GA4 — even if you have the needed expertise and experience.

To make your life easier, we have simplified the process. All you need to do is set up data transformation using our pre-built Template.

Simply choose the template → choose ad platforms you set up in pipelines, in the operations → set the path to the tables with source data in variables → set the trigger → SAVE.

That’s it! All the hard work of cleaning the data, understanding the nature of it, and writing complex SQL queries with JOINS, UNIONS etc. is already done in this template. As you can see in the screenshot, SQL code is already set. It is compatible with all data collected via our pipelines.

Once you set it all up, OWOX BI will do the job diligently. In your BigQuery account, there will be a table compatible with GA4 data import requirements. Also, there will be a table with Blended AdSpend — fresh data about all your costs in one structure. It also allows you (if you want) to build advanced reports in BigQuery and visualize them where you want: for example, in Looker Studio (formerly Data Studio).

By the way, data does not leave your BigQuery project for processing, so you have the highest level of security for all your data.

Set up a pipeline that delivers data from a BigQuery table to your SFTP server (remember, it’s automatically deployed and managed by OWOX). This is the SFTP server that Google Analytics 4 will visit repeatedly to pick up a fresh CSV file.

1. Choose Google BigQuery as the Data Source in the first step.
2. Choose Google Analytics 4 (via SFTP) as the data destination.

Then you need to copy–paste the data provided (a public key) into GA4 and pass the mapping fields. As you may remember, we showed you what this looks like in GA4. Now you can see the flip side — how it looks in OWOX BI.

Once everything is done, just click Activate. Then we’ll take care of the rest; you don’t need to do anything else manually.

Further, GA4 visits the server via the SFTP protocol on schedule. At this moment, a CSV file with all the data is already created by OWOX BI. Starting from here, you don’t need to invest any of your valuable time to manage it or even monitor it. OWOX BI automatically does that for you on a daily basis.

In addition, OWOX BI has an analyst-friendly Workspace UI. It’s a convenient visual tool that allows you to get a helicopter view and be aware of your data processing.

You can start our 7-day FREE trial right now. For a whole week, you’ll be able to extract data from your advertising services, including Facebook, Bing, and Twitter, seamlessly collect this data in Google BigQuery, play around with our easy-to-use transformation template for Blended Ads spend + GA4, and import all of your non-Google advertising cost data into Google Analytics 4 via SFTP (managed by OWOX).

After the 7-day free trial, you’ll be able to switch to any of our pricing plans or book a demo with our team to see how you can get the most out of our platform.