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
Share this Story:
  • facebook
  • twitter
  • gplus

Comments(20)

  1. PandaMarketer
    2460 days ago

    Hmm, I wasn’t using the “limit 1” because I wasn’t using the “like” operator. (was using “=”)

    Also, do you always put “(int)” in front of your GET command if you know there should only be numbers? Never done that before. Clearly I have a lot to learn, and am thankful for this primer in custom php datafeed manipulation.

    I sent you a link to my project earlier, did you go through it? Thanks.

  2. Eric Nagel
    2460 days ago

    If you know there’s only 1 record that will be returned by the sql query, “limit 1” will speed things up. Say you have 60,000 records and the one you’re looking for is 1st… then limit 1 will tell MySQL to stop looking.

    Yes, if I’m expecting an int, I’ll type-cast it for security reasons. Part of the Zend PHP Certification was about securing your scripts.

    I saw your site… still some work to do (remove dead pages) but I liked your breadcrumbs. I couldn’t think of a good way to do that with my site.

    Next is link-building & get some traffic in.

  3. PandaMarketer
    2459 days ago

    Thanks for looking. Like I said, was really only doing it for practice, but if I finish what I started, I’m sure I can release it to the internets. heh.

  4. LGR
    2458 days ago

    I have created various datafeed sites in the past and you have created a great tutorial on how to do it.

    What I have discovered is that creating the datafeed site is the easy part. Getting the datafeed site to get indexed, rank and bring in sales is another. It would be interesting to continue the series on how it goes after the basics are created and most importantly if you get it to turn a profit.

    Just a thought.

  5. @boudga
    2457 days ago

    What LGR^^^ said!!!

  6. Eric Nagel
    2457 days ago

    OK… let me see what I can do for promoting the site. What do you guys want to see? PPC, SEO, social? Do you want theory, or real examples?

  7. PandaMarketer
    2457 days ago

    I think PPC is dead.

  8. Maarten
    2444 days ago

    @Eric,

    I would like to see how you can promote the site with SEO. Because PPC is relative easy, if you have a budget to start with. For example, Linkbuilding will be much harder with a site only incorporating only affiliate links. So how do you get a site like this profitable and with how much traffic is that?

    For example:
    3000 unique visitors
    2% conversion
    avg. commision 3 euros

    This will make you on avg. 180 euro a month. What will be the break even point or when do you start focusing on other sites. What kind of revenue can produce a site like this?

  9. Eric Nagel
    2444 days ago

    I haven’t forgotten about this site… just some “real work” has taken priority. I’m going to start with some PPC stuff, because I’ll provide some PHP scripts to take the database & export in an AdWords-friendly format, but I’m still thinking about the SEO-factor (without giving away any secrets I may have)

  10. Andrew
    2434 days ago

    Thanks for the interesting tutorial Eric.

    In regard to SEO: I have built some feed sites and I always try to add some unique content above the product listings. Also, there is some related content on other pages.

    In my case, based on Eric’s example, if I extrapolate my figures from last month (March) to 3000 (I actually had ~4000) visitors, I get earnings of 19 euros from eBay.

    I haven’t tried Share A Sale, maybe that would be higher-paying?

    Also I have Adsense link units and a some fixed affiliate Ads on the sites to generate some more income.

    It would be very interesting to hear what other people make over a month based on 3000 visitors.

  11. Trackback: LinkShare Merchandiser Web Service

  12. Max M
    2111 days ago

    Hey eric. Great post. I am currently in the process of implementing datafeeds for CJ, GAN, ShareASale, Linkshare, and PepperJam. I have already coded they scripts to automatically download and insert the datafeeds into the table. The problem I am facing now is the sheer size of the table. The script I wrote hasnt finished inserting CJ products yet and I am already up to 3.5 million records – about 2.8GB! The part I would like your advise on is how to handle the sheer size of the table. Should I create a new table for each network? Create partitions? Would indexing suffice? Currently a query takes about 6 seconds. Would appreciate any ideas.

    • Eric Nagel
      2106 days ago

      Hi Max,

      I ran into that problem years ago. For the bigger merchants, you need to break things apart. Either break off a merchant, or do it by network.

      Proper indexing of the table helps, too.

  13. pankaj
    1846 days ago

    Great tutorial. But i have a question, how to add content to these sites. At this moment , the site has pulled in all the datafeeds and displaying links to merchant- more info and buy now. But i want to add unique content on them or even edit some of the import content
    — to avoid duplicate penalty and to give more boost in SER by writing content and avoiding the latest Panda slap of thin quality content. Is there a way out to do it?

    Do we have to manually go to each import post and then edit them manually ?

    also do we have the option of importing specific products rather then whole stream. Say i would like to work with only 30 products rather then 100.

    • Eric Nagel
      1846 days ago

      After that, it’s all custom coding. Either add a field for unique description, or build a blog to go along side of the product feed. There’s no one solution that fits everyone

      • pbtasse
        1845 days ago

        Can we use a bit of random products per page to make things unique. If this way is good, it will be easy to do it

  14. pbtasse
    1845 days ago

    Good design for the website: http://www.greenwhiteandblacktea.com/browse-black-tea.php
    Please can you share with us the mod rewrite for this website.
    There is no .htaccess in the files I download in this tutorial
    Thanks

    • Eric Nagel
      1845 days ago

      Sure:

      RewriteEngine On
      RewriteCond %{HTTP_HOST} ^greenwhiteandblacktea.com [NC]
      RewriteRule ^(.*)$ http://www.greenwhiteandblacktea.com/$1 [L,R=301]

      RewriteRule ^browse\-(.*).php$ browse.php?cCategory=$1 [L]
      RewriteRule ^(.*)\-p([0-9]+).php$ item.php?ProductID=$2&cName=$1 [L]
      RewriteRule ^images/(.*)\-i([0-9]+).jpg$ images/image.php?ProductID=$2 [L]

  15. pbtasse
    1845 days ago

    The 3th and the 4th line: can you explain a bit?
    If the website is under another folder (not in the root) does this need some change?
    Thanks again!
    I love Datafeed. It is easy to build website with.

    • Eric Nagel
      1844 days ago

      That forces the site to go to www.

      If your datafeed site is in a folder, you’d change the RewriteRules at the end. Instead of ^browse (starts with browse) you’d have ^folder\/browse

      Eric

Leave a comment

Comment