Saturday, May 2, 2009

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...

2 comments:

Robert Konigsberg said...

JR, that's interesting because I find that I'm definitely overwhelmed by the frequent tweeters. The UI really doesn't support that, which only really means we need to improve the mobile / desktop clients!!

mahadewa said...

This is cool! I didn't know that Google Docs can do API stuff!
Thanks for the info.