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