Lining up Facebook Ads data over a time series can be a bit tricky when dealing with actions such as offsite_conversion, comment, leadgen.other, like, link_click, mention, post, and many more. This is due to Facebook not returning a JSON record if there is no data for that action for the particular date. However, utilizing xpath axes will allow your data to align.
Let's take a quick peak at the structure of the JSON response/data source.
{}
[]data
{}1
campaign_name
campaign_id
adset_id
adset_name
ad_id
ad_name
spend
[]actions
{}1
action_type like
value 2
{}2
action_type comment
value 1
{}3
action_type mention
value 1
ctr
clicks
impressions
reach
date_start
date_stop
{}2
....
If you would like to create this dataset with your own data, please use this query with the Facebook Ads connector:
https://graph.facebook.com/v5.0/<insert active campaign ID>/insights?&level=campaign&fields=campaign_name,campaign_id,adset_id,adset_name,ad_id,ad_name,spend,actions,action_values,ctr,clicks,impressions,reach&time_range[since]={date.add(-15).format('yyyy-MM-dd')}&time_range[until]={date.today.format('yyyy-MM-dd')}&time_increment=1&limit=500&sort=date_start_ascending
In our example dataset, the tabular version would looks something like this:
Date Likes
2017-10-20 2
2017-10-21 no record
2017-10-22 no record
2017-10-23 no record
2017-10-24 no record
2017-10-25 no record
2017-10-26 no record
2017-10-27 3
2017-10-28 no record
2017-10-29 4
2017-10-30 1
2017-10-31 no record
If we select @/data/date_start from our sample dataset there is 12 items; one for each day the campaign ran. However, by selecting @/data/actions/values (where 'likes' reside), more than 12 records will be returned as all values that fall under the 'actions' hierarchy will also be returned. Looking at the diagram of the data source above, @data/actions/values will return all values for all action_types including like, comment, mention and any others for subsequent days.
We have to filter for 'likes' with @/data/actions[action_type='like']/value, but now only 4 items are returned. Since there is no JSON record for some of the days, the records that do exist are squished to the top and the data is misaligned. This results in filter or grouping not working as intended:
Date Likes
2017-10-20 2
2017-10-21 3
2017-10-22 4
2017-10-23 1
2017-10-24
2017-10-25
2017-10-26
2017-10-27
2017-10-28
2017-10-29
2017-10-30
2017-10-31
To ensure that the data aligns, we can use xpath axes to only return the dates where there were likes. The xpath filtering will look like this:
@/data/date_start[preceding-sibling::actions[action_type='like']]
Date Likes
2017-10-20 2
2017-10-27 3
2017-10-29 4
2017-10-30 1
Now that the same amount of values are in each set of data, grouping and filtering is possible.
A bit more explanation of xpath axes. In our example, we use preceding-sibling. Preceding-sibling informs the system that 'actions' is at the same hierarchical level as date_start, but is preceding it alphabetically (preceding signifies preceding alphabetically while following signifies following it alphabetically). This results in the xpath searching for 'actions' at the correct hierarchical level and in the correct spot in the alphabet ( a is between a to d). As a test, replace "preceding-sibling" with "following-sibling" and note how no results are returned (a is not between d to z).
To recap:
Likes: @/data/actions[action_type='like']/value
Dates where there is >0 like: @/data/date_start[preceding-sibling::actions[action_type='like']]
This logic can be used if you would like to return other nodes at the same hierarchical level as date_start such as campaign, ad set or ad names by replacing date_start with the name of the record.
Hopefully this helps you in building out the Facebook dashboard of your dreams!
Happy Dashboarding!
Adam