Here is an SQL query I construct to extract customers with billing and shipping address from a Magento 1.9 store.
This joins on table sales_flat_order_address to ensure that only customers that have ordered are included.
You may need to adjust the entity attribute id’s – if you look in those tables it is pretty obvious what each value represents.
SELECT ce.entity_id AS customer_id, ce.email, cev2.value AS firstname, cev3.value AS lastname, caet.value AS billing_first_line, caev1.value AS billing_town, caev2.value AS billing_postcode, sfoa.entity_id AS sfoa_entity_id, sfoa.street AS shipping_first_line, sfoa.city AS shipping_city, sfoa.postcode AS shipping_postcode FROM customer_entity ce -- first name INNER JOIN customer_entity_varchar cev2 ON (ce.entity_id = cev2.entity_id AND cev2.attribute_id = 5) -- last name INNER JOIN customer_entity_varchar cev3 ON (ce.entity_id = cev3.entity_id AND cev3.attribute_id = 7) -- address first line INNER JOIN customer_address_entity cae ON (ce.entity_id = cae.parent_id) INNER JOIN customer_address_entity_text caet ON (cae.entity_id = caet.entity_id) -- town INNER JOIN customer_address_entity_varchar caev1 ON (cae.entity_id = caev1.entity_id AND caev1.attribute_id = 26) -- postcode INNER JOIN customer_address_entity_varchar caev2 ON (cae.entity_id = caev2.entity_id AND caev2.attribute_id = 30) -- sales INNER JOIN sales_flat_order sfo ON (ce.entity_id = sfo.customer_id) -- shipping address INNER JOIN sales_flat_order_address sfoa ON (sfo.entity_id = sfoa.parent_id) WHERE sfo.status = 'complete' |