Need help with your pay per click (PPC) campaign management? Microsoft Excel can help you out with:
Crafting Your Ads
A few tricks in Excel substantially assist in efforts to macro-manage ad writing.
1. Character tricks: Concatenate, Convert Case, Len, Trimming
=CONCATENATE(cell,cell) combines the characters of the specified cells. Add characters between them by using quotation marks (i.e. use “ “ to add a space).
Use =PROPER(cell) to capitalize every first letter, =UPPER(cell) to change everything to upper case, and =LOWER(cell) to change everything to lower case.
=LEN(cell) counts how many characters are in that cell.
=TRIM(cell) trims any unnecessary spaces, while =RIGHT(cell,x) trims a cell from the right by the number specified (x), and =LEFT(cell,x) does the same from the left.
All these are beginner level functions.
2. Counting Words
You can count how many words there are in a cell through the calculation: =LEN(cell)-LEN(SUBSTITUTE(cell,” “,””))+1. This tells Excel to recognize every string of characters before and after spaces as 1 count, returning a number that counts the words in a cell. Tweaking this formula entails basic knowledge of function formulation.
Monitoring PPC Performance
Excel tricks can also help monitor performance. A favorite for intermediate users is the vertical lookup (VLookUps) function.
3. A literal example: =VLOOKUPS(B2,C3:E56,3,). This tells Excel that you want to use vertical search in a data table composed of cells C3 to E56 using cell B2 as your source (typically a keyword). You want column 3 in the data table to be the source you want to retrieve the data from. Leaving the final value after the last comma blank is the same as specifying it to be TRUE. That value is a conditional value that specifies how close to your source cell (keyword) your matches should be. Simply leave it blank.
This intermediate function is great for looking up PPC keywords that are performing poorly or outstandingly. You can also use Excel’s wizard for this function.
4. Another fan favorite are Pivot Tables – an easier and graphical version similar to VLookUp. It visually compares metrics and calculations such as Click Through Rate (CTR) via graphical representations. PivotTables are vary for different versions of Excel, but the process is the same:
- Open PivotTable Report wizard
- Select Excel worksheet as data source
- Choose the range of data cells to work with
- Specify formula (e.g. CTR)
- Tweak the resulting table or graph
PivotTables require an intermediate level of Excel prowess.
Boosting PPC Performance
The IF conditional function is a powerful tool that makes Excel perform computations that help boost PPC performance.
5. IF functions evaluate a condition. You specify one action when the condition is true, and a different action if it is false. You can use an IF condition to change bids depending on cost per action (CPA) and conversion rates, as an example. If the CPA for some keywords are more than a specific value or overshadows conversion, you can command Excel to reduce maximum bids for those keywords. Otherwise, you can just leave them be.
The IF statement is an advanced function. Even if you use a wizard, if you can’t define your own calculations (e.g. reducing maximum bids), you will not be able to maximize its use.
There are numerous more tricks and functions in Excel that work well with managing large PPC campaigns. These 5 are among the most used and most useful.