A way to archive and display Twitter hashtag chats

It’s interesting to watch the popularity of Twitter hashtag chat communities (like #uklibchat) grow. It’s also interesting to the number of different ways these chats are recorded from dumping/exporting a Twitter Search into a word or pdf doc to using tools like Storify to capture the highlights. If you organise or are thinking of organising a #chat here’s one way you might want to keep a complete record of what was said and a couple of ways you can use this data.

Archiving the conversation

Perhaps not surprisingly it starts with my Google Spreadsheet Template for capturing Twitter searches. I realise that this solution isn’t as straight forward as old services like Twapper Keeper and it can look a bit daunting but trust me it’s not that bad and even with my dodgy typing it can be setup in under 3 minutes.

Publish archive as a spreadsheet

Here’s an example from the last #uklibchat (for demo purposes only, the archive isn’t been updated). What you can do is select the rows that cover your chat period and paste them to a new sheet (as I have done here). Once you’ve copied you might want to sort on the Time column to get the tweets back in chronological order. At this point you have a couple of options. You can File > Publish to the web generating a link for the sheet (or as I’ve done just File > Share then entire spreadsheet).

Embed tweets in your blog

By adding a column with the formula ="[tweet "&M2&"]" and filling it down the entire column of your chat sheet when you paste the values generated into certain blogging tools like WordPress.com the twitter status urls are automatically embedded in the page like the one shown below (you might want to be selective about the tweets you include as too many will kill your page load time):

Experimental – Visualise, interact and replay the conversation

TAGSExplorer #uklibchatWith you #chat in a Google Spreadsheet another thing you can do is use my TAGSExplorer to let other people see and interact with the conversation.  Here’s #uklibchat for the 12th January:

Some things worth noting: click on a node lets you see their tweets, replies and mentions; and you can replay the conversations with that person 

What do you think?


Join the conversation

comment 7 comments
  • #assnchat and #cmgrchat: Happy Community Manager Appreciation Day! at Danegeld

    […] Last week I downloaded tweets for both chats using Searchhash and scanned them in a spreadsheet, but at 250+ and 800+ tweets respectively during the hour it’s not really feasible to follow a chat in this way. Alternatives for grabbing a whole chat include Chirpstory, which is slightly more readable but does not offer a download, and Martin Hawksey’s Google spreadsheet solution. […]

  • @ginabegin (Twitter)

    Thank you so much for doing all of this! I do have a question… I can’t get the embed formula to work in the spreadsheet. No code comes up. I just put: [tweet “&M2&”] into the head of the column and pressed enter. I am sure I am doing something wrong. I also tried it without the brackets. Can you help? I tried searching google but can’t figure it out.
    Also, my Twitter Chats are every Tuesday night at 7pm MST. I have the Trigger set up to Capture tweets on “Day” and Time: 7-8pm MST. Will this auto capture everything during my tweet or do I need to manually get it to capture?
    Thank you!

  • #vircomm12: a Twitter archive at Danegeld

    […] A way to archive and display Twitter hashtag chats  – nice explanation from Martin of how to use TAGS plus some ways you can use the data […]

  • Data Visualizing For Dummies « Playing With Sand

    […] where I started using Google Spreadsheet first to try and collect some Twitter data. “A way to archive and display Twitter hashtag chats” helpfully lays out how to install […]

Comments are closed.