Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2005
    Location
    Santa Cruz, CA
    Posts
    26
    Plugin Contributions
    0

    Default 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
    www.powersquid.net

  2. #2
    Join Date
    Sep 2004
    Location
    Western Massachusetts
    Posts
    2,945
    Plugin Contributions
    5

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

    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:
    Code:
    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:
    Code:
    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:
    Code:
    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...
    Last edited by bunyip; 26 May 2006 at 04:55 AM.
    Neville
    An assumption is what you arrive at when you get tired of thinking...

  3. #3
    Join Date
    May 2006
    Posts
    105
    Plugin Contributions
    0

    Default Re: Can you send emails to ONLY Customers that have ordered 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...

  4. #4
    Join Date
    Jan 2004
    Posts
    66,364
    Blog Entries
    7
    Plugin Contributions
    274

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

    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.
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

  5. #5
    Join Date
    Dec 2004
    Posts
    1,031
    Plugin Contributions
    0

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

    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....
    Live and learn... the Zen way.

  6. #6
    Join Date
    Aug 2007
    Posts
    62
    Plugin Contributions
    0

    Idea or Suggestion Re: Can you send emails to ONLY Customers that have ordered something?

    Quote Originally Posted by bunyip View Post
    Want one for customers who purchased product #103? here it is:
    Code:
    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.

  7. #7
    Join Date
    Aug 2007
    Posts
    62
    Plugin Contributions
    0

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

    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', '');

 

 

Similar Threads

  1. Replies: 0
    Last Post: 14 Nov 2011, 07:49 PM
  2. Replies: 2
    Last Post: 27 Apr 2010, 10:05 AM
  3. Can you set a minimum dollar amount that has to be ordered
    By inklingsolutions in forum Setting Up Categories, Products, Attributes
    Replies: 1
    Last Post: 2 Apr 2009, 11:27 PM
  4. Can you set a minimum dollar amount that has to be ordered
    By inklingsolutions in forum Setting Up Categories, Products, Attributes
    Replies: 1
    Last Post: 2 Apr 2009, 11:24 PM
  5. Contacting Customers that have not ordered anything
    By Nellie in forum Managing Customers and Orders
    Replies: 0
    Last Post: 20 Jun 2006, 06:08 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR