Skip to content

Instantly share code, notes, and snippets.

@drewgillson
Created June 2, 2012 13:52
Show Gist options
  • Save drewgillson/2858502 to your computer and use it in GitHub Desktop.
Save drewgillson/2858502 to your computer and use it in GitHub Desktop.
Magento customer wishlist report
SELECT b.email, c.value AS name, a.updated_at, d.added_at, d.product_id, e.name, SUM(g.qty_ordered) AS purchased
FROM `wishlist` AS a
INNER JOIN customer_entity AS b ON a.customer_id = b.entity_id
INNER JOIN customer_entity_varchar AS c ON a.customer_id = c.entity_id AND c.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'firstname' AND entity_type_id = b.entity_type_id)
INNER JOIN wishlist_item AS d ON a.wishlist_id = d.wishlist_id
INNER JOIN catalog_product_flat_1 AS e ON d.product_id = e.entity_id
LEFT JOIN sales_flat_order AS f ON f.customer_email = b.email
LEFT JOIN sales_flat_order_item AS g ON (f.entity_id = g.order_id AND g.sku LIKE CONCAT(e.sku,'%') AND g.product_type = 'simple')
GROUP BY b.email, c.value, a.updated_at, d.added_at, d.product_id, e.name
@yamini
Copy link

yamini commented Oct 4, 2016

Thank you. This was very helpful.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment