Finding out what customers ordered a specific product by SKU can be hard, especially if you don’t have an advanced report extension, as the default reports of a Magento 1.9 store don’t have this.
First and foremost: I know, Magento 2 is the new kid on the block. But there are a lot of stores running Magento 1.9.x or a flavor of it, and cannot upgrade due to costs, custom code or a bunch of factors.
This is not going to be a pretty interface thing, what you will get is a table that you can export into Excel or another format like a .CSV file, a .XML file etc.
Why would you need this?
Well, maybe you want to import people into dedicated newsletter lists by product, or by type of product. Or you need to do a recall or warn people of a manufacturer’s defect on that product.
Any reason you might have, the good thing is that you can, and if you’re reading this it only takes a couple of seconds (minutes at most).
How to get the details of the people that bought a product by SKU?
We’re going to go through this step by step. You will not harm your installation, data, or anything else while doing this. It’s completely safe. This was tested on Magento 2.3.x and 1.9.x.
This being said, you’re working with private customer data. Be aware of that, and make sure you keep the data you get safe.
In what’s coming next, we’re going to run a query against our database, that will give us the data we need.
Step one
Log in to phpMyAdmin, MySQL server or whatever tool you are using to manage your database. A lot of people run Cpanel and there you can find a link to your phpMyAdmin instance.
After doing that, select your store’s database, by clicking on its name or by the specific method based on how you run queries on your database.
Tip: can’t find your store’s database name on Magento 1? Go to your ‘File Manager’ or log in via FTP, and look into the file local.xml, located in public_html/app/etc/local.xml. In this file, usually around line 46, between <dbname><![CDATA[ and ]]></dbname> you will see your database name.
Step two
Once you database is selected, run the below code. In phpMyAdmin, you do this by goind to the SQL tab, pasting the code in the text area and hitting Go.
Here is the SQL code for Magento 2.x:
SELECT t1.customer_email, t3.sku, t1.created_at, t1.customer_firstname AS 'fname', t1.customer_lastname AS 'lname'
FROM sales_order as t1
LEFT JOIN sales_order_item as t3 ON t1.entity_id = t3.order_id
LEFT JOIN customer_address_entity as t4 ON t4.entity_id = t1.entity_id
LEFT JOIN customer_address_entity as t5 ON t5.entity_id = t1.entity_id
WHERE t3.sku = 'YOURSKUHERE'Here is the SQL code for Magento 1.9.x:
SELECT t1.email, t3.sku, t2.created_at, t4.value AS 'fname', t5.value AS 'lname'
FROM customer_entity as t1
LEFT JOIN sales_flat_order as t2
on t1.entity_id = t2.customer_id
LEFT JOIN sales_flat_order_item as t3
ON t2.entity_id = t3.order_id
LEFT JOIN customer_entity_varchar as t4
ON t4.entity_id = t1.entity_id
LEFT JOIN customer_entity_varchar as t5
ON t5.entity_id = t1.entity_id WHERE t4.attribute_id = 5 AND t5.attribute_id = 7
AND t3.sku = 'YOURSKUHERE'Don’t forget to replace YOURSKUHERE with the SKU of the product you want to find who ordered it.
Note for Magento 1.9: if by any chance this doesn’t work, replace 5 and 7 with the attribute code of the first name and last name customer attribute id’s.
Step three
The result you get from running that query is a table that has the following columns:
- the e-mail of the person who bought the product;
- the actual SKU you inputted;
- the date of purchase (date when the order was placed);
- first name of the person who bought the product;
- last name of the person who bought the product;
You can now export this data, and use it as you need to.
Concluding the custom report on Magento 2 and 1.9.x
Basically, you made your own report by running the above query against your database. It might be uglier and dirtier than a pretty table and chart, but it does the job.
If this article helped you, share it! Others might find it useful. Thanks!
