AdWords campaign analysis and data mining using AdWords Scripts

If you run or manage PPC Campaigns of even moderate scale, chances are that you have already felt the need to be able to automatically extract data from AdWords into a custom designed database for reporting/statistical analysis. The use cases for this are numerous. For example, you are looking to

  • Measure AdWords performance when conversions do not happen online or outside the 30 day cookie period default used by AdWords
  • Run reports against custom dimensions. For e.g. you Campaigns are named using a certain convention and contain name of the country/product/brand. You wish to run performance reports for each of these dimensions
  • You want to do deep dive trend analysis and forecasting using past campaign data
  • You need to compare Bing and AdWords performance side by side in the same dashboard

All these scenarios require bringing data out of AdWords. In less tech-savvy environments, users would typically download AdWords data into Excel and do custom analysis. This approach however, remains a tedious, manual process that wanes in utility as data transformation and reporting complexity increase.

Larger accounts have the option of building real-time integrations with AdWords using AdWords API. However, the process for obtaining a production enabled API developer token remains convoluted and long drawn out requiring applicants to carefully follow Google’s RMF guidelines. Over time, this completely ridiculous, innovation killer of a process has become even more cumbersome and Google has privately stated that it discourages small/medium size accounts from pursuing the API route.

Luckily enough, Google recently introduced the AdWords Scripts feature as an alternate means for integrating with AdWords. While not as sophisticated as the API approach, scripts offer a powerful mechanism for automating AdWords data extraction. In the section below, we describe how we build AdWords Data Warehouses using Scripts, MySql and Talend ETL platform

  1. Dumping AdWords data into Google Spreadsheets-With a basic knowledge of JavaScript, users can create scripts inside AdWords to automatically pull data from AdWords and push into Google Spreadsheets. The list of supported metrics/dimensions is not as comprehensive as that with API but still, scripts provide enough data to be able to conduct meaningful analysis
  2. Pulling data from Spreadsheets into MySql-This can be implemented with a Data Integration tool such as Talend. Talend provides a native Google Docs component to fetch data into MySql. Almost any data transformation can be implemented in this stage making it a powerful approach to implementing custom reports.
  3. Automating the entire flow-AdWords scripts can be easily scheduled from within the interface. The automation of data extraction from spreadsheets into MySql can be implemented within the Talend Job as a subjob that triggers when script has finished loading into spreadsheets. The whole job can now be scheduled for daily run either through Windows Task Scheduler or Unix cron.

With this basic integration architecture in place, we can now shift focus to actual data model within MySql. For reporting requirements, it is likely that you will implement a STAR schema based design to support multi-dimensional queries. Having access to raw data opens up new possibilities for campaign reporting and optimization. A common scenario we encounter is for customers to plugin to this data repository using Business Intelligence tools like Tableau, Qlikview, Pentaho to perform deep drill down analysis.

Interested in finding out more? Contact us today to discuss how we might be able to help out with your PPC Campaign Analysis requirements