RE: Private message regarding: Server Error when trying to build report

Ad Manager API Forum Advisor Prod

unread,

Feb 11, 2020, 12:05:57 PM2/11/20

to google-doubleclick...@googlegroups.com

Hi Mario,

Thanks for providing the details. Upon checking further, I see that the columns (IMPRESSIONS, CLICKS, CTR) from the SOAP logs are not available in the API columns. You can find that these are the only available API columns and you need to specify the ones from here corresponding to the UI metrics. Please give this a try and let me know if you still face issues.

Thanks,
Sravani Yelamarthi, Ad Manager API Team

ref:_00D1U1174p._5001UV0Nc0:ref

Mario Muniz

unread,

Feb 13, 2020, 7:33:26 PM2/13/20

to Google Ad Manager API Forum

I am still getting a server error even when using columns that are available in the API columns.

INFO:googleads.soap:Request made: Service: "ReportService" Method: "runReportJob" URL: "https://ads.google.com/apis/ads/publisher/v201911/ReportService"
DEBUG:googleads.soap:Outgoing request: {'SOAPAction': '""', 'Content-Type': 'text/xml; charset=utf-8', 'authorization': 'REDACTED'}
<soap-env:Envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/">
  <soap-env:Header>
    <ns0:RequestHeader xmlns:ns0="https://www.google.com/apis/ads/publisher/v201911">
      <ns0:networkCode>21611127761</ns0:networkCode>
      <ns0:applicationName>cc3_admanager_report_bot (DfpApi-Python, googleads/22.0.0, Python/3.6.8, StatementBuilder, zeep)</ns0:applicationName>
    </ns0:RequestHeader>
  </soap-env:Header>
  <soap-env:Body>
    <ns0:runReportJob xmlns:ns0="https://www.google.com/apis/ads/publisher/v201911">
      <ns0:reportJob>
        <ns0:reportQuery>
          <ns0:dimensions>AD_UNIT_NAME</ns0:dimensions>
          <ns0:columns>TOTAL_LINE_ITEM_LEVEL_IMPRESSIONS</ns0:columns>
          <ns0:columns>TOTAL_LINE_ITEM_LEVEL_CTR</ns0:columns>
          <ns0:columns>TOTAL_LINE_ITEM_LEVEL_CLICKS</ns0:columns>
          <ns0:startDate>
            <ns0:year>2020</ns0:year>
            <ns0:month>1</ns0:month>
            <ns0:day>1</ns0:day>
          </ns0:startDate>
          <ns0:endDate>
            <ns0:year>2020</ns0:year>
            <ns0:month>1</ns0:month>
            <ns0:day>31</ns0:day>
          </ns0:endDate>
          <ns0:dateRangeType>CUSTOM_DATE</ns0:dateRangeType>
          <ns0:statement>
            <ns0:query>WHERE CREATIVE_NAME LIKE ':company%' AND ORDER_ID IN (:order_ids)</ns0:query>
            <ns0:values>
              <ns0:key>company</ns0:key>
              <ns0:value xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns0:TextValue">
                <ns0:value>Lux</ns0:value>
              </ns0:value>
            </ns0:values>
            <ns0:values>
              <ns0:key>order_ids</ns0:key>
              <ns0:value xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns0:SetValue">
                <ns0:values xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns0:TextValue">
                  <ns0:value>2285977422</ns0:value>
                </ns0:values>
                <ns0:values xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns0:TextValue">
                  <ns0:value>2521762987</ns0:value>
                </ns0:values>
                <ns0:values xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns0:TextValue">
                  <ns0:value>2521772548</ns0:value>
                </ns0:values>
              </ns0:value>
            </ns0:values>
          </ns0:statement>
        </ns0:reportQuery>
      </ns0:reportJob>
    </ns0:runReportJob>
  </soap-env:Body>
</soap-env:Envelope>

DEBUG:googleads.soap:Incoming response: 
b'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">\n  <soap:Header>\n    <ResponseHeader xmlns="https://www.google.com/apis/ads/publisher/v201911">\n      <requestId>5e2e7200ba224c2a364acca1ed6f3394</requestId>\n      <responseTime>252</responseTime>\n    </ResponseHeader>\n  </soap:Header>\n  <soap:Body>\n    <soap:Fault>\n      <faultcode>soap:Server</faultcode>\n      <faultstring>[ServerError.SERVER_ERROR @ ]</faultstring>\n      <detail>\n        <ApiExceptionFault xmlns="https://www.google.com/apis/ads/publisher/v201911">\n          <message>[ServerError.SERVER_ERROR @ ]</message>\n          <errors xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ServerError">\n            <fieldPath/>\n            <trigger/>\n            <errorString>ServerError.SERVER_ERROR</errorString>\n            <reason>SERVER_ERROR</reason>\n          </errors>\n        </ApiExceptionFault>\n      </detail>\n    </soap:Fault>\n  </soap:Body>\n</soap:Envelope>\n'
WARNING:googleads.soap:Error summary: {'faultMessage': '[ServerError.SERVER_ERROR @ ]', 'requestId': '5e2e7200ba224c2a364acca1ed6f3394', 'responseTime': '252', 'serviceName': 'ReportService', 'methodName': 'runReportJob'}

Ad Manager API Forum Advisor Prod

unread,

Feb 14, 2020, 2:02:47 AM2/14/20

to ma...@covalentcareers.com, google-doubleclick...@googlegroups.com

Hi Mario,

Thanks for providing your SOAP logs. I will need more time to investigate this and reach out to you as soon as I have more information.

Thanks,

Kevin Soriano, Ad Manager API Team

ref:_00D1U1174p._5001UV0Nc0:ref

Mario Muniz

unread,

Mar 9, 2020, 2:55:51 PM3/9/20

to Google Ad Manager API Forum

Any updates on this?

Ad Manager API Forum Advisor Prod

unread,

Mar 9, 2020, 3:57:53 PM3/9/20

to mobile...@aetn.com, google-doubleclick...@googlegroups.com

Hi Mario,

Apologies for the delay as it looks like you did not receive Kevin's previous response:

Upon testing, there appears to be some issues with your filter statement. I've noticed that enclosing the binded variable in '' (e.g. CREATIVE_NAME LIKE ':company%') doesn't make use of the actual variable and it looks for values that matches string enclosed instead. Also, it seems that the API doesn't accept your second condition. Upon checking, I wasn't able to find a possible way to bind multiple IDs in a single bind variable key.

I would recommend to use something like "<query>WHERE CREATIVE_NAME LIKE :company AND ORDER_ID IN (2285977422, 2521762987, 2521772548)</query>" instead.

Please try applying my colleague's suggestions and let us know if you still run into any issues.

Thanks,
Danica Calusin, Ad Manager API Team

ref:_00D1U1174p._5001UV0Nc0:ref

Mario Muniz

unread,

Mar 11, 2020, 2:27:39 PM3/11/20

to Google Ad Manager API Forum

Thanks, that seems to have gotten me one step further.

However, now my report is failing. "Ad Manager report job failed. The ID of the failed report is: 11604262757"

Here are the SOAP logs.

INFO:googleads.soap:Request made: Service: "ReportService" Method: "runReportJob" URL: "https://ads.google.com/apis/ads/publisher/v201911/ReportService"
DEBUG:googleads.soap:Outgoing request: {'SOAPAction': '""', 'Content-Type': 'text/xml; charset=utf-8', 'authorization': 'REDACTED'}
<soap-env:Envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/">
  <soap-env:Header>
    <ns0:RequestHeader xmlns:ns0="https://www.google.com/apis/ads/publisher/v201911">
      <ns0:networkCode>21611127761</ns0:networkCode>
      <ns0:applicationName>cc3_admanager_report_bot (DfpApi-Python, googleads/22.0.0, Python/3.6.8, StatementBuilder, zeep)</ns0:applicationName>
    </ns0:RequestHeader>
  </soap-env:Header>
  <soap-env:Body>
    <ns0:runReportJob xmlns:ns0="https://www.google.com/apis/ads/publisher/v201911

">
      <ns0:reportJob>
        <ns0:reportQuery>
          <ns0:dimensions>AD_UNIT_NAME</ns0:dimensions>
          <ns0:columns>TOTAL_LINE_ITEM_LEVEL_IMPRESSIONS</ns0:columns>
          <ns0:columns>TOTAL_LINE_ITEM_LEVEL_CTR</ns0:columns>
          <ns0:columns>TOTAL_LINE_ITEM_LEVEL_CLICKS</ns0:columns>
          <ns0:startDate>
            <ns0:year>2020</ns0:year>
            <ns0:month>2</ns0:month>
            <ns0:day>1</ns0:day>
          </ns0:startDate>
          <ns0:endDate>
            <ns0:year>2020</ns0:year>
            <ns0:month>2</ns0:month>
            <ns0:day>29</ns0:day>
          </ns0:endDate>
          <ns0:dateRangeType>CUSTOM_DATE</ns0:dateRangeType>
          <ns0:statement>
            <ns0:query>WHERE CREATIVE_NAME LIKE ':company%' AND ORDER_ID IN (2285977422, 2521762987, 2521772548)</ns0:query>
            <ns0:values>
              <ns0:key>company</ns0:key>
              <ns0:value xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns0:TextValue">
                <ns0:value>Lux</ns0:value>
              </ns0:value>
            </ns0:values>
          </ns0:statement>
        </ns0:reportQuery>
      </ns0:reportJob>
    </ns0:runReportJob>
  </soap-env:Body>
</soap-env:Envelope>

DEBUG:googleads.soap:Incoming response: 
b'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">\n  <soap:Header>\n    <ResponseHeader xmlns="https://www.google.com/apis/ads/publisher/v201911">\n      <requestId>b0334821a3554ca8949d04eb643d196c</requestId>\n      <responseTime>1208</responseTime>\n    </ResponseHeader>\n  </soap:Header>\n  <soap:Body>\n    <runReportJobResponse xmlns="https://www.google.com/apis/ads/publisher/v201911">\n      <rval>\n        <id>11604262757</id>\n        <reportQuery>\n          <dimensions>AD_UNIT_NAME</dimensions>\n          <adUnitView>TOP_LEVEL</adUnitView>\n          <columns>TOTAL_LINE_ITEM_LEVEL_IMPRESSIONS</columns>\n          <columns>TOTAL_LINE_ITEM_LEVEL_CTR</columns>\n          <columns>TOTAL_LINE_ITEM_LEVEL_CLICKS</columns>\n          <startDate>\n            <year>2020</year>\n            <month>2</month>\n            <day>1</day>\n          </startDate>\n          <endDate>\n            <year>2020</year>\n            <month>2</month>\n            <day>29</day>\n          </endDate>\n          <dateRangeType>CUSTOM_DATE</dateRangeType>\n          <statement>\n            <query>WHERE CREATIVE_NAME LIKE \':company%\' AND ORDER_ID IN (2285977422, 2521762987, 2521772548)</query>\n            <values>\n              <key>company</key>\n              <value xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="TextValue">\n                <value>Lux</value>\n              </value>\n            </values>\n          </statement>\n          <timeZoneType>PUBLISHER</timeZoneType>\n        </reportQuery>\n      </rval>\n    </runReportJobResponse>\n  </soap:Body>\n</soap:Envelope>\n'
INFO:googleads.soap:Request made: Service: "ReportService" Method: "getReportJobStatus" URL: "https://ads.google.com/apis/ads/publisher/v201911/ReportService"
DEBUG:googleads.soap:Outgoing request: {'SOAPAction': '""', 'Content-Type': 'text/xml; charset=utf-8', 'authorization': 'REDACTED'}
<soap-env:Envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/">
  <soap-env:Header>
    <ns0:RequestHeader xmlns:ns0="https://www.google.com/apis/ads/publisher/v201911">
      <ns0:networkCode>21611127761</ns0:networkCode>
      <ns0:applicationName>cc3_admanager_report_bot (DfpApi-Python, googleads/22.0.0, Python/3.6.8, zeep)</ns0:applicationName>
    </ns0:RequestHeader>
  </soap-env:Header>
  <soap-env:Body>
    <ns0:getReportJobStatus xmlns:ns0="https://www.google.com/apis/ads/publisher/v201911">
      <ns0:reportJobId>11604262757</ns0:reportJobId>
    </ns0:getReportJobStatus>
  </soap-env:Body>
</soap-env:Envelope>

DEBUG:googleads.soap:Incoming response: 
b'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">\n  <soap:Header>\n    <ResponseHeader xmlns="https://www.google.com/apis/ads/publisher/v201911">\n      <requestId>1bdde1d56556d027fcaf9af799fd7e66</requestId>\n      <responseTime>427</responseTime>\n    </ResponseHeader>\n  </soap:Header>\n  <soap:Body>\n    <getReportJobStatusResponse xmlns="https://www.google.com/apis/ads/publisher/v201911">\n      <rval>IN_PROGRESS</rval>\n    </getReportJobStatusResponse>\n  </soap:Body>\n</soap:Envelope>\n'
INFO:googleads.soap:Request made: Service: "ReportService" Method: "getReportJobStatus" URL: "https://ads.google.com/apis/ads/publisher/v201911/ReportService"
DEBUG:googleads.soap:Outgoing request: {'SOAPAction': '""', 'Content-Type': 'text/xml; charset=utf-8', 'authorization': 'REDACTED'}
<soap-env:Envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/">
  <soap-env:Header>
    <ns0:RequestHeader xmlns:ns0="https://www.google.com/apis/ads/publisher/v201911">
      <ns0:networkCode>21611127761</ns0:networkCode>
      <ns0:applicationName>cc3_admanager_report_bot (DfpApi-Python, googleads/22.0.0, Python/3.6.8, zeep)</ns0:applicationName>
    </ns0:RequestHeader>
  </soap-env:Header>
  <soap-env:Body>
    <ns0:getReportJobStatus xmlns:ns0="https://www.google.com/apis/ads/publisher/v201911">
      <ns0:reportJobId>11604262757</ns0:reportJobId>
    </ns0:getReportJobStatus>
  </soap-env:Body>
</soap-env:Envelope>

DEBUG:googleads.soap:Incoming response: 
b'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">\n  <soap:Header>\n    <ResponseHeader xmlns="https://www.google.com/apis/ads/publisher/v201911">\n      <requestId>e2955a31f89de947d2e34185b11fe23e</requestId>\n      <responseTime>291</responseTime>\n    </ResponseHeader>\n  </soap:Header>\n  <soap:Body>\n    <getReportJobStatusResponse xmlns="https://www.google.com/apis/ads/publisher/v201911">\n      <rval>FAILED</rval>\n    </getReportJobStatusResponse>\n  </soap:Body>\n</soap:Envelope>\n'

Ad Manager API Forum Advisor Prod

unread,

Mar 11, 2020, 7:05:05 PM3/11/20

to ma...@covalentcareers.com, google-doubleclick...@googlegroups.com

Hi Mario,

I was able to replicate your query and successfully download a report when I added a "%" symbol to the beginning and end of the 'company' string. Below is the statement I used:

WHERE CREATIVE_NAME LIKE '%company%' AND ORDER_ID IN (2285977422, 2521762987, 2521772548)

Please note that when using the LIKE operator, the following string must be surrounded by "%". You can read more about this in our PQL Guide here. Once you have applied this change, try making your request to the API again and let me know if you still encounter any issues.

Mario Muniz

unread,

Mar 12, 2020, 6:02:55 PM3/12/20

to Google Ad Manager API Forum

This conflicts with one of your code snippets for the Python library.

See line 35:

https://github.com/googleads/googleads-python-lib/blob/f070075a081cf2b32fd3d3b1b34b3e3770858c52/examples/ad_manager/v201908/publisher_query_language_service/get_line_items_named_like.py#L35

When I use the syntax you have just suggested, I get an [PublisherQueryLanguageSyntaxError.UNPARSABLE @ ] error response"

.Where('CREATIVE_NAME LIKE \'%:company%\' AND ORDER_ID IN (2285977422, 2521762987, 2521772548)')

Ad Manager API Forum Advisor Prod

unread,

Mar 12, 2020, 11:45:50 PM3/12/20

to ma...@covalentcareers.com, google-doubleclick...@googlegroups.com

Hi Mario,

For context, the added % symbol is there to represent wildcard characters around the string to be searched (see the "LIKE" entry on this page for further details).

After further investigation, it doesn't seem like bound variables would work inside the LIKE query's string (that is, ':company' does not work, with or without the % symbols). What happens is that the variables do not get bound at runtime, and so the query would in fact look for a literal ":company" entry instead of the intended one.

With this in mind, I would suggest just entering the literal entry inside the LIKE query's string in order to fix this (ie. LIKE '%Lux%')

Regards,

Ziv Yves Sanchez, Ad Manager API Team

ref:_00D1U1174p._5001UV0Nc0:ref

Mario Muniz

unread,

Mar 13, 2020, 2:02:01 PM3/13/20

to Google Ad Manager API Forum

Just in case it was something like that, I tried hardcoding it (ie. LIKE '%Lux%') but that report job still fails.

<ns0:query>WHERE CREATIVE_NAME LIKE 'Lux%' AND ORDER_ID IN (2285977422, 2521762987, 2521772548)</ns0:query>

DEBUG:googleads.soap:Incoming response: 
b'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">\n  <soap:Header>\n    <ResponseHeader xmlns="https://www.google.com/apis/ads/publisher/v201911">\n      <requestId>ba423c68d51b24e4cdfb119cbea178f7</requestId>\n      <responseTime>78</responseTime>\n    </ResponseHeader>\n  </soap:Header>\n  <soap:Body>\n    <getReportJobStatusResponse xmlns="https://www.google.com/apis/ads/publisher/v201911">\n      <rval>FAILED</rval>\n    </getReportJobStatusResponse>\n  </soap:Body>\n</soap:Envelope>\n'

Mario Muniz

unread,

Mar 13, 2020, 2:14:42 PM3/13/20

to Google Ad Manager API Forum

At this point, it seems to be an issue with the Python library. I am literally using the same query statement from a report I created in the UI and it is returning a PQL UNPARSABLE error.

<ns0:runReportJob xmlns:ns0="https://www.google.com/apis/ads/publisher/v201911

">
      <ns0:reportJob>
        <ns0:reportQuery>
          <ns0:dimensions>AD_UNIT_NAME</ns0:dimensions>
          <ns0:columns>TOTAL_LINE_ITEM_LEVEL_IMPRESSIONS</ns0:columns>
          <ns0:columns>TOTAL_LINE_ITEM_LEVEL_CTR</ns0:columns>
          <ns0:columns>TOTAL_LINE_ITEM_LEVEL_CLICKS</ns0:columns>
          <ns0:startDate>
            <ns0:year>2020</ns0:year>
            <ns0:month>2</ns0:month>
            <ns0:day>1</ns0:day>
          </ns0:startDate>
          <ns0:endDate>
            <ns0:year>2020</ns0:year>
            <ns0:month>2</ns0:month>
            <ns0:day>29</ns0:day>
          </ns0:endDate>
          <ns0:dateRangeType>CUSTOM_DATE</ns0:dateRangeType>
          <ns0:statement>
            <ns0:query> where (creative_name like '%Lux%' and order_id in (2285977422, 2521762987, 2521772548))</ns0:query>
          </ns0:statement>
        </ns0:reportQuery>
      </ns0:reportJob>
    </ns0:runReportJob>

Query from saved report:

{
            'id': 11551324570,
            'name': 'Example Lux Report',
            'reportQuery': {
                'dimensions': [
                    'AD_UNIT_NAME'
                ],
                'adUnitView': 'FLAT',
                'columns': [
                    'TOTAL_LINE_ITEM_LEVEL_IMPRESSIONS',
                    'TOTAL_LINE_ITEM_LEVEL_CLICKS',
                    'TOTAL_LINE_ITEM_LEVEL_CTR'
                ],
                'dimensionAttributes': [],
                'customFieldIds': [],
                'customDimensionKeyIds': [],
                'startDate': None,
                'endDate': None,
                'dateRangeType': 'LAST_MONTH',
                'statement': {
                    'query': " where (creative_name like '%Lux%' and order_id in (2285977422, 2521762987, 2521772548))",
                    'values': []
                },
                'includeZeroSalesRows': False,
                'adxReportCurrency': None,
                'timeZoneType': 'PUBLISHER'
            },
            'isCompatibleWithApiVersion': True
        }

Ad Manager API Forum Advisor Prod

unread,

Mar 16, 2020, 11:14:16 AM3/16/20

to google-doubleclick...@googlegroups.com

Hi, Mario.

I tried running this report and was successful. You can see my API request below, and the WHERE clause is the one you mention was unparsable. (The other fields aren't the same, but that shouldn't make a difference here.)

Could you send me the full SOAP logs of the new failing request so I can be sure we're trying the same filter statement?

<soap-env:Envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/">
<soap-env:Header>
<ns0:RequestHeader xmlns:ns0="https://www.google.com/apis/ads/publisher/v201911">
<ns0:networkCode>21611127761</ns0:networkCode>

<ns0:applicationName>Client Testing (DfpApi-Python, googleads/22.0.0, Python/3.6.7, StatementBuilder, zeep)</ns0:applicationName>

</ns0:RequestHeader>
</soap-env:Header>
<soap-env:Body>

<ns0:runReportJob xmlns:ns0="https://www.google.com/apis/ads/publisher/v201911">
<ns0:reportJob>
<ns0:reportQuery>

<ns0:dimensions>ORDER_ID</ns0:dimensions>
<ns0:dimensions>ORDER_NAME</ns0:dimensions>
<ns0:columns>AD_SERVER_IMPRESSIONS</ns0:columns>
<ns0:columns>AD_SERVER_CLICKS</ns0:columns>
<ns0:columns>AD_SERVER_CTR</ns0:columns>
<ns0:columns>AD_SERVER_CPM_AND_CPC_REVENUE</ns0:columns>
<ns0:columns>AD_SERVER_WITHOUT_CPD_AVERAGE_ECPM</ns0:columns>
<ns0:dimensionAttributes>ORDER_TRAFFICKER</ns0:dimensionAttributes>
<ns0:dimensionAttributes>ORDER_START_DATE_TIME</ns0:dimensionAttributes>
<ns0:dimensionAttributes>ORDER_END_DATE_TIME</ns0:dimensionAttributes>

<ns0:startDate>
<ns0:year>2020</ns0:year>

<ns0:month>3</ns0:month>
<ns0:day>8</ns0:day>

</ns0:startDate>
<ns0:endDate>
<ns0:year>2020</ns0:year>

<ns0:month>3</ns0:month>
<ns0:day>16</ns0:day>

</ns0:endDate>
<ns0:dateRangeType>CUSTOM_DATE</ns0:dateRangeType>
<ns0:statement>
<ns0:query> where (creative_name like '%Lux%' and order_id in (2285977422, 2521762987, 2521772548))</ns0:query>
</ns0:statement>
</ns0:reportQuery>
</ns0:reportJob>
</ns0:runReportJob>

</soap-env:Body>
</soap-env:Envelope>

Thanks,
Donovan McMurray, Ad Manager API Team

ref:_00D1U1174p._5001UV0Nc0:ref

Mario Muniz

unread,

Mar 16, 2020, 12:15:12 PM3/16/20

to Google Ad Manager API Forum

Here's a Python code sample. Notice I have now hardcoded the statement instead of using a statement builder, just to rule anything with that out.

# Instantiate the Ad Manager client

ad_oauth2_client = oauth2.GoogleServiceAccountClient(

settings.GOOGLE_STORAGE_CREDENTIALS_PATH, oauth2.GetAPIScope('ad_manager'))

ad_manager_client = ad_manager.AdManagerClient(

ad_oauth2_client, '###', network_code='###')

# Create list of ad-hoc reports to run for resource impressions

end_date = datetime.date.today().replace(day=1) - datetime.timedelta(days=1)

start_date = end_date.replace(day=1)

base_report_job = {

'reportQuery': {

'dimensions': ['AD_UNIT_NAME'],

'columns': [

'TOTAL_LINE_ITEM_LEVEL_IMPRESSIONS',

'TOTAL_LINE_ITEM_LEVEL_CTR',

'TOTAL_LINE_ITEM_LEVEL_CLICKS',

],

'dateRangeType': 'CUSTOM_DATE',

'startDate': start_date,

'endDate': end_date

}

report_jobs = []

companies = ['Lux']

for company in companies:

current_job = base_report_job

statement = (ad_manager.StatementBuilder(version='v201911')

.Where("creative_name LIKE '%Lux%' and order_id in (2285977422, 2521762987, 2521772548)")

.Limit(None)

.Offset(None))

print(statement.ToStatement())

# current_job['reportQuery']['statement'] = statement.ToStatement()

current_job['reportQuery']['statement'] = {

'query': " where (creative_name like 'Lux%' and order_id in (2285977422, 2521762987, 2521772548))",

'values': None

}

report_jobs.append(current_job)

# Run reports

report_downloader = ad_manager_client.GetDataDownloader(version='v201911')

for report_job in report_jobs:

report_job_id = report_downloader.WaitForReport(report_job)

SOAP log for failed report:

INFO:googleads.soap:Request made: Service: "ReportService" Method: "runReportJob" URL: "https://ads.google.com/apis/ads/publisher/v201911/ReportService

"
DEBUG:googleads.soap:Outgoing request: {'SOAPAction': '""', 'Content-Type': 'text/xml; charset=utf-8', 'authorization': 'REDACTED'}
<soap-env:Envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/">
  <soap-env:Header>
    <ns0:RequestHeader xmlns:ns0="https://www.google.com/apis/ads/publisher/v201911">
      <ns0:networkCode>21611127761</ns0:networkCode>
      <ns0:applicationName>cc3_admanager_report_bot (DfpApi-Python, googleads/22.0.0, Python/3.6.10, StatementBuilder, zeep)</ns0:applicationName>
    </ns0:RequestHeader>
  </soap-env:Header>
  <soap-env:Body>
    <ns0:runReportJob xmlns:ns0="https://www.google.com/apis/ads/publisher/v201911">
      <ns0:reportJob>
        <ns0:reportQuery>
          <ns0:dimensions>AD_UNIT_NAME</ns0:dimensions>
          <ns0:columns>TOTAL_LINE_ITEM_LEVEL_IMPRESSIONS</ns0:columns>
          <ns0:columns>TOTAL_LINE_ITEM_LEVEL_CTR</ns0:columns>
          <ns0:columns>TOTAL_LINE_ITEM_LEVEL_CLICKS</ns0:columns>
          <ns0:startDate>
            <ns0:year>2020</ns0:year>
            <ns0:month>2</ns0:month>
            <ns0:day>1</ns0:day>
          </ns0:startDate>
          <ns0:endDate>
            <ns0:year>2020</ns0:year>
            <ns0:month>2</ns0:month>
            <ns0:day>29</ns0:day>
          </ns0:endDate>
          <ns0:dateRangeType>CUSTOM_DATE</ns0:dateRangeType>
          <ns0:statement>
            <ns0:query> where (creative_name like 'Lux%' and order_id in (2285977422, 2521762987, 2521772548))</ns0:query>
          </ns0:statement>
        </ns0:reportQuery>
      </ns0:reportJob>
    </ns0:runReportJob>
  </soap-env:Body>
</soap-env:Envelope>

DEBUG:googleads.soap:Incoming response: 
b'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">\n  <soap:Header>\n    <ResponseHeader xmlns="https://www.google.com/apis/ads/publisher/v201911">\n      <requestId>b506b4b18f8175fdf0b062ab7f523340</requestId>\n      <responseTime>686</responseTime>\n    </ResponseHeader>\n  </soap:Header>\n  <soap:Body>\n    <runReportJobResponse xmlns="https://www.google.com/apis/ads/publisher/v201911">\n      <rval>\n        <id>11610129578</id>\n        <reportQuery>\n          <dimensions>AD_UNIT_NAME</dimensions>\n          <adUnitView>TOP_LEVEL</adUnitView>\n          <columns>TOTAL_LINE_ITEM_LEVEL_IMPRESSIONS</columns>\n          <columns>TOTAL_LINE_ITEM_LEVEL_CTR</columns>\n          <columns>TOTAL_LINE_ITEM_LEVEL_CLICKS</columns>\n          <startDate>\n            <year>2020</year>\n            <month>2</month>\n            <day>1</day>\n          </startDate>\n          <endDate>\n            <year>2020</year>\n            <month>2</month>\n            <day>29</day>\n          </endDate>\n          <dateRangeType>CUSTOM_DATE</dateRangeType>\n          <statement>\n            <query> where (creative_name like \'Lux%\' and order_id in (2285977422, 2521762987, 2521772548))</query>\n          </statement>\n          <timeZoneType>PUBLISHER</timeZoneType>\n        </reportQuery>\n      </rval>\n    </runReportJobResponse>\n  </soap:Body>\n</soap:Envelope>\n'
INFO:googleads.soap:Request made: Service: "ReportService" Method: "getReportJobStatus" URL: "https://ads.google.com/apis/ads/publisher/v201911/ReportService"
DEBUG:googleads.soap:Outgoing request: {'SOAPAction': '""', 'Content-Type': 'text/xml; charset=utf-8', 'authorization': 'REDACTED'}
<soap-env:Envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/">
  <soap-env:Header>
    <ns0:RequestHeader xmlns:ns0="https://www.google.com/apis/ads/publisher/v201911">
      <ns0:networkCode>21611127761</ns0:networkCode>
      <ns0:applicationName>cc3_admanager_report_bot (DfpApi-Python, googleads/22.0.0, Python/3.6.10, zeep)</ns0:applicationName>
    </ns0:RequestHeader>
  </soap-env:Header>
  <soap-env:Body>
    <ns0:getReportJobStatus xmlns:ns0="https://www.google.com/apis/ads/publisher/v201911">
      <ns0:reportJobId>11610129578</ns0:reportJobId>
    </ns0:getReportJobStatus>
  </soap-env:Body>
</soap-env:Envelope>

DEBUG:googleads.soap:Incoming response: 
b'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">\n  <soap:Header>\n    <ResponseHeader xmlns="https://www.google.com/apis/ads/publisher/v201911">\n      <requestId>7223d6500f01d75e5ab211c594f78ad6</requestId>\n      <responseTime>67</responseTime>\n    </ResponseHeader>\n  </soap:Header>\n  <soap:Body>\n    <getReportJobStatusResponse xmlns="https://www.google.com/apis/ads/publisher/v201911">\n      <rval>IN_PROGRESS</rval>\n    </getReportJobStatusResponse>\n  </soap:Body>\n</soap:Envelope>\n'
INFO:googleads.soap:Request made: Service: "ReportService" Method: "getReportJobStatus" URL: "https://ads.google.com/apis/ads/publisher/v201911/ReportService"
DEBUG:googleads.soap:Outgoing request: {'SOAPAction': '""', 'Content-Type': 'text/xml; charset=utf-8', 'authorization': 'REDACTED'}
<soap-env:Envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/">
  <soap-env:Header>
    <ns0:RequestHeader xmlns:ns0="https://www.google.com/apis/ads/publisher/v201911">
      <ns0:networkCode>21611127761</ns0:networkCode>
      <ns0:applicationName>cc3_admanager_report_bot (DfpApi-Python, googleads/22.0.0, Python/3.6.10, zeep)</ns0:applicationName>
    </ns0:RequestHeader>
  </soap-env:Header>
  <soap-env:Body>
    <ns0:getReportJobStatus xmlns:ns0="https://www.google.com/apis/ads/publisher/v201911">
      <ns0:reportJobId>11610129578</ns0:reportJobId>
    </ns0:getReportJobStatus>
  </soap-env:Body>
</soap-env:Envelope>

DEBUG:googleads.soap:Incoming response: 
b'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">\n  <soap:Header>\n    <ResponseHeader xmlns="https://www.google.com/apis/ads/publisher/v201911">\n      <requestId>bcbbeb8211086b1acf7d28e0cfaf3718</requestId>\n      <responseTime>51</responseTime>\n    </ResponseHeader>\n  </soap:Header>\n  <soap:Body>\n    <getReportJobStatusResponse xmlns="https://www.google.com/apis/ads/publisher/v201911">\n      <rval>FAILED</rval>\n    </getReportJobStatusResponse>\n  </soap:Body>\n</soap:Envelope>\n'

Ad Manager API Forum Advisor Prod

unread,

Mar 16, 2020, 12:48:01 PM3/16/20

to google-doubleclick...@googlegroups.com

Hi, Mario.

Thanks for sharing your code. I see that you set the WHERE clause to contain "creative_name LIKE '%Lux%'" but then you overwrite it to contain "creative_name like 'Lux%'". If you check your SOAP request, you'll see the missing "%" at the beginning of the match string, as a result.

The ReportService has a different set of valid PQL statements (this is mentioned on our PQL Guide). So, while other services might not have this PQL restriction, the ReportService does. I verified that removing the leading '%' gives me the same error you're seeing. So, if you add a leading '%' you should be fine.

Mario Muniz

unread,

Mar 16, 2020, 12:51:42 PM3/16/20

to Google Ad Manager API Forum

Sorry for not matching your instructions exactly, but that doesn't work either. Here's what happens when I amend it to be %Lux%.

Including both things again.

'query': " where (creative_name like '%Lux%' and order_id in (2285977422, 2521762987, 2521772548))",

'values': None

}

report_jobs.append(current_job)

SOAP:

">
      <ns0:reportJob>
        <ns0:reportQuery>
          <ns0:dimensions>AD_UNIT_NAME</ns0:dimensions>
          <ns0:columns>TOTAL_LINE_ITEM_LEVEL_IMPRESSIONS</ns0:columns>
          <ns0:columns>TOTAL_LINE_ITEM_LEVEL_CTR</ns0:columns>
          <ns0:columns>TOTAL_LINE_ITEM_LEVEL_CLICKS</ns0:columns>
          <ns0:startDate>
            <ns0:year>2020</ns0:year>
            <ns0:month>2</ns0:month>
            <ns0:day>1</ns0:day>
          </ns0:startDate>
          <ns0:endDate>
            <ns0:year>2020</ns0:year>
            <ns0:month>2</ns0:month>
            <ns0:day>29</ns0:day>
          </ns0:endDate>
          <ns0:dateRangeType>CUSTOM_DATE</ns0:dateRangeType>
          <ns0:statement>
            <ns0:query> where (creative_name like '%Lux%' and order_id in (2285977422, 2521762987, 2521772548))</ns0:query>
          </ns0:statement>
        </ns0:reportQuery>
      </ns0:reportJob>
    </ns0:runReportJob>
  </soap-env:Body>
</soap-env:Envelope>

{'query': "WHERE creative_name LIKE '%Lux%' and order_id in (2285977422, 2521762987, 2521772548)", 'values': None}

DEBUG:googleads.soap:Incoming response: 
b'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">\n  <soap:Header>\n    <ResponseHeader xmlns="https://www.google.com/apis/ads/publisher/v201911">\n      <requestId>09b3f3139ab8b58f263829ec1565ce88</requestId>\n      <responseTime>440</responseTime>\n    </ResponseHeader>\n  </soap:Header>\n  <soap:Body>\n    <runReportJobResponse xmlns="https://www.google.com/apis/ads/publisher/v201911">\n      <rval>\n        <id>11610158309</id>\n        <reportQuery>\n          <dimensions>AD_UNIT_NAME</dimensions>\n          <adUnitView>TOP_LEVEL</adUnitView>\n          <columns>TOTAL_LINE_ITEM_LEVEL_IMPRESSIONS</columns>\n          <columns>TOTAL_LINE_ITEM_LEVEL_CTR</columns>\n          <columns>TOTAL_LINE_ITEM_LEVEL_CLICKS</columns>\n          <startDate>\n            <year>2020</year>\n            <month>2</month>\n            <day>1</day>\n          </startDate>\n          <endDate>\n            <year>2020</year>\n            <month>2</month>\n            <day>29</day>\n          </endDate>\n          <dateRangeType>CUSTOM_DATE</dateRangeType>\n          <statement>\n            <query> where (creative_name like \'%Lux%\' and order_id in (2285977422, 2521762987, 2521772548))</query>\n          </statement>\n          <timeZoneType>PUBLISHER</timeZoneType>\n        </reportQuery>\n      </rval>\n    </runReportJobResponse>\n  </soap:Body>\n</soap:Envelope>\n'
INFO:googleads.soap:Request made: Service: "ReportService" Method: "getReportJobStatus" URL: "https://ads.google.com/apis/ads/publisher/v201911/ReportService"
DEBUG:googleads.soap:Outgoing request: {'SOAPAction': '""', 'Content-Type': 'text/xml; charset=utf-8', 'authorization': 'REDACTED'}
<soap-env:Envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/">
  <soap-env:Header>
    <ns0:RequestHeader xmlns:ns0="https://www.google.com/apis/ads/publisher/v201911">
      <ns0:networkCode>21611127761</ns0:networkCode>
      <ns0:applicationName>cc3_admanager_report_bot (DfpApi-Python, googleads/22.0.0, Python/3.6.10, zeep)</ns0:applicationName>
    </ns0:RequestHeader>
  </soap-env:Header>
  <soap-env:Body>
    <ns0:getReportJobStatus xmlns:ns0="https://www.google.com/apis/ads/publisher/v201911">
      <ns0:reportJobId>11610158309</ns0:reportJobId>
    </ns0:getReportJobStatus>
  </soap-env:Body>
</soap-env:Envelope>

DEBUG:googleads.soap:Incoming response: 
b'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">\n  <soap:Header>\n    <ResponseHeader xmlns="https://www.google.com/apis/ads/publisher/v201911">\n      <requestId>4a01320459db45c0c1ea1bd1c8584742</requestId>\n      <responseTime>258</responseTime>\n    </ResponseHeader>\n  </soap:Header>\n  <soap:Body>\n    <getReportJobStatusResponse xmlns="https://www.google.com/apis/ads/publisher/v201911">\n      <rval>IN_PROGRESS</rval>\n    </getReportJobStatusResponse>\n  </soap:Body>\n</soap:Envelope>\n'
INFO:googleads.soap:Request made: Service: "ReportService" Method: "getReportJobStatus" URL: "https://ads.google.com/apis/ads/publisher/v201911/ReportService"
DEBUG:googleads.soap:Outgoing request: {'SOAPAction': '""', 'Content-Type': 'text/xml; charset=utf-8', 'authorization': 'REDACTED'}
<soap-env:Envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/">
  <soap-env:Header>
    <ns0:RequestHeader xmlns:ns0="https://www.google.com/apis/ads/publisher/v201911">
      <ns0:networkCode>21611127761</ns0:networkCode>
      <ns0:applicationName>cc3_admanager_report_bot (DfpApi-Python, googleads/22.0.0, Python/3.6.10, zeep)</ns0:applicationName>
    </ns0:RequestHeader>
  </soap-env:Header>
  <soap-env:Body>
    <ns0:getReportJobStatus xmlns:ns0="https://www.google.com/apis/ads/publisher/v201911">
      <ns0:reportJobId>11610158309</ns0:reportJobId>
    </ns0:getReportJobStatus>
  </soap-env:Body>
</soap-env:Envelope>

DEBUG:googleads.soap:Incoming response: 
b'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">\n  <soap:Header>\n    <ResponseHeader xmlns="https://www.google.com/apis/ads/publisher/v201911">\n      <requestId>f4c1f5e9baeb53f1913702a23f6c44cc</requestId>\n      <responseTime>190</responseTime>\n    </ResponseHeader>\n  </soap:Header>\n  <soap:Body>\n    <getReportJobStatusResponse xmlns="https://www.google.com/apis/ads/publisher/v201911">\n      <rval>COMPLETED</rval>\n    </getReportJobStatusResponse>\n  </soap:Body>\n</soap:Envelope>\n'
INFO:googleads.soap:Request made: Service: "PublisherQueryLanguageService" Method: "select" URL: "https://ads.google.com/apis/ads/publisher/v201911/PublisherQueryLanguageService"
DEBUG:googleads.soap:Outgoing request: {'SOAPAction': '""', 'Content-Type': 'text/xml; charset=utf-8', 'authorization': 'REDACTED'}
<soap-env:Envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/">
  <soap-env:Header>
    <ns0:RequestHeader xmlns:ns0="https://www.google.com/apis/ads/publisher/v201911">
      <ns0:networkCode>21611127761</ns0:networkCode>
      <ns0:applicationName>cc3_admanager_report_bot (DfpApi-Python, googleads/22.0.0, Python/3.6.10, zeep)</ns0:applicationName>
    </ns0:RequestHeader>
  </soap-env:Header>
  <soap-env:Body>
    <ns0:select xmlns:ns0="https://www.google.com/apis/ads/publisher/v201911">
      <ns0:selectStatement>
        <ns0:query>11610158309 LIMIT 500 OFFSET 0</ns0:query>
      </ns0:selectStatement>
    </ns0:select>
  </soap-env:Body>
</soap-env:Envelope>

DEBUG:googleads.soap:Incoming response: 
b'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">\n  <soap:Header>\n    <ResponseHeader xmlns="https://www.google.com/apis/ads/publisher/v201911">\n      <requestId>c5d4dd72a46c18395eb5053efc44534e</requestId>\n      <responseTime>202</responseTime>\n    </ResponseHeader>\n  </soap:Header>\n  <soap:Body>\n    <soap:Fault>\n      <faultcode>soap:Server</faultcode>\n      <faultstring>[PublisherQueryLanguageSyntaxError.UNPARSABLE @ ]</faultstring>\n      <detail>\n        <ApiExceptionFault xmlns="https://www.google.com/apis/ads/publisher/v201911">\n          <message>[PublisherQueryLanguageSyntaxError.UNPARSABLE @ ]</message>\n          <errors xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="PublisherQueryLanguageSyntaxError">\n            <fieldPath/>\n            <trigger/>\n            <errorString>PublisherQueryLanguageSyntaxError.UNPARSABLE</errorString>\n            <reason>UNPARSABLE</reason>\n          </errors>\n        </ApiExceptionFault>\n      </detail>\n    </soap:Fault>\n  </soap:Body>\n</soap:Envelope>\n'
WARNING:googleads.soap:Error summary: {'faultMessage': '[PublisherQueryLanguageSyntaxError.UNPARSABLE @ ]', 'requestId': 'c5d4dd72a46c18395eb5053efc44534e', 'responseTime': '202', 'serviceName': 'PublisherQueryLanguageService', 'methodName': 'select'}