I have a Magento EE 1.14.0.1 and I have access to mysql. What I am trying to do is write a query in SQL that will give me a listing of all customers first name, last name, email address AND the website ID that they created an account on (this magento install has two website stores on it) from the last 30 days who not only made a purchase on the site but also created an account AND signed up to the newsletter.
I have the current query (below) which I believe is giving me a listing of every ones email address who made a purchase. however I do not know how to alter the sql
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 !='' group by ce.email;