Rivery Tips: Flatten Actions Data from Facebook Ads Using BigQuery Logic

At Rivery, we love sharing our team’s technical know-how in the form of quick tips that save lots of time and resources.

There’s nothing better than an easy tip that makes data projects more efficient!

For our latest Rivery Tip, we’ve outlined a simple tutorial on how to flatten actions data from Facebook Ads using BigQuery logic. Read on for the full scoop!

Why Flatten Actions Data from Facebook Ads In the First Place?

In Facebook Ads, an action is an activity that happens on an ad, such as a user watching a video ad. An action also refers to an activity that results from an ad, such as a user viewing an ad and then downloading an associated app.

Actions data in Facebook Ads offers a rich source of metrics on user behavior, campaign effectiveness, and other key indicators. But because Facebook Ads actions data is pulled in a repeated, semi-structured fashion, many user have to “flatten” the data into structured data to perform seamless analysis.

To demonstrate what this means, let’s walk through an example of pulling actions data from Facebook Ads in Rivery.

How to Use Logic in BigQuery to Flatten Actions Data From Facebook Ads

In order to pull actions data from Facebook Ads in Rivery, specify actions as a Report Column in your Facebook Ads River.

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

When actions data is pulled like this, the data is returned in a repetitious, semi-structured format.

But in order to facilitate frictionless analysis, actions data must be transformed into a business-ready structured format. Here’s how to do that.

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

In a single record, there’s a row for each action type. This is usually not ideal for analysis by business users or for ingestion into a BI/dashboarding tool.

One option to remedy this is to flatten the data by performing a transformation in a data warehouse using a function. This results in only one row per record instead of repeated values.

To accomplish this, you can use the Google BigQuery FLATTEN operator. This operator can handle repeated fields in all data returned from the Facebook Ads API.

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

This query returns the following data:

The results above are still not 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 together. The action types are broken out into separate columns. The final step in this query is grouping by all dimensional fields.

The results of the query are seen below:

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

What’s even better: you can automatically orchestrate the final workflow in a Logic River:

• Step 1: Open new Logic River
• Step 2: Create Logic Step that pulls raw data from Facebook Ads.
• Step 3: Create a subsequent Logic Step that uses BigQuery’s FLATTEN operator to transform the repeated values into structured format for analysis.
• Step 4: Save Logic River.

It’s as simple as that!

Get Ready for More Rivery Tips

Flattening actions data from Facebook Ads is a very helpful tool to have in your toolkit.

And the function is made even more powerful when it’s automated in a broader data workflow using Rivery’s Logic Rivers.

But that’s just one of the many Rivery Tips that we have up our sleeve! Join the Rivery Community discover more quick tips that can make a big impact.