20 Excel-ent tips for PPC

May 11, 2010
by Andrew Harris

Excel is an excel-lent tool for managing and analysing PPC campaigns.

Understanding how to use Excel to its full potential gives a PPC manager the tools to complete their day to day work efficiently by helping them grow the campaigns, analysis performance correctly and therefore helping them make the correct strategic decisions.

This blog lists the top 20 functions we believe are the most useful when setting up and managing PPC campaigns. If you disagree with the list, write a comment.

20. Basic Calculations

This covers very basic calculations such as SUM, ADD, DEVIDE, MULTIPLY, all of which are used on a daily basis to calculate basic sums needed in PPC and in business. Basic calculations are used to calculate PPC sums, such as CTR%, CPC, CPA and ROI.

19. Short Keys

There are a huge range of short keys which we recommend using. Using short keys can enable you to complete very complicated tasks very quickly. The most popular short keys are Ctrl+A which selects the entire worksheet. Ctrl+C which copies it, and Ctrl+V which then pastes.

Other short Keys such as Alt+D,F,F to apply the auto filer is very useful too.

18. Combing cells

This can be completed a number of ways. The formulas below highlight the different methods.
=CONCATENATE(A1,B1)
=CONCATENATE(A1,” “,B1)
=A1&” “&B1

These methods can be used to write adverts by referencing the keyword it relates too. It can also be used to generate new keyword to include additional variations etc. Understanding this basic method can bring huge efficiencies when creating keywords and adverts in bulk within Excel.

17. Shared Files

This functionality will allow more than one user to use and edit the same file simultaneously. The changes are introduced every time a user clicks save. This report is very useful when sharing a file across a PPC project team.

16. Removing Duplicates

This is a great tool for cleaning up keyword lists to ensure there are no duplications. Highlight the range of cells you want to de-dupe and then click on the data tab followed by remove duplicates.

This can be found under the home tab.

15. Text to Columns

This technique is used to separate one cell into many columns.

This is great for taking URLs, and then getting your campaign/adgroup names and keywords associated with them quickly from a site map.

14. Trim

This function will remove any extra spaces. This is very useful if keyword data is grabbed or copied incorrectly from another source. Sometimes page layout, formatting can result in empty spaces before and after your characters. This function can be used to clean them up.

Formula example =TRIM(A1)

13. Proper Function

This is a great formula to use to convert your adverts into a proper structure.

It will basically capitalise the first character of every word. It is suggested that adverts with frequent capitals gain a higher CTR%.

Formula example =PROPER(A1)

12. Auto fill

Auto fill is a function which will predict the content of your next cell. Excel has a number of default rules such as ascending/descending numbers, dates, days etc, however you can add your own custom fill rules by clicking the Microsoft Office Button, and then click Excel Options. Click Popular, and then under Top options for working with Excel, click Edit Custom Lists.

11. Macros

Macros can be used if you have to a common set of functions that you need to get carry out regularly. EG: if you have to copy and paste some data into a sheet, then perform a find and replace to remove some data then also paste some further data.

In order to enable the ability to create Macros you need to enable the Developer tab in Excel Options. This will enable the devleop tab:

This will enable you to record

Then simply press Record Macro on the Excel ribbon, then enter the both a name for the Marco and assign a shortcut as shown.

Carry out the task step by step in Excel then press stop Macro. This will complete your macro and enable the macro to be enabled within your workbook.

If you require a step by step guide see the following:

10. Find and Replace

This function is available in most software, however I use Find and Replace regularly when creating new keyword permutations.

This is particularly useful when the asterix * is used. By inserting the asterix before the word Campaign in Find, this would replace everything before the word “campaign” with the word keyword. If you placed the asterix after it, it would replace everything after.

9. Freeze Panes

A simple function which is great when analysing large amounts of data, especially when working in workbooks which has lots of rows or columns.

8. Paste Special

Paste special has many options all which bring different functionality to the table. I mainly use Paste Special – Value, in order to remove formulas, or the transpose function which will convert rows into columns and vice versa.

7. Conditional Formatting

This will highlight cells based on criteria you set. This function is great for analysing trends and highlight poor/good performers.

I use this tool to highlight duplicated keywords, and to see which keywords are performing well and which keywords need optimising.

6. LEN

This function is well known within the search industry. This function will count the number of characters within a cell. This function is essential for writing new adverts. To ensure the title’s, description lines do not exceed advert policies.

Formula example =LEN(A1)

5. Charts

This function has been available in excel for many years, however it is a very simple and powerful method of presenting trends and analysis.

4 IF Formula

The IF formula has many functions and can be applied to a number of PPC related tasks. This function would usually be used to show something in one cell as result of a value being entered in another cell.

Formula example =IF(A1=20,TRUE,FALSE)

I use the IF formula to create action plans when determining my bidding strategy. For example if the CPA is greater than my desired amount, I can use the True field to include “text” such as – “reduce bids”.

Multiple IF formula can be added to ensure the correct strategy is used.

For example – If the CPA is lower than the desired amount and the average position is great than 1, insert a text field “push CPC bid” etc

3. Auto Filter

A very simple function within Excel however it is used on a daily basis when managing PPC campaigns. It is used quickly to sort cost ascending, or to filter out certain type of keywords, match types etc.

To apply the auto filter, select your title row and then under the home tab, click on the Sort & Filter tab. alternatively you can select your headline/title row and then click Alt+D. F. F

2. “The VLookup”

This formula is great for comparing two documents which contain a key which links both documents.

Formula example =VLOOKUP(A1,A1:A2,2,FALSE)

This formula is usually used within PPC to compare keyword cost data against conversion/revenue generated by that keyword. Once the ROI/CPA of a keyword has been calculated, the keyword and its bidding strategy can then are optimised accordingly.

and our winner is….

1. The mighty Pivot table

This was once, a very difficult function with Excel. However Excel 2007 has made it very easy. The best guide I can find is a Video found on dummies.com
Pivot tables can be used to digest huge data files in order to analyse trends and campaign performance. Pivot charts can also be applied to the table to help analyse the data further.

I hope you like our top 20 excel-ent tips for PPC. If you have any excel tips which you believe should be in this list, let us know and we will add it accordingly. If you would like to understand a particular function in more details and how to apply it to you PPC campaign management, feel free to contact me on andrew.harris@latitudegroup.com

Written by Andrew Harris and Steve Grave.

5 Comments

Latitude Group

Integrated Marketing Intelligence