Twitter lets you do a lot with its data. One of the most under utilized things you can do with Twitter data is exporting and analyzing your own tweets. The main reason people don’t use take advantage of this feature is  because it’s a complex and time consuming process to organize the information they give you. It can get very frustrating very quickly.

To make the process easier, I’ve put together a simple guide to exporting your Twitter archive, as well as a spreadsheet template that makes analyzing your Tweets, at least I hope, significantly less painful.

(1 Exporting your Tweets and setting up a spreadsheet. 

First thing you need to do is go to the “Settings” tab on Twitter. Scroll all the way to the bottom and hit the big grey button that says “Request your archive.” Twitter will send an email to your inbox containing a .CSV file of your Tweets, which should come in a few minutes. The timing really depends on the number of Tweets your account has.

(2 Setting up the Google Spreadsheet.

You can use any spreadsheet to analyze your Tweets, but I prefer using Google Spreadsheets. It’s completely free and  you don’t need to download anything to get it to work. Most importantly,  it supports an extremely powerful search functionality called regular expressions, or regex for short. Using regex, you can search your tweets with even more flexibility than you can by using Twitter’s own search functions.

As an example to speed up the process of analyzing your Tweets, I’ve put together a basic spreadsheet template that should make it easier to get started. I’ve included some of my own Tweets as dummy data, just so you can get the hang of things before you import your own Twitter archive.

(3 Create your own copy of the Google Spreadsheet.

Click on “file” at the top left of the screen. Five down from the top, you should see an option marked “Make a copy.” This will create your own copy, which will give you editing rights to the file. This allows you to upload your Tweets to the spreadsheet

After making your own copy, you’re ready to import your Tweets .CSV file.

(4 Importing the Tweets from their .CSV file.

To get your Tweets into this spreadsheet, you need to upload the .CSV download from your Twitter archive to the sheet tab labeled “Data”. You should click on the option labled “file –> Import, then select the “replace current sheet” option when you are viewing the sheet marked “Data.”

*Please Note* Make sure that you rename the tab “data” once you replace it. The functions on the rest of the spreadsheet will not work if the name of the tab is changed.

If you happen to click on anything wrong and lose some of the data, don’t worry. You can always go back and re-copy the original example sheet to start again.

*Another note* is that this spreadsheet may not work for really active Twitter users. As you noticed, I cut down my Tweets to 250 in the spreadsheet example. You may have trouble using this spreadsheet if you have many, many thousands of Tweets.

(5 How to Search your Tweets.

As mentioned previously, the blue colored search bar is setup to support regular expressions. For those who are unfamiliar, regular expressions are basically data sorcery. They let you look for nearly anything you can want in a text document with far more granularity than Boolean (which is what most social media platforms support natively).

You can use some of the example search terms I have in the spreadsheet to get yourself started. Remember, this search is case sensitive, so if you want to find capitalized and non-capitalized versions of the same word, you’ll have to use a few regex tricks.

(6 Some basic tricks to using regex.

() these work the exact same way as discussed in my earlier posts about mastering Twitter search. Like in math, they group operations together.

| this is the OR function in Regex. You’ll need to set them up with a little bit of care. Use the | in combination with the () terms to help organize things. For example tw(itter|eet|eets) matches all the words “twitter,” “tweet,” and “tweets.”

. The period matches one of any character, even a punctuation or a whitespace. This function is especially powerful when used in conjunction with the search terms below.

+ The plus symbol matches one or more of the character that precedes it. So the regex Ha+ matches Haa, as well as Haaa, Haaaa  ect. This is can be especially powerful when used in combination with parenthesis, which applies the plus sign to everything within it. For example @.+ matches all Tweets that contain an @ mention.

* Nearly exactly as the same as the plus sign, except that it matches none or more of the character that precedes it. For example, .* matches all of anything, or nothing.

? The question mark matches one or none of the character that precede it. This function, similar to | symbol, works very well in combination with the () terms. For example, tweet(s)? matches both the singular and the plural form of the word “tweet.”

^ and $ These symbols search for the start of a section of text and the end respectively. This can be extremely powerful, since it will allow you to search for positions of words. As an example, ^@.+ matches all replies (^ matches the beginning of the Tweet, @ matches the @, and the .+ matches as many of anything that follows it).

If you want a full list of regex functions, check out the site Rubular. Google really is slacking on the documentation for Regex in Google Docs, so you’ll have to make do with general regex documentation. If you want to get REALLY into them, give “How to Master Regular Expressions” a read. It has more information than you could ever possibly hope to need on regex.

Hopefully these few tips and tricks will get you started analyzing your Tweets. There’s lots to be learned analyzing your own social media output, and this guide is just the beginning. I’m excited to learn more about what people want to learn from their Twitter history.

If you have any questions, comments, general rants about this topic,  shoot me a Tweet, or sound off in the comments, and we can chat more about this topic. I’m always down to talk social media analytics with interesting people.

Until next time.

Mr.  David Benson is a social media analyst and coffeephile. He currently lives in New York City and works as an analyst for Mashwork, a social media analytics company.