Back in 2012, I developed an Excel worksheet that would take post-level data exported from Facebook Insights and do a little pivot tabling on it to generate some simple heat maps that would provide a visual way to explore when, for a given page, the optimal times of day and days of the week are for posting.
Facebook being Facebook, both the metrics used for that and the structure of the exports evolved to the point that that spreadsheet no longer works. I’ve updated it, and, with the assistance of Annette Penney, even done a little testing to confirm that it works. The workbook is linked to at the end of this post.
The spreadsheet is intended as a high-level exploration of three things:
- When (weekday and time of day) a page posts
- Which of those time slots appear to generate the highest organic reach for posts
- Which of those time slots appear to generate the highest engagement (engaged users / post reach) for posts
I initially added in some slicers to filter the heatmaps by post type, but then ran into the harsh reminder that Excel for Macs doesn’t support slicers (Booooo!). Maybe I’ll get around to posting that version at some point — leave a comment if you want it.
WHAT IT LOOKS LIKE
The spreadsheet takes a simple export of post-level data from Facebook Insights (the .xls format) and generates three basic charts.
The first chart simply shows the number of posts in each time slot and each day of week — this answers the question, “When have I not even really tried posting?”
In this example, the page does most of their posting between 9:00 and noon, and then again from 3:00 to 6:00 PM (the spreadsheet lets you set the timezone you want to use, as well as what you want to use for time blocks). In my experience — for operational/process reasons as much as for data-driven reasons — brands tend to get into something of a rut as to when they post. The example above actually shows a healthy sprinkling of posts outside of the “dominant” windows, and shows that Thursday didn’t follow the normal pattern (they had a unique promotion during the analysis period that drove them to post earlier than normal on Thursdays).
The next two charts are crude heatmaps of a couple of metrics, but they both use the same grid as above, and they use a pretty simple white-to-green spectrum to show which slots performed best/worst relative to the other slots:
The first of these charts looks at the average organic reach (the number of unique users of Facebook who were exposed to the post not through Facebook advertising) of the updates that were posted in each time slot:
In the example above, while the brand posts most often from 9:00 AM to noon, it appears that earlier posts are actually reaching more users organically. Digging in, the earlier morning posts on Thursdays, at least, were for a unique campaign, so it’s not possible to know if it was the nature of the campaign/content, the timing of the posting, or some combination. But, the results certainly indicate that some experimentation with posting earlier in the day — with “normal” posts — garner the same results.
The next chart shows the average engagement rate of the posts, defined as the number of engaged users divided by the total reach of the post. This is a pretty straightforward measure of the content quality: did the post drive the users who saw it to take some action to engage with the content? Arguably, the propensity for a user to engage is less impacted by the time of day and day of week, but, who knows?
In this example, those earlier-in-the-day Thursday posts again stand out as being more engaging. And, the 9:00 to noon slot, as well as Fridays, appear to be some of the less engaging times for the page to post.
HOW TO USE THIS FOR YOUR OWN PAGE
If you want to try this out for your page(s), simply download the Excel file and follow the instructions embedded in the worksheet. You will need to export post-level Facebook Insights data for your page, which means you will have to have, at a minimum, Analyst-level admin access to the page. Use the settings shown in the screen cap below for that export:
Then, just follow the instructions in the spreadsheet and drop me a note if you run into any issues!
SOME NOTES ON THE SHORTCOMINGS
This approach isn’t perfect, and, if you have ideas for improving it, please leave a comment and I’ll be happy to iterate on the tool. Specifically:
- This approach measures all updates against the other posts for the same page – there is no external benchmarking. This doesn’t bother me, as I’m a proponent of focusing on driving continuous improvement in your performance by starting where you are. Certainly, this analysis should be complemented by performance measurement that tracks the actual values of these metrics over time.
- The overall visualization could be better. It’s not ideal that you need to jump back and forth between three different visualizations to draw conclusions about what days/times are really “good” or “bad”…including factoring in the sample size. I’ve toyed with making more of a weighted score and then doing the same grid, but, then, you’d be looking at a true abstraction of the performance, so I didn’t go that route. Suggestions?
- Facebook Advertising introduces a wrinkle into this whole process. While the “reach” metric looks at organic reach only — attempting to remove the impact of paid media — the engagement rate grid uses total engaged users and total reach. In my experience, posts that have heavy Facebook promotion tend to get less engagement as a percent of total reach. So, it’s important to dig into the numbers a bit.
AND ONE FINAL NOTE
This spreadsheet isn’t “the answer,” and is not intended to be. If anything, it’s Step 1 in an analysis of optimizing the timing of your posts. The goal is almost certainly not to post only once a week in one time slot, but, if you’re going to post 10 times a week, it makes sense to make those 10 times the best 10 times possible. To really figure out when those sweet spots are requires more than just an analysis of historical data. It requires experimentation — posting in slots you haven’t tried or that the historical analysis indicates might be good slots to try. It requires a close collaboration between the analyst and the community manager to apply the requisite structure to that testing. And, sadly, “the answer,” even when you get one, will likely change as Facebook continuously evolves their algorithms for which users see what and when.
Please do weigh in with how you would change this. I’m happy to rev it based on input!