Exporting Segmented Subscribed Newsletter Customers

Question

I'm trying to update segments in MailChimp and I'm not sure how to get this info out of Magento. Is there an easy way to export a list of customer email addresses that:

A) purchased a specific SKU or series of SKUs that begin with XYZ

AND

B) are subscribed to the store's newsletter?

I'm guessing there's probably a simple SQL statement that can do this-- but don't know enough about the Magento 1.9 database structure to do it. Any assistance would be greatly appreciated!

Thanks

Answer 1

As I do not see any good possibility to export this via the admin backend (except there might be good chances to do it with customer segmentation of EE), here's the SQL for 'series of SKUs that begin with XYZ':

SELECT email
FROM customer_entity as ce
JOIN newsletter_subscriber as ns ON ns.subscriber_email = ce.email AND ns.subscriber_status = 1
JOIN sales_flat_order as sfo ON sfo.customer_id = ce.entity_id
JOIN sales_flat_order_item as sfoi ON sfo.entity_id = sfoi.order_id
JOIN catalog_product_entity as cpe ON cpe.entity_id = sfoi.product_id
WHERE cpe.sku LIKE 'XYZ%';

There are a lot of joins in and there might be a potential to optimize, but as you're only runny the query once and not during every page load, this should do the job.