How to create a ROAS report using data blending

Supermetrics Support Site Team

Modified on: Wed, 5 Feb, 2025 at 1:46 PM

This guide will help you set up a data blend to analyze the results of your marketing efforts in data analytics platforms.

In this example, we build a custom field and data blend to blend data from Google Analytics 4, Google Ads, and Facebook Ads to get the channel data from Google Analytics 4.

You can apply this setup also to Adobe Analytics and Piano Analytics or ecommerce and CRM platforms like Shopify or Salesforce.

Before you begin

You need a Supermetrics subscription with access to the custom fields and data blending features. Custom fields and data blending are part of the Supermetrics Business package, but you can run a limited number of custom fields and data blending queries with Growth and Pro subscriptions. Contact us to learn more about the Marketing Intelligence Platform and Business package.

Instructions

Step 1: Create a custom field to transform Google Analytics 4 source/medium dimension to channel names

On the Supermetrics Hub, go to Transform → Custom fields.
Click New custom field.
Select Dimension and click Next.
Select the Google Analytics 4 data source and click Next.
Select the Lookup type transformation.
Select the Source / medium field to transform.
In the Lookup table, insert the source/medium dimension into the Lookup value field, and the channel name into the Return value field. For example, set the lookup value "facebook / cpc" to return the value "Facebook Ads". Note that it's important to have the exact same wording as returned in the Data source field, or the Google Analytics 4 and ads data won't be joined.
- For example, the lookup value is the value returned directly from the Google Analytics 4 platform. If the lookup value is "google / cpc" from the platform, enter "google / cpc" instead of "google CPC".
- Similar to the return values, enter the same wording of the data source name returned by Supermetrics. For example, use "Facebook Ads", not "Facebook Advertising" or "Facebook".
  Tip: To get the correct lookup value and return value, run queries for Google Analytics 4 and data analytics platforms separately to get the correct values.
Once your lookup table is set up, click Next.
Give your custom field a name, such as "Lookup GA4 results to Channel".
Click Create custom field.

Note that you can create your own custom field logic by using the Google Analytics 4 reference field of your choice, for example, the "source", "first user medium", or "utm" field, as long as you transform the returned value to be the same as the value you want to link from the ads platforms.

Step 2: Create a union blend with Ads data sources

On the Supermetrics Hub, go to Transform → Data blending.
Click Create new blend.
Select Union blend.
Select the Ads data sources and accounts to use.
Once you have the data sources and accounts set up, add the fields to include in the blend. Make sure the Impressions, Clicks, Cost, and any other metrics you want to include in the blend are matched in all ads data sources.
After your fields are mapped, click Next step.
Give your blend a name and an optional description.
Click Create blend.

Step 3: Create a join blend with your union blend and Google Analytics 4

On the Supermetrics Hub, go to Transform → Data blending.
Click Create new blend.
Select Join blend.
Select Data Blending data source, and your newly created Ads union blend, and add Google Analytics 4.
Add all the relevant metrics and dimensions in both your union blend and Google Analytics 4.
1. Make sure the Data source dimension is present in the union blend.
2. Make sure the custom field you created in Step 1 is added to the Google Analytics 4 table.
3. Add the conversion metric you want to track to Google Analytics 4, for example, the Revenue metric. You can also add any custom conversion value.
Click Next step.
Select your join key.
For the union blend, that’s the Data source dimension.
For Google Analytics 4, that’s the custom field created in Step 1.
Select the type of join (Left, Right…). If you don’t know what to choose, learn the difference between different join types.
Name your blend.
Click Create blend.

Step 4: Create a custom field for the whole blend

On the Supermetrics Hub, go to Transform → Custom fields.
Click New custom field.
Select Metric and click Next.
Select the Data blending data source and the data blend you created in step 3.
Use the Function transformation type.
Select the Divide function.
For the Divide field, select Revenue, and for the by field, select Cost.
Click Next.
Give your custom field a name, such as "ROAS (custom field)", and click Create custom field.

This new custom field won't be visible in the blend mapping as it's not a field to be mapped, but it will be available in the Data blending data source.

Step 5: Set up your query in your destination

To build a report using the data blend, take a look at these instructions on using the data blend in your destination.

In your destination, select the Data blending connector and the data blend you created in step 3.
Select the Clicks, Impressions, Cost, Revenue, and ROAS (custom field) metrics and the Channel dimension.
Filter out channels that are not equal to "Facebook Ads" or "Google Ads" to filter out all the Google Analytics 4 traffic sources not coming from Ads data sources.
Run your query.

This example was for paid channels and Google Analytics 4, but you can do similar reports for any social platform with analytics tools, or ecommerce platforms, as long as you can link the result to the source channel.