How to query WHERE cost > X?
I've run into an issue with the Google Ads Query Validator and need some help debugging.
* API Version: v10
* Query: "SELECT customer.id, metrics.impressions FROM group_placement_view WHERE group_placement_view.placement_type IN ("YOUTUBE_CHANNEL") AND (metrics.impressions + metrics.average_cpm) > 1000000"
* Result: Invalid Query (errors below)
* The filtering condition '(metrics.impressions + metrics.average_cpm) > 1000000' in the WHERE clause is not valid. Filtering conditions must take the form of 'field operator operand'. The operators IS NULL and IS NOT NULL take no operand.
* Expected result: Using SQL, I can do arithmetic operation in the "WHERE" clause. This syntax is not valid for GAQL?
How can I filter group_placements WHERE the spend is above X ? There is no metric "spend" or "cost"
I thought to use: (all_conversions * cost_per_all_conversions)
Unfortunately, this is not possible. GAQL isn't SQL. It's up to the developer to do any arithmetic operations on the returned AdsRows.
You can put a simple predicate based operator on the WHERE clause but only for limiting results of that exact resource (like the cost_micros below).
SELECT campaign.name, ad_group.name, customer.id, customer.descriptive_name, campaign.advertising_channel_type, segments.date, metrics.cost_micros, metrics.impressions, metrics.clicks, metrics.ctr, metrics.conversions FROM ad_group_ad WHERE segments.date BETWEEN {date_range} AND campaign.advertising_channel_type = 'SEARCH' AND metrics.cost_micros > 0 ORDER BY segments.date