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

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

September 1, 2026 · Illustrated author avatarBy Stefan Bogdanescu
List Magento customers that haven't ordered in the past 12 months

Retrieving Magento Customers Who Haven’t Placed Orders in the Last Year

As an experienced developer, I frequently encounter requests like this from clients who use the Magento platform for their e-commerce stores. The need to identify customers who haven’t made a purchase recently is crucial for targeted marketing campaigns and customer retention strategies.

Understanding the Data Schema

Before diving into the SQL query, let’s briefly examine the relevant database tables in Magento v1.9:

  • customers - Stores basic customer information (email, created_at, updated_at).
  • sales_flat_order - Contains order data, including customer ID (customer_id) and creation date (created_at).

Writing the SQL Query

To list all customers who haven’t placed any orders in the past 12 months, you can use a combination of subqueries and JOINs. Here’s an example query that achieves this:

SELECT c.entity_id, c.created_at, o.customer_id
FROM `customers` AS c
LEFT OUTER JOIN (
    SELECT customer_id FROM sales_flat_order WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 YEAR)
) AS o ON c.entity_id = o.customer_id
WHERE o.customer_id IS NULL

Let’s break this down:

  • The subquery (SELECT customer_id FROM sales_flat_order WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 YEAR)) identifies customers who have placed orders in the last year.
  • We then join this result set with the main customers table on the entity_id.
  • The WHERE o.customer_id IS NULL clause filters out the results, returning only those customers present in the customers table but not in the subquery (i.e., customers who haven’t ordered in a year).

Optimizing Performance

For large datasets, you can optimize this query by adding indexes to the relevant columns:

ALTER TABLE sales_flat_order ADD INDEX idx_sfo_created_at ON created_at;

Adding an index on created_at will significantly speed up the subquery.

Best Practices and Further Reading

When dealing with SQL queries in Magento, always keep these best practices in mind:

  • Use Magento’s built-in indexes for performance-critical columns.
  • Use the correct table prefix (e.g., sales_flat_order) instead of assuming a standard naming convention.
  • If you’re working on a live site, always test your queries thoroughly before executing them.

For more advanced Magento development techniques and tips from experienced developers, check out our recent blog post on Optimizing Magento Performance with Advanced Indexing Strategies.

Conclusion

Retrieving customers who haven’t ordered in a year requires a careful balance between SQL syntax and an understanding of the Magento database schema. By using a combination of subqueries, JOINs, and proper indexing, you can efficiently identify these customers for your marketing campaigns.

Remember to test your queries thoroughly before deploying them on a live site, and always consider the performance implications of your database operations.

Happy coding!

Magento 1 Database Email Orders

Our network