Flatten Facebook Ads Actions data using BigQuery Logic

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.


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’.

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:


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:


This query returns the following data:


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.

The results of the query are as seen below:

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.