报表基础知识

简介

本指南将向您介绍如何使用 API 生成和下载报表,其中涵盖了如何使用现有的已保存报表查询和创建临时报表查询。

前提条件

  • 对正式版 DFP 广告管理系统(以下简称 DFP)广告联盟的访问权限
  • 一个 DFP 客户端库

基础入门

如果您对 DFP 中的报表不熟悉,请参阅这篇帮助中心文章,了解如何在 DFP 界面中生成报表。此外,界面中提供了对报表输出结果的预览以及用来说明系统支持哪些列/维度组合的提示。创建复杂报表查询时,您可以先在界面中进行创建,然后再使用 API 检索该查询,这样会轻松很多。

检索已保存的 ReportQuery

ReportQuery 对象包含报表的所有细节。您可以在 DFP 界面中创建报表查询,然后使用 ReportService.getSavedQueriesByStatement 方法进行检索。在界面中查看查询时,已保存的查询 ID 会包含在相应网址中。例如,在网址 https://www.google.com/dfp/1234#reports/query/qid=456789 中,查询 ID 为 456789

如果查询与您的 API 版本不兼容,则 SavedQuery.reportQuery 将为 nullSavedQuery.isCompatibleWithApiVersion 将为 false

无论是否做出修改,您都可以运行已保存的兼容查询。

Java


  StatementBuilder statementBuilder = new StatementBuilder()
      .where("id = :id")
      .orderBy("id ASC")
      .limit(1)
      .withBindVariableValue("id", savedQueryId);

  SavedQueryPage page = reportService.getSavedQueriesByStatement(statementBuilder.toStatement());
  SavedQuery savedQuery = Iterables.getOnlyElement(Arrays.asList(page.getResults()));

  if (!savedQuery.getIsCompatibleWithApiVersion()) {
    throw new IllegalStateException("The saved query is not compatible with this API version.");
  }

  ReportQuery reportQuery = savedQuery.getReportQuery();
    

Python


  statement = (dfp.StatementBuilder()
               .Where('id = :id')
               .WithBindVariable('id', long(saved_query_id))
               .Limit(1))

  response = report_service.getSavedQueriesByStatement(
      statement.ToStatement())

  if 'results' in response and len(response['results']):
    saved_query = response['results'][0]

    if saved_query['isCompatibleWithApiVersion']:
      report_job = {}

      # Set report query and optionally modify it.
      report_job['reportQuery'] = saved_query['reportQuery']
    

PHP


      $statementBuilder = (new StatementBuilder())->where('id = :id')
          ->orderBy('id ASC')
          ->limit(1)
          ->withBindVariableValue('id', $savedQueryId);

      $savedQueryPage = $reportService->getSavedQueriesByStatement(
          $statementBuilder->toStatement()
      );
      $savedQuery = $savedQueryPage->getResults()[0];

      if ($savedQuery->getIsCompatibleWithApiVersion() === false) {
          throw new UnexpectedValueException(
              'The saved query is not compatible with this API version.'
          );
      }

      $reportQuery = $savedQuery->getReportQuery();
    

C#


    StatementBuilder statementBuilder = new StatementBuilder()
        .Where("id = :id")
        .OrderBy("id ASC")
        .Limit(1)
        .AddValue("id", savedQueryId);

    SavedQueryPage page =
        reportService.getSavedQueriesByStatement(statementBuilder.ToStatement());
    SavedQuery savedQuery = page.results[0];

    if (!savedQuery.isCompatibleWithApiVersion) {
      throw new InvalidOperationException("Saved query is not compatible with this " +
          "API version");
    }

    // Optionally modify the query.
    ReportQuery reportQuery = savedQuery.reportQuery;
    

Ruby


  statement = dfp.new_statement_builder do |sb|
    sb.where = 'id = :saved_query_id'
    sb.with_bind_variable('saved_query_id', saved_query_id)
  end

  saved_query_page = report_service.get_saved_queries_by_statement(
      statement.to_statement()
  )

  unless saved_query_page[:results].nil?
    saved_query = saved_query_page[:results].first

    if saved_query[:is_compatible_with_api_version]
      # Create report job.
      report_job = {:report_query => saved_query[:report_query]}
    else
      raise StandardError, 'Report query is not compatible with the API'
    end
    

要运行查询,请参阅创建 ReportJob

构建 ReportQuery

除了使用已保存的查询外,您还可以创建一个临时 ReportQuery。为此,您必须设置报表的维度维度属性、过滤条件和日期范围。下面的示例适用于针对单个订单的基本投放报表。

Java


  // Create report query.
  ReportQuery reportQuery = new ReportQuery();
  reportQuery.setDimensions(new Dimension[] {Dimension.DATE, Dimension.ORDER_ID});
  reportQuery.setColumns(new Column[] {Column.AD_SERVER_IMPRESSIONS,
      Column.AD_SERVER_CLICKS, Column.AD_SERVER_CTR,
      Column.AD_SERVER_CPM_AND_CPC_REVENUE});
  reportQuery.setDimensionAttributes(new DimensionAttribute[] {
      DimensionAttribute.ORDER_TRAFFICKER, DimensionAttribute.ORDER_START_DATE_TIME,
      DimensionAttribute.ORDER_END_DATE_TIME});

  // Create statement to filter for an order.
  StatementBuilder statementBuilder = new StatementBuilder()
      .where("ORDER_ID = :orderId")
      .withBindVariableValue("orderId", orderId);

  // Set the filter statement.
  reportQuery.setStatement(statementBuilder.toStatement());

  // Set the start and end dates or choose a dynamic date range type.
  reportQuery.setDateRangeType(DateRangeType.CUSTOM_DATE);
  reportQuery.setStartDate(
      DateTimes.toDateTime("2013-05-01T00:00:00", "America/New_York").getDate());
  reportQuery.setEndDate(
      DateTimes.toDateTime("2013-05-31T00:00:00", "America/New_York").getDate());
    

Python


  # Create statement object to filter for an order.
  statement = (dfp.StatementBuilder()
               .Where('ORDER_ID = :id')
               .WithBindVariable('id', long(order_id))
               .Limit(None)  # No limit or offset for reports
               .Offset(None))

  # Set the start and end dates of the report to run (past 8 days).
  end_date = datetime.now().date()
  start_date = end_date - timedelta(days=8)

  # Create report job.
  report_job = {
      'reportQuery': {
          'dimensions': ['ORDER_ID', 'ORDER_NAME'],
          'dimensionAttributes': ['ORDER_TRAFFICKER', 'ORDER_START_DATE_TIME',
                                  'ORDER_END_DATE_TIME'],
          'statement': statement.ToStatement(),
          'columns': ['AD_SERVER_IMPRESSIONS', 'AD_SERVER_CLICKS',
                      'AD_SERVER_CTR', 'AD_SERVER_CPM_AND_CPC_REVENUE',
                      'AD_SERVER_WITHOUT_CPD_AVERAGE_ECPM'],
          'dateRangeType': 'CUSTOM_DATE',
          'startDate': start_date,
          'endDate': end_date
      }
  }
    

PHP


      // Create report query.
      $reportQuery = new ReportQuery();
      $reportQuery->setDimensions(
          [
              Dimension::ORDER_ID,
              Dimension::ORDER_NAME
          ]
      );
      $reportQuery->setDimensionAttributes(
          [
              DimensionAttribute::ORDER_TRAFFICKER,
              DimensionAttribute::ORDER_START_DATE_TIME,
              DimensionAttribute::ORDER_END_DATE_TIME
          ]
      );
      $reportQuery->setColumns(
          [
              Column::AD_SERVER_IMPRESSIONS,
              Column::AD_SERVER_CLICKS,
              Column::AD_SERVER_CTR,
              Column::AD_SERVER_CPM_AND_CPC_REVENUE,
              Column::AD_SERVER_WITHOUT_CPD_AVERAGE_ECPM
          ]
      );

      // Create statement to filter for an order.
      $statementBuilder = (new StatementBuilder())->where('ORDER_ID = :orderId')
          ->withBindVariableValue(
              'orderId',
              $orderId
          );

      // Set the filter statement.
      $reportQuery->setStatement($statementBuilder->toStatement());

      // Set the start and end dates or choose a dynamic date range type.
      $reportQuery->setDateRangeType(DateRangeType::CUSTOM_DATE);
      $reportQuery->setStartDate(
          DfpDateTimes::fromDateTime(
              new DateTime(
                  '-10 days',
                  new DateTimeZone('America/New_York')
              )
          )
              ->getDate()
      );
      $reportQuery->setEndDate(
          DfpDateTimes::fromDateTime(
              new DateTime(
                  'now',
                  new DateTimeZone('America/New_York')
              )
          )
              ->getDate()
      );
    

C#


    // Create report job.
    ReportJob reportJob = new ReportJob();
    reportJob.reportQuery = new ReportQuery();
    reportJob.reportQuery.dimensions = new Dimension[] {
      Dimension.ORDER_ID,
      Dimension.ORDER_NAME
    };
    reportJob.reportQuery.dimensionAttributes = new DimensionAttribute[] {
      DimensionAttribute.ORDER_TRAFFICKER, DimensionAttribute.ORDER_START_DATE_TIME,
      DimensionAttribute.ORDER_END_DATE_TIME};
    reportJob.reportQuery.columns = new Column[] {Column.AD_SERVER_IMPRESSIONS,
      Column.AD_SERVER_CLICKS, Column.AD_SERVER_CTR, Column.AD_SERVER_CPM_AND_CPC_REVENUE,
      Column.AD_SERVER_WITHOUT_CPD_AVERAGE_ECPM};

    // Set a custom date range for the last 8 days
    reportJob.reportQuery.dateRangeType = DateRangeType.CUSTOM_DATE;
    System.DateTime endDateTime = System.DateTime.Now;
    reportJob.reportQuery.startDate =
      DateTimeUtilities.FromDateTime(endDateTime.AddDays(-8), "America/New_York").date;
    reportJob.reportQuery.endDate =
      DateTimeUtilities.FromDateTime(endDateTime, "America/New_York").date;

    // Create statement object to filter for an order.
    StatementBuilder statementBuilder = new StatementBuilder()
        .Where("ORDER_ID = :id")
        .AddValue("id", orderId);
    reportJob.reportQuery.statement = statementBuilder.ToStatement();
    

Ruby


  # Specify a report to run for the last 7 days.
  report_end_date = dfp.today()
  report_start_date = report_end_date - 7

  # Create statement object to filter for an order.
  statement = dfp.new_report_statement_builder do |sb|
    sb.where = 'ORDER_ID = :order_id'
    sb.with_bind_variable('order_id', order_id)
  end

  # Create report query.
  report_query = {
    :date_range_type => 'CUSTOM_DATE',
    :start_date => report_start_date.to_h,
    :end_date => report_end_date.to_h,
    :dimensions => ['ORDER_ID', 'ORDER_NAME'],
    :dimension_attributes => ['ORDER_TRAFFICKER', 'ORDER_START_DATE_TIME',
        'ORDER_END_DATE_TIME'],
    :columns => ['AD_SERVER_IMPRESSIONS', 'AD_SERVER_CLICKS', 'AD_SERVER_CTR',
        'AD_SERVER_CPM_AND_CPC_REVENUE', 'AD_SERVER_WITHOUT_CPD_AVERAGE_ECPM'],
    :statement => statement.to_statement()
  }
    

创建 ReportJob

构建 ReportQuery 后,您就可以生成报表了。ReportJob 对象会显示报表的状态,以便让您知道何时可以下载报表。要开始生成报表,请使用 ReportService.runReportJob 方法。

Java


  // Create report job.
  ReportJob reportJob = new ReportJob();
  reportJob.setReportQuery(reportQuery);

  // Run report job.
  reportJob = reportService.runReportJob(reportJob);
    

Python


  # Initialize a DataDownloader.
  report_downloader = client.GetDataDownloader(version='v201805')

  try:
    # Run the report and wait for it to finish.
    report_job_id = report_downloader.WaitForReport(report_job)
  except errors.DfpReportError, e:
    print 'Failed to generate report. Error was: %s' % e
    

PHP


      // Create report job and start it.
      $reportJob = new ReportJob();
      $reportJob->setReportQuery($reportQuery);
      $reportJob = $reportService->runReportJob($reportJob);
    

C#


    // Run report job.
    reportJob = reportService.runReportJob(reportJob);
    

Ruby


  # Create report job.
  report_job = {:report_query => report_query}

  # Run report job.
  report_job = report_service.run_report_job(report_job);
    

下载报表

启动报表任务后,服务器会为该任务设置一个 ID。您可以将此 ID 与 ReportService.getReportJobStatus 方法结合使用,以检查报表的状态。当状态显示为 ReportJobStatus.COMPLETED 时,即表示该报表已可供下载。

我们的某些客户端库含有可对 API 进行轮询并等待报表完成的辅助实用工具。报表完成后,您可以使用 ReportService.getReportDownloadURL 方法获取下载网址。您可以采用不同的格式下载报表。如果您想对报表进行进一步的机器处理,则应使用 CSV_DUMP 格式。

Java


  // Create report downloader.
  ReportDownloader reportDownloader = new ReportDownloader(reportService, reportJob.getId());

  // Wait for the report to be ready.
  reportDownloader.waitForReportReady();

  // Change to your file location.
  File file = File.createTempFile("delivery-report-", ".csv.gz");

  System.out.printf("Downloading report to %s ...", file.toString());

  // Download the report.
  ReportDownloadOptions options = new ReportDownloadOptions();
  options.setExportFormat(ExportFormat.CSV_DUMP);
  options.setUseGzipCompression(true);
  URL url = reportDownloader.getDownloadUrl(options);
  Resources.asByteSource(url).copyTo(Files.asByteSink(file));

  System.out.println("done.");
    

Python


  # Change to your preferred export format.
  export_format = 'CSV_DUMP'

  report_file = tempfile.NamedTemporaryFile(suffix='.csv.gz', delete=False)

  # Download report data.
  report_downloader.DownloadReportToFile(
      report_job_id, export_format, report_file)

  report_file.close()

  # Display results.
  print 'Report job with id "%s" downloaded to:\n%s' % (
      report_job_id, report_file.name)
    

PHP


      // Create report downloader to poll report's status and download when ready.
      $reportDownloader = new ReportDownloader($reportService, $reportJob->getId());
      if ($reportDownloader->waitForReportToFinish()) {
          // Write to system temp directory by default.
          $filePath = sprintf(
              '%s.csv.gz',
              tempnam(sys_get_temp_dir(), 'delivery-report-')
          );
          printf("Downloading report to %s ...\n", $filePath);
          // Download the report.
          $reportDownloader->downloadReport(ExportFormat::CSV_DUMP, $filePath);
          print "done.\n";
      } else {
          print "Report failed.\n";
      }
    

C#


    ReportUtilities reportUtilities = new ReportUtilities(reportService, reportJob.id);

    // Set download options.
    ReportDownloadOptions options = new ReportDownloadOptions();
    options.exportFormat = ExportFormat.CSV_DUMP;
    options.useGzipCompression = true;
    reportUtilities.reportDownloadOptions = options;

    // Download the report.
    using (ReportResponse reportResponse = reportUtilities.GetResponse()) {
      reportResponse.Save(filePath);
    }
    Console.WriteLine("Report saved to \"{0}\".", filePath);
    

Ruby


  MAX_RETRIES.times do |retry_count|
    # Get the report job status.
    report_job_status = report_service.get_report_job_status(report_job[:id])

    break unless report_job_status == 'IN_PROGRESS'
    puts 'Report with ID %d is still running.' % report_job[:id]
    sleep(RETRY_INTERVAL)
  end

  puts 'Report job with ID %d finished with status "%s".' % [report_job[:id],
      report_service.get_report_job_status(report_job[:id])]

  # Get the report URL.
  download_url = report_service.get_report_download_url(
      report_job_id, export_format
  )

  puts 'Downloading "%s" to "%s"...' % [download_url, file_name]
  open(file_name, 'wb') do |local_file|
    local_file << open(download_url).read()
  end
    

读取报表数据

我们的很多客户端库都包含用于读取报表数据的实用工具。在对报表数据进行进一步处理,或合并不同日期范围的报表时,这些工具会非常有用。请注意,示例代码假设相关文件未经压缩。

Java


  List rows = CsvFiles.getCsvDataArray(filePath, true);
  for (String[] row : rows) {
    // Additional row processing
    processReportRow(row);
  }
    

Python


  with open(report_file.name, 'rb') as report:
    report_reader = csv.reader(report)
    for row in report_reader:
      # Additional row processing
      process_row(row)
    

PHP


  $report = fopen($filePath, 'r');
  while (!feof($report)) {
    // Additional row processing
    processRow(fgetcsv($report));
  }
  fclose($report);
    

C#


  CsvFile file = new CsvFile();
  file.Read(fileName, true);
  for (String[] row : file.Records) {
    // Additional row processing
    ProcessReportRow(row);
  }
    

Ruby


    CSV.foreach(file_name, converters: :numeric, headers: true) do |row|
      # Additional row processing
      process_row(row)
    end
    

如需更多报表示例,请查看我们在 github 上的客户端库

常见问题解答

为什么我在测试广告联盟中的所有报表均为空白?

测试广告联盟不会投放广告,因此投放报表中不会显示任何数据。

我的报表为什么会出现 ReportError.COLUMNS_NOT_SUPPORTED_FOR_REQUESTED_DIMENSIONS 错误?

DFP 并不支持所有的列和维度组合。对于复杂报表,您可以先在界面中构建一个有效查询,然后使用 ReportService.getSavedQueriesByStatement 方法进行检索,这样会轻松很多。

为什么我的已保存查询与 API 不兼容?

某些报表功能无法在 API 中使用,其中包括列、维度属性、维度和日期范围类型。对于不兼容的日期范围类型,您可以先使用受支持的类型保存查询,以使其可供检索,然后对查询进行更改,使其日期范围与所需的固定日期范围相吻合。

为什么整个有效期内的点击次数/展示次数与我在界面中的报表不一致?

整个有效期内的展示次数是针对相应订单项的整个有效期而言的,与报表的日期范围无关。如果某个订单项仍在投放,则在任意两次报表生成操作相隔的时段内,该值可能会发生变化。

报表的生成时间过长,并且偶尔还会出现超时问题。该怎么办?

缩小日期范围或减少维度数量将有助于改善这种情况。不妨尝试针对较小的日期范围生成多个报表。然后,合并报表数据来涵盖所需的日期范围。

INVENTORY_LEVEL 列和 LINE_ITEM_LEVEL 列之间有何区别?我应该使用哪种列?

仅当您在广告联盟中启用了订单项级动态分配时才可使用 LINE_ITEM_LEVEL 相关列。此类列包含针对 AdSense 和 Ad Exchange 的订单项级动态分配状况的数据。同样,INVENTORY_LEVEL 相关列包含广告资源级动态分配的数据。要详细了解动态分配,请参阅这篇帮助中心文章

如果您仍然不确定要使用哪些 API 列,请在 DFP 界面中创建一个已保存的查询,并使用 ReportService.getSavedQueriesByStatement 方法进行检索。