I had lunch with a girlfriend recently and she told me about a conversation she had with her boss about Facebook. Her boss wanted shorter posts and thought it would drive more engagement. My friend on the other hand, was worried that cutting the word count by more than half would have a negative impact on engagement. She asked me what I would do if I were in her position and that conversation was the inspiration for today’s blog post!
My answer was twofold…
First, Facebook posts should be interesting and compelling enough to inspire your fans to like, comment or share. If you haven’t read Jonah Berger’s book, Contagious: Why Things Catch On, buy it now.
Second, take a look at your Facebook Page Insights to analyze how short posts perform versus longer posts.
WARNING: This post is going to go into detail on how to do this in Excel. Hope you’re ready! Let’s get started…
1. Go to your Facebook Page Insights and click on “Export Data.”
2. When the Export Insights Data modal window appears, select at least a month’s worth of data.
3. Choose “Post Level Data,” select “Excel” as your file format and download your data.
4. Open the Excel file and move the tab “Lifetime Post Stories by Action Type” into a new workbook.
5. Delete columns A, F, G, H and K. You should be left with the following columns: Permalink, Post Message, Type, Comment, Like and Share.
6. Next, insert a new column between the “Type” and “Comment”—name that column as “Character Count.” Now it’s time to count how many characters there are in each of your Facebook posts. Don’t worry, we’ll use the formula “=len(C2)”. C2 represents the cell you want to be counted.
7. Copy the formula above and paste it in the remaining rows to get a full count. Delete the “Type” column to clear up some space or leave it in there if you want to see what type of content is driving engagement.
8. Next is the fun part (at least in my opinion – I’m a data nerd). Select all of your data and create a filter. Sort the “Character Count” column in ascending order.
9. We’re now going to break up the data into three categories: Under 100, 101 to 150, 151 and up. You can choose different categories if you want. Create a new column between “Post Message” and “Character Count” and name it “Category.” Go through each post and identify which category each post falls under.
10. The next step is calculating the average Comments, Like and Shares of each category. Insert a line after each category and use the formula “=average(select range).”
10. You can now create a table illustrating an average of engagement you get on each length of post. Note: To make sure your averages are accurate, make sure you are comparing the same amount of posts (i.e. 15 posts under 100 character, 15 posts that are 101 – 150 characters and so on).
11. Once your table is all set, create a Clustered Column Chart and guess what—you’re all done now!
Note: If you don’t want to make the chart, you can just sort the data by Comments, Likes and Shares in descending order. You can eyeball how posts are doing in terms of engagement.
I know this exercise is really long and can be hard for those that aren’t Excel friendly but I’m here if you need help. Post your questions or leave feedback in the comment section below.
I’ve been meaning to start going through my Facebook data (and other SM sites) I think this post has spurred me on. Thanks for the quick overview of how you do it in excel. It’s a good thing I was a corporate accountant in my previous life, because I’ve excel skills that would rock your world 😉
I wouldn’t know what I would do without Excel! You should have seen me in the office today when I got my vlookup up and working. And I totally believe that you’ve got some Excel shortcuts and macros that’ll leave me in the dust. I was fortunate to sit next to the Accounting department at my first full-time job and they showed me the ropes a bit.
hey Stephanie (that was fun ’cause it’s the name of your site) I know I’m not quite ready for this yet, read: not enough engagement yet to matter and I also worry ’cause I often feel overwhelmed with analytics and such but I did find it interesting to read about length of posts and then realize that there are some peeps I follow on FB that post their full posts there. The ones I really love I read it on FB and then again when I get their blog post in my email. Maybe that seems funny but I love what they have to say so much that I will read it twice. What I love about FB is that it’s easier to share it there. I bookmarked this post for that time in my life when I’m ready to take on the analytics side…hoping I get there, someday.
Wow. I had no idea you could get all this data from facebook. Thanks so much for showing us how in such a detailed way.