How to calculate your MER in GA4 – Complete guide

Google Analytics 4 (GA4) is the latest generation of Google’s web analytics platform designed to provide comprehensive insights into user behavior across various devices and platforms. As GA4 became the only option with the depreciation of Universal Analytics (UA), many people got confused. What is it made for? Is it a product analytics tool? A marketing analytics tool? A reporting tool? A BigQuery pre-loader? An interface for Ads? I’m sure many of you are having trouble find your good ol’ reports from UA. But let’s be brave, adapt to GA4 and get the most out of it. It has way more to offer than UA.

What is this guide about?

I’m not going to talk about technically complicated solutions. Instead I’m going to show you a very simple method that you can do yourself. This guide is for you if you are a:

  • DTC brand owner who wants to have up to date KPIs
  • CMO who decides in what marketing channel to invest
  • Media buyer who needs to see the big picture of how the whole project it doing

It’s all about ROI (return on ad spend) on the end of the day, isn’t it? Where do you look at when you want to check your overall ROI?

  • Maybe you pull data from different marketing platforms to Google sheets or Looker studio using Supermetrics, Zapier or DataSlayer and calculating the ROI manually
  • Or you look at the dashboards of some fancy expensive analytics tools like Triple Whale, Northbeam…

Have you ever considered doing this in Google Analytics? It’s not so hard to make a report like this in GA4:

All you have to do is upload your cost data from different platforms to GA4. Some of the benefits:

  1. Unified view of marketing performance: By bringing all cost data into one place, you get a more complete picture of your marketing performance across various channels. This can make it easier to compare the effectiveness of different marketing initiatives.
  2. Improved ROI calculations: With cost data integrated, you can ROI directly within GA. This allows you to understand the value you’re getting from different channels at a granular level.
  3. Better budget allocation: Knowing the cost and return of different channels can help you allocate your marketing budget more effectively. You can invest more in channels that are providing a good return and less in those that aren’t.
  4. Enhanced audience insights: When you understand the cost associated with acquiring users from different channels, you can better understand the value of different audience segments. This can inform your targeting and personalization strategies.
  5. Cross-channel attribution: With all cost data in one place, you can better understand the customer journey across multiple touchpoints. This can help you optimize your marketing funnel and improve overall conversion rates.
  6. And the main advantage: it’s free and it can be fully automated.

How to begin?

First of all you can pull Google ads costs to GA4 by linking those two platforms. The data starts to flow after 24 hours after setting up the link. You can set up the connection from the Admin section of GA4 UI. What about costs of other marketing platforms, like Meta or Twitter ads (all non-Google costs)? You can pull them to GA4 with the Data Import feature. You have two options:

For both options you need to have your data in a csv file and the columns that Google wants.

  • Date in YYYY-MM-DD format
  • Dimensions to pair the costs with actual sessions in GA4: Campaign ID, Campaign source, Campaign medium, and Campaign name
  • Metrics: impressions, clicks, cost

When you have the cost data in the csv you can choose manual upload. Upload the csv file and voila. You have all your cost data in GA4. However, there is one major drawback. You have to do this everyday manually. However, if you decide to do it manually and you have some trouble with failing imports or low match rate jump to the end of this guide for help.

I’m a big fan of automation, so I will talk here about how to automate the process. To do it you have to go through a three step process:

  1. Get cost data to csv
  2. Upload the csv to SFTP server (and set up an SFTP server if you don’t have one)
  3. Establish a connection between SFTP server and GA4

Get cost data to csv

The easiest way is to use an external tool like Supermetrics, DataSlayer, Zapier, or other. If you want fresh daily data those tools might be quite expensive. For example Google sheet connectors that include Meta, Pinterest, Twitter, etc. with daily automatic refresh starts at $199 per month. However if you use any of those tools anyway that might be a good option. As an alternative you can download the cost data directly from the APIs of those marketing platforms with a Google Apps script like this.

You can upload only one csv file at a time to GA4, so make sure you have costs from all marketing platforms in the same file.

Upload the csv to SFTP server

First, you will have to create your own server if don’t have one already. Second you have to upload the file as a csv and set up the automation. In order to do this, you need to have an FTP server with SSH security. Many cloud service providers (e.g. Google Cloud Storage, Amazon S3 Object Storage, Microsoft Azure Blob Storage) and hosting companies provide these services.

Maybe you have your own server or you can use any cloud server provider. It’s up to you. We tried a few, but Bluehost came out as a winner.

Second, you have to set up the automatic upload of the csv file with the ad costs. Here are also several options. Apps like Zapier (with EasyFTP integration) or Make are the most user-friendly solution. However they’re not free. For our cost imports we used Keboola instead, which is an ETL (extract-transform-load) app and has a free tier.

Here you can find a step-by-step guide on how we used Bluehost and Keboola to upload cost data to the SFTP server. In any other case you can continue to the next section.

‌Establish a connection between SFTP server and GA4

Now that you have the csv file with cost data on your SFTP server, all you have to do is establishing a connection between GA4 and the server. Go to Admin section and find Data Import. On the end of the setup process you will have to fill in the SFTP server username and SFTP server url. Those can be found at your SFTP provider.

In the next step you have to set up the mapping of columns from the source file to GA4.

Select all the columns, and for each of the columns, set the “Import field” value as it is in the source table.

On the end of the process you will get a public key that enables secure communication between your server and GA4. You will have to authorize this key on your SFTP server.

Testing your data import Now that you have set up a connection, it’s time to test the data import. If you run an import and click on the status you will see two percentage values:

  1. Percent (%) imported – tells whether you have mapped the columns well
  2. Match rate – tells if you have paired the rows to an existing campaign_id in GA4

Important note: Although % imported should be always 100%, the match rate is rarely 100%, and it’s not a problem. GA4 looks 90 days back, so if you imported, let’s say, the last 45 days with 100% correct data, your match rate will still be only around 50%. The second reason for discrepancies is usually in the UTM parameters of some campaigns. If you have any of the mandatory UTM parameters missing, the data won’t be paired. Mandatory UTM parameters are: utm_source, utm_medium, utm_campaign (campaign name) and utm_id (campaign id). For example your Facebook UTMs should look like this: utm_source=facebook&utm_medium=cpc&utm_campaign={{}}&utm_id={{}}

Use the data

This is the best part. Let’s assume you have all your Google and non-Google cost data in GA4. Now it’s up to you how you leverage that data. You can create Explorations right away in the GA4 interface to see whether your marketing channels and campaigns are doing well or not.

Or you can connect GA4 with Looker Studio and create custom reports like these. All the data is now in GA4, so you don’t need any paid connectors to Looker Studio for Meta and so on.

Now you can see GA4 is a tool that offers endless possibilities of data ingestion, storage, modeling and activation. You can get the most out of it if your combine it with the right tools from the Google Cloud Platform (GCP) and beyond. Just a few examples:

  • Try to connect it with all your Google tools like Search Console, Google ads, Merchant Centre to get more accurate data
  • Set up Measurement Protocol and send margins for each product to GA4
  • Collect in-store (offline) conversions with server-side Google Tag Manager (GTM)
  • Connect all that data in real-time to BigQuery and use GCP’s machine learning models or just simply analyze your data in Looker Studio.

Gabriel Jurčo

After five years at 6clickz, Gabi developed into an e-commerce consultant, in charge of international e-shops with 6+ markets and 8-digit turnover.