05 Mar 2012

HOW TO: Search Twitter from Google Spreadsheets and Excel

Twitter search is the only usable (and useful!) social media search tool I’ve been using since it was Summize. It has turned invaluable for many essential tasks:

  • Track your brand mentions: Be sure to check out these cool ideas on various ways to search Twitter to effectively track your brand mentions.
  • Monitor new link building opportunities (e.g. guest post invites or citation requests).
  • Monitor any twitter hashtag (e.g. your company Twitter chat or conference): collect and visualize Tweets around a conference hashtag and easily publish it to the web!

Besides using Twitter search a lot, I love using Spreadsheet-type documents to analyze it. Here are the three ways to search Twitter right from Google Spreadsheets and Excel:


1. Import RSS

While Twitter hid the RSS icons from profiles and search results, the feeds still exist and they are still supported.

To find an RSS feed for your Twitter search, add your search query after this URL string:

http://search.twitter.com/search.rss?q=

For example:

http://search.twitter.com/search.rss?q=internet marketing

http://search.twitter.com/search.rss?q= ”internet marketing” (for exact match)

Now, to import these feeds into Google Plus, we’ll need =ImportFeed function.

=ImportFeed(URL; [feedQuery | itemQuery]; [headers]; [numItems])

Here’s what it means:

  • [feedQuery | itemQuery] – defines what you want to import and where (for example, only the feed title, or the feed author, or everything)
  • [headers] turns table header on / off;
  • [numItems] defines how many items are imported.

For example, the following function returns 10 items from the feed in cell A1 with headers:

=ImportFeed(A1, “items”, TRUE, 10)

See the example here:

twitter-search-google-spreadsheets

Limitations: The published feed will grab only most recent results. This method is still good if you open the document often enough to see only recent few tweets


2. Use GetTweets Script

I have already mentioned this one in the reputation management spreadsheet we have built. This script by Mikael Thuneberg will fetch up to 100 Twitter search results. You can get it from the Scripts Gallery (in Tools menu of your Google Spreadsheet) or just get it from our published template:

GetTweets Script

The script is very easy to set up and use.

>>>Now to the cool idea to use one of the above spreadsheets (FireFox)!

  • Copy the spreadsheet;
  • Use it to search for your brand name or for the name of your competitor;
  • Use one of these FireFox addons to schedule the spreadsheet to open daily, weekly or on the defined days of the week => never forget to check your brand name or competitor on Twitter (without being distracted by tracking it constantly)

3. Excel: Twitter Analytics

Microsoft Office has a free template that let’s you search Twitter from an Excel file as well as analyze the results. To be able to run it:

  • Make sure you are on Windows (very unfortunate to me, so I had to run it on someone else’s computer);
  • Make sure you have Office installed (better if it’s Office 2010);
  • Download and install this software;
  • Download and install this add-in to enable pretty tables and charts in your analytics spreadsheet.

After you are all set, open “Analytics for Twitter.xlsx” Excel file (it should be on your desktop) and run your own search in the search field.

After the results are collected, you’ll be requested to update the PowerPivot and Data tabs.

Once you are done, you’ll be provided with some pretty impressive data:

  • The actual search results: The list of most recent tweets around your topic;
  • General stats: total number of tweets and retweets; unique hashtags, etc;
  • The list (and charts) of most active Twitter users who tweet (and retweet) your search term;
  • Top hashtags that usually accompany your search term;
  • Most active time of the day when your search term is being tweeted;
  • Tone” distribution (Positive, negative, neutral)
  • Many more interesting details and stats you will be amazed at!

Here are the search results visualized through various charts:

Excel Twitter Analytics

Here’s the sentiment explained (words that were found in tweets and defined the sentiment)

Twitter search sentiment - Excel

How awesome is that!


Let’s Compare!

Features: Google Docs Excel
ImportRSS (built-in function) GetTweets (script that needs to be installed) Twitter Analytics(Windows only)
Update search results each time you open the document Yes Yes Update manually
Imports most recent … results ~15 ~100 ~1500 results per query per day
Easy to set up Very easy Very easy Moderately Easy (Windows-only)
Analytics! No No Yes!
Twitter search archive? No No No
Cool tip! (FireFox) Use one of these FireFox addons to schedule the spreadsheet to open regularly automatically => get reminded to check your Twitter reputation or new link building opportunities Open the file to track your brand reputation sentiment on Twitter!

Good luck tracking your brand mentions on Twitter!

Don’t forget to follow our official Ninja Twitter account to have articles like this (and better) to be delivered right to your Twitter home page!