This blog series follows the journey of building the new and improved Interactive Google Ads Query Builder. Part 2 of this series described the design of the detailed JSON resource schema that will serve as the canonical data set for the Interactive Query Builder Angular application. Part 3 will focus on how we can use the GoogleAdsFieldService to create that schema.

Retrieving the data

We can retrieve most of the data for the schema described in Part 2 by making an API call to the GoogleAdsFieldService with the following query:

SELECT name, category, data_type, selectable, filterable, sortable, selectable_with, metrics, segments, is_repeated, type_url, enum_values, attribute_resources

The result contains an array of JSON objects for every field available in the Google Ads API. Each object in the array will contain the fields in the SELECT clause above. For example, the ad_group list item would look like this:

{
      "resourceName": "googleAdsFields/ad_group",
      "name": "ad_group",
      "category": "RESOURCE",
      "dataType": "MESSAGE",
      "selectable": false,
      "filterable": false,
      "sortable": false,
      "selectableWith": [...],
      "metrics": [...],
      "segments": [...],
      "isRepeated": false,
      "typeUrl": "com.google.ads.googleads.v6.resources.AdGroup",
      "enumValues": [],
      "attributeResources": [...]
}

We’ll restructure this into key-value pairs, where the keys are the entity names and the values are the metadata for each entity, making it easier to lookup the metadata for a given entity. For example, the ad_group entry would look like this:

{
    "ad_group": {
      "resourceName": "googleAdsFields/ad_group",
      "name": "ad_group",
      "category": "RESOURCE",
      "dataType": "MESSAGE",
      "selectable": false,
      "filterable": false,
      "sortable": false,
      "selectableWith": [...],
      "metrics": [...],
      "segments": [...],
      "isRepeated": false,
      "typeUrl": "com.google.ads.googleads.v6.resources.AdGroup",
      "enumValues": [],
      "attributeResources": [...]
}

As compared with the schema we designed in Part 2, this restructured object is missing several fields, which we’ll add in the following sections: attributes, fields, description, and display name.

Attributes

If you recall from the schema design, each resource should contain an array named attributes which contains the names of all fields present on the resource itself and any attributed resources. We can create this array by iterating through the results of our GoogleAdsFieldService query and adding the name of entries beginning with either our resource or one of its attributed resources, followed by a dot.

Fields

The fields entry in our schema is an object with an entry for each (a) item in the attributes array we just created, (b) metric on the resource, and (c) segment on the resource. The value of each entry will be each respective field’s value in the object we created earlier. However, we still need to add an incompatible_fields array to each field.

To construct the incompatible_fields array for each entry in our fields object, we’ll check to see if each of the fields, metrics, and segments present on the top level object is selectable_with the given field being evaluated. If not, we’ll add that field, metric, or segment to the incompatible_fields array.

Descriptions

Next, we need to add descriptions to each top level resource and item in its fields entry. It is important to note that a field may have a different description depending on the top level resource. For example, the description of ad_group.id is “Output only. The ID of the ad group.” but the description of campaign.id is “Output only. The ID of the campaign.” The REST discovery docs contain nested descriptions that we can use to create a canonical descriptions object, which we’ll use to populate our schema. This step entails parsing and formatting, the details of which we will not discuss here. We just want you to know that the REST discovery docs exist in case you ever need them. This is currently the best solution available, although it would be easier if the descriptions were returned from the GoogleAdsFieldService.

Display names

All that is left to do is populate the display names field in our resource schema. We can do this by simply replacing underscores with spaces and capitalizing the first letter of each word in the name.

Filtering for resources

Our resource schema is now fully populated. However, it contains every resource, field, segment, and metric returned from our GoogleAdsFieldService query. We can filter this schema to include only items with a category of RESOURCE.

Conclusion

We have now created an expanded resource schema containing detailed field information and a list of incompatible fields for each field, which we can use in our Angular application. In this post, we’ve covered:

• How to use the GoogleAdsFieldService to retrieve field metadata.
• Field compatibility in GAQL.
• The REST discovery API.

- Devin Chasanoff, on behalf of the Google Ads API Team

This blog series follows the journey of building the recently released Interactive Google Ads Query Builder tool. Part 1 of this series outlined what we’ll be covering in the series as well as the rationale behind publishing this content. Part 2 will focus on designing a detailed JSON resource schema that will serve as the canonical data set for the Interactive Query Builder Angular application.

Background

As mentioned in Part 1, one of the major benefits of the new Interactive Query Builder is that it provides real-time feedback detailing why fields may or may not be selectable in a given clause of a Google Ads Query Language (GAQL) query.

For example, let’s say you are constructing a GAQL query with ad_group as the main resource in the FROM clause. Both segments.conversion_action and metrics.absolute_top_impression_percentage are selectable on the ad_group resource. However, taking a look at the detailed reference documentation for segments.conversion_action, we can see that there is a list of “Selectable With” fields, and that list does not include metrics.absolute_top_impression_percentage. Therefore, those two fields are incompatible. Regardless of what resource is in the FROM clause, if one of those two fields is present in the query, we know that the other cannot be. That is why metrics.absolute_top_impression_percentage is no longer selectable in the Interactive Query Builder once segments.conversion_action is selected.

Rather than trying to piece together all of this logic at runtime with various back-and-forth server calls, we thought it would be beneficial to feed that data into the application with static JSON files containing the resource schema. What might that optimal schema look like?

Schema Design (definition at the end of the blog post)

A GAQL string requires a single resource in the FROM clause. Given that constraint, the top level JSON schema will be a map from resources to detailed schemas for each resource. For example, the ad_group entry in our schema will look like this:

{

    "ad_group": {

        "name": "ad_group",
        "display_name": "Ad Group",
        "description": "An ad group.",
       // Array of all attribute and attributed resource fields.
        "attributes": [
            "ad_group.ad_rotation_mode",
            "ad_group.base_ad_group",
            "ad_group.campaign",
            ...
            "campaign.ad_serving_optimization_status",
            "campaign.advertising_channel_sub_type",
            "campaign.advertising_channel_type",
            ...
            "customer.auto_tagging_enabled",
            "customer.call_reporting_setting.call_conversion_action",
            "customer.call_reporting_setting.call_conversion_reporting_enabled",
            ...     
        ],
        // Array of all metrics selectable with ad_group.
        "metrics": [...],
        // Array of all segments selectable with ad_group.
        "segments": [...],
       // Expanded info for all items listed in attributes, metrics, and segments arrays.
        "fields": {...}

}

The crux of this enhanced schema is the fields entry. The keys of this object will be all of the attributes, metrics, and segments of the top level resource (e.g. ad_group). The value of each item in this object will be objects containing detailed information about that given field, as well as an additional field called incompatible_fields, an array of the fields that are incompatible with the given field. For example, the metrics.phone_impressions entry of the fields object would look like this:

            "metrics.phone_impressions": {
                "field_details": {
                    "name": "metrics.phone_impressions",
                    "category": "METRIC",
                    "selectable": true,
                    "filterable": true,
                    "sortable": true,
                    "data_type": "INT64",
                    "is_repeated": false,
                    "type_url": "",
                    "description": "Number of offline phone impressions.",
                    "enum_values": [],
                    "selectable_with": [
                        "ad_group",
                        "ad_group_ad",
                        "campaign",
                        "customer",
                        "extension_feed_item",
                        "segments.ad_network_type",
                        "segments.click_type",
                        "segments.date",
                        "segments.day_of_week",
                        "segments.interaction_on_this_extension",
                        "segments.keyword.ad_group_criterion",
                        "segments.keyword.info.match_type",
                        "segments.keyword.info.text",
                        "segments.month",
                        "segments.month_of_year",
                        "segments.quarter",
                        "segments.week",
                        "segments.year"
                    ]
                },
                "incompatible_fields": [
                    "segments.slot",
                    "segments.device",
                    "segments.external_conversion_source",
                    "segments.conversion_action_category",
                    "segments.conversion_lag_bucket",
                    "segments.hour",
                    "segments.conversion_action_name",
                    "segments.conversion_action",
                    "segments.conversion_adjustment",
                    "segments.conversion_or_adjustment_lag_bucket"
                ]
            },

The recursive nature of the schema may seem somewhat redundant, as some fields will appear in multiple resources. However, we will ultimately divide this main schema into individual JSON files for each resource to decrease load times, and we will only retrieve a single resource-specific schema depending on the resource in the FROM clause.

Schema Definition

For reference, the full schema definition is below:

interface ResourceSchema {
  name: string; // the name of the resource
  display_name: string; // the display name of the resource
  description: string; // the description of the resource
  attributes: string[]; // the resource's fields (including attributed resource fields)
  metrics: string[]; // available metrics when the resource is in the FROM clause
  segments: string[]; // available segments when the resource is in the FROM clause
  fields: { // detailed info about all fields, metrics, and segments
    [key: string]: {
        field_details: FieldDetails; // details about the field (defined below)
        incompatible_fields: string[]; // fields that are incompatible with the current field
    }
  };
}

interface FieldDetails {
  name: string; // the name of the field
  category: string; // the field's category (e.g. ATTRIBUTE, METRIC, SEGMENT)
  selectable: boolean; // whether or not the field is allowed to be placed in the SELECT clause
  filterable: boolean; // whether or not the field is allowed to be placed in the WHERE clause
  sortable: boolean; // whether or not the field is allowed to be placed in the ORDER BY clause
  data_type: string; // the field's data type
  is_repeated: boolean; // whether or not the field is a repeated field
  type_url: string; // the field's type_url
  description: string; // the field's description
  enum_values: string[]; // possible enum values if the field is of type ENUM
  selectable_with: string[]; // the list of field the current field is selectable with
}

Conclusion

With that, we now have designed an expanded resource schema containing detailed field information and a list of incompatible fields for each field, which we can use in our Angular application. In part 3, we’ll discuss how to create this schema using the GoogleAdsFieldService.
Hopefully this has deepened your understanding of and shown you what is possible with the Google Ads API. If you have any questions or need additional help, contact us via the forum or at [email protected].
- Devin Chasanoff, on behalf of the Google Ads API Team

- Devin Chasanoff, on behalf of the Google Ads API Team