If you follow us on Twitter, you know that we share daily historical trivia about what happened on that day in Boston history through the years. What’s that? You don’t follow @HUBhistory on Twitter? You should start.) I have a real job, so I can’t troll through the historical sources every day to find these tidbits. Instead, I let my Twitter archive do the work for me, building a giant chronological spreadsheet of Boston trivia that I can quickly check each day. As I find and tweet new resources, the spreadsheet grows each year.
Around the first week of January each year, I download my archive and convert it into a spreadsheet so I have an updated list. If you have chronological content in Twitter, you might find this process useful, too. (And even if you don’t, it can’t hurt to have a backup of your tweets that’s easy to use.)
- From a desktop class browser, go to your Twitter account and select Settings and Privacy. Scroll down under Content and select Request Your Archive. You’ll get an email when Twitter is ready to give you your archive. Sometimes this takes an hour, sometimes it takes a few days.
- When you get the email saying your archive is ready, download it to your computer and unzip it.
- Right click on the extracted tweets.csv and open it with your favorite spreadsheet editor. I use Google Sheets, but Open Office and Excel also work. (If you use Google Sheets, it will figure out the .csv import for you. You may have to tell Open Office and Excel that the file is comma delimited.)
- When you get it open, you’ll have far more columns than are are useful. At least to me. Delete every column except timestamp, text, and expanded URLs, then duplicate the timestamp column.
- Highlight the second timestamp column, go to the edit menu, and select Search and Replace. In the search box, make sure that the selected range is your timestamp column (in my case, “tweets!B:B”), then enter “2019-” into the Find field and leave the replace field blank, and finally hit Replace All. Repeat this step for every year you have tweets for. (The idea is to strip out the year from the timestamp, so you can sort by month.)
You’ll know you’re done when it stops finding matches.
- Now put the cursor in the modified timestamp column and go to Data and select Sort by Column B, A -> Z. Boom, you have a chronological spreadsheet of your tweets sorted by month and day.
- To make things easy to read, I like to size column A down so only the year shows, then size column B down so only the month and day show. To make it easy to work with, I create a sheet for each month. A quick and dirty way to do this is just duplicating the original sheet 12 times, naming each one for a month, then deleting out the rows that don’t belong to that month. We’re left with a well-organized tweet calendar that’s quick and easy to reference.
