Flatten Facebook Ads Actions data using BigQuery Logic

Knowledge Tutorial
1

A common use case we see from working with customers who pull data from Facebook Ads is flattening their ‘actions’ data. To further explain what this means, I’ll first walk through an example of pulling actions data from Facebook Ads in Rivery.

In order to pull actions data from Facebook Ads, you’ll need to specify it as a Report Column in your Facebook Ads river.

image

In the ‘Column Mapping’ step, you’ll notice the ‘_actions’ field is set to REPEATED as its data mode with nested fields ‘_value’ and ‘_action_type’.

image
image1099×201 7.39 KB

Pulling actions data returns data in a repeated, semi-structured format. However, in order to integrate the actions data with structured data for seamless analysis, you will first need to transform the repeated fields into a structured format.

One option is to do this transformation in a data warehouse using a function to flatten the data so that the results contain only one row per record instead of repeated values.

In this example, we’ll walk through using the Google BigQuery FLATTEN operator to handle repeated fields in data returned from the Facebook Ads API.

First, let’s take a look at how the raw actions data comes out of the API:

image

You can see that in a single record, we are given a row for each action type. This is usually not ideal for analysis by business users or ingestion from a BI/dashboarding tool.

Now, let’s apply the flatten() operator to the FROM clause of a SELECT statement:

image

This query returns the following data:

image

The results above may not be an ideal format for analysis. For example, the ‘_impressions’ column values have been duplicated to match the number of action types returned for a given ad.

To get around this duplication, aggregation and grouping can be used in conjunction with the FLATTEN operator. In the query below, the measures such as ‘_impressions’ and ‘_reach’ are summed and the action types are broken out into separate columns. The final step of the query is grouping by all dimensional fields.

image
image888×552 14.1 KB

The results of the query are as seen below:

image
image828×414 15.7 KB

Now we have all metrics and action values at the same level of granularity in a relational table format.

Thus, the final workflow can be orchestration in a Logic River using the combination of 1) pulling raw data from FB Ads and 2) using the FLATTEN operator to transform the repeated values into the ideal structured format for analysis.

image
image690×635 22.4 KB

4 Likes
2

Find the below example for Standard SQL in BigQuery:

SELECT
 CAST(_date_start AS DATE) AS date_start,
 _account_id  AS  account_id,
 _account_name AS account_name,
 _campaign_id  AS campaign_id,
 _campaign_name as campaign,
 _adset_id  AS adset_id,
 _adset_name as adset_name,
 _ad_id  AS ad_id,
 _ad_name as ad_name,
 cast(_spend as NUMERIC) AS cost,
 cast(_impressions as NUMERIC) AS impressions,
 cast(_clicks as NUMERIC) AS clicks,
 cast(_reach as NUMERIC) AS reach,
 _currency AS currency,
 SUM(CAST(Case When action._action_type = 'comment' Then action._value Else Null End AS NUMERIC)) as comments,
 SUM(CAST(Case When action._action_type = 'offsite_conversion.fb_pixel_lead' Then action._value Else Null End AS NUMERIC)) as leads,
 SUM(CAST(Case When action._action_type = 'like' Then action._value Else Null End AS NUMERIC)) as page_likes,
 SUM(CAST(Case When action._action_type = 'post_engagement' Then action._value Else Null End AS NUMERIC)) as post_engagement,
 SUM(CAST(Case When action._action_type = 'post' Then action._value Else Null End AS NUMERIC)) as post_shares,
 SUM(CAST(Case When action._action_type = 'post_reaction' Then action._value Else Null End AS NUMERIC)) as post_reactions,
 SUM(CAST(Case When action._action_type = 'video_view' Then action._value Else Null End AS NUMERIC)) as video_views,
FROM `rivery_demo.fb_ads_raw` as base
left join UNNEST(base._actions) as action
  GROUP BY
  campaign,
  account_name,
  date_start,
  ad_id,
  cost,
  adset_name,
  impressions,
  campaign,
  campaign_id,
  ad_name,
  account_id,
  adset_id,
  currency,
  reach,
  clicks

Standard SQL uses the UNNEST operator instead of FLATTEN.

In the logic step itself, you can change the SQL dialect to Standard SQL in the Advanced Options menu:

image

2 Likes