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>
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 . ')');
}