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].
- Devin Chasanoff, on behalf of the Google Ads API Team