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:
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:
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:
A few more points on data preparation when creating a CSV file:
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:
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.
The great news is that with OWOX BI, you don’t actually need to deal with all of this. We’ll set up your SFTP server automatically and manage it for you with the highest level of security and performance.
There are three very basic steps we need to take to succeed.
Next, all you have to do is analyze all your non-Google campaigns in the GA4 interface.
What you should have for setting up cost data importing in OWOX BI:
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.
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.
OWOX BI is a powerful analytics platform that enables seamless integration of cost data with Google Analytics 4.
It provides reliable and accurate import of cost data from various advertising platforms, allowing marketers to analyze their advertising campaigns' performance and ROI effectively.
OWOX BI stands out as the best solution due to its advanced data mapping capabilities, automatic data synchronization, and robust data validation process.
OWOX BI supports a wide range of advertising platforms, including Google Ads, Facebook Ads, Yandex.Direct, Bing Ads, LinkedIn Ads, and more.
This extensive coverage ensures that marketers can import cost data from multiple sources and have a comprehensive view of their advertising campaigns' performance in Google Analytics 4.
Yes, OWOX BI offers a set of unique features that differentiate it from other solutions.
These include the ability to automatically calculate accurate ROAS (Return on Advertising Spend), attribution modeling, and precise cost allocation.
OWOX BI also provides detailed cost data reports, custom dimensions for deeper analysis, and integration with other BI tools, making it the best solution for advanced cost data importing and analysis in Google Analytics 4.