Google Ads API: cannot filter by a field which is not in select

[hiromi hishida]

Hi, 

I met a error while migrating our reporting feature.

error:

The following field must be present in SELECT clause: 'segments.ad_network_type'.

GAQL: 
SELECT customer.descriptive_name,segments.year,segments.month,segments.date,metrics.content_impression_share,metrics.content_budget_lost_impression_share,metrics.content_rank_lost_impression_share,metrics.impressions,metrics.clicks,metrics.cost_micros,metrics.conversions,metrics.all_conversions,metrics.view_through_conversions,metrics.conversions_value,metrics.all_conversions_value,metrics.ctr,metrics.conversions_from_interactions_rate,metrics.all_conversions_from_interactions_rate,metrics.average_cpv,metrics.average_cpc,metrics.cost_per_conversion,metrics.cost_per_all_conversions,metrics.video_views,metrics.video_view_rate,customer.currency_code,customer.time_zone,metrics.active_view_cpm,metrics.active_view_ctr,metrics.active_view_impressions,metrics.active_view_measurability,metrics.active_view_measurable_cost_micros,metrics.active_view_measurable_impressions,metrics.active_view_viewability,metrics.average_cost,metrics.average_cpe,metrics.average_cpm,metrics.cross_device_conversions,metrics.engagement_rate,metrics.engagements,customer.id,metrics.interaction_rate,metrics.interactions,metrics.interaction_event_types,metrics.invalid_click_rate,metrics.invalid_clicks,customer.auto_tagging_enabled,customer.test_account,segments.quarter,metrics.value_per_all_conversions,metrics.value_per_conversion FROM customer WHERE segments.ad_network_type IN ("CONTENT","YOUTUBE_SEARCH","YOUTUBE_WATCH","MIXED") AND segments.date BETWEEN '2020-04-01' AND '2020-04-05'

in AdWords API, the same request is OK.

ReportDefinition:

<?xml version="1.0"?> <reportDefinition><selector><fields>AccountDescriptiveName</fields><fields>Year</fields><fields>Month</fields><fields>Date</fields><fields>ContentImpressionShare</fields><fields>ContentBudgetLostImpressionShare</fields><fields>ContentRankLostImpressionShare</fields><fields>Impressions</fields><fields>Clicks</fields><fields>Cost</fields><fields>Conversions</fields><fields>AllConversions</fields><fields>ViewThroughConversions</fields><fields>ConversionValue</fields><fields>AllConversionValue</fields><fields>Ctr</fields><fields>ConversionRate</fields><fields>AllConversionRate</fields><fields>AverageCpv</fields><fields>AverageCpc</fields><fields>CostPerConversion</fields><fields>CostPerAllConversion</fields><fields>VideoViews</fields><fields>VideoViewRate</fields><fields>AccountCurrencyCode</fields><fields>AccountTimeZone</fields><fields>ActiveViewCpm</fields><fields>ActiveViewCtr</fields><fields>ActiveViewImpressions</fields><fields>ActiveViewMeasurability</fields><fields>ActiveViewMeasurableCost</fields><fields>ActiveViewMeasurableImpressions</fields><fields>ActiveViewViewability</fields><fields>AverageCost</fields><fields>AverageCpe</fields><fields>AverageCpm</fields><fields>CrossDeviceConversions</fields><fields>CustomerDescriptiveName</fields><fields>EngagementRate</fields><fields>Engagements</fields><fields>ExternalCustomerId</fields><fields>InteractionRate</fields><fields>Interactions</fields><fields>InteractionTypes</fields><fields>InvalidClickRate</fields><fields>InvalidClicks</fields><fields>IsAutoTaggingEnabled</fields><fields>IsTestAccount</fields><fields>MonthOfYear</fields><fields>Quarter</fields><fields>ValuePerAllConversion</fields><fields>ValuePerConversion</fields><predicates><field>AdNetworkType1</field><operator>IN</operator><values>CONTENT</values><values>YOUTUBE_SEARCH</values><values>YOUTUBE_WATCH</values><values>MIXED</values></predicates><dateRange><min>20200401</min><max>20200405</max></dateRange></selector><reportName>***</reportName><reportType>ACCOUNT_PERFORMANCE_REPORT</reportType><dateRangeType>CUSTOM_DATE</dateRangeType><downloadFormat>TSV</downloadFormat></reportDefinition>

Can I do the same query as AdWords API?

[Google Ads API Forum Advisor Prod]

Hi Hiromi,

Thank you for reaching out. If you put segments.ad_network_type in the WHERE clause you would also have to put it in the SELECT clause in order to successfully query the data. Please let me know if you have any further questions. 

Thanks and regards,
Xiaoming, Google Ads API Team

 

ref:_00D1U1174p._5001UZXzFz:ref

[hiromi hishida]

Hi Xiaoming,

Thank you for reply.

But we do not want to segment the report by ad_network_type.

AdWords API allows us to get the report segmented by only date with ad_network_type filtering.

expected report:

date, impression, clicks, impression_share, ...

2020-04-01, 100, 10, 0.1, ...

2020-04-02, 200, 10, 0.1, ...

2020-04-03, 300, 10, 0.1, ...

report with ad_network_type select as you mentioned:

date, ad_network_type, impression, clicks, impression_share, ...

2020-04-01, CONTENT, 50, 5, 0.1, ...

2020-04-01, YOUTUBE_SEARCH, 50, 5, 0.1, ...

2020-04-02, CONTENT, 200, 10, 0.1, ...

Is there any methods to do this?

regards,

2020年4月15日水曜日 3時30分38秒 UTC+9 adsapiforumadvisor:

[Google Ads API Forum Advisor Prod]

Hi Hiromi,

This is expected behavior in the Google Ads API. Whenever you use a segment criterion in the WHERE clause it is necessary to put the segment criterion in the SELECT clause. Please let me know if you have any further concerns.

Thanks and regards,
Xiaoming, Google Ads API Team

 

ref:_00D1U1174p._5001UZXzFz:ref

[hiromi hishida]

Hi Xiaoming,


Thank you for your reply.

Will the "expected behavior" be changed someday?

Then I strongly request a feature: GROUP BY clause in GAQL, as I've requested long before. ( https://groups.google.com/forum/?hl=ja#!topic/adwords-api/WlygUGpMBfM )

regards,

Hiromi

2020年4月16日木曜日 4時34分18秒 UTC+9 adsapiforumadvisor:

[Oliver]

Hi Xiaoming,

This is quite a serious deviation from how the Adwords API behaves.  Including a field in the SELECT clause causes segmentation which might not be wanted.  It's perfectly valid to want to filter the data (WHERE) but not segment it (SELECT).  If data is segmented, we can not easily get the aggregate performance we want.  There should be no logical or business reason to have to include a field in SELECT for it to also be included in WHERE.

There are a few worrying differences between the new and old API. Unless the Ads API behaves exactly like the Adwords API from a logical point of view, a LOT of apps will break when migrating and there is no easy fix to this.  Why is the new API being done in such a way that is different from how you pushed your old API for years? These are not small changes; they are major breaking ones with no added benefit to your users. 

Please guys consider the impact your new API is going to have. We already are going to 'waste' so much effort to upgrade for no functional benefit.  Please consider making the life of your users easier.

Oliver 

[Google Ads API Forum Advisor Prod]

Hi Oliver,

Thank you for reaching out. I have reported it as a bug internally to get it fixed for your concerns. We will keep you updated if there is any progress on the bug fix. Please also keep an eye on our blog for the announcements and updates on the new releases. We recommend to continue running production systems using the AdWords API. While Google Ads API is in “beta”, you may use the new API for testing and development work in preparation for future migration. Please keep in mind that during this "beta" phase, there may be breaking changes in the Google Ads API. We are trying our best to provide our customers the best possible experience when they are using the Adwords API/Google Ads API and we will continue to do so. Please trust us that we are improving all the time and the Google Ads API will become as full-fledged as Adwords API in the future.

Thanks and regards,
Xiaoming, Google Ads API Team

 

ref:_00D1U1174p._5001UZXzFz:ref