Pulling Reports 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.

library(rdfp)
options(rdfp.network_code = 123456789)
dfp_auth()

Delivery by Advertiser

A common report request is to check how each line item is delivering for an advertiser. This type of request can be run through a DFP report job. Report jobs have a special flow, but there is a single function, dfp_full_report_wrapper(), in the rdfp package that will manage that entire flow and run a requested job for you.

In this example, we specify dimensions including the line, order, advertiser, and ad unit. The 'FLAT' ad unit view just ensures that we get tabular data with one row per ad unit in case we’d like to total them up. If you’d like to run multiple reports with slight variations in date or dimensions, it is encouraged that you keep you criteria and use a loop or vectorized function to generate each report since the request data must be formatted as a list in each call.

request_data <- list(reportJob =
                        list(reportQuery =
                               list(dimensions = 'MONTH_AND_YEAR',
                                    dimensions = 'AD_UNIT_ID',
                                    dimensions = 'AD_UNIT_NAME',
                                    dimensions = 'ADVERTISER_NAME',
                                    dimensions = 'ORDER_NAME',
                                    dimensions = 'LINE_ITEM_NAME',
                                    adUnitView = 'FLAT',
                                    columns = 'AD_SERVER_IMPRESSIONS', 
                                    columns = 'AD_SERVER_CLICKS',
                                    dateRangeType = 'LAST_WEEK')
                             )
                      )
report_data <- dfp_full_report_wrapper(request_data)
report_data[,c('Dimension.MONTH_AND_YEAR', 'Dimension.AD_UNIT_ID', 'Column.AD_SERVER_CLICKS')]
#> # A tibble: 19,237 x 3
#>    Dimension.MONTH_AND_YEAR Dimension.AD_UNIT_ID Column.AD_SERVER_CLICKS
#>    <chr>                                   <dbl>                   <dbl>
#>  1 2019-05                           21677451947                       1
#>  2 2019-05                           21677451947                       0
#>  3 2019-05                           21677451947                       0
#>  4 2019-05                           21677451947                     157
#>  5 2019-05                           21677451947                      31
#>  6 2019-05                           21677451947                       0
#>  7 2019-05                           21677451947                      47
#>  8 2019-05                           21677451947                      14
#>  9 2019-05                           21677451947                      28
#> 10 2019-05                           21677451947                      48
#> # … with 19,227 more rows

Working with Saved Queries

DFP has a feature that allows for saving query criteria. These queries can be pulled back via the API and the criteria submitted back so that you always run the same report each time, or can easily replicate a query that another person has created in the UI.

# look for a particular saved query
request_data <- list(filterStatement=list(query="WHERE id = 936165016"))
this_result <- dfp_getSavedQueriesByStatement(request_data, as_df=FALSE)
this_report_query <- this_result$reportQuery

# resubmit the report job with the saved query
request_data <- list(reportJob=list(reportQuery = this_report_query))
report_data <- dfp_full_report_wrapper(request_data)
report_data[,c('Dimension.AD_UNIT_ID', 'Column.AD_SERVER_CLICKS')]
#> # A tibble: 11,363 x 2
#>    Dimension.AD_UNIT_ID Column.AD_SERVER_CLICKS
#>                   <dbl>                   <dbl>
#>  1          21677553895                       0
#>  2          21677553895                       0
#>  3          21677553895                       0
#>  4          21677553895                       0
#>  5          21677451944                       0
#>  6          21677451944                       0
#>  7          21677451947                      11
#>  8          21677451947                      30
#>  9          21677451947                       1
#> 10          21677451947                       2
#> # … with 11,353 more rows

A More Detailed Explanation of the Report Process

Reports actually require 3 steps from the [ReportService] (https://developers.google.com/ad-manager/api/reference/v201905/ReportService): 1) to request the report, 2) check on its status, and 3) download. This basic process flow is required for all reports requested via this service. The wrapper function used above named dfp_full_report_wrapper manages all aspects of reporting, so this level of detail is not needed unless the wrapper service does not quite fit your needs.

# In order to run a report you must specify how the report should be structured 
# by specifying a reportQuery inside a reportJob. All of the dimensions, columns, 
# date range options, etc. are documented at:
# https://developers.google.com/ad-manager/api/reference/v201905/ReportService.ReportQuery
request_data <- list(reportJob=list(reportQuery=list(dimensions='MONTH_AND_YEAR', 
                                                     dimensions='AD_UNIT_ID',
                                                     adUnitView='FLAT',
                                                     columns='AD_SERVER_CLICKS', 
                                                     dateRangeType='LAST_WEEK'
                                                     )))

# the result is a list and most importantly the ID is included for checking its status
dfp_runReportJob_result <- dfp_runReportJob(request_data)
dfp_runReportJob_result$id
#> [1] 11265209521

# to check the status repeatedly you just need to provide the id
# dfp_getReportJobStatus_result returns a character string of the reportJob status
request_data <- list(reportJobId = dfp_runReportJob_result$id)
dfp_getReportJobStatus_result <- dfp_getReportJobStatus(request_data, as_df = FALSE)
dfp_getReportJobStatus_result
#> [1] "IN_PROGRESS"

# a simple while loop can keep checking a long running request until ready
counter <- 0
while(dfp_getReportJobStatus_result != 'COMPLETED' & counter < 10){
  dfp_getReportJobStatus_result <- dfp_getReportJobStatus(request_data, as_df = FALSE)
  Sys.sleep(3)
  counter <- counter + 1
}

# once the status is "COMPLETED" the data download URL can be retrieved
request_data <- list(reportJobId=dfp_runReportJob_result$id, exportFormat='CSV_DUMP')
dfp_getReportDownloadURL_result <- dfp_getReportDownloadURL(request_data, as_df = FALSE)

# this function has been provided to download the data from URL and convert to a tbl_df
# supported exportFormats are currently c('CSV_DUMP', 'TSV', 'TSV_EXCEL')
report_data <- dfp_report_url_to_dataframe(report_url = dfp_getReportDownloadURL_result, 
                                           exportFormat = 'CSV_DUMP')
report_data[,c('Dimension.MONTH_AND_YEAR', 'Dimension.AD_UNIT_ID', 'Column.AD_SERVER_CLICKS')]
#> # A tibble: 18 x 3
#>    Dimension.MONTH_AND_YEAR Dimension.AD_UNIT_ID Column.AD_SERVER_CLICKS
#>    <chr>                                   <dbl>                   <dbl>
#>  1 2019-05                           21677451947                     936
#>  2 2019-05                           21677451950                     173
#>  3 2019-05                           21677553898                    5447
#>  4 2019-05                           21677553901                     102
#>  5 2019-05                           21677553904                    4304
#>  6 2019-05                           21677451953                    2264
#>  7 2019-05                           21677553910                      44
#>  8 2019-05                           21677553913                    2637
#>  9 2019-05                           21677554012                      69
#> 10 2019-06                           21677451947                     431
#> 11 2019-06                           21677451950                      66
#> 12 2019-06                           21677553898                    1740
#> 13 2019-06                           21677553901                      43
#> 14 2019-06                           21677553904                    1895
#> 15 2019-06                           21677451953                     865
#> 16 2019-06                           21677553910                      20
#> 17 2019-06                           21677553913                    1146
#> 18 2019-06                           21677554012                      34