Adwords Keyword Performance data pull

If you are handling multiple accounts or a large account at Adwords center managing few tens of thousand of keywords to millions of keywords, you would be interested in tracking the keyword performance over a period of time. You can do so by pulling daily summary report from Google through Adwords API and storing the data in your data mart or data warehouse for further analysis.

Pulling the data through API involves 6 steps:
   1. definedReportJob object - Specifies the type of report you want.
   2. validateReportJob - Validate the report that you would like to get. Returns nothing.
   3. scheduleReportJob - Now schedule the job for above object. This request returns an ID.
   4. getReportJobStatus - Get status of the report. If "Completed" go to next.
   5. getReportDownloadUrl - Retrieve the URL where your report is stored. The returned URL is valid for five minutes. If the URL expires, just call getReportDownloadUrl again. Keep this URL private, since anyone can use it to access your report.
   6. getGzipReportDownloadUrl - Download your report by sending an HTTP GET request.

Following pseudo code and code explains each step execution in more detail.  I use Perl SOAP calls to generate Adwords requests. Also, note the following code works well with API version v13 and Google is transitioning many of its Adwords API to v2009. As of Dec.2009 ReportService was still available only in v13.




Before you get to the first step, build a data structure with job parameters & create a SOAP service -

my $service = SOAP::Lite->service($wsdl)->autotype(0)->readable(1)->proxy($url); 
#- $url = 'https://adwords.google.com/api/adwords/v13/CampaignService' ;
#- $wsdl = $url . '?$wsdl' ; 
#- $userAgent = "My company name. Adwords v13 API"

my  @headers = 
    (SOAP::Header->name('email'            => $email),
     SOAP::Header->name('password'         => $pw),
     SOAP::Header->name('useragent'        => $userAgent),
     SOAP::Header->name('developerToken'   => $developerToken),
     SOAP::Header->name('applicationToken' => $applicationToken) );


my $AWJobParams = 
 {'selectedReportType' => $selectedReportType, 
  'name'               => $reportName,
  'aggregationTypes'   => $aggregationTypes,
  'startDay'           => $startDay,
  'endDay'             => $endDay,
  'selectedColumns'    => $selectedColumns,
  'crossClient'        => $crossClient,
  'campaigns'          => $campaigns
};

#- Example of selected columns
my $selectedColumns = [ qw (
   KeywordId Keyword CampaignId CampaignStatus AdGroupId AdGroupStatus AdGroupMaxCpa AdWordsType Impressions Clicks CTR CPC Conversions ConversionRate Cost AveragePosition QualityScore AverageConversionValue BottomPosition CPM CostPerConverstion CostPerTransaction DailyBudget DefaultCount DefaultValue FirstPageCpc KeywordDestUrlDisplay KeywordStatus KeywordTypeDisplay LeadCount LeadValue MaxContentCPC MaximumCPC MaximumCPM PageViewCount PageViewValue SalesCount SalesValue TopPosition TotalConversionValue Transactions ValuePerClick ValuePerCost CustomerName ExternalCustomerId )];

Job Parameter	What it is
selectedReportType	You can request 11 types of reports including keyword performance report. For more info see http://code.google.com/apis/adwords/docs/developer/DefinedReportJob.html
reportName	Name of report. Provide something that is meaningful with date.
aggregationType	Daily, Weekly, Monthly. This works in combination with selectedReportType and selectedColumns. For more details take a look at http://code.google.com/apis/adwords/docs/developer/adwords_api_reports.html.
startDay	Date of the report. Typically yesterday for daily pull.
endDay	End date of report.
selecteColumns	Columns for this report that you are interested in.
crossClient	1 if you are using MCC.
campaigns	Array. If set to 1 pull all the data for all campaigns under MCC.

1. Building the report object

my $report_job = SOAP::Data->name('job' => $AWJobParams);
$report_job->attr({'xsi:type' => 'DefinedReportJob', 'xmlns' => 'https://adwords.google.com/api/adwords/v13'});

2. Validating the report

$service->call('validateReportJob' => $report_job, @headers);

3. Scheduling the job

my $job_id = $service->call('scheduleReportJob' => $report_job, @headers)->result();

4. Check for job completion status

# Job completed?
my $status = $service->call('getReportJobStatus' => $job_id, @headers)->result();

while ($status ne 'Completed' and $status ne 'Failed') {
    sleep(60);
    $status = $service->call('getReportJobStatus' => $job_id, @headers)->result();
}

5. Get URL to download from and 6. Get the report

# Download report.
my $report_url = $service->call('getGzipReportDownloadUrl' => $job_id, @headers)->result();
my $gzipped_report = LWP::Simple::get($report_url);

This gives you crux of downloading the daily keyword performance report and upload the file to a data warehouse staging environment before processing further. For more robust code you need to handle error or exceptions that happen, create output file before loading into database with appropriate field separators (tab, for example), match fields in input file to columns staging table, etc.

MySQL bulk loading:

LOAD DATA  CONCURRENT LOCAL INFILE $input_file_to_load
 INTO TABLE $staging_table_name
 FIELDS TERMINATED BY '\t'
 ESCAPED BY '\'
 LINES TERMINATED BY '\n'
  (date searchEngine KeywordId Keyword CampaignId CampaignStatus AdGroupId AdGroupStatus AdGroupMaxCpa AdWordsType Impressions Clicks CTR CPC Conversions ConversionRate Cost AveragePosition QualityScore AverageConversionValue BottomPosition CPM CostPerConverstion CostPerTransaction DailyBudget DefaultCount DefaultValue FirstPageCpc KeywordDestUrlDisplay KeywordStatus KeywordTypeDisplay LeadCount LeadValue MaxContentCPC MaximumCPC MaximumCPM PageViewCount PageViewValue SalesCount SalesValue TopPosition TotalConversionValue Transactions ValuePerClick ValuePerCost CustomerName ExternalCustomerId )

Cheers,
Shiva