Create Content & Source Opportunities Using ImportXML - Bring Digital
Let's Talk - 01204 977001
Back to Blog Stories

Create Content & Source Opportunities Using ImportXML

Create Content & Source Opportunities Using ImportXML

There comes a time in an SEOs life where we look to streamline the processes we use, common tasks that get frustrating. We look within our industry for the tools of the trade to help our lives to become easier, we already know what the solution should be, we just need the right tool to accomplish that goal.

For our clients or for ourselves a common problem would be “What content do our visitors actually want?”

Time honoured tradition would be to turn firstly to look within our analytics packages to see what our current visitors are actually seeking through searches that have brought them to our site(s).

But you can only go so far before you realize that there must be more out there that they are seeking and we are not offering them it.

Q: So how do we go about finding out beyond the analytics exactly what else?

A: Ubbersuggest + ImportXML = Content Ideas Goldmine

I saw the solution in my head already, I wanted a plethora of content ideas but I did not want to continuously export from Ubbersuggest 1 query at a time, it’s painful and it’s time consuming. So this is where ImportXML enters the fold, if you are not sure what it is or does there are some well documented resources around already to get you started. But beyond all of that reading (I have read them all) I wanted a formula that would work for me.

First Attempt

=importxml(""YOUR KEYWORD"&format=html&language=English%2FUK&source=web","//div[@id='result']//li")

This was the beginning, now that it worked I wanted to extend it beyond the 1 query and get it to pull 10 queries at a time. So it was a case of creating another sheet and simply referencing my list of 10 keywords and splitting them into columns of their own.

Second Attempt


Now I have a formula I can simply replicate for the 10 queries I need!

Grab a copy of my Competitor & Content Ideas which is also a mash up of Dan Shure’s solution + the SEOMoz formula for knowing the top 10 Google SERP competitors for your list of keywords/phrases.

The document has 3 sheets, the first for your keywords, the second for your main competitors and the third for the content ideas. bear in mind that this document acts as a seed process i.e link builders can get a quick overview of who the competitors are and see where the juicy links are coming from. The content ideas pull all of the Google suggest data but it does not categorize them.

Once you have your monster lists of content ideas it’s over to you to craft the content your visitors are searching for.

Guest Posting Opportunities

So you want to give some of your beautifully crafted content away huh?

Well Jayson Bagio has brought another variation of the same formula together to source guest posting opportuniies and you can grab a copy of his tool.

A lot of these ImportXML function can also be achieved using XPATHONURL via Neils Bosmas SEO for Excel plus a whole lot more functionality including grabbing those all important potential traffic stats. I just happened across this way first but I am not adverse to using either.

Dean Cruddace is a Mackem on a never ending SEO mission and a collector of Internet Marketing tools over at

Share This Article

  • seoteky

    Thanks for the mention Dean :) A great 1-2 punch tool that SEO’s can use together. Indeed there are a number of Google spreadsheet tools out there that can be used. So we need to look for tools that are perfect match and can work well together.

  • Dean Cruddace

    Great analogy Jayson and glad I managed to inspire you to create the guest blogging opportunities tool!

  • hyderali

    Hey Dean, thanks for the tool.

    I downloaded your copy, but do I’ve to “Make another copy” to see competitors of my keyword list?


  • Dean Cruddace

    Hi Hyderali, yeah go to: file > Make a Copy
    Then you can play to your hearts content :)

    • hyderali

      Hmm..Can’t see any data in “Content Ideas”? Added keywords & getting competitors but nothing in content ideas? Am I doing something wrong?

  • Dean Cruddace

    I will have a look first thing Monday morning mate.

Read our PPC posts here...
Read our Dev posts here...