Google DFP API ReportService is failing with no notable error message

Question

I'm trying to pull a pretty basic report from DFP via API and the PHP library provided by Google, https://github.com/googleads/googleads-php-lib/. I've tried both creating the query in the UI and fetching that ReportQuery from the API and running it (per https://developers.google.com/doubleclick-publishers/docs/reporting#retrieving_a_saved_reportquery) as well as creating an ad hoc query. All create the request okay, which seems to imply that the query validates okay. Other API requests I've tested work fine, including fetching a list of line items etc.

The pertinent code is as follows:

$statementBuilder = (new StatementBuilder())


->where('AD_UNIT_NAME like \'prebid_\'');

# Create report query.
$reportQuery = new ReportQuery();
$reportQuery->setDimensions([
  Dimension::AD_UNIT_NAME,
  Dimension::CUSTOM_CRITERIA,
  Dimension::DATE
]);
$reportQuery->setColumns([
  Column::TOTAL_INVENTORY_LEVEL_IMPRESSIONS,
  Column::TOTAL_INVENTORY_LEVEL_WITHOUT_CPD_AVERAGE_ECPM
]);
$reportQuery->setStatement($statementBuilder->toStatement());
$reportQuery->setAdUnitView(ReportQueryAdUnitView::TOP_LEVEL);
$reportQuery->setDateRangeType(DateRangeType::YESTERDAY);

# Create report job.
$reportJob = new ReportJob();
$reportJob->setReportQuery($reportQuery);

# Run report job.
$reportJob = $reportService->runReportJob($reportJob);

# Create report downloader.
$reportDownloader = new ReportDownloader($reportService, $reportJob->getId(), POLL_TIME_SECONDS);

# Wait for the report to be ready.
$reportDownloader->waitForReportToFinish();

# Change to your file location.
$filePath = sprintf('%s.csv.gz', tempnam(sys_get_temp_dir(),
    'delivery-report-'));

printf("Downloading report to %s ...\n", $filePath);

# Download the report.
$reportDownloader->downloadReport('CSV_DUMP', $filePath);

Here's the error message I get from the API:

PHP Fatal error:  Uncaught exception 'UnexpectedValueException' with message 'Cannot download report 1834923393 because it has a status of FAILED.' in ~/test/vendor/googleads/googleads-php-lib/src/Google/AdsApi/Dfp/Util/v201611/ReportDownloader.php:146
Stack trace:
#0 ~/test/vendor/googleads/googleads-php-lib/src/Google/AdsApi/Dfp/Util/v201611/ReportDownloader.php(116): Google\AdsApi\Dfp\Util\v201611\ReportDownloader->getDownloadUrl('CSV_DUMP')
#1 ~/test/dfp/get_prebid_unit_data.php(85): Google\AdsApi\Dfp\Util\v201611\ReportDownloader->downloadReport('CSV_DUMP', '/tmp/delivery-r...')
#2 {main}
  thrown in ~/test/vendor/googleads/googleads-php-lib/src/Google/AdsApi/Dfp/Util/v201611/ReportDownloader.php on line 146

Answer 1

I have done similar kind of report using below code. Hope it would be helpful for you.

<?php
ini_set('display_errors', 'On');
error_reporting('E_ALL');
$date_to = isset($_GET['dateTo']) && !empty($_GET['dateTo']) ? date('Y-m-d', strtotime(trim($_GET['dateTo']))) : date('Y-m-d', strtotime('now'));
$date_from = isset($_GET['dateFrom']) && !empty($_GET['dateFrom']) ? date('Y-m-d', strtotime(trim($_GET['dateFrom']))) : date('Y-m-d', strtotime('-1 day'));

require_once 'Google/Api/Ads/Dfp/Lib/DfpUser.php';
//require_once dirname(__FILE__) . '/../../../Common/ExampleUtils.php';
require_once 'Google/Api/Ads/Dfp/Util/ReportUtils.php';
require_once 'Google/Api/Ads/Dfp/Util/DateTimeUtils.php';

try {
    // Get DfpUser from credentials in "../auth.ini"
    // relative to the DfpUser.php file's directory.
    $user = new DfpUser();
    // Log SOAP XML request and response.
    // Get the ReportService.
    $reportService = $user->GetService('ReportService', 'v201405');
    // Create report job.
    $reportJob = new ReportJob();
    // Create report query.
    $reportQuery = new ReportQuery();
//    $reportQuery->dateRangeType = 'LAST_MONTH';
    $reportQuery->dateRangeType = 'CUSTOM_DATE';
//    $reportQuery->dateRangeType = 'YESTERDAY';
//    $reportQuery->dateRangeType = 'TODAY';
    $reportQuery->startDate = DateTimeUtils::GetDfpDateTime(new DateTime($date_from))->date;
    $reportQuery->endDate = DateTimeUtils::GetDfpDateTime(new DateTime($date_to))->date;

    //-- create PQL statement
//    $reportStatement = new Statement();
//    $reportStatement->query = "WHERE LINE_ITEM_TYPE = 'SPONSORSHIP'";
//    $reportQuery->statement = $reportStatement;
    //Date    Salesperson    Advertiser    Order    Line item    Line item type    Ad unit    Salesperson ID    Advertiser ID    
//
//Order ID    Line item ID    Ad unit ID    Total impressions    Total impressions    Total clicks    Total CTR
    $reportQuery->dimensions = array(
        'DATE', 'SALESPERSON_NAME', 'ADVERTISER_NAME','ORDER_NAME', 'LINE_ITEM_NAME', 'LINE_ITEM_TYPE', 'AD_UNIT_NAME'
    );

    $reportQuery->columns = array(
        'TOTAL_INVENTORY_LEVEL_IMPRESSIONS', 'TOTAL_LINE_ITEM_LEVEL_IMPRESSIONS', 'TOTAL_LINE_ITEM_LEVEL_CLICKS', 'TOTAL_LINE_ITEM_LEVEL_CTR', 'TOTAL_LINE_ITEM_LEVEL_CPM_AND_CPC_REVENUE', 'TOTAL_LINE_ITEM_LEVEL_WITH_CPD_AVERAGE_ECPM'
    );

    $reportJob->reportQuery = $reportQuery;
    // Run report job.
    $reportJob = $reportService->runReportJob($reportJob);

    do {
        printf("<p>Report with ID '%s' is running.", $reportJob->id);

        sleep(5);
        // Get report job.
        $reportJob = $reportService->getReportJob($reportJob->id);
    } while ($reportJob->reportJobStatus == 'IN_PROGRESS');

    if ($reportJob->reportJobStatus == 'FAILED') {
        printf("Report job with ID '%s' failed to finish successfully.</p>", $reportJob->id);
    } else {
        printf("Report job with ID '%s' completed successfully.</p>", $reportJob->id);
        $reportJobId = $reportJob->id;

        //-- Set the format of the report (e.g., CSV_DUMP) and download without compression so we can print it.
        $reportDownloadOptions = new ReportDownloadOptions();
//        $reportDownloadOptions->exportFormat = 'CSV_DUMP';
        $reportDownloadOptions->exportFormat = 'TSV';
        $reportDownloadOptions->useGzipCompression = false;

        //-- get download url of the report
        $downloadUrl = $reportService->getReportDownloadUrlWithOptions($reportJobId, $reportDownloadOptions);

        //-- get printed data of the report from the download url
        $report = ReportUtils::DownloadReport($downloadUrl);
        //-- now parse the report data and manage it to view/store it in Database
//        print $report.'<br><br>';die;
    }
} catch (OAuth2Exception $e) {
    ExampleUtils::CheckForOAuth2Errors($e);
//    echo "check";
} catch (ValidationException $e) {
    ExampleUtils::CheckForOAuth2Errors($e);
} catch (Exception $e) {
    print $e->getMessage() . "\n";
}

Answer 2

Sometimes sleeping on it makes all the difference. Notably, the same query, when fetched via ReportService::getSavedQueriesByStatement, returns the following:

Google\AdsApi\Dfp\v201611\SavedQuery {#52
  #id: 468197553
  #name: "Prebid KV Query"
  #reportQuery: Google\AdsApi\Dfp\v201611\ReportQuery {#49
    #dimensions: array:3 [
      0 => "AD_UNIT_NAME"
      1 => "CUSTOM_CRITERIA"
      2 => "DATE"
    ]
    #adUnitView: "TOP_LEVEL"
    #columns: array:2 [
      0 => "TOTAL_INVENTORY_LEVEL_IMPRESSIONS"
      1 => "TOTAL_INVENTORY_LEVEL_WITHOUT_CPD_AVERAGE_ECPM"
    ]
    #dimensionAttributes: null
    #customFieldIds: null
    #contentMetadataKeyHierarchyCustomTargetingKeyIds: null
    #startDate: null
    #endDate: null
    #dateRangeType: "YESTERDAY"
    #statement: Google\AdsApi\Dfp\v201611\Statement {#48
      #query: " where ad_unit_name like 'prebid_'"
      #values: null
    }
    #useSalesLocalTimeZone: false
    #includeZeroSalesRows: false
  }
  #isCompatibleWithApiVersion: true
}

Note the lack of % in the query.

However, when generating the report, % are required in the LIKE statement in order to properly filter.

The interesting thing here is that even pulling the saved query and generating a ReportJob from that without modification fails, which seems to imply that you cannot programmatically run a saved query that contains a LIKE filter?