This guest post comes from Adam Green, a Twitter API consultant and author. Adam blogs about Twitter programming at 140dev.com and tweets from 140dev. His latest book, Twitter API Engagement Programming, is available on Amazon in paperback and Kindle editions. This is the third installment in a four-part series. Part 1 can be seen here and Part 2 is here.
I run my own queries against MySQL databases using phpMyAdmin, and often create a fancy User Interface to display query results for public web pages. For engagement systems I build for clients, however, I also create a visually minimal reporting system that they can use to run pre-written queries. This lets me deliver tons of data without worrying about making it pretty for the public. The complete source code for this type of Back-end reporting system is available as part of my engagement programming open source system.
The heart of engagement queries is finding the “best” entities of any type, such as tweets, tags, users, etc. In most cases, best equates to most, so a SQL query based on COUNT(*) and GROUP BY can be used. For example, I can find the most used tags in the tweets collected with:
SELECT COUNT( * ) AS cnt, tag FROM tweet_tags GROUP BY tag ORDER BY cnt DESC, tag ASC
The application of this query can be found in the reporting source code. It results in this report:
Here are a few more engagement queries along with links to the Source Code that uses them.
SELECT COUNT( * ) AS cnt, users.* FROM tweet_mentions, users WHERE tweet_mentions.target_user_id = users.user_id GROUP BY tweet_mentions.target_user_id ORDER BY cnt DESC
Most tweeted users:
SELECT COUNT( * ) AS cnt, users.* FROM tweet_retweets, users WHERE tweet_retweets.target_user_id = users.user_id GROUP BY tweet_retweets.target_user_id ORDER BY cnt DESC
Friends who are not following back:
SELECT users.* FROM friends, users WHERE friends.user_id NOT IN (SELECT user_id FROM followers) AND friends.user_id = users.user_id ORDER BY users.followers_count DESC
In the next installment, we’ll look at some of the ways you can automate the process of long-term engagement, especially when carried out by multiple people running a Twitter account over time.