Generating report from either saved query or via the report API not working

[Rasmus Gadensgaard]

Hello

I'm trying to create a python script to download a report from DFP. I am able to create and download the report from the DFP UI, but not through the API. 

I used the examples in api v201705.

When im running the saved query through the api I get 0 results. The code looks like this:

...

SAVED_QUERY_ID = '10000118344'

def main(client, saved_query_id):

  # Initialize appropriate service.

  report_service = client.GetService('ReportService', version='v201705')

  # Initialize a DataDownloader.

  report_downloader = client.GetDataDownloader(version='v201705')

  # Create statement object to filter for an order.

  values = [{

      'key': 'id',

      'value': {

          'xsi_type': 'NumberValue',

          'value': saved_query_id

      }

  }]

  query = ''

  statement = dfp.FilterStatement(query, values)

  response = report_service.getSavedQueriesByStatement(

      statement.ToStatement())

      

  print (response)
....

And prints the following: 

(SavedQueryPage){

   totalResultSetSize = 0

   startIndex = 0

 }

.

So since I was not able to get this working, I tried to make tha API generate the entire report. I was able to generate some of the report, however when I add some fields such as AD_UNIT_ID, the script fails with the error:

Failed to generate report. Error was: DFP report job failed. The ID of the failed report is: 10004293710

Traceback (most recent call last):

  File "run_reach_report.py", line 75, in <module>

    main(dfp_client)

  File "run_reach_report.py", line 57, in main

    report_job_id, export_format, report_file)

UnboundLocalError: local variable 'report_job_id' referenced before assignment

I need the report to have the following data:

Dimensions

 Date

 Ad unit

 Device category

 Aggregated demand channel

 Line item type

 Line Item

Metrics

 Total impressions

 Total clicks

 Total CPM, CPC, CPD, and vCPM revenue

 Total Active View measurable impressions

 Total Active View viewable impressions

This script looks like this:

def main(client):

  # Initialize a DataDownloader.

  report_downloader = client.GetDataDownloader(version='v201705')

  # Create report job.

  report_job = {

      'reportQuery': {

          'dimensions': ['DATE', 'CREATIVE_NAME', 'PLACEMENT_NAME', 'DEVICE_CATEGORY_NAME', 'AGGREGATED_DEMAND_CHANNEL', 'LINE_ITEM_TYPE'],

          'columns': ['TOTAL_LINE_ITEM_LEVEL_IMPRESSIONS', 'TOTAL_LINE_ITEM_LEVEL_CLICKS', 'TOTAL_LINE_ITEM_LEVEL_ALL_REVENUE', 'TOTAL_ACTIVE_VIEW_MEASURABLE_IMPRESSIONS', 'TOTAL_ACTIVE_VIEW_VIEWABLE_IMPRESSIONS'],

          'dateRangeType': 'YESTERDAY'

      }

  }

#'dimensions': ['DATE', 'PLACEMENT_NAME', 'DEVICE_CATEGORY_NAME', 'AGGREGATED_DEMAND_CHANNEL', 'LINE_ITEM_TYPE'],

  try:

    # Run the report and wait for it to finish.

    report_job_id = report_downloader.WaitForReport(report_job)

  except errors.DfpReportError as e:

    print('Failed to generate report. Error was: %s' % e)

[Vincent Racaza (DFP API Team)]

Hi Rasmus,

For your saved query issue, there seems to be a problem on your query filter as your filter is empty (query = ''). In the run saved query example, there is a query filter on this line of code. Please correct this part of your code.

For your manual API report, this seems to be an issue on your code as the error message says "local variable 'report_job_id' referenced before assignment". Please refer to a manual API report example here. I have also tried this report (with your complete report parameters and AD_UNIT_ID) in my test network, and it worked successfully. If this issue still persists on your network, please provide your network code so I could further investigate this.

Thanks,

Vincent Racaza, DFP API Team

[Rasmus Gadensgaard]

Hello Vincent

Thanks for taking the time to look at this. 

My scripts are based on the two samples "run_reach_report.py" and "run_saved_query.py"

The problem with the saved query script is that I do now want to have a filter on the query. I want to run the saved query and save the result as a file, not have a filter.

For the manual API report problem, when I use the run_delivery_report.py example script, i get the same error. The only thing i modified from the example was to remove the filter in the report_job, and correct the syntax to work with python 3.6 

My network code is 56257416

full script of "run_delivery_report.py":

#!/usr/bin/env python

#

# Copyright 2015 Google Inc. All Rights Reserved.

#

# Licensed under the Apache License, Version 2.0 (the "License");

# you may not use this file except in compliance with the License.

# You may obtain a copy of the License at

#

#      http://www.apache.org/licenses/LICENSE-2.0

#

# Unless required by applicable law or agreed to in writing, software

# distributed under the License is distributed on an "AS IS" BASIS,

# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

# See the License for the specific language governing permissions and

# limitations under the License.

"""Runs a report similar to the "Orders report" on the DFP website.

Includes additional attributes and can filter to include just one order.

"""

from datetime import datetime

from datetime import timedelta

import tempfile

# Import appropriate modules from the client library.

from googleads import dfp

from googleads import errors

ORDER_ID = 'INSERT_ORDER_ID_HERE'

def main(client, order_id):

  # 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'],

          '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

      }

  }

  # Initialize a DataDownloader.

  report_downloader = client.GetDataDownloader(version='v201705')

  try:

    # Run the report and wait for it to finish.

    report_job_id = report_downloader.WaitForReport(report_job)

  except errors.DfpReportError as e:

    print ('Failed to generate report. Error was: %s' % e)

  # 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))

if __name__ == '__main__':

  # Initialize client object.

  dfp_client = dfp.DfpClient.LoadFromStorage()

  main(dfp_client, ORDER_ID)

[Vincent Racaza (DFP API Team)]

Hi Rasmus,

For the saved query issue, the filter (WHERE id = :id) is needed as the id in the filter is the saved query id which is a required field for downloading the query into a file.

For the manual API report, I was not able to replicate your issue as I was able to download this report in your network (56257416) using Java. In your original post, you said that you were able to run some reports, but then, when you add the AD_UNIT_ID, the error was generated. Could you confirm if you were able to successfully run some reports in your network?

It is also weird that the last report configuration that you sent does not include AD_UNIT_ID but it generated the same error. Could you just provide to me your customized run_reach_report.py so I could check on it?

[Rasmus Gadensgaard]

Hello Vincent

When I have the Filter in the saved query example, the script just returns the following when i print response: 

(SavedQueryPage){

   totalResultSetSize = 0

   startIndex = 0

 }

.

For the manual API report, i can for example run the following:

  report_job = {

      'reportQuery': {

          'dimensions': ['DATE', 'PLACEMENT_NAME', 'DEVICE_CATEGORY_NAME', 'AGGREGATED_DEMAND_CHANNEL', 'LINE_ITEM_TYPE'],

          'columns': ['TOTAL_LINE_ITEM_LEVEL_IMPRESSIONS', 'TOTAL_LINE_ITEM_LEVEL_CLICKS', 'TOTAL_LINE_ITEM_LEVEL_ALL_REVENUE', 'TOTAL_ACTIVE_VIEW_MEASURABLE_IMPRESSIONS', 'TOTAL_ACTIVE_VIEW_VIEWABLE_IMPRESSIONS'],

          'dateRangeType': 'YESTERDAY'

      }

  }

My entire run_reach_report.py looks like this:

#!/usr/bin/env python

#

# Copyright 2015 Google Inc. All Rights Reserved.

#

# Licensed under the Apache License, Version 2.0 (the "License");

# you may not use this file except in compliance with the License.

# You may obtain a copy of the License at

#

#      http://www.apache.org/licenses/LICENSE-2.0

#

# Unless required by applicable law or agreed to in writing, software

# distributed under the License is distributed on an "AS IS" BASIS,

# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

# See the License for the specific language governing permissions and

# limitations under the License.

"""This code example runs a reach report."""

import os

import time

import tempfile

import gzip

# Import appropriate modules from the client library.

from googleads import dfp

from googleads import errors

def main(client):

  # Initialize a DataDownloader.

  report_downloader = client.GetDataDownloader(version='v201705')

  # Create report job.

  report_job = {

      'reportQuery': {

          'dimensions': ['DATE', 'PLACEMENT_NAME', 'DEVICE_CATEGORY_NAME', 'AGGREGATED_DEMAND_CHANNEL', 'LINE_ITEM_TYPE'],

          'columns': ['TOTAL_LINE_ITEM_LEVEL_IMPRESSIONS', 'TOTAL_LINE_ITEM_LEVEL_CLICKS', 'TOTAL_LINE_ITEM_LEVEL_ALL_REVENUE', 'TOTAL_ACTIVE_VIEW_MEASURABLE_IMPRESSIONS', 'TOTAL_ACTIVE_VIEW_VIEWABLE_IMPRESSIONS'],

          'dateRangeType': 'YESTERDAY'

      }

  }

  try:

    # Run the report and wait for it to finish.

    report_job_id = report_downloader.WaitForReport(report_job)

  except errors.DfpReportError as e:

    print('Failed to generate report. Error was: %s' % e)

  # Change to your preferred export format.

  export_format = 'XLSX'

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

  # Download report data.

  report_downloader.DownloadReportToFile(

      report_job_id, export_format, report_file)

  report_file.close()

  destination_file_name = "C:/GoogleServices/dfp/dfp_" + str(int(round(time.time()))) +".xlsx.gz"

  os.rename(report_file.name, destination_file_name)

  

  with gzip.GzipFile(destination_file_name, 'rb') as inF:

  

    with open(destination_file_name[:-3], 'wb') as outF:

        s = inF.read()

        outF.write(s)

  os.remove(destination_file_name)

if __name__ == '__main__':

  # Initialize client object.

  dfp_client = dfp.DfpClient.LoadFromStorage()

  main(dfp_client)

If I change the above to include AD_UNIT_ID as dimension, i get the error: 

Failed to generate report. Error was: DFP report job failed. The ID of the failed report is: 10004325334

Traceback (most recent call last):

  File "run_reach_report.py", line 74, in <module>

    main(dfp_client)

  File "run_reach_report.py", line 56, in main

    report_job_id, export_format, report_file)

UnboundLocalError: local variable 'report_job_id' referenced before assignment

To me it seems that the UnboundLocalError simply happens because of the "DFP Report job failed" error.

Best regards

Rasmus

[Vincent Racaza (DFP API Team)]

Hi Rasmus,

Thanks for the confirmation.

For the saved query issue, since you already have a filter, and then the totalResultSize is still 0, then, there is a possibility that your saved query ID is not existing in your network, or the API user (that you used in your authentication) that is accessing the saved query id has no access to the query. Please make sure that the API user is added on the "Users able to edit" permission on the UI query. Please see attached screenshot on this. If indeed the saved query ID is existing and the API user is added to the "Users able to edit" permission, then kindly provide UI screenshots on this.

For the manual report, I really could not replicate this issue as I was able to download this report (with AD_UNIT_ID dimension) in your network (56257416).

On these two issues, could you provide the email address of the API user you are authenticating?

You may use the Reply privately to author option when replying back to me.