Twitter search results are more useful in a spreadsheet

I know I've overstayed my welcome in this utterly boring space of Twitter API data in a spreadsheet - but I just have to share one more... This time, it's something that might actually be useful (omg, did I just admit that all the other stuff was useless? uh huh).

Let's say, for example, you are a product manager (hey - i know one of those) and you want to know who is tweeting about your product... You do a twitter search! Cool! It's really easy to see recent tweets about your product. You can page through the results, and, in some tools even see a quick info box on the specific Tweeters listed (like location, number of followers, etc). But - let's say you want to calculate the total "reach" or, as @psychemedia called it in a recent tweet, "amplification" of the tweets which match your search?

"There's an app...err... a spreadsheet for that" !

Here's what this spreadsheet does:

- Pulls the most recent (up to) 400 tweets which match your search terms (there's limits in the twitter search API how far in time that will go back) into the spreadsheet.

- It includes: the tweet text, author, date/time

- For the most recent 50 tweets, it pulls the number of followers for the person who tweeted somethhing that matched your search terms... and it adds those up and give you a "Distribution for the most recent 50 tweets". In other words - the number of people following the people who tweeted about the thing you searched for.

- For the most recent 50 tweets, it filters those which are ReTweets (RT) and sums the followers for the authors of those tweets - giving you a distribution of RTs of that concept or tweeter (this is meant mostly for searching for a tweeter's screen name to see the distribution of RTs of that persons tweets).

This is not rocket science (I realize) - but it forms a basis to allow you to:
> get a sense for the amplification of a specific term or product name or tweeter
> focus on the tweets in a search result set which were authored by highly-followed tweeters (if you are a PR/marketing/customer-service person in particular)
> do further stuff with this data that I haven't thought of or had time to do...

Like the other twitsheets I've done - this is just meant to be a starting point for people with a purpose... so if you come up with something useful from this, let me know!

Twitter says: Coldplay follows the unpopular, Oprah doesn't

Coldplay follows 2,624 tweeters on twitter - who, on average, are only followed by 10 people each. Oprah on the other hand only follows 14 people, who, on average, are followed by 709,350 people (as of this post on 9 June, 2009). I know - I spent the past 14 weeks counting! Nah... actually - there's a cool way to do this in - yes, you guessed it - a spreadsheet.

In tweaking the TweeterScore and other TwitSheets I've discovered that using xpath, you can quickly summarize data from an XML feed into a spreadsheet... that is, you don't have to do the adding and looping yourself.
In one command, you can summarize data across all the entries in any XML feed.
For example... the twitter XML data for "friends statuses" (the recent tweets of all the people you follow) can be grabbed with a URL like this:
http://twitter.com/statuses/friends/whatsername.xml - and you can do that for any tweeter, to see the statuses of (and other stuff about) all the people they follow...

The actual XML data - in brief form - includes something like this (shortened massively):
<users>
  <user>
    <id>1010101</id>
    <name>Whatsher Name</name>
    <screen_name>Whatsername</screen_name>
    ...
    <followers_count>1031</followers_count>
    ...
    <status>
      <created_at>Tue Apr 07 22:52:51 +0000 2009</created_at>
      <id>1234567890</id>
      <text>brb - going to get ice cream now</text>
      <source><a href="http://www.tweetdeck.com/">TweetDeck</a>
      </source>
      ...
    </status>
  </user>
</users>

So - for the given tweeter (that you gave as the tweeter-screen-name.xml file name) it gives information for every other tweeter that person follows. A cool way to get the raw data for every person they follow. But the best part is the ability to summarize across all the entries... and XPath lets you do that.

Example: Let's say I wanted to know not just all the names of the people that Oprah follows (no idea why i picked her) - but I wanted to know the average number of people who follow the people she follows. That would tell me whether Oprah is following her fans (unpopular) or other celebs (popular, with lots of followers themselves).
I can do that in a spreadsheet in one command (almost).

I use the ImportXML() command with an XPath command string of "sum(/users/user/followers_count)" to get the total number of followers of all the people being followed by this tweeter... so for Oprah, the spreadsheet formula looks like this:
=importXML("http://twitter.com/statuses/friends/oprah.xml","sum(/users/user/followers_count)")
Divide that by the number of people she follows, and you have the average...
I'll leave it to you to check out this sample spreadsheet which does a whole bunch of this xml manipulation to compare 10 tweeters.

Show-n-Tell gadget in honor of GoogleIO

Find out more about this gadget... or, If you see nothing above this message, or you just don't get this whole post... please excuse me and move on to your next critical task for today. ;)

TweeterScore: a Tweeter report card

I made some enhancements to that original Tweetquency spreadsheet I posted, and turned it into something a bit more useful (an overstatement, for sure). While it's fun to look at the profiles of tweeters on Twitter, to see how many people they follow or follow them, it might be more interesting to understand their tweeting habits. How often do they tweet? How often do they reply or include a link? I created this "Tweeter report card" to help do this easily for any Twitter screen name.

We can't use the typical subjects seen in school report cards (thank goodness), so I had to make up some of our own. Here's what you'll see on the TweeterScore report card which spans the past n tweets (which you can set between 5 and 500):

• Tweetquency, viewed as a chart (this version, btw, lets you change the charting buckets, in case you want more detail for those tweeters who are too concentrated in the long or short end of the duration curve (sorry - sounds like wall st.).
• Follow factor - which is a simple measure of the "cost of getting a follower". It's just a ratio the number of followers one has for each person they follow. A super high number here usually represents a celebrity of sorts.
  
  
  
• Quietness - which rates high for the less tweety of us in the crowd (included, quite simply, to make me feel better about my tweetlessness), an inverse measure of the next one...
• Chattiness - which is the average number of tweets per day. These inversely represent the same data which is in the tweetquency chart, but on average. The most chatty will have numbers above 20 or more (hi Tara!)
• Link-i-ness rates the percentage of recent tweets which contained a link
• @Reply-ness shows the percentage of recent tweets which contained at least one @reply.

Beyond pure fun, the usefulness of these measures might arise when, for example, a small business wants to know the habits of another tweeter whom they feel is doing things "right" on twitter. See some examples included here - such as CNN Breaking News - which hardly ever includes a link, has huge followfactor (they don't need to follow others to get people to follow them) and they only tweet on average about once per day (rounded, but still surprised me). Then look at Orli Yakuel, who is constantly pointing people to great products and sites, including links in 62% of her recent tweets. Matt Cutts and Tara Hunt (missrogue) just have huge followings, but one doesn't follow many people and the other does - so their followfactors are quite different.

I have some ideas for how the trends seen across types of tweeters would make an interesting thesis either in business or social research... for example, Techcrunch (not shown here) had a Linkiness score of 100% over the 250 tweets I collected. Sounds like a Blogging business trend we might have predicted. I also bet the general shape of a tweeter's Tweetquency chart can be indicative of...(yawn)... ok - I'm boring myself now... on to the next project ;)

I'll write more about how this was all done in a future post - and then describe a more useful way to use these mechanics.... but for now, if you want to score a few tweeters you know - Get your own copy of TweeterScore... and find me on the first day of 140tc or at GoogleIO next week if you have questions about all this sheet ;)

Finding the most popular tweeter tools

I've become a little more interested in Twitter lately - and finally started looking for something to use for tweeting and searching beyond what's on twitter.com. Of course, I began by starting a list (yes, a spreadsheet) of the tweeter tools available - but that list too quickly grew to well beyond 100 choices. So, while interesting, it was too daunting to analyze or try each tool in detail.

Then I realized that I could see what tools people use for tweeting pretty easily - so, if I trust that popularity might be some indication of value, I could at least find a narrow set of popular tools for tweeting (won't help me with search, but hey...)

So - on three separate days this past week, I took a sample of 1,000 tweets - that's 3,000 in total - and counted which tools were used. Not necessarily a statistically significant methodology, but not bad. See my results in the spreadsheet embedded here. I left most of the collection and math to another post - but figured other people might want the results sans all the formula rigor anyway. The "Follower Score" takes into consideration the number of followers of each tweeter - so that a tool used by a heavily followed Tweeter will rank higher. For now the chart just uses the pure Tweet Count to tell me that I should use TweetDeck, TwitterFeed or TwitterFon, if I just want to follow the pack.


I'm starting to collect a few interesting spreadsheets related to twitter these days... more to come on this...

Stock Screen Revisit - howz that doin' anyway?

I posted back in February about a stock screen I ran using Google Finance - to pick stocks which seemed to have gotten beat up in the market while still having no debt and consistent income growth. (if you look at that link now - it will show the current stocks which meet the criteria I set, not the same stocks as on Jan 27th when I originally ran it).

I realized today that I hadn't revisited that to see how that list of stocks has performed against the market...
Well - here's the deal.

On Jan 27th, 2009, the Dow was at 8174. The S&P 500 was at 846.
On May 8th, 2009, the Dow closed at 8575. The S&P was at 929.
That's a total increase of: 4.9% for the Dow and 9.8% for the S&P 500.

On Jan 27th, 2009, my list of stocks was valued at: $21,929.
On My 8th, 2009, that list of stocks was valued at: $27,351.
That's a total increase of: 24.7%.

oh. ok. So now what? I'll just hop into my time machine - go back to Jan 27th and actually invest? nah. I'm so tired of that time machine. I think it's time to try that same screen a few times... basically it says that stocks that were beat down, which still have some fundamental value and no debt, MIGHT perform better than the market as a whole... Or, at least it did in this one instance... by a healthy margin.
(and, again, I'm not giving any financial advice here, and if historic results were any indication of future results, you might get lucky once or twice, but in the long run you'd lose your shirt most likely).
[Update: here's a link if you want more info on the spreadsheet itself]

My Dad would have loved this... he would have played with it for hours and then he'd go check in on his friends at Silicon Investor to see what they thought... and then he'd make some pretty great trades. I only mention that because I'm thinking about him all day today, this 2nd anniversary of the day he left us to toil away in the markets (and this life) without his incredible love and guidance. Damn.

Chart your Twitter Tweetquency

I follow a few people on Twitter who pretty much make me feel like I'm with them every step of their day... and I follow some who tweet so infrequently, that I feel lucky if I catch what they say between all the other blabber. That made me think that it might be interesting to see a chart of how frequently we tweet - our Tweetquency. As many of you know, I just can't help myself sometimes - I have to do everything on a spreadsheet.

Click the image to see a view-only version of this spreadsheet.
You can also click here to get your own copy of this spreadsheet which you can edit and play with.

I expect to be playing lots more with this, but here's how this simple version was done... I created a spreadsheet with two sheets. In one sheet (the second one in the file), I used the Twitter API to pull in what's called a user timeline - the same thing you see when you click on a user's screen name in Twitter. This is done using the =ImportFeed() function in Google Docs. The actual formula looks like this:

=importfeed("http://twitter.com/statuses/user_timeline.atom?screen_name=" & B1, "items", true)
- where cell B1 holds the user screen name I want to grab.
[note - I also tried to have a count - to pull in a certain number of tweets, but that doesn't seem to work - so this spreadsheet actually is totally dependent on puling in 20 tweets at a time right now - and does the formula 5 times to get 100 tweets to play with. Look at that second sheet and scroll down a bit to look for the repetitive formulas and you'll see this hack.]

Then I have a second sheet (which is actually the main/first sheet you see) which is basically the user interface - it allows you to enter a screen name and shows you the chart once the data in that other sheet is collected.

It's fun to enter a name of someone you follow, just to show you something visually that you already know - that they either blab all the time, or hardly ever ;)

For now, this only looks at the prior 99 tweets... but I'll likely be posting some updates and new versions of this TwitSheet... so let me know if you have other ideas or watch this space...