What is Publisher Query Language?
Publisher Query Language (PQL) is a standard query language of the Google Ad Manager (GAM) database system. PQL helps Google’s API better understand user queries and display the results in GAM reports.
In this article, you’ll learn what Publisher Query Language is, what it’s used for, and how it benefits the communication between the ad server and publishers.
What is a Query Language?
A query language is a programming language that sends queries to a database management system and orders to retrieve data from the system in response to the user’s input.
Query languages are often used with relational databases, which store data in tables with predefined relationships between them. The most common query language example is Structured Query Language (SQL), which is widely used and supported by most database management systems.
Query languages can perform a wide range of database operations–selecting and filtering data, joining multiple tables, grouping data, and calculating aggregate statistics.
What is Structured Query Language (SQL)?
SQL (Structured Query Language) is a programming language that simplifies communication with databases. It helps to manage and share data for user analysis.
A database is a tool for collecting and organizing information. Databases can store information about people, products, orders, etc.
Many databases start with a simple setup–a word processing program or a spreadsheet. Transferring them to a database created by a database management system is more efficient once they get larger and more complicated.
Businesses typically use SQL programs to access and analyze the information in their databases. SQL queries are written using simple statements, and the interface is user-friendly, making it easier to learn and understand.
Nonetheless, SQL is very powerful.
With SQL, you can insert, adjust, or delete data from database tables. It also allows to change the database structure by creating, modifying, and deleting tables and other objects.
What is Publisher Query Language (PQL)?
The publisher query language is similar to SQL. However, PQL is designed for the Google Ad Manager database system and includes unique features tailored to ad inventory management.
In general, Publisher Query Language helps Google’s API (application programming interfaces) better understand user queries and display the results in GAM reports. Publishers can use PQL to create custom reports, view performance data, and manage ad inventory.
Simply put, PQL helps the ad server understand what you are looking for and then generate a report based on that.
Syntax of Publisher Query Language
Publisher Query Language and Structured Query Language are very similar. Therefore, writing your own PQL searches is easier if you’re already familiar with SQL. The PQL syntax is structured to help advertisers and publishers write queries in GAM.
You can see some of the examples below from the PQL Developer’s Guide.
WHERE
The WHERE clause is used to filter data and extract only data that fulfills a specified condition. It can be combined with two operators–AND or OR.
AND or OR operators are primarily used to filter data based on more than one condition.
AND operator will include the data if both of the conditions are fulfilled.
Here are 3 examples for you to better understand how this works:
- WHERE width = 728 AND height = 90
The query will generate the output when both of the conditions (width and height) are met.
- WHERE (width = 728 AND height = 90) AND id IN (1234, 5678, 9876)
The report will only retrieve the data when all conditions (728×90 ad size and the advertiser’s ID) are met.
- WHERE (width = 728 AND height = 90) OR id IN (1234, 5678, 9876)
The ad should be in size 728×90 or equal the id in the parentheses. If either of the conditions fulfills, then the query will retrieve the data for the report.
LIKE
The LIKE clause is used in the WHERE clause to search for a specified value in the database.
WILDCARD
The WILDCARD clause is used with the LIKE clause. It’s used as a substitute for one or more characters in the string.
IN
The IN clause enables you to add multiple values in the WHERE clause. It’s equivalent to “=” queries.
If any of the values match the query, then it’s a positive match.
NOT IN
NOT IN clause excludes the values added for the condition.
It’s exactly the opposite of the IN clause.
For example:
- WHERE NOT name IN (‘CompanyNameA’, ‘CompanyNameB’);
This will exclude «CompanyNameA» and «CompanyNameB» from the condition, and it will be a positive match if the two values aren’t included in the query.
ORDER BY
The ORDER BY clause sorts the result in ascending order or descending order.
To do this, you must use the specific keyword–ASC or DESC.
However, if you don’t add the keyword, the results will be generated in ascending order by default.
Here are 2 examples for you to better understand how this works:
- WHERE id IN (1234, 5678, 9876) ORDER BY id
The above command will select the advertisers with the given IDs and generate the results in ascending order (by default).
- WHERE id IN (1234, 5678, 9876) ORDER BY DESC id
The results generated will be the advertisers’ IDs in descending order, that is–9876, 5678, and 1234.
LIMIT
The LIMIT clause limits the number of results which return to the query.
- WHERE type = ‘AGENCY’ LIMIT 50 OFFSET 50
The OFFSET clause (optional) will skip the offset rows from the beginning and return the remaining 50 rows.
In this case, it skips 50 rows (1-50) and returns 50 (51-100).
IS NULL
The IS NULL clause enables you to create queries/reports for the attributes that don’t have any value.
For example:
- WHERE parentId IS NULL
The above syntax will retrieve the data where the parentID field has no value.
PQL in Google Ad Manager Reporting
To retrieve data and construct a report in GAM with Publisher Query Language requires users to input structured commands.
For example, when you use filters for GAM historical reports, you need to use commands like “contains” or “does not contain,” etc.
It’s important to note that in GAM reports, PQL only relates to filters.
The closest thing you can get in GAM reports to writing your own PQL queries is to filter by substring with boolean operators. You can read more about them in the GAM help center and see the example below.
Source: Google Ad Manager
Conclusion
Google Ad Manager has had multiple fixes and updates to help publishers and the ad server communicate more easily, and Publisher Query Language is one of the techniques that helps developers and publishers produce their own queries and reports.
Publishers can use PQL to apply filters to narrow their search in the GAM database, view performance data, and manage their ad inventory. PQL helps the ad server to better understand what the user is looking for.
PQL queries can be run in the GAM UI or through the GAM API, allowing publishers to automate their ad operations and easily share data with other platforms. Although the possibility of using PQL in GAM UI is quite limited, it’s still beneficial to understand the basic commands.
FAQs
What is Publisher Query Language?
Publisher query language is a standard query language of the Google Ad Manager (GAM) database system. PQL helps Google’s API to better understand user queries and display the results based on that in GAM reports.
What are the top 5 query languages?
The most popular and used database query language is SQL. There are very few languages that are used as much. According to Indeed, some of them are XQuery, OQL, SQL/XML, GraphQL, and LINQ.
What are the three types of query language?
There are 3 main types of query languages:
- Procedural Query Language (e.g., COBOL and Fortran) requires the user to specify the steps to achieve the desired results.
- Declarative Query Language (e.g., SQL and XQuery) enables the user to specify what they want without specifying how to achieve it.
- Object-Oriented Query Language (e.g., OQL and LINQ) allows the user to query data stored in an object-oriented database.
