Eric Nagel

Previously I have posted how to automatically get your revenue figures from ShareASale, Commission Junction, and PepperJam. Having that done, the next step to automate my P&L report was to get the advertising costs from Google using the AdWords API.

First, I had to set-up a MCC account and apply for a developer token. In about 24 hours, my developer token was approved and I was ready to roll.

At the time I’m writing this, the AdWords API is on v2009, but reporting is only available via v13. So my script is written for the v13 service.

AdWords, like some other APIs, transfer data via the SOAP protocol.

Start by downloading the AdWords API PHP Client Library, which includes the SOAP library file. I saved these in a folder called “lib”, knowing I may use the other files at a later time.

The script starts by defining SoapClientFactory:

class SoapClientFactory{
  public static function GetClient(
	  $endpoint, $wsdl = false, $proxyhost = false, $proxyport = false,
	  $proxyusername = false, $proxypassword = false, $timeout = 0,
	  $response_timeout = 30) {
	if (!extension_loaded('soap')) {
	  return new soapclientNusoap($endpoint, $wsdl, $proxyhost, $proxyport,
		$proxyusername, $proxypassword, $timeout, $response_timeout);
	} else {
	  return new soapclientNusoap($endpoint, $wsdl, $proxyhost, $proxyport,
		$proxyusername, $proxypassword, $timeout, $response_timeout);
	}
  }
}

Then define the SOAP headers and set them

$headers =
	'<email>' . $email . '</email>'.
	'<password>' . $password . '</password>' .
	'<clientEmail>' . $client_email . '</clientEmail>' .
	'<useragent>' . $useragent . '</useragent>' .
	'<developerToken>' . $developer_token . '</developerToken>' .
	'<applicationToken>' . $application_token . '</applicationToken>';

$namespace = 'https://adwords.google.com/api/adwords/v13';
$report_service = SoapClientFactory::GetClient($namespace . '/ReportService?wsdl', 'wsdl');
$report_service->setHeaders($headers);

Next, I had to define the job I wanted to run. Read through the Report Rules to figure out what you’re looking for; for this example, I did a summary by AdGroup in one Campaign. Note: “Validating a report job costs only 1 API unit, whereas a failed call toscheduleReportJob will cost 500 API units.”

$report_job =
	'<selectedReportType>AdGroup</selectedReportType>' .
	'<name>My Report Name Here</name>' .
	'<aggregationTypes>Summary</aggregationTypes>' .
	'<campaigns>8675309</campaigns>' .

	'<startDay>' . $dYesterday . '</startDay>' .
	'<endDay>' . $dYesterday . '</endDay>' .

	'<selectedColumns>Campaign</selectedColumns>' .
	'<selectedColumns>CampaignId</selectedColumns>' .
	'<selectedColumns>AdGroup</selectedColumns>' .
	'<selectedColumns>AdGroupId</selectedColumns>' .
	'<selectedColumns>AdGroupStatus</selectedColumns>' .

	'<selectedColumns>Impressions</selectedColumns>' .
	'<selectedColumns>Clicks</selectedColumns>' .
	'<selectedColumns>Cost</selectedColumns>' .

	'';

Of course, use your own Campaign ID – if you omit this, you will get all AdGroups from all Campaigns. My first report omitted this, just so I was able to see the proper value to set in there. In case you were wondering, $dYesterday was set earlier in my script as:

$dYesterday = date("Y-m-d", time()-86400);

The rest is some code I pulled from Google’s sample code, but I had to get it from a cached version of the page, so I’ll put it here, too, so everything’s in one place:

$request_xml =
	'<validateReportJob>' .
	'<job xmlns:impl="https://adwords.google.com/api/adwords/v13" ' .
	'xsi:type="impl:DefinedReportJob">' .
	$report_job .
	'</job>' .
	'</validateReportJob>';

# Validate report.
$report_service->call('validateReportJob', $request_xml);
if ($debug) show_xml($report_service);
if ($report_service->fault) show_fault($report_service);

# Schedule report.
$request_xml =
	'<scheduleReportJob>' .
	'<job xmlns:impl="https://adwords.google.com/api/adwords/v13" ' .
	'xsi:type="impl:DefinedReportJob">' .
$report_job .
	'</job>' .
	'</scheduleReportJob>';
$job_id = $report_service->call('scheduleReportJob', $request_xml);
$job_id = $job_id['scheduleReportJobReturn'];
if ($debug) show_xml($report_service);
if ($report_service->fault) show_fault($service);

# Wait for report to finish.
$request_xml =
	'<getReportJobStatus>' .
	'<reportJobId>' .
	$job_id .
	'</reportJobId>' .
	'</getReportJobStatus>';
$status = $report_service->call('getReportJobStatus', $request_xml);
$status = $status['getReportJobStatusReturn'];
if ($debug) show_xml($report_service);
if ($report_service->fault) show_fault($service);
while ($status != 'Completed' and $status != 'Failed') {
	// echo 'Report job status is "' . $status . '".' . "\n";
	sleep(30);
	$status = $report_service->call('getReportJobStatus', $request_xml);
	$status = $status['getReportJobStatusReturn'];
	if ($debug) show_xml($report_service);
	if ($report_service->fault) show_fault($service);
}

if ($status == 'Failed') {
	echo 'Report job generation failed.' . "\n";
	return;
}

# Download report.
$request_xml =
	'<getGzipReportDownloadUrl>' .
	'<reportJobId>' .
	$job_id .
	'</reportJobId>' .
	'</getGzipReportDownloadUrl>';
$report_url = $report_service->call('getGzipReportDownloadUrl', $request_xml);
$report_url = $report_url['getGzipReportDownloadUrlReturn'];
if ($debug) show_xml($report_service);
if ($report_service->fault) show_fault($service);
echo 'Report is available at "' . $report_url . '".' . "\n";

At this point, we have the URL where the report is available, so I use wget to download it, then extract it.

`/usr/bin/wget -q --output-document=temp/$dYesterday.xml.gz $report_url`;
`/bin/gunzip temp/$dYesterday.xml.gz`;

(Those are backticks, not single quotes) Then I use simplexml_load_file to load the report into a variable, which I can then work with:

$xml = simplexml_load_file('temp/' . $dYesterday . '.xml');
$namespaces = $xml->getNamespaces(true);

The script then loops through the rows of data, converts the data into an easily usable format, then it’s up to you do actually do something with it:

for ($i = 0; $i < count($xml->table->rows->row); $i++) {
	$rsData = array();
	foreach($xml->table->rows->row[$i]->attributes() as $var => $val) {
		$rsData[$var] = (string)$val;
	} // ends foreach($xml->table->rows->row[0]->attributes() as $var => $val)

	print_r($rsData);
	/*Array
	(
		[campaignid] => 8675309
		[campaign] => My Campaign Name
		[adgroupid] => 4815162342
		[adgroup] => First AdGroup Name
		[agStatus] => Enabled
		[imps] => 37
		[clicks] => 1
		[cost] => 300000
	)
	*/
} // ends for ($i = 0; $i < count($xml->table->rows->row); $i++)

If you notice, cost seems really high. I did not pay $300,000 for a single click – you have to massage that figure a bit:

(float)($rsData['cost']/1000000)

Finally, remove the XML file from the server

unlink('temp/' . $dYesterday . '.xml');

And stick some helper functions to the end

function show_xml($service) {
	echo $service->request;
	echo $service->response;
	echo "\n";
}

function show_fault($service) {
	// print_r($service);
	echo "\n";
	echo 'Fault: ' . $service->fault . "\n";
	echo 'Code: ' . $service->faultcode . "\n";
	echo 'String: ' . $service->faultstring . "\n";
	echo 'Detail: ' . $service->faultdetail . "\n";
	exit(0);
}

It looks like a lot of programming, but really, if you just put it all together, and adjust the variables as necessary, you’ll be able to automate the process of importing your AdWords advertising costs into whatever tracking system you use.