How do I delete customers with no orders?

  • Resolved Sarah

    (@sareejoh)


    8 years, 7 months ago

    One of the sites I’m working on has thousands of old users that never placed an order. I do not see a way to bulk delete them from the woocommerce reports and when I go to WordPress users I can’t tell which users have placed an order. Is there a MySQL statement I can use to find and delete customers without an order directly from the database? Or another solution? Thank you!

    https://www.ads-software.com/plugins/woocommerce/

Viewing 7 replies - 1 through 7 (of 7 total)
  • Plugin Contributor Mike Jolley (a11n)

    (@mikejolley)

    What role do the users have? Are they all ‘customer’ or ‘subscriber’?

    Thread Starter Sarah

    (@sareejoh)

    The users are all the ‘customer’ role.

    Plugin Contributor Mike Jolley (a11n)

    (@mikejolley)

    BACKUP FIRST and USE AT YOUR OWN RISK. No crying if you deleting something by accident please ??

    Run a select first to ensure you’re not deleting anyone important;

    SELECT * from wp_users where wp_users.ID not in (
	SELECT meta_value FROM wp_postmeta WHERE meta_key = '_customer_user'
) AND wp_users.ID not in (
	select distinct(post_author) from wp_posts
)

    That selects users with NO ORDERS and NO POSTS

    If it looks good, change SELECT * to DELETE and run the query again.

    After doing that, clear up user meta with:

    delete from wp_usermeta where wp_usermeta.user_id not in (select ID from wp_users);

    You need to change the wp_ prefixes in these SQL queries to whatever your DB uses.

    Thread Starter Sarah

    (@sareejoh)

    Tested on a localhost version first to be certain. This worked perfectly. Thank you Mike!

    1) How would I do the same thing, but place a date restriction of 180 days?

    I don’t want to delete a customer who created their account today, just those that were created and never used.

    2) How would I delete customers WITH orders if they have not placed an order within the last 2 years?

    I would love a WooCommerce plugin that automatically deleted old orders and customers based on criteria like this. Better yet, if they were “archived” in a way that they would no longer be in the WooCommerce database, but could be “restored” if needed.

    With 47,000 orders and 14,000 customers, WooCommerce works at a snail’s pace. However, the majority of those are not connected to “active” customers.

    Hope this is not a dumb question…

    This can be used with myphpAdmin correct?

    I tried the select version and it crashed my database. I do have 28k customers, so I know that doesn’t help.

    Trying to clean out my DB or customers with no orders but no luck finding a solution. Everything I try either hangs up or crashes the DB.

    Any suggestions? I am lost.

    Thank you for you time.

    Hello Ron,

    I feel your pain! I can not run this SELECT on my 14,000 customers. Just says “loading” and never progresses.

    In the end, I don’t believe that the WooCommerce database is designed in a way that can handle medium sized (or larger) stores like ours ??

    I don’t know much about databases, but there must be more efficient ways to structure the data because it’s painfully slow for basic tasks. I will be considering taking the plunge to Shopify Plus. The only problem is, it’s $2,000 per month!

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘How do I delete customers with no orders?’ is closed to new replies.