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 using drag and drop SSIS XML/SOAP connector ( No coding required :) ). For demo purpose we will extract data from Google AdWords Account and load into Microsoft SQL Server. But you can use technique save to any supported targets in SSIS (e.g. Excel, FlatFile, Oracle, MySQL, IBM DB2 etc). If you are looking for 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).

Custom SSIS Tasks - Call REST API Webservice (GET, POST, DELETE etc) REST API Task
SSIS OAuth Connection Manager OAuth Connection Manager
Custom SSIS Components - XML Source (File, REST, SOAP) XML Source (REST API, SOAP Call or File data)

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

Google AdWords API Integration Example - Create Ad, Get Accounts, Get Campaigns, Get Ad Groups, Get Ads (Extract AdWords data via SOAP API) , Read data using Selector Method or use AWQL query language

Google AdWords API Integration Example – Create Ad, Get Accounts, Get Campaigns, Get Ad Groups, Get Ads (Extract AdWords data via SOAP API, Read data using Selector Method or use AWQL query language)

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

    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

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

How to find ClientID of your Google AdWords Account (Useful for making SOAP API Calls)

How to find ClientID of your Google AdWords Account (Useful for making SOAP API Calls)

 

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.

Connecting to Google AdWords API using OAuth 2.0 in SSIS (Consuming XML/SOAP API)

Connecting to Google AdWords API using OAuth 2.0 in SSIS (Consuming XML/SOAP API)

 

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.

Extract data from Google AdWords SOAP/XML Response

Extract data from Google AdWords SOAP/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.

REST API Pagination - Passing PageNumber inside POST Body (Example: Google AdWords SOAP Api)

REST API Pagination – Passing PageNumber inside POST Body (Example: Google AdWords SOAP Api)

 

Configure REST API Pagination - using Page Number via POST Body Method

Configure REST API Pagination – using Page Number via POST Body Method

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

Extract data from Google AdWords and load into SQL Server table using SSIS

Extract data from Google AdWords and load into SQL Server table using SSIS

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.

Creating Google AdWords Ad using SOAP/XML API in SSIS (REST API Task)

Creating Google AdWords Ad using SOAP/XML API in SSIS (REST API Task)

Verify Google Ad created programmatically using SOAP/XML API Call (SSIS REST API Task)

Verify Google Ad created programmatically using SOAP/XML API Call (SSIS REST API Task)

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.

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

Posted in Google API and tagged , , , , , , , , , , , , .