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!
