Tag Archives: reports

Announcing the Google Ads Query Language Query Validator

Today, we are releasing a Google Ads Query Language (GAQL) Query Validator, so you can validate GAQL queries directly in the browser via the developer documentation site. This new tool is integrated with the Interactive GAQL Query Builder to make for a streamlined workflow.
In order to use the new query validator, either visit the query validator page directly or click the Enter or edit a query button on any resource’s query builder page.



Upon successfully validating a query with the query validator, continue editing the query using the Interactive Query Builder by clicking the Continue Editing in Query Builder button.





The Query Validator will also provide feedback related to errors in invalid queries.




In addition, you can continue manually editing and validating queries built with the query builder in the query validator by clicking the Enter or Edit a Query button after constructing a query with the query builder.





As you get started with the new query validator, please feel free to share feedback by clicking the Send feedback button on the top right of the page.

If you have any questions or need additional help, contact us via the forum or at [email protected]

The Query Builder Blog Series: Part 8 – Conclusion

This blog series follows the journey of building the new and improved Interactive Google Ads Query Builder tool. If you’ve read this entire series up to this point, you should understand many details and nuances of the Google Ads Query Language (GAQL). Part 8 will wrap up our journey and discuss what we’ve learned along the way.

Creating the User Interface

With the ResourceService, SelectionService, and ValidationService in place, all that is left to do is create the components that will comprise the user interface. In some cases, we can pull data directly from our services. In other cases, such as tracking selectability, selection status, or query validity, we can subscribe to our services so that the components always reflect the current state of the application.

Conclusion

This blog series has walked through several integral parts of the Query Builder application with an emphasis on those which can be useful in understanding GAQL. Let’s recap our learnings from each post:


Part 1 - Setting the Stage

Part 2 - Designing a Resource Schema
  • What a schema might look like for our application.

Part 3 - Creating a Resource Schema
  • How to use the GoogleAdsFieldService to retrieve field metadata.
  • Field compatibility in GAQL.
  • The REST discovery API.

Part 4 - Creating the Resource Service
  • GAQL query structure.
  • The various types of fields that can appear in GAQL clauses.
  • Field properties and how they correspond to different GAQL clauses.

Part 5 - Determining Field Selectability
  • Field compatibility and how to determine if a field is selectable.

Part 6 - Selecting and Deselecting Fields
  • GAQL query structure.
  • Additional detail regarding selectability.
  • Using Observables in Angular.

Part 7 - Query Validation
  • The various facets of GAQL query validation.

Bonus

In addition to everything we’ve learned about the Google Ads Query Language, I hope you’ve also picked up some tips about writing Angular applications. This was my first time using Angular, and I’ve found it to be a powerful framework for quickly building complex applications. Using Angular services with dependency injection along with observables has been an efficient way to manage app-wide state.


Hopefully this series deepened your understanding of how to construct GAQL queries with the Google Ads API. If you have any questions or need additional help, contact us via the forum or at [email protected]

The Query Builder Blog Series: Part 7 – Query Validation

This blog series follows the journey of building the new and improved Interactive Google Ads Query Builder tool. Part 6 of this series described how we select and deselect fields from a query. In Part 7, we will discuss how to validate queries based on user selections.

Background


While we’ve learned about field compatibility in Part 5 and a bit more about selectability in Part 6, it is still possible to create an invalid Google Ads Query Language (GAQL) string using the Query Builder. In order to account for this, we’ll create a ValidationService that subscribes to the Observable we created in the SelectionService in Part 6. Each time the Observable is triggered, we’ll perform a set of validation tests and generate a list of error messages. We’ll create another Observable in the ValidationService that is emitted each time the validations are run. This way, we can let users know if their query contains any errors. If the user hasn’t made any selections, this represents the initial state of the application, so we won’t show any errors in this case.





We will perform the following validation tests:
  • Ensure the SELECT clause contains fields
  • Ensure core date selections are valid
  • Ensure click_view has a valid date filter
  • Ensure change_event and change_status have valid date filters
  • Ensure change_event and change_status have valid limits

Ensure the SELECT Clause Contains Fields

A valid GAQL query must contain at least one valid field in the SELECT clause. If selections have been made in clauses other than SELECT, and the SELECT clause is empty, we will generate an error.

Ensure Core Date Selections are Valid

If there exists a core date segment (segments.date, segments.week, segments.month, segments.quarter, segments.year) in any clause of a query, then the filtering conditions in the WHERE clause must combine to form a finite date range of core date segments that, in aggregate, form a date range of at least one day. If there are no core date segments present in the query, we will not generate an error.


Otherwise, we’ll ensure that the core date segment filters in the WHERE clause combine to form a finite range. In other words, a single filter such as WHERE segments.date > ‘2021-01-01’ would fail because the date range is open ended, in which case we’ll generate an error. However, the following filters would be valid: WHERE segments.date > ‘2021-01-01’ AND segments.date < ‘2021-02-01’, WHERE segments.date = ‘2021-01-01’, and WHERE segments.date DURING LAST_7_DAYS. All three examples have a beginning and end date, so we will generate no error.


Finally, if the core date segment filters do form a finite range, we’ll check to ensure that, in aggregate, they result in at least a single day. For example, a query containing the following filtering conditions will fail because no dates meet both filtering criteria: WHERE segments.date = ‘2021-01-01’ AND segments.date BETWEEN ‘2021-02-01’ AND ‘2021-03-01’, in which case we will generate an error. However, this filtering condition is valid: WHERE segments.date BETWEEN ‘2021-01-01’ AND ‘2021-01-31’ AND segments.date >= ‘2021-01-15’ AND segments.date < ‘2021-03-01’ because the date range of ‘2021-01-15’ - ‘2021-01-31’ meets all filtering criteria, and therefore, we will generate no error.

Ensure click_view has a Valid Date Filter

When click_view is the main resource in the FROM clause, a date filter specifying a single day in the last 90 days must be present in the WHERE clause regardless of what other selections have been made.

Ensure change_event and change_status have Valid Date Filters

If either change_event or change_status is the resource in the FROM clause, there must be a valid, finite date range composed of filtering criteria in the WHERE clause similar to the rule Ensure Core Date Selections are Valid. However, this criteria applies regardless of whether or not any date fields are present in the query. In addition, the filtering conditions are not composed of core date segments because none of the core date segments are available when change_event or change_status is the main resource in the FROM clause (see Part 4). When change_event is the resource in the FROM clause, date evaluation on the Google Ads API server is performed on filters on the change_event.change_date_time field. When change_status is the resource in the FROM clause, date evaluation on the Google Ads API server is performed on the change_status.last_change_date_time field.

Ensure change_event and change_status have Valid Limits

If either change_event or change_status is the resource in the FROM clause, the query must contain a valid limit, or a positive integer.

Conclusion

We have now created a ValidationService that checks for errors in a GAQL query. Each time a GAQL string changes, we’ll run these checks, generate a list of errors, and emit an event from the Observable in our ValidationService. In the component that subscribes to this Observable, we’ll show an error icon for a non-empty error list. In this post, we’ve covered the various facets of GAQL query validation.


Hopefully this has deepened your understanding of constructing GAQL queries with the Google Ads API. If you have any questions or need additional help, contact us via the forum or at [email protected]

The Query Builder Blog Series: Part 6 – Selecting and Deselecting Fields

This blog series follows the journey of building the new and improved Interactive Google Ads Query Builder tool. Part 5 of this series described how we determine whether or not a field is selectable. In Part 6, we will discuss how we add fields to a Google Ads Query Language (GAQL) string using the SelectionService.

GAQL String State

In order to track which fields have been selected, we need to keep track of the GAQL string’s state, which we can do in an instance variable called selectedFields that has the following interface definition:


interface SelectedFields {
select: string[];
where: Array<{field: string, context: string}>;
orderBy: Array<{field: string, context?: string}>;
limit?: string;
params?: string;
}



The select field holds an array of field names. The where field holds an array of objects, each of which contains a field name and context string. By context, we mean the filtering condition to apply to the field (or operator and operand). For example, if we added the filtering condition, ad_group.id = 1234567890 to the WHERE clause, the field would be ad_group.id, and the context would be = 1234567890. Similarly, the orderBy field holds an array of field names and optional context strings. Here, context is the optional ASC or DESC direction (default ASC). The limit field is the optional string representation of the integer LIMIT. Finally, the optional params field represents the optional string value of the PARAMETERS clause. We don’t need an array here because there is currently only a single option for this clause.

Selecting Fields

Now that we have a data structure in place to track the state of a user’s query, we can implement a method to select a field in any clause.


selectField(field: string, clause: string, context?: string): void {
...
}

If the clause is SELECT, we’ll add the provided field to the select array in selectedFields. When users add a field in the SELECT clause, no context is provided.


If the clause is WHERE, all three parameters are required because a filtering condition containing an operator and operand must be provided as a context string. When users click the checkbox to add a field in the WHERE clause, we’ll open a dialog prompting them to specify the operator, and then an operand once the operator has been set. Depending on the data_type of the field being selected, we’ll pre-populate the list of available operators a user can select, and depending on the operator selected, we’ll show the user a different component to input the operand. Once a user adds the filtering criteria, we’ll join the operator and operand into a single string to create the context string.





If the clause is ORDER BY, context is optional. When a user selects a field in the ORDER BY clause, we’ll call selectField without any context and add the field to the orderBy array of selectedFields without any context. We’ll also present users with radio buttons under the field name to specify an order of ASC or DESC. Upon clicking one of the options, we’ll update the respective field’s entry in the orderBy array to add the order context.




If the clause is LIMIT or PARAMETERS, we’ll update the limit and params entries of selectedFields, respectively, with the provided string as the field parameter. The limit must be a positive integer, the validation for which is performed in the related UI component. The only parameter currently available is include_drafts, and the default value is false. Therefore, we’ll present users with a checkbox in the UI component for PARAMETERS with a single option ‘include_drafts=true’. If the user clicks the checkbox, we’ll pass the string include_drafts=true to selectField as the field.

SELECT Presence

While the logic of selecting fields is straight-forward, there are two rules regarding selectability that we intentionally skipped in part 5. Certain fields must be present in the SELECT clause before they can be inserted into the WHERE or ORDER BY clauses.



Rule #1: with the exception of “core date segments” (segments.date, segments.week, segments.month, segments.quarter, segments.year), all segments and segmenting resources must be present in the SELECT clause before they can be inserted into the WHERE clause.



Rule #2: all segments, segmenting resources, metrics, and attributed resource fields must be present in the SELECT clause before they can be inserted into the ORDER BY clause. In other words, only fields on the resource in the FROM clause can be placed in the ORDER BY clause without first being inserted into the SELECT clause.


In these cases, we’ll present a dialog that enables the user to add the field to the SELECT clause as well as the desired clause in a single step.





Deselecting Fields

In order to deselect fields, we’ll implement a method in the SelectionService called deselectField.


deselectField(field: string, clause: string): void {

}




Deselecting a field is similar to selecting a field. As a safeguard, we’ll first check if the field is selected. Then, if the clause is SELECT, WHERE, or ORDER BY, we’ll remove the deselected field from the respective array entry of selectedFields. In the event the field that is required to be present in SELECT before being added to WHERE or ORDER BY according to the rules described above and is removed from SELECT, we’ll automatically remove the field from SELECT in a single operation. If the clause is LIMIT or PARAMETERS, we’ll update the respective entry of selectedFields to undefined.

Updating the Output

With the selectedFields variable, selectField method, and deselectField method in place, we can now track the state of the query string. In order to keep track of changes throughout the application, we’ll create an Observable in the SelectionService and call next on it each time selectField or deselectField is called. Then, we can subscribe to the observable in any component that needs to be aware of the GAQL query state.

Conclusion

We have now updated the SelectionService to select fields and deselect fields. In this post, we’ve covered:
  • GAQL query and clause structure.
  • Additional detail regarding selectability.
  • Using Observables in Angular.
Hopefully this has deepened your understanding of constructing GAQL queries with the Google Ads API. If you have any questions or need additional help, contact us via the forum or at [email protected]

AdWords API and Scripts: Deprecation of Automatic Placements in the Placement Performance Report

Starting June 10, 2021, the Placement Performance Report for the AdWords API and Google Ads scripts will exclude automatic placements according to data retention policy changes. Afterwards, the Placement Performance Report will only contain the data for managed and excluded placements at the ad group level.

What's changing?
The schema (attributes, segments and metrics) of the Placement Performance Report will remain the same, but only the data for managed and excluded placements will be available. The data for automatic placements that have not been explicitly targeted will be removed from this report, and queries for the automatic placements data (WHERE Id = 0) from this report will return empty results.

Who will be impacted?
All developers that access the Placement Performance Report using the AdWords API or Google Ads scripts today for automatic placements reporting will be affected.

What should you do?
Before June 10, 2021, review and modify the reporting queries in your AdWords API and Google Ads scripts applications to use the Automatic Placement Performance Report instead for automatic placements reporting.

If you have any questions or need additional help, contact us through the Google Ads API and AdWords API forum or at [email protected].

The Query Builder Blog Series: Part 5 – Determining Field Selectability

This blog series follows the journey of building the new and improved Interactive Google Ads Query Builder tool. Part 4 of this series described how we created a ResourceService to display relevant fields given the resource in our FROM clause based on a user’s location in the app. In Part 5, we will discuss how to determine whether a field is selectable or not in a Google Ads Query Language (GAQL) query string.

Background & Objective

The determination of whether a field is selectable, or can be added to a clause of GAQL string, is dependent on both: 1) inherent properties of the main resource in the FROM clause and its fields’ properties as well as 2) the current state of the GAQL query string. The ResourceService we created in Part 4 addresses item (1) because users will only be shown fields that are able to be selected based on those inherent properties.

In order to address item (2), we will create a new service called the SelectionService. The responsibilities of this service will include determining selectability, selecting fields, and deselecting fields. We will discuss determining field selectability in this post, and in Part 6 of this series, we’ll discuss how the SelectionService selects and deselects fields.

Field Compatibility

Not all fields are compatible with each other. When two fields are compatible, both can be present in a GAQL string. If two fields are incompatible, only one of the two fields may be present in any clause of a GAQL string at a time. Therefore, if there is a field in the GAQL string that is incompatible with the field being evaluated, the field being evaluated is not selectable, and the UI will reflect this by presenting users with a corresponding error message.





Implementation


We will keep track of selected incompatible fields for each field on the main resource with an instance variable called incompatibleSelected that maps each field to a Set of its incompatible fields that have been selected.

interface IncompatibleSelected: {[key: string]: Set<string>}


We’ll initialize this incompatibleSelected such that the keys of the map are the aggregated list of fields on the resource, as determined by the ResourceService, and the value of each entry is an empty Set.

Each time a field is selected, we will add the selected field to the incompatibleSelected Set of each field that is incompatible with the selected field. For example, let’s assume that ad_group is the resource in our FROM clause. segments.ad_destination_type is incompatible with metrics.absolute_top_impression_percentage and metrics.active_view_cpm, among other fields. The table below illustrates the incompatibility relationship among these fields.
Field Incompatible Fields
segments.ad_destination_type [metrics.absolute_top_impression_percentage, metrics.active_view_cpm, …]
metrics.absolute_top_impression_percentage [segments.ad_destination_type, …]
metrics.active_view_cpm [segments.ad_destination_type, …]
If segments.ad_destination_type is selected, we’ll add segments.ad_destination_type to the entries for metrics.absolute_top_impression_percentage and metrics.active_view_cpm in the incompatibleSelected map.

Following the addition of segments.ad_destination_type to any clause in our query, a subset of our incompatibleSelected field would look like this.

incompatibleSelected = {

'segments.ad_destination_type': {},
'metrics.absolute_top_impression_percentage': {'segments.ad_destination_type'}
'metrics.active_view_cpm': {'segments.ad_destination_type'}
...
}


Each time a field is deselected, we’ll first check if that field is present in any clause of the query (more detail to be provided in Part 6) because if so, incompatibleSelected should not be updated. For example, if segments.ad_destination_type had been selected in the SELECT and ORDER BY clauses, but was only deselected from ORDER BY, we don’t want to make any adjustments to the incompatibleSelected map because segments.ad_destination_type is still present in the query. However, if the deselected field is absent from all clauses, we’ll delete the deselected field from the Set for each field that is incompatible with the deselected field.

In our example, let’s assume we now deselect segments.ad_destination_type from the SELECT clause, and it is no longer present in the query. Now, we’ll remove this field from the metrics.absolute_top_impression_percentage and metrics.active_view_cpm entries of the incompatibleSelected map. At this point, every entry in the incompatibleSelected map would contain an empty Set.


With this data structure in place, we can create a method called isSelectable that accepts a field name as a parameter. isSelectable returns true if the Set for the field in incompatibleSelected is empty and false if it is not.


  isSelectable(field: string): boolean {
return this.incompatibleSelected[field]?.size === 0;
}

Conclusion

We have now implemented logic into a SelectionService that determines whether a field is selectable. As we display each field to users, we can simply call isSelectable(field) to determine what to show users in the UI. In this post we’ve covered field compatibility and how it relates to whether or not a field is selectable. In Part 6, we’ll build on this section to see how we can select fields, deselect fields, and keep track of the state of the GAQL query string with the SelectionService.

Hopefully this deepened your understanding how to construct GAQL queries using the Google Ads API. If you have any questions or need additional help, contact us via the forum or at [email protected]

The Query Builder Blog Series: Part 4 – Creating the Resource Service

This blog series follows the journey of building the new and improved Interactive Google Ads Query Builder tool. Part 3 of this series described how we used the GoogleAdsFieldService to create a detailed JSON resource schema to serve as the canonical data set for the angular application. In Part 4, we will discuss how to create a resource service that determines which fields are displayed to users in various parts of the application.

Background

One of the benefits of the new Interactive Google Ads Query Builder is that fields are dynamically updated based on user selections to display whether a field is selectable or not, and if not, users are provided with feedback to understand why a given field is not selectable. However, we must first present lists of fields that are available to select in each clause of a Google Ads Query Language (GAQL) string based on the main resource in the FROM clause of that GAQL string. We will create a service called the ResourceService that will contain this logic. Then, we can use Angular’s services and dependency injection model to pull the appropriate fields and their related information into any component.

Objective

The app is designed such that the FROM resource is determined by the user’s current URL. Therefore, the resource in the FROM clause and list of all available fields is constant based on that URL. For the sake of this post, we are only concerned with three GAQL clauses, SELECT, WHERE, and ORDER BY, because these are the only clauses that can be dynamically populated with fields. LIMIT accepts an integer, and PARAMETERS only has a single option.


Within each clause, we will organize each of the available fields into four categories: attribute fields, metrics, segments, and attributed resource fields for a better user experience. Our objective is to create a ResourceService that provides the relevant fields for a given clause and category.

Implementation

We can leverage the resource schema we generated earlier to select the entry for the main resource in the FROM clause as determined by the URL and filter its fields sub-entry to provide only fields that match specific criteria.


Let’s begin by categorizing our fields by clause.
  • Fields in the SELECT clause have the selectable property equal to true.
  • Fields in the WHERE clause have the filterable property equal to true.
  • Fields in the ORDER BY clause have the sortable property equal to true.
We can further break these field lists into attribute fields, metrics, segments, and attributed resource fields. Retrieving the metrics and segments in a given clause is trivial because our resource schema provides a list of metrics and segments (including segmenting resources). For each clause, we’ll include all of the segments and metrics that meet the clause-related criteria listed above.

Similarly, we can retrieve each clause’s main attribute fields by looking at the attributes of the resource in our FROM clause, filtering for those which begin with the resource name followed by a dot, and applying the clause-related filtering criteria above.

All of the other fields in our attributes entry are attributed resource fields. We can generate a list of attributed resources by creating a set of the unique prefixes (i.e., the text that precedes the dot) of the resource’s attributes excluding our main resource. Finally, we can create a list of fields by clause for each attributed resource by selecting the fields that are prefixed with each respective attributed resource’s name.

With all of this logic in place, we can create an interface that exposes methods for returning a list of fields in a given category and clause. Then, any component that is injected with the ResourceService can simply call the corresponding method to retrieve the appropriate list of fields.

Conclusion

We now have created a ResourceService that we can use to display relevant fields to a user constructing a GAQL query based on the clause and category they are viewing in the app. In this post, we’ve covered:
  • GAQL query structure.
  • The various types of fields that can appear in GAQL clauses.
  • Field properties and how they correspond to different GAQL clauses.

Hopefully this has deepened your understanding of constructing GAQL queries with the Google Ads API. If you have any questions or need additional help, contact us via the forum or at [email protected]

The Query Builder Blog Series: Part 3 – Creating a Resource Schema

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.
Hopefully this has deepened your understanding of 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]

The Query Builder Blog Series: Part 2 – Designing a Resource Schema

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]

The Query Builder Blog Series: Part 1 – Setting the Stage

An important part of our role in Developer Relations is gathering your feedback, as developers using our APIs, so that we can improve products and create tools that will make for a better developer experience. This is particularly important now because the Google Ads API is now out of Beta. We have received several important pieces of feedback as it relates to the Google Ads Query Language (GAQL) and constructing GAQL queries. Specifically, we’ve learned that

  • GAQL is a powerful and flexible mechanism for retrieving data from the Google Ads API. In order to get the most out of it and construct queries efficiently, it is important to understand the details and nuances of this query language.
  • The previous version of the Interactive Google Ads Query Builder tool was useful for constructing GAQL query strings. However, there were opportunities to make the query building process faster while exposing some of the logic behind the tool to better understand how GAQL query string validation works.
As a result, we have released a new version of the Interactive Google Ads Query Builder tool that has several benefits, which you can view in the release blog post.

In developing this new tool, we approached the Google Ads API from a user's perspective to better understand developer use cases. Throughout the process, we documented our experience to share how we approach using the Google Ads API. This is the first in a series of blog posts following that journey. The Query Builder Blog Series will include the following topics.

  • Designing a resource schema: designing a detailed JSON resource schema that will serve as the canonical data set for the Interactive Query Builder Angular application.
  • Creating a resource schema: how we can use the GoogleAdsFieldService to create an enhanced resource schema that includes incompatible fields to make building this application easier.
  • Creating a resource service: how to create a resource service that determines which fields are displayed to users in various parts of the application.
  • Determining Field Selectability: how to create a selection service that determines whether a field is selectable or not in a given clause of a Google Ads Query Language (GAQL) query string.
  • Field Selection & Query Validation: how to update the selection service to select fields and validate the GAQL string.
  • Summary: a summary of key lessons learned from this process.

We hope you enjoy and learn from this blog series. Stay tuned for more interesting content.

If you have any questions or need additional help, contact us via the forum or at [email protected]