How to Use Excel to Estimate New PPC Account Performance
September 10, 2012
To start off our September PPC Hero series on how to use Excel for that next level of PPC super performance, I decided to do just that: start from the beginning! Is there a way to determine possible account performance before opening up the floodgates and turning your account on? We got this question via our Twitter account last month and the short answer is yes…and you can do it using Excel and a few simple formulas. The long answer is…well you’ll see.
First, I want to say that the following strategy is one I use to determine what I might be able to expect from a new account and I’m sure other account managers have various methods that differ from mine. What’s important is getting some kind of baseline data to look at and see if you’ll be getting anywhere close to your goals using the paid search medium, be it traffic or budget. As long as the method you’re using gets you some educated data to mull over, mission accomplished.
The last disclaimer I’m putting on this post is that the driving term here is ‘estimate.’ I never forward this information on to clients or our agency teams with a guarantee or notion of “we’ll provide you X leads and get you a Y CPL.” Never ever. All of these numbers must be qualified by the fact that they are estimates, with some assumed metrics applied in order to get some ballpark figures.
The overall point is that we’re making some educated guesses when estimating performance of an account before it goes live. Now that we’ve had that little chat…let’s dive in to the process!
- Complete some keyword research, if you haven’t already. I usually start on the client’s website and pull a handful of keywords from each page of the site or something similar. I then plug those terms I found in to the Google Keyword Tool to find some more ideas or variations of the terms I pulled manually. I suggest clicking the ‘Only show ideas closely related to my search terms’ option before running the Keyword Tool, so that you’re keeping things as tightly-themed as possible in this step.
- Pull the Keyword Tool data out to an Excel spreadsheet. While you can see all the data I’m about to discuss within the actual interface, I want to run some calculations on those numbers and possibly edit the list of terms, so it’s much easier to do if you pull all the data out in to an Excel sheet.
- Edit the proposed keyword list. This is an important step because while the interface isn’t intentionally trying to skew your data, depending on what vertical you’re getting information for, the interface could be tying keywords to your search that aren’t quite what you’re looking for. Find those terms in your spreadsheet and delete them and their data from your list. In this exercise, I pulled 61 keyword ideas into my Excel sheet, but ended up only keeping 37 of them for the remainder of the example based on relevancy.
- Total up searches and average out CPC. The data you just pulled from the Keyword Tool is going to show you approximate monthly search numbers (local and global), as well as estimated CPC’s and competition for each term. Total up your monthly search columns and average out your CPC and competition columns. This should give you a total number of searches per month across all your terms and averages for CPC and competition for all proposed keywords. That will look something like this (I hid a bunch of rows to get the column headers in the shot, FYI):
- Apply ‘industry averages’ to your totals.Now that you’ve added up searches, you’ve essentially got a guesstimate on total number of possible impressions in a given month for your term list. Using that number and your averaged out CPC, you can apply a typical or goal CTR percentage to ballpark clicks, cost and perhaps even conversions. Kind of like this (I used a 1% CTR and a 2% conversion rate on this example):
- Check the same terms in the Traffic Estimator. So you have some baseline reach and CPC estimates for a possible account structure, which is great. Putting all those same terms in the Traffic Estimator will allow you to manipulate your max CPC or daily budget to see how your traffic could fluctuate based on your budget restrictions or goals. You can also take a look at the corresponding graphs to see when additional budget (by click or day) will begin to plateau and stop bringing in proportionally more traffic.
- Take a look at the competition. Using a website like Spyfu or something similar, search out one of your top competitors and see what they’re spending and how many PPC clicks they’re getting in a day. Keep in mind these websites are very much rounding numbers and you may end up with some figures that don’t seem 100% accurate…because they probably aren’t. If you keep that in mind while looking at this information, it can give you more information about what you could be looking to spend or how much action you can expect to see as a result of launching your campaign in a similar vertical.
As I said at the very beginning, this is most definitely not the only way to get information about how your account could perform or what kind of reach/budget you might be looking at. If some of our readers have other methods, I encourage you to share them below! Take a couple different routes if you can, and average out what you find with a different method and this one. Have something interesting that happened to you in regards to running similar pre-launch estimations and finding out the actual performance varied greatly? Share you thoughts and ideas in the comments section below and also be sure to stay tuned the rest of this week for the remaining four posts on how to use Excel to your PPC benefit!
Olympic PPC Ad Strategies
How spectators consume sporting events and a massive expansion in the digital space will prove to shake up who and how advertisers reach their audiences.
Hanapin's PPC Resources Are Now Ungated!
You can now access Hanapin's whitepapers and toolkits without having to fill out a form!
View Ad Creative Through The Eyes Of Your Searchers
Learn to understand that ads do not live in the vacuum of an excel sheet and common practices should be tested.
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
[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.
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.