List of Customers who purchased a specific Woocommerce Product

Sometimes you need to find out who has purchased a specific product and contact them

This snippet will use SQL SELECT to query the database and produce a table of clickable order numbers (to take you quickly to the specific order edit screen), email address, First Name and Last Name

function ns_sup_nustart_admin_pg(){
    echo '
	<div class="wrap">
		<h2>Nustart Tools</h2>';

        echo '<p>Nustart Support Engine</p>';



// Access WordPress database
global $wpdb;

$siteurl = get_site_url();
 
// Select Product ID
$product_id = 25090;
       
// Find billing emails in the DB order table
$statuses = array_map( 'esc_sql', wc_get_is_paid_statuses() );
$customer_emails = $wpdb->get_results("
   SELECT DISTINCT pm.meta_value, pm1.meta_value as meta_value_1, pm2.meta_value as meta_value_2, i.order_id FROM {$wpdb->posts} AS p
   INNER JOIN {$wpdb->postmeta} AS pm ON p.ID = pm.post_id
   INNER JOIN {$wpdb->postmeta} AS pm1 ON p.ID = pm1.post_id
   INNER JOIN {$wpdb->postmeta} AS pm2 ON p.ID = pm2.post_id
   INNER JOIN {$wpdb->prefix}woocommerce_order_items AS i ON p.ID = i.order_id
   INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS im ON i.order_item_id = im.order_item_id
   WHERE p.post_status IN ( 'wc-" . implode( "','wc-", $statuses ) . "' )
   AND pm1.meta_key IN ( '_billing_first_name' ) 
   AND pm2.meta_key IN ( '_billing_last_name' )
   AND pm.meta_key IN ( '_billing_email' )
   AND im.meta_key IN ( '_product_id', '_variation_id' )
   AND im.meta_value = $product_id
");
 
// Print array on screen
/*
echo '<pre>';
print_r( $customer_emails );
echo '</pre>';
*/
echo '<table>
        <tr>
            <th>Order Number</th>
            <th>Email Address</th>
            <th>First Name</th>
            <th>Last Name</th>
        </tr>';
foreach ($customer_emails as $customer_email) {
    echo '<tr>
            <td><a href="'.$siteurl.'/wp-admin/post.php?post='.$customer_email->order_id.'&action=edit">'.$customer_email->order_id.'</a></td>
            <td>'. $customer_email->meta_value.'</td>
            <td>'. $customer_email->meta_value_1.'</td>
            <td>'. $customer_email->meta_value_2.'</td>
        </tr>';
}
    echo '
    </table>
    </div>';


}

Where to put this code?

I created a quick plugin to create a scratchpad called Nustart Tools in the menu – the product purchaser details show show on this screen. The Menu page calls the callback function ns_sup_nustart_admin_pg we see above

<?php
/**
 * Plugin Name:Nustart Support
 * Plugin URI: https://nustart.solutions/
 * Description: Adding Custom Functionality you requested for your site
 * Version: 1.0.1
 * Author: Anne Allen
 * Author URI:  https://nustart.solutions/
 */

//Admin Pages
add_action( 'admin_menu', 'ns_sup_admin_menu' );

function ns_sup_admin_menu(){

    add_menu_page( 'Nustart  Tools', 'Nustart  Tools', 'manage_options', 'lc-admin-page.php', 'ns_sup_nustart_admin_pg', 'dashicons-dashboard',35  );
  
}

Props to Rodolfo Melogli over at Business Bloomer for the code that I started the process with – Thanks Rodolfo!