Twitter Hashtag analysis using Excel 2013   Leave a comment


Recently I had written a blog post on my non-SQL Server blog on an event that was being organized world wide to raise awareness to help end violence against women. As with today’s events, the social media was used to garner support and spread the word. There was even a live twitter feed that was running for the #RingTheBell hashtag!

The campaign’s name is Bell Bajao, which in Hindi literally means Ring the Bell started with showing how domestic violence can be prevented by simply ringing the door bell.

The event at Delhi took place at the British council on 8th March, 2013. I used the Tweet Archivist service to start a Tweets archive so that I could do analysis of the tweets received after the event. The archive created can be downloaded as an Excel/CSV file.

This blog post is to show how the Power View option in Excel 2013 can be utilized for performing analysis of Tweets. Once I had the tweets exported to an Excel file, I used the Power View report option to create a new Power View report. See screenshot below.

image

I added a bar chart, a table, a line chart and a pie chart to create a dashboard of sorts in the design area with the following properties:

1. The bar chart shows all the tweets between 28th February, 2103 to 9th March, 2013
2. The line chart shows the tweets from 6th-7th March, 2013 with a 24-hour period
3. The table shows all the users who have tweeted using this hashtag and with a tweet count of over 100.
4. The pie chart shows the percentage of tweets by each user for the period being analyzer with a tweet count over 250.

As it is clear from the above, information present in each of the four components have a different set of filters applied to them.

image
What makes it interesting is that the above report has interactivity built into it. So if I click on any one of the charts or table, the rest of the table/charts also change to reflect the data for the selection made. This is evident from the video at the bottom of this post.

I wanted to take this a bit further, so I created a scatter graph with the Date column as the Play axis. The graph below shows me all twitter handles that have over 150 tweets over a period of three days! This allows me to see how the Twitter accounts were sharing updates/tweets prior to the event and during the event.

image

The video below shows the interactivity features of the Power View report in Excel. What you can do with such rich visualizations are endless. Just by looking at certain visual representations, it is easy to draw interesting conclusions. For example, the timeline shows me that the Bell_Bajao and PixelProject twitter handles were extremely active on the day of the event. There were others who were more silent in the days running upto the event and then started live tweeting/re-tweeting throughout the event. The TOP 5 contributors on the day of the event had a major pie of the tweets that were shared on 7th March. Within a very short time, I was able to decipher trends which would have taken me a while to dig out using traditional analysis methods!

*Note that the above times are based on the US timezone (Pacific time), which is why the 8th March event activity shows up as 7th March on the timeline.

I do know of a lot of individuals who use Twitter raw data for trend analysis. So here is a quick way to get that done with Power View and Excel 2013!

About these ads

It is always good to hear from you! :)

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,327 other followers

%d bloggers like this: