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 name | Query 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)
AQL operator | AdWords 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.