Dec.23

Getting Stats from Google AdWords using the API

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.

How To & Tips

Jan.03

Reduce AdWords Bids with Increased CTR

I’ve heard of this trick before, but never really got to play with it until recently… if you increase your CTR (click-through rate), you’ll decrease your minimum bid price.

Recently, I found a keyword that had 0 competition so I took an offer from CJ, direct to merchant (whose landing page stunk for the keyword), and was set at minimum bids of $0.50. After day 1, I increased my budget and was able to drop my bids to only $0.15. Day 3, I went down to $0.10 and still had 0 competition.

Clicks vs. Cost graphs

Day 3 the clicks remained the same as day 2, but look at that drop in cost! Too bad the event I was promoting ended, so my campaign was paused. But it was a huge learning experience – hearing about this and seeing it first-hand are completely different.

How To & Tips

Aug.15

Google AdWords Campaign Optimizer

When I talked about What I Do,I mentioned something in AdWords that really screwed me up for a while. I was referring to the “Optimize Campaign” link.

I thought, “heck, now that I have conversion data, I’ll click this little button, let AdWords manage my bids, and sit back and make some real money!” Boy, was I wrong.

I’ve heard about other people using this and finding Google to hit them in the wallets, too, but I still went through with it. Go ahead, try it… you’ll be sorry you did.

Within one week, Google figured out how to blow past my daily spending limit, and (shamefully) I had a mere 24% ROI. I think the Campaign Optimizer is optimized to use up your budget, so Google makes more money.

Now, I’m back to the old-fashioned way of managing bids: manually. Alright, here’s how I figure out what I’m bidding per keyword:

First, you need to know how much you want to spend per conversion. For the program I’m promoting, I’m willing to spend $7.75 per conversion. Next, in AdWords, get to your Ad Group and click on the Keywords tab. You should see your list of keywords, max bid, conversion rate, etc.

Select a date range with some history, and sort by Conversion Rate. To figure out your max CPC per keyword, multiply your target ($7.75) by the conversion rate. For example, one keyword I have is converting at 19.05%. So $7.75 * .1905 = 1.48. My max CPC on that keyword is 1.48.

What this means is that if 5 people click on my ad for this keyword, I’ll spend at most $7.40, and with a 19% conversion rate (about 1 in 5), I’ll have 1 conversion, which cost me $7.40, just under my target of $7.75.

Make sure you have a good foundation of data before doing this… if you see a 100% conversion rate, based on 1 click, I do not suggest setting your max CPC at $7.75.

Is this a lot of work if you have a lot of keywords? Yes. But money doesn’t come easy, or else everyone would be rich.

Reviews