In this page we’ll walk through the process of Flattening Facebook Ads Actions data using Snowflake’s logic. If you’d like to see documentation for Flattening Facebook Ad’s Actions data using Google’s BigQuery Logic, check here.
Creating a Facebook Data Source to Target River
You’ll want to ensure that ‘Actions’ is selected in the ‘Report Column’ in your River. More general information for setting up your Facebook Ads connection can be found here and here. We’ll want to pull this data from Facebook Ads and select Snowflake as the Target, you can use the Columns Mapping to map the Facebook Fields to your Target Snowflake Table.
In the following examples the auto mapping below is used:
Flattening the _actions Field
Once this has been created, you’ll want to test that this data is properly flowing into your Snowflake table. If everything is setup properly, you will see a similar JSON arrays/objects in your ‘_actions’ columns in Snowflake:
[
{
"_action_type": "landing_page_view",
"_value": "31"
},
{
"_action_type": "onsite_conversion.post_save",
"_value": "1"
},
{
"_action_type": "link_click",
"_value": "33"
}
Actions data from Facebook will be in a repeated, semi-structured format, in order to integrate the actions data with structured data for seamless analysis, we will need to transform the repeated fields into a structured format.
To do this, we’ll utilize Rivery’s Logic River and Snowflake’s Lateral Flatten function.
Note: You may want to use the Data Source to Target we created above as the first Logic Step to ensure that the data we’re flattening is the most recent pull.
We’ll want to set up a Source to Target in the Logic River to handle Flattening, this can be done by using a Source to Target River and the Flatten Function in Snowflake.
In this example we’ve chosen to use ‘SQL/Script’ and our Snowflake database. The following is the SQL script we’ll use to flatten the _actions data in this example.
select fb._account_name as account_name,
fb._account_id as account_id,
fb._campaign_id as campaign_id,
fb._date_start as date_start,
fb._date_stop as date_stop,
fb._clicks as clicks,
fb._reach as reach,
fb._impressions as impressions,
max(case when a.value:_action_type::string = 'comment' then a.value:_value::int else null end) as comments,
max(case when a.value:_action_type::string = 'post' then a.value:_value::int else null end) as posts,
max(case when a.value:_action_type::string = 'like’ then a.value:_value::int else null end) as like,
max(case when a.value:_action_type::string = 'offsite_conversion.fb_pixel_lead' then a.value:_value::int else null end) as offsite_conversion,
max(case when a.value:_action_type::string = 'post_engagement' then a.value:_value::int else null end) as post_engagement,
max(case when a.value:_action_type::string = ‘post_reaction’ then a.value:_value::int else null end) as post_reaction,
max(case when a.value:_action_type::string = 'video_view' then a.value:_value::int else null end) as video_views
from location_of_your_table as fb,
lateral flatten(input => PARSE_JSON(fb._actions), OUTER => TRUE) a
group by
fb._account_name,
fb._account_id,
fb._campaign_id,
fb._date_start,
fb._date_stop,
fb._clicks,
fb._reach,
fb._impressions
The key parts to note are the max/case when statements in the SELECT Query and the lateral flatten statements in the FROM Query.
Lateral Flatten Statement
What this does is take the’ _actions’ field and flatten these using ‘lateral flatten(input => fb._actions)’ additionally, we also use the PARSE_JSON function to read the JSON.
Max/Case When Statement
Each of the individual values in the _actions JSON values are taken by using max() in their corresponding fields and a column created for each. Note - min() could also be used here.
The following is a view of the table that will be generated after:
Note:If you’d like to add other fields from the source document, you’ll need to add this in the select and group by statement. If there are additional fields from _action that you’d like to add, you can add these by adding additional statements:
max(case when a.value:_action_type::string= ‘action_type’’ then a.value:_value::int else null end) as action_name