Insights

How to Connect Microsoft Power BI to Facebook Advertising Data

by Paul Cote on Feb 10, 2023

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

We're Here to Help

If you need any assistance setting up your data flow, then you've come to the right place. The data experts at Calibrate Analytics will be more than happy to guide you through our no-code platform, Launchpad, or create a custom solution specifically to fit your unique business needs. Don't hesitate to get in touch!

Schedule A Demo

Share this post: