How to create a ROAS report using data blending

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 Marketing Intelligence Cloud license to use the custom fields and data blending features.

Instructions

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

1. On the Supermetrics Hub, go to Transform → Custom fields.
2. Click Create new custom field.
3. Select Dimension and click Next.
4. Select the Google Analytics 4 data source and click Next.
5. Select the Lookup type transformation.
6. Select the Source / medium field to transform.
7. 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.

     

8. Once your lookup table is set up, click Next.
9. Give your custom field a name, such as "Lookup GA4 results to Channel".
10. 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 Blend with Google Analytics 4 and ads data sources

1. On the Supermetrics Hub, go to Transform → Data blending.
2. Click Create new blend.
3. Select your data sources.
4. Click Continue to configuration.
5. Select the accounts you want to include in the blend for the first data source, and click Next data source.
6. After selecting accounts for all data sources, click Continue to overview.
7. Give your blend a name.
8. Click Create blend.
9. Click Review blended fields.
10. Make sure the Impressions, Clicks, Cost, and any other metrics you want to include in the blend are matched in all ads data sources.
11. 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. This Google Analytics 4 field isn't mapped to fields from other data sources.
12. For Google Analytics 4, add a new field containing the custom field created in step 1 that assigns the Channel name to the source/medium value from Google Analytics 4.
13. For the ads data sources, map the Data source dimension to this new Google Analytics 4 field and give this field the name "Channel".
    

Step 3: Create a custom field for the whole blend

1. On the Supermetrics Hub, go to Transform → Custom fields.
2. Click Create new custom field.
3. Select Metric and click Next.
4. Select the Data blending data source and the data blend you created in step 2.
5. Use the Function transformation type.
6. Select the Divide function.
7. For the Divide field, select Revenue, and for the by field, select Cost.
8. Click Next.
9. 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 4: 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. 

1. In your destination, select the data blend you just created.
2. Select the Clicks, Impressions, Cost, Revenue, and ROAS (custom field) metrics and the Channel dimension.
3. 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.
4. 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.