Checking Availability with rdfp

A LineItem from Scratch

Creating your own line item is easy. Some line item fields are absolutely required in order to forecast so you may need to try different lines to see how they forecast. Personally, I’ve found that most line items need the fields we create in the example below (e.g primaryGoal, targeting, etc). One quirk with the DFP API is that the fields on your line item must be provided in the same order as the reference documentation. You can review the documentation for this object at https://developers.google.com/ad-manager/api/reference/v201905/LineItemService.LineItem.

sample_line <- list()
sample_line$startDateTime <- ''
sample_line$endDateTime <- ''
sample_line$deliveryRateType <- 'EVENLY'
sample_line$lineItemType <- 'STANDARD'
sample_line$priority <- 8
sample_line$costType <- 'CPM'
sample_line$creativePlaceholders$size <- list(width=666, height=176, isAspectRatio='false')
sample_line$creativePlaceholders$expectedCreativeCount <- 1
sample_line$creativePlaceholders$creativeSizeType <- 'PIXEL'
sample_line$primaryGoal <- list(goalType='LIFETIME', unitType='IMPRESSIONS', units=1000)
sample_line$targeting <- list(inventoryTargeting=list(targetedAdUnits=list(adUnitId=133765936, 
                                                                           includeDescendants="true")))

You’ll notice that this example line item does not have targeting, which you can add. It also does not have a startDateTime and endDateTime. We’ll write a loop and pass those in each time to get availability for each month across multiple months.

Modifying an Existing LineItem

If you want to use an existing line item because it’s already got so many details, then here is an example of how you would pull that item down and use it. You will need to change out the filterStatement to pick the line item you want.

my_filter <- "WHERE LineItemType='STANDARD' and Status='DELIVERING' LIMIT 10"
line_item_details <- dfp_getLineItemsByStatement(list(filterStatement=list(query=my_filter)))

# pull out the 1st line item in the list of returned results
# we'll use this as a template for creating the hypothetical line items
single_item <- line_item_details[[1]]

Creating Datetimes in DFP

DFP keeps track of dates and times by separating things like the year, month, and day into 3 separate integers. The special DFP format requires a function to convert Date objects in R into the corresponding DFP list. The following function is helpful in doing just that.

# supply a datetime
dfp_date_to_list(Sys.time())
#> The date provided is not at least 1 hour into the future. Setting to one hour after now.
#> $date
#> $date$year
#> [1] 2019
#> 
#> $date$month
#> [1] 6
#> 
#> $date$day
#> [1] 5
#> 
#> 
#> $hour
#> [1] 14
#> 
#> $minute
#> [1] 30
#> 
#> $second
#> [1] 36
#> 
#> $timeZoneId
#> [1] "America/New_York"
# supply a date and assume the beginning of the day for hours, mins, secs
dfp_date_to_list(Sys.Date()+1, "beginning")
#> $date
#> $date$year
#> [1] 2019
#> 
#> $date$month
#> [1] 6
#> 
#> $date$day
#> [1] 6
#> 
#> 
#> $hour
#> [1] 0
#> 
#> $minute
#> [1] 0
#> 
#> $second
#> [1] 0
#> 
#> $timeZoneId
#> [1] "America/New_York"

Generating Forecasts

Here is an example of how to loop through individual months and determine availability 6 months out for the sample line item created previously. It is fairly straightforward to take the month start and end dates and substitute them into the hypothetical line item. The one quirk is that, in order to follow the API, we must create lists of lists that are redundant at times. For example, the forecast request has a lineItem field that takes a ProspectiveLineItem that contains its own lineItem field: list(lineItem=list(lineItem=this_sample_line) .... As long as you follow the object fields according to the Google reference documentation, then everything should work. Most everything needs to be formed as a list of lists with field names.

all_forecasts <- NULL
month_start_dates <- as.Date(format(Sys.Date() %m+% months(1:6), '%Y-%m-01'))
month_end_dates <- ceiling_date(month_start_dates, 'months') - 1
for(i in 1:length(month_start_dates)){
  this_sample_line <- sample_line
  this_sample_line$startDateTime <- dfp_date_to_list(month_start_dates[i], daytime='beginning')
  this_sample_line$endDateTime <- dfp_date_to_list(month_end_dates[i], daytime='end')
  forecast_request <- list(lineItem=list(lineItem=this_sample_line),
                           forecastOptions=list(includeTargetingCriteriaBreakdown='false', 
                                                includeContendingLineItems='false'))
  this_result <- dfp_getAvailabilityForecast(forecast_request)
  this_result <- this_result[,c('unitType', 'availableUnits', 'reservedUnits')]
  this_result$forecast_month <- format(month_start_dates[i], '%Y-%m')
  all_forecasts <- rbind(all_forecasts, this_result)
}
all_forecasts
#> # A tibble: 6 x 4
#>   unitType    availableUnits reservedUnits forecast_month
#>   <chr>                <dbl>         <dbl> <chr>         
#> 1 IMPRESSIONS              0          1000 2019-07       
#> 2 IMPRESSIONS              0          1000 2019-08       
#> 3 IMPRESSIONS              0          1000 2019-09       
#> 4 IMPRESSIONS              0          1000 2019-10       
#> 5 IMPRESSIONS              0          1000 2019-11       
#> 6 IMPRESSIONS              0          1000 2019-12

Determining Geo Ids

In this example, we’ll determine availability for all counties in Texas. First, you will need to determine the geography codes for all of the counties in Texas. Luckily, DFP provides a table of geographic codes for the entire world. Codes can be retrieved like so:

# get codes for US states and counties
request_data <- list(selectStatement=
                       list(query="SELECT 
                                      Id
                                    , Name
                                    , CanonicalParentId
                                    , CountryCode
                                    , Type 
                                  FROM Geo_Target 
                                  WHERE CountryCode='US' AND (Type='State' OR Type='County')"))

us_geos <- dfp_select(request_data)
texas_id <- us_geos %>%
  filter(type == 'STATE', name == 'Texas') %>%
  select(id, state = name)

us_counties <- us_geos %>%
  filter(type == 'COUNTY') %>%
  select(id, canonicalparentid, county = name)

texas_counties <- inner_join(us_counties, texas_id, by=c('canonicalparentid'='id'))

Next, we need to format the geographies into objects of type Location that at least contain an id denoting the area.

# format the county ids into a list so they can be passed 
# over to the geoTargeting field of the ProspectiveLineItem
geo_targets <- as.list(texas_counties$id)
geo_targets <- lapply(geo_targets, FUN=function(x){list(id=x)})
names(geo_targets) <- rep('targetedLocations', length(geo_targets))

The sample line we created earlier does not contain the start and end datetimes, so we’ll consider a 90-day period in the future.

this_sample_line <- sample_line

# look at availability for the next 90 days
this_sample_line$startDateTime <- dfp_date_to_list(Sys.Date() + 1, daytime='beginning')
this_sample_line$endDateTime <- dfp_date_to_list(Sys.Date() + 91, daytime='end')

Setting up The Targeting

Specifying the targeting can be a little tricky. We need to NULL the original targeting field we created in the sample line and put geoTargeting first because the documentation and the API needs elements in the order that they are specified. For example, the ForecastService needs the targeting fields to be in the order listed at https://developers.google.com/ad-manager/api/reference/v201905/ForecastService.Targeting.

# recreate the targeting field from scratch
this_sample_line$targeting <- NULL
this_sample_line$targeting$geoTargeting <- geo_targets
# re-use the inventoryTargeting criteria from the original sample
this_sample_line$targeting$inventoryTargeting <- sample_line$targeting$inventoryTargeting

Requesting Targeting Criteria Breakdowns

Finally, we’ll make the request to determine availability. There are 2 important details in getting back the breakdown of availability for each county in this example.

1. Ensure that includeTargetingCriteriaBreakdown = 'true'
2. Ensure that as_df = FALSE in dfp_getAvailabilityForecast()

Targeting criteria breakdowns are omitted by default so we need to tell DFP to return them. Those breakdowns are formatted as a nested list, so if you do not specify, you’ll be stuck with a very wide data.frame containing one column for every element in the nested list and it is not easy to work with.

# request the targeting criteria breakdown this time to get that detail
forecast_request <- list(lineItem = list(lineItem = this_sample_line),
                         forecastOptions = list(includeTargetingCriteriaBreakdown = 'true', 
                                                includeContendingLineItems = 'false'))

# get the forecasted availability and make sure to specify as_df=FALSE
this_result <- dfp_getAvailabilityForecast(forecast_request, as_df=FALSE)
breakdowns <- this_result[c(names(this_result) %in% 'targetingCriteriaBreakdowns')]

Parsing Targeting Criteria Breakdowns

Once you’ve got the result, you’ll see an element in the list called targetingCriteriaBreakdowns. This element contains a breakdown for each targeting field specified, including the inventory targeting that is for the ad unit. We are not interested in that breakdown and it is not mutually exclusive from the geoTargeting, so we need to exclude. There are many ways to determine the break downs you want. In the example below we use sapply to find all breakdowns that have a dimension of 'GEOGRAPHY', but we could also check that the targetingCriteriaId is in the list of county ids. This all depends on which breakdowns you’re interested in and how you want to pull them out of the forecasted response.

# only select the breakdowns that are GEOGRAPHY
# this omits the Ad Unit and Ad Size breakdowns
geo_breakdowns <- breakdowns[sapply(breakdowns, 
                                    FUN=function(x){
                                      x$targetingDimension == 'GEOGRAPHY'
                                    })]

avails <- plyr::ldply(geo_breakdowns, 
                      .fun=function(x){
                        return(as.data.frame(x))
                      }, .id=NULL)

avails <- avails %>% 
  mutate(ImpressionsTotal=as.integer(matchedUnits),
         ImpressionsAvailable=as.integer(availableUnits), 
         ImpressionsBooked=as.integer(ImpressionsTotal-ImpressionsAvailable), 
         PctAvail=ImpressionsAvailable/ImpressionsTotal) %>%
  select(targetingCriteriaName, ImpressionsTotal, ImpressionsBooked, ImpressionsAvailable, PctAvail)

The breakdowns are not necessarily mutually exclusive, so be careful when totaling them up. The counties are exclusive, so it’s okay to sum them.

# total availability across Texas counties
sum(avails$ImpressionsAvailable) / sum(avails$ImpressionsTotal)
#> [1] 1