6 Excel Tricks to Save Your PPC Life

By , Senior Account Manager at Hanapin Marketing

377 SHARES

It comes as no surprise to most of you, but we get a lot of mileage out of Excel in PPC. Seeing as how the AdWords and Bing Editors are capable of accepting spreadsheet uploads, and how each interface can turn out roughly a billion Excel-based reports, it doesn’t hurt to brush up on your Excel skills from time to time. While every function listed here isn’t always useful 100% of the time, when you do need them, they’re incredibly handy.

Here are some of our latest tricks (along with some old favorites) that you can use to save yourself some time.

1. VLOOKUP

I’ve been getting a lot of use out of VLOOKUP lately. This powerful function can help you correlate large amounts of data from multiple spreadsheets, streamlining both your analytical and practical tasks. How, you ask?

  • Generating 200,000 unique keyword-level Destination URLs: One of our ongoing client projects here at PPC Hero has been a massive multi-account restructure – one that requires individual keyword-level destination URLs that are classified according to their product category, location, and that are able to interface with this client’s site search function. Manually performing this task is time-consuming and mind-numbingly inefficient. It takes a little prep work beforehand (in this case, by creating a master list of Ad Group-level product category IDs, and keyword-level queries for their site search function), but this strategy allowed us to create a massive number of unique destination URLs in only an hour or so – by correlating the Ad Groups and Keywords with their corresponding entries in the master list via VLOOKUP.
  • Analyzing Max. CPC bids versus Avg. CPC on a keyword level across multiple accounts: If you’re running multiple accounts that are potentially in competition with one another, VLOOKUP can help you determine which keywords are having their average cost per click inflated by your bidding practices elsewhere. In this case, you would download a keyword report from each account and VLOOKUP the selected keywords in each account against the other’s Max. CPC and Average CPC.

2. Concatenate

This one has always been a favorite of mine – but here are a few real-life cases where Concatenate has saved my PPC life:

  • Generating 200,000 unique keyword-level Destination URLs: Yep, this is on here twice. VLOOKUP provided the first half of this equation, Concatenate the second. Once we had correlated our keywords in this restructure with their corresponding product categories, locations, and site search queries, it was a simple matter of concatenating all of these different elements together to make a fully-functional Destination URL. This is useful even for simple tasks, like manually tagging your URLs with UTM codes.
  • Ad Copy: Concatenate can assist you in creating ad copy, too! You can name your Ad Groups in such a way that you can make use of them in your ads (with a little cleanup work first) through the use of concatenate. For instance: if your Ad Group is titled “Red Shirt”, you can Concatenate your ad copy around it to say “Buy a Red Shirt Today!” The use of this formula can then extend this copy to all of the other product variants in a matter of seconds.

3. Find/Replace

This is an old standby. We’ve discussed how Find and Replace can help you in the past, but I keep coming across instances where the judicious application of Find/Replace can save you a ton of time.

  • Keyword Research and Account Buildouts: As detailed in the article above, a little bit of Find/Replace can go a long way in fleshing out your Keyword Research.
  • Ad Copy: Building off of our previous point regarding the Concatenate function, this can also assist you in writing your ad copy. For instance: if you make use of ad group titles followed by their match type variants (like “Red Shirt – Exact”), you can simply Find/Replace away the match type designation and continue on your merry way.
  • Modified Broad Keywords: Combined with concatenate, you can easily make all of your broad match keywords in to modified broad – just concatenate the “+” sign in front of your keywords, and then Find/Replace all the spaces with ” +” to create your new modified broad keywords in a matter of seconds.

4. LEN & Word Count Formulas

LEN, Excel’s character-counting formula, is obviously pretty helpful when it comes to working with Ad Copy character limitations, but it can also assist you in analyzing the efficacy of long-tail keywords.

This Word Count formula can assist you in doing the same, as well as other functions. For instance: if you’re restructuring an account in such a way that any Exact keyword that is three words or less is also to be added in as a Modified Broad Keyword, the linked formula will identify which keywords meet that criteria. Once they’ve been identified, it’s a simple matter of Copy/Paste, Concatenate, and Find/Replace to finish that task.

5. Pivot Tables

Really? You thought I wouldn’t mention these? Pivot tables are an invaluable tool to any PPC professional – you can slice, dice, and analyze data in countless ways. You can look at Quality Score, Ad Copy Performance, Average Position, Locations, Devices – literally anything that comes in spreadsheet form, you can Pivot table it for valuable insights. Use them!

6. Text to Columns

This function has become infinitely useful as I’ve started to work with Product Listing Ads. Why? Because product targets don’t download from the AdWords interface in a way that will allow you to quickly re-upload them for bid changes.

For those unfamiliar with PLAs, you have to define certain variables on your products to target them – like “brand=Product Brand”. The interface downloads that product target in to one singular cell, but the Editor requires they be split out in to two – one that defines the product targeting conditions (“brand” in this case), and another that defines the product target value (“Product Brand”).

Text to columns will help you split that cell – you can either select a specific character that denotes where to split the cell (we would use the equal sign for that here), or a fixed width at which to split it. Once you’ve completed that step, you’re good to go!

It can also help with other tasks, like splitting out the UTM tags on a Destination URL for further editing, and more. Give it a try!

There are others that we’ve talked about before – I’m a big fan of IF and SUMIF, but I’ll let you folks discuss the benefits of that below. What about you, PPC Heroes and Heroines? Any tips and tricks close to your heart that you’d like to share? Let us know in the comments and, as always, thanks for reading!

ACO_endad_Knockout

Twitter Facebook LinkedIn Google+ Email Print More
  • Adam Harahush

    Thanks Eric, I try to use Excel in different ways every day. I will definitely try your Vlookup for keyword level landing pages

    I now religiously export my data to excel and analyze using pivot tables. I like to, for scheduling purposes, arrange my conversions, CTR’s and average ad position by time of day or day of the week. I make a chart from it and I can see a pretty clear picture of when favorable clicks begin/end and adjust my scheduling from there.

    Also for negative keyword research I love the subtotal function. I export a large amount of search terms to a single column along with my kpi’s in separate columns along side the search terms. At the bottom I put in a =subtotal(sum, )function and a simple =sum() side by side for conversions or clicks or spend. I can then start using a text contains filter on my search terms and see how the results influence the cumulative total. I found some very insightful things this way!

    • EricCouch

      Sounds like some great tactics to employ – and more pivot tables are rarely a bad thing. Thanks for the input!

  • Daniel Vardi

    I agree with all your 6 and add: 1). trim (especially when you concatenated uneven number of words 2). text (to get day of week, month etc…)

    • EricCouch

      I’m a huge fan of the TRIM function, too – that Word Count formula is a combination of LEN/TRIM that has saved me a few times. Thanks for the added tips!

  • anna

    Great Post really helps to save time.

  • CT Chong

    These are very useful build-in formula, especially for Vlookup and pivot table.
    There is one very important feature that I think we can explore, VBA. It is not as difficult as it looks, they are BASIC language and can be recorded.

    Using excel for many years, I started to compile my Excel VBA codes to become Excel add-in. Excel add-in does not necessary needed to be installed (which many perceived the installation steps are cumbersome). You can double click the Excel Add-in and the extended functions provided by the VBA will be readied for use.

    For instance, with VBA you can easily add + as modifier to specific words in keywords without being mistaken as formula by the worksheet. This is my Excel add-in for BMM and other match types that can be downloaded here http://blog.wordz.co/2014/05/broad-match-modifier-and-tools.html

    I also developed a sub to perform function like Concatenate in one click. No formula, but just select the cells, click and append. The words in the each row will be appended into one keyword. I can change options to decide what should be the joint between words, instead of space.

    You may want to take a look. Hope you like it.