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.||Description|
|Account Performance||acp||Reports performance data for your entire account.|
|Ad Performance||ap||Reports performance data for your ads.|
|Ad Extensions Performance||aep||Report performance data for ad extensions.|
|Ad Group Performance||agp||Reports ad group performance data for one or more of your campaigns.|
|Automatic Placements Performance||app||Reports performance data for automatic placements on the content network.|
|Campaign Negative Keywords Performance||cnkp||Reports performance data for negative keywords at the campaign level.|
|Campaign Negative Placements Performance||cnpp||Reports performance data for the negative placements at the campaign level.|
|Campaign Performance||cp||Reports performance data for your campaigns.|
|Creative Conversion||cc||Reports performance data for creative conversions (e.g. free clicks).|
|Criteria Performance||crp||Segments the data by the criterion that triggered the impression, so you can determine how well your criteria are performing.|
|Demographic Performance||dp||Reports performance data for sites by demographic.|
|Destination URL||du||Reports performance data for destination URLs.|
|Geo Performance||gp||Reports performance data by geographic origin.|
|Keywords Performance||kp||Reports performance data for your keywords.|
|Managed Placements Performance||mpp||Reports performance data for your managed placements on the content network.|
|Search Query Performance||sqp||Reports performance data for search queries which triggered your ad and received clicks.|
|URL Performance||up||Reports performance data for URLs which triggered your ad and received clicks.|
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.