Query structure and clauses

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.

Note: There is a Yes/No select field on each resource page in the Reference section that filters out resource fields, segments fields, and metrics fields that can't be used when a resource is specified in the FROM clause.

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

Note: Not all resources have attributed resources. The Reference section includes details of the attributed resources available with each resource.

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

Note: If the resource in the FROM clause lists another resource as a segment, selecting any attributes of that resource acts as a segment.

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

Note: The AND separator can only be used between operator conditions (WHERE segments.device != "DESKTOP" AND segments.device != "MOBILE"), and not within a single operator condition (WHERE segments.device != "DESKTOP" AND "MOBILE").

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 local currency for the account. 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 local currency of the account.

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"

Learn more

• Query grammar