4

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?

8

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.

  • 2
    looks there is a bug in 1.9.x: the query generated by Mage_Reports_Model_Resource_Quote_Collection::addCustomerData uses an innerjoin to retrieve the middle name of the customer, in case you have disabled the middle name it happens you will not see any data for the abandon carts ... – Fra Nov 30 '15 at 5:20
0

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);  
};
  • How come this is a solution? – Rinto George May 2 '18 at 12:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.