How to Connect Microsoft Power BI to Facebook Advertising Data

If your organization is doing B2C paid social advertising, then you're almost certainly buying ads with Facebook. In addition to conversions from the ads themselves, you should be leveraging the data from your Facebook ads to discover useful insights and guide business decisions. In order to do this, you will need to extract your data from Facebook and transfer it to a data warehouse (such as Google BigQuery). From there you can feed your data into visualization and business-intelligence tools (like PowerBI). In this tutorial we will show you how to connect your Facebook data to Power BI using Launchpad and BigQuery.

Requirements

Before you get started, make sure you have the following:

Google Cloud Project account (free or paid)
Power BI locally installed
Launchpad account (free trial or paid)

For this tutorial, we are going to assume that you have already connected Launchpad to your Google Cloud Console project and configured a destination. If you have not done this, then log in and follow the instructions in your destination setup wizard.

Step One

Connect Launchpad to your Facebook account by navigating to the source section and following the instructions to configure a new Facebook source.

Step Two

Configure a job in Launchpad to send your campaign data from the Facebook API to your Google BigQuery database.

Step Three

Unnest the Facebook data using the following query example and build a table by saving the data as a BigQuery table or setting up a scheduled query to update the table each day after your daily job in Launchpad has finished.

Sample Facebook Query:

    
    SELECT
        etl_date AS date,
        account_name,
        campaign_name,
        campaign_id,
     SUM((
        SELECT value
        FROM UNNEST (actions)
        WHERE action_type = 'link_click'
     )) AS link_clicks,
     SUM((
        SELECT value
        FROM UNNEST (actions)
        WHERE action_type = 'landing_page_view'
     )) AS landing_page_view,
     SUM((
        SELECT value
        FROM UNNEST (actions)
        WHERE action_type = 'page_engagement'
     )) AS page_engagement,
     SUM((
        SELECT value
        FROM UNNEST (action_values)
        WHERE action_type = 'offsite_conversion.fb_pixel_add_to_cart'
     )) AS offsite_add_to_cart,
     SUM((
        SELECT value
        FROM UNNEST (actions)
        WHERE action_type = 'offsite_conversion.fb_pixel_view_content'
     )) AS offsite_page_engagement,
     SUM((
        SELECT value
        FROM UNNEST (action_values)
        WHERE action_type = 'offsite_conversion.fb_pixel_initiate_checkout'
     )) AS offsite_initiate_checkout,
     SUM((
        SELECT click_7d
        FROM UNNEST (action_values)
        WHERE action_type = 'offsite_conversion.fb_pixel_purchase'
     )) AS revenue_click_7d,
     SUM((
        SELECT view_1d
        FROM UNNEST (action_values)
        WHERE action_type = 'offsite_conversion.fb_pixel_purchase'
     )) AS revenue_view_1d,
     SUM((
        SELECT click_7d
        FROM UNNEST (actions)
        WHERE action_type = 'offsite_conversion.fb_pixel_purchase'
     )) AS purchase_click_7d,
     SUM((
        SELECT view_1d
        FROM UNNEST (actions)
        WHERE action_type = 'offsite_conversion.fb_pixel_purchase'
     )) AS purchase_view_1d,
        SUM(reach) AS reach,
        SUM(clicks) AS clicks,
        SUM(impressions) AS impressions,
        SUM(spend) AS spend
     
     FROM `your_project_id.your_dataset_name.your_table_name_202*` /* Use an asterisk * in the table name as a wildcard to include multiple date partitions in your query */ 
     
     GROUP BY
        date,
        account_name,
        campaign_name,
        campaign_id

Step Four

Open Microsoft Power BI and choose "Get data"
Click on "Database" from the list of data source options
Select Google BigQuery as your database
Configure advanced options or simply click okay and Microsoft will send you to Google's authentication wizard
Select your Facebook ads table and click okay