The Schizophrenic Local SERP & Import XML Screencast

7-PACK, OR BLENDED, GOOGLE???

I’m going to show you a little Import XML thing I’ve been playing around with, to hopefully satiate some frustration.

But first, a letter to our friend Google.

I love you. We’ve had some great times together. Like that time when I asked about a toe fungus, you showed me some interesting stuff!! Ha! And didn’t judge me for it.

But why? Why do you bring me a 7-pack when I ask about a good “restaurant in boston, ma”, yet show me blended place pages when I ask about “coffee shops in portland, me”?

I just don’t get it. Since 2004 when we started seeing each other, I really thought you’d always be there for me, and we’d never be apart.

BUT CAN’T YOU JUST MAKE UP YOUR MIND?!

Sorry, sorry, I don’t mean to yell… but I can’t figure you out, and it may be time we take a break. There’s other SERPs out there, but frankly, you’re the cutest, so I’d really like to make this work.

I’ll take your suggestion to go on a little Import XML date, and maybe it can renew some of the passion we once had; but don’t get too comfortable…

Cautiously,

-Dan

Ok, ok – you can see why I got in trouble sometimes in school for “goofing off”. I’ll try to be serious now…

What’s Going On? Two Hypotheses

So that’s the issue here. What’s Google gonna give us? Blended or a 7-Pack? It seems random. My hypothesis that this discrepancy between Blended and 7-Pack is either;

1. Query triggered – meaning the exact phrasing of the query triggers one result or the other, like “boston dentist” could bring a 7-pack while “dentist in boston, ma” could bring blended.

or

2. Results triggered – meaning that, you perform a search, results are returned and then displayed according to what the results are – maybe geographic density of the business locations.

I’ve decided to test the first hypothesis, but after manually running about 100 queries between myself and my wife, and logging the data into excel, I could see that wasn’t going to work. That was no fun. Get me my data, and quickly!

That’s were the ImportXML tool comes in, but just a second…

First, I’ll clearly define what I’m testing here. My hypothesis is that the exact format of the query could have an effect on whether blended results or a 7-pack is returned. I’d like to validate this to a degree where you could even predict, within a small margin of error, what you’d get. For example, would using the word “near” as in “plumber near chicago” be more likely to get you a 7-pack?
[hr]

SERP Data Needed

The data I’d need on any given query, in an Excel spreadsheet:

1. Keywords
2. How many organic listings?
3. Blended Listings (yes or no) and how many?
4. 7-Pack (yes or no) or 3-Pack (yes or no)

There may be a tool which exists that could do this but after 10-20 minutes of searching I couldn’t find anything. Unrelated, I’d recently heard about ImportXML from the interview I just did with John Doherty. So upon checking out a few videos with Tom Critchlow explaining ImportXML (and being completely blown away!), it was clear that I could use that as a way to get my data.
[hr]

Fetching Local SERP Data with Import XML / Screencast

Here’s a quick screencast demo. I apologize, that it opens in a separate window as flash. Being my first screencast and using Jing for the first time, I didn’t realize you had to upgrade to get it YouTube compatible. Next time!


Watch the screencast here (separate tab will open)

The Import XML Functions

Note: With xpath, there could be multiple ways to reference the tag elements to grab the needed info. See reference documents below. For example, I use “//div[@id=’ires’]/ol/li/h3|H3” to get the organic headers, but there’s other ways too.

Field One: Grab Organic Results

=importxml("http://www.google.com/search?hl=en&q="&A2&"+"&B2&"+"&C2&"
&pws=0&gl=US", "//div[@id='ires']/ol/li/h3|H3")

• q=”&A2&”+”&B2&”+”&C2&” – allows me to put three keywords in separate cells in the spreadsheet, for easy modification or cut and paste
• pws=0&gl=US – turns off personalization and sets location to US
• //div[@id=’ires’]/ol/li/h3|H3 – Fetches H3 Headers for organic results.

Field Two: Grab Blended Listings

=importxml("http://www.google.com/search?hl=en&q="&A2&"+"&B2&"+"&C2&"
&pws=0&gl=US", "//table[@class='ts']//div/@style")

• //table[@class=’ts’]//div/@style – fetches the existance of the red place page marker blended with an organic result

Field Three: Grab 7-Pack or 3-Pack

=importxml("http://www.google.com/search?hl=en&q="&A2&"+"&B2&"+"&C2&"
&pws=0;gl=US", "//h4|H4")

• //h4|H4 – fetches the header for a 7-pack or 3-pack listing

Count: Wrap Them in counta()

Wrap all of the above statements in a counta() to create the second spreadsheet as shown in the video, like this:

=counta(importxml("http://www.google.com/search?hl=en&q="&A2&"+"&B2&"+"&C2&"
&pws=0&gl=US", "//div[@id='ires']/ol/li/h3|H3"))

• wrapping an importxml function with “counta()” simply counts the number of results returned. This is perfect, since that’s all I need for this experiment.

What’s Next: Collect and Analyze

Coming up in the next post on this topic, I’m going to collect and analyze the data. I’m looking to see if there is an discernible correlation between the exact search typed and whether Google returns a 7-Pack or blended place page listings.

So bearing in mind, the above tool is just a quick prototype, and as others have pointed out, not as robust as other ways of collecting the same data, but certainly a fun way the start!

Let me know if you use this for anything and what the results are!

Bonus: Import XML Resources

Import XML Cookbook – SEER Interactive Blog

Guide to Import XML and Google Docs – Distilled SEO Blog

XPath Syntax and Documentation