Reports UI to PQL tool

[Kevin]

Hi,

I'm trying to build a query with the API for what I believe to be a rather simple query. The attached image is my query which builds the required report. I was wondering if there was a tool in the reporting admin that might spit out the PQL that is used to build this report.

I'm currently using https://github.com/ShinyAds/node-google-dfp/blob/master/examples/runReport.js#L15-L27 to run a test report. This report returns successfully however, I'm not sure what args I should pass in order to have a report that runs in the DFP admin as illustrated in the attached image.

Any help is appreciated. My network code is 8749

Thank you!

[Tesfamichael Hailegeorgis(DFP API Team)]

Hi,

To clarify:

1. Are you trying to translate the UI report to its API version or is it the other way around?
2. Were you able to run the UI report as shown in the screenshot? If yes, did you get any data? I tried to run the same exact report in the UI for your network (8749) and I got "There is no data for this report. Please check the criteria and try again." message. But, if I remove the filter I get a report data. So, I believe this means there is no data that satisfies the query you specified in the filter for the UI report. The API report break down is not the same as the UI report. These are different reports.

Basically, you will have to run the report on the UI before you try to recreate the same report via the API to validate your use case. The behavior should be the same in both cases. If the UI report doesn't retrieve any data so does the API report.

Thanks,

Tesfamichael Hailegeorgis, DFP API Team

[Kevin]

I am trying to translate from the UI report to the API. The report runs as expected in the UI, I don't know where to begin on how to translate it to the API version.

The "varies" keyword is actually just a placeholder variable. This variable would be changed on a case by case basis using the api. If you would like to see some data, you can change "varies" to "pages"

[Tesfamichael Hailegeorgis(DFP API Team)]

Hi,

Thanks for clarifying.

The API report breakdown for the UI report shown on this thread is as follows:

 Dimension.DATE,  Dimension.LINE_ITEM_ID,Dimension.CUSTOM_CRITERIA

Column.AD_SERVER_IMPRESSIONS,Column.AD_EXCHANGE_LINE_ITEM_LEVEL_IMPRESSIONS,Column.AD_EXCHANGE_LINE_ITEM_LEVEL_REVENUE

 reportQuery.setDateRangeType(DateRangeType.CUSTOM_DATE);

reportQuery.setStartDate( DateTimes.toDateTime("2014-5-22T00:00:00", "America/New_York").getDate()); //America/New_York

reportQuery.setEndDate(  DateTimes.toDateTime("2014-6-21T00:00:00", "America/New_York").getDate());

 StatementBuilder statementBuilder = new StatementBuilder()

 .where("CUSTOM_TARGETING_VALUE_ID IN(12345)");

reportQuery.setStatement(statementBuilder.toStatement());

Note that this breakdown is in Java and you might need to tweak it to suit your requirement.

For the filter statement, you need to pull the ID(s) of the CUSTOM_TARGETING_VALUE_ID using the custom targeting service and put it within the IN clause of the where statement. Here is the Java version of the sample code from our client library.

Thanks,

Tesfamichael Hailegeorgis, DFP API Team

[Kevin]

Thank you, that's very helpful. The only issue that I have now is the value_id. Is it true that I won't be able to search by string? I was hoping to search by string, for example, using the previous example CUSTOM_CRITERIA~pages

I case I can't search by string, is there by any chance a list of ids that I can pull from the dfp frontend?

[Tesfamichael Hailegeorgis(DFP API Team)]

Unfortunately, filtering with string values using the LIKE expression isn't currently supported by the reporting API. Currently, you'll need to use the CUSTOM_TARGETING_VALUE_ID as mentioned on this thread. I believe your best bet would be to pull all the custom targeting value IDs via the API and pick the ones you are interested in and use them in the IN clause.

Thanks,

Tesfamichael Hailegeorgis, DFP API Team

[Kevin]

Thank you. It sure is unfortunate that you can't search by the string value.

I just ran a report based on CUSTOM_TARGETING_VALUE_ID, and I was able to pull the data. Now, how can I associate the value with the ID? A value ID of 102002178518 does very little for me to associate it to a string value.

Thanks again!

[Tesfamichael Hailegeorgis(DFP API Team)]

I agree with your concern, but using the CUSTOM_TARGETING_VALUE_ID is the right workaround. As you did, you can  pull all the custom targeting values for your network and do some manual filtering for the string pattern you wanted and pick those Ids to be used in the IN clause. You can either do a post-processing (you might want to use something like a Java regular expression to filter those) or just do it manually.

Thanks,

Tesfamichael Hailegeorgis, DFP API Team