Transcript – 5 Really Useful Excel Formulas for PPC
PPC and Excel go hand in hand like Larry Page and Sergey Brin, so I’m going to show you some of the PPC formulas you need to know to get the most from your paid search campaigns. Coming up after this.
Hi guys, thanks for jumping on this very quick tutorial video. This is going to be around Excel formulas for PPC. First of all, we’re going to start off with a very simple formula to change and manipulate the text within text ads. Let’s start off with the upper formula. This is literally transferring text into uppercase. Hit the equals symbol to start your formula and start typing in upper.
Select the formula and select the cell you want to add the formula on then hit enter and there you have it. Upper as a formula transfers text to uppercase. Let’s do the same thing again to transfer lowercase. You’ll notice in the cell below we have a sentence of text for the headline, that is in sentence case, so we transfer this to lower case, so the same formula again just slightly different.
Equals lower and then that will transfer the text you select in the formula to lower case and that’s it. Simple as that. When you use this formula would be for things like if you get some ad copy or some copy from your marketing department and you need to make into lowercase and it’s all sentence case, you can do that quite easily. Next step we have another very simple formula called proper and this one creates a sentence case.
Literally enter equals and then hit proper on your formulas and that’s it. Very simple. Next step we have the LEN formula. I always talk about planning’s important in ad campaigns on PPC, so this will literally when you create a text ad, it will give you the character length of each cell you can choose. Very simple formula equal LEN, then choose the cell you want to count the characters off and hit enter.
Like you can do with all formulas in Excel, you can select the cell you’ve done the formula in, drag it down with the bottom right corner option, and there you have it. For the headline 1, headline 2, and the body text, you now have a character count and you can see where all within the character limits for ad words which are 30 characters, 30 characters, 80 characters. We are absolutely fine there.
Next up with have concatenation which is literally adding one cell to another. When would you use this formula? It’s for making keyword lists. You’ll see here I’ve got a list of every single city in the UK and I’m running a plumbing campaign and I’ve started off with my keyword list wanting to bid on keywords, plumbers in and their location. For the purpose of this, I’m going to make the keywords modified broad match. All of them need a plus in front.
Let’s start the formula with an equals and then you then you can concatenate selecting from the list. Let’s start off with adding the plus first of all once you activate the formula. Do some brackets, quote marks and then do a plus and then you can do a comma to start the next cell. We started the first cell, then we do another comma, and then we do another quotation mark, and then a space for the space in the next word of the keyword, then the plus for the modified broad match data. Another quotation mark to end it, another quote to separate the next cell, which is very easy to do. Then you do the same thing over and over again.
Essentially what you’re doing there is concatenating the cells together with a customized string of texts which is a space and obviously the plus mark for the modified broad match keyword. Add all those together, hit enter and you’ve concatenated your cells into keywords. If you click the bottom right-hand corner of a cell and activate it to go down and repeat the formula and do the same on the other side the way you’ve just done your formula, you’ll see you’ve now got a fully fledged keyword list of modified broad match keywords.
Now we’re going to kick things up a little bit and we’re going to start off with “if” statements. This is a formula I would use to analyze keyword data at a glance for a client, for example. Here’s a keyword list I downloaded from AdWords earlier and in this example, I want to find out which keywords have a cost per conversion above 50 pounds. In order to do that, I need to create an “if” statement to give me some statements as to whether or not the keyword is a threshold keyword or not, whether it meets that criteria. Let’s start off with the equal sign and the type in “if” to start your “if” statement. First of all you need to give it a logical test. In my example, I’m going to select the first cost per conversion cell. If that cell is greater than 50 so the greater symbol then 50, then I want to return a specific statement, so hit comma to start the next part of the formula to give the statement whether it’s true.
If the cell is indeed above that threshold, I’m going to put a quotation mark and I’m going to type in high CPA. This will return the result of high CPA if they go above that threshold. Let’s do a comma and I’ll start off with the negative threshold. That will tell it to say it’s an okay CPA if it does go under 50 quid. This is a very easy way to see it at a glance whether your keywords are performing or not. Her’s the formula. We’ve done a logical test, we’ve hit okay and there it is. This keyword at the top has an okay CPA. If we drag that all the way down, it’ll come back with all the different results and you’ll see them there.
For the benefit of reporting, we’re going to mix this with some conditional formatting as well. By using conditional formatting, what that’s going to do is going to allow us to see at a glance whether or not a keyword is good or bad or meeting the results that we want them to. Head over to the Home tab, go to conditional formatting, highlight cell rules, cells that contain the text and then you start typing. Obviously, you want to do one your logical tests. In this example, we’ll start off with okay CPA, we’ll make that green for good because there’s a good result, it’s below what we wanted. We’ll start off with the next one which is the bad CPA or the undesirable CPA, and if it’s greater than 50, then we go with dark red text to highlight the bad of the keywords. That’s it.
Now you have a nice report there for your clients or for your internal reporting. You can see at glance good keywords or bad keywords. The final formula I’m going to show you guys is a bit more complex. This is the Vlookup. We’ve got two tables of data here which are month one and month two. Say, for example, I want to find out the cost per conversion of one of my keywords in month two and see how it performed in month one and pull that data from another table.
This is called a Vlookup. What you need to do for this is type in equal as usual to start the formula and then type in Vlookup and select the formula. First of all, you need to tell Excel what value you’re looking for. In this example, we’ll start off with the first keyword in my list of month two and select that value. This value is what we’re going to look for in the month one data table to try and pull the conversion rates.
We’ve selected that data, now, we head over to month one. We need to tell Excel the entire table that we’re looking for. Select the entire table, go to the top left of my cell, hold control and shift, hit down and right, you’re selecting the entire cell. Now we need to tell Excel which column to find that data in, and it’s by index number. Column J is index number 10, counting across all the way to number 10.
You enter the comma first of all and then put number 10 to tell Excel we’re looking for the data in column number 10 and then hit you hit comma. That’s done. Next is a true or false test. Hitting true means it’s going to be a half match. Hitting false means it’s an exact match, and we’re looking exactly for that keyword. You’ll see that now we’ve found that value. Very easy to do and you can drag that formula down to find the values in the previous sheets, in this sheet as well and you can see here that’s pulled the value. Let’s double check that. Go back to month one, go to our very first keyword which is e-mail marketing lists. That’s down there, we found it and it’s pulled the correct cost per conversion. There it is.
Thanks for watching. I hope you enjoyed this video. I’ll get some more technical tutorial, but I’m sure you hopefully took a lot out of it. I do talk fast and I do go through these things quickly, so anytime you can pause the tape, have a look back and look at the very top as well where I’m entering the formulas and look at what I’m actually typing in as well and review it. Let me know in the comments how you’re getting on with analyzing your PPC campaigns. I’ll see you guys on my next video.