Magento2 product recall: retrieve customers that bought a certain product
Sometimes a manufacturer issues a recall, for instance for a polluted food product. If that is the case, you need to be able to send an email to all customers that purchased that particular product in a certain period - and fast!
Magento Commerce has an option to create a customer segment for this, but it only contains registered customers, not customer that bought the product as guest.
You can run this query to quickly retrieve all customers that bought and succesfully paid for a particular product in a certain period.
--
-- RECALL ACTION QUERY
--
-- Return a list of customers,
-- both logged in and not,
-- that purchased product X,
-- between dates Y and Z,
-- with an order status that indicates their payment was received.
SET @date_from = '2020-01-01';
SET @date_to = '2020-02-01';
SET @sku = '18292302';
SELECT customer_email, customer_prefix, customer_firstname, customer_middlename, customer_lastname, state, sales_order.created_at FROM sales_order
WHERE state IN ('processing', 'complete')
AND entity_id IN (
SELECT order_id FROM sales_order_item
WHERE created_at BETWEEN @date_from AND @date_to
AND sku = @sku
)
GROUP BY customer_email;