Get a list of all customers which have bought something

Question

How can I get a list of customers who bought at least something and if is probable order them by number of orders? From what I have understood _order_count meta key exists and is set to number of order placed as integer if they have placed an order.

This query ignores order count and just shows all users.

$args = array(
     'role' => 'customer',
     'number' => -1,
     'order' => 'ASC',
     'orderby' => 'meta_value',
     'meta_query' => array(
          array(
               'key'     => '_order_count',
               'value'   => array(''),
               'compare' => 'NOT IN'
          )
    )
);

$query = new WP_User_Query($args);

This is a table which will echo info on how much user has spent and how many orders he placed

<?php if (! empty($query->get_results())) : ?>

     <table class="table">

          <thead>
                    <tr>
                         <th data-sort="string">Name</th>
                         <th data-sort="string">Email</th>
                         <th data-sort="float">Total spent (<?php echo get_option('woocommerce_currency'); ?>)</th>
                         <th data-sort="int">Order placed</th>
                    </tr>
          </thead>

          <tbody>

          <?php foreach ($query->get_results() as $user) : ?>

               <?php $customer = new WP_User($user->ID); ?>

               <tr>
                    <td><?php echo $user->display_name; ?></td>
                    <td><?php echo $user->user_email; ?></td>
                    <td><?php echo wc_get_customer_total_spent($user->ID); ?></td>
                    <td>
                    <?php 
                    $customer_orders = get_posts(array(
                         'numberposts' => -1,
                         'meta_key'    => '_customer_user',
                         'meta_value'  => $user->ID,
                         'post_type'   => wc_get_order_types(),
                         'post_status' => array('wc-pending', 'wc-processing', 'wc-completed') //array_keys(wc_get_order_statuses()),
                    ));
                    echo count($customer_orders);
                    ?>
                    </td>
               </tr>

          <?php endforeach; ?>

          </tbody>
     </table>

<?php endif; ?>

Answer 1

Try the following code that uses a very light SQL query to get $customers Ids. For customer orders count you can use the dedicated function wc_get_customer_order_count().

<?php
global $wpdb;
$customer_ids = $wpdb->get_col("SELECT DISTINCT meta_value  FROM $wpdb->postmeta
    WHERE meta_key = '_customer_user' AND meta_value > 0");
$currency = ' (' . get_option('woocommerce_currency') . ')';
if (sizeof($customer_ids) > 0) : ?>
<table class="table">
    <thead>
        <tr>
            <th data-sort="string"><?php _e("Name", "woocommerce"); ?></th>
            <th data-sort="string"><?php _e("Email", "woocommerce"); ?></th>
            <th data-sort="float"><?php _e("Total spent", "woocommerce"); echo $currency; ?></th>
            <th data-sort="int"><?php _e("Orders placed", "woocommerce"); ?></th>
        </tr>
    </thead>
    <tbody>
    <?php foreach ($customer_ids as $customer_id) :
        $customer = new WP_User($customer_id); ?>
        <tr>
            <td><?php echo $customer->display_name; ?></td>
            <td><?php echo $customer->user_email; ?></td>
            <td><?php echo wc_get_customer_total_spent($customer_id); ?></td>
            <td><?php echo wc_get_customer_order_count($customer_id); ?></td>
        </tr>
    <?php endforeach; ?>
    </tbody>
</table>
<?php endif; ?>

Tested and works.

Users meta_key _order_count and _order_total doesn't exist until WC_Customer_Data_Store get_order_count() and get_total_spent() methods have run once for each customer. Also the related meta values are also updated when those methods are executed for a customer.

The functions wc_get_customer_total_spent() and wc_get_customer_order_count() execute those methods and refresh the corresponding user meta data.