Social Media

How to Analyze & Optimize Your Facebook Posts Using Pivot Tables

0 Comments 23 December 2012

Since Facebook Pages were launched in 2007, brands have made significant headway in figuring out how to use social media and integrating it within their overall strategies and tactics. Most brands now have specific purposes for their social media programs (e.g. drive sales, foster loyalty, activate advocates, etc).

More importantly, there is increased interest in proving the value and impact of social media. The query “facebook best practices” returns 370,000,000 results on Google and “value of a Facebook fan” returns 161,000,000. In short, social media is often a requirement to compete in the digital space and brands want to make sure they are measuring up.

Yet one looming barrier remains: gathering and manipulating social media data can be cumbersome, time-consuming, and costly if you use a social media analytics vendor. But it doesn’t need to be that way.

The aim of this post is to show how you can discover key learnings about the following elements and apply them to your own Facebook marketing/editorial calendars.

  • Post type (link vs. status update vs. photo)
  • Post length
  • Calls to action
  • Time of the day
  • Day of the week

For example, you may publish posts that are meant to engage your fans with a question or fill in the blank. This blog post gives you the tools to understand when your fans are most likely to engage.

Alternatively, you may be an online retailer and publish posts with links to your deals or weekly specials. This blog post gives you the tools to understand when your fans are most likely to be reached.

Below are the steps for analyzing the aforementioned post elements.

1. Download & Clean Up

First, access your Page’s Facebook Insights and download the Page Level data for the time period in which you are interested. The more data you are able to download, the better.

Facebook Insights Interface

facebook insights interface post level


Facebook Insights Post Level Export

Excel screenshot of facebook insights post level export

Time & Day

Within the Excel export, add a column titled “Day”. Next, fill in the day that corresponds to each date. The formula for this is: =TEXT([cell that contains the date],”dddd”). Here I am using =TEXT(H3,”dddd”). Drag this down for each row in the spreadsheet.

Separate date and time into different columns (column header is “Posted”):

  • Find and replace “ PM” with “PM” – and repeat for AM.
  • Select the column titled “Posted”
  • Pull up the Text to Columns tool, select space as the delimiter and choose a destination column that does not contain data (such as Column E)
  • Ensure the columns are split correctly

Label the columns accordingly. Add an additional column titled “Rounded Time” and fill out based on the time closest to the hour mark.

Post Length

Add another column to the right of the “Post Message” column and title it “Character Count.” Insert the length formula, =LEN(), a into the first cell of this column and select the corresponding cell in the “Post Message” column. Drag the formula down. Add one more column to house the post length. To start, calculate if the post has greater than or less than 150 characters. Here we calculate this using the =IF(COUNTIF()) formula. Past studies have shown that fewer than 150 characters is the ideal length, so this column will help us identify the benchmark and performance based on it.

Post Objective

If each post has a specific objective (e.g. engage or drive sales, etc.), add another column titled “Objective.”

The first several columns of your data sheet should now look like this:

Example of facebook page level excel spreadsheet

2. Build the Pivot Table

Let’s start by analyzing day of the week. Create a new tab. Open the Pivot Table builder and add in the Row Label (here we are using day of the week) and Values (the metrics that you want to analyze). In this case, we are using the following metrics:

  • Total Impressions
  • Post stories (likes, comments, shares, etc.)
  • Post consumptions (photo views, link clicks, etc. plus stories)
  • Negative feedback (post hides, report as spam, etc.)

Be sure that the Values listed above are set to Sum; by default, these are set to Count. However, note that Count can be used to tell you the number of posts by pulling in any column name (for example Count of Lifetime Impressions).

The Pivot Table will look like this:

Excel Pivot Table with Facebook Post Data

These steps can be repeated for any element in which you are interested: day, time of day, objective, post length range, etc. We recommend creating a new tab for each element, which will make it easier to organize and find data later on.

3. Calculate metrics

Now that the data is aggregated, we can proceed with calculating metrics and KPIs for each day of the week. In this case I would like to calculate the following metrics:

  • Average engagement per post (lifetime stories/lifetime impressions). In other words, the number of interactions for each time the post was seen. High engagement rates indicate strong engagement from your fans – the higher the better.
  • Average consumption rate (lifetime consumptions/lifetime impressions). This tells us the total number of clicks on your post, including the number of times a photo was viewed or a link was clicked for each time the post was seen. Again, the higher the better.
  • Average negative feedback rate (lifetime negative feedback/lifetime impressions).

    While likes and comments are generally indicative of good content, negative feedback shows us how many times the post was hidden and/or reported as spam out of the number of times it was seen. If you are posting repetitive content or the tone of your posts is not consistent with your brand, you will probably experience a high negative feedback. Generally, the higher your engagement rate, the lower your negative feedback rate.

  • Average impressions per post (Sum of Lifetime Impressions/Count of Lifetime Impressions) tells us how many times posts were seen.
  • Average reach per post (Sum of Lifetime Reach/Count of Lifetime Impressions) tells us the average unique number of people reached per post.

The image below shows the top performing days for the aforementioned metrics.

Excel Pivot table with calculated Facebook metrics

4. Draw out key findings

Based on the data highlighted above in green, our most engaging posts tend to happen on Wednesdays and Saturdays, while Thursdays and Fridays see the least engaging posts. Depending on how much data you have and the variation of content and times/days you post, this could be due to two main reasons:
1. You post the most engaging types of content (questions, etc.) on these days, either coincidentally or on purpose


2. Your fans are more likely to engage on these days

Similarly, if you publish promoted (paid) posts on particular days, those days will have higher reach and impressions. If you post photos or links more often on particular days, those days will likely have a higher consumption rate.

Start out with data observations and after taking all information into account, pull out key findings. For example, the community manager on my brand tries to post during a variety of days and times. She also evenly publishes photos, questions, etc. So, I can safely assume that the data is not skewed.

When doing the analysis for post type (photo, status update, link, etc.) I discovered that Photo posts have 3x the reach of Question/Poll posts. But, Question/Poll posts see 2x the consumption rate of Photo posts. So, if I want to maximize reach to share info about deals, Photo posts are my best bet. But if I want to engage my fans, a Question/Poll post would be more appropriate.

Facebook Insights and social media analytics in general can be confusing. And you don’t need to blindly follow best practices to be successful in social media. Best practices may give you a good place to start, but using Pivot Tables to isolate attributes of your best performing posts can quickly pay off.

At Proximity, we provide analysis and recommendations for day/time, post length, content, and several other ongoing optimization techniques within a reporting cadence that makes the most sense for each client. As a result, the posts are well-optimized to deliver on our clients’ social objectives and (ultimately) their business goals.

Share your view

Post a comment

About Search Work

Proximity Search is a client service capability within the BBDO/Proximity Worldwide Network. We serve as the Global Center of Excellence for Search out of the Barefoot Proximity office in Cincinnati, Ohio and collaborate with our colleagues in over 62 offices around the world to meet the organic, paid, social, local and mobile search needs of our clients. We also offer comprehensive measurement and analytics capabilities to identify the effectiveness and ROI of all digital marketing programs.


© 2015 Proximity Search Work.

Daily Edition Theme by WooThemes - Premium WordPress Themes