The use of Twitter to collecting tweets around an event hashtag allowing participants to share and contribute continues to grow and has even become part of mass media events, various TV shows now having and publicising their own tag. This resource is often lost in time, only tiny snippets being captured in blog posts or summaries using tools like Storify, which often loose the richness of individual conversations between participants.
It doesn’t have to be this way. Using a combination of Google Spreadsheets as a data source and a simple web interface to add interactivity it’s possible to let users explorer your entire event hashtag and replay any of conversations.
View example conversation replay
Try out a LIVE version
Update: If you are still struggling to understand the concept Radical Punch have done a overview of this tool
Here’s how to archive event hashtags and create an interactive visualization of the conversation (written instructions below):
Twitter: How to archive event hashtags and create an interactive visualization of the conversation
Capturing the tweets
Use this Google Spreadsheet template.
For more reliable data collection it’s recommended that you follow the steps to get authenticated API access to Twitter search results and setup a ‘script trigger’ to automate collection. Here are instructions on how to do it:
- Open the TAGS Google Spreadsheet making a copy
- Register for an API key with Twitter at dev.twitter.com/apps/new. In the form these are the important bits:
- Application Website = anything you like
- Application Type = Browser
- Callback URL = https://spreadsheets.google.com/macros
- Default Access type = Read-only
- Once finished filling in the form and accepting Twitter’s terms and conditions you’ll see a summary page which includes a Consumer Key and Consumer Secret
- Back in the Google Spreadsheet select Twitter > API Authentication (you’ll need to select this option twice, the first time to authorise read/write access to the spreadsheet). Paste in your Consumer Key and Secret from the previous step and click ‘Save’ (if the Twitter menu is not visible click on the blue button to show it)
- From the spreadsheet select Tools > Script Editor … and then Run > authenticate and Authorize the script with Twitter using your Twitter account
- While still in the Script Editor window select Triggers > Current script’s triggers… and Add a new trigger. Select to run ‘collectTweets’ as a ‘Time-driven’ choosing a time period that suits your search (I usually collect 1500 tweets once a day, but increase to hourly during busy periods eg during a conference). Click ‘Save’
- Now close the Script Editor window. Back in the main spreadsheet on the Readme/Settings sheet enter the following settings (starting in cell B9):
- Who are you = any web address that identifies you or your event
- Search term = what you are looking for eg #jiscel11
- Period = default
- No. results = 1500 (this is the maximum Twitter allows)
- Continuous/paged = continuous
- Click TAGS > Run Now! to check you are collecting results into a ‘Archive’ sheet
- To allow the results to be visualised from the spreadsheet select File > Publish to the web… You can choose to Publish All sheets or just the Archive sheet. Make sure Automatically republish when changes are made is ticked and click Start publishing
Creating a public interactive visualisation of the archived tweets
- Copy the url of the spreadsheet you just created
- Visit hawksey.info/tagsexplorer and paste your spreadsheet url in the box, then click ‘get sheet names’
- When it loads the sheet names leave it on the default ‘Archive’ and click ‘go’
- You now have a visualisation of your spreadsheet archive (click on nodes to delve deeper)
- To share the visualisation at the top right-click ‘link for this’ which is a permanent link (as your archive grows and the spreadsheet is republished this visualisation will automatically grow)
Last updated by Martin Hawksey at .
Martin,
Thanks so much – this is the best tweet-archiving tool I have managed to find! You’ve saved my research project! Will be recommending this one for sure!
Regards,
Jenni
sir, if I have to specify multiple tags do I need to obtain multiple keys?
secondly is there a way this tool can help me to obtain only retweets/ replies to a a particular tweet containing hashtag
sir,
the tool is perhaps the first integrated one. i have one doubt whether we can specify multiple tags, secondly can we have multiple sheets archived corresponding to multiple tags data. if yes where can we see them.
can we import the spreadsheet to nodexl.if yes, how??
Hi Martin, this is fantastic and I’ve used it a bunch of times for events. Thanks very much for sharing a brilliant tool.
I’m struggling to make complex search terms work. Could you possibly share some examples?
In point 3 of the instructions you say “You can control the type of data returned by changing the column heading” – sorry for me being slow, but I can’t see where to change the column heading. Can you advise please? Thanks very much for your help.
Hi,
Thanks for making this guide!
I started making a collection but it seems that the script soon fills the spreadsheet, exceeding maximum cell count. At the same time, data in ‘Archive’ seems to get lost (or at least FX is not displaying anything in the window).
What would you recommend in terms of long-term archiving, should I just start creating manually copies of the original file for real archives and manually clear out stuff from the ‘Archive’ sheet?
Al
Hello Martin, I have the same problem as Al here. I, too, would welcome a recommendation.
Make a copy of your original or download archive and then clear all of the archive sheet except the header and 1st row after that. Then just keep on archiving. (I find it best to remerge the archives in Excel)
twitter google archive script pingback
for its contents. This is a safe-cache copy of the original web site.