Finding customers with 0 orders - Ecommerce Forums
Results 1 to 5 of 5

Thread: Finding customers with 0 orders

  1. #1

    Finding customers with 0 orders

    Help please!
    I am trying to export a list of customers who got as far as creating an customer record but never placed an order. This code returns a list of customer id's that are not assigned to any orders, but does not take into account anonymous checkout or where the customer tried 2+ times to create an account and finally placed an order. Is there a way to resolve the duplicate accounts?

    SELECT

    CustomerID
    , FirstName
    , LastName
    , EmailAddress
    , CompanyName

    FROM Customers

    WHERE
    customers.CustomerID NOT IN (SELECT orders.customerid FROM orders)
    AND CustomerType IS NULL
    AND CustomerID > 0
    AND EmailSubscriber = 'Y'

  2. #2
    SELECT Customers.EmailAddress
    FROM Customers
    LEFT JOIN Orders ON Orders.CustomerID=Customers.CustomerID
    WHERE Orders.CustomerID IS NULL

  3. #3
    Thank you, dmoore! That did the trick. I had a feeling that simpler was better, but I could not see the forest for the trees

  4. #4
    Hi all... I am resurrecting this thread because - after 2 days of SQL agony - I cannot make it work. The issue is the multiple customer id's per email address thanks to anonymous checkout; and then of course there is sorting out folks who use multiple emails but same first/last name & address.

    Does anyone have some code that more or less accurately tells me who has never ordered; and (separately), who has ordered only once?

    The code above is accurate for customerID, but does not filter out those with the same email address but multiple customer numbers.

    HELP anybody??!!

  5. #5
    Quote Originally Posted by MelanieF View Post
    Hi all... I am resurrecting this thread because - after 2 days of SQL agony - I cannot make it work. The issue is the multiple customer id's per email address thanks to anonymous checkout; and then of course there is sorting out folks who use multiple emails but same first/last name & address.

    Does anyone have some code that more or less accurately tells me who has never ordered; and (separately), who has ordered only once?

    The code above is accurate for customerID, but does not filter out those with the same email address but multiple customer numbers.

    HELP anybody??!!
    I don't allow anonymous checkout because of the problems that it causes (multiple accounts, tracking numbers, download files, etc). What I do is about once per year I export all my customers, sort by name and city and the find the duplicates and then merge the accounts and then delete the duplicate account. The real problem is the Anonymous checkout....

Similar Threads

  1. Session Lost - Customers Can Place Orders
    By gifty in forum Site Performance
    Replies: 0
    Last Post: 08-29-2014, 10:02 AM
  2. Customers that have never puchased
    By saltysupply in forum Reporting
    Replies: 4
    Last Post: 05-31-2014, 09:19 PM
  3. Is there a way for customers to repeat previous orders?
    By bjcbranding in forum Order Management
    Replies: 0
    Last Post: 05-08-2013, 04:09 PM
  4. Customers Cant Log In
    By Dani in forum Site Performance
    Replies: 0
    Last Post: 07-08-2012, 09:36 AM
  5. Prevent Orders With No Shipping Orders Chosen
    By AWCthreads in forum Tips & Tricks
    Replies: 2
    Last Post: 06-24-2012, 12:09 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
  •