by Kate Heath, @Mktg4Conversion
Creating keyword lists and ad copy for pay-per-click (PPC) campaigns can be a daunting task. There are several ways to use excel in order to make this job more efficient. Below are 3 ways for entry level or expert excel users to gain efficiencies when building out PPC expands for uploading to AdWords Editor or the Microsoft adCenter Desktop tool.
1. Character Limits
If you use AdWords or AdCenter you know that uploading ad copy that is over the specified character limits will result in an error. An easy way to ensure that your ad copy fits within the appropriate character limits is to use the length function in excel. When setting up the spreadsheet to create your ad copy, simply insert a column next to the Headline, Description Line 1, Description Line 2 and the Display URL and use the following formula “=len(B3)” in each column. To clarify, “B3” is used as an example cell.
The example below shows how many characters are within each cell. You can see that the Headline characters are all within the AdWords defined 25 character limit and the Description Line 1, Description Line 2 and Display URL are all within the 35 character limits for AdWords.
This formula allows the user to quickly filter for characters counts that are outside of the limits. Making sure each piece of the ad copy is within the appropriate character limit allows marketers to confidently upload their expands and not waste time making updates in tools such as AdWords Editor.
2. Deduping
I use the deduping formula most often when creating ad copy. I always create my keyword lists firsts and break these keywords into tightly themed ad groups. Once I have the keyword list completely build out I copy the ad groups column onto a separate tab that I label “Ads”. In order to ensure that I am creating at least one ad for each ad group, I dedupe the list of ad groups. This can be done by first sorting the ad groups and then entering the formula “=if(A3=A2,1)” into the column next to the ad groups.
Copy this formula down next to all of the ad groups and then copy the cells, right click on the selected cells and click paste special then select values (this will remove the formulas allowing you to sort the column).
You will see either 1 or false next to each of the ad groups. The number 1 means that the ad group is a duplicate and the word false means that the ad group is not a duplicate. Select both the ad group and the dedupe column and sort to get all of the 1’s together and all of the false cells together. Delete the 1’s and you will be left with a deduped list of ad groups to create your ad copy.
3. Capitalization
To ensure that your campaigns are well organized the best practice is to make sure each of your keywords are uploaded in lower case, while the first letter of each word in your ad copy should be capitalized. There are three easy formulas that can be quickly utilized to capitalize, lower case or proper words within a cell.
1. Lower case “=Lower(A2)”, this formula allows you to lower case all words within a cell.
2. Upper case “=Upper(A2)”, this formula allows you to upper case all words within a cell.
3. Proper “=Proper(A2)”, this formula allows you to upper case the first letter of each work within a cell.
Once you have used this formula, make sure you copy the cells, paste special and select values in order to copy, sort, etc. the updated cells.
--
Kate Heath is currently providing online marketing freelance services for several clients. Kate has her MBA in Marketing and specializes in PPC, SEO and e-mail marketing. She has worked in the marketing field for over 5 years. Her previous employers include AOL, Pulte Homes and GoIndustry DoveBid, where she worked with both domestic and international markets.
Twitter: @Mktg4Conversion
