I’m excited about analytics. That’s a phrase I infrequently hear from people – until you start realizing some of the cool information you can glean from the data. Applying science into social dynamics isn’t perfect, but it does lend support to your gut feels about words to use, types of posts to send, and other key factors – such as time of day or day of week. A quick and easy way to analyze your tweets is to manually categorize them and then apply a little analysis to determine the chance a particular tweet will be favorited or retweeted. Today, we’re going to explore how to do that using Excel and a little manual labor.
First, let’s create an Excel spreadsheet to help us analyze our data. There will be six columns of data in our spreadsheet:
- Date
- Tweet
- Type of Post
- Favorited
- Retweets
- Total
Like this:
[table]
,a,b,c,d,e,f
1,Date,Tweet,Type of Post,Favorited,Retweets,Total
2,,,,,,
[/table]
The type of post is a subjective categorization of your tweets. For example, you might categorize them like this:
- Quotes
- Photo
- News Article
- Blog Post
- Conversion
- Engagement
You’ll have to use your best judgment to categorize posts. I use Excel’s data validation featured to create a select list for the Type of Post column. Create your list somewhere else in the spreadsheet, for example,
[table]
,a,b,c,d,e,f,g,h,i
1,Date,Tweet,Type of Post,Favorited,Retweets,Total,,,
2,,,,,,,,,Quotes
3,,,,,,,,,Photo
4,,,,,,,,,News Article
5,,,,,,,,,Blog Post
6,,,,,,,,,Conversion
7,,,,,,,,,Engagement
[/table]
In cell c2, click on the Data ribbon and select Validate and then Data Validation. Under the Allow menu, select List. Select the cells your list is in (in our example, i2 through i7. Click OK.
You should now see a dropdown list to select a value in c2. You can fill this cell down to copy your validation throughout column c.
Now comes the manual part of our effort. (Side note: if you know of an automated way to achieve this part, I’d forever be grateful, I was unable to find a tool to extract this data for me – just respond in the comments below.) Go to Twitter and open your profile. Navigate to your tweets. For each tweet in the month you’re analyzing, enter in the date, copy and paste in the tweet into column b (Tweet).
Click Expand on the Tweet. You’ll see the number of Favorites and Retweets. Enter this information into columns d & e. Column f, Total, is a sum of d & e (=sum(d2:e2)). For example:
[table]
,a,b,c,d,e,f
1,Date,Tweet,Type of Post,Favorited,Retweets,Total
2,4/1,Be the change you want to see – Ghandi,Quotes,5,8,13
3,4/1,US nears another debt ceiling – bit.ly/jhfhgf,News Article,0,1,1
4,4/1,@jeremyreis you are awesome,Engagement,1,0,1
5,4/2,What do you have planned for this week?,Engagement,0,4,4
6,4/2,Be the dollars you can believe in – Uncle Scrooge,Quotes,8,7,15
[/table]
When your table is filled out, select it, and then on the Data tab select PivotTable and create a new manual PivotTable.
On the PivotTable, add Type of Post to the Row Labels section. Add Count of Type Post, Count of Favorited, and Count of Retweets to the Values section.
Add a Chance of Favorite and Chance of Retweet column. In f5, the formula is =C5/B5 and the formula for g5 is =D5/B5. You can fill these values down to complete the chart. The total cells, f11 and g11 are averages. The formula is =AVERAGE(F5:F10) and =AVERAGE(G5:G10) in cells f11 and g11 respectively.
In this example, a quote has a 58% chance of being favorited and an 83% chance of being retweeted. This is a small subset of data, so I wouldn’t state this with any statistical significane, but it does provide some support for statements you make about types of posts.
You can also use this same process for accounts with multiple authors. You can assign the appropriate author to their Tweet and then determine the authors who produce the most conversation on the account.
Though social media isn’t entirely predictable, with enough data, you can learn to make good assumptions about how certain posts will do with your audience.
