How To Query Your Facebook Data

Sample queries to kick-start your data exploration efforts

Published in

Openbridge

4 min readJul 2, 2018

In the following examples, we demonstrate how to query Facebook post data. These examples show how you can explore different post metrics, identify patterns, and determine which metric is most useful for analysis.

Creating A Query For Facebook Posts Insights

Our first query will be for post metrics that match post_fan* for post ID (1488123957789999) that we think performed really well.

Let's 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 looks at a specific date and 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

You can determine which one you want to explore from these metrics. 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, let's 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!

Let's 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 the 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 create more complex queries, including joins to other data sources. Check out the reference definitions for Facebook metrics here: https://developers.facebook.com/docs/graph-api/reference/v3.0/insights#availmetrics

In addition to SQL, you can use your favorite tools for data analysis, visualization, reporting, and analysis, like Tableau Software, Looker, and many others for these types of queries!

DWant to discuss further? Need a platform and team of experts to kickstart your data and analytic efforts? We can help! Getting traction adopting new technologies, especially if it means your team is working in different and unfamiliar ways, can be a roadblock to success. This is especially true in a self-service-only world. If you want to discuss a proof-of-concept, pilot, project, or any other effort, the Openbridge platform and team of data experts are ready to help.

Reach out to us at hello@openbridge.com. Prefer to talk to someone? Set up a call with our team of data experts.

Visit us at www.openbridge.com to learn how we are helping other companies with their data efforts.

How To Query Your Facebook Data

Sample queries to kick-start your data exploration efforts

Creating A Query For Facebook Posts Insights

Written by Thomas Spicer