Get all abandoned carts in Magento

Question

How could I get all the abandoned carts with customer's email (programatically)? What does Magento consider as being abandoned? Not updated in the last day?

Answer 1

If you go to

Magento Admin -> Reports -> Shopping Cart -> Abandoned Carts

You will see that Magento let's you pick the date, customer email etc. filters on the quote report table to get the abandoned carts. Magento leaves it up to you to decide what you want to call an abandoned cart. The only thing you need to check to ensure that an order hasn't already been placed for a cart is the is_active flag in sales_flat_quote table should be 1.This flag is set to 0 as soon as an order is placed for a quote/cart.

This is the query used to fetch list of abandoned carts my Magento's default functionality without any date etc. filters:

SELECT
  `main_table`.*,
  (main_table.base_subtotal_with_discount * main_table.base_to_global_rate) AS `subtotal`,
  `cust_email`.`email`,
  `cust_fname`.`value`                                                      AS `firstname`,
  `cust_lname`.`value`                                                      AS `lastname`,
  CONCAT_WS(' ', cust_fname.value, cust_lname.value)                        AS `customer_name`
FROM `sales_flat_quote` AS `main_table`
  INNER JOIN `customer_entity` AS `cust_email` ON cust_email.entity_id = main_table.customer_id
  INNER JOIN `customer_entity_varchar` AS `cust_fname`
    ON cust_fname.entity_id = main_table.customer_id AND cust_fname.attribute_id = 5
  INNER JOIN `customer_entity_varchar` AS `cust_lname`
    ON cust_lname.entity_id = main_table.customer_id AND cust_lname.attribute_id = 7
WHERE (items_count != '0') AND (main_table.is_active = '1')

Have a look at the code in:

Mage_Adminhtml_Block_Report_Shopcart_Abandoned_Grid::_prepareCollection() 

to get a better idea of how you could programatically add more filters to this abandoned cart collection. The SQL query I pasted above is derived from the collection in this class.

Answer 2

Solutions: Yes this is known bug caused by the middle name attribute in recent versions of Magento. Here is the solution for this issue, we need to insert records for middle name attribute for old customers, here is the script to update database table

require 'app/Mage.php';
Mage::app('default');

$db_read = Mage::getSingleton('core/resource')->getConnection('core_read');
$tablePrefix = (string) Mage::getConfig()->getTablePrefix();

$sql = 'SELECT entity_id FROM ' . $tablePrefix .'customer_entity where entity_id NOT IN (SELECT entity_id FROM '. $tablePrefix.'customer_entity_varchar where attribute_id = 6)';

$db_write1= Mage::getSingleton('core/resource')->getConnection('core_write');
$value=$db_read->query($sql);
echo "<br>Total: ".count($value);
$i =1;
while ($row = $value->fetch())
{
    //print_r($row);
    echo "<br>".$i." - ".$row['entity_id'];
    $sql = 'INSERT INTO '. $tablePrefix.'customer_entity_varchar (entity_type_id, attribute_id, entity_id) VALUES (1, 6, '.$row['entity_id'].' )';
    $db_write1->query($sql);  
};