Can you send emails to ONLY Customers that have ordered something?

I have searched the forums for anything on this and have been unable to find anything.

I want to be able to ATLEAST send emails to customers that have ONLY ORDERED, rather than EVERYONE THAT HAS CREATED AN ACCOUNT. I don't see this option anywhere.

EVEN BETTER would be a way to send emails to customers of specific products. Is this possible?

Is there a plugin or module that has this functionality?

Tanner
https://www.powersquid.net

The functionality doesn't exist in a standard cart, but it's certainly not difficult build new 'audience lists'. It's just a matter of adding new rows to the query_builder table in the database: the tricky bit is getting the select query right.

For example, if you want a new list that selects customers who have ordered something from the store, without time limits, you could use this query:

select c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o WHERE c.customers_id = o.customers_id and o.orders_id > 0 GROUP BY c.customers_email_address order by c.customers_lastname, c.customers_firstname ASC

If you want a query to add that to the database with all the other fields, you could run this from Admin > Tools > Install SQL Patches:

INSERT INTO query_builder (`query_id`, `query_category`, `query_name`, `query_description`, `query_string`, `query_keys_list`) VALUES ('', 'email,newsletters', 'All customers who have purchased', 'All customers who have ever purchased anything, ignoring newsletter-subscription status.', 'select c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o WHERE c.customers_id = o.customers_id AND o.orders_id > 0 GROUP BY c.customers_email_address ORDER by c.customers_lastname, c.customers_firstname ASC', '');

Want one for customers who purchased product #103? here it is:

INSERT INTO query_builder (`query_id`, `query_category`, `query_name`, `query_description`, `query_string`, `query_keys_list`) VALUES ('', 'email,newsletters', 'All customers who have purchased product #103', 'All customers who have ever purchased product #103, ignoring newsletter-subscription status.', 'select c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o, TABLE_ORDERS_PRODUCTS op WHERE c.customers_id = o.customers_id AND o.customers_id = c.customers_id AND o.orders_id = op.orders_id AND op.products_id = 103 GROUP BY c.customers_email_address ORDER by c.customers_lastname, c.customers_firstname ASC', '');

The possibilities are only limited by your ability with sql queries

EDIT: by the way, the term 'active customer' in the drop-down box for the audience list means a customer who has purchased something...

i want to do a similar thing...
i have a mailing list of 6000 subscribers (using the subscribtion module)
i want to group them into groups of 500 or 250 each...

so i went to the query_builder, made a new table and here's the value in the query_string:

select s.email_address as customers_email_address from TABLE_SUBSCRIBERS as s LIMIT 0 , 250

honestly, i dont know if thats working fine. i dont know how to test the query_builder either... any help...

A handy way to test is to grab the Email Address Exporter contribution from the downloads area under Admin Tools.

It'll let you export email addresses in various formats ... the key here being that it uses the same sets of queries to do it... and thus you could quickly test to see how many records were being located with the query.

what would the sql be for selecting customers that NEVER ordered?

I tried this but it is not working:

INSERT INTO query_builder (query_id, query_category, query_name, query_description, query_string, query_keys_list) VALUES ('', 'email,newsletters', 'All customers who have purchased', 'All customers who have ever purchased anything, ignoring newsletter-subscription status.', 'select c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o WHERE c.customers_id = o.customers_id AND o.orders_id = 0 GROUP BY c.customers_email_address ORDER by c.customers_lastname, c.customers_firstname ASC', '');

I changed "o.orders_id > 0" to "o.orders_id = 0" but no selection is made.

Please help....

bunyip:

Want one for customers who purchased product #103? here it is:

INSERT INTO query_builder (query_id, query_category, query_name, query_description, query_string, query_keys_list) VALUES ('', 'email,newsletters', 'All customers who have purchased product #103', 'All customers who have ever purchased product #103, ignoring newsletter-subscription status.', 'select c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o, TABLE_ORDERS_PRODUCTS op WHERE c.customers_id = o.customers_id AND o.customers_id = c.customers_id AND o.orders_id = op.orders_id AND op.products_id = 103 GROUP BY c.customers_email_address ORDER by c.customers_lastname, c.customers_firstname ASC', '');

Okay, just for my own future reference and to help others with this problem:

TO SEND AN EMAIL TO ALL CUSTOMERS WHO HAVE EVER PURCHASED A PARTICULAR PRODUCT:

I just copied the above coding (thanks bunyip!) into the SQL patch tool in the admin site, and changed the three instances of product #103 to the product ID I want, and it created a query in mySQL AND! added a line in the send email tool (under the dropdown menu of who to send email to) called "All customers who have purchased product #xxx".  

I've seen this question throughout the forum, so this should solve many people's problems on how to do this. :clap:

Continuing the last post, to add an email group for ALL CUSTOMERS WHO HAVE NEVER PURCHASED A GIVEN PRODUCT (i.e., have purchased anything except product x), use this code, same instructions as last post:

INSERT INTO query_builder (query_id, query_category, query_name, query_description, query_string, query_keys_list) VALUES ('', 'email,newsletters', 'All customers who have NOT purchased product #196', 'All customers who have NEVER purchased product #196, ignoring newsletter-subscription status.', 'SELECT c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c WHERE c.customers_id NOT IN (SELECT c.customers_id FROM TABLE_CUSTOMERS c, TABLE_ORDERS o, TABLE_ORDERS_PRODUCTS op WHERE c.customers_id=o.customers_id AND o.orders_id=op.orders_id AND op.products_id=196) GROUP BY c.customers_email_address ORDER BY c.customers_lastname, c.customers_firstname ASC', '');