The GAQL cookbook: ten queries that answer the questions from the whole course

Advanced training · Module 8 — GAQL and the API — reading what the interface does not show

The ten questions that come up most often in this course, turned into GAQL queries you can use straight away. Each one with what it answers, the query itself, and the note that avoids the typical mistake. The numeric values (thresholds) are examples: tune them to your account.

1 · Search terms that convert and are not keywords

SELECT campaign.id, ad_group.id, search_term_view.search_term,
       segments.search_term_match_type, metrics.clicks, metrics.cost_micros,
       metrics.conversions, metrics.conversions_value
FROM search_term_view
WHERE segments.date DURING LAST_90_DAYS
  AND search_term_view.status != 'ADDED'
  AND metrics.conversions >= 1
ORDER BY metrics.conversions DESC

Note: status distinguishes terms already added as a keyword (ADDED), excluded ones (EXCLUDED) and neither (NONE).

2 · Keywords with low QS and high spend

SELECT campaign.id, ad_group.id, ad_group_criterion.criterion_id,
       ad_group_criterion.keyword.text,
       ad_group_criterion.quality_info.quality_score,
       ad_group_criterion.quality_info.creative_quality_score,
       ad_group_criterion.quality_info.post_click_quality_score,
       ad_group_criterion.quality_info.search_predicted_ctr,
       metrics.cost_micros, metrics.conversions
FROM keyword_view
WHERE segments.date DURING LAST_30_DAYS
  AND ad_group_criterion.status = 'ENABLED'
  AND ad_group_criterion.quality_info.quality_score <= 4
ORDER BY metrics.cost_micros DESC
LIMIT 100

Note: the three components come back as enums (BELOW_AVERAGE, AVERAGE, ABOVE_AVERAGE).

3 · IS lost to budget with a good CPA

SELECT campaign.id, campaign.name, metrics.cost_micros, metrics.conversions,
       metrics.cost_per_conversion,
       metrics.search_budget_lost_impression_share,
       metrics.search_rank_lost_impression_share,
       metrics.search_impression_share
FROM campaign
WHERE segments.date DURING LAST_14_DAYS
  AND campaign.status = 'ENABLED'
  AND campaign.advertising_channel_type IN ('SEARCH', 'SHOPPING')
  AND metrics.search_budget_lost_impression_share > 0.2
  AND metrics.conversions >= 10
ORDER BY metrics.search_budget_lost_impression_share DESC

Note: IS comes back as a fraction (0.2 = 20%); the CPA is then compared against the business target inside the script.

4 · RSA assets rated "Low" with volume

SELECT campaign.id, ad_group.id, ad_group_ad.ad.id, asset.id,
       asset.text_asset.text, ad_group_ad_asset_view.field_type,
       ad_group_ad_asset_view.performance_label,
       ad_group_ad_asset_view.pinned_field, metrics.impressions
FROM ad_group_ad_asset_view
WHERE segments.date DURING LAST_30_DAYS
  AND ad_group_ad_asset_view.performance_label = 'LOW'
  AND metrics.impressions > 2000
ORDER BY metrics.impressions DESC

Note: field_type distinguishes HEADLINE from DESCRIPTION; keep the asset.id for your archive.

5 · Products that spend without selling

SELECT segments.product_item_id, segments.product_title,
       segments.product_custom_attribute0,
       metrics.cost_micros, metrics.clicks, metrics.conversions,
       metrics.conversions_value
FROM shopping_performance_view
WHERE segments.date DURING LAST_30_DAYS
  AND metrics.cost_micros > 30000000
  AND metrics.conversions = 0
ORDER BY metrics.cost_micros DESC

Note: 30,000,000 micros = €30; custom_attribute0 is your margin label.

6 · Yesterday's placements (Display/video) for the census

SELECT campaign.id, detail_placement_view.placement,
       detail_placement_view.display_name,
       detail_placement_view.placement_type,
       metrics.impressions, metrics.clicks, metrics.cost_micros,
       metrics.conversions
FROM detail_placement_view
WHERE segments.date DURING YESTERDAY
  AND metrics.impressions > 0
ORDER BY metrics.clicks DESC

Note: for PMax, performance_max_placement_view with only metrics.impressions.

7 · Conversions by action and campaign

SELECT campaign.id, campaign.name, segments.conversion_action_name,
       segments.conversion_action_category,
       metrics.all_conversions, metrics.all_conversions_value
FROM campaign
WHERE segments.date DURING LAST_30_DAYS
  AND segments.conversion_action_name IS NOT NULL
ORDER BY campaign.id

Note: the golden rule — the segment is in the SELECT and in the WHERE. With all_conversions you see primary and secondary actions; for primary only, use metrics.conversions without segmenting by action.

8 · Budget and target changes over the last week

SELECT change_event.change_date_time, change_event.user_email,
       change_event.client_type, change_event.change_resource_type,
       change_event.changed_fields, change_event.old_resource,
       change_event.new_resource, campaign.id
FROM change_event
WHERE change_event.change_date_time DURING LAST_7_DAYS
  AND change_event.change_resource_type IN ('CAMPAIGN_BUDGET', 'CAMPAIGN', 'BIDDING_STRATEGY')
ORDER BY change_event.change_date_time DESC
LIMIT 1000

Note: filtering by date and setting a LIMIT are both compulsory; client_type distinguishes the interface, the API, scripts and automated recommendations.

9 · Zero conversions with normal clicks (broken measurement)

SELECT campaign.id, campaign.name, segments.date,
       metrics.clicks, metrics.conversions
FROM campaign
WHERE segments.date DURING LAST_3_DAYS
  AND campaign.status = 'ENABLED'
  AND metrics.clicks > 50
  AND metrics.conversions = 0

Note: the script compares this against the average clicks/conversions of the previous 30 days before raising an alert (conversion lag explains a single day of zeros).

10 · Performance by hour and day of the week

SELECT campaign.id, segments.day_of_week, segments.hour,
       metrics.clicks, metrics.cost_micros, metrics.conversions
FROM campaign
WHERE segments.date DURING LAST_30_DAYS
  AND campaign.status = 'ENABLED'

Note: 7 × 24 = 168 rows per campaign; aggregate in the sheet. Remember that with Smart Bidding hourly adjustments do not apply: this is diagnostics.

How to run them

In a script: var rows = AdsApp.search(query); while (rows.hasNext()) { var r = rows.next(); /* r.campaign.id, r.metrics.clicks… */ } and write to a sheet. In the online validator: paste and check. For a manager account (MCC): iterate over the accounts with AdsManagerApp and run in each one, reading customer.currency_code first.

💡 Ninja trick: these ten queries are, with small variations, the reading core of the Suite: 1 and 7 belong to SQONS and the Agent, 2 to QS Analyzer, 3 to Guardian and SBNS, 4 to RSA Optimizer, 5 to Shopping Ninja, 6 to Ninja Shield, and 8 and 9 to the Agent. What changes from one script to the next is not the query: it is what it does with the rows every night.

What you should remember

Ten queries cover the questions from the whole course; tune the thresholds to your account.
Micros ÷ 1,000,000; IS as a fraction; enums in quotes; the segment you filter on goes in the SELECT.
change_event requires a date and a LIMIT; PMax gives placements with impressions only.
The validator to test; AdsApp.search to run; customer for the currency.