Use Excel To Analyze Keywords That Rank For Multiple Pages

Here’s a quick tutorial on how to use Excel to analyze the keywords that have more than one of your site’s pages ranking in Google organic search results.

Your site may have plenty of keywords that have more than one landing page ranking for a variety of reasons. For example, when someone googles “Google Analytics Training”, there are many different Bounteous pages that might display, based largely on where the user is located.

Let’s look at how we can break these out and analyze them further.

Step 1: Export all your Google keywords with the associated landing pages.

This can be done in a few seconds.

1. First, open Google Webmaster Tools and go to the landing pages tab of the Search Queries report.

2. Add the parameter &grid.s=100000 to the URL. This displays all landing pages by changing from the default of 25 to up to 100,000 pages.

3. Next, use the Search Queries exporter bookmarklet by Noah Haibach to export ALL the queries by landing page. It can take a minute or two if you have a high-traffic site.

Step 2: Isolate the keywords with more than one landing page.

Now we’ll just pull our desired list of keywords in Excel. Note: I use Excel 2010.

1. Import text file into Excel.

2. Make a table.

In Excel, go to Insert > Table.

3. Use conditional formatting to highlight duplicate keywords.

Highlight the keyword column. Then go to home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Now any keyword that appears more than once is highlighted.

4. Filter the table to show only cells of color you used for highlighting.

Go to the little arrow button in B1; then Filter by Color.

All done! 

Step 3: Analyze.

Easy enough to pull that list right? Now let’s talk about what to do with it.

Too much data!?

First, if your list seems overwhelming and non-useful at first, I feel you. My list for www.bounteous.com had over 2,300 keywords with more than one landing page. Deeply analyzing all of these columns isn’t very valuable. Additionally, manipulating tables like this can be resource intensive if you have a lot of data; it’s possible that Excel could freeze here.

So, you might want to cut out unimportant data. One way is to exclude the lowest trafficked landing pages before you perform step 1.

Alternatively, you can skim off the fat after you exported but before you make the table. I like to Advanced Sort by clicks and then by impressions, and then I delete columns at the bottom. I reduced the 2,300 columns for www.bounteous.com to about 500 by deleting columns with 0 clicks and less than 10 impressions. In the last screenshot, I did this, then I alpha-sorted by keyword.

Figure out why your keywords might have more than one page ranking.

Understanding why you have multiple pages ranking can help you understand your search engine visibility better, and help you identify opportunities for improvement. Below are a few common reasons this could happen:

• Localization – Sometimes a search query made by a nearby user will return one page (for example, your homepage or store page shows in a local 7 pack); whereas the same query from a more distant user will return a different page. This technique is one of the best ways I know to grab a nice list of relevant keywords impacted by location.
• Rankings changes – Perhaps one page was ranking consistently for a search query, and now another one is.
• Multiple pages ranking simultaneously – if you’re really dominating the rankings.
• Sitelinks or other extra links (CAUTION). The data I often see in GWT on Sitelinks, breadcrumb links, event listing links, or other rich snippet links is often not what I’d expect, and I’m not sure what is going on. For example, I always see Sitelinks when I Google “bounteous”; however, the # of impressions listed by GWT for the  home page for the query “bounteous” is several times greater than any other landing page for that query. As another example, the breadcrumb links to a category page within the Google listings for a client’s product pages do not appear to show up either.
• To that last point, be wary of  jumping to conclusions with GWT search query data. It’s probably better to use this analysis for creating general hypotheses.

Sample analysis questions:

• What queries are impacted by localization? Should I change up how we’re doing geo-targeting and local SEO?
• Which page do I want to rank for keyword X?
• Why is page x ranking for these keywords? Should I shift the targets on page x?
• Do I have keyword cannibalism? (See #4 in 11 Keyword Targeting Mistakes for a definition.)
• Are there any pages getting substantial traffic from unexpected queries? Why?
• Are there landing page optimization opportunities to give said unexpected traffic a better experience (and improve conversion rates?)

Happy analyzing. Let us know in the comments what pearls of insight you’ve found by analyzing the landing pages of keywords.