AdWords Query Builder (AWQB)

AdWords Query Builder (AWQB)

The AdWords Query Builder (AWQB) classes can help you generate the AdWords Query Language (AWQL) strings for pulling data from AdWords API reporting and other services.

Use case 1: Building queries for AdWords API reporting services

In order to generate AWQL for reporting services, you can construct a ReportQueryBuilder object to prepare a report query. Here is an example of a resulting AWQL query:

SELECT CampaignId,
           AdGroupId,
           Id,
           Criteria,
           CriteriaType,
           Impressions,
           Clicks,
           Cost
FROM KEYWORDS_PERFORMANCE_REPORT
WHERE Status IN ('ENABLED', 'PAUSED')
DURING LAST_7_DAYS

The select, from, where, duringDateRange and during functions may be chained in any arbitrary order to compose the corresponding query clauses (i.e., SELECT, FROM, WHERE, DURING).

// Create report query to get the data for the last 7 days.
$query = (new ReportQueryBuilder())
    ->select([
        'CampaignId',
        'AdGroupId',
        'Id',
        'Criteria',
        'CriteriaType',
        'Impressions',
        'Clicks',
        'Cost'
    ])
    ->from(ReportDefinitionReportType::CRITERIA_PERFORMANCE_REPORT)
    ->where('Status')->in(['ENABLED', 'PAUSED'])
    ->duringDateRange(ReportDefinitionDateRangeType::LAST_7_DAYS)
    ->build();

The select function takes an array of fields belonging to the report specified in the from function. All fields must belong to the same report. A field can be repeated more than once.

To make it easy for specifying report names in the from function, we generate constants for all supported report names in the ReportDefinitionReportType class. New report names are added for each released version of the API.

For building the WHERE clause, please see its own section below.

The duringDateRange function takes one of the range types (as defined constants in ReportDefinitionDateRangeType). It can be replaced with the during function for arbitrary date ranges.

The build function constructs and returns a ReportQuery object that contains the complete AWQL string.

A ReportQuery object can be used as a string.

// Note: the $query variable is a ReportQuery object.
$var = "Report query: ${query}";            // Embedding, or string interpolation
$var = sprintf('Report query: %s', $query); // Formatting a string
printf('Report query: %s', $query);         // Printing a formatted string
print $query;
echo $query;

Finally, you can convert the ReportQuery object to a string and pass the string to a ReportDownloader object for pulling data from AdWords API reporting services:

// Note: the $query variable is a ReportQuery object.

// Download report as a string.
$reportDownloader = new ReportDownloader($session);
$reportDownloadResult = $reportDownloader->downloadReportWithAwql(
    sprintf('%s', $query),
    $reportFormat,
    $reportSettings
);
print $reportDownloadResult->getAsString();

Use case 2: Building queries for other AdWords API services

In order to generate AWQL for other services that are not reporting services, you can construct a ServiceQueryBuilder object to prepare a service query for any service with a query() method. The list of services and their selectable fields can be found on the Selector Fields developer guide.

As an example of this use case, we will use the ServiceQueryBuilder to build a query for the CampaignService. The result query string will look like this:

SELECT Id,
       Name
WHERE Status = 'ENABLED'
ORDER BY Name DESC
LIMIT 0,50

The select, where, orderByAsc, orderByDesc and limit functions can be chained in any arbitrary order to compose the respective query clauses (i.e., SELECT, WHERE, ORDER BY, LIMIT).

// Create a query to select all enabled campaigns.
$query = (new ServiceQueryBuilder())
    ->select(['Id', 'Name'])
    ->where('Status')->equalTo('Enabled')
    ->orderByDesc('Name')
    ->limit(0, 50)
    ->build();

The select function takes an array of fields. All fields must belong to the same service. Otherwise, the ServiceQueryBuilder will build a syntactically correct AWQL but the AdWords API servers will reject it and return an exception. If the same field name is repeated multiple times, only the first occurrence is used.

For building the WHERE clause, please see its own section below.

The orderByAsc and orderByDesc functions take single field names and can be repeated, each time on a different field you'd like to order by. When building a complex ORDER BY clause, you must make sure that the field names are distinct or you'll get an InvalidArgumentException for repeating field names.

For paging the result set, you can use the limit function which takes 2 integers: start index and page size. The start index is zero-based and must not be a negative number. The page size must be greater than 0.

The build function constructs and returns a ServiceQuery object that contains the complete AWQL string.

A ServiceQuery object can be used as a string.

// Note: the $query variable is a ServiceQuery object.
$var = "Service query: ${query}";            // Embedding, or string interpolation
$var = sprintf('Service query: %s', $query); // Formatting a string
printf('Service query: %s', $query);         // Printing a formatted string
print $query;
echo $query;

Finally, you can convert the service query object to a string and pass the string to a service client object for pulling data from AdWords API services. Typically, you would retrieve and iterate through the results in pages using a loop:

do {
    // Advance the paging offset from previous page.
    if (isset($page)) {
        $query->nextPage();
    }

    // Make a request using an AWQL string.
    // This request will return one page of results at a time.
    $page = $campaignService->query(sprintf('%s', $query));

    if ($page->getEntries() !== null) {
        foreach ($page->getEntries() as $campaign) {
            // Process each result of the current page.
        }
    }
} while ($query->hasNext($page));

Building the WHERE clause

Both the ServiceQueryBuilder and ReportQueryBuilder classes can build the WHERE clause containing one or many logic expressions. To start a logic expression you can use the where function which takes a single field name. Then, you can complete the logic expression by calling one of the following simple or list operator functions:

Simple operator functions:

• equalTo
• notEqualTo
• greaterThan
• greaterThanOrEqualTo
• lessThan
• lessThanOrEqualTo
• startsWith
• startsWithIgnoreCase
• contains
• containsIgnoreCase
• doesNotContain
• doesNotContainIgnoreCase

List operator functions:

• in
• notIn
• containsAny
• containsNone
• containsAll

While a simple operator function takes a single value as an argument, a list operator function takes an array of values. For combining multiple logic expressions, you can call the where function multiple times. Each call appends its expression to the existing expression using the AND operator. Please note that only the AND operator is supported.

For example:

// Simple operator
$queryBuilder1->where('Id')->greaterThanOrEqualTo('2147483648');
// Result query: WHERE Id >= '2147483648'

// List operator
$queryBuilder2->where('Status')->in(['ENABLED', 'PAUSED']);
// Result query: WHERE Status IN ['ENABLED', 'PAUSED'];
// Combining expressions
$queryBuilder
    ->where('Id')->greaterThanOrEqualTo('2147483648')
    ->where('Status')->in(['ENABLED', 'PAUSED']);
// Result query: WHERE Id >= '2147483648' AND Status IN ['ENABLED', 'PAUSED'];

Special notes on querying AdWords API DataService

When querying against the DataService, the ServiceQuery->hasNext and ServiceQuery->nextPage functions require an instance of the CriterionBidLandscapePage or AdGroupBidLandscapePage class to compute the current page size. You can read more details about paging through the DataService results on our developer guide.

For a complete example, please check out the file GetKeywordBidSimulations.php under the examples/AdWords/v201809/Optimization folder.

Alternatives to the AWQB

These AWQB classes are added as new utilities for improving developer experience with the AdWords API without breaking existing code. You can continue to use custom-built AWQL strings, or use the Selector class (developer guide) to query the AdWords API services.