Tag Archives: analytics

An Assignment and Spreadsheet for Teaching Students to Track Social Media Metrics in my Social Media Class (Post 1 of 2)

In the social media education community, there has been a lot of discussion about teaching social media metrics and analytics to students. This has been a challenge and frustration for myself and many others. Access to industry tools is cost prohibitive for many universities, making it difficult for us as educators to prepare our students for this aspect of their careers.

I’ve worked hard over the last few years to try and enhance how I’m teaching these concepts. And I’m not where I want to be. But I know there are many fellow educators also on this journey with me. So, I’d like to share how I teach students to track social media metrics as part of a semester long assignment and a few modifications I have recently made to enhance that aspect of my teaching.

I’ve split this topic into two blog posts for length purposes. In both of these posts, we’ll focus on my social media class (2016 syllabus; and all articles about this class). In this post, we’ll talk about the spreadsheet for tracking metrics. In the follow up post, we’ll discuss Microsoft Social Engagement and how I integrate it into the metrics assignment portion of the class.

Update: The follow up post on Microsoft Social Engagement is now available.

My aim in my social media class is to introduce metrics to students both in lecture & discuss (which I’ve been doing for some years) as well as by use of software. Then, when students get into the Communication Research class (2015 syllabus; articles about this class), they will get more in-depth learning about analytics. I’ve increased/improved my focus on this area in that class for next spring. And my long term hope is to really build that part of the class out. During the upcoming spring semester, I will write a blog post about what we will be doing with analytics. And, at that time, I will share all of my assignments and handouts.

Okay, back to my social media class. In past years we’ve used Twitter Analytics – which has been the best, free tool. Unfortunately, other platforms have been limited in their analytics. We’ve used a slew of free tools that have been here today, gone tomorrow.

This year, we still faced the challenges of relying on Twitter Analytics and whatever free tools we could find. But I also added a brief introduction to Microsoft Social Engagement (which will be discussed in the next post in this series).

But first, let’s discuss how I teach students to track performance metrics in my social media class.

In my social media class, students are divided into teams. Each team is in charge of running a social media platform for our department’s social media. In the past, I had my students use a spreadsheet developed by Jeremy Floyd to track metrics. At the time, I modified the spreadsheet for our purposes. At the start of this semester I modified the spreadsheet further simplify it and to add a section on Microsoft Social Engagementƒ (again, which I will discuss in the next blog post).

Here is a copy of the spreadsheet as it was distributed in my Fall 2016 class which you can use in following along with the below post. You can also download a copy for yourself to modify and use as you prefer. Again, credit goes to Jeremy Floyd for the original incarnation of this spreadsheet.

In lecture, I teach students about the activity, engagement and performance metrics discussed in Kim’s book, Social Media Campaigns: Strategies for Public Relations and Marketing. I also emphasize the importance of choosing metrics that are tied to goals. (You’ll see a tab in the spreadsheet discussed below, where students are to determine their objectives and what metrics would be important to those objectives).

Student teams begin with the planning tab, then they establish their metrics goals to use the spreadsheet to establish benchmarks and KPIs for their platform and track metrics over the semester. They then move over to reporting tab to track weekly metrics.

Tip. You can see tips by mousing over the small triangles in the upper right corner of some cells, as shown below. I’ve created these to help students when working on their spreadsheets in groups.

In the image below, you can see the ‘reporting’ tab of this spreadsheet. We start tracking in week 9 of the semester, but you can modify this as you like. After each week, you’ll see the percentage change. Of course, you can also modify what you are tracking. I throw in a number of potential metrics to track for different platforms. But, students can delete all the rows they don’t need and modify the individual metrics for that platform as needed. The metrics identified in the spreadsheet are just a guide.

I’ve also divided the spreadsheet up into different platforms so each team can pick their platform (as shown in image below) for tracking the success of their posts. The idea here, is that by tracking these posts across time, students can begin to analyze these metrics for trends (though, I don’t have any ways to quickly analyze and visualize this data at this time). This could help them learn when the best time to post is. However, you could also add variables about the post that can help them identify which is the type of content that is most successful. Other spreadsheets I’ve seen track variables such as whether an image was used, what hashtags are used, if links are used, etc. So, again, you can modify the optimization section as you see fit. I discuss other variables to track, but focus on the ones in this spreadsheet so as to not overwhelm students. I’ve found if I ask students to track too many things, nothing gets tracked as they get overwhelmed. So choose what you want them to track, and stick with it.

I’ve relied on Kim’s metrics categories for metrics students can track. Also, please know the metrics I have identified isn’t perfect and modification of what I’ve identified may be needed – some of my initial metrics may not work, or changes have occurred.

Integrating The Metrics Into the Semester-Long Assignment

As noted above,  across the entire semester of my social media class, students are strategizing, building and executing social media for my class. As a part of that, they present their content to the class for approval at intervals throughout the semester.  In the latter half of the semester, the students present their current metrics to the class alongside the content they are proposing for the next content time period. At the end of the semester, we discuss their metrics, whether they met their KPIs and during what week they did, and what they learned from them.

While the above enables us to track interaction with our social content and extract some insights, it doesn’t account for listening to competitors, following trends, etc. It also doesn’t take deeper analytics and the extraction of insights into consideration. We don’t do anything to plot or discern specific insights – I am saving that for the Communication Research class this spring. Said another way, the assignment and use of this spreadsheet in my social media class, as I executed it in Fall 2016, was really more about tracking metrics, following change and teaching students  to see the impact (outcome) of their efforts on social media, while connecting those back to objectives and KPIs.

In the next blog post, I go into the “social listening” tab of the spreadsheet and discuss how students got a little hands on use with Microsoft Social Engagement in my social media class during fall 2016.

In the meantime, if you have any thoughts or suggestions or resources you’d like to share about teaching metrics to students, please share them with me and the readers via a comment in the post or Tweet me. This is an important journey for all of us as we work to enhance hands-on metrics learning for our students.

I hope you found this post helpful. If you did, please share it. It helps a lot.


#Hokies Tweets Network Visualization: How I extracted Tweets via TAGS 6 and visualized them in Gephi

Click to see larger or download.
Click to see larger or download.

A professional development goal of mine is to learn a lot more about social network analysis and visualization of social media data. This area has grown increasingly valuable and important in our field.  And I believe we all need to have at least a base knowledge of social data and how to play with it.

With my wife traveling for work and rainy weather here in West Virginia, this weekend presented a great opportunity to finally get my feat wet (no pun intended).

As you may know, my beloved Virginia Tech Hokies haven’t been playing so well this college football season.  So I decided to use Saturday’s game as an opportunity to play with Twitter data and Gephi, an open source data visualization program.

I’ll explain what I did below to make the above visualization in case you’d like to try this for yourself. This is a simple approach and I think you’ll find you can do it if I can learn it in a weekend! I started Saturday morning with zero knowledge of graph theory, social network analysis, how to use Gephi, and how to pull down Tweets.

I’m writing this up because I found several tutorials online. But, none of them quite came together to show me how to do all the parts in one tutorial. A major reason is that the Twitter API has changed since many tutorials available online were built. So, the ways offered for getting the Twitter data on those tutorials no longer works.  As such, getting Twitter data is a challenge if you don’t know a little programming with Python, etc (Needless to say, I don’t).

Fortunately, each of the tools together below made this first experiment in Twitter data visualization possible.

Here’s how I did it:

1) I used the TAGS v.6 Twitter Archiving Tool to gather Tweets with the hashtag #hokies. This is an amazing, free tool – thank you so much to Martin Hawksey for this! You can learn to use the TAGS archiver fairly easily via Google Docs. The only real slow down is that you have to get a Twitter API key via your Twitter account.

I ended up gathering 1583 Tweets between 3:19am – after midnight before the game – and the majority of the way through the game at 2:43. So, whatever Tweets going back I could pull when I extracted the data at 2:43; not a great picture of the #Hokies conversation, but it worked for this exercise.

2) I used @DFeelon’s spreadsheet converter to convert the TAGS spreadsheet to a file I could put into GEPHI to do the visualization. Thanks Deen!

His converter pulls only the first Twitter account that is mentioned in the Tweet or in a RT – so any additional persons mentioned in a Tweet were not counted. You can learn more about it here on Deen’s blog. It is easy to use. In short, I copied my Tweeter and Tweet text into his spreadsheet, and voila! This created my edge file in CSV for GEPHI with 2 columns (vertices, or nodes) – the first column being the person who sent the Tweet and the second column being the person to whom the Tweet was directed.

3) I noticed that some mentions of Twitter account handles were all lowercase whereas others were not. This had created duplicate nodes. That is, in some instances, one Twitter account had been split into two: an all lowercase version and the original. So, I simply made all text lowercase to address this problem. I used Google Refine to clean my CSV file because I want to learn to use this program. But, you could change the case in Excel or any spreadsheet software.

4) I then loaded the cleaned CSV file into Gephi (download it here) so I could do the visualization.

5) I spent a lot of time on Saturday reading about visualization and getting a basic knowledge of graph theory and how to use Gephi. While I’ve still got a lot to learn, I decided to follow a tutorial for my first “go round.” It seemed like a great opportunity to put together concepts and tools in Gephi that I’d learned in a guided environment. So, I followed the instructions on the latter half of this YouTube video for how to visualize the data and export it into the file you see with this post. The tutorial is by Michael Bauer via the International Journalism Festival. Of note, the first half shows you how to extract data using Twitter’s old API and that process no longer works. So you can take your CSV file gained through the process above, import it into Gephi, and pick up with the tutorial at 1:05:46.

So, that’s it!

A few quick things about this visualization:

As indicated by the size of the Twitter account name, we can see that Virginia Tech sports beat writer Andy Bitter for the Roanoke Times had the largest number of Tweets directed at him regarding the game (that is, his node – his Twitter account – had the most degrees. The degrees are the number of edges, or connections one node has to another). This makes sense. I’ve followed the #Hokies conversation on Twitter for years and Andy has been a constant presence and leader in providing news and analysis of Tech.

The communities are indicated in colors. I used the modularity script in Gephi to identify these, as is shown in the above-noted YouTube video. In short, you can use the color coding to make a basic clustering of who is talking to who.

In Closing:

While I’ve got a ton to learn, I’m thrilled with the progress I’ve made in just over a weekend from not knowing the first thing about graph theory, basic spreadsheet formatting for nodes and edges, or how to visualize a social network, to building my first visualization. And, while my goal is not to become a data scientist, I am excited to continue to learn and grow a base knowledge in this area. I know I am just scraping the tip of the iceberg.

I’d love to hear your thoughts and tips on how I can improve my knowledge and skills! Also, please feel free to share your tips, tutorials, and experiences with social data.


Note: Thanks to Nathan Carpenter at the ISU SMACC for helping me get started with data gathering and visualization by generously sharing his experiences and tools!