How to get data from Google AdWords using SSIS

Introduction

Google AdWords is one of the most popular Advertise Platforms. In this article, you will learn how to get data from Google AdWords by calling Google AdWords Web Service method and using drag and drop SSIS XML/SOAP connector (No coding required :)). Google AdWords Web Services allows retrieving various data such as account info, campaigns; as well as writing data, such as creating a campaign, add a keyword. Basically, it allows controlling your AdWords account programmatically instead of using the AdWords dashboard. For demo purposes, we will get data from Google AdWords Account and load it into Microsoft SQL Server (but you can use this technique to save any supported targets in SSIS (e.g. Excel, FlatFile, Oracle, MySQL, IBM DB2, etc.).

If you desire to download a report from AdWords, you must follow a different approach described in an article on how to download AdWords report. It describes how to download a report using ODBC and XML Driver but the concepts introduced there can be applied to SSIS as well.

If you are looking for a similar article for Google DoubleClick for Publisher (Google DFP) then check this article.

You will also learn how to create Google Ads or modify any other objects by calling SOAP API using SSIS REST API Task.

SSIS Tasks/Connectors discussed in this Article

In this article we will use following Connector(s) and Connection Manager to archive full data integration with Google AdWords.

Following SSIS Connectors support OAuth Connectivity. When you use below connectors no coding required to pull data from any OAuth enabled services (e.g. Twitter, Facebook, LinkedIn).

Two different ways to get Google AdWords Data in SSIS

There are two ways you can extract Google AdWords data in SSIS.

1. Use Google Analytics Source (For more info see this article)  (Consume AdWords data using Google Analytics REST API)
2. Use XML Source along with OAuth connection manager (Consume AdWords data using SOAP API)

In this article we will only discuss Approach #2. For approach #1 refer see this link

Advantage of #2 is you get total control of full API set for AdWords but you have to do lot more manual work. For Approach #1 is lot simpler but you get access to limited reports (Dimensions and Metrics).

Google AdWords API Integration Example Screenshot

Below is screenshot of sample package discussed in this article (Download from here). As you can see we tried to demonstrate Reading data and Managing data (e.g. Create Ad) in the same package. You can extend these technique to perform any type of operations in Google AdWords or similar service such as DoubleClick for Publisher

Step-By-Step Tutorial

Here are few high level steps you will have to perform to extract data from Google AdWords API using SSIS. This section assumes you have ClientID and Developer Token to access data. if you don’t have that then read next 2-3 sections.

1. Download and install SSIS PowerPack from here
2. Create new SSIS Project
3. From SSIS toolbox drag DataFlow Task and go to dataflow designer
4. From toolbox of drag ZS XML Source
5. Double click XML Source to configure it
6. Specify API URL from which you want to read data
   Example : https://adwords.google.com/api/adwords/mcm/v201509/ManagedCustomerService
7. Now check Use Credentials check box and select New ZS-OAuth connection manager.
8. On connection manager UI you can select Google from Provider Dropdown.
9. Under Scopes enter following (Scope is basically permission)
   
   https://www.googleapis.com/auth/adwords

   1	https://www.googleapis.com/auth/adwords

    
10. Now click Generate Token and when prompted login and in the last step Accept.
11. If everything went ok then you will see AccessToken and RefreshToken populated.
12. Click Test connection and close the UI by clicking OK
13. Now on XML Source connector Select HTTP Request Method as POST
14. In the Request Body click edit and enter request as below (Replace DEV_TOKEN and CLIENT_ID placeholders below)
    
    XHTML

    <?xml version="1.0" encoding="utf-8"?> <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <soap:Header> <RequestHeader xmlns="https://adwords.google.com/api/adwords/mcm/v201605"> <developerToken>ENTER_YOUR_DEV_TOKEN_HERE</developerToken> <clientCustomerId>ENTER_YOUR_CLIENT_ID_HERE</clientCustomerId> <userAgent>ZappySys SSIS PowerPack v1</userAgent> </RequestHeader> </soap:Header> <soap:Body> <get xmlns="https://adwords.google.com/api/adwords/mcm/{{User::ApiVersion}}"> <serviceSelector> <fields>CustomerId</fields> <fields>Name</fields> </serviceSelector> </get> </soap:Body> </soap:Envelope>

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

    <?xml version="1.0" encoding="utf-8"?> <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <soap:Header> <RequestHeader xmlns="https://adwords.google.com/api/adwords/mcm/v201605"> <developerToken>ENTER_YOUR_DEV_TOKEN_HERE</developerToken> <clientCustomerId>ENTER_YOUR_CLIENT_ID_HERE</clientCustomerId> <userAgent>ZappySys SSIS PowerPack v1</userAgent> </RequestHeader> </soap:Header> <soap:Body> <get xmlns="https://adwords.google.com/api/adwords/mcm/{{User::ApiVersion}}"> <serviceSelector> <fields>CustomerId</fields> <fields>Name</fields> </serviceSelector> </get> </soap:Body> </soap:Envelope>

     

    If you are wondering how did we get above XML fragment for Request Body then Check this article on how to use SoapUI (free 3rd party tool) to create SOAP request Bodyfrom WSDL. Google AdWords has many API endpoints for different service. For each service you have different Api URL and different WSDL. In the next section you can learn how to create correct SOAP Body (for POST) using SoapUI tool.

    For example if you are calling ManagedCustomerService API for version v201609 then your help page URL would be like this
    https://developers.google.com/adwords/api/docs/reference/v201609/ManagedCustomerService

    And on the same page you will see WSDL link below
    https://ads.google.com/apis/ads/publisher/v201702/NetworkService?wsdl
    (Just download that WSDL XML and save to local disk then use with SoapUI to generate XML body to submit for any DFP API call.)

    See this link for more information (Its for Google DoubleClick Ad Platform but you will get an idea)

15. Go to Array handling tab at the bottom and enter text : ns2:entries – By doing this we will treat <ns2:entries> node as array eventhough only one entry found in response (By default content with multiple records treated as array).
    

    Handling Array Element Nodes in SOAP Response

16. On the filter expression click edit and highlight entries node and click ok. This will produce something like below in filter textbox
    
    $.soap:Envelope.soap:Body.ns2:getResponse.ns2:rval.ns2:entries[*]

    1	$.soap:Envelope.soap:Body.ns2:getResponse.ns2:rval.ns2:entries[*]

     
17. Now click preview to see actual data.
18. Click OK to save UI. Now you can connect source to target such as SQL Server or Trash Destination.

NOTE: in above example we used v201605 API version but change it to latest if this API is no more valid. Google expires API version pretty often so keep that in mind if you get depreciated API version error.

Register for Developer Token to access AdWords account data using API

First important step to access any Google AdWords Account is request developer token. Click this link for step by step information. Watch short vide to get overview on entire process to access data via API.

Find your Client ID

Next thing you will need to know is your ClientID. Most of API Requests will require your ClientID. Perform following steps to find your ClientID.

Login to your AdWords Account using email address. At the top you will see your client ID as below screenshot.

 

Connect to Google AdWords API using OAuth

Connecting to any Google API will require OAuth 2.0. SSIS PowerPack has Protocol so you can easily extract data from API without coding complex token extract/renew workflow. Such things usually requires some sort of programming but in our case you will do few clicks as described in Step-By-Step section earlier.

Here is the screenshot of OAuth Connection Manager.

 

Extract data from Google AdWords using SOAP API Calls

When you define filter expression you can check/uncheck “Include Parent Columns” setting. For AdWords SOAP Api you can uncheck so you only extract columns from rvals node in XML Response.

 

SOAP Response Paging / Looping

Many time you will get response which is larger than default page size in AdWords API Call. In that case you can specify few pagination attributes on XML Source Paging tab. For more information on paging options you may refer this article . This article is talking about JSON Source but same options are applicable for XML Source too.

 

Read Google AdWords data and Load into SQL Server

Using XML Source you can extract data in tabular format and then you can map that to SQL Server Target (Use OLEDB Destination / ADO.net Destination).

Write data / Manage AdWords Account via REST API Calls

So far we have seen how to read data from AdWords account. But what if you have to create new records or write/update AdWords data via API Calls in automated manner? No worry – If you have need for that then use SSIS REST API Task which supports adhoc HTTP WebRequests including POST/DELETE/PUT etc. You can also upload files via REST API call using this task. This task supports rich error handling and saving your response into variable or files.

Create Ad under specific AdGroup

Below example shows how to create new ad under specific AdGroup using REST API Task

Notice we have used few placeholders to make things dynamic using SSIS Variables. Once REST API Task is executed you will see new ad created.

Methods for Creating new records

Google AdWords API supports mutate method for writing data. Many service endpoints supports this method. Below example request will create new Ad.

Methods for Reading AdWords data

Google AdWords API supports two methods for reading data. Most of AdWords service endpoints supports GET method also known as Selector method (Its basically sending query in XML format). Seconds method is Query Method. It uses SQL like query language known as AWQL. (Read more about AWQL query language). If you are SQL Programmer then you may like AWQL Query method better than Selector. But either way you will be able to pass following information.

• Columns you want to return
• Filter (e.g. Where Clause)
• Order By
• Paging information (e.g. PageNumber, PageSize)

Get Method (XML Selector)

For Get Method using XML tags to define columns, filters and order by/paging information. Here is example Get method for below select query

Get Request (Get all Active Ads)

Query Method (AWQL Query – SQL like query language)

Below is sample Soap request for AWQL Method to read Ads listed under from specific AdGroups. To learn more AWQL Query Syntax Click Here.

To learn more about which Columns you can use inside your AWQL Query Click here. Note that each service support different fields for Select Clause, Where Clause and Order By Clause. AWQL language can be also used to query Predefined AdWords Reports. The only difference is .. FROM clause is required when you query Reports.

 

Call AdWords Reports API – Download Campaign Performance data

There will be a time you like to download Reports from Adwords. For that  read this article. Its showing how to use ZappySys ODBC Driver to call AdWords Reporting API but concept is same in SSIS Product too due to similar UI elements.

Download Example SSIS Package

Download SSIS 2012 / 2014/ 2016 Sample

Conclusion

Google AdWords data integration can be very challenging without right set of tools. SSIS PowerPack certainly makes it easy when you have to extract/manage Google AdWords data. Innovative drag and drop approach gives you various options to cover simple or most advanced scenarios without relying on any Programming language (e.g. C#, Java, Python, Ruby etc).