How to export all customers based on ordered items SKU?

Question

How to find all customers who ordered a certain SKU ?

Answer 1

The simplest way to do that, is to run a SQL statement. For example with the following statement you get email, firstname and lastname for all customer who have ordered a product with the specified sku:

select distinct(o.customer_email), o.customer_firstname, o.customer_lastname 
from sales_order o 
join sales_order_item oi on o.entity_id = oi.order_id 
where oi.sku = '[YOUR SKU HERE]';

Answer 2

Execute below script.

use Magento\Framework\App\Bootstrap;
require 'app/bootstrap.php';
$bootstrap = Bootstrap::create(BP,$_SERVER);

$objectManager = $bootstrap->getObjectManager();
$state = $objectManager->get('\Magento\Framework\App\State');
$state->setAreaCode('frontend');

$storeManager = $objectManager->get("\Magento\Store\Model\StoreManagerInterface");
$storeId = $storeManager->getStore()->getId();
$websiteId = $storeManager->getStore($storeId)->getWebsiteId();
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();
$sku = '24-WB04';

$Itemcollection = $objectManager->create('Magento\Sales\Model\Order\Item')->getCollection();
$Itemcollection->getSelect()->join('sales_order as order',"main_table.order_id = order.entity_id",array('order.customer_email'))->where("main_table.sku = '".$sku."'")->group(array('order.customer_email'));
print_r($Itemcollection->getData());

Answer 3

To get all the customer with all ordered SKU use the following:-

SELECT o.customer_email,o.customer_firstname,o.customer_lastname,oi.qty_ordered 
AS numOfTimes FROM sales_order AS o
LEFT JOIN
sales_order_item AS oi ON o.entity_id=oi.order_id

For the specific SKU try the below:-

SELECT o.customer_email,o.customer_firstname,o.customer_lastname,oi.qty_ordered 
AS numOfTimes FROM sales_order AS o
LEFT JOIN
sales_order_item AS oi ON o.entity_id=oi.order_id WHERE oi.sku="YOUR_SKU"