This post is in response to you, our readers! In case you haven’t noticed, we’ve added a box at the bottom of the page where you can write to the PPC Hero team with ideas for us to write about next. This is great as it allows us to give the people what they want and learn things that maybe we hadn’t thought of visiting before. Thanks for all your responses! Now on to my favorite Excel tricks…
When I first started working in PPC, I had some experience in Excel, but nothing beyond what the common person would use it for. Sure Excel can add things together, and help me budget, but there’s so much value in this program that doing PPC without it is simply ridiculous. Now a days, when I’m teaching people how to do a PPC task, I make it a point to make sure I’m contributing any Excel knowledge that I can to make it easier for them from the beginning. The following Excel tools are things that I use on a regular basis that I think would have saved me a lot of time. As always, there’s still more to learn – do you have an Excel trick that you absolutely love? Share it in the comments section!
1.) The VLookup Function: I love this little gem. The Vlookup is no secret to PPCers, but it’s something that I didn’t know right away, and I’m thankful to have it in my arsenal. This function is highly useful in PPC. For one, you can use it to analyze your data over two different time frames or you can use it to compare a list of new keywords that you want to add to your account to keywords already in your account.
When an account starts to go on a downward spiral for no apparent reason, the best place to start is looking at where you were a month ago, six months ago, and a year prior. Sometimes in PPC, we do things to accounts that can have long-term consequences that we didn’t think of beforehand. Maybe you turned something off that was never activated again? Maybe more competition has turned off in the market place and you need to bid more competitively. Taking the time to pull data together and look at it side by side is the best place to start your research and come up with an action plan. Check out Amy’s article on PPC Hero for some tips on analyzing a PPC account with a historical performance review.
It’s really easy to accidentally add duplicate keywords in your account. For the most part, if these keywords are in separated campaigns that are geotargeted differently and focus on separate areas (search vs. display), they won’t interact with one another. However, adding duplicates to campaigns that are targeting the same area is a big no-no as you can end up increasing your own costs as your keywords compete with one another for positions. By using a Vlookup function to compare additions to current keywords, you can avoid this conundrum and have a better functioning account overall.
2.) Find and Replace: I’m pretty sure the first time I had a big list of Google tagged URLs that needed to be converted for use in Bing, I just generated a whole bunch of brand new URLs. Live and learn, I suppose. If you’re new to PPC and making the same sort of mistake, save yourself some time and use find/replace to your advantage. Download all of your URLs from the Adwords Editor and replace Google for Bing, along with any other search engine specific tagging you’re using in your URLs, then spot check them. It’s also beneficial to do a find for anything that might break your URLs (like slashes, commas, apostrophes, ampersands, etc.) so you can replace them. Badabing, Badaboom – upload into Bing.
Find and Replace is also super useful in creating modified broad terms for Google. It’s a bit time consuming, but it’s a whole lot better than manually adding a ‘+’ before each word. You just highlight your keyword column and type the word for Excel to find, then replace it with a + in front of it. Acquisio has a tool for creating modified broad, which works pretty well and bypasses Find/Replace altogether. Unfortunately, I can’t get it to export correctly as it downloads as a .tsv.
3.) Concatenate: Here at Hanapin, we have a different URL builder for just about every client. This way, we can generate hundreds of URLs quickly with just a few copy/paste commands. Creating individual URL builders would be really difficult without the help of the Concatenate Function.
Concatenate essentially combines data from separate cells to form one combined product. As you can see in the example from one of our URL builders below, it compiles all of the relevant information we need to track conversions and, for lack of a better word, mashes it all together. In the example, we’re keeping track of leads that come from PPC, and specifically which keyword, campaign, ad group, and landing page they came from.
4.) Character Case Conversion Tricks: Snaps to Bethany for this trick. One day, I found myself wishing I had a tool that could easily change my character cases, and she delivered on it. You can use this for your Bing keywords, or when you need to quickly make changes to ad text in Excel.
=PROPER(cell) – Capitalizes the first letter of each word in a cell
=UPPER(cell) – Makes every letter uppercase in a cell
=Lower(cell) – Makes every letter lowercase in a cell
Most people are familiar with Dynamic Keyword Insertion in Google, where you use a special command in your ads to pull the actual query into an ad to make it more relevant to searchers. Google makes it very easy to capitalize your text for DKI. For example, in Google, {KeyWord:ad headline} will capitalize the first letter of each word for you, so your ad will look like (assuming it goes to default text because the keyword was longer than 25 characters):
Bing is a bit different. When you use dynamic keywords in Bing, the way you capitalize {Keyword} won’t make a difference as to how your headline appears. One way to combat this technicality is to upload your keywords into Bing in the case you would want them to be inserted in an ad.
5.) Conditional Formatting: This tool goes hand-in-hand with the Vlookup function. When you use a Vlookup to conduct a historical performance review of your account, try using conditional formatting to quickly highlight increases or decreases in your data from the previous time frame. Instead of searching for the data manually that has changed, you can simply color code. In the example below, I’ve set the condition to anything in the column less than zero to be highlighted in pink. From here, I can begin making decisions on how to proceed.
Well, there you have it! These are my top 5 Excel tools that I use on a regular basis for my PPC tasks. I hope that if you’re not currently implementing any of these, that you’ve learned something new!
