If you’re an advanced Google Ad Manager user, you might have heard of the ‘Publisher Query Language (or PQL). After all, it makes your reporting work faster and simpler.

If PQL is a new term for you, no worries. We’ll walk you through what it does and how publishers can benefit from it.

Before we talk about Publisher Query Language and its reporting capabilities, let’s do a quick brief of Google Ad Manager and it’s reporting feature.

Also read: How to Use Google Ad Manager with AdSense and Ad Exchange

Google Ad Manager Reporting

With Google Ad Manager, you can create reports of every service and task managed on the platform — demand partners, yield partners, AdSense, Ad Exchange, and other linked services.

The reports can be scheduled by day, week, and month. GAM allows filters based on dimensions and metrics. And if you are new to GAM reporting, you can start with predefined templates. Once these reports are generated, you can share them with your team within the dashboard or save as CSV or Excel files.

In short, GAM’s reporting feature helps analyze the data and extract actionable insights that helps publishers increase the overall ad revenue and extract publisher-focused metrics such as click-through rates, eCPMs, etc.

Read more: How to Generate Reports in Google Ad Manager

But, if you want to do more with GAM, you need to take a look at PQL and see how it directly interacts with the Google Ad Manager database and generates different reports accurately?

In this article, you will get to know everything about Publisher Query Language. But let’s clear the basics first.

What is a Query Language?

Before discussing Publisher Query Language, let us first understand what a Query language is. A query language is a programming language used to request data from the database and retrieve data based on the commands or specifications. 

What is a Publisher Query Language?

PQL or Publisher Query Language is an SQL- based language to interact with the Google Ad Manager database and generate reports based on the queries provided by the publisher. PQL requires users to give structured commands to retrieve data and report. 

Users apply filters to narrow the results by using commands like ‘is any of,’ ‘contains,’ ‘does not contain,’ ‘line item end date.’ PQL helps the ad server understand what the user is asking for, retrieves the data based on the queries, and generates reports. 

Also read: Data Discrepancy between Google Ads Manager and Google Analytics

What is Structured Query Language (SQL)?

As you might’ve read, we mentioned SQL in the previous paragraph. To give some context, Structured Query Language (SQL) is a standardised programming language used for managing relational databases and perform various operations on the data in them.

SQL is used not only by database administrators, but also by data analysts and developers who write data and run analytical queries.

The term SQL is pronounced ess-kew-ell or sequel.

SQL is used for the following:

• modifying database table
• Modify index structures
• adding, removing, and updating rows of data; and
• retrieving information from within relational database management systems

SQL queries take the form of commands written as statements. These queries are aggregated into programs which enable users to retrieve, add, or modify data from database tables.

Syntax of Publisher Query Language

The PQL syntax can be recapitulated as: 

[WHERE <condition> {[AND | OR] <condition> …}]

[ORDER BY <property> [ASC | DESC]]

[LIMIT {[<offset>,] <count>} | {<count> OFFSET <offset>}]

<condition> := <property> { = | != } <value>

<condition> := <property> { = | != } <bind variable>

<condition> := <property> IN <list>

<condition> := NOT <property> IN <list>

<condition> := <property> LIKE <wildcard%match>

<condition> := <property> IS NULL

<bind variable> := :<name>

(Source: Google)

PQL keywords are not case-sensitive. For example, take a look at the following keywords: 

• WHERE- The keyword WHERE expresses a set of conditions, optionally conjoined with AND or OR phrases with parentheses ( ). AND or OR phrases filter data based on one or more conditions.  Example– WHERE width = 728

WHERE width = 728 AND height = 90

WHERE (width = 728 AND height = 90) AND id IN (5008, 8745, 3487)

In the above example, the report will contain only data about an ad that meets both the conditions, 728×90 size, and the Advertiser’s ID is the same as stated above. 

Example- WHERE (width = 728 AND height = 90) OR id IN (5000, 5678, 8775)

In this example, the report will contain data about an ad that meets either of the conditions, either the ad with 728 x 90 size or the advertiser’s ID the same as above. 

(Source)

• ORDER BY: This command sorts the results in either ascending (ASC) order or descending order (DESC). In case of any specification, the results generated will be in ascending order by default. 

Example: WHERE id IN (6873, 6979, 9764) ORDER BY id; 

The above command will select the advertisers with these IDs and generate the results in ascending order, by default, 6873, 6979, and 9764.

Example- WHERE id IN (6873, 9764, 7654) ORDER BY DESC id; 

The results generated will be the advertisers’ IDs in descending order, that is, 9764, 7654, and 6873. 

• LIMIT- The LIMIT command limits the number of results of the query. LIMIT can also include <offset>, which will state how many rows, from start to offset, the results will set. Example– WHERE type = ‘AGENCY’ LIMIT 50 OFFSET 100. The resulting report will show data from rows 50 to 100. 

• IN: The command IN compares the value of a property with each item in a list. A matched value means a positive match.

Example: WHERE name IN ( ‘CompanyNameA,’ ‘CompanyNameB’)

The values of which data is requested are comma-separated and enclosed in parentheses. 

• NOT IN: The command NOT IN compares the value of a property with each item in the list. Unmatched values mean a positive match. Like in IN command, the values specified are comma-separated and enclosed in parentheses.  Example: WHERE name NOT IN ( ‘CompanyNameA’ , ‘CompanyNameB’ )

• LIKE: LIKE clause is used in the WHERE clause to look for a specified value in the database using WILDCARD string matching. Example: WHERE name LIKE ‘foo %searchstring% bar’

WHERE name LIKE ‘Aus%.’ 

Being well-versed in SQL will benefit you in writing your PQL queries in Google Ads Manager as Publisher Query Language is quite similar to SQL. 

• IS NULL – Enables publishers to query for objects with an undefined property value. A good example of this is querying for the root AdUnit by querying for an AdUnit with a null parent ID. Example: WHERE parentId IS NULL.
• <bind variable> – GAM user can use Value objects in place of hard-coded <value> values in their PQL query.

Example (Creates a query and enters two variables in place of hard-coded id and status property values):

// Create two mapped parameters: id and status
String_ValueMapEntry[] values = new String_ValueMapEntry[2];
values[0] = new String_ValueMapEntry("id", new NumberValue(null, "123"));
values[1] = new String_ValueMapEntry("status", new TextValue(null, "APPROVED"));

// Create our statement and map our bind variables
Statement statement = new Statement();
statement.setQuery("WHERE id = :id AND status = :status LIMIT 500");
statement.setValues(values);

Conclusion: 

Publisher Query Language is an advanced solution to help advertisers and publishers get accurate reports. PQL, a SQL-based programming language, is used to retrieve data from the Google Ads Manager database regarding eCPMS, CTRs, etc., on specified ads and gives you actionable insights on how you can improve the overall performance of ad campaigns. 

Summary: 

1. Publisher Query Language, i.e., PQL, is an SQL-based query language used to interact directly with the Google Ads Manager database and generate reports based on the specifications. 
2. PQL syntax is structured to help advertisers and publishers write queries in GAM.
3. PQL allows users to apply filters to narrow down their search in the Google Ads Manager database. 
4. Commands like ‘is any of,’ ‘contains,’ ‘does not contain,’ ‘line item end date,’ etc., are used to make queries. 
5. If you are well-versed in SQL, you can quickly write queries in PQL. 
6. PQL helps the ad server understand what the user is looking for and then retrieves data from the GAM database. 

FAQs

---