{"id":564,"date":"2016-06-13T16:32:55","date_gmt":"2016-06-13T16:32:55","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=564"},"modified":"2025-04-17T14:27:37","modified_gmt":"2025-04-17T14:27:37","slug":"get-data-from-google-adwords-using-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/get-data-from-google-adwords-using-ssis\/","title":{"rendered":"How to get data from Google AdWords using SSIS"},"content":{"rendered":"

Introduction<\/h2>\n
UPDATE:<\/strong>\u00a0ZappySys has released a brand new API Connector for Google AdWords<\/a> which makes it much simpler to\u00a0Read\/Write Google AdWords Data in SSIS\/ODBC<\/strong> compared to the steps listed in this article. You can still use steps from this article but if you are new to API or want to avoid learning curve with API then use newer approach.<\/p>\n

Please visit this page to see all<\/a>\u00a0Pre-Configured ready to use API connectors which you can use in SSIS API Source<\/a> \/ SSIS API Destination<\/a> OR\u00a0API ODBC Driver<\/a> (for non-SSIS Apps such as Excel, Power BI, Informatica).
\n<\/div><\/div>\n

\"\"<\/a>Google AdWords<\/strong> is one of the most popular Advertise Platforms. In this article, you will learn how to get data from Google AdWords<\/em>\u00a0by calling Google AdWords Web Service method and using drag and drop SSIS XML\/SOAP connector<\/a><\/strong> (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\u00a0data from Google AdWords<\/strong> Account and load it into Microsoft SQL Server <\/strong>(but you can use this technique to save any supported targets in SSIS (e.g. Excel, FlatFile, Oracle, MySQL, IBM DB2, etc.).<\/p>\n

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<\/a>. It describes how to download a report using ODBC and XML Driver but the concepts introduced there can be applied to SSIS as well.<\/p>\n

If you are looking for a similar article for Google DoubleClick for Publisher<\/strong> (Google DFP) then check this article<\/a>.<\/p>\n

You will also learn how to create Google Ads<\/strong> or modify any other objects by calling SOAP API using SSIS REST API Task<\/span><\/u>.<\/p>\n

SSIS Tasks\/Connectors discussed in this Article<\/h2>\n

In this article we will use following Connector(s) and Connection Manager to archive full data integration with Google AdWords.<\/p>\n

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

\n\n\n\n\n\n
\"Custom<\/span><\/td>\nREST API Task<\/a><\/span><\/u><\/td>\n<\/tr>\n
\"SSIS<\/span><\/td>\nOAuth Connection Manager<\/span><\/a><\/td>\n<\/tr>\n
\"Custom<\/span><\/td>\nXML Source (REST API, SOAP Call or File data)<\/span><\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

Two different ways to get Google AdWords Data in SSIS<\/h2>\n

There are two ways you can extract Google AdWords data in SSIS.<\/p>\n

    \n
  1. Use Google Analytics Source<\/a> (For more info see this article<\/a>)\u00a0\u00a0(Consume AdWords data using Google Analytics REST API)<\/li>\n
  2. Use XML Source along with OAuth connection manager (Consume AdWords data using SOAP API)<\/li>\n<\/ol>\n

    In this article we will only discuss Approach #2. For approach #1 refer see this link<\/a><\/p>\n

    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).<\/p>\n

    Google AdWords API Integration Example Screenshot<\/h2>\n

    Below is screenshot of sample package discussed in this article (Download from here<\/a>). 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<\/strong> or similar service such as DoubleClick for Publisher<\/strong><\/p>\n

    \"Google<\/a>

    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)<\/p><\/div>\n

    Step-By-Step Tutorial<\/h2>\n

    Here are few high level steps you will have to perform to extract data from Google AdWords API<\/strong> 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.<\/p>\n

      \n
    1. Download and install SSIS PowerPack from here<\/a><\/li>\n
    2. Create new SSIS Project<\/li>\n
    3. From SSIS toolbox drag DataFlow Task and go to dataflow designer<\/li>\n
    4. From toolbox of drag ZS XML Source<\/a><\/li>\n
    5. Double click XML Source<\/strong> to configure it<\/li>\n
    6. Specify API URL from which you want to read data
      \nExample : https:\/\/adwords.google.com\/api\/adwords\/mcm\/v201509<\/strong>\/ManagedCustomerService<\/span><\/li>\n
    7. Now check Use Credentials check box and select New ZS-OAuth<\/strong> connection manager.<\/li>\n
    8. On connection manager UI you can select Google<\/strong> from Provider Dropdown.<\/li>\n
    9. Under Scopes enter following (Scope is basically permission)\n
      https:\/\/www.googleapis.com\/auth\/adwords<\/pre>\n
       <\/li>\n
    10. Now click Generate Token<\/strong> and when prompted login and in the last step Accept.<\/li>\n
    11. If everything went ok then you will see AccessToken<\/strong> and RefreshToken<\/strong> populated.<\/li>\n
    12. Click Test connection<\/strong> and close the UI by clicking OK<\/li>\n
    13. Now on XML Source – Select HTTP Request Method as POST<\/strong><\/li>\n
    14. In the Request Body click edit and enter request as below (Replace DEV_TOKEN and CLIENT_ID placeholders below)\n
      <?xml version=\"1.0\" encoding=\"utf-8\"?>\r\n<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\">\r\n\t<soap:Header>\r\n\t\t<RequestHeader xmlns=\"https:\/\/adwords.google.com\/api\/adwords\/mcm\/v201605\">\r\n\t\t\t<developerToken>ENTER_YOUR_DEV_TOKEN_HERE<\/developerToken>\r\n\t\t\t<clientCustomerId>ENTER_YOUR_CLIENT_ID_HERE<\/clientCustomerId>\r\n\t\t\t<userAgent>ZappySys SSIS PowerPack v1<\/userAgent>\r\n\t\t<\/RequestHeader>\r\n\t<\/soap:Header>\r\n\t<soap:Body>\r\n\t\t<get xmlns=\"https:\/\/adwords.google.com\/api\/adwords\/mcm\/{{User::ApiVersion}}\">\r\n\t\t\t<serviceSelector>\r\n\t\t\t\t<fields>CustomerId<\/fields>\r\n\t\t\t\t<fields>Name<\/fields>\r\n\t\t\t<\/serviceSelector>\r\n\t\t<\/get>\r\n\t<\/soap:Body>\r\n<\/soap:Envelope><\/pre>\n
       <\/p>\n
      \n
      If you are wondering how did we get above XML fragment for Request Body then Check\u00a0this article on how to use SoapUI (free 3rd party tool) to create SOAP request Bodyfrom WSDL<\/a>. Google AdWords\u00a0has 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<\/strong> tool.<\/p>\n
      For example if you are calling ManagedCustomerService\u00a0API for version v201609<\/strong>\u00a0then your help page URL would be like this<\/strong>
      \n      https:\/\/developers.google.com\/adwords\/api\/docs\/reference\/v201609\/ManagedCustomerService<\/a><\/p>\n
      And on the same page you will see WSDL link below<\/strong>
      \n      https:\/\/ads.google.com\/apis\/ads\/publisher\/v201702\/NetworkService?wsdl<\/a>
      \n(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.)<\/p>\n
      See this link for more information<\/a>\u00a0(Its for Google DoubleClick Ad Platform but you will get an idea)<\/p>\n<\/div><\/div><\/li>\n
    15. Go to Array handling tab at the bottom and enter text : ns2:entries<\/strong> – By doing this we will treat <ns2:entries><\/strong> node as array eventhough only one entry found in response (By default content with multiple records treated as array).\n
    16. On the filter expression click edit and highlight entries node and click ok. This will produce something like below in filter textbox\n
      $.soap:Envelope.soap:Body.ns2:getResponse.ns2:rval.ns2:entries[*]<\/pre>\n
       <\/li>\n
    17. Now click preview to see actual data.<\/li>\n
    18. Click OK to save UI. Now you can connect source to target such as SQL Server or Trash Destination<\/a>.<\/li>\n<\/ol>\n
      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.<\/p><\/blockquote>\n
      Register for Developer Token to access AdWords account data using API<\/h2>\n
      First important step to access any Google AdWords Account is request developer token. Click this link<\/a> for step by step information. Watch short vide to get overview on entire process to access data via API.<\/p>\n
      Find your Client ID<\/h2>\n
      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.<\/p>\n
      Login to your AdWords Account using email address. At the top you will see your client ID as below screenshot.<\/p>\n
      \"How<\/a>
      How to find ClientID of your Google AdWords Account (Useful for making SOAP API Calls)<\/p><\/div>\n
       <\/p>\n
      Connect to Google AdWords API using OAuth<\/h2>\n
      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.<\/p>\n
      Here is the screenshot of OAuth Connection Manager.<\/p>\n
      \"Connecting<\/a>
      Connecting to Google AdWords API using OAuth 2.0 in SSIS (Consuming XML\/SOAP API)<\/p><\/div>\n
       <\/p>\n
      Extract data from Google AdWords using SOAP API Calls<\/h2>\n
      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.<\/p>\n
      \"Extract<\/a>
      Extract data from Google AdWords SOAP\/XML Response<\/p><\/div>\n
       <\/p>\n
      SOAP Response Paging \/ Looping<\/h2>\n
      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<\/a> . This article is talking about JSON Source but same options are applicable for XML Source too.<\/p>\n
      \"REST<\/a>
      REST API Pagination – Passing PageNumber inside POST Body (Example: Google AdWords SOAP Api)<\/p><\/div>\n
       <\/p>\n
      \"Configure<\/a>
      Configure REST API Pagination – using Page Number via POST Body Method<\/p><\/div>\n
      Read Google AdWords data and Load into SQL Server<\/h2>\n
      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).<\/p>\n
      \"Extract<\/a>
      Extract data from Google AdWords and load into SQL Server table using SSIS<\/p><\/div>\n
      Write data \/ Manage AdWords Account via REST API Calls<\/h2>\n
      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<\/a> 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.<\/p>\n
      Create Ad under specific AdGroup<\/h3>\n
      Below example shows how to create new ad under specific AdGroup using REST API Task<\/span><\/u><\/p>\n
      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.<\/p>\n
      \"Creating<\/a>
      Creating Google AdWords Ad using SOAP\/XML API in SSIS (REST API Task)<\/p><\/div>\n
      \"Verify<\/a>
      Verify Google Ad created programmatically using SOAP\/XML API Call (SSIS REST API Task)<\/p><\/div>\n
      Methods for Creating new records<\/h2>\n
      Google AdWords API supports mutate<\/strong> method for writing data. Many service endpoints supports this method. Below example request will create new Ad.<\/p>\n
      <?xml version=\"1.0\" encoding=\"utf-8\"?>\r\n<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\">\r\n\t<soap:Header>\r\n\t\t<RequestHeader xmlns=\"https:\/\/adwords.google.com\/api\/adwords\/cm\/v201605\">\r\n\t\t\t<developerToken>{{User::varDevToken}}<\/developerToken>\r\n\t\t\t<clientCustomerId>{{User::varClientId}}<\/clientCustomerId>\r\n\t\t\t<userAgent>My Test AdWords App (AwApi-DotNet\/18.20.0, Common-Dotnet\/3.11.0, .NET CLR\/4.0.30319.18444, gzip)<\/userAgent>\r\n\t\t<\/RequestHeader>\r\n\t<\/soap:Header>\r\n\t<soap:Body>\r\n\t\t<mutate xmlns=\"https:\/\/adwords.google.com\/api\/adwords\/cm\/v201605\">\r\n\t\t\t<operations>\r\n\t\t\t\t<operator>ADD<\/operator>\r\n\t\t\t\t<operand>\r\n\t                <adGroupId>30800928123<\/adGroupId>\r\n\t                <ad xsi:type=\"TextAd\">\r\n\t                    <displayUrl>\/\/zappysys.com\/ad1<\/displayUrl>\r\n\t                    <finalUrls>\/\/zappysys.com\/ad1<\/finalUrls>\r\n\t                    <headline>This is my Ad<\/headline>\r\n\t                    <description1>This is Line 1<\/description1>\r\n\t                    <description2>This is Line 2<\/description2>\r\n\t                <\/ad>            \r\n\t\t\t\t<\/operand>\r\n\t\t\t<\/operations>\r\n\t\t<\/mutate>\r\n\t<\/soap:Body>\r\n<\/soap:Envelope><\/pre>\n
      Methods for Reading AdWords data<\/h2>\n
      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<\/a>. (Read more about AWQL query language<\/a>). 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.<\/p>\n