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.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.

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/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.

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.

Examples

Coming Soon.