How to get RSS into Excel: Google docs for analysing online buzz

A lot of search conversation online is about:

  • online marketing (of one form or another) - often including encouraging more conversations about a client online
  • reputation monitoring - in real time and looking at both alerts about bad news and information about the spread of good news
  • reputation management - sometimes not so different from regular SEO, but with different goals

Google docs

Research is an area that is often over-looked (beyond the research needed to do the above - e.g. keyword research).

Large brands often care intensely about a level of detail that simply doesn’t break onto the radar for small businesses. One area that we have found ourselves doing more of recently is analysis of online conversations, share of voice, positive and negative slants and tracking the way conversations spread.

I have sometimes felt that it is a bit strange that I should be a good person to do this kind of thing, but with a background in stats, consulting and (obviously) search, I have been able to dig out a few tricks that help with the analysis.

One of these tricks is what I wanted to share today.

Background - why am I doing this?

When you are measuring share of voice and tracking the spread of conversations, you are sometimes in the position that you were tracking the topic from the beginning, but sometimes you need to do the analysis retrospectively.

When you are digging into something retrospectively, you can carry out all kinds of search on a variety of platforms - regular search engines, dedicated blog search engines and buzz or reputation tracking tools, but the output is typically HTML and RSS.

Now, I find that Excel is by far and away the best tool for slicing and dicing data (I love the way the new Excel makes pivot tables and pivot charts so much easier by the way). It is not particularly easy to get data from an RSS feed into Excel in order to cut and splice.

In order to save you from the pain of data entry or grep / sed / awk hacking, I wanted to present an easy way to do this:

How to get RSS information into Excel

For example, earlier this week, I was analysing discussion about Sharon Osbourne and Vic Reeves at the Brit Awards (they had a bit of a bust-up, possibly because they represent competitors in the bingo space).

So, supposing I want to get information into Excel about blog posts about both of them. I could start by creating an advanced Google blog search in order to find posts mentioning both names published in the week after the Brits. Then:

  • Create a Google Docs spreadsheet
  • Go to your blogsearch results page
  • Copy the URL of the results as RSS
  • Create a formula in your Google Docs spreadsheet containing:

=importfeed(rss-url,,true)

Make sure you wrap the URL in double quotes. The empty second argument is for restricting which bits of data you want from the RSS (the help is very good). The ‘true’ third argument says you want a header row to tell you what your columns are. If you want to add a fourth numerical argument, you can limit how many rows you return.

Once you have done this, the information from your RSS feed (in this case your custom search) is pulled into the spreadsheet - you can then either work on it there or (more likely) export it to Excel and work on it locally to make some cool charts and graphs for your presentations.

When I first needed to do this, I was just about to dive into an hour-long task of faffing with the data, before I thought of doing this. An hour-long job then took literally 5 minutes. Happy :)

Other cool things Google Docs can do

I haven’t had a play with the rest of the functionality yet, but just the names of some of the other functions imply that they might be pretty cool:

=importXML()

=importHTML()

The importHTML() function appears to be able to drag data in from html tables or lists. Potentially hugely powerful.

Will Critchlow

Will Critchlow

Will founded Distilled with Duncan in 2005. Since then, he has consulted with some of the world’s largest organisations and most famous websites, spoken at most major industry events and regularly appeared in local and national press. Will is part...   read more

Get blog posts via email

14 Comments

  1. Wow - cool! That really is quite nifty. The latest excel is pretty powerful but if it lacks the online integration then google docs will emerge victorious...

    reply >
  2. Possibly. I love the online integration of google docs, but I still love the power and speed of Excel.

    I think it's the only MSFT product I love.

    reply >
  3. Hi Will, great post, very interesting indeed!

    I know you guys have a great ORM tool, but I thought you might be interested to check out a product we’ve just launched. It pretty much does everything you are tying to achieve here without the excel hassles. It’s designed to allow for deep analysis of your online mentions and therefore reputation.

    Its called BrandsEye. If you’ve got time, check it out, I’d love to know what you think of it.

    Cheers

    Tim

    reply >
  4. this is AMAZING!

    reply >
  5. Thanks for the heads-up Tim. I will definitely check out your tool.

    Thanks for the comments everyone :)

    reply >
  6. solle

    but you only get a handful of results. not terribly useful. how do you get all results?

    reply >
  7. This is awesome. Thanks for sharing this. You've saved me hours of work. Very cool, indeed.

    reply >
  8. Steve

    The feed I'd like to capture is behind a login page. Is there any way to get around this.

    reply >
  9. Luke

    Hi Will,

    Fantastic post, I was unaware of the rss import facility on Google Docs so this is was quite a revelation. However, monitoring conversations normally involves hundreds if not thousands of results, and as far as I can work out, there is a limit of 20 results per search on Google Docs, which in many cases does not allow for meaningful analysis. There is clear potential here, so does anybody know a workaround other than OutWit (suggested by Ann Smarty at http://www.searchenginejournal.com/6-ways-to-export-your-backlink-data-in-excel/7693/)?

    Many thanks,

    Luke

    reply >
  10. Hi Will,

    This is nice but have you not experienced difficulty with the 20 item limit imposed by importfeed? If you have a workaround for that I'd really like to know what it is!

    Also, have you played around with importing data from the web in Excel? That seems to have some problems too - it's a bit clunky - but you can bring more than 20 entries into it.

    Regards
    Brendan

    reply >
  11. I am running into the 20 post limit as well.

    Has anyone found a way to work around this?

    reply >
  12. I just selected the XML file in Excel and it opened it. I did try the same feed in Google docs (it was a Google Picasa feed) and it only pulled back the first 20 rows.

    Thanks though.

    Ian Brown

    reply >
  13. Steve

    Which version of Excel are you using?

    When you say you selected it, what did you do?

    reply >
  14. Natalie

    Very cool! Do you happen to know how to set up an import like this for Facebook and/or Twitter?

    reply >

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>