Working as a PPC advertising professional for a PPC agency means I’m knee deep analysing and working on my client’s PPC accounts using Microsoft Excel on a regular basis. Microsoft Excel is extremely powerful and has many features that can help a PPC professional to complete their day to day tasks much faster and to make data analysis more fruitful.
I’ve put together 13 Microsoft Excel formulas that I use a lot as a PPC professional. If you’re looking for some of the most common PPC formulas that Pay Per Click experts should know, then check out my blog on PPC formulas.
1. Check length of ad copy
If you want to check the length of any message in your ad copy, then you can easily do it with the LEN formula:
=LEN=(Text)
2. Check if ad copy is too long
If you have a large number of ad copies and would like to quickly check if any of them are too long, then you can do that by combining an IF formula with the LEN formula.
=IF(LEN(A2)>31,”Too Long”,”OK”)
3. Change ad copy to proper case
It is best practice for all headline messages in your ad copy to be in proper case. If you need this en mass for a number of ads, then there is an easy way to do this with Excel’s PROPER function.
=PROPER(Test)
4. Make keywords lower case
It is better if all your keywords use the same case. This is because it makes it easier to look through your keywords. I prefer to make all of my keywords lower case. To do this, you can use Excel LOWER function.
=lower(Text)
5. Change keyword into a Broad Match Modified keyword
To make a keyword into a broad match modified keyword, each word needs to have a plus symbol before it. To do this we can use the substitute formula to replace all spaces with a space and a plus symbol. This will add a plus symbol to all words apart from the first word. To add a plus symbol to the first word, we simply need to add a plus symbol to the start of the formula.
=”+”&SUBSTITUTE(Cell,” “,” +”)
6. See if a keyword’s CPA or ROI is too high
Looking through a large number of keywords to find the ones with a high CPA or ROI can be difficult if you have so many to scan. This is why to make life easier; I like to add an extra formula that will tell me if a keyword has a CPA or ROI that is too high. For example, if I want to look at all keywords that have a CPA higher than £25, then I could use the below formula
=IF(Cell>=CPA/ROI target,”Too High”,””)
7. Find date of Monday before a given date
Often when reporting on performance, it can be useful to group daily performance data by week. If this is something that you need to do, then the first step is to group all your dates by the Monday that came before the date. To do this, one way is to use the WEEKDAY formula
=Cell-WEEKDAY(Cell,2)+1
8. Find number of words in a search term
One piece of analysis that you can do, is to look the performance of searches that have more words in them compared to the ones that have less words in them. To be able to do this, you will first need to find out how many words are in each search term. You can do this by combining the LEN and SUBSTITUTE formulas
=LEN(Cell)-LEN(SUBSTITUTE(Cell,” “,””))+1
9. Make a keyword into Exact match
Making a keyword into an exact match keyword requires adding square brackets around it. To do this, you can use the below formula:
=”[“&Cell&”]”
10. Make a keyword into Phrase match
Making a keyword into a Phrase match keyword requires adding double speech marks around it. To do this you will need to add a special character around each side of the keyword.
=CHAR(34)&Cell&CHAR(34)
11. Group campaigns into Brand, Non-Brand and Display
When reporting on the performance of a PPC account, it can be useful to group campaign performance data into various ways. This will make it easier to see how key areas within an account are doing. I have a given a simple example on how you can group campaigns into Brand, Non-Brand and Display below. Note that this formula will only work if all of your campaigns contain either Brand, Non-Brand or Display. If one of these words is not included in any of your campaigns, then the formula will return ‘Error’.
You could even group data into other ways as well. For example, you could use the same basic formula to group campaigns by the clothing type or the location that is being targeted. As long as this information is present in the campaign name.
= IF(ISNUMBER(SEARCH(“Non Brand “,A2))=TRUE,”Non Brand”, IF(ISNUMBER(SEARCH(“Brand”,A2))=TRUE,”Brand”,IF(ISNUMBER(SEARCH(“Display”,A2))=TRUE,”Display”,”Error”)))
12. Show nothing if formula outputs an error
Sometimes, your formulas will show an error that you don’t really mind seeing. For example, if you are calculating the conversions rate of several keywords and one of the keywords in your spreadsheet has zero conversions and zero clicks, then Excel will throw up a #DIV/0! error. You can tell Microsoft Excel to show nothing if an error occurs. Or if you want, you can tell Microsoft Excel to tell you if an error occurs by using the below formula.
=IFERROR(Formula,””)
13.Get performance data for all searches containing a specific word
You have a whole wealth of data in your search query reports. I find it useful to see if there any words that generally perform very well. I can then make new good performing keywords from this data. I also like to see if there are any words that don’t do very well as I may decide to block these words out.
One way to do this is to use the SUMIF formula with a wildcard to add up all the data where the search term contains a word or set of words that you select. I’ve added a basic example below, but you can easily use the formula below to calculate any necessary metrics that you need In order to carry out your analysis.
=SUMIF(Range,”*”&Criteria&”*”,Sum Range)
Wrapping up
There are several formulas that you can use to improve your PPC data analysis, reporting, and also generally work faster within Microsoft Excel. I have added a few examples above that should help to get your creative juices flowing. Once you’re comfortable using these formulas, you can start creating much more complex formulas to further improve your PPC data analysis and reporting skills.