Here is the ask from your manager - they want to know how much Facebook ad spend led to those conversions you reported from Google Analytics goals. In other words, how much did each conversion cost us? Not only that but can you also identify which ad creatives resulted in the highest number of new site visitors and lowest bounce rate? Simple ask right? If you don't already have an automated way to do this, you'll want to combine this data together manually.

If you're already mapping your conversion data back to your paid metrics, then you are ahead of the curve. You may be interested in Join, our product that automates this process for you.

A Quick Review of UTMs

If you’re on top of your tracking management, every ad will have a URL composed of various UTM tracking parameters that uniquely identify that specific creative. The standard parameters like utm_source, utm_campaign, or utm_content are often used to understand which ad, campaign, and sources are driving the most clicks. The parameter utm_term is commonly used to denote the specific search term that resulted in your ad to be served up to the viewer. These parameters are freeform and can be used in any way you would like. And that's for better or worse.

Savvy marketers with elaborate tracking plans will often use their own custom URL parameters. This allows for any combination of unique tracking parameters, or in many cases, a single complex value that captures multiple data in one string. As long as you understand your parameters, and have a way to track down to the ad level, you can do this analysis.

As you can imagine, once you get a tracking method established it's difficult to change and adapt that approach down the road since you'll probably impact some downstream reporting.

Given that you already have a great UTM tracking methodology, we're going to focus on how to join your conversion data to your paid data using Excel.

Exporting ad URL Data from the Ad Networks

Let's use Facebook as our example. To quickly summarize our process, we are going to log into our Facebook Business Manager account, view the Ads tab, make sure that the Ad ID, Ad Set ID, and Campaign Name & ID columns are visible, then download that data.

A quick side note. In Facebook, you have the option to set up your UTM parameters independently of your destination URL and use dynamic parameters. Those look like utm_campaign=. If this is the case like it is for us at AdStage, remember to export the actual campaign, ad set, and ad IDs so that we can match on those values.

Once you have your Ads table, go ahead and download the data. If you're using the Facebook Ads Manager for Excel (FAME), just make sure your template includes the necessary columns and run your report.

Exporting Your Google Analytics Data

Now that you have your ad data ready for analysis, we're going to download conversion data. In this example, we're using GA goals as they are easy to understand and every marketer has used them at one point in their career.

If we only cared about Google Ads performance, then we wouldn't have to do any additional steps as that data already can be analyzed within GA. But we want to learn how many conversions came from our Facebook ads, and furthermore how much we spent on those ads.

We're pulling our data from the Source/Medium report, using the Secondary dimension Ad Content, and have selected a conversion goal that measures new trial sign-ups. This will provide us with a table of data that we can use to join with our Facebook advertising metrics.

It's important to note that if you use the same ad creative in multiple campaigns, you will want to also export the utm_campaign parameter value as well. In our example, we have unique ads so we can join using only the ad ID value. But if you don't, you can easily join using both the Ad ID and the Campaign ID.

Not only will be able to attribute new sign-ups back to the Facebook ad creative, ad set, and campaign, but we can also look at other metrics such as New Users, Sessions, and Bounce Rates. This is helpful data when you're optimizing your direct response ads.

Next, we download this data as an Excel file so that we can combine our GA data with our Facebook data we fetched earlier. If you prefer to do all of this in Google Sheets, that also works just as well. In fact, here’s a shared Google Sheet so you can follow along.

Combining the Ad Data with Your Tracking Data

Remember when we used the UTM parameter Ad Content as our secondary dimension in our GA report. That's the value that we are going to use in a vlookup function to match the Facebook ad level data. Once we match on this value, we are able to combine all the GA metrics alongside our Facebook metrics.

With your Facebook metrics on one sheet, and the Google Analytics metrics on a second sheet, let's start joining these up!

Since most of the information that we would like to know is on the Facebook data sheet, we're going to start there and build our vlookup function to reference the GA data. The VLOOKUP function is "=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup])"

Our values will be:
lookup_value = Ad ID
table_array = Facebook data with Ad ID in the first column
col_index_num = The column number that lists your conversion values
range_lookup = FALSE (For exact match. Each Ad ID should have it's own row)

In the first empty column to the right of your data, add the function: =vlookup([ad id column], [GA data array], [column of your metric], FALSE)

The function in our example document looks like this:
=VLOOKUP(F2,'GA Data'!A1:I4,8,FALSE)

If the VLOOKUP function does not match a value in your array, it will return a #N/A value. Ugly! If you want to clean up your sheet, you can use a combination of IF and ISERROR functions in front of the VLOOKUP to replace the #N/A value with any value of your choice.

If the ISERROR function returns TRUE (#N/A), the function will return what you designate as the [value_is_true] parameter. I personally like to use a value like "No Match" or "False". This makes it easy to filter out these rows and hide non-matches.

That function looks like this:
=IF(ISERROR(VLOOKUP(F2,'GA Data'!$A$1:$I$4,8,FALSE)),"No Match",VLOOKUP(F2,'GA Data'!$A$1:$I$4,8,FALSE))

Note: Remember to use absolute references in your [table_array] reference.

Now you can add a few more VLOOKUPs and pull in any other GA metrics that you would like to map back to the ad ID. Once you have the data that you want, we can also aggregate these metrics to the Ad Set, Campaign, and Account levels.

Conclusion

So to get back the questions we were asked at the beginning, we spent $3,843 for 8 conversions. That’s an average of $480 per conversion. One ad did better than the other, so we’ll dive deeper into that creative to see if we can replicate those results. New Users, Sessions, and Bounce rate didn’t show any significant differences, so we’ll report as much and recommend we keep testing!

This example is a very simplified version of what you're dealing with, but I hope that it demonstrates the necessary steps involved in mapping your conversion data back to the paid data.