Querying AdWords using ShufflePoint
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:
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
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.
The table below lists the supported AdWords report types.
|Report name||Query Abbr.|
|Account Reach Frequency||arf|
|Ad Extensions Performance||aep|
|Ad Group Performance||agp|
|Ad Group Reach Frequency||agrf|
|Age Range Performance||arpr|
|Automatic Placements Performance||app|
|Bid Goal Performance||bgp|
|Call Metrics Call Details||cmcd|
|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|
|Display Keyword Performance||dk|
|Display Topics Performance||dtp|
|Paid and Organic Query||poq|
|Placeholder Feed Item||pfi|
|Search Query Performance||sqp|
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.
|AQL operator||AdWords predicate|
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/secure-auth/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.
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.
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.