Blind Five Year Old

There are plenty of SEO metrics staring you right in your face as the folks at SEOmoz recently pointed out.

SEO Metrics Dashboard

I’ll quickly review the SEO metrics I’ve tracked and used for years. Combined they make a decent SEO metrics dashboard.

SEO Visits

Okay, turn in your contractor SEO credentials if you’re not tracking this. Google Analytics makes it easy with their built in Non-paid Search Traffic default advanced segment.

However, be careful to measure by the week when using this advanced segment. A longer time frame can often lead to sampling. You do not want to see this. It’s the Google Analytics version of the Whammy.

Alternatively, you can avoid the default advanced segment and instead navigate to All Traffic -> Search Engines (Non-Paid) or drill down under All Traffic Sources to Medium -> Organic. Beware, you still might run into the sampling whammy if you’re looking at longer time frames.

SEO Landing Pages

Using Google Analytics, use the drop down menu to determine how many landing pages drove SEO traffic by week.

I’m less concerned with the actual pages then simply knowing the raw number of pages that brought SEO traffic to the site in a given week.

SEO Keywords

Similarly, using the Google Analytics drop down menu, you can determine how many keywords drove SEO traffic by week.

Again, the actual keywords are less important to me (at this point) than the weekly volume.

Indexed Pages

Each week I also capture the number of indexed pages. I used to do this using the site: operator but have been using Google Webmaster Tools for quite a while since it seems more accurate and stable.

If you go the Webmaster Tools route, make certain that you have your sitemap(s) submitted correctly since duplicate sitemaps can often lead to inflated indexation numbers.

Calculated Fields

With those four pieces of data I create five calculated metrics.

• Visits/Keywords
• Visits/Landing Pages
• Keywords/Landing Pages
• Visits/Indexed Pages
• Landing Pages/Indexed Pages

These calculated metrics are where I find the most benefit. While I do track them separately, analysis can only be performed by looking at how these metrics interact with each other. Let me say it again, do not look at these metrics in isolation.

Inevitably I get asked, is such-and-such a number a good Visits/Landing Pages number? The thing is there are no good or bad numbers (within reason). The idea is to measure (and improve) the performance of these metrics over time and to use them to diagnose changes in SEO traffic.

Visits/Keywords

This metric can often provide insight into how well you’re ranking. When it goes up, your overall rank may be rising. However, it could also be influenced by seasonal search volume. For example, if you were analyzing a site that provided tax advice, I’d guess that the Visits/Keywords metric would go up during April due to the increased volume for tax terms.

Remember, these metrics are high level indicators. They’re a warning system. When one of the indicators changes, you investigate to determine the reason the metric changed. Did you get more visits or did you receive the same traffic from fewer keywords? Find out and then act accordingly.

Visits/Landing Pages

The Visits/Landing Pages metric usually tells me how effective an average page is at attracting SEO traffic. Again, look under the covers before you make any hasty decisions. An increase in this metric could be the product of fewer landing pages. That could be a bad sign, not a good one.

In particular, look at how Visits/Keywords and Visits/Landing Pages interact.

Keywords/Landing Pages

I use this metric to track keyword clustering. This is particularly nice if you’re launching a new set of content. Once published and indexed you often see the Keywords/Landing Pages metric go down. New pages may not attract a lot of traffic immediately and the ones that do often only bring in traffic from a select keyword.

However, as these pages mature they begin to bring in more traffic; first from just a select group of keywords and then (if things are going well) you’ll find they begin to bring in traffic from a larger group of keywords. That is keyword clustering and it’s one of the ways I forecast SEO traffic.

Visits/Indexed Pages

I like to track this metric as a general SEO health metric. It tells me about SEO efficiency. Again, there is no real right or wrong number here. A site with fewer pages, but ranking well for a high volume term may have a very high Visits/Indexed Pages metric. A site with a lot of pages (which is where I do most of my work) may be working the long-tail and will have a lower Visits/Indexed Pages number.

The idea is to track and monitor the metric over time. If you’re launching a whole new category for an eCommerce site, those pages may get indexed quickly but not generate the requisite visits right off the bat. Whether the Visits/Indexed Pages metric bounces back as those new pages mature is what I focus on.

Landing Pages/Indexed Pages

This metric gives you an idea of what percentage of your indexed pages are driving traffic each week. This is another efficiency metric. Sometimes this leads me to investigate which pages are working and which aren’t. Is there a crawl issue? Is there an architecture issue?  It can often lead to larger discussions about what a site is focused on where it should dedicate resources.

Measure Percentage Change

Once you plug in all of these numbers and generate the calculated metrics you might look at the numbers and think they’re not moving much. Indeed, from a raw number perspective they sometimes don’t move that much. That’s why you must look at it by percentage change.

For instance, for a large site moving the Visits/Keyword metric from 3.2 to 3.9 may not look like a lot. But it’s actually a 22% increase! And when your SEO traffic changes you can immediately look at the percentage change numbers to see what metric moved the most.

To easily measure the percentage change I recommend creating another tab in your spreadsheet and making that your percentage change view. So you wind up having a raw number tab and a percentage change tab.

SEO Metrics Analysis

I’m going to do a quick analysis looking back at some of this historical data. In particular I’m going to look at the SEO traffic increase between 3/23/08 and 3/30/08.

That’s a healthy jump in SEO traffic. Let there be much rejoicing! To quickly find out what exactly drove that increase I’ll switch to the percentage change view of these metrics.

In this view you quickly see that the 33% increase in SEO traffic was driven almost exclusively by a 28% increase in Keywords. This was an instance where keyword clustering took effect and pages began receiving traffic for more (related) query terms. Look closely and you’ll notice that this increase occurred despite a decrease of 2% in number of Landing Pages.

Of course the next step would be to determine if certain pages or keyword modifiers were most responsible for this increase. Find the pattern and you have a shot at repeating it.

Graph Your SEO Metrics

If you’re more visual in nature create a third tab and generate a graph for each metric. Put them all on the same page so you can see them together. This comprehensive trend view can often bring issues to the surface quickly. Plus … it just looks cool.

Add a Filter

If you’re feeling up to it you can create the same dashboard based on a filter. The most common filter would be conversion. To do so you build an Advanced Segment in Google Analytics that looks for any SEO traffic with a conversion. Apply that segment, repeat the Visits, Landing Pages and Keywords numbers and then generate new calculated metrics.

At that point you’re looking at these metrics through a performance filter.

The End is the Beginning

This SEO metrics dashboard is just the tip of the iceberg. Creating detailed crawl and traffic reports will be necessary. But if you start with the metrics outlined above, they should lead you to the right reports. Because the questions they’ll raise can only be answered by doing more due diligence.

Infographics can be a great way to generate backlinks. But the prevalence of infographic spam threatens this link building technique.

Infographic Spam

You’ve undoubtedly seen infographic spam. The hallmarks of infographic spam are a mediocre graphic from multiple data sources with a link back to a tenuously related website.

This TSA infographic is linked to a criminal justice degree site. Related? Barely. Of note, nice going keeping the utm parameters in your source link.

Here’s one about the sexual revolution that’s linked by to a site offering online counseling degrees. Related? No.

Pay per Infographic

Oh, did I mention that they pay sites to post infographics? Earlier this year Aaron Wall wrote about link buyers outing themselves. Well here’s a similar example.

This is an actual post where the site owner admits to posting infographics for money and asking if readers mind. The verdict? Readers are fine with it. But I’m not, and neither should you.

The sites that generate this garbage are usually making a lot of money – most often coming from the lucrative education vertical. But what about this guy? What about the individual site owner? He’s making just $130. I don’t blame the guy really. He’s just trying to break even on this site and maybe make a little bit in the bargain.

Paid Links vs Paid Infographics

In my mind, there is a big difference between paid links and paid infographics. Paid links are essentially static. You’re renting the trust and authority of that site and probably getting a bit of traffic as well. There’s no expectation of amplification. Said another way, paid links aren’t viral.

Paid infographics are problematic because they are engineered to create additional installations through social distribution and embeds.

The sole purpose of infographic spam is to drive keyword specific anchor text from multiple domains. Domain diversity anyone?

In addition, when buying links you’re usually looking for links from sites that are similar in topic. You’re seeking to build your profile in a certain neighborhood.

As an aside, this is exactly what you’re doing if you buy a directory listing. You’re buying the trust and authority from a relevant section of that directory. I’m still not sure why one is verboten and the other is okay, but that’s a topic for another post. While I don’t actually recommend buying links, I don’t find the transaction to be that disturbing.

Nevertheless, there’s no such targeting involved in paid infographics. It’s the Sherman’s March of link building.

Algorithm Blues

It doesn’t seem like the algorithm is currently capable of finding infographic spam. I can completely understand why it might be difficult.

The link graph may not be that different for paid and non-paid infographics. Because once an infographic gets out in the wild, the viral component takes over. Users don’t care about the little bit of HTML at the bottom of the infographic, they just think it’s interesting. As the poll above showed, even when told, users aren’t running to Google to file a spam report.

You can’t use anchor text bombing as a signal since any good SEO is going to use proper anchor text in an infographic.

Now, perhaps you could work to determine whether the source sites (where it first appeared) between infographics differed. Yet, the example I provided mentions that ‘many’ of the infographics posted of late were paid. So, he might be mixing in ones found and enjoyed with ones where he’s getting paid. So is his site a poison source site or not?

In the end, maybe we need a little human intervention and outreach. A couple of emails, a bit of sleuthing and some Law & Order type of immunity deals and I think you’d locate the sites and intermediaries who were polluting the infographic space. I’m not advocating going after the posting sites or contract designers but instead the sponsors of this content.

Am I entirely comfortable with Google using their nuclear option (the dreaded penalty) in this type of subjective manner? No. But lately I’m seeing way too much getting through the algorithm (both infographic and otherwise) and relying on users to report spam doesn’t seem like enough.

Do you care about infographic spam? If so, what would you do to stop it?

I recently fired up a new AdWords campaign for a client that was perfect for Dynamic Keyword Insertion. But it didn’t seem to be working, so I emailed AdWords support and got this answer.

Dynamic Keyword Quality Score

First off, my name isn’t John. This isn’t the first time I’ve been addressed like this though. Since I go by AJ many customer service folks seem to transpose the J onto my last name (Kohn) and come up with John. Now, at one point in my youth I told my parents I wanted to be called Tom after the cat in Tom and Jerry, but I’ve never used John as a pseudonym.

What was more surprising was the fact that Dynamic Keyword Insertion as a feature is gated by Quality Score. Was I supposed to know this? Nowhere does it mention that you must achieve a certain Quality Score to use Dynamic Keyword Insertion. But more to the point, does it make any real sense?

AdWords (il)Logic

The advice they have is to create static text ads (for the nearly 10,000 keywords I have) until such time as they grant me the ability to use Dynamic Keyword Insertion. You tell me to make it more relevant. I can do that … by using Dynamic Keyword Insertion. But I can’t use that until I make the ads relevant. It’s a Catch-22.

I wrote back explaining that it seemed like an onerous amount of work. You really want me to create static ads until the Quality Score increases to a point where you turn on Dynamic Keyword Insertion?

Yeah, still not John. But here’s the kicker. This is a new campaign! Yet users are not finding my ad very relevant to click on? So – how did you figure that out before it was launched? (Okay, I actually know they’re probably using an account or domain Quality Score. But tell me if that’s the case.)

And lets talk about relevancy. While I can’t divulge the exact keyword set, it is very specific. This ad is relevant if you’re searching for any of these keywords.

Making Relevance Difficult to Achieve

I’m still trying to figure out why Google would decide to gate this feature. Dynamic Keyword Insertion is a fairly advanced strategy. You’re not going to get a lot of small businesses using it, particularly if you’re playing these types of games.

Using the keyword in the ad is a pretty good way to make the ad more relevant. That seems to be one of Google’s goals. The advice is essentially to make a static ad that would be the same as the dynamic ad. So … Google wants the ad to look that way and include that keyword, they just don’t want me to use the feature that makes that easy to do.

It makes about as much sense as a smiling Spock.

Can anyone give me a good reason for this policy?

The other day I followed a ping back to Elsewhere. There I found a fantastic blog commenting policy.

I’ve turned “nofollow” back on for links in comments.I can find a good WordPress plugin from Collectiveray.com that allows me to disable this on a per-comment basis, I will manually remove that on comments I think deserve it.

Use your name, nickname, pseudonym, handle, or other personally-relevant identifier in the “Name” field. Your name is not “Miami Attorneys” or “Solar Panels” or “Bingo Games”. If you use a product or site name as your own name and it makes it through the spam filters, I will manually delete it. This applies to obvious keyword linking, too. The keyword you’re trying to boost is not your name. If you use it as your name, I will remove your comment. Use your own name, or something reasonably name-like.

Linking to the site you’re promoting is fine, as long as it’s relevant to the post or other comments in the thread. If I feel it is spammy, I may delete the link or the whole comment, depending on my mood. Your link will have rel=”nofollow” applied, unless I think it deserves otherwise.

If you are not a spammer or SEO practitioner, you probably don’t know what any of that meant. Don’t worry about it; it doesn’t apply to you.

This is why so many people hate SEO and you really can’t blame them either. The sad truth is that most people lump SEO in with this obnoxious form of blog commenting spam. This is what they see, despite the reality. Which got me thinking.

Dread Pirate SEO

Most people probably think of SEO as the Dread Pirate Roberts.

If you’re familiar with The Princess Bride (and you should!) then you know that the Dread Pirate Roberts was thought to be an incredible villain. What they didn’t know (among other things) was that the Dread Pirate Roberts wasn’t just one person.

So you can think of the Dread Pirate SEO declaring that his name is Miami Attorneys on blog after blog, taking no prisoners in his quest for keyword anchor text.

SEO Must Die

Of course this provokes a rather normal reaction.

Yes, a phalanx of Inigo Montoyas rise up to call for the head of the Dread Pirate SEO. They seek to battle him at every turn, not knowing the truth behind the mask.

Inconceivable!

The problem is what most people see looks like (and often is) trickery. Yes, many in our profession are true Dread Pirate SEO. Compounding this is the fact that every good SEO does know some tricks. Not only that, but many like to poke and prod the algorithm in an effort to understand what will really work.

SEOs enjoy this battle of wits. And we like to win. However, it may give many the wrong impression of our true purpose.

We’re Westley

Dread Pirate SEO is actually … a good guy!

Good SEO is simply ensuring that your content finds the right audience. It would be nice if a good site or great content would immediately rank for the right queries. But that’s not what happens, despite the Google dogma. Instead, SEO is there to storm the castle and ensure that your time and effort is rewarded with the right traffic. That your site and content are seen by the right people.

Will most people ever think of SEO as their ally? Probably not. That just happens in the movies.

I’m not the biggest fan of Facebook for personal use. Instead, I hang out at FriendFeed. The main reason is because FriendFeed revolves around content instead of people. The secret sauce is the Friend of a Friend (FoaF) feature. FoaF lets me see content that my friends commented on or liked. So instead of my world view being limited to just my friends, I let my friends bring interesting content to me from other people.

Using people as filters. Information discovery at its best.

Facebook Friend of a Friend

The other day I visited Facebook and lo and behold I saw something different … yet familiar.

I haven’t liked Dexter. He’s not my friend. But I’m seeing Dexter’s status because Oguz and Louis (who are my friends) liked it. The beauty of this is that I enjoy Dexter (though I’m way behind and am only on season 3.) Sure enough my friends (my filters) brought me the ‘right’ content.

Here’s another instance. I’m not connected to Jason Falls. Sure I know who he is but we’re not buds. Yet, I’m seeing his status update because Susan and Louis commented on it. Once again, it’s content that is interesting to me. I’ve been talking about follow and friend abuse for a long time so it’s great to see others pruning their connections.

And while I’ve used two status examples, I’ve seen FoaF on photos and links as well.

I’d have to have my head in the sand not to know who Loic is, but I’m not friends with him. I see his link because Oguz commented on it.

The FriendFeedification of Facebook

Facebook’s implementation of FoaF as well as duplicate detection and aggregation all make me like Facebook a lot more. Suddenly, I can use Facebook like I use FriendFeed. In fact, it may actually work better since there are (sadly) so many more people on Facebook.

I’ll likely be spending more time on Facebook. That’s something few people – myself included – thought they’d ever hear me say. The only (big) thing remaining is lists so I can create different views of the content my friends and their friends bring me.

Oddly, I’m more confident this will happen given the continuing FriendFeedification of Facebook.

(From time to time I post things outside of search, marketing and social media. This is one of those posts. So if that’s not what you’re looking for, this is your time to bail.)

For my birthday I received a Kurt Kinetic Bike Trainer. I’ve been on it a lot lately, spinning away in my new garage while listening to a variety of music. Yesterday it was Billy Idol’s Vital Idol, a collection of extended versions and remixes of some of his more popular songs.

I’m not exactly the biggest Billy Idol fan, but he – and this album in particular – captured a moment in time for me. I recall driving down to the shore (Long Beach Island to be exact) during the height of summer. I left at 3 or 4 in the morning to beat the traffic that inevitably stacked up on the two lane highway. It was rather desolate, my headlights making a small hole in the darkness as I flew past the gnarled Pine Barrens on Route 72.

Vital Idol was in the cassette player. The music was loud, drowning out the roar of the wind through the open windows. The adolescent sex fueled lyrics, thumping beat, synthesized surround wash and Steven Stevens blazing guitar riffs seemed to make the car fly.

What I remember is being in that moment. Being completely content – not complacent – but simply enjoying the small horizon ahead of me, arriving in LBI and doing whatever felt right once I got there. The future was small, yet large. What happened next? I simply wanted to find out.

Return to splendor

I believe that state of mind is important to retain. Yet, as we get older we accumulate responsibilities (families, mortgages and retirement portfolios) and a history of experience (success and failure) that makes it difficult to do so.

Instead of being consumed with the future and protecting ourselves, what if we let things happen? What if we got that teenage swagger back? What if we didn’t dwell on that track-record of experience, the knowledge that failure could lurk around the corner?

Am I talking about risk taking? Sort of, but not really. Am I talking about ‘thinking out of the box’? It’s more about throwing the box out altogether. That box is how things are supposed to be, but why are they supposed to be that way? Who says!

Paul Buchheit has written eloquently about this on a few occasions, helping to remind me not to live based on fear or lack of imagination.

It’s a nice day to start again

Thankfully my past is littered with examples of starting again. When I knew advertising wasn’t going to be my career, I quit. No job to go to, just the knowledge that it was time to move on. I did data entry temp work for PBS in Alexandria, Virginia until I got a job in fundraising.

When that job disappeared, a retroactive job freeze of all things, I took it as a sign. I drove cross-country from D.C. to San Diego with girlfriend (now wife) and cat in tow. When I tired of fundraising and wanted to get into Internet marketing I moved to San Francisco.

The Web 1.0 bubble burst and I wound up back in fundraising at De La Salle High School. One of the Lasallian teachings is that you can only take the step right in front of you. You can only make the next best decision. Trying to forecast the future is fruitless because each decision leads to something completely different.

I’m not a religious person, but this resonated strongly with my own personal beliefs. So when I broke down sobbing at an assembly, mourning a miscarriage and depressed over my job, I knew it was time to really do something.

And I did. I got back into Internet marketing. In fact, I got into search marketing. And see where that led?

Sweat, Sweat, Sweat

Here I am today, a husband and father with a new mortgage. The reflex for economic stability is huge. Yet, I began consulting because I wanted to spend more time at home with my family. I had no idea if it would be successful. But it was and I couldn’t be happier.

So now I spin on my trainer. I sweat. I sing along with my 17-year old self. My heart is full and I look forward to what comes next.

I tell myself I’ll do the Mount Diablo Challenge again in 2011. I tell myself that I’ll continue to work from home and spend time with the family. I tell myself that I’ll figure out ways to leverage my growing consulting business. There will always be people who say you can’t, but if you’re one of those people, you’re doomed from the start.

I had chronic appendicitis as a child. I’ve been hit by a car while bicycling. I’ve been mugged. I was unemployed for nearly a year. I dealt with the heartbreak of a miscarriage. I went through a crisis of confidence. Do those experiences make me stronger? I don’t know. But I know I have my sneer back.

It’s a sneer that says I’m going to enjoy the life I create … and you can’t stop me.

Don’t lose that Billy Idol sneer

One of the most common Google Analytics questions I get is to explain the difference between bounce rate and exit rate. Here’s what I hope is a simple explanation.

Bounce Rate

Bounce rate is the percentage of people who landed on a page and immediately left. Bounces are always one page sessions.

High bounce rates are often bad, but it’s really a matter of context. Some queries may inherently generate high bounce rates. Specific informational queries (e.g. – What are the flavors of Otter Pops?) might yield high bounce rates. If the page fulfills the query intent, there may be no further reason for the user to engage. It doesn’t mean it was a bad experience, it just means they got exactly what they wanted and nothing more. (I was always partial to Louie-Bloo Raspberry or Alexander the Grape.)

A high bounce rate on a home page is usually a sign that something is wrong. But again, make sure you take a close look at the sources and keywords that are driving traffic. You might have a very low bounce rate for some keywords and very high for others. Maybe you’re getting a lot of StumbleUpon traffic which, by its very nature, has a high bounce rate.

Bounce rate is important but always make sure you look beyond the actual number.

Exit Rate

Exit rate is the percentage of people who left your site from that page. Exits may have viewed more than one page in a session. That means they may not have landed on that page, but simply found their way to it through site navigation.

Like bounce rates, high exit rates can often reveal problem areas on your site. But the same type of caution needs to be applied. If you have a paginated article – say four pages – and the exit rate on the last page is high, is that really a bad thing? They’ve reached the end of the article. It may be natural for them to leave at that point.

Of course, you’ll want to try different UX treatments for surfacing related articles or encourage social interactions to reduce the exit rate, but that it was high to begin with shouldn’t create panic.

Exit rate should be looked at within a relative navigation context. Pages that should naturally create further clicks, but don’t, are ripe for optimization.

(Extra points if you get my visual ‘bounce’ reference.)

But There’s More! I’ve developed the Ultimate Guide to Bounce Rate to answer all of your bounce rate questions. This straight-forward guide features Ron Paul, The Rolling Stones and Nyan Cat. You’re sure to learn something and be entertained at the same time.

Reports are coming in from queries around the Internet about the unprovoked attack by Google on indents. Please be aware that the following screencaps may not be suitable for younger viewers.

Google Indent Massacre

Google Indent Hostilities

Hostilities against indents boiled over at 2010 SMX Advanced.

Danny goes on a rant about indented listings — can’t they go away? Danny yells “death to the indents” and half the audience boos!

While Matt Cutts declined to take a position during this flare up, the cozy relationship between Danny Sullivan and Google’s Matt Cutts is well documented.

Google Indent Appeasement

Searches on the ground report that Google is now presenting three consecutive listings in some cases.

Will these efforts at appeasement mollify pro-indent supporters? To date, Google and anti-indent activist Sullivan remain silent on these activities.

We’ll monitor the situation and provide updates as they become available.

November 12th Update

Embedded reporters are now telling us that Google is no longer providing domain grouping on search results.

The fragile relationship indents had with Google seems fractured with what pro-indent supporters call ‘a step backward’. Google will only say that they conduct many bucket tests to determine the best search results for users. Such tests are cold comfort for the millions of indents that have been suddenly removed from SERPs.

November 18th Update

The demise of indents seems all but sealed. Yesterday, Google unilaterally released a statement confirming that they’re “expanding the feature so that, when appropriate, more queries show additional results from a domain.”

The statement also tells us that while up to four results from a domain may be presented, additional results will have single-line compact snippets.

As you can see, this two result domain listing uses a single-line compact snippet instead of an indented full-snippet result.

No matter the clever wording used in Google’s statement to ingratiate themselves to the world audience, indents have been marginalized and are approaching extinction. Indent preservation campaigns in Skitch, TinyGrab, SnagIt and others are working to capture indent images so that they can always be remembered.

The other day I wrote about the potential for inflated Like numbers. In particular, I was interested in how comments were factored into the Like total.  It was pretty clear that Likes and comments were not mutually exclusive. But were comments a count of unique contributors or simply a total count of comments.

The Like Experiment

So, I ran a small experiment using an old satirical blog post: LOLCats and Religion: A Dissertation.

This post originally had two shares but no Likes or comments. So I went ahead and Liked it and asked my colleague Jeremy Post to have a comment dialog on the item. In all, we generated 10 comments.

One of my concerns was that comments might not always relate to the item and interestingly enough we actually did switch topics during the dialog from LOLCats to Dune. Go figure. (Note to self – fix image being attributed to blog posts.)

The Like Results

So what was the result? How many Likes did this old post rack up due to this comment stream? Sure enough, every comment is counted as a Like.

A quick check using my Facebook Like Number Bookmarklet reveals how the number is calculated.

So, did 13 others like this? No, it’s just two people having a conversation on a shared item. And that’s how you could get …

100 Likes from 2 People on 1 Item

One of the biggest errors I see (consistently) in SEO and PPC analysis is using Excel’s AVERAGE function on Click Through Rate (CTR). As I mentioned in my SEO Pivot Tables post, do not do this. Here’s why averaging CTR is dangerous.

Take the following set of 10 data points.

If you SUM all of the Impressions and Clicks and then do the CTR calculation you arrive at 10.05%. If you AVERAGE the 10 CTR percentages you arrive at 6.14%.

If I change the Clicks for these 10 data points I can produce the opposite effect.

And will you look at that, the average CTR is the same in both instances. Can you see how misleading average can be here?

Don’t Average Click Through Rate

For years, I’ve used a structured Excel quiz in my hiring process that tests just this issue. In my experience upwards of 50% of applicants fail the quiz. If you’re pulling down data into Excel for PPC or SEO, make sure you don’t fall into this trap.