Upcoming LunaMetrics Seminars
New York City, Aug 4-8 San Francisco, Aug 11-15 Los Angeles - Anaheim, Sep 8-12 Washington DC, Sep 22-26

TOOL: Facebook Post Optimizer

Lately I’ve been on a bit of a tear against sites and tools that pretent to telling you the optimal time for posting content to your social networks. There’s a lot of things they do that I question, and I cover that in depth in this article here on Marketing Pilgrim.

This post is to share a tool I’ve developed using a Google Docs’ spreadsheet that lets you simply copy & paste your Facebook Insights data into it and instantly see when you’re optimal posting time is for both organic reach (not paid) and engagement (story) generation. Better yet, both of these figures are normalized to your current follower count. This means that the data from two weeks ago, before you added 200 followers in that campaign you ran, is just as valid as the data from today – they’re both based on the number of followers at the time of posting.

How does it work?

Step 1: Download your Insights Data

Download your Page-level and Post-level data in .CSV format from Facebook’s Insights Manager. Download your page level data one day earlier than your post level data. This ensures that the sheet can find corresponding likes for each post you’ve made.

Selecting-Insights-Dataset

Step 2: Enter your dataset into the tool

Copy and paste these spreadsheets into the respective PostData and PageData sheets in the spreadsheet. Because of the 50,000 character copy and paste limit of Google Doc’s, only copy and paste up to column ‘H’ of the Page-level data set.

PostData-and-Pagedata-Sheets

If you haven’t targeted a specific country for one of your posts during this time, you’ll need to add an extra column to your Page-level data sheet. Just add an extra column in between column C, ‘Type’ and column D, ‘Posted’. You have to do this before you paste it into the spreadsheet, otherwise it might get finicky. Pages who have posted a country-specific post will already have this column, labelled ‘Countries’, and won’t have to do anything.

Column-to-be-added

Step 3: Check your Insights

Check out the Reach or Stories sheet. You should see graphs for each day of the week from 0-24 hours with your posts graphed at their respective times. This will give you a top-level overview of when your posts are doing well. You can cross reference particular points to find deeper insights.

 

Organic-Reach-Charts

Other Notes

Careful with you data
I would warn against using data from before July 3rd, 2012. That’s when Facebook announced it would now be including mobile reach in it’s reach. It also announced a change in the fundamental way that it calculated reach. This means that all of your previous data won’t include mobile views and could poison your insights. I did a write-up on this, coincidentally also for Marketing Pilgrim, that you can check out here.

Time zone considerations
It’s calibrated to spit you that data in EST, because Facebook’s downloadable spreadsheets are in PST by default. Working on a different time? Just replace .125 in the ‘D’ column of the ‘Magic’ sheet (it’s at the end of the formula) with .0416 for each hour plus or minus EST that you’re working in.

Time-Adjustment-Variable

Woo! Just post at all the same times and you’ll get more reach/engagement!

Not quite. This is only a jumping off point. For starters, the data only shows your effectiveness based on when you’ve already published. If you do well at 3:00 PM on Wednesdays doesn’t mean you might not do EQUALLY well at 10:00 PM. It’s only as smart as the data you feed into it, so make sure you’re testing some different posting strategies if you haven’t already.

Correlation is not causation. Look at your results, make some educated hypotheses, and test, baby, test. This is meant to be a starting point, not an end-all.

Also don’t forget that Facebook uses it’s EdgeRank algorithm to determine who sees what. Although the Ticker can provide you a powerful place for real-time feedback and interaction, most consumption happens in the feed. This tool will help you optimize for the Time Decay portion of the algorithm, but there’s still Weight and Affinity scores that affect your reach.

Want to test it out yourself? Awesome. Go ahead and make yourself a copy here.

Having an issue? Write me in the comments section or tweet @LunaMetrics and I’ll help you get it sorted.

http://www.lunametrics.com/blog/2012/08/07/facebook-posts-optimizer/

13 Responses to “TOOL: Facebook Post Optimizer”

Mindaugas says:

It doesn’t seem to work for me. As your Doc is in .xls format, I can’t properly copy .csv into it without ruining the table itself. If i copy .xls data, everything seems neat but there are no results in Reach and Stories sheets.

Also I have another 2 questions:
1) Download your page level data one day earlier than your post level data – does that mean the last or the first date of the range?
2) Just replace .125 in the ‘D’ column of the ‘Magic’ sheet (it’s at the end of the formula) with .0416 for each hour plus or minus EST that you’re working in. – if i work on +2:00 time, so the value should be .0416*7=0,2912?

Thank you very much!

Bjoern Sjut says:

Dan,

that’s a really interesting approach!

As you point out it should follow a testing strategy of posting systematically at different times. My suggestion to get to such a testing strategy would be to take daytime traffic or conversions broken down by hours from your normal business site to identify “hot hours” and see if these results can be replicated on Facebook.

Hey Bjoern,

Interesting suggestion! As they say, always be testing :)

Dan

Hey Mindaugus,

Not sure what you mean by the ‘doc in .xls’ format thing – are you downloading the Google doc to your computer? You should be making a copy of it to your Drive and working with it fully online. The CSV data comes from Facebook’s Insights tools – you can just copy that unadulterated into the appropriate page.

To your other two questions:

1.) Yes, one day earlier than the earliest date. For instance, if you wanted to use your post level data from July 3rd to July 25th, you would download your page level data from July 2nd to July 25th. This is just to make sure there aren’t any issues correlating the date with the number of likes your page has on that day.

2.) That should do it, yes – you can check by going to the ‘Magic’ page and looking at Column D – it should show the same times as when you published to Facebook.

Let me know if you have any other questions!

Cheers,
Dan

Mindaugas says:

Hi Dan,
Thanks for your prompt reply. I am still having trouble copying page data into appropriate sheet. I’ve made a screenshot depicting it http://imgur.com/bU0LO. The thing is there is way more than 50k of symbols in a .csv with page data which I’ve opened with text editor, because Google Docs won’t make that large .csv into a Google Document. So I don’t know what else to do. Even if I copy a portion that is less than 50k, I still get blank diagrams in Reach and Stories sheets.
Hope you can help.
Thanks in advance!

Amanda says:

It really depends on who your audience is. For my current client, whose target is suburban moms between 18 and 40, daytime posts don’t work–they’re on duty until at least 8:30 or 9 pm. But Timely and the rest don’t seem to know that. Roost/Vertical Response’s “automatic” scheduling optimizer does nothing but post at the next 15 minute interval.

At this point, I have had better luck just using common sense to determine when my posts will be viewed, and then observing the actual views and engagements using their easy insights data right below each post. Maybe it’s because I plan to handle no more than 5 clients at a time, and they’re all really small. But I’m running on common sense, it’s free, and it works for me.

Hey Mindaugas,

Well, you’ll want to open those .CSV files using Excel or another spreadsheet management tool to ensure that they are formatted properly before copy-pasting them into the Google Doc. They should be spreadsheets, not lists :)

Best,
Dan

zsolt says:

Dear Dan!

I think there is a little mistake, because you wrote:

” you’ll need to add an extra column to your Page-level data sheet.”

But the picture below shows the PostData table’s snippet. Am I wrong?

The right sentence will be this:

“add an extra column to your POST-level data sheet”

Isn’t?

And there are not only countries, there is languages too. It isn’t necessary to put in the table another empty column after C?

My Post data looked little different.
There was not only “Post Id” and “Post Message” column, but in between there was a “Post Id” column too.

And in my Post data table there wasn’t such a column:

“Lifetime The number of times people have given negative feedback to your post, by type. (Total Count) – unlike_page_clicks”

Dan, can you help me to solve my problems?

Best regards!

Zsolt
from hungary

Hey Zsolt,

As long as your post times end up in the D column and “Lifetime The number of people who saw your Page post in News Feed or ticker, or on your Page’s Wall. (Unique Users)” is in column F, it will work appropriately. Unfortunately, Facebook has revised some of their metrics, so their spreadsheets have lost something in the way of consistency.

Best,
Dan

So I am confused just getting my time right and I am really bad with numbers. I work in EST, so can someone please tell me what value I need to put into my spreadsheet to see when I get my most views? Est is three hours behind PST so I need to put in….?

Hey James,

I think I left it set to EST. I think. If not, you simply figure out the numeric value for an hour in Excel i.e. create a 1:00:00 cell and then convert it to a number. For some reason I think it’s .4166666, but I would double check me just to be safe.

Best,
Dan

Aileen says:

Hello Dan,

I’ve been trying this but i don’t see the graphs. Based on your reply above: “As long as your post times end up in the D column and “Lifetime The number of people who saw your Page post in News Feed or ticker, or on your Page’s Wall. (Unique Users)” is in column F, it will work appropriately.” — the figures are on the right columns but nothing happens. Please help…

thank you.

Hey Aileen,

Unfortunately, when you fiddle with the columns in this sheet, it gets cranky. Try this updated version:

http://fbppc.com/advertising/happy-early-4th-free-tool-to-help-time-your-facebook-posts/

Best,
Dan