Welcome back to AIMCLEAR‘s Deep in the Sheets! In this week’s episode, we’re going to create ad copy reports to analyze the success of different ads and headlines. The two major metrics we’ll use include conversions per thousand impressions and click-through rate. Follow along as we demonstrate how to construct an easy (yet VERY effective!) ad copy report using core Excel functions. Find out which of your ads is a winner. Thanks for tuning in!
Read on for the full transcript:
Hi everybody, Dion here. This week on Deep in the Sheets we’ll be looking at ad copy reports. Alright, in this account we have four basic ads. There are two ad copy variations, two headline variations, and two ad copy variations as well. So, what we wanna do is to see how do the ads compare to each other, and how do the headlines compare to each other. So, to do that we’re gonna start by combining to make full ads within our data. That will allow us to do pivot tables with them, and to, ya know, look at all of the ads together, at once. So, first we’re gonna insert a row after our display URL. Gonna call this “Full Ad.” And this is going to be our ad headline, and then it’s going to be—I’m going to put a carrot between them using the ampersand to concatenate. And I add the carrot so I can replace it later. And the next thing here is going to be the display URL. Then it’s going to be the next piece. Just adjust that. So, another carrot, then we do description line 1, Then we’ll do the same thing for description line 2. So, once I drag that down and paste values, and now we have one column that’s every single piece of the ad altogether, so we can pivot table—pivot off all of those at once. So, we’re gonna go ahead and throw that into a pivot table. Once we have that in a pivot table, we’re gonna start with the headline first. So, just looking at ad, as it’s called. And, um, these are our two ads, and now we wanna look at conversions per thousand impressions and click-through rate. Those are gonna be the two major metrics we look at here. We’ll also keep in impressions just to understand volume, and conversions to uh—just so we have that in place as well. So, the first thing I’m gonna do is to remove my totals. So, grand totals are off, sub totals are off. Now I’m going to do a calculated field which we’ve talked about in previous Deep in the Sheets. This is going to be conversions per thousand impressions so I’m gonna call this CPI. The formula for this is going to be in a bracket: conversions divided by impressions. So that’s conversion per impression and to make the number a little more digestible we’re gonna multiply it by 1000. So, we’re just gonna do an asterisk, 1000, and that will give us our conversions per thousand impressions. Now what we also want is a calculated click-through rate. We’re gonna call that “Calc CTR,” and the formula for that one, real simple, is just going to be conver—or clicks down here divided by impressions. So, now we have our CPI, which I keep this as a number and I like to shrink it down to make it, ya know, a little more readable. So, for every thousand impressions in the headline “National Spaghetti Day,” we get about one conversion, and it’s about half that for May 6th spaghetti. Click-through rate—we can also see here that—pretty big lead for National Spaghetti Day as a headline. So, for my headline report I would take these, drop these into a new table. First I’m gonna paste the values, then I’m gonna paste the—the uh, formatting on that. And there we go. And then you would remove “sum of.” “Sum of,” replace that with nothing. And that’s a real quick way to look at our headlines compared to each other. The next thing we’re gonna do real quick is to use our full ad which we created in a column and I’m gonna replace “ad” with “full ad.” And these are gonna look a little choppy here, we have our formulas all ready to go. We’re gonna drop this into our new sheet as well. Go ahead and paste formatting for that. I’m gonna bring over these headlines. Title that “full ad.” And now, this is a good one here. We’re gonna wanna take these and we’re gonna do a find and replace for the carrot that we added in earlier. And we’re going to replace it with a line-break. So, a line break on this one is going to be—let me bring up my num-pad. For Windows, a line-break is 010. So, with that every time there was a carrot, a line-break was created that dropped it to the next line. So, this is going to be a properly formatted AdWords style ad here for each one of these. And now we can do some quick formatting changes where we can go ahead and—let’s see. First here we’re going to make the headline a couple sizes bigger. Gonna make it blue. Do that to the next one as well. Two sizes bigger, blue. The last two as well—blue, a couple sizes bigger. And here as well. Last thing we’re gonna do here is go ahead and make our display URL green as they do. Do that here as well. And with these quick changes here we have an easy way to look at which ads are standing out versus each other. It’s a good comparison. Ya know, we can look at CPI here, we can do some conditional formatting to say which one really stands out. And it really looks like this ad is our winner. So, that’s a pretty easy way to look at ad reporting. Those calculated fields will give you good metrics to compare to each other. And that formatting makes it easy to present them in a way that the ads might look in the Google search results. With that, you can do an ad report. Join us next time on Deep in the Sheets.
