How to correctly remove SEO optimized products?

It is not good to simply remove outdated products from the shop, because somewhere at the web links could exist, pointing to the removed product page. After product removal, all such links will point to the 404 error page, and many chances are that potential customers will just close such page without trying to inspect site any further.

That is why it is better to keep outdated product for some period of time, especially if customers keep visiting this product page.

We made a module to monitor outdated products, to analyze how often their pages are visited by customers, and to eventually delete these products when nobody visit their pages anymore.

Task in details:

1. After product acquired “out of stock” status, all visits to its page will be monitored. If for certain amount of time (this amount is taken from the module settings) quantity of visits will be less than certain minimum value (this value is also taken from the module settings), then product become “disabled”.
2. In “disabled” state product will continue to be kept in the database for one last period of time (its amount is also taken from the module settings). While in this state, all customer visits of its page will be redirected to the product’s category page.
3. After this last “disabled” period elapsed, product will be permanently deleted from the database.

To achieve this task, module creates two tables:

CREATE TABLE `mm_outofstock_watch` (
 `watch_id` int(10) UNSIGNED NOT NULL COMMENT 'ID',
 `product_id` int(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Watched product ID',
 `watch_start_time` timestamp NULL DEFAULT NULL COMMENT 'Watch creation time',
 `current_stage` smallint(5) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Current watch stage',
 `cron_time` timestamp NULL DEFAULT NULL COMMENT 'Last time processed by cron'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Watched products Table';

CREATE TABLE `mm_outofstock_watch_visit` (
 `watch_visit_id` int(10) UNSIGNED NOT NULL COMMENT 'ID',
 `watch_id` int(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Watch ID',
 `visited_at` timestamp NULL DEFAULT NULL COMMENT 'Date and time of visit'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Watched products visits';

Table mm_outofstock_watch contains information about all “out of stock” products along with their statuses.

Table mm_outofstock_watch_visit keeps track of all product page visits.

Also it is necessary to create foreign keys for these tables, so their records will be cascade deleted along with a corresponding parent record.

Here is full code, used to create these tables:

$installer = $this;
/* @var $installer Mage_Core_Model_Resource_Setup */
$installer->startSetup();
/**
 * Create table 'outofstockremoval/watch'
 */
$table = $installer->getConnection()
    ->newTable($installer->getTable('outofstockremoval/watch'))
    ->addColumn('watch_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
        'identity'  => true,
        'unsigned'  => true,
        'nullable'  => false,
        'primary'   => true,
    ), 'ID')
    ->addColumn('product_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
        'unsigned'  => true,
        'nullable'  => false,
        'default'   => '0',
    ), 'Watched product ID')
    ->addColumn('watch_start_time', Varien_Db_Ddl_Table::TYPE_TIMESTAMP, null, array(
    ), 'Watch creation time')
    ->addColumn('current_stage', Varien_Db_Ddl_Table::TYPE_SMALLINT, null, array(
        'unsigned'  => true,
        'nullable'  => false,
        'default'   => '0',
    ), 'Current watch stage')
    ->addColumn('cron_time', Varien_Db_Ddl_Table::TYPE_TIMESTAMP, null, array(
    ), 'Last time processed by cron')
    ->addIndex(
        $installer->getIdxName('outofstockremoval/watch', 'product_id', Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE),
        'product_id',
        array('type'=>Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE)
    )
    ->addForeignKey(
        $installer->getFkName('outofstockremoval/watch', 'product_id', 'catalog/product', 'entity_id'),
        'product_id',
        $installer->getTable('catalog/product'),
        'entity_id',
        Varien_Db_Ddl_Table::ACTION_CASCADE,
        Varien_Db_Ddl_Table::ACTION_CASCADE
    )
    ->setComment('Watched products Table');
$installer->getConnection()->createTable($table);
/**
 * Create table 'outofstockremoval/watch_visit'
 */
$table = $installer->getConnection()
    ->newTable($installer->getTable('outofstockremoval/watch_visit'))
    ->addColumn('watch_visit_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
        'identity'  => true,
        'unsigned'  => true,
        'nullable'  => false,
        'primary'   => true,
    ), 'ID')
    ->addColumn('watch_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
        'unsigned'  => true,
        'nullable'  => false,
        'default'   => '0',
    ), 'Watch ID')
    ->addColumn('visited_at', Varien_Db_Ddl_Table::TYPE_TIMESTAMP, null, array(
    ), 'Date and time of visit')
    ->addIndex($installer->getIdxName('outofstockremoval/watch_visit', 'watch_id'), 'watch_id')
    ->addForeignKey(
        $installer->getFkName('outofstockremoval/watch_visit', 'watch_id', 'outofstockremoval/watch', 'watch_id'),
        'watch_id',
        $installer->getTable('outofstockremoval/watch'),
        'watch_id',
        Varien_Db_Ddl_Table::ACTION_CASCADE,
        Varien_Db_Ddl_Table::ACTION_CASCADE
    )
    ->setComment('Watched products visits');
$installer->getConnection()->createTable($table);
$installer->endSetup();

To keep track of all product page visits, it is necessary to rewrite Mage_Catalog_ProductController

class viewAction method. Moreover, that rewritten method is used to redirect customers to the product category page, if product is in the “disabled” state. Such redirection can be done with following command:

$this->_redirectUrl($categoryToRedirectTo->getUrl());

Main job, however, executes in the cron task.

To define such task, we must add cron job definition it the config.xml file:

<crontab>
   <jobs>
      <mm_outofstockremoval>
         <schedule>
            <cron_expr>0 1 * * *</cron_expr>
         </schedule>
         <run>
            <model>outofstockremoval/cron::removeOutOfStockProducts</model>
         </run>
      </mm_outofstockremoval>
   </jobs>
</crontab>

In fragment above, removeOutOfStockProducts is a method of the outofstockremoval/cron model.

In that method collection of all “out of stock” products created as follows:

$outOfStockCollection = Mage::getResourceModel('catalog/product_collection')
    ->addFieldToFilter('visibility', Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH)
    ->joinField(
        'inventory_in_stock',
        'cataloginventory/stock_item',
        'is_in_stock',
        'product_id=entity_id',
        '({{table}}.is_in_stock=0)'
    );

Then a loop is executed on this collection, where “out of stock” products is looked up at themm_outofstock_watch table. If corresponding record is not found, new record will be added to that table. If corresponding record is found, field cron_time will be filled with the time when current cron execution started.

In this loop, product statistics is also checked and, if necessary, new product status is written in the current_stage field. Also, if necessary, product will be deleted in this loop.

After loop finished, table mm_outofstock_watch is checked for “outdated” records. “Outdated” records are records, whose field cron_time contains value which is less than the time when current cron execution started. If such records found, it means that in previous cron execution circle (a day before), corresponding products were “out of stock”, but by now these products become “in stock”, and no longer need to be watched. This can happen if administrator change product status in admin panel.