Are unconverted quote records ever removed in Magento?

Question

tl;dr: There appears to be no other out-of-box mechanism for deleting quote records which were never converted to orders.

The Mage_Sales module has a scheduled job to clean expired quotes, but this only deletes sales_flat_quote records which are marked as inactive (i.e. is_active = 0). To my knowledge, quotes are only marked as inactive when a quote converts to an order. If this is the case, then the quote table will only grow larger and larger.

Ref Mage_Sales_Model_Observer::cleanExpiredQuotes()

class Mage_Sales_Model_Observer
{
    //...

    public function cleanExpiredQuotes($schedule)
    {
        Mage::dispatchEvent('clear_expired_quotes_before', array('sales_observer' => $this));

        $lifetimes = Mage::getConfig()->getStoresConfigByPath('checkout/cart/delete_quote_after');
        foreach ($lifetimes as $storeId=>$lifetime) {
            $lifetime *= 86400;

            /** @var $quotes Mage_Sales_Model_Mysql4_Quote_Collection */
            $quotes = Mage::getModel('sales/quote')->getCollection();

            $quotes->addFieldToFilter('store_id', $storeId);
            $quotes->addFieldToFilter('updated_at', array('to'=>date("Y-m-d", time()-$lifetime)));
            $quotes->addFieldToFilter('is_active', 0);

            foreach ($this->getExpireQuotesAdditionalFilterFields() as $field => $condition) {
                $quotes->addFieldToFilter($field, $condition);
            }

            $quotes->walk('delete');
        }
        return $this;
    }

    //...
}

I opened a ticket with Magento Suport (as a Magento Enterprise Customer and as a Silver Partner). 1st response was: give us all your ssh/db details to check... took them a while to understand what I was actually referring to (their support guys have no Magento coding experience). They said that Magento cannot check if a quote is still active or not and that's why it won't be deleted. If the customer returns, he won't find his quote. Of course, one of our customers has couple of millions of quotes in the database and adding to cart was massively affected. — FlorinelChis, Sep 22 '12 at 16:04
@FlorinelChis, Ben the problem is not so easy to solve, and Magento guys are right that they cannot determine which quote they can delete. First of all the problem in payment method that redirects customer to thirdparty website without changing is_active status, but another problem, that even core modules like Paypal is doing the same :). Problem with is_active, that if this flag is equal to 1, you can occasionally delete a quote of logged in customer, who just saved products for latter. — Ivan Chepurnyi, Sep 23 '12 at 17:58
@IvanChepurnyi - RE saved carts - I'd think that a time limit could apply - how often do customers convert 1-2 months after adding to cart (not sure)? I *did* forget about callback payment methods, but that seems easily handled too, e.g. any active quote over 6 months is likely a dead quote. Even for callback payment methods though, isn't an order created & the cart "dead"? — benmarks, Sep 23 '12 at 18:17
The main issue is that large customers have a huge number of records (milions of records within 1-2 months) that add up in those tables and the performance is affected visibly. Magento washed their hands with this matter. Each partner solves this issue the best hey can. @IvanChepurnyi Saving products for later: add it to whishlist. Callback from payment gw: who will finish the transaction after 2 weeks? — FlorinelChis, Sep 24 '12 at 14:02
@benmarks this is still an issue, is magento planning to solve this at some point ? — Allan MacGregor, Jun 13 '14 at 11:41
Hmm, how does Magento deal with (guest) quotes whose sessions have long been expired? Does `is_active` get set to 0? There's no reason for them to stay in the system since no customer can get that quote again.. — Erfan, Jan 04 '16 at 10:12
@Erfan - It doesn't. See [this answer] (http://stackoverflow.com/a/12545295/833795) for a solution. — benmarks, Jan 04 '16 at 22:25
this is actually pretty ridiculous if you ask me. Why can't they just sniff the updated_at field — David, Nov 30 '16 at 21:50

Fiasco Labs · Answer 1 · 2016-01-04T17:21:04.110

Overwrite the observer with your own cleanup routine. We keep specific quotes for specific periods of time, hence 4 tiers of deletion. Guest carts get remail and are recoverable, empty registered customer quotes expire beside completed quotes and registered carts with contents get saved for a long period per observed customer behavior.

<?php
/**
 * Magento
 *
 * NOTICE OF LICENSE
 *
 * This source file is subject to the Open Software License (OSL 3.0)
 * that is bundled with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://opensource.org/licenses/osl-3.0.php
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@magentocommerce.com so we can send you a copy immediately.
 *
 * DISCLAIMER
 *
 * Do not edit or add to this file if you wish to upgrade Magento to newer
 * versions in the future. If you wish to customize Magento for your
 * needs please refer to http://www.magentocommerce.com for more information.
 *
 * @category    Mage
 * @package     Mage_Sales
 * @copyright   Copyright (c) 2010 Magento Inc. (http://www.magentocommerce.com)
 * @license     http://opensource.org/licenses/osl-3.0.php  Open Software License (OSL 3.0)
 */


/**
 * Sales observer
 *
 * @category   Chief
 * @package    Chief_Sales
 * @author     Magento Core Team <core@magentocommerce.com>
 */

/* Valid for 1.4.2.0, 1.5.1.0 */

class Chief_Sales_Model_Observer extends Mage_Sales_Model_Observer
{
    /**
     * Clean expired quotes (cron process)
     *
     * @param Mage_Cron_Model_Schedule $schedule
     * @return Mage_Sales_Model_Observer
     */
    public function cleanExpiredQuotes($schedule)
    {
        $lifetimes = Mage::getConfig()->getStoresConfigByPath('checkout/cart/delete_quote_after');

        /* Quotes converted to orders */
        foreach ($lifetimes as $storeId=>$lifetime) {
            $lifetime *= 86400;

            $quotes = Mage::getModel('sales/quote')->getCollection();
            /* @var $quotes Mage_Sales_Model_Mysql4_Quote_Collection */

            $quotes->addFieldToFilter('store_id', $storeId);
            $quotes->addFieldToFilter('updated_at', array('to'=>date("Y-m-d", time()-$lifetime)));
            $quotes->addFieldToFilter('is_active', 0);            // Filled Quotes
            $quotes->walk('delete');
        }


        /* Quotes abandoned by Guest Carts */
        foreach ($lifetimes as $storeId=>$lifetime) {
            $lifetime *= 86400;

            // triple lifetime for abandoned cart remail
            $lifetime *= 3;

            $quotes = Mage::getModel('sales/quote')->getCollection();
            /* @var $quotes Mage_Sales_Model_Mysql4_Quote_Collection */

            $quotes->addFieldToFilter('store_id', $storeId);
            $quotes->addFieldToFilter('updated_at', array('to'=>date("Y-m-d", time()-$lifetime)));
            $quotes->addFieldToFilter('is_active', 1);            // Active Quotes
            $quotes->addFieldToFilter('customer_group_id', 0);    // Which are Group NLI (Guest)
            $quotes->walk('delete');
        }


        /* Quotes abandoned by Registered carts no contents */
        foreach ($lifetimes as $storeId=>$lifetime) {
            $lifetime *= 86400;

            $quotes = Mage::getModel('sales/quote')->getCollection();
            /* @var $quotes Mage_Sales_Model_Mysql4_Quote_Collection */

            $quotes->addFieldToFilter('store_id', $storeId);
            $quotes->addFieldToFilter('updated_at', array('to'=>date("Y-m-d", time()-$lifetime)));
            $quotes->addFieldToFilter('is_active', 1);                      // Active Quotes
            $quotes->addFieldToFilter('customer_group_id', array('gt'=>0)); // For all other groups
            $quotes->addFieldToFilter('items_qty', 0);                      // For empty carts
            $quotes->walk('delete');
        }


        /* Quotes abandoned by Registered carts */
        foreach ($lifetimes as $storeId=>$lifetime) {
            $lifetime *= 86400;

            // Registered cart lifetime for abandoned cart remail 7*25 = 175 days
            $lifetime *= 25;

            $quotes = Mage::getModel('sales/quote')->getCollection();
            /* @var $quotes Mage_Sales_Model_Mysql4_Quote_Collection */

            $quotes->addFieldToFilter('store_id', $storeId);
            $quotes->addFieldToFilter('updated_at', array('to'=>date("Y-m-d", time()-$lifetime)));
            $quotes->addFieldToFilter('is_active', 1);                      // Active Quotes
            $quotes->addFieldToFilter('customer_group_id', array('gt'=>0)); // For all other groups
            $quotes->addFieldToFilter('items_qty', array('gt'=>0));         // For expired carts
            $quotes->walk('delete');
        }
        return $this;
    }
}

I can see in a 1.9.x version of Magento, that the rewrite is not needed anymore, since they actually have a event dispatched at the very beginning of the Observer which is **clear_expired_quotes_before** so an observer of this event is the way to go now. — β.εηοιτ.βε, Dec 30 '15 at 10:39
That last foreach loop (quotes abandoned by registered carts) should also have the `is_active` filter set to `1` right? (not 0, because it wouldn't clear old quotes, just quotes that potentially have been converted to an order) — Erfan, Jan 04 '16 at 08:00
@Erfan - Yes, you are correct. I just compared it with the external script I'd created to do the same thing before modifying the observer to make it automatic. We want it to remove active quotes over XXX days so the customer can come back and place the order. The filled quotes are already taken care of. — Fiasco Labs, Jan 04 '16 at 17:23
Should be the accepted answer since the other one is a link only answer. — β.εηοιτ.βε, Jan 08 '16 at 20:17
Thank you so much for this info. I found in our DB we had over 1.7 million active quotes from over a year ago! That can't be helping anything! Magento should not have named this setting "Quote Lifetime", because that's not what it is. It's the age post-conversion to keep it in the db. But any quote that isn't converted is kept indefinitely. Cleaning these up is good practice. — Mageician, Apr 04 '18 at 16:19

Are unconverted quote records ever removed in Magento?

1 Answers1