I have been interested in analysing various Twitter stats and producing lists of top twitter users (j-j.co.za/toptwits) for some time now and been using various tools to do so. This has been challenging and the results I have been producing have been limited as a result. The tool I have used most is NodeXL,which is more for mapping social networks but has the side effect of being able to download “extended statistics” for each of the Nodes (people) on the network. This however does not give me all of the information I wanted and also works on screen names rather than the Twitter ID’s, so if someone changes their name they fall off my list.

Searching on-line I found that Google Docs spreadsheet provides a function ImportXML which a number of people have been using to bring XML results into a spreadsheet. It appears to have some limits (50 calls per document) which at the time seemed problematic since I was wanting to work with much larger lists. I know better understand some of the Twitter API calls so that limit is less extreme than I first though. Nonetheless I wanted to work in Excel as I am more comfortable with it.

I found online that Excel can import an XML document, and that you can enter a URL there, which includes the ability to enter the Twitter API calls directly. Using the macro recorder I then figured out the calls and put some basic scripting around it to take a column of screen names, break it up into batches of 25 and then execute the XMLImport function for each batch and insert the results into the sheet.

A couple of (minor) snags:

• Twitter has rate limiting, so you can execute only 150 requests per hour (this increases to 350 if you authenticate, which I haven’t figured out how to do yet in Excel). As a result of this I have restricted the tool to 250 names, being 25 x 10.
• Twitter in the API documents also strongly suggest that for large calls to the lookup.xml API you use POST rather than GET requests. As I am not sure how to do this in Excel I use a batch size of 25 (vs the 100 allowed) considering that a “reasonable” size
• The XML Import Map gets generated dynamically based on the results returned by Twitter, and these maps are not 100% consistent. This meant that each block must have its own set of headings. Most likely the columns you are interested in will be consistent, but check to ensure there is no misalignment.

You can edit the VB Script (See line :  For Each Cell In Range(“$A7:$A256”) .Cells ) to increase the size of sheet to cope with. Currently the tool executes 10 calls of 25 items each, you could increase that to say 120 calls of 50 to provide a list size of 6000 items. I would suggest if going this route that adding a pause after each call (say 30 seconds) may be the polite thing to do and could avoid trouble with calls being rejected.

Download the tool here : twitter lookup tool v2

It has some sample data so you can just click on the * to run the download, or you can dive in and replace the list of names with your own list. Please ensure that the “clean” screen names are shown there, no @’s. In theory the TwitterID should also work, haven’t got around to trying that yet.

The results provided allow you to do some interesting analysis. I have included sample screen names from the Afcon 2013 players in the pre-populated sheet.  Run it, remove the headings, provide an auto-filter and you can quickly get things like :

• Most followed users (sort by followers)
• List of verified accounts (filter by the verified flag)
• Calculate the Tweets to followers ratio
• Execute the queries over time and calculate tweets per week or month

I intend using this to populate my personal research database. I would also like to be able to upload a “Twitter List” directly from Excel, but still need to figure this out, along with how to make calls to Klout to see the Klout scores of a list to do similar analysis.

If you have ideas around other tools or methods that can do this kind of extraction, analysis or list management more effectively then I would be very interested in hearing from you.

Download the Excel tool here: twitter lookup tool v2 and don’t forget to enable Macros. The code is freely view-able if you are paranoid and want to see what it does before running it

Tags: Analytics, download, Excel, ImportXML, Klout, Script, Twitter, XMLImport

Categories: Research, Social Media

---