My Top 5 Excel Tips for PPC Managers

By ,

494 SHARES

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!

ACO_endad_Knockout

Twitter Facebook LinkedIn Google+ Email Print More
  • http://www.takingoffthesuit.com ExSuit

    Nice share. I havn’t gotten too deep into PPC yet, but I use alot of these same excel functions to help keep my SEO campaigns organized!

  • Pingback: My Top 5 Excel Tips for PPC Managers | Iuvo

  • http://www.ecommerce-web-developers.com Ecommerce Website Developers

    Well, I do not know much about PPC, However, I know basic of Excel as you said what the common person would use it for. I love to know more about this topic. I hope to see more article related to this. Thanks for sharing the information with us.

  • Michael Taylor

    The thing I have used most in PPC is pivot tables. They allow you to drag and drop different metrics into different areas of the table to quickly compare historical data without the need for endless vlookups or other formulas.

    They are also pretty useful for making more automated reports if linked to a data table.

    Not things I had ever used until about ten months ago but I use pivot tables every day now!

    • http://fendorf.dk Jackie Frandsen

      Exactly pivot rocks. I even build my Campaigns for the use of pivot.

      Imagine that you sell mobiles, and have the same campaign with the different kind of mobiles like:
      Nokia N8, Nokia E72, HTC wildfire, HTC Desire, Sony Ericsson Elm, Sony Ericsson Neo

      Simple call
      the adgroups: Nokia_n8, Nokia_E72 etc. then you can separate the producer (Nokia) with the mobile by cutting the underscore ‘_’. In the pivot you can see the performance of all Nokia’s, HTC’s etc.

      Thanks for a great article

  • http://www.calculatemarketing.com/blog Alan Mitchell

    I also find the LEN(cell) function extrenely useful for counting characters when writing ad descriptions in Excel

    • Jessica Cates

      Hi Alan,

      The LEN function is definitely another one of my favorites. Thanks for reading
      :]

  • http://twitter.com/askppc Cleofe Betancourt

    Using Excel daily, I can definitely relate to your article and the frustration sure to follow without these shortcuts! Thank you for sharing this with your readers.

  • Pingback: April Greatest Hits | The Adventures of SEO Boy®

  • Pingback: Ryan’s PPC Playlist-11 Best PPC Reads for May | Amplify Interactive

  • Deanna Hatfield

    I love learning new Excel short cuts – thanks so much for sharing!

  • Pingback: Letter from the CEO – “Talent wins games, but team work and intelligence wins championships.” | The Adventures of SEO Boy®

  • Sara

    I also find Filters very usefull, automatic subtotals and Pivot Tables.

  • http://www.threedeepmarketing.com Three Deep Marketing

    A little more advanced Excel function is using the IF statement to evaluate anything you want.  For example, if CPA > $x OR CONV = 0 AND COST > $x, change the Max CPC Bid to whatever… else, leave the max CPC bid alone.

    So an example formula would look like:

    =if(OR(CPA>x,AND(CONV=0,COST>x)),(Reference MAX CPC Bid*0.9),(Reference Max CPC Bid))

    That little formula says: If the CPA for a given keyword is greater than x or conv = 0 and cost is greater than x, reduce the Max CPC bid by multiplying it by 90%, else the bid is fine and leave it alone.

    • Shawn McCarthy

      I like that one. I’ll have to give that a try. I would normally use a crazy amount of nested IF statements in place of this clever little gem. 

  • Pingback: EXCELlent Series: Using Excel To Boost PPC Performance | PPC Marketing Advice

  • Pingback: EXCELlent Series: 5 MORE Top Excel Tips for PPC Managers | PPC Marketing Advice

  • Pingback: Letter from the CEO – “Rolling, rolling, rolling…keep them doggies rolling!” | PPC Marketing Advice

  • http://www.mactonweb.com web design bangalore

    I got fantastic information It is really help full Thanks for sharing  

  • Pingback: Our Greatest Hits for June 2011 | PPC Marketing Advice

  • http://twitter.com/coxen00 Chris Coxen

    Does anyone know of a good excel tool than can create multiple ad version in bulk quantity?

  • fu2ion123

    Thanks for sharing tips for ppc

  • Pingback: 35 PPC ideas for 2012 « Digital and Data-driven Marketing

  • http://serialsmallbusiness.com/ Graham Hunter

    I would love something a little more advanced. I use pivot charts and would be really interested to know what metrics people look at using pivot charts and how they use them to optimize campaigns

  • http://twitter.com/LALyndaD81 Lynda Marie Doria

    In the Yahoo Bing Network, we do accommodate DKI just as it functions in Google. Therefore, no extra steps need to be taken to implement it. We do, however, recommend utilizing Param2 values if you are bidding on misspellings or reverse word-order keywords because DKI will not correct that for you.

    • http://www.hanapinmarketing.com PPC Hero

      Thanks for passing that along!

  • http://twitter.com/GnosisArts Gnosis Media Group

    Thank you for including PROPER(“”) for those of us who are Excel-Challenged. I’ve been looking for something like this forever! I’ve been using Word’s SHIFT + F3 command for this. But it doesn’t work as reliably as Excel. Saweet!

  • Eric Jones

    cant believe nobody has mentioned my favorite, SUMIFS().

    similar to VLOOKUP, but if you are using only numbers SUMIFS is basically a vlookup where you can include multiple criteria easily.

    i use SUMIFS() more than just about anything else. as long as your data is stored in an organized, numerical way there is a lot you can do with this formula.

    • http://www.hanapinmarketing.com PPC Hero

      Eric, this is a great tip. Thanks for passing it along. We’ve been using SUMIF for a while, but adding the S makes it so much better. If we ever update this post that is a great one.

      Thanks!