简介
本指南将向您介绍如何使用 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 将为 null
,SavedQuery.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
Listrows = 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 方法进行检索。