Using the AdWords API Services to collect data on AdWords MCC structures can be slow, especially as your account grows. However, there is an easier way to get this basic structural information on your Account, and the Campaigns, AdGroups, Ads, Keywords, etc., below it. AdWords AdHoc Reporting quickly returns data, including object IDs, names and values, which can be used to create the basic skeleton of your Account. From there, you can find the elements you want to know more about; and then if required, use the AdWords Services for those Objects to flesh out the area you're more interested in, based on the object IDs gathered.
The documentation for the various Report Types shows which column entries can be retrieved for each level of structural data. These column titles can also be obtained programmatically (see the getReportFields method of the ReportDefinitionService). By careful selection of report columns you can obtain enough information to build your model from the selected client account. The resulting reports can come in a number of formats (such as CSV or XML), but selecting CSV allows you to simply feed the response stream through readily available libraries to filter each entry into an object format of your choice. In addition, CSV output is more concise, and so processing can be faster.
Campaigns example
So let's take a look at how we might gather some relevant data about each Campaign in our account. To gather the data from AdWords, we can use an AdWords Query Language request and then insert the data we get back into a local database. Once we have all the data we require, we can make SELECT requests on the local database to build the structural tables we're interested in. Here are the three steps to construct the Campaign:
1. Query campaign data from reports
SELECT CampaignStatus, CampaignId, CampaignName, ExternalCustomerId, CustomerDescriptiveName, Amount, BiddingStrategy, Impressions FROM CAMPAIGN_PERFORMANCE_REPORT DURING YESTERDAY
We first build the AWQL request string, in which we place all the column headings we want from the Campaign Performance Report, as obtained from the Report Types documentation. We also need to state the report type and a date range within which to run. We then use that query to create the Report, also specifying CSV as the download format.
2. Create local campaigns table
CREATE TABLE campaigns (Status STRING, id LONG, name STRING, extCID LONG, custName STRING, amount FLOAT, biddingStrategy STRING, impressions INTEGER)
This statement creates the table, spelling out the column titles and their type in our SQLite table.
3. Fill in campaigns table
INSERT INTO campaigns (Status, id, name, extCID, custName, amount, biddingStrategy, impressions) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
This is the SQL statement for inserting entries into the campaigns table. We will set a parameter value for each "?" using the values we receive in the report download.
Reports format
The general format of a CSV Report is to have a title line, followed by the column titles on the second line, then the results, and finally a "Total" line which gives the sums of any numeric returned columns. So we need to step over the first two lines of the AWQL response, and then insert each remaining line into our local database, until we find the "Total" line, which we can ignore. Here's a sample response from the Ad Extension Report:
"Sample Extension Report" State,Campaign ID,Ad Extension ID,Ad Extension Type active,68637337,14086417,location extension deleted,84039937,21702097,location extension active,85539817,51774217,mobile extension active,94709137,29009257,location extension Total, --, --, --
Here you can see the Title line, followed by the column names we requested from the Report as the second line. The returned columns will be in the same order we specified in the request (unless you request duplicate columns, in which case the second instance is omitted). This allows us to insert these lines into our database table in the proper order. The Total line concludes the report.
We can repeat these tasks for AdGroups, Ads, Keywords and AdCriteria, and then use SQL requests to fetch and print out tables; your own code can utilise the collected information however you wish. The power of using a local SQL database is shown by SQL statements such as
SELECT * FROM campaigns LEFT OUTER JOIN adGroups ON campaigns.id=adGroups.campaignId LEFT OUTER JOIN ads ON ads.adAdGroupId=adGroups.adGroupIdand
SELECT * FROM criteria JOIN keywords WHERE criteria.critType='Keyword' AND keywords.keywordId=criteria.critId
Tips and tricks
There are a number of pitfalls that should be avoided when using this approach to retrieve Account data, however. When requesting certain Reports, for instance, the inclusion of columns that prevent rows with zero impressions from returning will mean you get no results. The KeywordId field in the Ad Performance Report is one such case, and this is spelt out in the Notes column for that field. Other columns are mutually exclusive, as for example ConversionTypeName and Clicks. You should avoid these column headings when downloading structure reports—sticking to the ones listed as "Attributes" in the Behaviour column is best.
Be careful how you name the columns in the tables you create. Remember you will need to request these column headings to get the values from joins, so avoiding duplicate column names across multiple tables will simplify requests. You should also avoid requesting duplicate columns, since they'll be removed from the response, and will confuse the ordering. Refer to the Notes column in the documentation for advice on how that column is formatted, and what it represents.
Notice also that care should be taken picking the first column in your AWQL query, so that you can be sure that we will only ever see the word "Total" at the start of the final line. You could use IDs here, which will only be Longs. However, we've picked the Status column, as it's a String, but has a tightly managed set of available values. This allows us to make a safe assumption when we've handled all the data returned in the Response, without having to worry about parsing the first returned value of each line.
You should also be careful to ensure you get the right type for each column, as the parsing of those values into the correct values from strings will throw exceptions if you receive something unexpected and then call a type-specific string parser on it.
References
- AdWords API Client Libraries for various programming languages
- Report Types documentation
- AdWords Query Language guide
- getReportFields method of ReportDefinitionService