In the following examples we demonstrate how to query Facebook post data. The goal is to show how you can explore different post metrics, identify patterns and determine which metric has the most utility for an analysis.
Our first query will be for post metrics that match post_fan* for post ID (1488123957789999). We think this post performed really well but we want to dig into the numbers more closely. Lets dig in!
database=> SELECT ob_date, metric_name, value FROM fb_post_insights WHERE metric_name like 'post_fan%' AND post_id = '14881239577899999' and ob_date LIKE '%2018-08-1%' ORDER BY ob_date ASC;
In this case our query is looking at a specific date and a particular post ID. The results are as follows:
ob_date | metric_name | value
------------+----------------+-------
2018-08-10 | post_fan_reach | 30170
2018-08-11 | post_fan_reach | 30184
2018-08-12 | post_fan_reach | 31077
2018-08-14 | post_fan_reach | 31649
2018-08-15 | post_fan_reach | 31787
2018-08-16 | post_fan_reach | 31848
2018-08-17 | post_fan_reach | 31890
2018-08-18 | post_fan_reach | 31895
2018-08-19 | post_fan_reach | 33736
Next, we look at want to look at post_impression* metrics
database=> SELECT ob_date, metric_name, value FROM fb_post_insights WHERE metric_name like 'post_impression%' AND post_id = '14881239577899999' and ob_date LIKE '%2018-08-18%' ORDER BY ob_date ASC;
This time you can see there are multiple metrics that match that query:
ob_date | metric_name | value
------------+------------------------------------------+-------
2018-08-18 | post_impressions_viral_unique | 2038
2018-08-18 | post_impressions_fan_paid | 0
2018-08-18 | post_impressions_viral | 3406
2018-08-18 | post_impressions_fan | 56095
2018-08-18 | post_impressions_organic | 61643
2018-08-18 | post_impressions_by_paid_non_paid_unique | 0
2018-08-18 | post_impressions_by_paid_non_paid | 65049
2018-08-18 | post_impressions_organic_unique | 35411
2018-08-18 | post_impressions_by_paid_non_paid | 65049
2018-08-18 | post_impressions_fan_unique | 31895
2018-08-18 | post_impressions_paid_unique | 0
2018-08-18 | post_impressions_by_paid_non_paid | 0
2018-08-18 | post_impressions_by_story_type | 3406
2018-08-18 | post_impressions_fan_paid_unique | 0
2018-08-18 | post_impressions_unique | 37643
2018-08-18 | post_impressions | 65049
2018-08-18 | post_impressions_by_paid_non_paid_unique | 37643
2018-08-18 | post_impressions_paid | 0
2018-08-18 | post_impressions_by_story_type_unique | 2038
Out of these metrics you can determine which one you want to explore. Is itpost_impressions_organic_unique , post_impressions_unique , post_impressions_by_paid_non_paid_unique?
You say “Hey, I think post_impressions_unique might be the one I want.”
Before we decide, lets run the same query for a different date:
database=> SELECT ob_date, metric_name, value FROM fb_post_insights WHERE metric_name like 'post_impression%' AND post_id = '14881239577899999' and ob_date LIKE '%2018-08-19%' ORDER BY ob_date ASC;
Here are the results:
ob_date | metric_name | value
------------+------------------------------------------+-------
2018-08-19 | post_impressions_organic_unique | 38506
2018-08-19 | post_impressions_by_story_type_unique | 2057
2018-08-19 | post_impressions | 70088
2018-08-19 | post_impressions_by_paid_non_paid | 70088
2018-08-19 | post_impressions_fan_paid_unique | 0
2018-08-19 | post_impressions_unique | 40750
2018-08-19 | post_impressions_fan | 59649
2018-08-19 | post_impressions_paid | 0
2018-08-19 | post_impressions_paid_unique | 0
2018-08-19 | post_impressions_organic | 66670
2018-08-19 | post_impressions_viral_unique | 2057
2018-08-19 | post_impressions_fan_paid | 0
2018-08-19 | post_impressions_by_paid_non_paid | 70088
2018-08-19 | post_impressions_by_paid_non_paid_unique | 40750
2018-08-19 | post_impressions_fan_unique | 33736
2018-08-19 | post_impressions_viral | 3418
2018-08-19 | post_impressions_by_paid_non_paid_unique | 0
2018-08-19 | post_impressions_by_story_type | 3418
The 18th results for the metric post_impressions_unique is 37643 and on the 19th the post_impressions_unique number was 40750. It is in the range of what was in the UI so this looks good!
Lets dig into this metric for the entire month:
database=> SELECT ob_date, metric_name, value FROM fb_post_insights WHERE metric_name like 'post_impressions_unique%' AND post_id = '14881239577899999' and ob_date LIKE '%2018-08-%' ORDER BY ob_date ASC;
Here are results for the query:
ob_date | metric_name | value
------------+-------------------------+-------
2018-08-01 | post_impressions_unique | 32884
2018-08-02 | post_impressions_unique | 33013
2018-08-03 | post_impressions_unique | 33063
2018-08-04 | post_impressions_unique | 34618
2018-08-04 | post_impressions_unique | 33631
2018-08-05 | post_impressions_unique | 34173
2018-08-06 | post_impressions_unique | 34299
2018-08-07 | post_impressions_unique | 34569
2018-08-08 | post_impressions_unique | 34618
2018-08-09 | post_impressions_unique | 34698
2018-08-10 | post_impressions_unique | 34754
2018-08-11 | post_impressions_unique | 34817
2018-08-12 | post_impressions_unique | 36109
2018-08-14 | post_impressions_unique | 37046
2018-08-15 | post_impressions_unique | 37248
2018-08-16 | post_impressions_unique | 37470
2018-08-17 | post_impressions_unique | 37607
2018-08-18 | post_impressions_unique | 37643
2018-08-19 | post_impressions_unique | 40750
Looking at post_impressions_unique numbers reveal this is the metric you want to use for reporting!
This is a simple example of exploring Facebook post data via database queries. You can certainly create more complex queries, including joins to other data sources. Have fun exploring!
For reference to Facebook metric definitions, check out their docs here: https://developers.facebook.com/docs/graph-api/reference/v3.0/insights#availmetrics