Checking Availability with rdfp

Steven M. Mortimer

2018-03-29

First, we load rdfp and specify the DFP network we would like to connect to. Then we authenticate by using dfp_auth(). Any existing cached token would be used or we will be prompted to authenticate via the browser.

suppressWarnings(suppressMessages(library(dplyr)))
suppressWarnings(suppressMessages(library(lubridate)))
library(rdfp)
options(rdfp.network_code = 123456789)
dfp_auth()

Availability by Month

Ad traffickers or display advertising reporters might get requests to determine the availability of a line item by month for a multi-month contract proposal. A LineItem has one InventoryTargeting object that describes which AdUnit and Placement objects it can target, and optional additional Targeting subclass objects that represent geographical, custom, or other criteria. You can either:

  1. Create your own line item from scratch
  2. Use dfp_getLineItemsByStatement() to pull down details on a line item and modify any fields that need to be different for your line item.

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/doubleclick-publishers/docs/reference/v201802/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] 2018
#> 
#> $date$month
#> [1] 12
#> 
#> $date$day
#> [1] 29
#> 
#> 
#> $hour
#> [1] 12
#> 
#> $minute
#> [1] 44
#> 
#> $second
#> [1] 29
#> 
#> $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] 2018
#> 
#> $date$month
#> [1] 12
#> 
#> $date$day
#> [1] 30
#> 
#> 
#> $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-01       
#> 2 IMPRESSIONS              0          1000 2019-02       
#> 3 IMPRESSIONS              0          1000 2019-03       
#> 4 IMPRESSIONS              0          1000 2019-04       
#> 5 IMPRESSIONS              0          1000 2019-05       
#> 6 IMPRESSIONS              0          1000 2019-06

Availability by Targeting Criteria

The previous example shows how to determine availability across multiple months for a single line item. It is also possible to determine the availability for each targeting segment of your forecast. The dfp_getAvailabilityForecast() function takes a second or two, so it is very inefficient to submit each county individually. Instead, you can submit a line item with targeting that says County A or County B or County C … until you’ve covered each county that you need a forecast for. If you ask for targeting criteria breakdowns in the forecastOptions, then you can retrieve the availability contribution from each county while only using one call to dfp_getAvailabilityForecast().

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/doubleclick-publishers/docs/reference/v201802/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)[[1]]
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

Availability for an Existing Line

Sometimes you just want to check the availability on an existing line, say, to see if you can renew an existing contract. DFP makes this very easy, by providing the function dfp_getAvailabilityForecastById(). You’ll need to first determine the Id of the line item you would like to check, then plug it into the function. Note the empty list() provided to the forecastOptions argument. The API will error out if you do not provide it, but an empty list will quell those error messages and utilize the default forecast options.

# request for this id with no special forecastOptions
forecast_request <- list(lineItemId=single_item$id,
                         forecastOptions = list())

this_result <- dfp_getAvailabilityForecastById(forecast_request)
this_result[,c('lineItemId', 'orderId', 'availableUnits', 'deliveredUnits', 'matchedUnits')]
#> # A tibble: 1 x 5
#>   lineItemId    orderId availableUnits deliveredUnits matchedUnits
#>        <dbl>      <dbl>          <dbl>          <dbl>        <dbl>
#> 1 4578088239 2216754702           1352          16215         1352

Check out the Tests

The rdfp package has quite a bit of unit test coverage to track any changes made between newly released versions of DFP (typically 4 each year). These tests are an excellent source of examples because they cover most all cases of utilizing the package functions.

For example, if you’re not sure on how to use custom date ranges when requesting a report through the ReportService, just check out the tests at https://github.com/StevenMMortimer/rdfp/blob/master/tests/testthat/test-ReportService.R

If you want to know how to create a user, just look at the test for dfp_createUsers()

request_data <- list(users=list(name="TestUser - 1",
                                email="testuser123456789@gmail.com",
                                roleId=-1)
                     )
dfp_createUsers_result <- dfp_createUsers(request_data)