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

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.

Tweet This Post

If you have enjoyed this post you can subscribe to the rss feed to read more about how you can monitor and protect your brand online