Affiliate Data Feeds

4/21/2005: PHPmill has released an affiliate datafeed parser. We have tested the parser and it works very well for affiliate data feeds that are tab, comma, or pipe delimited. The PHP/MySQL based software will check an affiliate datafeed for errors and remove or fix rows with errors. It also features load balancing to help prevent excessive server use with parsing the datafeed. The software convers the tab, comma, or pipe delimited affiliate datafeed into a MySQL table. This is generally the first step in using a datafeed for affiliate website purposes. Check out the online demo of the affiliate datafeed parser, Datafeed-to-MySQL

Are you a webmaster using an affiliate program to generate extra revenue for your site? If so, you may be able to greatly increase your earnings by using an affiliate data feed.

An affiliate data feed generally contains all the information about all of the products that a site carries... for most sites this is anywhere from a thousand to tens of thousands of products. Dealing with a file this large can be cumbersome if you don't know how to do it. Many sites offer data feeds of their products for your use, many do not. For those that do not, a data feed can still be obtained; you can create your own!

This article will attempt to teach you both how to use an affiliate data feed and how to create one from a site that does not offer one.

How to use affiliate data feeds
The first step is to evaluate the format of the data including how is it organized, how clean is it, how are columns differentiated, and what irregularities exist. Second, you need to verify the integrity of the affiliate links that are contained in the feed or that you will make with the feed. These links need to work properly so that you get paid for the products you sell. Third, you need to choose a database to dump the data into. Our choice is the open source database MySQL because it is powerful, fast, widely supported, and works wonderfully with PHP. Fourth, you need to create a MySQL database with columns that match the order of the text that the feed contains. Fifth, you need to write a script in PHP that will parse the data into the MySQL database. These scripts are generally short and simple but may need fine tuning if the feed contains irregularities.

At this point, you've taken the data from a text file and made it into a MySQL table. Now that your data is in a MySQL table, you can develop the database around it. Generally, you'll want a table containing the product information, a table containing category information, another one linking product and category information, possibly one for manufacturer information, and others as you see fit. Once you make these tables, you'll need to write another script in PHP to go through the feed table and distribute the feed to the rest of your database. This script will generally be about 2-4 times lengthier than the first script, but still, not a difficult task. You can create or modify your website to work with the new MySQL database.

TIP: Design your feed table with excess columns that may be useful for other feeds. For example, some sites will give 3 category levels for each product, some 5. Your feed table should have 5 category levels to accommodate both sites. This flexibility will allow you to reuse both your feed table and your script that distrubutes the MySQL feed table to the rest of the database.

If an affiliate data feed is not available, make your own!

Making a data feed from a site that does not offer one can be accomplished by writing a script that visits the website, grabs the information you need from the website and puts it into your MySQL feed table (see above). We use PHP to accomplish this. There are a few basic requirements of the site that will allow you to generate a feed without too much headache. First, the site must be database driven and the products must be displayed by variable calls to the site's database on as few product pages as possible. Most well organized e-commerce sites will fit this model as it is simplest for them to have only one dynamic product page that generates all of the static product display pages. An example of this is a site that uses something like product.asp?pid=5892 where product.asp is the product page and pid is the products id that makes the page display the product with the id 5892. URL rewriting can create complications in writing your script to identify the product page. This needs to be dealt with on a case by case basis by evaluating how the URLs are rewritten. Sure, we could just bomb the site and have our script visit every page that is linked withing the site but this will use a lot of unnecessary bandwith on their end and a lot of CPU time on your end when your feed-generation script is running. Second, it is very helpful if product images and thumbnails have similar names except one change that is the same for every image. An example is product-5892.jpg for the main image and product-5892th.jpg for the thumbnail. Third, it is desirable for a category path of the product page to be displayed on the product page itself, example Home > Garden > Tomatos > Wire Basket. This will make creation of the category entries of your MySQL feed table much easier.

The two step approach to creating an affiliate data feed

The first step is to grab the links of the category pages that display the products. For most sites these will not change much over time so doing this only occassionally will be enough. Create a MySQL table with two columns and call it urls_categories. The first column should be an integer, primary key, auto increment. The second column a varchar with length 255. If a script will be grabbing the category pages for you, index the second column for matching when the script is adding urls to prevent adding the same one twice. You can grab category urls manually or by writing a script. For most sites, it may be quicker, although tedious, to do it manually.

Here is the MySQL query to generate the urls_categories table.

CREATE TABLE `urls_categories` (
  `urls_categories_id` int(11) NOT NULL auto_increment,
  `companies_id` int(11) NOT NULL default '0',
  `urls_categories` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`urls_categories_id`),
  KEY `companies_id` (`companies_id`)
) TYPE=MyISAM;

Note that we added a companies_id for use with an affiliate site displaying the products of multiple companies. Now insert the category urls into this table.

Once you have all the category urls inserted you'll need to write a script to grab all the product urls from the category pages. This can be done fairly easily with fopen() or fsockopen(), and preg_match_all(). We recommend fsockopen() so you can pass headers which will inform the site owner why you're such a high bandwidth user. Provide an address that will show in their log files so they can contact you if need be rather than forcing them to ban your IP. When the product urls are grabbed, they need to be inserted into another MySQL database table, we'll call this one urls_products and it can be set up like that below.

CREATE TABLE `urls_products` (
  `urls_products_id` int(11) NOT NULL auto_increment,
  `companies_id` int(11) NOT NULL default '0',
  `urls_categories_id` int(11) NOT NULL default '0',
  `urls_products` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`urls_products_id`),
  KEY `companies_id` (`companies_id`),
  KEY `urls_products` (`urls_products`)
) TYPE=MyISAM;

You might wonder why we duplicate the urs_categories_id in this table. The answer is that it can help us to recreate a category path for a product page that does not display a category path. The effect of having a useable category path for each product can be far reaching, greatly enhancing the ability of your site to properly categorize products, produce oddly named products for general user searches and more.

Having trouble on where to begin or how to write the scripts? We can help. We have created scripts to do just what this article describes for dozens of popular websites. The best part is, if you only have a little experience in programming in PHP, you can have us setup the scripts for one site and use them as a model for additional sites that you might want to create a feed from. The scripts we write are fairly portable and easy to follow. Our rates are as low as $30 an hour. Contact us today!

To be continued...

	Site Search