THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast. Andy is no longer updating this blog. His current blog is

Measuring Social Media: Twitter


If you haven't previously been warned: I am an engineer. I tinker here and there with social media. I've been mining Twitter as part of my fascination with Social Intelligence (BI applied to social media).

Measuring Twitter

I was reading an article entitled The Twitter Star: Nova or Supernova? by Brian Solis about the decline in Twitter site visits. I'm not surprised by this, but I was surprised that Evan Williams [Blog / Twitter] defended the traffic decline.

"Why were you surprised, Andy?" I'm glad you asked!


AndyWeather is the name of a Twitter account I created 26 Dec 2008. The setup is shown in this slide from my Business Intelligence at REST presentation:

(Click to view full size)

My weather station reports to a remote base station which is connected to my server. The weatherOS software (provided by Oregon Scientific) updates CSV files on the server every hour. At thirty seconds past each hour, SQL Agent runs an SSIS package that pulls all that raw data into a SQL Server database. Each minute, a .Net application named AndyChatter (I have to work on that name... the first version of AndyChatter interacted with MSN Messenger, back in the day) checks for new data and if it finds any, it tweets.

I'm not going to get into all the things that happen under the hood. I will tell you I like the Twitter API - a lot. I will also share one of the pieces of data returned by a successful tweet is a status\id. I'm not sure if status\id is exactly sequential or not, but it is a large integer value that appears to increase with each tweet. 

Whenever AndyChatter tweets, it captures and stores this value.

What To Measure?

This goes back to why I was surprised by Evan Williams' defense of the lack of traffic on the Twitter website: I don't think that's an accurate measure of the Twitter user experience (UX). I think a better measurement is how many tweets have been sent over time.

Why? Because of the Twitter API! I'm not the only person tinkering with it. There are lots of applications and websites out there that interact with Twitter via the API. Users do not have to visit the site to send a tweet.

To that end, I created a chart from the difference in status\id values for the first tweets from AndyWeather each month, for the past few months:

The chart shows a decline in tweets in half the months in the graph, each followed by a sharper increase (by comparison) in tweets. Overall, the linear trend is growing. Granted, it's not increasing 1,300% in any of these months; but Oprah could only sign up once.

:{> Andy


Published Thursday, December 17, 2009 8:00 AM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



dan said:

Very interesting, thought provoking use of Twitter.

December 17, 2009 9:17 AM

Leave a Comment


This Blog



My Latest Book:

Community Awards

Friend of Red Gate

Contact Me


Privacy Statement