The SERPzone

Thanks for stopping by! Please feel free to subscribe to the RSS feed to stay current. Feel free to submit comments or send an email. Thanks for visiting!

Excel is undoubtedly the most important program I use in conjunction with my PPC work. It’s the Swiss Army Knife of online tools where I build my keyword lists, write my ad copy, prep campaigns and ad groups for uploading, and the center of my analysis of historical data that my campaigns have produced. I have to assume that others use it as often as I do for these and other tasks, so I thought this would be a good time to go over what I consider the most useful tools to use:

Concatenation When I first learned to use this tool several years ago I asked where had it been all my life! This allows you to combine the information in two cells together into one additional cell.

=CONCATENATE(A1,B1)

To automatically include a space between the words in each cell, you can include a space using quote marks between the two cell numbers, so the equation would look like =CONCATENATE(A1,” “,B1). This is best used to combine two groups of words for keyword generation

Character Counting If you’ve done anything in search you know that the character limits in search engine ads are brutally short. That’s why you have the LEN counter to help you:

=LEN(A1)

This will tell you how many characters are in the designated cell. I usually set my ads up in three rows, each with a len equation, so I can play with any of them as needed.

Trimming There have been numerous times where I’ve spent much more time than it should have taken trying to format the ideal piece of ad copy to fit within character guidelines only to later find out I had an extra space or useless character taking up room. Using the trim equation will remove all of them and leave only one space between words, and remove any unneeded spaces at the beginning or end of the cell.

=TRIM(A1)

If you duplicated my character counting example in Excel, you’ll notice that the second description line (I Love Writing PPC Ads. Don’t You?) is actually 34 characters, with an extra space at the end. If you put the trim equation in C3 for A3, the LEN count in B3 will drop to 34 characters instead of the listed 35. Try it!

Left/Right Trimming Instead of removing all floating spaces regardless of position, this formula removes the dictated number of characters from either the left or right side of the cell. This is handy when editing URLs, where part of the cell stays constant, but other parts need to be removed.

=RIGHT(A1,2)

=LEFT(A1,2)

The formula will remove the number of characters stated after the comma starting on either the right or left side of the cell, depending on which formula you use. For example, if you have a URL that looks like http://www.yoursite.com?pid=13, and you wanted to remove everything after “.com”, you would write the formula to look like RIGHT(A!,7). I count 7 characters, which includes the question mark (?) and the equals sign (=).

Word Counting Word counting is good as an analysis tool to see how many words within a keyword string perform best. I go into some detail on how to use it here.

=LEN(A1)-LEN(SUBSTITUTE(A1,” “,”"))+1

This will return only a number. For example, if your keyword was “White car”, then this formula would return a value of “2″. If your keyword is “New Used Car”, then the formula would return a value of “3″.

VLOOKUPs Vlookups–short for vertical lookups–are probably one of the most powerful analysis tools, since they allow you to compare data for the same source cell to different entries within a table of data. In search, this is usually comparing a keyword’s performance over a given length of time.

=VLOOKUP(A2,$F$3:$J$247,3,)

There are some very important issues you need to be aware of when using these formulas:

• The first entry (A2) is your source cell, or most commonly your keyword. If you have the same keyword in multiple match types, you’ll need to concatenate the keyword and the match type so the two are in a single cell, otherwise you won’t get correct data. Be sure to concatenate your data the same way over all data periods.
• Do not forget to include the dollar signs ($) in the table field, which is the group of numbers between the first and second commas ($F$3:$J$247). If you forget the dollar sign, your data table will move down with each new Vlookup. The dollar signs keep the data table static.
• The number between the second and third commas is the column number in the data table that you want to retrieve the information from. This includes the column with your source data! Count by starting with the column your source data in as column 1, and count over the number of columns until you come to the data you want to see.
• Don’t forget the last comma after the column number. The help section states that this comma is used to lookup a range in the value, but in all honesty I have no clue what that means. All I know is that it works well with the comma and doesn’t work well without the comma.
• Be sure to use Excel’s formula help section to understand how to best use this formula for your specific data.

Proper Statements It’s always good to have proper punctuation! This formula will capitalize the first letter of each word in a cell, which I think is the most attractive formatting for ad copy text.

=PROPER(A1)

Using the formula will turn a sentence like “this is a ppc headline copy” into “This Is A Ppc Headline Copy”. After the cells have been PROPER-tized, copy it and right click on an empty cell. Select “Paste Special” and then “paste values” to turn the text from a formula result into editable text. Then you can make the minor adjustments like capitalizing all of “PPC” in the example, and maybe making the “A” lowercase for proper formatting and grammar.

Do you have some other tricks? Share them with the group and spread the knowledge!

Popularity: 32% [?]

Tags: Back to Basics, General PPC, MSN, Yahoo!, adwords, google // 1 Comment »

I’ve been a bad blogger, and for that I’m sorry.  The site is still active, and will be back to normal updates again in the near future.  Between my day job, and planning a wedding, I just haven’t had time to write like I used to. 

My bride-to-be and I are headed off to a tropical beach with umbrellas in the drinks very soon.  You can expect to see regular posts again around the middle of June. 

Popularity: 25% [?]

Tags: General PPC // Add Comment »

One of the most frustrating things I can imagine while managing small PPC accounts is getting priced out of a keyword that has the potential for a lot of traffic and lots of sales. If you’re in that situation, there are ways to compete by getting maximum exposure and going beyond Google’s quality score. The secret: Advanced day-parting.

The original purpose of day-parting was to let advertisers turn their ads off when they don’t want ads within a campaign to be seen. This could be due to call center hours, or perhaps a drop in the conversion rate. Here’s a better idea: Use day-parting across multiple mirrored campaigns to make sure you get exposure at different parts of the day. Let’s walk through it step-by-step:

1) Go into Google’s reporting interface and run an hourly report regardless of date for a sufficient time period at the campaign level, preferably for a single campaign. Be sure to check the boxes labeled “impression share”A sufficient time period is usually at least 30 days, but possibly up to 90, depending on your conversion volumes.

2) Export to Excel, and sort by hour of day. You should have 24 entries, ranging from 0-23. You need to look for a couple things:

• At what hours of the day is your impression share lowest?
• At what hours of the day is your conversion rate the highest?

Your impression share is what percentage of search queries are you getting for the keywords within the given campaign. Low numbers mean you’re losing out on opportunities. High numbers mean you show up often.

3) For example’s sake, let’s say that our busiest hours are 6:00-7:00am, 12:00-2:00pm, and 5:00-7:00pm on weekdays. For these hours we’d set our ad scheduling to look like this:

The above example is an extreme where you only run the ads during these peak times. However, if you’re using advanced day-parting also allows you to change your max bids during these different periods within a day. So if you know the 12-2 hour is where you’re going to make all your money, you can dictate that those bids automatically be raised any percentage over the Max CPC at the keyword level that you’re comfortable with. During the other hours of the day you can run the ads at a lower percentage than the max CPC, so you don’t get charged as much per click. Keep in mind that your average position will decline.

This seems pretty thorough as-is, so why use mirrored campaigns? For some really competitive keywords, you may not even last through this small peak hours to get the exposure you’re looking for. In those cases you need more than one campaign with different daily budget caps to make sure your traffic is getting spread across the times that you want, and not whenever Google thinks its best.

Expect to spend 2-3 hours of research per campaign in reports before deciding which hours are best suited for your campaign. Due to that time requirement, I’d recommend doing this on only your top 1 or 2 campaigns, and only if you see your daily budget being maxed out constantly, but can’t afford to raise it. Another good sign is when Google suggest that you should at least double your daily budget to get max exposure.

Remember, the goal is not to get more impressions or clicks for the sake of getting traffic, but instead to maximize conversions at peak times of the day. If you execute this tactic and you’re not seeing an overall increase in sales within a month or so, don’t bother with the extra management. Kill the test and go back to what you were doing originally.

Popularity: 42% [?]

Tags: Back to Basics, General PPC, google // 2 Comments »