We’ve made a couple posts in the past about the $75 in free adCenter credits available to all AppliedI.net hosting customers:
and this is but one of the special offers and promotions available to each customer in the appliedi.net marketplace. Getting $75 in free search engine pay per click ads that show on such sites as MSN and live.com and their affiliated sites translates to your ads in front of 99 million search users and that’s GREAT! But, if you don’t effectively use it, it’s pretty much $75 of free money wasted. Fortunately, Microsoft adCenter recently released a new tool that will help you pick your keywords and better target your adCenter campaign, the new tool is called “The adCenter Add-in for Excel 2007” and details on it can be found here. In addition on January 23rd they’ll have a free webinar on the Add-in. From a little research, the tool appears to have been developed by adSage and then acquired by Microsoft (yeah, like that never happens).
About the adCenter Add-in for Excel 2007
Based on the Microsoft Keyword Services Platform (KSP) the product has several features including:
- Keyword Expansion – we have included 3 different expansion algorithms: campaign association, fast contained, and category simulation
- Keyword Search Volume Historical and Forecast – we have included daily and monthly keyword traffic data, including forecasting
- Keyword Buzz – you can find buzz keywords in our 300+ verticals
- Keyword Demographic analysis
- Keyword Geographic analysis
- Keyword Categorization
- Keyword Monetization – you can find all monetization information (CPC, CTR, etc.) for your keyword list
Getting started with the adCenter Add-in for Excel 2007
Before we get started let’s make sure you’ve redeemed your $75 in credits from adCenter by following this link.
In order to get the most out of this article/tutorial I decided to come up with a fictional example. For this example we’re going to pretend we’re a small bed and breakfast in Key West, Florida and we have a very limited budget (let’s say $75 of free PPC because we just followed this link.). We haven’t done any PPC advertising yet so we’re going to launch excel 2007 and use the keyword wizard (you’ve already installed it right?).
Decide how we’re going to generate our keywords
Using the keyword wizard option we have 3 options on how to generate our initial list of keywords. We can extract keywords from a list of websites, pull keywords from one of our existing adCenter campaigns or we could have selected a particular market vertical and picked keywords based on that.
Since our site is brand new, I’m going to extract keywords from some of my competitors websites. I’m going to launch a browser and go to live.com and do a search for our main keyword “Key West Bed and Breakfast” then get the top 3 or 4 urls for that keyword and put in the textbox for urls.
The reason I’m doing it this way is that these sites already rank very well for organic searches for the main keyword and it’s pretty likely that they have already optimized their keywords on their site for other key keywords in this area. I’m going to then click through the wizard to have it pull the keywords.
- an existing PPC campaign
- other keywords that contain the original keyword
- similar keywords
For my test I’m going to say other keywords that contain the original keyword and similar keywords. I’m going to limit it to a maximum of 10 (for speed and ease of use you’ll want to play with the confidence factor too to rule out junk keywords (you’ll see what I mean after your first couple runs)).
Once we have these keywords established we’re going to review the Keyword KPIs, the add-in is going to provide us Monthly traffic numbers based on historical and forecasted queries and also let us know how much the cost per click (CPC), click through rate (CTR), impressions and etc are for each of these keywords.
For the monetization reports we’ll get the last 30 days.
(Don’t worry we’re almost to the pay-off )
The Wizard’s Results
After stepping through the wizard we’re presented with a pretty comprehensive report detailing the estimated number of impressions, the number of clicks, the click through rate and the cost per click for each keyword.
Some of the really interesting features you’ll find:
- CTR and CPC are both tagged with RED, YELLOW and GREEN images to help you quickly identify which ones are good.
- You’ll see that the clicks and impressions have a little background bar helping to identify which keywords get more traffic than others and more clicks.
All of this information will help us to find keywords that are generating traffic and clicks and give us a good idea of how much each of these keywords will cost per click.
JESS’S TIP: I’m a firm believer in not bidding on the most popular keywords (and thus most expensive) and instead going after the keywords that the other guys are most likely not going after (and thus less expensive)). Looking at the CPC you can get an idea of this. You want to find keywords that have some traffic and a low CPC.
I also like to sort the keywords based on traffic for the previous month and then pick keywords accordingly.
JESS’S TIP: You’ll notice that although we used “key west bed and breakfast” as our main keyword, we got words like fax, photo, location, etc in our results. You can adjust the settings in the wizard to tune these results better through a little trial and error.
Using the Add-in with your existing keyword list.
In the earlier example I showed how you could generate a keyword list using the wizard and the screen captures weren’t all that exciting, I know. If you already have a list of keywords your work is going to be much easier and the functionality of this tool is really going to come alive.
Let’s stick with the keywords for a “Key West Bed and Breakfast” but just use our keywords that contain key west in them:
We’re going to highlight all our words go to monthly traffic and generate a traffic report for monthly traffic for these keywords:
So know we know what kind of traffic to expect for these words each month. Then we’ll run a separate report for monetization to get an idea of how much our clicks are going to cost.
From this report it looks like key west bed and breakfast isn’t a too bad of a word, it’s a little expensive at $0.82 per click but with a 7.6% click through rate it’s a winner. For comparison it looks like key west hotels gets alot more traffic and has an marginal click through rate. A bad keyword seems to be key west inn with a click through of only 0.09% at $1.45 a keyword.
What else can we learn?
But we can get dig in even further than that. Thanks to the new tools we have we can also look at the geographic landscape of where our searches are coming from and also the demographic landscape of who’s searching for these keywords. With this information we can use the geographic information to target where our ads are going to show and we can use the demographic information to tailor our landing pages for the proper gender and age group (and for the keyword “key west boats”, it looks like 60% of searchers are males and majority are in the age group 50-65).
Hopefully the screen shots and little tutorial have shown you just what value the adCenter Add-in for Excel 2007 can provide for your pay per click search engine campaigns. With $75 free adwords credit, it’s a great opportunity to begin experimenting with PPC advertising and see for yourself just why it’s such a popular form of online advertising today.