Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Export WooCommerce Shop Customers…
SELECT
MAX(CASE WHEN pm.meta_key = '_customer_user' THEN pm.meta_value END) as 'customer_id',
MAX(CASE WHEN pm.meta_key = '_billing_first_name' THEN pm.meta_value END) as 'billing_first_name',
MAX(CASE WHEN pm.meta_key = '_billing_last_name' THEN pm.meta_value END) as 'billing_last_name',
MAX(CASE WHEN pm.meta_key = '_billing_email' THEN pm.meta_value END) as 'billing_email',
MAX(CASE WHEN pm.meta_key = '_billing_phone' THEN pm.meta_value END) as 'billing_phone' ,
MAX(CASE WHEN pm.meta_key = '_billing_address_1' THEN pm.meta_value END) as 'billing_address_1',
MAX(CASE WHEN pm.meta_key = '_billing_address_2' THEN pm.meta_value END) as 'billing_address_2',
MAX(CASE WHEN pm.meta_key = '_billing_postcode' THEN pm.meta_value END) as 'billing_postcode',
MAX(CASE WHEN pm.meta_key = '_billing_city' THEN pm.meta_value END) as 'billing_city',
MAX(CASE WHEN pm.meta_key = '_billing_state' THEN pm.meta_value END) as 'billing_state',
MAX(CASE WHEN pm.meta_key = '_billing_country' THEN pm.meta_value END) as 'billing_country',
MAX(CASE WHEN pm.meta_key = '_billing_company' THEN pm.meta_value END) as 'billing_company',
MAX(CASE WHEN pm.meta_key = '_shipping_first_name' THEN pm.meta_value END) as 'shipping_first_name',
MAX(CASE WHEN pm.meta_key = '_shipping_last_name' THEN pm.meta_value END) as 'shipping_last_name',
MAX(CASE WHEN pm.meta_key = '_shipping_address_1' THEN pm.meta_value END) as 'shipping_address_1',
MAX(CASE WHEN pm.meta_key = '_shipping_address_2' THEN pm.meta_value END) as 'shipping_address_2',
MAX(CASE WHEN pm.meta_key = '_shipping_postcode' THEN pm.meta_value END) as 'shipping_postcode',
MAX(CASE WHEN pm.meta_key = '_shipping_city' THEN pm.meta_value END) as 'shipping_city',
MAX(CASE WHEN pm.meta_key = '_shipping_state' THEN pm.meta_value END) as 'shipping_state',
MAX(CASE WHEN pm.meta_key = '_shipping_country' THEN pm.meta_value END) as 'shipping_country',
MAX(CASE WHEN pm.meta_key = '_shipping_company' THEN pm.meta_value END) as 'shipping_company'
FROM
wp_postmeta pm INNER JOIN wp_posts p ON p.ID = pm.post_id AND p.post_type = 'shop_order'
INNER JOIN wp_woocommerce_order_items oi on oi.order_id = pm.post_id
#INNER JOIN wp_woocommerce_order_items oi on oi.order_id = pm.post_id AND oi.order_item_type = 'shipping'
GROUP BY
pm.post_id
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment