报表基础知识

简介

本指南将向您介绍如何使用 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


  values = [{
      'key': 'id',
      'value': {
          'xsi_type': 'NumberValue',
          'value': saved_query_id
      }
  }]
  query = 'WHERE id = :id'
  statement = dfp.FilterStatement(query, values, 1)

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

  if 'results' in response:
    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 = DfpApi::FilterStatement.new(
      'WHERE id = :id',
      [
        {:key => 'id',
         :value => {
             :value => saved_query_id,
             :xsi_type => 'NumberValue'}
        }
      ],
      # Limit results to single entity.
      1
  )

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

  unless saved_query_page[:results].nil?
    saved_query = response[: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.
  values = [{
      'key': 'id',
      'value': {
          'xsi_type': 'NumberValue',
          'value': order_id
      }
  }]
  filter_statement = {'query': 'WHERE ORDER_ID = :id',
                      'values': values}

  # 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': filter_statement,
          '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 = DateTime.now
  report_start_date = report_end_date - 7

  # Create report query.
  report_query = {
    :date_range_type => 'CUSTOM_DATE',
    :start_date => {:year => report_start_date.year,
                    :month => report_start_date.month,
                    :day => report_start_date.day},
    :end_date => {:year => report_end_date.year,
                  :month => report_end_date.month,
                  :day => report_end_date.day},
    :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'],
    # Create statement object to filter for an order.
    :statement => {
        :query => 'WHERE ORDER_ID = :order_id',
        :values => [
            {:key => 'order_id',
             :value => {:value => order_id, :xsi_type => 'NumberValue'}}
        ]
    }
  }
    

创建 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='v201702')

  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 方法进行检索。

发送以下问题的反馈:

此网页
DFP 广告管理系统
DFP 广告管理系统
需要帮助?请访问我们的支持页面