How to Pull and Organize Tweet Chat Data

Tweet chat data is very interesting to pull.  There are websites that will analyze this for you; both free and paid.  When I first stared hosting tweet chats for fun (aka fan growth) I used HashTracking.  At the time I thought this was great because I didn’t know how to track a tweet chat and the stats they give you make your data look awesome!

A few months ago we when we decided that we wanted to host bi-weekly twitter chats, we realized that:

  1. We needed to understand how tweet chat metrics were calculated
  2. We needed a tool to provide us with this data
  3. We needed to be able to calculate the data ourselves and have both numbers match

Since we were hosting health chats we used Symplr to track our chats.  All you do is select a time frame and Symplr will give you your analytics.  While this site is helpful, I wanted to be able to track tweet chats myself.

Twitter Chat Metrics

Here’s what you need to be tracking:

  • Number of participants
  • Attendee role (you want to be able to differentiate moderator tweets from attendee tweets)
  • Audience reach (to get this number you add up all attendees twitter followers)
  • Total Impressions (attendee follower count multiplied by number of tweets)
  • Total tweets

When grabbing tweet chat data, it is important to be mindful of the time.  If you pull in extra hours your data will not be correct.  A tweet chat is typically an hour, so it is not ok to pull in three hours of data.  Your promotional tweets do not count.

Pulling Tweet Chat Data

When I pull tweet chat data, the only data I really have is the transcript.  Luckily, you can get a lot of information out of this!

I’m going to use #fitblog as an example because I am a big fan of their chats (and moderated once too).  In my tweet deck search column I searched for “#fitblog”, selected a few tweets (normally I would grab everything for the hour), copy and pasted (as text) them into excel.  It should look like this:


Yes, this does look messy but I promise it’s easy to organize and we will make it look like this:


While organizing tweet chat data it is important that you don’t grab the actual tweet.  If you do you you will count your attendees twice and your final metrics will be wrong.  Basically we want the first line of every “tweet” so that we can extract the twitter handle. You don’t want to sort by “@” because a user could have @mentioned a user in a tweet and that does not count as an attendee tweet.

While it might take 5-10 extra minutes, I went and categorized every line (ex: time, twitter handle, tweet, conversation). Luckily all the tweets have 4 lines, so I’m just copied and pasted all the way down.

Here’s what I did:

  • I first filtered by twitter handle so we don’t have to focus on other data
  • D2: I added an extra space at the end of the original text so I have a common variable to to look up in a later equation (formula: =A2&” “)
  • E2: Here I searched for “@”  and excel returned where in the text this located (formula: =Search(“@”,A2)
  • F2: Here is where the extra space came in handy; I needed the character count at the end of the text. I’m having excel return the number location of ” “, when it happens after the “@”  (formula: =Find(” “,D2,E2)
  • G2: Here we are subtracting the end of the text from the start of the “@” to extract the twitter handle (formula: =MID(A2,E2,F2-E2)

Now that we have our twitter handles we can calculate our chat metrics!  Here is what your excel spreadsheet will look like:


I added a date and role column so we can pivot this after.

  • D2: I pasted all my twitter handles and removed the duplicates so we can see who attended.  Next I did a count from our data set to see how many tweets they sent out
  • E2: I had to manually grab twitter followers
  • F2: I multiplied #  of tweets by # of followers

Add a sum at the bottom and you have your tweet chat metrics!  If you really want to get into it, you can pivot your data.  If you do this you can calculate:

  • Tweets per participant
  • Impressions and tweets of a participant vs a moderator
  • Tweet return rate (if you are doing WOW)

I am a Social Media Manager/Analyst working in San Francisco. Before moving to San Francisco, I lived in Washington, DC and was working in the health field. For over 4 years, I have developed and implemented marketing strategies and campaigns to drive social growth.

Tagged with: , , , , ,
Posted in Digital Media Metrics & Data
One comment on “How to Pull and Organize Tweet Chat Data
  1. Cara Garvey says:

    Thanks for sharing! This is exactly what I needed to organize Twitter data for a thesis project I am doing. Well done!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

March 2013
    Apr »
%d bloggers like this: