March 5th, 2009

Managing Twitter Groups with tags

Update: 6/22/2009: TweetDeck now supports group-synchronization as a built in feature.

Twitterdel.icio.usI’ve recently started to use Twitter and have been impressed with the TweetDeck client. It has a great user interface and makes it very easy to follow different groups of people in multiple visual columns.

One of the first tweets I saw was from Jon Udell, always a fountain of ideas. His recent blog post Collaborative curation as a service reminded me of how flexible can be as a distributed store for tags. I like the curating meme that Jon is promoting, where a little bit of technical infrastructure can go a long way to enable virtually anyone to contribute.

As I started to use TweetDeck and define groups locally in the application, I pondered how it might be better if these group definitions were stored on the web. They could then be accessed from different systems where I might use TweetDeck or other twitter clients for that matter. (Of course this will all happen when Twitter implements groups natively, but in the meantime there is a gap that can be filled). Then it dawned on me — is an ideal place to both manage and share twitter groups!

I wrote two Perl scripts to try out this idea. The first script,, uploads the Twitter URLs of everyone I’m following to and automatically tags them with ‘twitter’. (It acts as a sync process, adding/deleting links as necessary). Then I manually tag them further based on how I want to group them. So for example, I tag Tim O’Reilly with ‘technology’, and Jim Cramer with ‘finance’. I can tag people with multiple tags if I want to read them as part of multiple groups.

#! /usr/bin/perl
# - Sync Twitter contacts to 
use Net::Twitter;
use Net::Delicious;
use Log::Dispatch::Screen;
use strict;
my ($debug, %following);
$ARGV[0] eq '-d' and $debug = 1, shift;
@ARGV > 1 or die "usage: $0 [-d] twitter_username:password delicious_username:password\n";
my ($twusername, $twpasswd) = split(/:/, $ARGV[0], 2);
my ($delusername, $delpasswd) = split(/:/, $ARGV[1], 2);
my $tw = Net::Twitter->new({apiurl => '', 
                           apihost => '', 
                           username => $twusername, password => $twpasswd})
    or die "Couldn't connect to twitter\n";
my $friends; my $page = 1;
do {
    $friends = $tw->friends({page => $page++})
        or die("twitter: ".$tw->get_error->{error}."\n");
    foreach my $friend (@$friends) {
        $following{$friend->{screen_name}} = $friend->{name};
} while @$friends;
my $del = Net::Delicious->new({endpoint => '',
                              user => $delusername, pswd => $delpasswd})
    or die "Couldn't connect to\n";
# Workaround for Net::Delicious:all_posts() not supporting a tags argument:
# $del->all_posts({tag => 'twitter'}) 
$del->config('delicious_posts_all.tag' => '');
my $res = $del->_execute_method("delicious.posts.all", {tag => 'twitter'})
    or die "Error calling delicious posts/all\n";
my $posts = $del->_getresults($res, 'post');
foreach my $post ($del->_buildresults('Post', $posts)) {
    my $username = $post->href; $username =~ s#^;
    if (exists $following{$username}) { delete $following{$username} }
    else {
        # Delete stale entries
        $del->delete_post({url => "$username"});
        $debug and print "Deleted $username\n";
# Add new entries
foreach my $username (keys %following) {
    $del->add_post({url => "$username",
                    description => "Twitter / $following{$username}",
                    tags => 'twitter'});
    $debug and print "Added $username\n";

The second script,, creates/updates a group in TweetDeck corresponding to each tag that I have assigned to the Twitter links in When I reclassify people in, I simply stop TweetDeck, run the script, and restart TweetDeck. [When I add new people in Twitter and tag them for the first time, I end up restarting TweetDeck twice due to an implementation issue]. In any case, it’s a pretty simple process.

#! /usr/bin/perl
# - Update TweetDeck groups from 
use Net::Delicious;
use Log::Dispatch::Screen;
use DBI;
use File::Glob ':glob';
use strict;
my $debug;
$ARGV[0] eq '-d' and $debug = 1, shift;
@ARGV > 0 or die "usage: $0 [-d] delicious_username:password\n";
my $sqlitefile;
if ($^O =~ /linux/i) {
    $sqlitefile= bsd_glob("$ENV{HOME}/.appdata/TweetDeck*/Local Store/td_*.db");
elsif ($^O =~ /mswin32/i) {
    $sqlitefile = bsd_glob("$ENV{APPDATA}\\TweetDeck*\\Local Store\\td_*.db");
my (%groups, %gcids);
my ($delusername, $delpasswd) = split(/:/, $ARGV[0], 2);
my $del = Net::Delicious->new(
    {user => $delusername, pswd => $delpasswd, debug => $debug});
# Workaround for Net::Delicious:all_posts() not supporting a tags argument:
# $del->all_posts({tag => 'twitter'}) 
$del->config('delicious_posts_all.tag' => '');
# Get all twitter group memberships from
my $res = $del->_execute_method("delicious.posts.all", {tag => 'twitter'})
    or die "Error calling posts/all\n";
my $posts = $del->_getresults($res, 'post');
foreach my $post ($del->_buildresults('Post', $posts)) {
    my $username = $post->href; $username =~ s#^ or next;
    foreach my $tag (split(' ', $post->tags)) { 
        $groups{$tag}{$username} = 1 unless $tag eq 'twitter';
my $dbh = DBI->connect("DBI:SQLite:dbname=$sqlitefile", '', '');
my $columns = $dbh->selectall_hashref('select * from columns', 'cID');
foreach my $cid (sort keys %$columns) {
    # Exclude all non-group columns
    if ($columns->{$cid}{cType} > 0 && $columns->{$cid}{cType} != 999) {
        delete $columns->{$cid}; 
    # Look for corresponding groups that already exist
    my $cname = $columns->{$cid}{cName}; $cname =~ s/^Group: //;
    if (exists $groups{$cname}) {
        $gcids{$cname} = $cid;
        delete $columns->{$cid};
# Assign new groups in the columns table
my @cids = sort keys %$columns;
my $sth = $dbh->prepare("update columns set cName=?, ctype=? where cID=?");
foreach my $group (sort keys %groups) {
    next if exists $gcids{$group};
    last if !defined($gcids{$group} = shift @cids);
    $sth->execute("Group: $group", 0, $gcids{$group});
# Clear any stale groups in the columns table
$sth = $dbh->prepare("update columns set cName='',ctype='' where cID=?");
foreach my $cid (@cids) { $sth->execute($cid) }
# Set up userid hash map
my %userids = map @$_, 
    @{$dbh->selectall_arrayref('select fScreenName,fUserID from friends')};
# Rebuild the groups table
$dbh->do('delete from groups');
$sth = $dbh->prepare('insert into groups (gID, gCID, gUserID) values (?,?,?)');
my $gid = 1;
foreach my $group (keys %groups) {
    foreach my $username (keys %{$groups{$group}}) {
        $sth->execute($gid++, $gcids{$group}, $userids{$username});

Managing TweetDeck groups using tags opens up a broader set of possibilities. I can see how others have tagged the same people, and vice versa. If enough people were to do the same thing, we could build up a crowdsourced view of Twitter broadcasters. This is the kind of experience that made Spock so exciting in its early days, when anyone could tag everyone, with the results immediately available for all to see.

In a recent interview, Yahoo’s CEO stated that they are looking at how to “partner with sites such as YouTube, Twitter, Facebook and Skype to make the user experience as seamless as possible as they move from Yahoo to more social places”. With, I think Yahoo might have the capability to fill both the Twitter-group gap and the Spock-tagging gap, and advance their social software strategy in the process.

4 comments to Managing Twitter Groups with tags

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>