MANDY WHITE: Hi, I'm Mandy White.
I'm a developer advocate working on the AdWords API.
PAUL MATTHEWS: And I'm Paul Matthews.
I'm a developer programs engineer, also working on the
AdWords API.
MANDY WHITE: Today we're going to talk to you about a feature
that was introduced into the AdWords API
recently, called AWQL.
PAUL MATTHEWS: So what is AWQL?
AWQL is an AdWords query language.
It's a new feature introduced in v 2012 06
of the AdWords API.
It's SQL-like syntax for performing queries against the
AdWords API.
And it's an alternative to using generic selectors.
It's available for selected AdWords API services,
including Campaign Service, Ad Group
Service, and a few others.
But most importantly, it's also a [INAUDIBLE] querying
the AdHoc reports.
MANDY WHITE: So what is the motivation for introducing
this new query language?
So the traditional methodology used for querying, or making
queries against the AdWords API, generic selectors--
can require quite a substantial amount of code.
AWQL, as we will see in future slides,
greatly simplifies that.
Also the AWQL syntax is much more intuitive.
Any developers already familiar, will code new SQL.
PAUL MATTHEWS: OK, so let's start off with an example
service call.
In this instance we're going to be calling the Campaign
Service, and we're going to be trying to get hold of
Campaign's objects.
So we're going to give it the criteria of CTR of greater 0
or 0.5 or so clickthrough rate.
And we're going to be looking for impressions over 100, and
during the month of September 2012.
So that's the, sort of, clause.
And then we're going to sort the results by order of
impressions.
So this maybe code that you are used to.
But they translate into whatever language you're most
familiar in.
And basically this is the Java API.
Java library for querying the AdWords API.
So they are creating a predicate, couple of
predicates, adding these two selectors, and we're querying
the Campaign Service.
And getting back a page object.
So that's the old code that you're familiar with.
As you can see, it's quite a lot of code to
query a simple request.
So this is the equivalent using the AdWords AWQL.
You can see you're again querying the Campaign Service,
but we're using the query method.
We're passing in the string, which is
similar to an SQL string.
But the main difference here is that you don't have a From
clause, and you know it's more tailored towards AdWords
specific stuff.
But as you'll see, you also get a page object back.
So, the response is the same as what you're used to in
previous requests.
And this is the alternative using the Python library.
But it's a parameterized string.
So this is probably more likely what you'd be using to
query the service.
Again, returning the page object.
So let's break that down.
To start off with, you're supplying a Select, here you
pass in fields that you're interested in.
You give it a Where clause, and the criteria
that goes with that.
During, you're giving it a time period.
And then you Order By, and give it the order of the
results you want.
And finally , Limit.
In this example, we're going to return
the first 50 results.
OK, so Paul's described to you how we use AWQL against the
AdWords API services.
And I'm going to talk to you about how we would use AWQL
against AdHoc reports.
AdHoc reports are very simple to use.
You don't actually need to use a client [INAUDIBLE] to make
queries against AdHoc reports.
Send a post request, HTTP post request would do.
You'll supply some basic HTTP headers.
For example, you provide authorization, content type,
and customer id.
And in the body of the post request, you will provide, for
the old way of doing it using report definitions, you'll
provide an XML fragment.
The key value pair.
The key being underscore underscore rdxml.
And the actual value being the XML fragment, which is your
report definition.
Using AWQL is very similar.
You're parsing in the same headers.
But we replaced the underscore underscore rdxml, with
underscore underscore rd query.
And this query again is a key value pair, the value being
the query string.
Because we cannot actually specify the query string, the
format, sorry, there are no formats within the query
string, we have to specify it separately.
So that's an additional parameter we have to add to
the postBody.
In this case it's underscore underscore fmt.
And the value for that will be the
download format you require.
For example CSV or XML.
You don't have to do it this way.
You can use the AdWords API client libraries
to do this for you.
We have utility libraries that will actually
write queries for you.
And here's some examples.
And now we're looking at a code example of how you would
have done this with AdHoc reports traditionally, with
generic selectors.
And again this is very similar to the code that Paul showed
you for making queries against AdWords API services.
In this case we create a selector.
We specify the field we require.
And then we assemble a report definition object.
Report name, date range type.
What kind of report we require.
And finally we set the download format.
Then we use a utility library, reportUtils, and again
this is Java code.
We have a download report method on report utilities
library class.
And it'll be parsing an authenticated API user.
A report definition object, which [? we ?]
created earlier.
And an alpha string to where we want to write a response.
How we would do that with AWQL, it's a lot simpler.
Lot less code.
Obviously in this case, if you look at it, this is a basic
java string.
It's not parametrized in any way.
It's a select statement.
We specify the fields that we are interested in.
In this case we have to specify a From clause, and the
actual target table will be the report we want to make the
query against.
In this case that's keyword for [INAUDIBLE] report.
We also want to specify in this particular example, we
want impressions greater than 0.
So we don't want with rows that are 0 impressions.
And we want it for yesterday.
We can parametrize that string.
We can use string concatenation in Java, or we
can use a string builder object.
Or we can use a different language and use the kind of
example that Paul showed you earlier.
Then we can query, or make a call against a report
utilities library, again.
This [INAUDIBLE]
download report.
We are parsing the authenticated API user, this
time we're parsing the query string.
And we also have to specify the download format.
But this goes back to the example we showed you in post
request, where we actually had to specify which format we
required, because that could not be included in query.
And the last parameter to the call, is the alpha string
[INAUDIBLE]
write the results to.
So quickly breaking it down, similar to how Paul broke down
the other example.
We have a Select statement.
And in this case we have a mandatory From clause.
And we have a Where clause to define exactly what parameters
we want to use to base the query on.
And we always have to provide a During clause which will
specify the date range for which you want to pull
statistics from the report.
So let's summarize some of the differences for AdHoc reports,
between AdHoc reports, and making calls against the
AdWords API services.
As we've briefly shown, you have to specify which report
you want to make the call against, the query against.
And this will be specified in the From clause of the query.
Also the During clause is mandatory.
You have to specify a date range for which you want to
make the query against.
Also because AdHoc reports supports sorting and paging,
we have to provide an Order By, and a limit, I'm sorry we
cannot provide an OrderBy and Limit clause
for these query strings.
More details on the exact syntax of these queries can be
found at AWQL documentation at the following link.
Paul's now going to summarize the differences between making
AWQL queries against AdWords API services, and against
AdHoc reports.
PAUL MATTHEWS: OK, so firstly the regular service calls.
So with the regular service calls you do not
submit a From clause.
We mentioned it earlier. it is the main difference between
AdHoc reports and service.
Additionally, the During clause, the OrderBy, and the
Limit are all optional.
So you don't have to specify those.
Moving on to AdHoc reports, the
During clause is mandatory.
The From clause is also mandatory.
So you know, there's a lot more that you have to submit
to an AdHoc report.
And the Order By and Limit, as Mandy just mentioned, are
omitted because they cannot be used with the AdHoc reports.
MANDY WHITE: OK, so we talked about how we can use AWQL to
make queries against AdWords API services and
against AdHoc reports.
There's a few tips we put together about how we can make
the best use of AWQL.
One of the key messages we want to pass on here, is that
if you decide to replace your existing generic selector code
with AWQL code, you don't have to actually change the way you
process results.
Provided the queries are effectively the same, provide
the same kind of results, then you can process them exactly
the same way.
So you can just plug-in your AWQL code to replace the
generic selector code.
Also AWQL has a gigantically small impact on report
generation performance.
And so basically you don't really have to worry too much
about any performance decrease when you are using AWQL.
And similarly there's no actual difference in the cost
in terms of API units between [INAUDIBLE]
or AWQL.
One thing we should mention because of some of the quirks
of AWQL, there's a couple of parameters that are not
supported in AWQL that you can use with generic selectors.
Particularly for specifying date ranges.
You can't specify All Time.
Not really quite sure why that is.
And you can't specify Custom Date.
Custom dates can be provided though.
You just provide the date ranges as two
separate time objects.
Date, time objects.
So that's not a problem.
You just can't specify the clause, Custom Date.
So our call to action to you is make sure that you use AWQL
the next time you define a report query.
And so the next one we can show is, how we can do useful
things with AWQL, like create a command line interface tool
for querying report data.
PAUL MATTHEWS: OK, so we're going to step into a quick CLI
sample app demo here.
To mean to show one way in which you could use the AWQL
sort of feature.
So this application is very, very small.
It really didn't take a lot to write.
And I encourage you to do the same if you're just curious
about sort of trying out AWQL for the first time.
So all this does is it passes the query off to the AWQL, to
the endpoints on the services.
And in this case we are going to be
querying the AdHoc report.
So the first thing I do when I get to this point is, I want
to sort of find the information about the table
that I'm interested in querying.
So for the argument's sake of this query, I'm going to be
looking at querying the keywords performance report.
So here on the reports definition page we have a list
of all the fields that are available to us, along with
some information--
some notes in the right hand column, you know the behavior,
and a lot of good information there for you.
So I'll start off by navigating to this page and
then choosing the table I'm interested in.
One good way to find this is, if there's something you know
the name of, and you're trying to find out how to get all the
information about it, is you can just use your regular
built in Find field browser.
So we're going to be looking at
keywords performance report.
So then the next step would be to go through, find all the
fields that you're interested in and decide what your
criteria is for that particular report.
I'm not going to go through and select it all out now, as
it will take a bit too long.
But I have got one that I've prepared earlier.
So again, using that command line tool that I've prepared,
I'm going to be selecting the ad groups, the keyword id, the
keyword text, the keyword match type, impressions,
clicks, and costs from the keyword performance report.
Next up, I want to specify my criteria, which in this case
we're going to be using the impressions greater than 0,
which is the same as saying include 0 impressions False.
And then finally, we're going to say the During, because
that is a mandatory field for AdHoc reports.
So During last week.
And I query this report and hopefully the result should be
displaced on the screen for us in a csv format.
So what you can see here is the response back saying
keyword performance report.
The date of the query.
The first line of the actual csv is, as you'll see is sort
of the header columns.
So I have group id, keyword ID, keyword match type,
impressions, clicks, cost, it's all corresponding to the
criteria, to the Select clause that I've fed into the query.
And then you've got your results.
So they exist down to the second to last line.
And finally on the last line you have the totals, if you're
interested in that.
So here we've got a few different keywords, things
being displayed.
And that concludes the demo of the simple,
AdWords CLI query app.
We may have to go step back into it.
OK.
MANDY WHITE: OK.
Thanks Paul, that's brilliant.
So just to quickly mention, the reports, types,
documentation that Paul was looking at then is available
in the AdWords API documentation.
I'm sure you can easily find it by looking that up.
So having looked at how we can use AWQL against AdWords API
services, and also how we can make use of AWQL
against AdHoc reports.
Looking at a particular example, the command line
interfaces [INAUDIBLE]
AWQL.
Now we've pretty much finished and wrapped up.
And we have the opportunity to answer, that
you ask some questions.
You can answer the questions for us if you want.
The more difficult.
PAUL MATTHEWS: [INAUDIBLE] contributors can be involved
in that, I guess.
MANDY WHITE: Yeah.
So at the bottom there we have a URL.
A very short link URL, which we put in the first slide, but
we forgot to tell you about.
And you can use that slide, it's the moderator page, to
enter questions.
I'm not sure we can do live questions as well.
PAUL MATTHEWS: I don't think we've got the Hangout set up
to ask live questions.
So this time it's just.
MANDY WHITE: Right, so we can't do live questions, but
we can actually answer your questions on
the moderator page.
Next time we do this, we'll make sure we have
the Hangout set up.
PAUL MATTHEWS: Yup.
MANDY WHITE: So let's look at the moderator page.
Paul, you want to go for it?
PAUL MATHEWS: Sure.
OK.
No questions as yet.
MANDY WHITE: OK.
So one of the questions I actually entered myself was,
just to get things going, was do we have to use a From
clause with queries against API services, or not?
It seemed like there was no need to.
PAUL MATTHEWS: Yeah.
So basically with the From clause from the API services
you don't need to submit the, sorry, for queries against the
API services you don't need to submit the From clause because
that's implied by the service in which you
are querying against.
So for instance, if you are querying against the Campaign
Service, you're going to be going querying from
that sort of data.
So you don't really need to specify a From clause.
MANDY WHITE: Right, OK.
So those services, those, I think, six services you
identified on the second slide, they all have an
additional method on them called Query.
So there's Get, Mutate, and now there's
Query on those services.
And Query basically, if you're using query on the Campaign
Service, then you don't need to say from the Campaign
Service because that's implicit.
PAUL MATTHEWS: Mm hmm.
MANDY WHITE: OK.
That's brilliant.
PAUL MATTHEWS: Let's try refreshing the page, that
might bring back some more results.
You can remain hopeful.
MANDY WHITE: Where are the presenters is a popular
question [? as well. ?]
We are here.
PAUL MATTHEWS: We are here.
MANDY WHITE: Let us know if you have any questions by
answering on the moderator page.
PAUL MATTHEWS: Alternatively, if there are many questions
left over after this studio, then we will do our best to
turn them into forum requests, and then respond to them.
MANDY WHITE: So basically, on that subject,
we have an API forum.
We haven't linked to it here.
But you can easily find that by searching on Google for
AdWords API forum.
That's fairly easy to find.
And we have [INAUDIBLE] by both Googlers and talk
contributors.
So you'll get answers there.
You can also ask questions on the YouTube page, and we can
try and do our best to answer them.
I assume comments will be open in there.
So yeah.
That's good.
So yes, I thank you guys.
Thanks for attending our Google Developer live event
today on AWQL.
And we look forward to seeing you again in the future.
PAUL MATTHEWS: And just before we go.
MANDY WHITE: Just before we go.
Final slide.
There's some other [? events ?] coming up.
If you do have any other questions you want to ask,
specific questions about AWQL, or about anything else in the
AdWords API, we have these things called office hours.
Basically we have a bunch of engineers, sitting on the end
of a Hangout for an hour or so.
And you can ask questions there.
These are the times and the schedules.
PAUL MATTHEWS: And this is a face to face.
So you know, you get answers to your questions in real
time, which can be really quite beneficial.
And also open a dialog where you can ask
more than one question.
And also obviously we mentioned sort of regions.
So for instance, in America, New York, on
these office hours.
But that's not to say that you can't join them from Europe to
the New York ones, you know.
They are open to everybody.
It's just that the time zones suit the New York office
probably best.
MANDY WHITE: Yeah.
OK.
So thanks for doing this today.
And we look forward to seeing you again in the future.
PAUL MATTHEWS: Thank you.
MANDY WHITE: Bye bye.