This post may contain affiliate links. Please read my disclosure for details.In my last post, I discussed a new assignment that I’m using this semester in my Communication research class (all posts on that class). That social media analytics project assignment contains 3 parts. Each part is related but unique, allowing students to pick up a new skill set. In this post, post 2 of 4 in the series I’m writing about this assignment, we’ll discuss part 1 of the assignment. If you haven’t read the assignment overview post, I encourage you to do so before proceeding. There you will see a copy of the assignment discussed in the below post. Part 1 of the assignment asks student teams to analyze the Twitter data provided by their clients by creating pivot tables in Microsoft Excel. If you aren’t familiar with pivot tables, they enable you to filter and visualize spreadsheets. This allows you to focus in on specific data points and quickly extract insights from large data sets. I got the inspiration to create this part of the assignment from a very helpful conversation I had with Professor Stefanie Moore at Kent State University. A big thank you to Stefanie for taking the time to chat with me and for providing me with insights to how she teaches analytics. I am really impressed and inspired by what Professor Moore is doing at Kent State. Preparation: Getting Twitter Data In order to analyze Twitter data using pivot tables in Excel, you need to first download Tweets from Twitter’s analytics (ads) page. If you’ve never done this before, it is really quite easy. The reason we use Twitter is because Twitter enables you to extract a ton of valuable account data from your account in the form of a CSV spreadsheet. But, as an aside, you could analyze just about any data with pivot tables. My students were required to get the Twitter data from a client. Therefore, I created a step-by-step guide that they could provide to the client so that the client could extract the appropriate data and supply it to me. To ensure we had enough data, I instructed the students to ensure that their client was posting at least a few times per week. I asked students to get 6 months of Twitter data if possible. In short, I wanted to ensure that there were at least 50 Tweets from the client in the time period we collected. This number is somewhat arbitrary. And ideally you’d like to have more. But, 50 Tweets is enough to sort and play with. Here are the steps for extracting Twitter data from an account: Step 1: log into your organization’s Twitter account at http://twitter.com. Next, select your account profile picture (as shown below) and select “Analytics.” Step 2: A new window will appear. Click “Tweets” from the menu at the top. Then, select the date range (see below). A menu will open. Please select a date range of at least 3 to 6 months back so that there are enough Tweets for the students to analyze. Important: Click “Update” to change the selected date range. In the below example, I selected Feb 1 through May 1 (3 months). Step 3: Once the dates have been selected, click “export data.” A new window will appear. Click “save file” to save the file to your computer. Email that file (it should be a .CSV file named something starting with: “tweet_activity_metrics…”). You have your data. If someone else is downloading the data – such as a class client – , they will need to email the file to you or your student. Using Pivot Tables to Analyze Twitter Data A few days were set aside in class to work with the pivot tables and learn how to answer the questions students were asked to answer in the project. On day 1, I provided a brief lecture (about 10 minutes). And then I instructed students to begin working with the lab guide I had created. If you’re a longtime reader of this blog, you know I am big on creating lab guides to assist students in learning software. See the lab guide students used to learn to analyze their Twitter data using pivot tables: http://bit.ly/435_pivottableslab While working with the lab guide, students were to have a copy of the assignment that contained the research questions they needed to answer using the pivot tables. Those research questions were:
- Which Twitter posts received the most (Fill in the blank – you need to decide what variables are important engagement data for your client. You’ll need more than 1 variable. And, you’ll want to show more than just the top Tweet for that variable, but the top few)?
- What is the client’s Twitter engagement by month? (again, you choose the appropriate engagement metrics)
- Come up with 1 other RQs for important data points you extract from your pivot table analysis that you believe will be of value to your client.
- How to Use PivotTables for Social Media Analysis [Excel Tips] – Bonfire Marketing
- How to Analyze Your Social Media Activity with Excel – Social Media Examiner
- 3 Easy Ways to Leverage Categorized Pivot Tables for Analysis & Reporting – Acquisio Blog
- Using Excel to summarize your Twitter analytics data better – The Magic Bean Laboratory
- How to Manage Big Data with Pivot Tables – Searchengineland