Download AdWords report using Google Ads API, ODBC, and XML Driver

In the previous article, we learned how to get data from AdWords in SSIS and basically control your AdWords account programmatically, outside the dashboard. In this one, we will continue on how to download AdWords report by using Google Ads API, ODBC, and ZappySys XML Driver. Let’s not waste our time and cut to the chase!

Prerequisites

Google Ads account up and running.
ZappySys ODBC PowerPack installed.

Step-by-Step – Download AdWords report

Get Developer Token

The first important step to access any Google Ads Account is to request a developer token. Click this link for step by step information. Watch a short video to get an overview of the entire process to access data via API.

Get Client Customer Id

Getting Google Ads (formerly AdWords) Client Customer Id

Copy it to a notepad, we will use it later.

Open ODBC Data Sources Manager

Search for "odbc" in your start menu and click on ODBC (64 bits). If you can't find this then you can also go to Start Menu > ZappySys > ODBC PowerPack > Click on ODBC Data Sources (64-Bit).

Open ODBC Data Source

Create ODBC Data Source based on ZappySys XML Driver

Select tab User DSN if you want to create a data source only for your user, or select System DSN to create a data source for all users or for OS services (e.g. SQL Server Agent):

Creating ZappySys XML Driver based data source

Configure authentication in the ODBC data source

Let’s authenticate first:

Select OAuth in Connection Type property.
Click Click to Configure link.
In OAuth Provider property select Google.
Then either select Use Default OAuth App or Use Custom OAuth App. For the latter, you will need to create a Google OAuth App yourself. It’s perfectly safe to use the default application, but if your company policy does not allow it, you may consider creating it yourself.
In Scopes enter:
https://www.googleapis.com/auth/adwords
Then hit Generate Token and hit allow once asked.

Download AdWords report – Configuring authentication in ODBC data source

Proceed with HTTP request configuration

Then go on with configuring the data source:

Configuring HTTP request in ODBC data source to download an AdWords report

Give your data source a name
Then input this URL to download the report:
https://adwords.google.com/api/adwords/reportdownload/v201809
If the above URL does not work, get the working one from Google’s documentation.
Input POST as HTTP Request Method.
In Body enter this text:
__rdxml=<reportDefinition xmlns="https://adwords.google.com/api/adwords/cm/v201809"> <selector> <fields>CampaignId</fields> <fields>Impressions</fields> <fields>Clicks</fields> <fields>Cost</fields> <predicates> <field>CampaignStatus</field> <operator>IN</operator> <values>ENABLED</values> <values>PAUSED</values> </predicates> </selector> <reportName>My Campaign Performance Report</reportName> <reportType>CAMPAIGN_PERFORMANCE_REPORT</reportType> <dateRangeType>LAST_7_DAYS</dateRangeType> <downloadFormat>XML</downloadFormat> </reportDefinition>
This will display the data of CAMPAIGN_PERFORMANCE_REPORT report (campaign Id, impressions, clicks, and cost) for the last 7 days, for all campaigns that are enabled or paused. Make sure to visit the Google documentation page, which contains the information about how to download a different kind of report, how to configure predicates, etc.
In Body Content Type property select Form (application/x-www-form-urlencoded) option.
In HTTP Headers enter your developerToken and clientCustomerId retrieved in previous steps.

Select data filter in ODBC data source

Then hit Select Filter button and select row node, so that Array Filter is set to $.report.table.row[*]

Filtering on AdWords report data

If asked whether you want to treat the row element as an array, click “yes”. You can also go to the Xml Array Handling tab and see if the element row is specified there.

The Results

To view the results press Preview tab and then click Preview Data:

Previewing AdWords report data

Now you can use this data source and get the data into an Excel sheet, Power BI report, SQL Server (would need to create a data source in ZappySys Data Gateway instead) or elsewhere.

Reusing the data source

If your client application supports, you can override data source settings, such as HTTP body, in SQL query using WITH clause. In that case, you won’t need to create multiple data sources for different kind of reports; e.g.:

SELECT * FROM $ (RequestData='@c:\files\report-definition.txt')

SELECT * FROM $ (RequestData='__rdxml=<reportDefinition xmlns="https://adwords.google.com/api/adwords/cm/v201809"> <selector> <fields>CampaignId</fields> <fields>Cost</fields> </selector> <reportName>My Campaign Performance Report</reportName> <reportType>CAMPAIGN_PERFORMANCE_REPORT</reportType> <dateRangeType>LAST_7_DAYS</dateRangeType> <downloadFormat>XML</downloadFormat> </reportDefinition>')

Build a SQL query using the Query Builder:

Use Query Builder to construct SQL query and override data source properties

Conclusion

In this article, we learned how to download AdWords report using ODBC PowerPack and Google API. We created an ODBC data source based on XML Driver, then configured the OAuth authentication, HTTP request and data filtering without a single line of code. Then we previewed AdWords “Campaign Performance Report” data and finally, considered how to reuse the data source we created. Visit https://zappysys.com/blog/category/odbc-powerpack/odbc-app-integration for examples of how to integrate data sources based on ZappySys Drivers into applications.