The new Search Ads 360 Reporting API is now available. Join the searchads-api-announcements Google group to stay up to date on upcoming enhancements and releases.

Query structure and clauses

Page Summary

Search Ads 360 queries use clauses like SELECT, FROM, WHERE, ORDER BY, LIMIT, and PARAMETERS to retrieve and filter data.
The SELECT clause specifies the data to retrieve, while the FROM clause identifies the main resource for the query.
The WHERE clause filters data, ORDER BY sorts results, and LIMIT restricts the number of results.
The PARAMETERS clause sets meta parameters, like currency with metrics_currency and cross-account data retrieval with enable_mcc_expansion.
enable_mcc_expansion currently only works with the bidding_strategy resource.

A query is made up of a number of clauses: SELECT, FROM, WHERE, ORDER BY, LIMIT, and PARAMETERS.

Clauses use field names, resource names, operators, conditions, and orderings that combine into a single query request.

In basic terms, to build a query you:

Specify a resource from which to retrieve data.
Add fields and metrics to define the data you want to return.
Add segments to group your results.
Add attributed resources to implicitly join related resource data.
Filter, order, and limit your results.

`SELECT` clause

The SELECT clause:

Is a required clause in a query.
Specifies a set of fields to fetch in the request.
Takes a comma-separated list of resource fields, custom columns, custom Floodlight variables, segment fields, and metrics, and returns the values in the response.

This example query shows you how to select attributes of the campaign resource:

SELECT  campaign.id,  campaign.name FROM campaign

Multiple field types

You can request different field types in the same request.

The example query below shows a single query with a combination of:

Resource fields: campaign.id, campaign.name, bidding_strategy.id, and bidding_strategy.name.
Segment fields: segments.device and segments.date.
Metrics fields: metrics.impressions and metrics.clicks.

SELECT  campaign.id,  campaign.name,  bidding_strategy.id,  bidding_strategy.name,  segments.device,  segments.date,  metrics.impressions,  metrics.clicks FROM campaign WHERE segments.date DURING LAST_30_DAYS

See Segmentation to learn more about segmenting your search reports.

Main resource field

Typically, you would include your main resource field in the SELECT clause, but this is optional (not required).

This example query uses a main resource field (ad_group.status) to only filter the results.

SELECT campaign.id FROM ad_group WHERE ad_group.status = PAUSED

Custom Floodlight variables

You can include custom Floodlight variables in the SELECT clause using their ids.

In this example, the query includes a custom variable with ID 123454321 for the campaign resource.

SELECT  conversion_custom_metrics.id[123454321] FROM campaign

SELECT  conversion_custom_dimensions.id[123454321] FROM campaign

Custom columns

You can include custom columns in the SELECT clause using their IDs.

In this example, the query includes a custom column withID 12345678 for the campaign resource.

SELECT  custom_columns.id[12345678] FROM campaign

See how to get custom column IDs.

Metrics fields

You can select metrics fields for a given resource without including any other fields from the resource in the SELECTclause.

This example query selects impressions and clicks metrics for the campaign resource.

SELECT  metrics.impressions,  metrics.clicks FROM campaign

See metrics for a list of metrics fields you can use in your queries.

Segments fields

You can select segments fields without specifying accompanying resource fields or metrics in the SELECT clause.

This example query segments results by device.

SELECT segments.device FROM campaign

See segments for a list of segments fields you can use in your queries.

Prohibited fields

You cannot use the following fields in the SELECT clause:

Non-selectable fields, that is, fields with the Selectable metadata attribute marked as false.
Repeated fields, that is, fields with the Repeated metadata attribute marked as true.
Fields that are not available for the given resource in the FROM clause. Attributes of some resources cannot be selected together. Some resources only make a subset of all metrics and segments available.
Incompatible segments or metrics. See Segmentation for more information.

See Reference documentation for details on where to find this information for each resource.

FROM clause

The FROM clause:

Is a required clause for queries to SearchAds360Service (both Search and SearchStream methods).
Should not be included for queries to SearchAds360FieldService.
Specifies the main resource the query returns.
Can only specify a single resource.
Defines the fields you can use in all other clauses in the query.

Attributed resources

If attributed resources are available they are implicitly joined with the resource you specify in the FROM clause. You only need to add their attributes to the SELECT clause to return their values.

This example query returns both the ad group ID and the campaign ID, because campaign is an attributed resource of the ad_group resource.

SELECT  campaign.id,  ad_group.id FROM ad_group

`resource_name` field

The resource_name field of the main resource in the FROM clause is always returned.

In this example query, ad_group.resource_name will be included in the response even though it is not explicitly selected in the query:

SELECT ad_group.id FROM ad_group

The resource_name field of an attributed resource is returned when at least one field is selected.

In this example query, campaign.resource_name will be included in the response because campaign.id is selected:

SELECT  campaign.id,  ad_group.id FROM ad_group

WHERE clause

The WHERE clause:

Is an optional clause in a query.
Specifies conditions for filtering and segmenting the data for the request. Conditions follow this pattern: FIELD_NAME OPERATOR VALUE (separated by blank spaces).
Can include multiple conditions separated by the AND separator.

This example query shows how to use the WHERE clause to return impressions metrics for a given time period:

SELECT  campaign.id,  campaign.name,  metrics.impressions FROM campaign WHERE segments.date DURING LAST_30_DAYS

See Segmentation to learn more about segmenting your search reports.

See Date ranges to learn more about specifying date ranges in your queries.

Filter by `resource_name` field

You can use the resource_name field to filter or order data.

This example query uses the campaign.resource_name field to filter the results by a given campaign:

SELECT  campaign.id,  campaign.name FROM campaign WHERE campaign.resource_name = 'customers/1234567/campaigns/987654'

Multiple conditions

You can combine multiple conditions to filter your data.

This example query requests the number of clicks metrics for all campaigns with impressions metrics on mobile for the last 30 days.

SELECT  campaign.id,  campaign.name,  segments.device,  metrics.clicks FROM campaign WHERE metrics.impressions > 0  AND segments.device = MOBILE  AND segments.date DURING LAST_30_DAYS

See Segmentation to learn more about segmenting your reports.

Case sensitivity

When filtering on string values, the default case sensitivity of each operator plays an important role in correctly filtering your results.

The following table shows the default case sensitivity of each operator.

Default case sensitivity
`=/!=`	`Case sensitive`
`IN/NOT IN`	`Case sensitive`
`LIKE/NOT LIKE`	`Case insensitive`
`CONTAINS (...)`	`Case sensitive`
`REGEXP_MATCH/NOT REGEXP_MATCH`	`Case sensitive`

You can use the (?i) modifier to change the default sensitivity for REGEXP_MATCH and NOT REGEXP_MATCH to case insensitive, for example:

SELECT campaign.id FROM campaign WHERE campaign.name REGEXP_MATCH "(?i).*test.*"

See Query grammar reference for a complete list of operators you can use to filter your data.

Core date segments

The following segments fields are known as core date segments: segments.date, segments.week, segments.month, segments.quarter, and segments.year.

You can use core date segments in your WHERE clause to specify a date or time period.

This example query specifies DURING LAST_30_DAYS for the segments.date field in the WHERE clause:

SELECT  campaign.id,  campaign.name,  segments.date,  metrics.clicks FROM campaign WHERE segments.date DURING LAST_30_DAYS

See Segmentation > Core date segments for detailed information about using core date segments.

Prohibited filtering

Filtering is not allowed:

On non-selected segment fields, except for core date segments.
On fields of any message type, except primitives (for example, Int64Value, StringValue etc.).
On attributes of repeated fields of any message type, except primitives (for example, Int64Value, StringValue, etc.).

ORDER BY clause

The ORDER BY clause:

Is an optional clause in a query.
Specifies the order in which the results are returned. Ordering follows this pattern: FIELD_NAME ORDERING_OPTION (separated by a blank space).
Allows two options: ASC (ascending) or DESC (descending). Default is ascending.

This example query orders the campaigns by number of clicks in descending order (highest to lowest):

SELECT  campaign.name,  metrics.clicks FROM campaign ORDER BY metrics.clicks DESC

Multiple orderings

You can specify multiple fields in the ORDER BY clause using a comma-separated list. The results will be ordered in the same sequence as you specify in the query.

This example query selects ad group data, and orders the results in ascending order by campaign name, then in descending order by number of impressions, and then in descending order by number of clicks:

SELECT  campaign.name,  ad_group.name,  metrics.impressions,  metrics.clicks FROM ad_group ORDER BY  campaign.name,  metrics.impressions DESC,  metrics.clicks DESC

Combine ordering and limit

You can use the ORDER BY clause in combination with the LIMIT clause to refine your results.

This example query returns the five campaigns with the highest impressions over the last 30 days:

SELECT  campaign.id,  campaign.name,  metrics.impressions FROM campaign WHERE segments.date DURING LAST_30_DAYS ORDER BY metrics.impressions DESC LIMIT 5

Prohibited ordering

Ordering is not allowed:

By attributes of non-selected resources.
By non-selected metrics.
By non-selected segments.
For these field types:
- MESSAGE
- Repeated fields
- Attributes of repeated fields.

LIMIT clause

The LIMIT clause:

Is an optional clause in a query.
Allows you to limit the number of results the query returns.

This clause is useful, for example, if you're only interested in a sample or summary of results.

This example query limits the total number of results to 50:

SELECT  campaign.name,  ad_group.name,  segments.device,  metrics.impressions FROM ad_group ORDER BY metrics.impressions DESC LIMIT 50

PARAMETERS clause

The PARAMETERS clause lets you specify meta parameters for the request.

Include drafts

The include_drafts parameter controls whether draft entities are included in the results. The default is false. Set it to true to include draft entities.

This example query returns both draft campaigns and regular campaigns:

SELECT campaign.name FROM campaign PARAMETERS include_drafts=true

Omit unselected `resource_name`

The omit_unselected_resource_names parameter allows you to exclude the resource_name field of all resources that are not explicitly requested in your SELECT clause. The default is false. If you set this parameter to true, we recommend that you explicitly request the resource name of the primary resource and any attributed resources in your SELECT clause.

This example query returns neither the campaign.resource_name nor the customer.resource_name field, because they are not included in the SELECT clause:

SELECT  campaign.name,  customer.id FROM campaign PARAMETERS omit_unselected_resource_names = true

This example query returns the campaign.resource_name field, because it is explicitly requested in the SELECT clause:

SELECT  campaign.name,  campaign.resource_name FROM campaign PARAMETERS omit_unselected_resource_names = true

Change currency used in metrics

The metrics_currency parameter lets you specify the currency to use when calculating a metric included in your SELECT clause. The default is to use the currency of the account specified by customer_id. If you set this parameter, you need to use the ISO 4217 3-character currency code. For example: USD, EUR.

This example query returns the cost_micros metric in the currency of the account specified by the customer_id.

SELECT  campaign.name,  metrics.cost_micros FROM campaign WHERE segments.date >= "2018-08-15" AND segments.date < "2018-08-16"

This example query returns the cost_micros metric in Chilean pesos (CLP).

SELECT  campaign.name,  metrics.cost_micros FROM campaign WHERE segments.date >= "2018-08-15" AND segments.date < "2018-08-16" PARAMETERS metrics_currency = "CLP"

Enable MCC expansion

The enable_mcc_expansion parameter, when set to true, lets you include metrics, fields, and segments from all accounts rooted at customer_id, for the resource in the FROM clause. The response will use the currency of the customer_id, unless explicitly specified in the metrics_currency parameter.

This example query returns bidding_strategy.name, bidding_strategy.type and metrics.cost_micros from all accounts in the account hierarchy rooted at customer_id, because the enable_mcc_expansion parameter is set to true.

SELECT  bidding_strategy.name,  bidding_strategy.type,  metrics.cost_micros FROM bidding_strategy WHERE segments.date DURING LAST_14_DAYS PARAMETERS enable_mcc_expansion = true

Learn more

Query grammar