You Are Browsing The Analytics Category

Google Split Testing Tool

December 23 2010 // Analytics + SEO // Comments Off on Google Split Testing Tool

In November Matt Cutts asked ‘What would you do if you were CEO of Google?‘ He was essentially asking readers for a wish list of big ideas. I submitted a few but actually forgot what would be at the top of my list.

Google Christmas

Google A/B Testing

Google does bucket testing all the time. Bucket testing is just another (funnier) word for split testing or A/B testing.

A/B testing, split testing or bucket testing is a method of marketing testing by which a baseline control sample is compared to a variety of single-variable test samples in order to improve response rates. A classic direct mail tactic, this method has been recently adopted within the interactive space to test tactics such as banner ads, emails and landing pages.

Google provides this functionality through paid search via AdWords. Any reputable PPC marketer knows that copy testing is critical to the success of a paid search campaign.

SERP Split Testing Tool

Why not have split testing for SEO? I want to be able to test different versions of my Title and Meta Description for natural search. Does a call to action in my meta description increase click-through rate (CTR)? Does having my site or brand in my Title really make a difference?

As search marketers we know the value of copy testing. And Google should want this as well. Wouldn’t a higher CTR (without an increase in pogosticking) be an indication of a better user experience? Over time wouldn’t iterative copy testing result in higher quality SERPs.

Google could even ride shotgun and learn more about user behavior. If you need a new buzz word to get it off the ground, try crowd sourced bucket testing on for size.

This new testing tool can live within Google Webmaster Central Tools and Google should be able to limit the number of outside variables by ensuring the test is only served on one data cluster. For extra credit Google could even calculate the statistical relevance of the results. Maybe you partner with (or purchase) someone like Optimizely to make it happen.

If this tool is on your Christmas list, please Tweet this post.

SEO Metrics Dashboard

December 20 2010 // Analytics + SEO // 6 Comments

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.

Non-paid Search Traffic 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.

Sampled Data 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.

SEO Metrics

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.

SEO Metrics

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.

SEO Metrics

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.

SEO Metrics 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.

SEO Metric Analysis

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.

SEO Metrics Analysis

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

Circular Google Logo

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.

Bounce Rate vs Exit Rate

November 15 2010 // Analytics + SEO // 23 Comments

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

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

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.

How To Get 100 Likes From 2 People

November 08 2010 // Analytics + Social Media // 9 Comments

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.

Facebook 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.

Facebook Like Numbers

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

Facebook Like Count

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

Don’t Average CTR

November 08 2010 // Analytics + PPC + Rant + SEO // 6 Comments

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.

Don't Average Click Through Rate

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.

Don't Average CTR

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.

Facebook Like Number Bookmarklets

November 05 2010 // Analytics + SEO + Social Media // 2 Comments

Want to know the Facebook Like statistics for the page you’re on? No problem.

Facebook Like Number Bookmarklets

Using the old REST API you can find out the Facebook Like statistics for any page. For easy access, simply drag these two links to your bookmark bar.

FB Stats: Current Page

FB Stats: Home Page

The Current Page bookmarklet will provide Like statistics for the page you’re on. So, if you were on the ReadWriteWeb article about Facebook Places Deals you can click on this bookmarklet and be provided with the Like statistics for that page.

Facebook Like Bookmarklet

The Home Page bookmarklet will provide Like statistics for the home page for the site you’re on. Please note that this is not showing the aggregate Like statistics for the entire site, but just that of the home page.

Like Number Use Cases

Why are these bookmarklets useful apart from abject curiosity?

First off, you can determine the true number of Likes. Second, they provide competitive intelligence and potential insight into Facebook’s search algorithm (aka Facebook SEO). Do pages with a higher distribution of comments get a higher weight? I’m not sure.

This is one way to begin understanding the ways in which pages enter the Open Graph and how they are treated based on Like activity.

Impact of Google Instant

October 01 2010 // Analytics + SEO // 2 Comments

Everyone wants to know how Google Instant is changing search.

There’s been some great analysis on whether Google Instant has changed keyword length. While auto complete could certainly have an impact on query behavior, the impact so far seems to be negligible.

I’ve been more interested in whether Google Instant would change assessment behavior. I theorized that Google Instant might result in more clicks above the fold because users would become focused on watching – and assessing – search results as they typed.

Google Instant Rank Analysis

Each week I measure the amount of traffic produced by each rank via Google Analytics. Using this data across two large sites from different verticals, I’m able to compare traffic by rank the week prior to Google Instant’s launch versus the most recent week.

Google Instant Traffic by Rank

The distribution of traffic by rank certainly seems different. But there’s a good deal of noise in pulling this data.

First, while the volume of searches is high the number of data sources is low.

Second, the number of terms driving traffic at each rank and the query volume for those terms may have changed. (However, a quick analysis shows that the number of terms doesn’t have a bearing on the data.)

Lastly, the Google Analytics rank hack only captures a certain percentage of traffic where the ‘cd’ parameter is passed. Historically that was about 20% to 25% of search results. However, just prior to the launch of Google Instant that percentage shot up to ~40%. The subsequent weeks of decline in ‘ranked’ traffic don’t map to overall traffic patters, so I believe the amount of traffic with a ‘cd’ parameter has likely decreased.

Long story short, the variation by rank is the signal, not the actual changes in rank.

Google Instant Click Distribution

Has Google Instant changed the distribution of clicks by rank? If above the fold ranks are getting a higher distribution of clicks, SEO is not dead – its become more important than ever.

But what about the odd behavior of those with a rank of 1 or 2? Could paid search or Onebox presentations be sucking away traffic from the first and second positions?

Google Webmaster Tools should be able to provide some additional insight. Yet, as I was performing the analysis I came to realize that the search queries report in Google Webmaster Tools has less coverage (5% to 15%) then the Google Analytics rank hack (20% to 40%).

Furthermore, the coverage in Google Webmaster Tools is weighted by rank, with (far) higher visibility for higher ranks. I’m not sure analysis on lower ranks is reliable given the thin data set. However, I do see appreciable declines in CTR for both the first and second positions. This seems to support the data gleaned from Google Analytics.

Above The Fold SEO

If the distribution of clicks is changing, being above the fold could become increasingly important. Earlier this year Jakob Nielsen conducted a study that showed that users spend 80% of their time and attention above the fold.

Eye Tracking Page Distribution

Lets be clear, the prerequisite is that the user scrolls. Does Google Instant disrupt the natural inclination to scroll? I say yes, and I think the preliminary data points in that direction.

Eye tracking studies have already shown the difference in assessing informational versus transactional queries. I’d like to see these studies performed using Google Instant to determine if those patterns have changed.

Baring that, I’d like to expand my data set and appeal to others who have Google Analytics rank data to perform the same analysis. Do it yourself and post the results or send me the data and I’ll aggregate it with my current data set.

Either way, I believe it’s important to understand how search behavior may be changing and adapt accordingly.

Google Analytics Default Profile

September 24 2010 // Analytics + SEO // Comments Off on Google Analytics Default Profile

If you’ve used Google Analytics for any stretch of time, you probably have a number of different Google Analytics profiles for your website. For instance, you might should be tracking keyword rank in a Google Analytics profile.

Different profiles can be handy but often the one you use the most isn’t the default profile. Each time you log-in to Google Analytics it defaults to a profile based on an alphabetic sort. This is annoying and, sadly, Google hasn’t launched a new ‘select-as-default-profile’ feature. Instead, there’s a very simple and easy hack.

Google Analytics Default Profile Hack

First, click on Analytics Settings in your Google Analytics account.

Google Analytics Settings

From there, find the profile you want to be the default profile. Next to that profile, on the far right under the Actions column you should see an Edit option.

Edit or Delete Profile

If you don’t see these actions, you don’t have the rights to make this change. Find someone with Administrator access or have them grant you that access. If you do see these actions, click Edit. Then you’ll want to edit the main website profile information. The Edit link is located at the upper right side.

Edit Google Analytics Profile

Now all you have to do is type an underscore at the beginning of your profile name. The example uses a domain entry, but it could just as easily be something like _My Website Name.

Underscore Your Profile

Then click Save and you’re done. Don’t worry, this will NOT impact the tracking on this profile. No data or history will be lost.

The underscore profile will now be the default profile since it’s first alphabetically. It’s certainly not the only way to do this, but this 1 minute hack can make your daily use of Google Analytics just a bit easier.

Google Instant Analysis

September 09 2010 // Analytics + PPC + SEO // 2 Comments

Google Instant delivers a new search experience, both in how users enter searches and how those searches are presented. What does it mean for the search industry?

Google Instant

Did Google Instant Kill SEO?

No! A thousand times no. Those making these claims are either being purposefully provocative or are … simply misinformed (see how nice I can be) about SEO. What makes SEO interesting is that it continually evolves. Google Instant is simply another evolution of search and those who adapt will flourish and those who don’t will fall behind. Pick a side.

Is Google Instant an Algorithm Change?

No! Google Instant does not impact search rankings. It is not an algorithm change. However, it may have an impact on traffic as search behavior changes. So lets look at exactly what Google Instant is and why it could be a major shift for SEO.

Google Instant vs Facebook Typeahead

Facebook Typeahead Search

Before we go into the details, let me take a brief detour. I believe Google Instant was developed, in large part, because of a continuous effort to provide users with a better search experience. However, I also think that Google Instant was influenced by the work Facebook has been doing with Typeahead Search, which has been presenting results in the same way for about a year.

Remember, the Open Graph is just another way to build a search index and one day soon you’ll go to Facebook and that small search box will suddenly be twice as large and presenting more results from outside the walled garden than inside.

The era of Facebook SEO is just around the corner and Google knows it.

Search Speed

At the launch event, Google stated that the average time to type a search was 9 seconds and the time to evaluate the results was 12 seconds. Google Instant seems aimed at reducing both of these metrics.

The time to type has clearly been reduced as Google delivers results on a letter-by-letter basis. However, the evaluation time may also be reduced as users become used to scanning the ever changing search results, watching them go from ‘wrong’ to ‘right’.

If you pay attention to UX (and you should) you know that users scan web content. Google Instant seems poised to take advantage of this behavior.

The Fold

Users will be evaluating the quality of searches as they type. The speed in which Google delivers these results means the evaluation will likely take place above the fold. Will users become more focused on above the fold results, even when they land on the ‘right’ search result? If so, this is compounded by the extra space taken up by Google Instant’s set of five auto-complete suggestions.

If the evaluation time decreases and the focus is on results above the fold, a greater emphasis would be placed on achieving top rankings and having a result that stands out. In other words, SEO becomes even more important.

Auto Complete

Google’s auto-complete feature becomes a much more powerful part of SEO. Today, the algorithm behind search engine results is refined on a daily basis. Gone are the ‘Google Dance’ days in which a monthly algorithm change created havoc on search results.

However, the auto-complete algorithm is not updated with that type of frequency. Some SEOs (myself included) target auto-complete suggestions (formerly know as Google Suggests) for optimization. While some of the suggestions change, most of them do not – and if they do, they do so infrequently. When they do change, you feel the impact – immediately.

The increased reliance on auto-complete and infrequent updates to those suggestions could result in a new type of Google Dance.

The auto-complete algorithm seems loosely based on search volume, location and recency with a heavy emphasis on volume. Google Instant creates a secondary SEO algorithm. It’s not just about those 10 blue links, it’s about those 5 auto-complete suggestions too.

We simply don’t know how often Google will change auto-complete suggestions, how they’ll change the criteria for those suggestions nor how quickly SEOs will find ways to influence and game those suggestions. Auto-complete gives Google quite a bit of power to direct user queries.

Google Analytics

Initial reports seem to indicate that the keyword returned to Google Analytics will be the auto-complete suggestion term that produced the click. This reduces the visibility into the actual typed search that triggered the click. This may inhibit the ability to infer the actual intent of those queries, or to determine if the auto-complete suggestion is creating non-qualified traffic.

While seemingly innocuous at first glace, I believe the lack of visibility is dangerous. Thankfully, a parameter (‘oq’) in the URL string has been identified that should provide the ‘original query’ string. If you’re already tracking rank through Google Analytics, setting up the Google Instant tracking shouldn’t be too hard. Experimentation on the exact output is necessary to ensure you’re able to leverage the data.

I’ll post my own Google Instant Analytics Hack after I find the right configuration.

Paid Search

Google Instant dynamically displays both organic and paid search results as you type. While the threshold for booking a paid search result as an impression is 3 seconds, many believe (myself included) that the number of impressions will increase.

This could have a dampening effect on clickthrough rates (CTR) which is the primary way Quality Score (QS) is measured. A lower QS leads to higher CPCs and impression thresholds. It remains unclear how Google will handle this potential scenario and whether more scanable paid search results will be more effective.

And if a change in search behavior does come to pass, does that change the distribution of clicks between paid and organic? The cynic in me says Google wouldn’t have launched Google Instant unless the impact on paid search revenues was thought to be neutral or positive.

Search Behavior

Google Instant makes hitting the return or enter button nearly obsolete. This simple action (or lack thereof) may change search behavior. There will be no delimiter – no time in which to enter, evaluate and iterate. Instead, evaluation and iteration take place almost simultaneously.

Users will ‘flip’ through results by typing, until they find the results they want. The speed also reduces the hesitation to try another search, particularly since searches are dynamically shown as you type or delete.

Ben Gomes (Google Distinguished Engineer) did state during the launch announcement that Google Instant resulted in more search queries.

Search may become less about a single search and more about a series of iterative searches. As such, SEOs may need to understand how to either interrupt or attract a click during that series of searches or understand how to optimize for the last search in that iterative process.

The Long-Tail

Google Instant should have an impact on the long-tail, but what exactly that impact will be depends on how search behavior evolves. In the short-term, I believe the number of words per query will rise as users take advantage of the auto-complete suggestions and iterations. The ability to drill-down to more refined queries should go up. We’ll see an expanding mid-tail.

Google SIdewalk Ends

However, what happens when you get to the end of auto-complete suggestions? Will users keep on typing and ultimately hit return? Or will they begin to view the lack of auto-complete suggestions as a sign they’ve gone too far, that they’ve reached the end of the sidewalk? Will they simply delete and try to iterate the search within the auto-complete suggestions?

We’ll have to wait and see.

Google Instant Analysis

Google Instant will not kill SEO.

Google Instant is not an algorithm change.

Google Instant looks eerily familiar to Facebook Typeahead Search.

Google Instant reduces the time it takes to type a query.

Google Instant may impact traffic as search behavior changes.

Google Instant may reduce the time users spend evaluating each SERP, putting a greater emphasis on having a scanable and eye-catching result.

Google Instant pushes all results down the page because of the five auto-complete suggestions.

Google Instant may make ranking above the fold more important as users become focused on evaluating results as they type.

Google Instant makes auto-complete suggestions extremely powerful, creating a secondary algorithm that can have serious traffic implications.

Google Instant could reduce the visibility of true search queries unless you configure Google Analytics to capture this data.

Google Instant will change the distribution of search terms on the tail.

Google Instant may increase paid search impressions which could negatively impact CTR and Quality Score, resulting in odd CPC cost and performance.

Google Instant is addictive.

SEO Pivot Tables

July 23 2010 // Analytics + SEO // 9 Comments

In my last post I covered SEO Excel functions. In this post I’m going to cover something even more valuable to SEO – pivot tables. Excel defines a PivotTable as follows:

A PivotTable report is an interactive table that combines and compares large amounts of data. You can rotate its rows and columns to see different summaries of the source data, and you can display the details for areas of interest.

Use a PivotTable report when you want to analyze related totals, especially when you have a long list of figures to sum and you want to compare several facts about each figure. Because a PivotTable report is interactive, you can change the view of the data to see more details or calculate different summaries, such as counts or averages.

What does a pivot table really do? A pivot table lets you slice and dice a big set of data.

Top Queries Pivot Table

Instead of using dummy data I’m going to show how to generate a pivot table report using the new Google Webmaster Top Queries report. I’ll be using Excel 2008 for Mac which is different (probably more difficult) than the PC version.

Obviously we need to the Search queries report in Google Webmaster Tools.

Webmaster Tools Top Queries

At the bottom of this report you can download the table.

Download Top Queries

What’s really nice is that it will actually download all of the data, not just the 100 queries on the page but all (4,010 in this case) queries. Other Google products could benefit from this feature. I’m looking at you Google Analytics.

The Data Table

What you download is a big CSV file. (CSV stands for Comma Separated Values if you’re interested.) It will look like this when you open it.

Google Top Queries Report Download

It’s certainly interesting but reading it line by line isn’t very useful or efficient. There are plenty of things you can do to make it easier to digest. You could sort it (by Impressions) or filter it (by Avg. position) but a pivot table can really make sense of the complex.

Before I go on I’m going to Save As, rename the file and change the file type to Excel. This is just a safeguard and good general practice.

Select The Data

Next, you’ll want to select the data you want to include in your pivot table. This doesn’t mean just the rows or columns you want. Instead, you’re going to select the entire set of data you just downloaded. Selecting it is actually really easy.

Click on the top left cell of the data. In this case it’s going to be cell A3 with the text ‘Query’ in it. Then hold shift-ctrl and tap the right arrow once (but don’t let go just yet). When you do this all the relevant columns in the entire top row should be highlighted.

Highlight Using Keyboard Shortcut

Keep holding shift-ctrl keys down and then tap the down arrow once. When you do this all the rows in the data table should be highlighted.

Shortcut to Selecting Excel Data Table

The shift-ctrl-arrow or shift-apple-arrow shortcut selects everything until it hits a blank cell. It’s a nifty time-saving shortcut for any Excel work.

Create The Pivot Table

At this point you can let go and, with the entire data table highlighted, select PivotTable Report from the Data menu.

Create Pivot Table

This will launch the PivotTable Wizard.

Pivot Table Wizard Step 1 of 3

Since you’ve already selected the data (which is what you’d do in step 2) you can actually skip steps 2 and 3 and just click Finish. When you do, a new Excel tab is created and you’re staring at an empty pivot table.

New Pivot Table

In my experience this is where most people get scared off. It’s like Excel is taunting you – demanding you to drop fields and data. It looks more daunting than it is and you can always undo or even create a brand new pivot table. As in nearly all things, trial and error is a great teacher.

It’s probably easier to show you how to do this rather than explain what each part means in the abstract. So lets create a pivot table that shows the total number of impressions, clicks and click through rate (CTR) by position. That would be handy, right?

Insert Pivot Table Fields

First you’ll want to drag the Avg. position field button to the row area. If you don’t see those field buttons, just click anywhere in the pivot table and they’ll magically appear.

SEO Pivot Table How To

In this instance there are no columns so we’ll move swiftly on to data.

SEO Pivot Table How To

Drag the Impressions button into the data area.

SEO Pivot Table How To

The result will look like this.

SEO Pivot Table How To

It looks wrong, I know! But be patient, we’ll fix that in short order. Next drag the Clicks into the data area.

SEO Pivot Table How To

The result will now look like this.

SEO Pivot Table How To

Change Field Settings

You don’t want to count impressions or clicks you want to sum impressions and clicks. To do this click on the first Count of Impressions cell (B4 in this case), then click the PivotTable button and select Field Settings.

SEO Pivot Table How To

After you do this you’ll be able to change the field from a count to a sum.

SEO Pivot Table How To

Click on Sum and then click OK.

SEO Pivot Table How To

Now we’ve finally got the right metric and you’re seeing the total number of impressions by position. Simply repeat the same process for Clicks so that you have both Sum of Impressions and Sum of Clicks.

Create a Calculated Field

You might now be tempted to drag CTR into the data area. Don’t! Averaging a set of percentages will not give you the results you want. Instead you need to create a calculated field. Click the PivotTable button, then select Formulas -> Calculated Field… from the drop down menu.

SEO Pivot Table How To

Now you get to create a Calculate Field. Again, much easier than it sounds.

SEO Pivot Table How To

You’ll first give this calculated field a name. The name CTR is already taken so I’m going to name it CTR by Position. Then create the calculation by typing in functions, highlighting and inserting fields. This is what it should look like before you (1) click the Add button and the (2) click OK.

SEO Pivot Table How To

Formatting Fields

Now you’re got the total number of Impressions, Clicks and CTR by position. But the formatting on the CTR is annoying. So lets change that. Click on the first Sum of Position CTR cell (B6 in this case), then click the PivotTable button and select Field Settings.

SEO Pivot Table How To

This brings you to a two pane process where you will (1) click Number… and (2) select Percentage from the Category menu and (3) click OK which takes you back to the first window where you will (4) click OK again.

SEO Pivot Table How To

At the end of all that you get a fairly easy to read table that shows impressions, clicks and CTR by position.

SEO Pivot Table How To

Filtering Pivot Tables

What if you wanted to just see the data for a specific position? No problem. Drag the Avg. position field from the row area into the page area.

SEO Pivot Table How To

Once the pivot table changes, you can then select the position you want to see using the drop down filter.

SEO Pivot Table How To

I’m going to select 1.7 as my position. At that point I might want to see what actual terms drove traffic at that position. To do that, drag the Query button to the column area. (You didn’t think we’d ignore the column area completely, did you?)

SEO Pivot Table How To

Now I get to see what terms drove traffic at the 1.7 rank and how effective each term was at that position.

SEO Pivot Table How To

Yes, my used books blog is getting a fair amount of traffic on the term ‘dr. evil’. (Image optimization works folks.)

Hopefully, you can envision another pivot table with Query as rows, search engines as columns and keyword rank as data. That’s a nice little table to have in your back pocket.

Hide Pivot Table Items

Still with me? I’m going to do a few other things that you might find useful. I’m going to drag query out of the column area, and drag Avg. position back into the row area so we’re back to the formatted pivot table first created. Then I’m going to click on the Avg. position cell (A3 ) and select Field Settings.

SEO Pivot Table How To

This will bring up a slightly different Field Settings window where you can hide certain items. (FYI – from here you can also click Advanced and change the sort order of your pivot table.)

SEO Pivot Table How To

I’m going to hide any position higher than 10 and then click OK. The result is a much more manageable table.

SEO Pivot Table How To

Pivot Table Charts

Now I want to see which of these positions drove the most clicks. I’m going to delete the Sum of Impressions and Sum of Position CTR fields. To do this, Click on the corresponding cell in the pivot table (B4) and then navigate to Field Settings and then click Delete.

SEO Pivot Table How To

Do the same thing for both Sum of Impressions and Sum of Position CTR and you’re left with a table that shows the clicks by position.

SEO Pivot Table How To

Now it’s time to make a chart. First you need to select the entire table by clicking the PivotTable button and using the Select menu to … select the Entire Table.

SEO Pivot Table How To

Now create a chart like you normally would.

SEO Pivot Table How To

And after a bit of tweaking here and there you can produce a presentation quality chart.

SEO Pivot Table Chart

In this instance you might be surprised to see where most of the clicks come from. It might make sense for me to review the terms at 2.8 and see if I could move them up a spot to grab a higher share of clicks.

Refreshing Pivot Table Data

Now for extra credit. I mentioned in my SEO Excel Functions post the need to round numbers. In this instance, perhaps I just want to see things by whole number rank.

Lets go back to the actual data table and create a column next to Avg. Position. I’ll name it Rounded Position and then use the ROUND function to change it to a whole number.

SEO Pivot Table How To

Now I’ll copy that all the way down the column. To do this quickly just double click the small box in the lower right hand corner of that cell.

SEO Pivot Table How To

Now select the entire column starting below the title by using the shift-ctrl-down arrow. Then copy and paste values over the data in the Avg. position column. You’re basically overwriting the decimal rank data with whole number rank data.

Alternatively you could insert a column into your data table (between D and E so it’s inside the original table range) and then paste values (including the header row) into that new column. By doing this you aren’t overwriting data. Instead you’re going to add an additional field to your pivot table.

If you choose this route you’d have to swap the Avg. position with Rounded Position in your pivot table and go though the hide items process again. Either way, the idea is to refresh the data in a pivot table.

So lets go back to our pivot table sheet and click the exclamation point.

SEO Pivot Table How To

Voila! Now you’ve got a super easy to use table that shows clicks by whole number rank.

SEO Pivot Table How To

Every SEO likes a good chart, right? The chart data should have updated automatically too. So with a small tweak you can produce another one.

SEO Pivot Table How To

Amazingly enough there is more I could go through, but …  I think that’s enough for now.

SEO Pivot Tables

If you’re doing SEO for any amount of time you’ll see the value of using a pivot table. They help you make sense of large sets of data, allowing you to accelerate your analysis and provide actionable insights. Hopefully this real data exercise was instructive and valuable. If it was, please give it a Sphinn.

Let me know if you have any questions, comments or pivot table tips and tricks of your own.

xxx-bondage.com