Part of the sales process for Twitter consulting is convincing a new client that Twitter is more than just another way to broadcast their message. You have to show them that what appears to be a random stream of tweets is really a collection of highly qualified sales prospects. By aggregating Twitter users as well as their Tweets, you can extract a great set of sales leads along with their contact info. One way to quickly demonstrate the value of tweet aggregation is to deliver an Excel spreadsheet of sales prospects that meet the client’s needs.
When you aggregate tweets from the Twitter streaming API, it also returns the complete account profile for each user. You can data mine this collection of users to extract highly targeted lists of users, along with their geographical location and home page URL.
The free 140dev Twitter framework is an example of the code you will need to do the tweet aggregation. The schema for the MySQL database it creates shows you it has a table for all the aggregated tweets, which links to the list of tweeting users. Since all of this data is collected for a specific set of keywords, you can then extract personal details on the users who tweet these keywords the most with a simple SQL statement:
SELECT count(*) AS cnt, users.screen_name, users.name, users.location, users.url
FROM tweets, users
WHERE tweets.user_id = users.user_id
AND users.location != ''
AND users.url != ''
GROUP BY tweets.user_id
ORDER BY cnt DESC
LIMIT 1000
The 140dev framework’s example database collects tweets for the keyword “recipe”, so this query gives us the most active tweeters in the food world. Here are the results in phpMyAdmin:
You can then export the results from phpMyAdmin to an Excel spreadsheet, and email it to your client. This gives them solid data in a familiar form. Twitter doesn’t deliver email addresses, and doesn’t even collect phone numbers, but you do get each user’s home page URL. This can be used to gather other contact info, a task that is easily farmed out to people on freelance sites like Mechanical Turk.
So the next time you want to convince a client that Twitter is not just a bunch of kids talking to each other, you can just create a tweet aggregation database for the client’s industry keywords, let it collect data for a few days, and pull out a list of targeted users.
