We, online marketers, love to track everything in our campaigns. Here’s another way to look at it:
This posts assumes you’re familiar with UTMs, that you use Facebook ads, that you know how to use Excel vlookup AND that you want to track your Facebook ad performance by UTM.
Side note: I’m the Marketing Engineer at Thinkful (we help you learn to code through mentorship and real coding projects) and I use this technique to tie Facebook ads to lead, engagement and student conversions with our CRM. The way I match everything is via UTMs. This is REALLY important since it enables me to determine the ROI of every campaign, audience and ad.
Ready? Let’s dive in!
1) Got to your Facebook ad dashboard and click edit columns:
2) Select the “Ad ID” field and click “Save Columns”:
You’ll now see an extra column labeled “Ad ID” (the 4th column in my example below). You can now select the time-range you want to analyze. In our example we’re looking at the last 7 days:
3) You’ll now want to export this to Excel by clicking on the blue “Export” button.
4) Next stop: Facebook Power Editor. Open Power Editor and click on “Options” > “Settings” > “More Settings” and enable the “Keep Deleted Campaigns/Ads” option. Click “Save”. This enables us to get info for ads you may have deleted.
5) You’ll now need to download your latest campaign data. Do so by clicking on the big “Download” button on the top-right. Once your data is done downloading (this can take a minute) select the “Ads” tab up top:
6) Click on your 1st ad (it should become highlighted in blue), then press cmd + a to select all your ads (they should ALL be highlighted in blue now). Press cmd + c to copy them to your clipboard.
7) Open the Excel file you downloaded in Step #3 and create a new tab.
8) Paste this data in the new tab.
Look at Column A… You see where we’re going with this, don’t you? Column A contains the Ad ID you need to tie this to your previous export (from Step #3). Now look at Column S (with the header “Link”). Oh snap, I smell some UTM awesomeness!
Side note: You may be wondering “why don’t we just look at THIS data (from Step #8) and call it a day?” From my experience, the spend, impression and click data from Step #8 is NOT always accurate. Another reason is that on the web interface you can ad conversions and other metrics (just follow the steps we used to add the “Ad ID” in Step #2 but select any other metric you want to analyze). Ready to keep going?
9) Select Column A and do a Find and Replace.
In the “Find what:” type “a:”
Leave the “Replace with:” blank and click “Replace All”. This will essentially remove all the “a:” from Column A.
10) Now go back to “Sheet 1” (which contains the data from the Facebook web export from Step #3) and create a new Column at the end called URL.
11) Time for some vlookups! You’ll want to lookup the Ad ID (Column D in Sheet 1 of this example) and match it with Column A in Sheet 2. You’ll want to return the “Link” field (which is Columns S for me).
Here’s what my vlookup looks like in Sheet 1, cell S2: =VLOOKUP(D2,Sheet2!A:S,19)
12) Drag your formula all the way down. Boom! You now have URL details at the ad level.
13) You can now use text-to-columns to split your URL by “?” or “&” and get UTM info.
14) Throw all this in a Pivot Table.
15) Find incredible insights, optimize your campaigns, and destroy your competition :)
P.S.: If you’re interested in more blog posts like this, you should follow me on Twitter.