6 Excel Tricks to Save Your PPC Life
October 4, 2013
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.
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.
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.
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!
[New Webinar!] 11 Red Flags To Watch Out For When Working With An Agency
Leveraging Analytics For Remarketing: Picking The Ripest Fruit
Remarketing prevalence is well founded based on competitive returns in both volume and efficiency. With that said, this "low hanging fruit" world isn't always easy to pick.
How To Analyze Time-Of-Day Performance For Luxury Products
The importance of ad schedules varies across industries, services, and products. For e-commerce companies that sell luxury goods, ad schedules are more crucial.
A bi-weekly newsletter packed full of resources and strategies that will help make you a better PPC expert.
Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero
One Week Left to Save 25% at Hero Conf London!
You still have time to join us for Hero Conf London, 24-26 October at etc.venues St Paul's at 25% off the conference price. But time is running out.
4 Common Questions Prospects Ask Me During The Sales Process
There’s always new, hot topics and questions we get asked by prospects to see what our company’s take is, and how we go about implementing from a services perspective. Read what our most common questions are.
Mobile Price Extensions Rolling Out In AdWords
It looks like "Christmas in July" has come to Google. Recently announced price extensions are designed to help mobile marketers qualify clicks and inform prospective consumers.
[New Whitepaper!] 4 Ways To Survive PPC Campaigns for the Healthcare Market...
This whitepaper will highlight four examples of how thinking at the corners can help boost your PPC performance in the healthcare industry.
Create Visually Appealing Reports With Google Data Studio
When we talk about what tasks tend to eat up a significant chunk of time, reporting is surely mentioned. You may want to explore another option - Google Data Studio.
Liveblogging 2016 DoubleClick Announcements
Today is the day we learn more about the latest DoubleClick updates. Follow along live as we share DoubleClick's announcements as well as our opinions.
The 2016 Top 25 Most Influential PPC Experts
After a month of voting, rigorous research and data analysis, we are excited to announce the wildly anticipated list of the Top 25 Most Influential PPC Experts for 2016.
[New Webinar with AdEspresso!] 4 Effective Strategies for Facebook Lead Gen...
In this new webinar, AdEspresso and Hanapin experts discuss four effective strategies to use Facebook Lead Gen Ads for your business.
PPC Hero Launches New Site!
We're excited to announce the launch of a brand new PPC Hero! We've redesigned the site to be more search friendly with a better navigation system and more opportunity to find the right resources quicker.
How To Get More Out Of Excel Solver
Excel Solver is a fantastic tool, but most examples only cover simple marketing use cases. We’ll cover the next steps of enhancing your models and how to automate the process.