While the Twitter search API can provide a news feed for a given date range you only get the newest 15 Tweets in that period. This isn't going to be of much use if you there was a real buzz about the product.
So, if you want all the Tweets for a given period the only choice is to get the data in HTML format from multiple requests of blocks of search results. Last week I came up with a way to retrieve the required data using two free open source utilities, to grab and save the search results in a file, and to parse the saved data.
I wrapped these utilities in a batch file (which I call tweets.bat) to which I have since added some extra error testing. I also created another batch file, domonth.bat, that calls tweets.bat for each day of a given month. Finally a simple program I wrote is called by tweets.bat and updates a comma separated variable (CSV) file that contains a line for each date with the number of Tweets found.
To perform the analysis in Excel a data source -- the CSV file -- is imported into an existing spreadsheet by refreshing the source, and a graph shows the number of Tweets per day for a given month. I admit it: This is ugly, ugly, ugly, but it works and requires minimal resources.
This system covers the historical data, but usually if you're interested in the public buzz on a specific topic you'll want to monitor that in real-time or thereabouts.
As I pointed out in the first column, to get all of the Tweets in the public timeline you'll need to make arrangements with the folks at Twitter. On the other hand, if the topic you're interested in is generating 15 or less Tweets in a given period (call that X minutes) you could just repeatedly access the RSS feed every X minutes to get a quasi real-time snapshot.
Here's how to do that: In Excel set up an XML Map by selecting Data > XML > XML Source and then click on XML Maps. In the XML Maps dialog that appears click on Add then in the filename field enter http://search.twitter.com/search.rss?q=PRODUCTNAME (fill in what you're tracking there at the end) and then click on Open. You'll go back to the XML Maps dialog so now click on OK. Excel will then display the schema of the feed.
Drag the item pubDate from the XML tree onto your spreadsheet, say, onto cell A3. Now right click on A3 and select XML > Refresh XML data and the cells below A3 will contain the publication times of the last 15 Tweets.
To get an analysis of this data you'll need to massage the pubDate values to extract dates and times, then use a pivot table to correlate the derived values and a pivot chart to plot them. Sounds complicated? It is.
I leave as an exercise for the more intrepid reader to make the spreadsheet periodically refresh and update the graph. On the other hand, I have also wrapped the spreadsheet with an XML Map in an Xcelsius presentation so it automatically refreshes and redraws the graph.
If you want a copy of the batch files I discussed above along with cURL, grep and my nasty little program, as well as the spreadsheet and the details of the automated Xcelsius version, send an e-mail to gearhead@gibbs.com with the subject "TA".