Apr.15

Handling Products Removed From Datafeeds

I received an email the other day from Garrett, and he asked:

I’m just curious how you might deal with pages/urls on your site that have been indexed (and may be getting good traffic/links), but all of a sudden they’ll disappear if the product happens to drop off the merchant’s feed.

After a couple emails back & forth, I think the best solution would be to find a similar product, and 301 redirect to that product. First, I’m going to change my .htaccess file to pass in the “name” of the item that was requested:

Change

RewriteRule ^(.*)\-p([0-9]+).php$ item.php?ProductID=$2 [L]

To

RewriteRule ^(.*)\-p([0-9]+).php$ item.php?ProductID=$2&cName=$1 [L]

Then edit item.php, and after $rsData = mysql_fetch_array($oResult); (about line 6), add:

if (!$rsData) {
	// We don't have this item anymore!

	// Get the parts of the product name
	// See http://ericnagel.wpengine.com/2010/03/follow-up-on-datafeeds-podcast.html
	$aNameParts = explode('-', $_GET['cName']);

	// Look up similar products in the database
	$cQuery = "select * from products where";
	foreach ($aNameParts as $cNamePart) {
		$cQuery .= " Name like '%" . mysql_real_escape_string($cNamePart) . "%' or ";
	} // ends foreach ($aNameParts as $cNamePart)
	$cQuery = ereg_replace(" or $", "",$cQuery ); // remove the last "or"
	$oResult = mysql_query($cQuery);
	$rsData = mysql_fetch_array($oResult);
	if (!$rsData) {
		// No clue
		header("Location:  /");
		exit();
	} // ends if (!$rsData)
	else {
		$p = 0;
		$nItemID = $cName = '';
		$cOrig = str_replace('-', ' ', $_GET['cName']);

		do {
			similar_text ($cOrig, $rsData['Name'], $fMatch);
			if ($fMatch > $p) {
				$nItemID = $rsData['ProductID'];
				$cName = $rsData['Name'];
				$p = $fMatch;
			} // ends if ($fMatch > $p)
		} while ($rsData = mysql_fetch_array($oResult));

		if ($p > 0) {
			// Best match
			header("Location:  /" . ereg_replace("[^A-Za-z0-9]", "-", $cName) . "-p" . (int)$nItemID . ".php", TRUE, 301);
			exit();
		} // ends if ($p > 0)
		else {
			// No good match
			header("Location:  /");
			exit();
		} // ends else from if ($p > 0)
	} // ends else from

} // ends if (!$rsData)

Now, if product 8675309 used to be at www.greenwhiteandblacktea.com/New-Snow-Sprout-p8675309.php, the script will see it’s no longer in the database, then get all products with “New”, “Snow”, or “Sprout” in the title, compare them to “New Snow Sprout” and redirect the user to the best choice (in this case, www.greenwhiteandblacktea.com/Ancient-Snow-Sprout-p469648555.php)

By default, the redirect is “HTTP/1.1 302 Found”, so we add “, TRUE, 301” to the header call to change it to a permanent redirect.

Of course, for all this to work, you have to be using .htaccess rewrites, otherwise there’s no way to get the name of the missing (and requested) product.

Thanks for the inspiration to this post, Garrett! Hope this answered your question for you!

How To & Tips

Apr.08

Creating an Image Sitemap from a Datafeed

Google just announced an image sitemap format which will help get images from your site indexed. I thought this was a perfect extension to the datafeed series.

The first step is to make Google think we have the images on our server. So inside an “images” folder, create “image.php” like so:

<?php
	include('../vars.php');

	$cQuery = "select * from products where ProductID=" . (int)$_GET['ProductID'] . " limit 1";
	$oResult = mysql_query($cQuery);
	$rsData = mysql_fetch_array($oResult);

	header('Content-Type: image/jpeg');
	$fp = fopen($rsData['Thumbnail'], "r");
	fpassthru($fp);
	fclose($fp);
	exit();
?>

next, at the root of the site, create images.php:

<?php
	header('Content-Type: text/xml');
	include("./vars.php");
?><<?= '?' ?>xml version="1.0" encoding="UTF-8"?>
 <urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"
  xmlns:image="http://www.google.com/schemas/sitemap-image/1.1">
<?php

$aProductsToInclude = array("Green Tea", "White Tea", "Black Tea", "Oolong Tea", "Iced Tea");

$cQuery = "select * from products where (";
foreach ($aProductsToInclude as $cCategory) {
	$cQuery .= "MerchantSubcategory like '%" . $cCategory . "%' or MerchantCategory like '%" . $cCategory . "%' or ";
} // ends foreach ($aProductsToInclude as $cCategory)
$cQuery = ereg_replace(" or $", ")", $cQuery);
$cQuery .= " and Thumbnail<>'' order by Name";
// echo("$cQuery");
$oResult = mysql_query($cQuery);
while ($rsData = mysql_fetch_array($oResult)) {
	?>
	<url>
		<loc>http://www.greenwhiteandblacktea.com/<?= simplify($rsData['Name']) ?>-p<?= $rsData['ProductID'] ?>.php</loc>
		<image:image>
			<image:loc>http://www.greenwhiteandblacktea.com/images/<?= simplify($rsData['Name']) ?>-i<?= $rsData['ProductID'] ?>.jpg</image:loc>
			<image:caption><?= $rsData['Name'] ?></image:caption>
		</image:image>
	</url>
	<?php
} // ends while ($rsData = mysql_fetch_array($oResult))

?>
</urlset>

What this does is creates an image sitemap file.

Finally, add to your .htaccess:

RewriteRule ^images/(.*)\-i([0-9]+).jpg$ images/image.php?ProductID=$2 [L]
RewriteRule ^images.xml$ images.php [QSA]

Now, you have an image sitemap (images.xml) and when you visit an image URL like http://www.greenwhiteandblacktea.com/images/Bao-Zhong-Royale-Oolong-i469648571.jpg , htaccess gets the productID, using fopen opens the image, and passes it through to the browser.

If you were slick, you’d add some error checking and caching to these scripts to make things go quicker.

The tea niche may not be ideal for someone searching for images (think shoes!) but this at least gives you an idea of how to take advantage of this new tool by Google.

How To & Tips

Mar.12

Custom Categories For Your Datafeed Site

I’m so happy to hear from affiliates who have followed in the datafeed series and created websites of their own! The other day, Jen Goode showed me a site she was working on, and brought up a common problem with datafeeds: how to categorize the products.

I was able to do this using simple like queries:

select * from products where MerchantSubcategory like '%green tea%' or MerchantCategory like '%green tea%' order by Name;

But sometimes this isn’t the case. Sometimes you have to (gasp!) manually categorize the products. To do this, start with some changes to the products table:

ALTER TABLE  `products` ADD  `MyCategory` VARCHAR( 10 ) NOT NULL ;
ALTER TABLE  `products` ADD INDEX (  `MyCategory` ) ;

Then create an admin page for you to categorize the products. Use the admin-sas-datafeed.php as your template, but change the inside code to:

<?php

if (!empty($_POST['ProductID']) && !empty($_POST['MyCategory'])) {
	mysql_query("update products set MyCategory='" . myres($_POST['MyCategory']) . "' where ProductID=" . (int)$_POST['ProductID'] . " limit 1");
	echo("<p>The product has been updated.</p>");
} // ends

$cQuery = "select * from products where MyCategory='' limit 1";
$oResult = mysql_query($cQuery);
if ($rsData = mysql_fetch_array($oResult)) {
	?>
	<form name="productForm" method="post" action="<?= $_SERVER['PHP_SELF'] ?>">
		<input type="hidden" name="ProductID" value="<?= (int)$rsData['ProductID'] ?>" />
		<table width="500">
			<tr>
				<td><a href="/item.php?ProductID=<?= (int)$rsData['ProductID'] ?>" target="_blank"><?= $rsData['Name'] ?></a>: </td>
				<td><select name="MyCategory" onChange="document.forms['productForm'].submit();">
					<option value="">Choose</option>
					<option value="green-tea">Green Tea</option>
					<option value="white-tea">White Tea</option>
					<option value="black-tea">Black Tea</option>
					<option value="oolong-tea">Oolong Tea</option>
					<option value="tea-pot">Tea Pot</option>
					<option value="tea-cup">Tea Cup</option>
					<option value="junk">Do Not Use</option>
				</select></td>
			</tr>
			<tr>
				<td></td>
				<td><input type="submit" name="cAction" value="Submit" /></td>
			</tr>
		</table>
	</form>
	<?php
} // ends if ($rsData = mysql_fetch_array($oResult))
else {
	echo("<p><strong>Congratulations</strong>! All products have been categorized!</p>");
} // ends else from if ($rsData = mysql_fetch_array($oResult))
?>

What this does is update the “MyCategory” field for each product. Then, your SQL query on the browse page is simply:

select * from products where MyCategory='green-tea' order by Name;

Don’t worry – when you update the datafeed (by downloading a new zip file and uploading it in the admin tool you built before), the MyCategory field isn’t touched!

Now you can categorize products however you’d like – enjoy!

How To & Tips

Mar.09

Building a Datafeed Site – Step 3

Final piece of the puzzleIn step 3, we’re going to actually display the data saved in the database! So far I’ve held your hand along the way, but now I’m just going to point you in the right direction, because there isn’t one way to do this. Merchants handle categories in their datafeeds differently, so you’re going to have to look at what the merchants you’ve chosen to work with are doing, and adjust accordingly.

Looking at my tea example, I wanted pages for Green Tea, White Tea, Black Tea and Oolong Tea. So (in theory, I don’t do it this way) we could make the Green Tea page /browse.php?cCategory=green+tea, and then pull in all the green teas. How do we do that? This is where you have to think.

What I’m doing is the following:

select * from products where MerchantSubcategory like '%green tea%' or MerchantCategory like '%green tea%' order by Name;

That works nicely for my dataset. However, if you were using the Baghaus datafeed, your query may be

select * from products where Name like '%Tylie Malibu%' order by Price desc;

Once you have your data, loop through it & display the products on the page, with a link to the product details page. You can use a list, or a table… whatever you’d like. In the end, you’ll be linking to individual products, like: /item.php?ProductID=466820102

And on item.php, you’ll do a quick lookup and display the product:

$cQuery = "select * from products where ProductID=" . (int)$_GET['ProductID'] . " limit 1";
$oResult = mysql_query($cQuery);
$rsData = mysql_fetch_array($oResult);
<a href="<?= $rsData['Link'] ?>" title="<?= $rsData['Name'] ?>" rel="nofollow"><?= $rsData['Name'] ?></a>

If you look at my tea site, you’ll notice I’m not using links like those in the blog post. Instead, I’m using .htaccess to rewrite the URLs, to make them “pretty”. You can learn about this in my Follow-up on Datafeeds Podcast blog post.

Finally, one problem you’ll be sure to run into is that merchants often have broken images in their datafeeds. This sucks, but there isn’t much you can do about it.

At first, I had a PHP script that made sure the image existed, and if so displayed it and if not, displayed another one. But, that slowed down the page display considerably. So instead, I use a bit of Javascript to replace broken images with a pre-defined image:

<script language="JavaScript" type="text/javascript">
function ImgError(source){
	source.src = "/images/no-image.gif";
	source.onerror = "";
	return true;
}
</script>

Warren Buffett once commented, “I want to give my kids just enough so that they would feel that they could do anything, but not so much that they would feel like doing nothing”.

In this datafeed series, I want to think the same thing. If I give you a finished site, you haven’t learned anything. However, if I give you the framework, and set you in the right direction, the finished product will be something you developed, not something you copied.

If you’ve followed from Step 1, and I know some of you have, you should be well on your way to a finished site. But if you’re waiting for a template to work off of, here it is:

Download the Zip

By no means is this a website that you can actually launch, but the framework is there for you to build off of.

As always, if you need help, leave a comment, email me, or find me on Twitter @ericnagel

Datafeed Site Navigation

How To & Tips

Mar.08

Building a Datafeed Site – Step 2

By now, we have selected the niche and chosen a template, added some supporting files and created the database, and are now ready to populate the products table with actual products!

The first step, if you haven’t done so already, is to download the datafeed from the ShareASale website. While many merchants offer FTP access, and we could automate this a bit more, it’s easier when getting started to upload the data manually (yeah, uploading a zip of the datafeed is “manual” to me).

You’ll need to create a “temp” folder, chmod it to 777 so the script can write to it, and maybe change where your unzip program sits on your server (line 17)

The script to load the products table isn’t terribly long, but I’m not going to paste the whole thing in this blog post. So…

Download the file here

Then, upload the file to your website, go to yourdomain.com/admin-sas-datafeed.php and click on the “Choose File” button, then select the zip file from your computer, and click “Upload”.
Uploading data

What this does is unzips the .zip file, reads the .txt file and if there’s a new product, adds it to the database. If the product already exists, it gets updated. Finally, any product that wasn’t just added or updated, gets removed.

Repeat as necessary for each merchant that you’re working with.

This step isn’t very long, or difficult (considering I give you everything you need!) but it’s exciting as now you have a database full of products, ready to display on your website!

Next up… we’ll work on the display of the products.

Datafeed Site Navigation

How To & Tips

Mar.07

Building a Datafeed Site – Step 1b

The next step in building a datafeed website is getting the data into the database, but to do this, we need to build an administrative section to your website, and add some helper functions & files.

vars.php was a file seen in Step 1, which I use to store the mysql connection and other variables. Here’s a simplified version of the one I’m using:

<?php
	// MySQL
	$DB_NAME = 'your_db_name';
	$DB_USER = 'your_db_username';
	$DB_PASSWORD = 'your_db_password';
	$DB_HOST = 'your_db_host';

	mysql_connect($DB_HOST, $DB_USER, $DB_PASSWORD);
	mysql_select_db($DB_NAME);

	// Your ShareASale ID
	$nSASID = 132296;

	// Shortcut / Alias
	function myres($cString) {
		return mysql_real_escape_string(stripslashes($cString));
	} // ends function mres($cString)
?>

Change lines 3-6 and 12.

Next, you’ll need to set-up an admin.php page, like so:

<?php
	include('./vars.php');
	include('./admin-password.php');
	$bNavHome = true;
	include('./header.php');
?>
			<div class="post">
				<h2 class="title">GreenWhiteAndBlackTea.com Admin</h2>
				<div class="entry">
					<ul>
						<li><a href="admin-sas-datafeed.php">Upload ShareASale Datafeed</a></li>
					</ul>
				</div>
			</div><!-- ends class="post" -->
<?php
	include('./footer.php');
?>

Change as necessary – this is just an index for your admin section.

and admin-password.php:

<?php
if (!$_SERVER['PHP_AUTH_USER']) {
        Header("WWW-authenticate: basic realm=\"" . $_SERVER['HTTP_HOST'] . " Admin\"");
        Header("HTTP/1.0 401 Unauthorized");
        exit;
}
else {
        if (($_SERVER['PHP_AUTH_USER'] == 'the-username-you-want')
        && ($_SERVER['PHP_AUTH_PW'] == 'ssssshhhhh-super-secret')) {
                // OK!!!!
				$_SESSION['bAdmin'] = true;
                ;
        }
        else {
                Header("WWW-authenticate: basic realm=\"" . $_SERVER['HTTP_HOST'] . " Admin\"");
                Header("HTTP/1.0 401 Unauthorized");
                echo("Login failed.\n");
                exit;
        }
}
?>

Set your username & password that you’d like on lines 8 & 9

Finally, in your MySQL database, create the products table:

CREATE TABLE `products` (
  `ProductID` int(10) unsigned NOT NULL,
  `Name` varchar(255) NOT NULL,
  `MerchantID` int(10) unsigned NOT NULL,
  `Merchant` varchar(255) NOT NULL,
  `Link` varchar(255) NOT NULL,
  `Thumbnail` varchar(255) NOT NULL,
  `BigImage` varchar(255) NOT NULL,
  `Price` float(8,2) unsigned NOT NULL,
  `RetailPrice` float(8,2) unsigned NOT NULL,
  `Category` varchar(50) NOT NULL,
  `Subcategory` varchar(50) NOT NULL,
  `Description` text NOT NULL,
  `Custom1` varchar(255) NOT NULL,
  `Custom2` varchar(255) NOT NULL,
  `Custom3` varchar(255) NOT NULL,
  `Custom4` varchar(255) NOT NULL,
  `Custom5` varchar(255) NOT NULL,
  `Lastupdated` datetime NOT NULL,
  `Status` varchar(50) NOT NULL,
  `Manufacturer` varchar(255) NOT NULL,
  `PartNumber` varchar(255) NOT NULL,
  `MerchantCategory` varchar(255) NOT NULL,
  `MerchantSubcategory` varchar(255) NOT NULL,
  `ShortDescription` varchar(255) NOT NULL,
  `ISBN` varchar(25) NOT NULL,
  `UPC` varchar(25) NOT NULL,
  `SKU` varchar(255) NOT NULL,
  `CrossSell` varchar(255) NOT NULL,
  `MerchantGroup` varchar(255) NOT NULL,
  `MerchantSubgroup` varchar(255) NOT NULL,
  `CompatibleWith` varchar(255) NOT NULL,
  `CompareTo` varchar(255) NOT NULL,
  `QuantityDiscount` varchar(255) NOT NULL,
  `Bestseller` tinyint(1) unsigned NOT NULL,
  `AddToCartURL` varchar(255) NOT NULL,
  `ReviewsRSSURL` varchar(255) NOT NULL,
  `Option1` varchar(255) NOT NULL,
  `Option2` varchar(255) NOT NULL,
  `Option3` varchar(255) NOT NULL,
  `Option4` varchar(255) NOT NULL,
  `Option5` varchar(255) NOT NULL,
  `bActiveProduct` tinyint(1) unsigned NOT NULL,
  UNIQUE KEY `ProductID` (`ProductID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I’m wrapping up the script which loads the data, which will be posted tomorrow.

Datafeed Site Navigation

How To & Tips

Mar.05

Building a Datafeed Site – Step 1

This is the first in a series of how to build a datafeed site. I’m building the site right now, and will share the experience with you.

This will be an intermediate to advanced method of using datafeeds to build a site. If you’re looking for a simple way to get started, go check out Pop Shops or Datafeedr. What I’m going to show you is how to build a high-quality site, driven by a datafeed, for free.

How To & Tips

Mar.04

Jan.26

Tracking ShareASale Commissions with Prosper202

Prosper202 is a powerful affiliate marketing tracking software package, with the ability to give you your revenue, cost, and ROI on your PPC campaigns. But the true power is shown when you automate as much as possible.

I’ve previously written how to use the ShareASale API, but left it up to you to fill in the blanks. In this example, I’m going to show you how to automatically get your ShareASale revenues to post back to Prosper202.

Before we begin, make sure you’re read the first post I wrote, or else this one will go by quickly. What this script does is fetches your sales from the previous day and submits the values and SIDs to your Prosper202 installation.

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

$cURL = 'https://shareasale.com/x.cfm?action=activity&affiliateId=yourid&token=yourtoken&dateStart=' . $dYesterday . '&dateEnd=' . $dYesterday . '&XMLFormat=0';
// $cURL = 'sas.csv';

$fp = fopen($cURL, "r");
if ($fp) {
	$rsMap = array();
	while (empty($rsMap) && (($rsHeadings = fgetcsv($fp, 1000, "|")) !== FALSE)) {
		if (md5(serialize($rsHeadings)) != '5b448a7bdbeea0be7d7f758f5f8ee90b') {
			// echo(md5(serialize($rsHeadings)) . "\n");
			// print_r($rsHeadings);

			while (list($nIndex, $cColumn) = each($rsHeadings)) {
				// echo("$cColumn => $nIndex<br />\n");

				$cColumn = ereg_replace("\(.+\)", "", $cColumn);

				$rsMap[$cColumn] = $nIndex;
			} // ends while (list($nIndex, $cColumn) = each($rsHeadings))
			// print_r($rsMap);
		} // ends
	}

	/*
	Array
	(
		[Trans ID] => 0
		[User ID] => 1
		[Merchant ID] => 2
		[Trans Date] => 3
		[Trans Amount] => 4
		[Commission] => 5
		[Comment] => 6
		[Voided] => 7
		[Pending Date] => 8
		[Locked] => 9
		[Aff Comment] => 10
		[Banner Page] => 11
		[Reversal Date] => 12
		[Click Date] => 13
		[Click Time] => 14
		[Banner Id] => 15
	)
	*/
	while (($rsStatData = fgetcsv($fp, 1000, "|")) !== FALSE) {
		// print_r($rsStatData);
		if (
			(md5(serialize($rsDeal)) != '5b448a7bdbeea0be7d7f758f5f8ee90b') &&
			($rsStatData[$rsMap['Commission']] > 0) &&
			!empty($rsStatData[$rsMap['Aff Comment']]) &&
			($rsStatData[$rsMap['Aff Comment']] != "none")
			) {

			$cPostback = 'http://yourdomain.com/tracking202/static/gpb.php?amount=' . urlencode($rsStatData[$rsMap['Commission']]) . '&subid=' . urlencode($rsStatData[$rsMap['Aff Comment']]);
			// echo("$cPostback\n");
			$fpPostback = @fopen($cPostback, "r");
			if ($fpPostback !== false) {
				fclose($fpPostback);
			} // ends

		} // ends if (!empty($rsDeal))

	} // ends while (($data = fgetcsv($fp, 1000, "|")) !== FALSE)
	fclose($fp);
} // ends if ($fp)

The script creates a URL to grab yesterday’s stats, creates an array of the column headers ($rsMap), loops through the rest of the stats and if:

  • the line isn’t empty
  • the Commission is greater than 0.00
  • there is a SID
  • the SID is not “none”

then it pings your Prosper202 installation and updates the database with the commission for that SID.

Most of the code in this script is just reading the CSV file and getting the column names in place. The real work is done in lines 55-60.

Remember you’re limited to 200 API calls per month with ShareASale, so schedule this script to run daily (via cron).

If you have any questions, please leave them in the comments. And if you haven’t done so already, signup with ShareASale!

How To & Tips

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