A very brief introduction to PQL (Publisher Query Language)

9 November 2019

The acronym 'PQL' stands for many things, from Penalized Quasi-Likelihood to Partial Quality Leadership. Multiple query languages also share this acronym, including Program Query Language and Public Query Language. But for users of Google Ad Manager (formerly Doubleclick for Publishers or DFP), the only PQL that matters is Publisher Query Language.

According to Google Ad Manager's Publisher Query Language Developer's Guide, PQL is a "SQL-like language for querying objects" with a "syntax similar to that of SQL", so one must assume that it is pronounced Peequel rather than as the initialism 'P-Q-L'.

Regular users of Google Ad Manager are likely to have been exposed to PQL without necessarily realising it. Here's how to have a peek at PQL:

  1. In Google Ad Manager, Go to Reports > Queries
  2. Create a query that has at least one filter
  3. RUN the query
  4. EXPORT the results with 'Include header' ticked

The exported document will include a PQL query matching your selected filters. (In effect, the filters in the query creator are a no-code way of writing PQL.)

For example, if I select the following filters:

  • Advertiser is any of Volvo (4583060857)
  • Line Item does not contain test
  • Line item end date between 01/01/2019 and 31/10/2019

As follows:

Google Ad Manager filters

Then the exported document contains the following PQL query:

where ((advertiser_id in (4583060857) and not (line_item_name like 'test')) and (line_item_end_date_time >= '2019-01-01' and line_item_end_date_time <= '2019-10-31'))

Note that the PQL query only relates to filters: it does not indicate which dimensions and metrics were selected in the query creator.

If you are like me, you will now be excited to start writing your own PQL queries! Unfortunately it isn't possible to do this in the Google Ad Manager website.

Probably the closest you can come on the website to writing your own queries is entering text strings with Boolean operators into the filter fields in the query creator. However even here the syntax is not the same as in the corresponding PQL query.

For example, to filter on two values, a and b, you would enter the following as filter field text:

"a" AND "b"

This would equate to the following PQL search:

LIKE '%a%' AND LIKE '%b%'

The Google Ad Manager Help documentation outlines more of these pairings:

Filter field search

If you are really keen to write and use your own PQL queries, the Google Ad Manager API is the way to go. For developing your own application, Google offers client libraries and example code for Java, .NET, Python, PHP and Ruby.

Although R is not an officially supported language, analysts may be interested in 'rdfp' which "allows you to use the DoubleClick for Publishers API from R... Manage inventory, create orders, pull reports, and more!"

Not being a developer myself, I was interested to know if it was possible to use PQL with Postman or similar API clients. A member of the Google Ad Manager API team has confirmed on the support forum that:

"It is possible to get Ad Manager data using POSTMAN" [however] "we do not provide support on how to implement this."

When time allows, I'll have a crack at making a SOAP request to the Google Ad Manager API using Postman, and see if I can actually write and use my own PQL queries. If anyone has done this already, I'd be very interested to hear about it...

Leave a Reply

Your email address will not be published. Required fields are marked *

Copyright © James Clark
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram