Querying AdWords using ShufflePoint

Running Queries

The AQL query syntax for AdWords is the basically the same as it is for Google Analytics. A quick example is always best to get started:

SELECT

METRICS aw-ap:Clicks, aw-ap:AverageCpc, aw-ap:AverageCpm, aw-ap:ConversionRate ON COLUMNS

DIMENSIONS aw-ap:Id, aw-ap:AdGroupId, aw-ap:CampaignId, aw-ap:AdGroupName, aw-ap:CampaignStatus, aw-ap:Status ON ROWS

FROM [xxx-xxx-xxxx]

WHERE

TIMEFRAME lastMonth, default

FILTER aw:Status == "ENABLED"

Replace with xxx-xxx-xxxx with your own adwords customer id.

One thing you will observe is that the datasource prefix is of the form "aw-<xxx>", where <xxx> is a report name appreviation. In this sample the appreviation is "ap", which is short for "Ad Performance Report". Unlike Google Analytis, which exposes a traditional star schema with dimensions and metrics, the AdWords API exposes named reports and fields (see below). The aw-xxx syntax is our strategy to make AdWords better fit the semantics of AQL.

Report Types

The table below lists the supported AdWords report types.

Report nameQuery Abbr.
Account Performance acp
Account Reach Frequency arf
Ad Performance ap
Ad Extensions Performance aep
Ad Group Performance agp
Ad Group Reach Frequency agrf
Age Range Performance arpr
Audience Performance aup
Automatic Placements Performance app
Bid Goal Performance bgp
Budget Performance bp
Call Metrics Call Details cmcd
Campaign Performance cp
Campaign Ad Schedule Target cast
Campaign Location Target clt
Campaign Negative Keywords Performance cnkp
Campaign Negative Locations cnl
Campaign Negative Placements Performance cnpp
Campaign Platform Target cptr
Campaign Reach Frequency crf
Click Performance clp
Creative Conversion cc
Criteria Performance crp
Destination URL du
Display Keyword Performance dk
Display Topics Performance dtp
Gender Performance gpr
Geo Performance gp
Keywordless Category kc
Keywordless Query kq
Keywords Performance kp
Paid and Organic Query poq
Placeholder Feed Item pfi
Placement Performance pp
Product Partition ppr
Search Query Performance sqp
Shopping Performance sp
URL Performance up

Report Fields

Since the AdWords API has tabular report semantics, the Google documentation describes the "fields" available for each report. In order to make AdWords compatible with AQL, the fields were categorized as metrics or dimensions depending on if the return value is a number or a string.

Rather than recreate the (constantly changing) list of available fields, we refer users to the Google documentation at http://code.google.com/apis/adwords/docs/appendix/reports.html.

Filters (Predicates)

See http://code.google.com/apis/adwords/docs/reference/latest/ReportDefinitionService.Predicate.Operator.html

AQL operatorAdWords predicate
== EQUALS
!= NOT_EQUALS
=[ IN
![ NOT_IN
> GREATER_THAN
>= GREATER_THAN_EQUALS
< LESS_THAN
<= LESS_THAN_EQUALS
^= STARTS_WITH
^=+ STARTS_WITH_IGNORE_CASE
=@ CONTAINS
=@+ CONTAINS_IGNORE_CASE
!@ DOES_NOT_CONTAIN
!@+ DOES_NOT_CONTAIN_IGNORE_CASE

Long Queries

AdWords queries are long. This is due to both the two-part prefix and the long field names. So AdWords queries will more quickly exceed Excel's 255 character IQY parameter length limit. I always recommend pasting your query into the query shortener utility page at https://www.shufflepoint.com/user/MinQuery.aspx to see if it's too long as is and to get the shortened version or generate a query-specific IQY if even the shortened version exceeds 255 chars.

Date ranges

Since ShufflePoint's query language has many more named timeframes than does AdWords, we choose for the time being to simply convert ShufflePoint timeframes into start and end dates when requesting data from AdWords.

Common Errors

Error: ReportDefinitionError.CUSTOMER_SERVING_TYPE_REPORT_MISMATCH

The error means that the report is not being run against an Advertiser account, but rather an MCC account. We do not support MCC reports in AdWords API.
The fix is to ensure that you send an advertiser account's customer id in the clientCustomerId header when downloading the reports.

Examples

Coming Soon.