In yesterday’s post I recommended searching for the screen names of influential accounts as a way of getting high-quality leads for engagement. A related approach is to collect the timelines of key accounts, and extract the people they @mention. This is a good way to find the cool crowd in any subject area. If a leader engages directly with someone, they are worth checking out. You can find code for collecting the past timeline tweets for any account, and then updating this collection on a regular basis in my engagement book‘s source code.
Once you have a leader account’s tweets collected along with all their @mentions, you can run SQL queries to extract the most mentioned accounts, such as:
SELECT COUNT( * ) AS cnt, users.*
FROM tweet_mentions, users
WHERE tweet_mentions.source_user_id IN
(SELECT user_id FROM leaders)
AND tweet_mentions.target_user_id = users.user_id
GROUP BY tweet_mentions.target_user_id
ORDER BY cnt DESC
This query assumes that you have recorded the user ids of leader accounts in their own table.
