Posts filed under 'Uncategorized'

SEO Keyword Competition Tool In Excel

Here’s a little SEO tool built in Excel for getting an idea of how difficult a keyword will be to rank well in Google. It comes with a couple big caveats** but I think the overall idea works. Here’s how to use it:

1. Put your keyword choices in column A.

2. Use Google’s keyword tool to find the Local Monthly Searches (local means it gives you the results based on the specified location and language above the search button) for that keyword and paste those in column B.

3. Do a search for those keywords using the allintitle: operator and paste the amount of results into column C. Use the allintitle: operator so that you get a more accurate number of sites you’ll be competing with that use your keyword in their page title. Also put in a quote like this: allintitle:” so that it keeps your keyphrase together.

4. The spreadsheet is pre-formated to calculate the average popularity and competition of of those keywords once you do steps 1 -3 and will highlight in red the ones that are harder and green for the ones that are easier.

Download the SEO keyword competition tool in excel.

Let me know what you think!

*If one of the keywords is much more popular than the others it will skew the average quite a bit which all the other keywords are based on, so try using words that have similar popularity.

* The amount of search results for a given keyword is not the only factor for the competition of a keyword. The amount of backlinks to those pages is just as important, if not more so.

Add comment June 25th, 2010

3 Great Uses of Advanced Filters in Google Analytics

While going through different reprots in Google Analytics, analyzing what is and isnt working can be difficult if you have thousands of keywords, referring sites and pages in your reports cluttering everything up. You want to be able to efficiently look through them to find something useful. To do this use the Advanced Filter under that report table.

Here a few uses I have found work great…

1. Top Landing Pages sorted by bounce rate

Content > Top Landing Pages

When you pull this report and sort by bounce rate you get a bunch of pages that only have one entrance that keep you from seeing the landing pages that matter. Create a filter with Entrances Greater than 100 or whatever number suits your site. Now the good and the bad are revealed and you can pick which pages need the most immediate help. Go to this post on analyzing top landing pages for the next step to finding insights.

2. Non-Paid Keywords sorted by conversion rate

Traffic Sources > Keywords. Show: non-paid. Ecommerce Tab

When looking at Non-paid keywords, create a filter for keywords with a conversion rate of better than 10%. There is something about these keywords that align perfectly with your site. You might consider including these words in your PPC campaigns or look at including them in meta tags, include them more often in the content of applicable pages or create altogether new pages that focus on these keywrods, all of which will help SEO.

3. Referring Sites sorted by per visit value

Traffic Sources > Referring Sites. Ecommerce Tab

Similar to sorting keywords, when you’re looking at referring sites you want to analyze only the ones that have any significance instead of all those random ones that have only one visit, so filter visits by a number that’s higher than the average and voilà, you can now see just those referring sites that convert the best. (To be able to see the full referring URL in the user defined report go here.)

Add comment April 15th, 2010

Consultants Are Overrated, You Need An Analyst

I think this screen shot from insights for search tells the story all by itself. The age of hiring a “consultant” are coming to an end. Why would you hire someone with all this so-called experience when your own business, with it’s specific problems and issues, has all the data it needs to figure out what to do? What you really need is a skilled analyst to figure out what all that data is saying.

In a post by Stephen Few,

The problems that we face today, both big ones in society like the current health care debate and smaller ones like strategic business decisions, do not exist because we lack information, but because we don’t understand it. They can be solved only by developing skills and tools to make sense of information that is often complex. In other words, the major obstacle to solving modern problems isn’t the lack of information, solved by acquiring it, but the lack of understanding, solved by analytics.

Add comment March 27th, 2010

Web Analytics Planning Dashboard In Excel

Sometimes while looking at all your web analytics data it’s difficult to figure out where to focus. This dashboard allows you to take a very maco look at where your site is at, define where you want to be in terms of revenue and then set some high up goals.

This dashboard uses the following metrics: visits, conversions, conversion rate, average order value and total revenue. With some simple formulas you can see what effect a percent increase in conversion rate or average order value will have on revenue in a visual way.

So if you set your revenue goal first you can mess with the other metrics and then focus on which other metrics you want to divide and conquer. So let’s say you decide that if you could get 1,000 more visits while maintaining the same conversion rate you can reach your revenue goal. Then you can make your tactical plan to get those 1,000 more visits like working at SEO, expand your PPC keyword coverage or push your affiliates. If you see that a $3 increase in average order value will get you there, then maybe you’ll start merchandising your site differently or include more up sells – you get the idea.

Download: Web Analytics Planning Dashboard In Excel

2 comments March 26th, 2010

Zach’s Best/Worst Of 2009

Best Purchase: M-Audio Firewire Solo Runner up: Two tennis rackets and 20 tennis balls at the thrift store for $12
Best Moment: Hearing Jane laugh for the first time
Biggest Achievement: Getting hired at Crocs
Scariest Moment: Getting caught in a thunder storm while jogging, thought I was going to get struck by lightning.
Most Painful Moment: Getting jalapeño juice in my eye, thought I was going to go blind
Suckiest Moment: Getting my first driving ticket. Runner Up: Sinus infection
Biggest Drag: Going to Mt Rushmore for the 4th of July but not being able to see the fireworks because it was too foggy.
Biggest Letdown: Hot stone massage, wasn’t that great
Thing I Thought I Would Never Do: Run a half marathon (2 hrs. 19 mins.)
Funnest Moment: Being in Teenage Bottlerocktet’s music video.
Best Movies I saw: Adventureland, Star Trek, Taken, 500 Days of Summer, Office UK Christmas special, Drag Me To Hell, Man On Wire
Best Music I Got: Dillinger Four – Civil War, Teenage Bottlerocket – They Came From The Shadows, Off With Their Heads – From the Bottom, Rancid – Let the Dominoes Fall, Cobra Skulls – American Rubicon, Everything by The Lillingtons
Best Books I Read: Moneyball, Predictably Irrational, What Would Google Do, Punished By Rewards
Worst Movies I Saw: Bride Wars, Transformers: Revenge of the Fallen, Knowing, Synecdoche New York, Paul Blart, Ghost Of Girlfriends Past

Add comment December 31st, 2009

How To SEO A Contact Us Page

A lot of businesses don’t take advantage of their Contact Us page. Most Search Engine Optimization comes down to keywords in the meta title and content on the page and the typical business’s Contact Us page meta title says “Contact,” and the only content on the page is an email form, phone number and physical address. Yet there is much more you can include to get the most of of your Contact Us page.

One of the most frequently searched add-on keywords is “find a,” “how to” and “where is” – as in “how to find a good dentist,” and “where is the nearest dentist.”  These kinds of keywords are highly used in search engines by people looking for businesses but not always easy to place on the website. The Contact Us page comes in as one of the few places on the website you can use these keywords.

In the meta title and content say things like, “Looking to find a local dentist?” or “How to find a dentist can be hard…”

Also in the meta title include your business name and location.

Other things to consider including in the page content: Realize that your Contact Us page might be the first interaction someone has with your website. People don’t just enter into your site from the homepage. You’ll want to include important keywords like your location and what services/products you provide.

  • If yours is a local business say something like, “At [business name] we are here to help you with all of your [business services keywords] needs. Please contact us at our local [your city] office.
  • Give a brief synopsis of what you do. Treat it like a mini About Us page.
  • List your services. Use the keywords people use to find a business like yours.

And don’t forget to include a map, directions, picture of your business and all that good stuff.

Add comment December 29th, 2009

Short Attention Span Writing

Does anyone else ever feel like me when reading content online that too many writers take too long to get to the point? I’m annoyed by long introductions in blog posts. People feel like they need to give me a synopsis of the history of what they are about to write and tell me why what they are about to write is important. Get to the point.

Or maybe my attention span is shrinking. According to the trends tab in my Google Reader: “From your 98 subscriptions, over the last 30 days you read 3,466 items, starred 0 items, shared 67 items, and emailed 5 items.” I do most of my reading on Tuesdays with an average of 1068  items that day and my favorite time of day to read articles is at 7 p.m, with an average of 818 items. I guess it’s easy to see how the average American could consume 34 gigs a day.

Most of these barely count as reading (Photobomb,, FreeAppAlert, etc.). But in the case of actual articles, I see a title and decide quickly weather or not it’s worth reading further, in the case I do end up reading further, I skim and pick out ideas and move on. Engaging headlines help, so do pictures, charts and lists. Long winded introductions don’t.

Some examples:

Mashable: As the news industry looks to reconstruct its suffering business model, the journalists of today must reconstruct their skill sets for the growing world of online media. Because of cutbacks at many news organizations, the jobs available are highly competitive, blah blah blah.

PPCHero: Testing your ad copy and your landing pages can significantly improve your paid search efforts. Of course, building a solid keyword base, creating an optimized account structure, and executing a well-planned bid management strategy are also crucial. However, testing blah blah blah

Hubspot: Calls to action are the gateways that your visitors must click through to become leads. If your calls to action aren’t optimized and attractive to your visitors, they are less likely to complete the actions you want them to on your website. Creating a great call to action isn’t simple of course, blah blah blah

Add comment December 9th, 2009

The SEO is Overrated Debate

I read this article thats ruffled some feathers lately about how SEO is not a legitimate form of marketing and I think what the article says is mostly right. Yet there are a few things about SEO in my opinion that make it worthwhile. In the article it says,

Look under the hood of any SEO plan and you’ll find advice like this: make sure to use keywords in the headline, use proper formatting, provide summaries of the content, include links to relevant information. All of this is a good idea, and none of it is a secret. It’s so obvious, anyone who pays for it is a fool.

This statement is absolutely true. Ranking better in search engines is no secret. All SEO comes down to three things: keywords on your site, other sites linking to yours and having your site code formatted correctly. People in SEO make a lot of hubub on all kinds of other stuff  other than those three things but in reality that’s all you need to do. A writer  for Search Engine Land who replied to the above writer makes a point that I think is very valid,

Still, sometimes people have problems. And the stuff that you think isn’t rocket science — that anyone knows — is indeed a mystery to others.

The fact that this information is well known doesn’t limit it’s value and won’t keep people from wanting someone to use that knowledge on their behalf. There is plenty of information out there on how to change the oil on your car by yourself but that doesn’t make Jiffy Lube a con man. Stock brokers also come to mind as people who are paid a lot to use other people’s money in an industry over which they have no control.
Nothing is stopping businesses from learning the best practices on how to get the most out of their website but they choose to pay experts to do it for them, so what’s wrong with that?

1 comment October 28th, 2009

Earning Attention In Marketing

I think there is a lot to be said about having a marketing strategy to create content/earn attention as opposed to buying media/buying attention. I’ve gotten used to most advertisements being  avoidable if I don’t want to pay attention to them, so when there are ads that I can’t avoid, like video pre-rolls or in shows on Hulu, I find that I have half the amount of patience with them and dislike the companies more as a result. Don’t try to force me to watch something I don’t want, which to me, does more harm than good. This is a good video below explaining the idea. From the video: “how can I help you accomplish the task you are looking  to accomplish versus interrupting you and distracting you from that task?”

Add comment October 17th, 2009

Links From Twitter = Better Traffic

800px-moscow_traffic_congestion2Saw this on TechCrunch this week:

Twitter [and Facebook] “will surpass Google [as a source of traffic] for many websites in the next year.” And just as nearly every site on the Web has become addicted to Google juice, they will increasingly try to find ways to get more links from Twitter. Because Twitter equals traffic…these Twitter links “convert better” than search links because they are often pre-filtered and come in the form of a recommendation from someone you are following.

This is a good point. Now that there are no more gate keepers to filter what gets published, everything gets published and we rely on filters (bloggers, friends online) afterwords to help us find the best stuff. Twitter is a great tool for networking with like-minded people to help you find the stuff you’re interested in. I rely heavily on the opinions of bloggers that I trust to point me to where the good stuff is at. This is the best reason, so far, for getting a business involved in Twitter.

P.S. Google has added a Creative Commons filtering capability to its Image Search results to allow you to find images without having to pay a stock photo company or steal an unlicensed picture from Image Search. The option isn’t currently available on the user interface, but you can enable the filter by adding a certain parameter to your search URL (in this case, just replace “mountains” with whatever you are actually searching for)

  • For public domain images —
  • For images licensed with Creative Commons Attribution (that is, images you can use as long as you attribute the image back to the creator) —
  • For images licensed using Creative Commons Attribution Share-Alike —

I got the image above from Wikimedia Commons. Nice!

Add comment June 21st, 2009

Newer Posts Older Posts

Hi, my name is Zach Olsen, internet marketing professional, web entrepreneur & proud dad. Read more...

Get New Posts Via Email

Enter your email address:

Digital Marketing Top Posts

Web Analytics Top Posts

Display/Remarketing Top Posts

Paid Search Top Posts

SEO Top Posts