Bestseller products in Magento

Bestseller products is one of the features people tend to ask about and look for when it comes to Magento. Default installation already has bestseller products option included…but these are static ones defined in a CMS page. We’re going to take it to the next level and automate it.

Magento already uses bestseller products aggregation that can be checked under Admin > Reports > Products > Bestsellers. Thanks to this aggregated data we no longer need to determine the total of all the orders to see which products are sold the most. By the variety of aggregation data we’re able to get the most popular products not only from the beginning, but for each specific day, month or year.

There’s many ways of doing this. What I’ve found the most useful to me is to join product collection with the monthly bestseller aggregation. This way is fast and products can always be accessed even though the aggregation table is empty.

class Inchoo_Damir_Block_Bestsellers extends Mage_Core_Block_Template
{
    public function getBestsellerProducts()
    {
        $storeId = (int) Mage::app()->getStore()->getId();
 
        // Date
        $date = new Zend_Date();
        $toDate = $date->setDay(1)->getDate()->get('Y-MM-dd');
        $fromDate = $date->subMonth(1)->getDate()->get('Y-MM-dd');
 
        $collection = Mage::getResourceModel('catalog/product_collection')
            ->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
            ->addStoreFilter()
            ->addPriceData()
            ->addTaxPercents()
            ->addUrlRewrite()
            ->setPageSize(6);
 
        $collection->getSelect()
            ->joinLeft(
                array('aggregation' => $collection->getResource()->getTable('sales/bestsellers_aggregated_monthly')),
                "e.entity_id = aggregation.product_id AND aggregation.store_id={$storeId} AND aggregation.period BETWEEN '{$fromDate}' AND '{$toDate}'",
                array('SUM(aggregation.qty_ordered) AS sold_quantity')
            )
            ->group('e.entity_id')
            ->order(array('sold_quantity DESC', 'e.created_at'));
 
        Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($collection);
        Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($collection);
 
        return $collection;
    }
}

What the code does is that it returns a product collection joined with the aggregation table with the products sold the most in the last month. If this logic will be or is being used more than once, it’s recommended to place the join logic into catalog product collection file (by extending the file or by using the events) and to call it as a method when constructing the collection.

Now, when the product collection is ready, there are more ways to display them on the frontend. For the purposes of the article let’s replace the original html in the CMS page with a block call. As it can be seen from the code above, I’ve used my own block for the bestsellers logic. Open CMS > Pages, select the row with the identifier “home” and replace the content with the html below:

<div class="col-left side-col">
    <p class="home-callout">&nbsp;</p>
    <p class="home-callout"><img src="{{skin url='images/ph_callout_left_rebel.jpg'}}" alt="" border="0" /></p>
    {{block type="tag/popular" template=“tag/popular.phtml"}}
</div>
<div class="home-spot">
    <p class="home-callout"><img src="{{skin url='images/home_main_callout.jpg'}}" alt="" width="470" border="0" /></p>
    <p class="home-callout"><img src="{{skin url='images/free_shipping_callout.jpg'}}" alt="" width="470" border="0" /></p>
    {{block type="damir/bestsellers" template=“damir/bestsellers.phtml"}}
</div>

This html does nothing more than replace Magento’s div tag that contained static bestseller products with our block call. The next and the last thing that has to be done is to create a template file. The template file uses the same html structure as the CMS page:

<div class="box best-selling">
<h3>Best Selling Products</h3>
<table border="0" cellspacing="0">
    <tbody>
    <?php $counter=0; foreach ($this->getBestsellerProducts() as $product): ?>
        <?php if ($counter%2 == 0): ?><tr class="<?php echo $counter%4 ? 'even' : 'odd'; ?>"><?php endif ?>
        <td>
            <a href="<?php echo $product->getProductUrl() ?>"><img class="product-img" src="<?php echo $this->helper('catalog/image')->init($product, 'small_image')->resize(99); ?>" alt="<?php echo $this->stripTags($this->getImageLabel($product, 'small_image'), null, true) ?>" width="95" border="0" /></a>
            <div class="product-description">
                <p><a href="<?php echo $product->getProductUrl() ?>"><?php echo $this->stripTags($product->getName(), null, true); ?></a></p>
            </div>
        </td>
        <?php if ($counter++%2): ?></tr><?php endif ?>
    <?php endforeach; ?>
    </tbody>
</table>
</div>

This example uses monthly aggregation which I’ve found the most useful. There’s also daily aggregation that can be use and yearly, which is unlikely to be used often. Many Magento extensions can be found around that does the same thing, just a little bit nicely wrapped, but the logic is actually in one fairly simple method.

As it’s highly not recommended to modify the core files, each project basically has to have at least one module to place our own block. If you’re unfamiliar with how to create modules, there’s a nice article to read about the basics of creating a Magento module that I recommend.

Note: This is a revamp of the article originally written in December 2008

You made it all the way down here so you must have enjoyed this post! You may also like:

Shell script for converting configurable to grouped products Tsvetan Stoychev
, | 5

Shell script for converting configurable to grouped products

Large number of input variables in Magento Kresimir Banovic
, | 5

Large number of input variables in Magento

Programmatically create bundle products in Magento Petar Sambolek
Petar Sambolek, | 3

Programmatically create bundle products in Magento

90 comments

  1. For using pricehtml on bestseller.phtml use the following code:

    setStoreId($storeId)->load($product->entity_id); ?>

    getPriceHtml($_product, true); ?>

    Regards,
    Tejasvini

  2. Hi Roman,

    The issue regarding the adding in the cart and price display has been solved. I have uploaded a new bestseller.phtml to the zip file. Thank you for bringing up the bug!

  3. Hi Erik,
    your extended code looks great, but adding to the cart doesn’t work and there is no price shown. Any tips how to manage these issues?

  4. Hi Chintan!

    What I get is that you want to run bestseller calculation only once
    and to set a bestseller flag attribute on products and when page loads the query is done by that attribute.
    Yes it can be done. You have to access each bestseller product object and update the flag.

  5. Hi.. Is there any way i can assign BEST SELLER sticker..
    I have created attribute named sticker and its value is bestseller.. I want my code to update sticker (attribute value) of products every night.. and top 5 sold products gets sticker attribute value to Best Seller..
    Any other option of getting this feature..?

  6. Hello I Tried with this.. but Category: shows blank.. I mean if a product is under Furniture .. then product is shown but not its category name.. I am using magento 1.5.. and will try the same with 1.6

  7. i have used this code for my best seller product display in home page but the best seller product which displays in home page is different from the bestsellers which displays in admin dashboard.can anyone help me out to display correct product? may i know the table name which the ‘Quantity Ordered’ stores? thanks in advance please help.

  8. Hi

    I am working on magento project , I have a issue some product url setting in home, i have home page in my site which display the product from different
    Category e.g best sell, hot deal, new, so i want to change url on home as after domain name e.g it should be ,http://sitename.com/best_sell/product_url.html but i want to same like http://sitename.com/product_url.html.. only on home page and inner catalog working is fine..

    So is there any solution to fix the problem.?

    Reply me it will be really appreciated.

  9. I was using this technique for a while now, but I just realised that it doesn’t work if caching is enabled? Any way around that?

  10. hi,
    good work, i am facing a problem with this extension they fetch all prduct from all store/website, i want to limitize this extension to one store, how i can do that ..? :).

    second thing is it possible from one or more categories.

    Thanks
    Muzafar Ali

  11. when i research this situation , i saw that it works when i put blocks code in homepage cms , but when i try to use catalog xml , it cant get variable.

  12. i’m using magento 1.4 but i couldnt success put variable (show total) to phtml file ,

    in bestseller.phtml
    $totalPerPage = ($this->show_total) ? $this->show_total : 6;
    this code return always value 6 ,

    whats wrong?

  13. Hello

    I want to give a title separably for a promotional product block and a bestseller block. How can I differences the two block?

    Thanks

  14. Hi,
    First of all thanks for this wonderful module. It works for me perfectly under Magento 1.4. I need help regarding price, how can I display price along each product?

  15. THX, i’m a primary learner of magento…But i’m not familiar with Zend framework method…

  16. Hi, Sorry but this code doesn’t work for me, Might be I m doing something wrong.
    I hv created one directory call bestseller under the template directory in my theme.
    so path for the bestseller.phtml is “C:\wamp\www\magento\app\design\frontend\default\mycustom\template\bestseller\bestseller.phtml” , is that right ???

    and I hv put that block part in my homepage content section.. and i hv changed to {{block type=”core/template” show_total=”12? template=”bestseller/bestseller.phtml”}}
    So what is the wrong here?
    I couldn’t see anything on my homepage..

    Plz help.

  17. Hi,
    I wonder if it would, with the help of your code, be possible to add the product->ordered_qty in same way in the backend, to be able to see
    how many of each products that has been ordered..
    Thanks
    Johan

  18. Excellent thanks for the tutorial that’s what i was looking for how i can integrate custom .phtml files to magento could’t think that would be that easy may i as how you get to know this 😉 so in future i will try to think your way 🙂 this is the only place except magento forum or wiki i can read about magento.

    Thanks again.

  19. i prefer using this module than the new one as my products are not that much. i have it set on my front page and it is showing the best selling products in vertical form, how can i make it to show them like the best selling products html form table magento has by default?

  20. i followed your code as it is.but still i couldn’t add bestseller to the homepage.can any one help me as soon as possible…

    Thank you…

  21. Speed – currend module loads all products and than only displays specified number of products. That is very very slow on a website with thousands of products.
    Just add ->setPageSize($totalPerPage) to the Mage::getResourceModel and it will be much faster.

  22. 1) It seems that it does not work for current store view. It displays products from another store.

    2) I want to use this module to display newest products. Do you think that changing ->setOrder(‘ordered_qty’, ‘desc’); to ->setOrder(‘created_at’, ‘desc’); will work?

  23. How are you able to display the path to the template file ? Is it a module ? Or i’m missing an important feature of Magento… ?!

    Thanks

  24. Is it possible for me to implement getPriceHtml($_product, true, ‘-best’) ?> in this piece of code? getPrice works, but I need getPriceHtml.

    Thanks in advance 🙂

  25. Hi,
    Is it possible to get this block to work with configurable products also. I’m affraid that those lines:
    Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH,
    Mage_Catalog_Model_Product_Visibility::VISIBILITY_IN_CATALOG

    are pretty good at not taking single products which are a part of configurables into account. :/

  26. Maybe something happened to the Magento filesystem with the latest update, but I can’t find “bestseller.phtml anywhere. Given the depth of Magento’s file system, when referring to files it would hep if a full path was provided. Thanks.

  27. @osdave: thanks, I had the same problem … but I did’nt have to think it ” ….
    {{block type=”core/template” template=”inchoo/bestseller.phtml”}}

  28. Hey Branko, thanks once more for another good Magento tip. I’ve been learning a few things from your sites.

    I have a question about your show_total property. You set it in the block description that goes in the content of the page. I was trying to do a similar thing in the layout xml files for some of my own blocks but it doesn’t seem to work in that case. I.e

    Do you know why this is?

    Also, if you’re preparing another tutorial, could you possibly do one about the various filter option when retrieving a collection (the add*Filter() methods)

    Thanks a lot.

  29. @Jack: if you copied the code from here and pasted it in the home page admin’s CMS management screen, you have to change the “, in: {{block type=”core/template” template=”inchoo/bestseller.phtml”}}

    @Branko: thanx for this tutorial, very helpfull, as always.
    I imagine you’re aware that configurable products don’t show up with this method. I’m going to look into it to see if I can manage them to appear, but if you have a hint for me I’d be please to read it 🙂

  30. I actually tried using your technique to display the bestellers but nothing shows up on the page … And i’m pretty sure i followed your instructions exactly and i deactivated the cache in Magento. I’m using Magento 1.8. It’s really strange…

  31. Thanks, this post is really useful!
    Do you think that on the basis of this “bestseller” phtml file it would be possible to edit it so that it only displays the items with a stock lower than 10 instead of displaying the bestsellers?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <blockquote cite=""> <code> <del datetime=""> <em> <s> <strike> <strong>. You may use following syntax for source code: <pre><code>$current = "Inchoo";</code></pre>.