In 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:

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 @esnagel

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…

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”.

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.

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.

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.

The absolute first thing you need to do is pick a niche. I’m not going to help you here, but because I’ve been talking with Deborah Carney (aka Loxly), I’m going to build a tea site. Loxly manages the American Tea Room affiliate program, so I’m going to use their feed, along with Art of Tea (both ShareASale merchants). After the site is “done”, I’ll look at adding a CJ merchant feed in there.

To find merchants in ShareASale that have datafeeds, login and look under “Find/Join Merchants” and click on “View those with Datafeeds“. To keep things simple, pick a merchant (or two, or three) with a couple hundred products. If you choose ecampus, with 2.7mm products, you’re on your own.

Choosing a domain name isn’t extremely important, but as Vinny O’Hare reminded me, have the keyword in there. So I’m building out GreenWhiteAndBlackTea.com. Just make sure you keep the domain under 35 characters, so you can use it as your AdWords display URL.

Set-up the hosting, and make sure you create a MySQL database when you do this. We’re going to save the datafeed in MySQL, to make a dynamic site. Don’t worry, I’ll give you the products table structure… all 41 fields!

Next, find a template from Free CSS Templates that matches the theme of your niche. You’ll see on GreenWhiteAndBlackTea.com I’m using the Begrimed theme.

Themes from Free CSS Templates include a single index.html file, but I want you to break this into 3 php files: header.php, index.php, and footer.php. Index.php should include header.php, then have the content unique to the index page, then include footer.php. My index.php file is simply:

<?php
	include('./vars.php');
	$bNavHome = true;
	include('./header.php');
?>
<div class="post">
	<h2 class="title">Welcome to GreenWhiteAndBlackTea.com</h2>
	<div class="entry">
		<p>This is <strong>Begrimed  </strong>, a free, fully standards-compliant CSS template designed by FreeCssTemplates<a href="http://www.nodethirtythree.com/"></a> for <a href="http://www.freecsstemplates.org/">Free CSS Templates</a>.  This free template is released under a <a href="http://creativecommons.org/licenses/by/2.5/">Creative Commons Attributions 2.5</a> license, so you’re pretty much free to do whatever you want with it (even use it commercially) provided you keep the links in the footer intact. Aside from that, have fun with it  </p>
		<p>Sed lacus. Donec lectus. Nullam pretium nibh ut turpis. Nam bibendum. In nulla tortor, elementum ipsum. Proin imperdiet est. Phasellus dapibus semper urna. Pellentesque ornare, orci in felis. Donec ut ante. In id eros. Suspendisse lacus turpis, cursus egestas at sem.</p>
	</div>
</div><!-- ends class="post" -->
<?php
	include('./footer.php');
?>

Don’t worry about vars.php right now.

That’s enough for one day. Next, we need to download the datafeeds, and get them into MySQL.

If you follow me on Twitter (which you should) or are my friend on Facebook, you’ll notice I’ve been doing a lot of running this week. Well get used to it, because I’m participating in the Affiliate Summit East 2010 Nike+ Challenge, where Affiliate Summit will donate $1 to the Avon Breast Cancer Crusade for each mile run (up to $5,000).

This actually works out perfectly for me. First, I’ve been meaning to run more, and now I have a reason to. Second, if you put a challenge in front of me, I’ll probably take it (except for the Ole 96er – I will not participate in eating contests anymore).

Finally, there’s been too much breast cancer in my family, especially lately, so anything I can do to help raise funds for awareness, access to care and finding a cure, I’ll be sure to help.

My goal is to run 250 miles over the 24-week period. While outside, I’ll be using my Garmin Forerunner for accurate recording (then converting the Garmin tcx file and uploading it to the Nike Running site) and while inside (treadmill running), I’ll be using the Nike+ kit.

In addition to Affiliate Summit’s donation on my behalf, I’ll be donating $1 for every mile I run to Roswell Park here in Buffalo. Roswell Park is America’s first cancer center, founded in 1898. The people at Roswell Park do great things for their patients and are focused on their mission to understand, prevent and cure cancer.

Your encouragement is greatly appreciated! There will be plenty of days I don’t want to run, and my running buddies have already been a great help this week.

I was listening to the “both sides of the tracks” podcast about Sports Marketing, Datafeeds and Traffic Geyser that Jason, Joe & I recorded earlier this week, and realized I forgot a point that I wanted to cover.

Joe was talking about a custom script he had written which generated tens of thousands of HTML pages on his server (one per product). We then jumped into PopShops and DataFeedr, completely skipping over how I handle datafeeds! So I’m writing this blog post to outline another solution.

By using .htaccess, PHP and a database, you can save the datafeed in your MySQL database and use a single PHP file with some .htaccess magic to make it appear as though you have thousands of pages on your site.

The basic concept is you’ll have a URL, such as mydomain.com/kabusecha-plum-123.html but that file does not exist on your server! Instead, .htaccess intercepts the request, and rewrites it to a dynamic PHP file (item.php). The .htaccess will look like:

RewriteEngine On
RewriteRule (.*)\-([0-9]+).html$ item.php?nItemID=$2 [L]

What this does is finds any requests for a file that ends in -##.html (dash, any number, dot html) and tells the server what the user really meant was item.php?nItemID=## (whatever that number was). Now your item.php file connects to the database, pulls up record $_GET['nItemID'] and inserts that information in your template.

Now when you have to update your template, you update just the item.php file, and all of your “.html” files are automatically updated!

I’m in talks with Loxly on coming up with a full case study on how to effectively use datafeeds for an affiliate website.

There’s not much worse than screwing an affiliate out of a bonus he/she earned. If you’re going to do this, don’t get caught red handed. Not like panties.com did.

I mentioned panties.com’s contest in the Feb-1 both sides of the tracks podcast, “Jason Retires, Contests & Buffalo“. Their bonus was quite simple:

We are so confident you’ll make a sale that we’ll pay you $50 if you put one of our new panties.com banners on your website and do not make a sale by February 28th!

So I placed the banner, and emailed the program manager on Mon, Jan 25, 2010 at 4:44 PM. On Mon, Jan 25, 2010 at 4:59 PM, Lila (the program manager) got back to me. Checking the server access logs, there were 3 IP’s that accessed this page on that day: myself, my server, and 199.120.67.39 (Lila at panties.com)

199.120.67.39 - - [25/Jan/2010:16:52:55 -0500] "GET /panties-com-m910.php HTTP/1.1" 200 14201 "http://www.clubcouponcode.com/" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.7) Gecko/20091221 Firefox/3.5.7 (.NET CLR 3.5.30729)"

Today I was thinking to myself that I didn’t make a sale (what a shame) and I need to contact her for my $50 bonus. So I logged into ShareASale, checked my stats (just to be sure) and imagine my surprise when I saw I made a sale! Yeah! A $48 sale means $7.20 for me!

What was strange, however, was the sale occurred at 02/28/2010 06:09:55 PM – just hours before the bonus period was ending. So again I turned to my trusty server access logs and found the following:

199.120.67.39 - - [28/Feb/2010:18:05:41 -0500] "GET / HTTP/1.1" 200 24842 "-" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.8) Gecko/20100202 Firefox/3.5.8 (.NET CLR 3.5.30729)"
199.120.67.39 - - [28/Feb/2010:18:06:01 -0500] "GET /panties-com-m910.php HTTP/1.1" 200 11364 "http://www.clubcouponcode.com/" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.8) Gecko/20100202 Firefox/3.5.8 (.NET CLR 3.5.30729)"
199.120.67.39 - - [28/Feb/2010:18:06:39 -0500] "GET /go/merchant.php?nMerchantID=910 HTTP/1.1" 200 955 "http://www.clubcouponcode.com/panties-com-m910.php" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.8) Gecko/20100202 Firefox/3.5.8 (.NET CLR 3.5.30729)"

Lila went right to my homepage, clicked on the merchant page, clicked on the link to set the cookie, and bought something nice for herself. She felt that it was cheaper for her to pay a $7.20 commission rather than a $50 bonus. But she failed to realize the implications of me tracking this down.

What a shallow thing to do. If you don’t want to pay a bonus, don’t put it out there. Instead, I feel tricked to have placed your banner on my site, and will make sure I spread the word how you ripped me off.

Anyone else participate in the panties.com February bonus program? Did you get your bonus, or did you end up making a sale on the last day, too?

On Saturday, March 27th we’ll be hosting an affiliate meetup at the Pearl Street Grill & Brewery at 1pm. Jason Rubacky from ShareASale (and my co-host on “both sides of the tracks“) is coming into town, and we’re inviting affiliates and merchants to come & talk shop.

Details can be found on ABestWeb.com

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!

I attended Jim Kukral’s session at Affiliate Summit entitled, “How to get Motivated for Success” only to hear Jim speak. He has a natural stage presence, and I was going simply to listen to a great public speaker. I didn’t expect to get anything out of the session, but Jim definitely proved me wrong.

Jim’s first shot at the audience was: get over yourself. Lose your ego. The reason I’m bothering to bring this up is because that’s how I found my success in the affiliate industry. For years, I thought I knew how to be a successful affiliate, and I didn’t listen to anyone. Then, frustrated with my failures (more on this later) and presented with a training opportunity with Jeremy Palmer, I left behind everything I thought I knew and followed Jeremy’s training. 1-1/2 years later, I was a full-time affiliate marketer.

Next, you need to produce, or perish. Get rid of distractions: turn off Twitter, Gmail, and your phone and get some real work done. Stop reading my blog (when you’re done with this article & left a comment) and get back to work! A tool I use for this is online-stopwatch.com. I tell myself, I’m going to do “X” for 20 minutes, and that’s all I do. When something else pops into my head, I write it down, and get back to the task at hand. Focus.

You need to get out there & do something. Don’t worry about what others are doing – worry about what you’re doing. You need to work your butt off. At this part of the session, Jim called me out, but in a good way. Yeah, I work hard. I’m up between 4:30 and 5:00 in the morning, I hit the gym (while listening to GeekCast.fm on my iPod), then come home & help get the kids off to school, work hard all day, spend some time with the family in the evening, and often get back online to answer some emails or plan tomorrow’s to-do list. Even right now, I’m writing this blog post while flying home.

Doers get what they want, everyone else gets what they get – Jim Kukral

What’s your goal? Write it down, and work on it every day.

Besides being hard workers, Jim and I have something else in common: we’re failures. But, we’re not losers. Failures fail hard, fail fast, and try again. Losers give up.

Get rid of the negative people in your life. It’s hard when they’re family, but believe me, you’ll feel better about what you’re doing, you’ll be more focused, and you’ll get more done.

Jim ended his session by asking the audience what they’re going to do in the next 6 months. He challenged people to come up to him and tell him what they’re going to do. And at Affiliate Summit East in New York, Jim wants to hear back from them.

I’ll be at ASE10 in NY, too – what are you going to have done? Tell me in the comments, or send me an email if you don’t want to share with the world. How can I help you get there?