The goal of the Google Ad Manager reporting feature is to analyze the data and extract actionable insights to help you increase the overall ad revenue and publisher-focused metrics such as click-through rates, eCPMs, etc. But have you ever wondered if there is a SQL-based language that directly interacts with the Google Ad Manager database and generates different reports accurately?
We did. And we thought some of you might find it interesting and useful to look at. So, here’s the answer to this question – Publisher Query Language. Never heard of it before? Don’t worry. In this tutorial, you will get to know everything about Publisher Query Language. But let’s clear the basics first.
What is a Query Language?
A Query Language is simply a programming language that requests the database management system and retrieves data from it by sending queries based on the information and instructions given by the user.
So, What is the Publisher Query Language?
PQL i.e. Publisher Query Language is a standard query language that sits at the heart of the Google Ad Manager database system and helps Google’s API to understand the queries of the user and display the results based on that in Ad Manager reports. Similar to other Structured Query Languages (SQLs), PQL requires users to input structured commands in order to retrieve the data and construct a report.
Take an example of the Google Ad Manager Ad Exchange Historical report. When you apply a filter to narrow down the results, you do it by using commands like “is any of”, “contains”, “does not contain”, and “is none of” for the dimensions.
So, it is the PQL that helps the ad server understand what you are looking for, and then generate a report based on that.
Note: Publisher Query Language only works for filters. It doesn’t work for dimensions and metrics you select in the later step when creating the report.
Syntax of Publisher Query Language:
There are some important commands/clauses that are used in Publisher Query Language that you should know.
[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>
WHERE: WHERE clause is used to filter the records/data and extract the only those data that fulfill a specified condition. The WHERE clause can be combined with two operators – AND or OR.
These AND or OR operators are primarily used to filter data based on more than one condition. When you use AND operator, the record will include the data if both the conditions are fulfilled.
Example 1: WHERE width = 728 AND height = 90;
With this, the query will generate the output when both the conditions i.e. width = 728 and height = 90 is met.
Example 2: WHERE (width = 728 AND height = 90) AND id IN (5008, 8745, 3487);
In the above example, the report will only retrieve the data when the two conditions i.e ad should be in 728×90 size, and the advertiser’s ID is equal.
Example 3: WHERE (width = 728 AND height = 90) OR id IN (5008, 8745, 3487);
Here, the ad should be in size 728×90 or equals the id given in the parentheses. If either of the conditions fulfills, then the query will retrieve the data for the report.
ORDER BY: ORDER BY clause sorts the result in the ascending order and descending order by using the keyword ASC and DESC respectively. However, if you don’t add the keyword, the results will be generated in ascending order by default. For example:
WHERE id IN (5008, 8745, 3487) ORDER BY id;
The above command will select the advertisers with the given IDs and generate the results in the ascending order i.e. 3487, 5008, and 8745.
IN: The IN clause allows you to add multiple values in the WHERE clause. It is equivalent to ‘=’ queries. If any of the values match with the query, then it is a positive match.
WHERE name IN (‘CompanyNameA’, ‘CompanyNameB’);
NOT IN: It is exactly the opposite of the IN clause. NOT IN clause excludes the values added for the condition. 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.
LIMIT: As the name suggests, the LIMIT clause limits the number of results to return to the query.
E.g. WHERE type = ‘AGENCY’ LIMIT 50 OFFSET 50;
In this syntax, OFFSET is optional. The OFFSET clause will skip the offset rows (Agencies) from the beginning and return the remaining 50 rows.
LIKE: The LIKE clause is used in the WHERE clause to search for a specified value in the database. Syntax:
WHERE name LIKE ‘%searchString% ‘;
WHERE name LIKE ‘searchString%’; //this will give values that start with “searchString”
WHERE name LIKE ‘%searchString’; //this will give values that end with “searchString”
WILDCARD: WILDCARD clause is used with a LIKE clause and used as a substitute for one or more characters in the string.
IS NULL: IS NULL clause enables you to create queries/reports for the attributes that do not have any value. E.g.
WHERE parentId IS NULL.
The above syntax will retrieve the data where the parentID field has no value.
Publisher Query Language is pretty much similar to Structured Query Language. So, if you’re well versed in SQL, then it’s easy to write your own PQL queries.
Conclusion
Over the past few decades, Google Ad Manager has introduced hundreds of solutions and updates to facilitate communication between publishers and the ad server. Publisher Query Language is one of such advanced solutions to help the developers and publishers create their own queries and reports. Found it useful? Let us know in the comments.
Cover image source: Stories from Freepik.
