phpapisoapgoogle-ad-manager

Google AdManager - PHP Client causing PublisherQueryLanguageSyntaxError.UNPARSABLE error


I am using this PHP lib to fetch impressions and revenue from Google AdManager. Everything works fine, until I add following code to exclude certain LINE_ITEMS.

$excluded_keywords = ["abc", "xyz"];
if (count($excluded_keywords) > 0) {
    $statementBuilder->where('LINE_ITEM_NAME NOT IN (:names)')
      ->withBindVariableValue(
        'names',
        $excluded_keywords
    );
}

The whole SOAP request looks like below but it give PublisherQueryLanguageSyntaxError.UNPARSABLE error from the API. Please suggest what am I doing wrong here.

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="https://www.google.com/apis/ads/publisher/v202011" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <SOAP-ENV:Header>
      <ns1:RequestHeader>
         <ns1:networkCode>111111</ns1:networkCode>
         <ns1:applicationName>my-app-name (DfpApi-PHP, googleads-php-lib/50.0.1, PHP/7.4.4)</ns1:applicationName>
      </ns1:RequestHeader>
   </SOAP-ENV:Header>
   <SOAP-ENV:Body>
      <ns1:runReportJob>
         <ns1:reportJob>
            <ns1:reportQuery>
               <ns1:dimensions>AD_UNIT_ID</ns1:dimensions>
               <ns1:dimensions>AD_UNIT_NAME</ns1:dimensions>
               <ns1:dimensions>DATE</ns1:dimensions>
               <ns1:dimensions>LINE_ITEM_NAME</ns1:dimensions>
               <ns1:columns>TOTAL_LINE_ITEM_LEVEL_CTR</ns1:columns>
               <ns1:columns>TOTAL_LINE_ITEM_LEVEL_CLICKS</ns1:columns>
               <ns1:columns>TOTAL_LINE_ITEM_LEVEL_ALL_REVENUE</ns1:columns>
               <ns1:columns>TOTAL_LINE_ITEM_LEVEL_IMPRESSIONS</ns1:columns>
               <ns1:columns>TOTAL_LINE_ITEM_LEVEL_WITHOUT_CPD_AVERAGE_ECPM</ns1:columns>
               <ns1:startDate>
                  <ns1:year>2021</ns1:year>
                  <ns1:month>1</ns1:month>
                  <ns1:day>1</ns1:day>
               </ns1:startDate>
               <ns1:endDate>
                  <ns1:year>2021</ns1:year>
                  <ns1:month>1</ns1:month>
                  <ns1:day>1</ns1:day>
               </ns1:endDate>
               <ns1:dateRangeType>CUSTOM_DATE</ns1:dateRangeType>
               <ns1:statement>
                  <ns1:query>WHERE LINE_ITEM_NAME NOT IN (:names) LIMIT 0 OFFSET 0</ns1:query>
                  <ns1:values>
                     <ns1:key>names</ns1:key>
                     <ns1:value xsi:type="ns1:SetValue">
                        <ns1:values xsi:type="ns1:TextValue">
                           <ns1:value>abc</ns1:value>
                        </ns1:values>
                        <ns1:values xsi:type="ns1:TextValue">
                           <ns1:value>xyz</ns1:value>
                        </ns1:values>
                     </ns1:value>
                  </ns1:values>
               </ns1:statement>
            </ns1:reportQuery>
         </ns1:reportJob>
      </ns1:runReportJob>
   </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Solution

  • With the help from google-doubleclick-for-publishers-api@googlegroups.com, I was able to solve this issue. The solution was not to use withBindVariableValue and also use single quotes around values as shown below:

    $excluded_keywords = ["abc", "xyz"];
    
    if (count($excluded_keywords) > 0) {
        $str = "'" . implode("','", $excluded_keywords) . "'";
        $statementBuilder->where('NOT LINE_ITEM_NAME IN (' . $str . ')');
    }