ThinkTank is a software package that provides PHP/SQL-based analytics environment for Twitter. It uses OAuth to negotiate access to Twitter user data, and then crawls posts and links into it's own database. It will expand bit.ly links.
Hacking
SQL examples
Some sample MySQL queries
- Who is someone following?
- select distinct u1.user_name , u2.user_name from ta_users as u1, ta_users as u2, ta_follows where ta_follows.follower_id = u1.user_id AND ta_follows.user_id = u2.user_id and u2.user_name='libbymiller';
- Find URLs posted by some user
- SELECT author_username, expanded_url, title FROM ta_links, ta_tweets, ta_users where ta_tweets.status_id = ta_links.status_id and author_username = ta_users.user_name;
- Find what people are saying about BBC programmes:
- SELECT author_username, expanded_url, title FROM ta_links, ta_tweets, ta_users where ta_tweets.status_id = ta_links.status_id and author_username = ta_users.user_name and expanded_url like 'http://www.bbc.co.uk/programmes/%';
- ...or IMDB:
- SELECT author_username, expanded_url, title FROM ta_links, ta_tweets, ta_users where ta_tweets.status_id = ta_links.status_id and author_username = ta_users.user_name and expanded_url like 'http://www.imdb.com%';
- ...or on Dutch catchup service (omroep.nl via uitzendinggemist):
- SELECT author_username, expanded_url, title FROM ta_links, ta_tweets, ta_users where ta_tweets.status_id = ta_links.status_id and author_username = ta_users.user_name and expanded_url like '%www.uitzendinggemist.nl%';
- Who is saying what about #uksnow ?
- SELECT distinct author_username, ta_tweets.tweet_text FROM ta_tweets, ta_users where author_username = ta_users.user_name AND ta_tweets.tweet_text like '%#uksnow%';
- What is Libby Miller saying about the BBC, ordered by when she said it?
- SELECT ta_tweets.pub_date, ta_tweets.tweet_text FROM ta_tweets, ta_users where ta_users.user_name ='libbymiller' AND author_username = ta_users.user_name AND ta_tweets.tweet_text like '%#bbc%' order by pub_date;
