Twitter Engagement Part 3: Identifying Influencers and Leads for Engagement

Guest Author
Nov. 20 2013, 10:00AM EST

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.

Once you start collecting tweets and users in a relational database as described in my previous post, you have the power of SQL queries to identify the best targets for engagement.

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.

Most mentioned users:

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.

Guest Author

Comments

Comments(1)