Adventures with R - Facebook Ads (Part 3)

Now that we have set up the required packages and authentications in Facebook, it is time to go to work in R !!

RStudio is the programming interface that I used. For ease of use, I go to Tools --> Global Options --> Appearance. I used Sky as my RStudio theme, Lucida console, font size 11 and Idle Fingers as my Editor theme. It gives a neutral black background and the fonts (comments, code, etc) is much more clearer to see


First part of the code - working directory and packages (All R code is in blue so that it easy to differentiate in the blog)

getwd()

setwd("<Use your own folder directory structure here")

#install.packages("httr")
library(httr)
library(dplyr)

library(devtools)

#devtools::install_github('Rdatatable/data.table')
library(data.table)

#install.packages("cardcorp/fbRads")
library(fbRads)
library(rlist)
library(RODBC)

library(stringr)

The next is a two step process.

Step 1 - One time to get the authentication for the first time
# Getting the authentication for the first time
app <- oauth_app('facebook', '<use your own account id here>','<get the token from Facebook and put it here>')
Sys.setenv('HTTR_SERVER_PORT' = '1410/')
tkn <- oauth2.0_token(oauth_endpoints('facebook'), app, scope = 'business_management', 
                      type = 'application/x-www-form-urlencoded', cache = FALSE)
tkn <- jsonlite::fromJSON(names(tkn$credentials))$access_token
saveRDS(tkn, 'token.rds')

This will create a rds file under your working directory. 

Step 2. Comment out the earlier step 1 code and use the code below for subsequent runs. 
#Invoke the FB Authentication token

tkn <- readRDS('token.rds')


#create the facebook initiation file
fbacc <- fbRads::fbad_init(accountid = '<use your business account id here', tkn, version = '2.11')

Note : The version number is hard coded to 2.11. The package does have an API version that you can use so that you are not hard coding values. 
Reference URL for Facebook API change log : https://developers.facebook.com/docs/graph-api/changelog

I had originally coded the R script under 2.11 and we already have a 2.12 available now. The good news is that Facebook *generally* has a pretty wide latitude around feature deprecation. The only issue you would run into is if a feature you used for data query that you used is no longer available in the new API version


startdate <- Sys.Date()-2

Note : My requirement was a daily pull with a 1 day attribution window. Because of that, I am pulling data for a day beyond yesterday. This feature is not a preset time frame that Facebook provides. 

l <- fbRads::fb_insights(time_range = paste0("{'since':'",startdate,"','until':'",startdate,"'}"), 
                         level = 'ad', 
                         fields = jsonlite::toJSON(c('account_name',
                                           'campaign_name', 
                                           'adset_name',
                                           'ad_name',
                                           'reach',
                                           'frequency',
                                           'impressions', 
                                           'clicks', 
                                           'unique_clicks',
                                           'total_actions',
                                           'spend',
                                           'actions',
                                           'action_values'
                         )
                         ),
                         breakdowns="age,gender",
                         action_attribution_windows="1d_click"
                         
)

I needed to pull custom variables and therefore I am pulling actions and action values separately.  If you don't have custom variables, the code becomes easier to execute. 

final <- data.table::rbindlist(l, fill = TRUE)
final$list_id <- 1:nrow(final)

actions_data <- data.table::rbindlist(final$actions, fill = TRUE, idcol = 'list_id')
actionvalues_data <- data.table::rbindlist(final$action_values, fill = TRUE, idcol = 'list_id')


actions_data1 <- actions_data[which(actions_data$action_type=="offsite_conversion.fb_pixel_purchase")]
actionvalues_data1 <- actionvalues_data[which(actionvalues_data$action_type=="offsite_conversion.fb_pixel_purchase")]


# create final data set without the action values
final1 <- subset(final, select = -c(actions) )
final1 <- subset(final1, select = -c(action_values))

#merge the data frames together
l1 <- merge(x = final1, y = actions_data1, by = "list_id", all.x = TRUE)
#l1 <- subset(l1, select = -c(list_id))
l1 <- subset(l1, select = -c(action_type))

l1 <- setnames(l1, 
               old=c("account_name", "campaign_name", "value", "1d_click"),
               new=c("Account", "Campaign", "Purchase","Purchase_1d")
)
l1 <-  replace(l1,is.na(l1),0)

l2 <- merge(x = l1, y = actionvalues_data1, by = "list_id", all.x = TRUE)
l2 <- subset(l2, select = -c(list_id))
l2 <- subset(l2, select = -c(action_type))

l2 <- setnames(l2, 
               old=c("value", "1d_click"),
               new=c("PurchaseConversionValue","PurchaseConversionValue_1d")
)
l2 <-  replace(l2,is.na(l2),0)

currentDate <- Sys.Date() 
csvFileName <- paste("//TTCDMFS01/ServerRepo/DatamartETL/Facebook/DropFolder/FBAdsTGC", currentDate,".csv",sep="")

I am outputting the final output file as a csv. If your need is to write it automatically to a warehouse, I recommend using the RODBC package to execute the SQL. 

write.csv(l2, 
          file=csvFileName, 
          row.names = FALSE, na = "")

I hope you had fun in this R series. Watch the space for more R adventures...