[Tutorial] Learn ImportXML – Best SEO Tool Ever

[/sourcecode]
If you’ve never written code or used ImportXML before, that query probably looking very complicated, but it’s not. Let’s break it down.

=ImportXML(A1,”//ul[@class=’af’]//li”)

=ImportXML(url,query) is the structure of the command

URL

A1 is calling our original URL for the CIA world factbook site

Query

// – select all the elements of the following type
[@class=”] – only select the elements of this certain type
//ul – select all of the unordered lists
//ul[@class=’af’] – show me all unordered lists with class of ‘af’
//li – show all list items
//ul[@class=’af’]//li – show me all list items inside all the unordered lists that have a class of af

Next, I wanted to get the page-specific URL extension, like /countryname.html, because all the countries started with the same root: https://www.cia.gov/library/publications/the-world-factbook/
The ImportXML script I used to find that was

=ImportXML(A1,”//ul[@class=’af’]//li/a/@href”)

The script is the same as before, because we’re looking for the same information, except we want to pull the info in the link (a href).
/a/@href works similarly to how //ul[@class=”] works to drill down in the code
Memorize this for the future to pull out URLs from web sites: “/a/@href”

So now we have this

And we still need the full URL and population data.
To combine the page specific URL and root URL I used ” =CONCATENATE($A$2,D2) ” and replicated it down the page.
CONCATENATE combines two fields into one — so I made field A2 “https://www.cia.gov/library/publications/the-world-factbook/” (without /index.html at the end) and added the country page to it to form the full URL.
The first time I did it, I used =CONCATENATE(A2,D2) and copied it down the page. It totally broke. Why? Because I left out the two $ symbols.
$ holds the spot in the query and doesn’t make it relative to the cell, it makes it ‘fixed’. So when I copied the formula down the sheet WITHOUT $ it essentially wrote this for me :
A2,D2
A3,D3
A4,D4
A5,D5
and so on…
When really I needed this :
A2,D2
A2,D3
A2,D4
A2,D5
because I always need the first part of the query to be the URL in cell A2.
With the dollar sign working correctly, row 5 looks like this : =CONCATENATE($A$2,D5)

The hard part

The last thing I needed to mark off this great accomplishment was the population data for every country.
Up until now I had really just set myself (or my spreadsheet) up to do work, but it hadn’t done any yet.
So let’s go back and find some population data on the page so we can find it in the HTML


Long story short – I couldn’t get the population data.
Why not?? Should the query be //div[@class=’category_data’]

Yes it should be, but the CIA has done a HORRIBLE job at marking up their site. They should have used unique section names in their html, but instead they made all data “category data” so you cannot get back the response you’re looking for.
Hmm.. ok well on the w3schools xpath page it says you can use [number] to select just a certain result like //div[9] for the 9th div which has the population data.
But it didn’t work!

Just when I thought it was over… Chris Le from SEER Interactive to the rescue!

Chris discovered that Google Docs does NOT support Xpath 2.0 fuctionalities, only 1.0. So the div[9] thing was never going to work.
Instead Chris wrote an awesome script to do the same thing for me!
[sourcecode language=”xml”]
/**
* From an input array, filter by regEx, and return the first integer values
* found within.
*
* @param {cells} inputArray Range of cells you want to filter
* @param {string} regEx Regular expression to filter by
* @author Chris le (@djchrisle)
*
* @example
*
* =findIntegerInCells(A1:A10, “,\d.*est\.\)”)
*
* Looks in cells A1 to A10 for a comma, followed by a number, then anything
* and followed by “est.)”
*/
function findIntegerInCells(inputArray, regEx) {
var retVal = [];
// Look for cells that match the regular expression
for (var i = 0; i < inputArray.length; i++) { if (inputArray[i].toString().match(regEx)) { retVal.push(inputArray[i]); } } if (retVal.length > 0) {
// Take the first thing found, remove commas
var value = retVal[0].toString().replace(/,/g, ”);
return parseInt(value); // Return just the numeric value
} else {
return ‘Nothing found : (‘;
}
}
[/sourcecode]
He created this scripted called findIntegerInCells under Tools > Script editor inside Google Docs. Then to get the population data, you need to use the function =findIntegerInCells(importXml(E3, “//div[@class=’category_data’]”), “,\d.*est\.\)”) in a new cell.
Since almost every population on the CIA site has est. after it, he used that in conjunction with the URLs I made to find the right area and grab the numbers for population.
And it worked like a charm!

He even put a line in there so that if it can’t find population data (ex. indian ocean, antarctica, etc) it doesn’t error out, it puts Nothing found 🙁

Final product

I realize this wasn’t the most complicated problem faced or tool built using ImportXML, but I sure learned a lot about it’s features and how to make it give me what I want.
I had to do a lot of reading, digging around Google and trial and error, but I feel much more confident now about building my own tools for SEO, and I hope you will too!
If you have any questions I’d love to help in any way I can. There are a lot of other great people in the industry who I’m sure would lend a hand as well.
Thanks again Chris!
 

Note :

Chris Le of SEER Interactive just wrote a great post and script to fix the new Google SERP scrapping issue with ImportXML. If you’re trying to scrape the SERPs, you need his hack.

Killer ImportXML Resources :

These posts below do an exceptional job of explaining ImportXML and demonstrating various uses for it. You’ll have a great understanding if you go through a few of these.
How To Build Agile SEO Tools Using Google Spreadsheets
http://www.distilled.net/blog/seo/how-to-build-agile-seo-tools-using-google-docs/
Playing Around with ImportXML in Google Spreadsheets
https://seogadget.co.uk/playing-around-with-importxml-in-google-spreadsheets/
ImportXML Cookbook
http://www.seerinteractive.com/blog/importxml-cookbook
The ImportXML Guide for Google Docs
http://www.distilled.net/blog/distilled/guide-to-google-docs-importxml/
 

Killer ImportXML Tools

Guest post opportunity finder tool
http://skyrckt.com/guestposttool – James Agate – Skyrocket SEO
Keyword research with Ubersuggest and ImportXML – Dan Shure – Evolving SEO
 
Do you have an awesome ImportXML tool? Add it to the list!