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 requiredclause 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 ( notrequired).

    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 SELECT clause.

    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 requiredclause for queries to SearchAds360Service (both Search and SearchStream methods).
    • Should notbe 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 optionalclause 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 optionalclause 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 optionalclause 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
    Create a Mobile Website
    View Site in Mobile | Classic
    Share by: