Connect Facebook Post Insights API to Google Sheets

Connecting to the Facebook Post Insights API to Google Sheets via SyncWith

In this reference we’ll look at how to setup a Facebook Post Insights API connection.
We’ll use SyncWith to get your Facebook Posts into Google Sheets. Please see our Facebook Ads Guide to see a full listing of all our facebook connections, templates, tutorial and specific facebook ads advertising use cases.
In SyncWith if you search for facebook you’ll get a few results, this guide deals with moving Facebook Post data into Google Sheets. You can check out our guides for Facebook Page Insights, Instagram Post Insights and Facebook Post Insights.
As a reference this connector utilizes the API graph, specifically the published_posts endpoint https://graph.facebook.com/v13.0/{pageId}/published_posts, if you’re curious about calling the api directly we have published a comprehensive Facebook Graph API Guide.
notion image

Don’t Have SyncWith?

  • SyncWith is a Google Sheets Add On
  • SyncWith allows you to move data from any API into Google Sheets
  • Learn more about why Marketers love SyncWith
  • Check out our Google Workspace Listing and see why we’re the highest rated API add on with over 100,000 installs. Install today and get your data into Sheets in minutes.

Choose a Reporting Time Frame

You can choose from a wide range of default reporting time frames as well as dynamic ones. Some marketers and agencies might report on the last 21 weeks of data. You may or may not want to see the current day in the reporting period.
All are possible with the date range picker. Popular options include:
  • Last 30 days
  • Last 90 days
  • Last Month, Last 3 Months
  • Month to Date
You may create multiple connections with different date ranges, for instance if you’re building a dashboard you might want to know your total ad spend in the last month, 3 months and year. You can create 3 queries to pull each of these numbers. It can be quite useful to pull a year or more of posts to see broader engagement and reach trends.
notion image

Choosing Your Dimensions

Dimensions are important if you want to pivot your data to see how different groups of users are interacting with your pages
  • Which videos have high engagement
  • What types of photos get clicked on
  • Which posts have high organic reach
  • The Facebook API does not in the traditional sense provide many dimensions for Post Insights so these are fairly restricted in v13 of the Graph API.
 
notion image
Available Dimensions
Dimension
Description
External URL
Show the site you were linking to from the post. This can be helpful to understand if promoting other content, or content from specific sites resonates with your audience.
Message
Message will contain all the contents of posted message. This can be useful if you want to quickly look was written for successful or unsuccessful posts. You could also do word count analysis and see if there is any correlation between the length of your posts and their engagement.
Full Picture
You can load the pictures used for your post and then use the Google Sheets image() function to display the photos used in the post directly in Google Sheets. This can be helpful in understanding any trends that popular vs unpopular posts have. Your audience responds to visuals much more quickly when scrolling through their feed so just seeing your top post images can be very telling.
Created Time
This provides a time stamp of when the post was created. Using the time stamp can be a great way of doing time based analysis on your page. For example what days of the week have the best engagement. What times of the day have the best engagement. Have you been posting regularly - what’s your monthly posting cadence vs your page traffic and engagement.
External Title
This is the Post’s title. Helpful to quickly analyze and understand which post is which.
ID
This is the Post’s unique identifier. This is not super helpful unless you’re cross referencing posts or doing a vlookup with post data located elsewhere.
URL
This is the facebook URL, you can go use this to see the post on the Facebook platform. This is useful if you want to dig in or view the post in it’s native format.
If there are specific dimensions you feel are missing please contact support and we’ll prioritize the updating of our Facebook Post connector. You can also craft custom requests with SyncWith using the Facebook Graph API.

Choosing Your Metrics

Now that you’ve selected the dimensions you want to report along - eg the things you’re interested in. Now you need to choose what you want to know about them. Eg you want to break things down by Post Image and Post Title and now you need to specify the information you’d like on each Post, for example you may want to focus on engagements such as Likes, Comments, Clicks or if you’re doing paid page promotion you may be more interested in Paid Reach and Paid Impressions. Finally if you’re more focused on video posts then Organic Video Views an 95% Organic Unique Video Views are important to tell how many people started watching and how many folks finished.
notion image
 
Available Metrics
Metric
Description
Likes
Total number of likes the post received.
Comments
Total number of comments left by people on the post
Shares
Total number of times the post was shared by people
Impressions
Total number of times your post (link, photo, video, etc.) was shown on someone’s screen, it is non-unique including Viral and Organic Impressions and utilizes the page_posts_impressions metric from the API.
Reach
Total number of non-paid unique people who saw the post (Viral Reach + Organic Reach NOT Paid Reach). Reach uses the page_impressions_unique metric in the graph API. Facebook does stipulate that sometimes the total reach will not always equal viral + organic.
Paid Reach
Total number of unique people who had the post viewable on their screen through paid promotion
Paid Impressions
Total number of times your post was shown on someone’s screen through paid promotion
Organic Impressions
Total number of times the post was shown on a person's screen through unpaid distribution.
Organic Reach
Total number of people who had the post viewable on their screen through unpaid distribution
Engaged Users
Total number of people who clicked anywhere on the post (comment, share, like, react, play, view photo, etc.)
Viral Impressions
Total number of times the post was shown on a person’s screen because of a social interaction for example a friend who engaged with the post (like, share, react) causing it to be seen from someone who does not follow your page. For example a user’s friend likes or follows your Page, engages with the post, shares a photo of your Page and checks into your Page.
Viral Reach
Total number of people the post was shown to because of a social interaction for example a friend who engaged with the post (like, share, react) causing it to be seen from someone who does not follow your page.
NOTE on CLICKS
Total Clicks are the sum of Link, Photo, Video and Other clicks. Facebook does not consider engagements that result in a Story to be a click, so Shares, Likes, Reactions etc are not considered to be clicks according to the Facebook API. We report using the post_clicks_by_type metric from the API which reports the non unique clicks of all types. Adding all these metrics together will provide the total clicks non-unique clicks on your post.
Clicks (Link)
Total number of times people clicked the external link from the post
Clicks (Other)
Other clicks that are not link, photo or video clicks. For example clicking a user or business name to go to their Page
Clicks (Photo View)
Total number of times the photo in your post was clicked to view.
Clicks (Video Play)
Total number of times your video was clicked to play.
Non-Viral Impressions
Total number of times your post was shown on a user’s screen. This does not include viral impressions. See Viral Impressions
Non-Viral Reach
Total number of people who had your post viewable on their screen. This does not include viral reach. See Viral Reach
Reactions: Like
Total like reactions to your post
Reactions: Love
Total love reactions to your post
Reactions: Wow
Total wow reactions to your post
Reactions: Haha
Total haha reactions to your post
Reactions: Sorry
Total sorry reactions to your post
Reactions: Anger
Total anger reactions to your post
Organic Video Views 95%
This represents the total number of completed video views via organic reach. Facebook considers a video complete if it was played from its beginning to 95% or more of its length. Any replays in a single instance of viewing a video are not included in the total views.
Organic Unique Video Views 95%
This represents the total number of people who completed watching your video via organic reach. Facebook considers a video complete if it was played from its beginning to 95% or more of its length. Any replays in a single instance of viewing a video are not included in the total views.
Paid Video Views 95%
This represents the total number of completed video views via paid reach. Facebook considers a video complete if it was played from its beginning to 95% or more of its length. Any replays in a single instance of viewing a video are not included in the total views.
Paid Unique Video Views 95%
This represents the total number of people who completed watching your video via paid reach. Facebook considers a video complete if it was played from its beginning to 95% or more of its length. Any replays in a single instance of viewing a video are not included in the total views.
Organic Unique Video Views
This represents the total number of people who payed your video for at least 3 seconds, or for nearly their total length if they're shorter than 3 seconds via organic reach.
Paid Unique Video Views
This represents the total number of people who payed your video for at least 3 seconds, or for nearly their total length if they're shorter than 3 seconds via paid reach.
 

Sorting By Specific Metrics or Dimensions

Currently posts can only sorted by the Post Created Time, if you wish to sort by a different dimension / metric you’ll nee

Setting the Update Mode

SyncWith supports 3 update modes:
  1. Replace contents of sheet
  1. Append rows to end of sheet
  1. Update or add rows as needed
The first two options are simple in nature, the last option to update or add rows as needed is more advanced
notion image

Replace Contents of Sheet

PREFERRED MODE for almost all Facebook Post Insights use cases
Every time you refresh or SyncWith refreshes based on your predefined schedule, all the cells will be deleted and the new data will be inserted.
  • Any computational rows that you’ve added will not get deleted as long as they are outside of the range being updated. If you’ve inserted a computational row in between other data rows it will get over written
  • Formatting you apply will stay in tact, eg if you specify a percentage, number, date that formatting for the column will persist
  • This mode is ideal for when you don’t want to collect or store previous data. For example you just want the last 30 days of data.

Append Rows to End of Sheet

NOT COMMONLY USED OR RECOMMENDED for Facebook Post Insights use cases
Every time you refresh or SyncWith refreshes based on your predefined schedule SyncWith will append the new rows to the last line with data
  • The entire sheet will not be re-sorted based on any sort you have but the appended data will be sorted and appended according to the sort you specified.
  • It will NOT prevent duplicate rows from being created
  • It will NOT update existing rows
  • If you want to only append new rows or properly update existing rows then you need to use Update or Add Rows as Needed
  • This is not a popular updating mode for Facebook users

Update or Add Rows as Needed

RARELY USED for Facebook Post Insights use cases
  • This mode uses all of the columns dimensions columns as a key to determine if a row is unique.
  • If the row is unique then it will add a new row
  • If the row is not unique it will update the metrics if they’ve changed.
This update mode is not commonly used because often you just download and replace all the data you need.
It’s possible that you want to limit really large updates from Facebook - eg avoid downloading 1,000s at each refresh but you want to be checking often for new data (because Facebook posts can often have engagement for 30 days after posting). In this case you could download all your historical data (say the last 3 years of posts) using the Replace Contents of Sheet mode and then change the time frame to last 30 days including today and change the update mode to Update or Add Rows as Needed and change the refresh period to day. Now every day it will check and update the last 30 days of data, but you’ll also have all the historical data present. This would be much better than downloading 1000s of duplicate rows every day when the data isn’t changing.

Scheduling Your Facebook Post Data to Google Sheets

Manual Refresh

Manual Refresh Is the default scheduling for a connection. Data will not be updated until you actively refresh it. In order to refresh the data you must either:
  • Refresh your data from in the SyncWith addon side bar via the refresh button - all connections can be manually refreshed
notion image
  • Refresh via the Add-on menu, allowing you to refresh all connections setup, or all connection(s) pushing data to the current active sheet
notion image
  • Refresh via the check box (located in the header above the data table)
notion image

Scheduled Refreshes

There are 3 options for scheduled refreshes:
  • Automatically refresh daily will refresh the data every day at the same time. The time is set based on when the connection was last updated. If you wish to set the refresh to happen at a specific time you would need to open the connection and save it at that time.
  • Automatically refresh hourly will refresh the data every hourly
  • Automatically refresh every 5 minutes will refresh the data every 5 minutes. This is not typically used for facebook data. A maximum of 100 connections can use 5 minute refresh.

Sample Connections

Facebook Video Engagement Funnel

This will help us understand how many people saw the video, started and then finished. This funnel helps us understand two very important things:
  • Whether our users are working well at the the top of the funnel - eg our titles or image catch the users attention such that they hit play
  • Whether our content is engaging enough such that they continue watching and finish the video
  • Useful Dimensions
    • Message - so we can understand what the post was that accompanied the video
    • External Title - so we can understand if it certain titles promote plays
    • Created Time - so we can look at our most recent videos first
    • External URL - in case we want to dig in and look at the actual post
  • Metrics That Make the Funnel
Reach ➡️ Organic Unique Video Views ➡️ Organic Unique Video Views 95%
  • Reach is the total unique people that saw the post
  • Organic Unique Video Views gives us the people who watched at least 3 seconds (eg not a mis click)
  • Organic Unique Video Views 95% means they essentially finished
  • In Google Sheets we then setup a helper column to calculate the % of people that click play and the % of People that finish watching
    • % Who Start =arrayformula(if(F3:F="",,G3:G/F3:F))
    • % Who Finish After Starting =arrayformula(if(G3:G="",,H3:H/G3:G))
  • Assuming this is data you want to be able to look at on daily we’ll set the refresh period to be daily so when you take a look it’s always up to date.
notion image
notion image

Facebook Post Engagement Health Check

Premise here is to look at your last 120 days of posts to understand how you’re doing. We want to generate a few things:
  • Graph showing our posting frequency - are we staying engaged with our community
    • Basically over the 120 days how many posts did we have
  • Graph showing engagement in our posts which is the key to driving viral reach:
    • Likes
    • Comments
    • Shares

Setting up the Connector

  • We’re using the custom date setting Last x days, weeks, months and using 120 - you could choose a longer or shorter period, you could even do all time to see the progress of your posts since the creation of your page
  • Dimensions we’ll need:
    • Created Time to graph our progress over time
    • External Title in case we want to dig into the underlying data of what posts did well
    • Full Picture in case we want to dig into the photos that maybe drove higher engagement
    • Message in Case we want to dig into the messages of popular posts
    • But for all intensive purposes to graph our engagement and posting regularity the only one we really need here is Created Time
  • Metrics we’ll need:
    • Comments, Likes, Shares to measure the engagement of our posts
notion image

SpreadSheet Overview

notion image
  • We’ll have our raw data showing the post date, likes, comments and shares. If you’re doing more than one post a day to your followers then you’ll have a lot more posts than days to summarize - in this example we have 232 posts over the 120 days
  • Ultimately we want a summary for each of the 120 days, so you’ll have a long list of posts, maybe 100s and you’ll have 120 days to analyze
  • You can see two example days highlighted as Red (2013-04-15) and Blue (2013-04-16) - In the raw data there are multiple rows per date but in the summary table there is only one summary per day
  • Further in the summary table 2013-04-28 had no posts - that means there will be no data for that date - but we still need to show a zero for it

Tracking Data by Day

We’re going to build graphs (could be one or many that show) our post stats by Created Time. The thing is we only get data for the days we did post not the ones we didn’t and to make the graph have all the zeros.
What we need is a all the 120 days starting with the first day we can do that by:
=arrayformula(FirstDate+{0;row(A1:119)})
notion image
You can get the first date in a number of ways one could be to take the maximum date from the Created Time column. Or if it’s just the last 120 days you then FirstDate is today()-1

Getting Posts Per Day

Now that we have a bunch of dates, we want to look at the number of posts on each day we do this with the following formula
=arrayformula(countif(K3:K,"="&P3:P122))
notion image
  • K3:K are your timestamps (Created Time) from every post
  • P3:122 is the 120 days we computed
  • So now for each of the last 120 days we count all the posts that happened on that day giving us the counts

Getting Sums of Likes , Comments and Shares by Day

  • Similar to getting the Post count by day we now want the sum of likes, comments and shares
  • So the formula is basically the same except we use SUMIF instead of COUNTIF
Likes
=arrayformula(sumif(K3:K,"="&P3:P122,L3:L))
notion image
Comments
=arrayformula(sumif(K3:K,"="&P3:P122,M3:M))
notion image
Shares
=arrayformula(sumif(K3:K,"="&P3:P122,N3:N))
notion image

Graphing the Results

If we graph Likes vs Posts we can see that our posting is waning and with it also the likes we’re receiving. Interestingly you can see that some posts generate outsized likes fvs other and that is probably a more important analysis to do. To take the likes/reach and see which posts were more viral and what was special about them. You could also take the titles of your posts and do a frequency analysis to see what keywords drive more engagement for your readerss.
notion image