Page Summary

outlined_flag

• Search Ads 360 queries use clauses likeSELECT,FROM,WHERE,ORDER BY,LIMIT, andPARAMETERS to retrieve and filter data.

• TheSELECT clause specifies the data to retrieve, while theFROM clause identifies the main resource for the query.

• TheWHERE clause filters data,ORDER BY sorts results, andLIMIT restricts the number of results.

• ThePARAMETERS clause sets meta parameters, like currency withmetrics_currency and cross-account data retrieval withenable_mcc_expansion.

• enable_mcc_expansion currently only works with thebidding_strategy resource.

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

Clauses usefield names,resource names,operators,conditions,andorderings 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

TheSELECT clause:

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

This example query shows you how to select attributes of thecampaignresource:

SELECTcampaign.id,campaign.nameFROMcampaign

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, andbidding_strategy.name.
• Segment fields:segments.device andsegments.date.
• Metrics fields:metrics.impressions andmetrics.clicks.

SELECTcampaign.id,campaign.name,bidding_strategy.id,bidding_strategy.name,segments.device,segments.date,metrics.impressions,metrics.clicksFROMcampaignWHEREsegments.dateDURINGLAST_30_DAYS

SeeSegmentationto learn more about segmenting your search reports.

Main resource field

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

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

SELECTcampaign.idFROMad_groupWHEREad_group.status=PAUSED

Custom Floodlight variables

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

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

SELECTconversion_custom_metrics.id[123454321]FROMcampaign

SELECTconversion_custom_dimensions.id[123454321]FROMcampaign

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 thecampaign resource.

SELECTcustom_columns.id[12345678]FROMcampaign

Seehow to get custom column IDs.

Metrics fields

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

This example query selectsimpressions andclicks metrics for thecampaignresource.

SELECTmetrics.impressions,metrics.clicksFROMcampaign

Seemetrics for alist of metrics fields you can use in your queries.

Segments fields

You can select segments fields without specifying accompanying resource fieldsor metrics in theSELECT clause.

This example query segments results by device.

SELECTsegments.deviceFROMcampaign

Seesegments for alist of segments fields you can use in your queries.

Prohibited fields

You cannot use the following fields in theSELECT clause:

• Non-selectable fields, that is, fields with theSelectable metadataattribute marked asfalse.
• Repeated fields, that is, fields with theRepeated metadata attributemarked astrue.
• Fields that are not available for the given resource in theFROM clause.Attributes of some resources cannot be selected together. Some resources onlymake a subset of all metrics and segments available.
• Incompatible segments or metrics. SeeSegmentation for moreinformation.

SeeReference documentation fordetails on where to find this information for each resource.

FROM clause

TheFROM clause:

• Is arequired clause for queries toSearchAds360Service(bothSearch andSearchStream methods).
• Shouldnot be included for queries toSearchAds360FieldService.
• 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 theresource you specify in theFROM clause. You only need to add their attributesto theSELECT clause to return their values.

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

SELECTcampaign.id,ad_group.idFROMad_group

resource_name field

Theresource_name field of the main resource in theFROM clause is alwaysreturned.

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

SELECTad_group.idFROMad_group

Theresource_name field of anattributed resource isreturned when at least one field is selected.

In this example query,campaign.resource_name will be included in theresponse becausecampaign.id is selected:

SELECTcampaign.id,ad_group.idFROMad_group

SELECTcampaign.id,campaign.name,metrics.impressionsFROMcampaignWHEREsegments.dateDURINGLAST_30_DAYS

SeeSegmentationto learn more about segmenting your search reports.

SeeDate ranges to learn more about specifying dateranges in your queries.

Filter byresource_name field

You can use theresource_name field to filter or order data.

This example query uses thecampaign.resource_name field to filter the resultsby a given campaign:

SELECTcampaign.id,campaign.nameFROMcampaignWHEREcampaign.resource_name='customers/1234567/campaigns/987654'

Multiple conditions

You can combine multiple conditions to filter your data.

This example query requests the number ofclicks metrics for all campaignswithimpressions metrics on mobile for the last 30 days.

SELECTcampaign.id,campaign.name,segments.device,metrics.clicksFROMcampaignWHEREmetrics.impressions >0ANDsegments.device=MOBILEANDsegments.dateDURINGLAST_30_DAYS

SeeSegmentationto learn more about segmenting your reports.

Case sensitivity

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

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

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

SELECTcampaign.idFROMcampaignWHEREcampaign.nameREGEXP_MATCH"(?i).*test.*"

SeeQuery grammar reference for a complete list ofoperators you can use to filter your data.

Core date segments

The following segments fields are known ascore date segments:segments.date,segments.week,segments.month,segments.quarter, andsegments.year.

You can use core date segments in yourWHERE clause to specify a date or timeperiod.

This example query specifiesDURING LAST_30_DAYS for thesegments.date fieldin theWHERE clause:

SELECTcampaign.id,campaign.name,segments.date,metrics.clicksFROMcampaignWHEREsegments.dateDURINGLAST_30_DAYS

SeeSegmentation > Core date segments for detailedinformation about using core date segments.

Prohibited filtering

Filtering is not allowed:

• On non-selected segment fields, except forcore 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

TheORDER BY clause:

• Is anoptional clause in a query.
• Specifies the order in which the results are returned. Ordering follows thispattern:FIELD_NAMEORDERING_OPTION (separated by a blank space).
• Allows two options:ASC (ascending) orDESC (descending). Default isascending.

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

SELECTcampaign.name,metrics.clicksFROMcampaignORDERBYmetrics.clicksDESC

Multiple orderings

You can specify multiple fields in theORDER BY clause using a comma-separatedlist. The results will be ordered in the same sequence as you specify in thequery.

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

SELECTcampaign.name,ad_group.name,metrics.impressions,metrics.clicksFROMad_groupORDERBYcampaign.name,metrics.impressionsDESC,metrics.clicksDESC

Combine ordering and limit

You can use theORDER BY clause in combination with theLIMIT clause torefine your results.

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

SELECTcampaign.id,campaign.name,metrics.impressionsFROMcampaignWHEREsegments.dateDURINGLAST_30_DAYSORDERBYmetrics.impressionsDESCLIMIT5

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

TheLIMIT clause:

• Is anoptional 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 orsummary of results.

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

SELECTcampaign.name,ad_group.name,segments.device,metrics.impressionsFROMad_groupORDERBYmetrics.impressionsDESCLIMIT50

PARAMETERS clause

ThePARAMETERS clause lets you specify meta parameters for the request.

Include drafts

Theinclude_drafts parameter controls whether draft entities are included inthe results. The default isfalse. Set it totrue to include draft entities.

This example query returns both draft campaigns and regular campaigns:

SELECTcampaign.nameFROMcampaignPARAMETERSinclude_drafts=true

Omit unselectedresource_name

Theomit_unselected_resource_names parameter allows you to exclude theresource_name field of all resources that are not explicitly requested in yourSELECT clause. The default isfalse. If you set this parameter totrue, werecommend that you explicitly request the resource name of the primary resourceand any attributed resources in yourSELECT clause.

This example query returns neither thecampaign.resource_name nor thecustomer.resource_name field, because they are not included in theSELECTclause:

SELECTcampaign.name,customer.idFROMcampaignPARAMETERSomit_unselected_resource_names=true

This example query returns thecampaign.resource_name field, because it isexplicitly requested in theSELECT clause:

SELECTcampaign.name,campaign.resource_nameFROMcampaignPARAMETERSomit_unselected_resource_names=true

Change currency used in metrics

Themetrics_currency parameter lets you specify the currency to usewhen calculating a metric included in yourSELECT clause.The default is to use the currency of the account specified bycustomer_id.If you set this parameter, you need to use theISO 4217 3-charactercurrency code. For example: USD, EUR.

This example query returns the cost_micros metric in the currency of the accountspecified by thecustomer_id.

SELECTcampaign.name,metrics.cost_microsFROMcampaignWHEREsegments.date>="2018-08-15"ANDsegments.date <"2018-08-16"

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

SELECTcampaign.name,metrics.cost_microsFROMcampaignWHEREsegments.date>="2018-08-15"ANDsegments.date <"2018-08-16"PARAMETERSmetrics_currency="CLP"

Enable MCC expansion

Theenable_mcc_expansion parameter, when set to true, lets you includemetrics, fields, and segments from all accounts rooted atcustomer_id, forthe resource in theFROM clause. The response will use the currency of thecustomer_id, unless explicitly specified in themetrics_currencyparameter.

SELECTbidding_strategy.name,bidding_strategy.type,metrics.cost_microsFROMbidding_strategyWHEREsegments.dateDURINGLAST_14_DAYSPARAMETERSenable_mcc_expansion=true

• Query grammar