Structure Reports

Using the AdWords API Services to collect comprehensive data on your account can be slow, especially as your account grows. However, there is an easier way to get basic structural information for your account—and the campaigns, ad groups, ads, keywords, etc., below it.

AdWords reporting can quickly return data—including object IDs, names and values—which can be used to reproduce the basic skeleton of your account. From there, you can identify elements you want to know more about, and then use their object IDs to retrieve details from the API.

Documentation for the various report types indicates which column entries can be retrieved for each account object. 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 a structure of your account.

For reports, you can select from 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, 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 local SELECT requests to build the structural tables we're interested in. Here are the three steps to construct a campaigns table.

1. 1. Query campaign data from reports

   SELECT CampaignStatus, CampaignId, CampaignName, ExternalCustomerId,
   CustomerDescriptiveName, Amount, BiddingStrategyId, 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. We also need to state the report type and a date range for the query. We then use the query to create the report, also specifying CSV as the download format.

2. 2. Create local campaigns table

   CREATE TABLE campaigns (Status STRING, id LONG, name STRING, extCID LONG,
   custName STRING, amount FLOAT, biddingStrategyId LONG, impressions INTEGER)
   

   This statement creates the table, spelling out the column titles and their type in our SQLite table.

3. 3. Fill in campaigns table

   INSERT INTO campaigns (Status, id, name, extCID, custName, amount,
   biddingStrategyId, 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 Campaign Performance Report:

"CAMPAIGN_PERFORMANCE_REPORT (Apr 1, 2015-Apr 6, 2015)"
Campaign state,Campaign ID,Campaign,Customer ID,Account,Cost,Bid Strategy Name,Impressions
paused,143072203,Search + DSAs,1234567890,My AdWords Account,0, --,0
paused,168682400,Interplanetary Cruise 1396462861759,1234567890,My AdWords Account,0, --,0
paused,168682099,Interplanetary Cruise 1396462699054,1234567890,My AdWords Account,0, --,0
enabled,238539688,Campaign 1234,1234567890,My AdWords Account,5970000, --,902
Total, --, --, --, --,5970000, --,902

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 table in the proper order. The Total line concludes the report.

We can repeat these tasks for ad groups, ads, and criteria—and then use SQL requests to fetch and print out tables. You can modify this example to utilize the collected data however you wish, for instance:

SELECT * FROM campaigns
LEFT OUTER JOIN adGroups ON campaigns.id=adGroups.campaignId
LEFT OUTER JOIN ads ON ads.adAdGroupId=adGroups.adGroupId

SELECT * FROM criteria
JOIN keywords
WHERE criteria.critType='Keyword' AND keywords.keywordId=criteria.critId

Tips and tricks

There are some caveats when using this approach to retrieve account data. When requesting certain reports, the inclusion of columns that prevent rows with zero impressions from returning will mean you will get no results. The KeywordId field in the Ad Performance Report is one such case; this is spelled out in the Notes column for that field, as well as the field having a "False" value in its Support Zero Impressions column.

Other columns can be mutually exclusive, for example, ConversionTypeName and Clicks. You should avoid these column headings when downloading structural data—sticking to ones listed as "Attributes" in the Behavior column is recommended.

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 report types page for specifications on how a column is formatted, and what it represents.

Also take care when selecting the first column in your AWQL query, so that you won't see the word "Total" at the start of the final line. You could use IDs here, which will be Longs. However in the example, we selected the Status column, which contains Strings with a limited set of 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 ensure that 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.

Reference

• AdWords API Client Libraries for various programming languages
• Report Types documentation
• AdWords Query Language guide
• getReportFields method of ReportDefinitionService
• Reporting Concepts guide