We’ve rolled out a change so you get more data when you use the Twitter search tool!

We’ve changed four columns. They used to all just randomly return one thing. Now they return all the things, separated by a space. The columns are:

• hashtags now returns all of them with the hashes, e.g. #opendata #opendevelopment
• user_mention has been renamed user_mentions, e.g. @tableau @tibco
• media can now return multiple images and other things
• url has been renamed urls and can return multiple links

We renamed two of the columns partly to reflect their new status, and partly because they now match the names in the Twitter API exactly.

What can you do with this new functionality?

We had a look at the numbers of media, hashtags, mentions and URLs for a collection of tweets on a popular hashtag (#kittens), using our favourite tool for this sort of work: Tableau. It requires a modicum of cunning to calculate the number of entries in a delimited list using Tableau functions. To count the numbers of entries in each field, we need to make a calculated field like this:

LEN([hashtags]) - LEN(REPLACE([hashtags],'#',''))

This is the calculation for hashtags, where I use # as a marker. You can do the same for mentions (using @ as the marker), and for URL and media use ‘http’ as a marker:

(float(LEN([urls]) - LEN(REPLACE([urls],'http','')))/4.0)

Hat-tip to Mark Jackson for that one.

For URLs and media we see that most tweets only contain one item, although for URLs there are posts with up to six identical URLs, presumably in an attempt to get search engine benefits. The behaviour for mentions and hashtags is more interesting. Hashtags top out at a maximum of 19 in a single tweet, every word has a hashtag.

#InstaSize #matteo #cat #animal #pet #cute #kawai #sweet #kitten #f4f #like4like #my #sleep #dream #peach #summer #nature #love #family b…

— LadyValy (@LadyValyTV) May 20, 2014

The distribution is shown in the chart below, each tweet is represented by a thin horizontal bar, the length of the bar depends on the number of hashtags, the bars are sorted by size, so the longest bar at the top represents the maximum number of hashtags.

For mentions we see that most tweets only mention one or two other users at most:

Thanks to Mauro Migliarini for suggesting this change.