List Magento customers that haven't ordered in the past 12 months

Question

In Magento v1.9 I need a MYSQL select that would do the following:

• list all the customers that did not place any orders in the past 12 months

Thanks!

Answer 1

Basically you want customers that have never placed an order or customers whose last order was over 12 months ago. You could do it using a customer collection:

$date = new DateTime();
$date->sub(new DateInterval('P12M'));
$customers = Mage::getModel('customer/customer')->getCollection();
$customers->getSelect()->joinLeft(
    array('o' => Mage::getSingleton('core/resource')->getTableName('sales/order')),
    'o.customer_id = e.entity_id',
    array(
        'last_order_date' => 'MAX(o.created_at)',
    )
);
$customers->groupByAttribute('entity_id')
    ->getSelect()
    ->having('last_order_date < ?',$date->format('Y-m-d'))
    ->orHaving('last_order_date IS NULL');

Or if you want the mysql for that just call $customers->getSelect();

And that will give you the following MYSQL query

SELECT `e`.*, MAX(o.created_at) AS `last_order_date` FROM `customer_entity` AS `e` LEFT JOIN `sales_flat_order` AS `o` ON o.customer_id = e.entity_id WHERE (`e`.`entity_type_id` = '1') GROUP BY `e`.`entity_id` HAVING (last_order_date < '2014-03-26') OR (last_order_date IS NULL)

Hope that helps!