Wouldn’t it be grand to have a way to defend Facebook Ads using the numbers most people are interested in, rather than mere qualitative analysis? This method is a straight forward one, but requires that you have your Google Analytics and Facebook accounts linked. It helps to have an organised GA account too. Ensure you have Excel and enough Google Analytics and Facebook data to work with.
Head to Google Analytics, go to “Conversions”, “Top Conversion Paths”, and then “Source/Medium Path”. Select “advanced” and adjust your filter so it includes Source/Medium Path with Facebook. What this does is to leave you only with conversion paths that include “facebook” or whatever it’s named. Once you’re certain you have all the conversion paths needed set to download, select “Export”, for conversions, use “All”, one or more path links, “All” for Type, and 30 days (or whatever you wish) for your Lookback Window before you export in csv format.
Open the file in Excel, copy the single-path data and paste it somewhere else, before you remove it from the raw data. Have a column inserted between “Conversions” and “Source/Medium Path”, naming it “Facebook Path Detail.” Have columns A to D filtered such that Source/Medium Path is filtered with “Begins with: “facebook” and use the same title for column B’s displayed empty rows.
Next, adjust the Source/Medium Path’s filter to “Begins with: facebook” AND “Ends with: facebook”. Name all of column B’s displayed empty rows with “Begins/Ends with Facebook.” There should be an overwrite in some rows. Then, set the Source/Medium Path filer to “Ends with: facebook / cpc” and name the empty column B cells “Ends with Facebook.” Make sure there are no overwrites.
Finally, clear the last two filters and selecting only the blanks let the Source/Medium Path have another filter. Let all empty cells left be named “Contains Facebook” before you clear the filters and check to make sure column B has no empty cells.
Select everything in columns D to B, select Insert and then Pivot Table. Hit “Ok”, put the table in a new sheet, set your pivot fields such that your data will appear in a compressed view, and head back to your sheet.
Copy the table and have the values pasted in a different sheet, with the name “Paid Facebook – Attribution (Multi-Path)” added. Next to the attribution data, paste the single-path data you cut out earlier and name it “Paid Facebook – Single Path Conversions.” Once you’re sure the date ranges have been added, have the rest formatted however you like. This will give you a qualitative understanding of your paid Facebook efforts which anyone can understand.