WooCommerce is a shopping cart plugin for WordPress. It comes with very little base functionality, and you're expected to buy plugins to extend its feature set. But, something as simple as exporting your customer data - so you can add or remove people from a mailing lists - shouldn't be such a mystery.

Wordpress and the EAV Layout

One of the reasons why Wordpress (and by extension WooCommerce) has been so successfully is because of the sheer number of plugins available for both. With so many plugin installations, it's a wonder the database doesn't get corrupted. But, because WP uses EAV style database design, very few - if any - ALTER TABLE statements are needed to store more data about a post.

Wordpress stores information about a post in the wp_postmeta table. This table has columns meta_key and meta_value. An interesting query to start inspecting your own WP or WC installation is to review all the unique meta keys stored in the wp_postmeta table. 

SELECT DISTINCT meta_value FROM wp_postmeta;

WooCommerce and Orders

WooCommerce stores order information in the same table as blog posts. I'm not sure if this is encouraged by Wordpress or not. It's certainly possible because of the post_type field. This technique might be essentially required in order to leverage some of the WP backend admin functionality for editing orders.

Incidentally, the EAV storage style is incredibly innefficient when you have WooCommerce storing many thousands of orders, each with multiple dozens of attributes. Storing many small pieces of information as individual rows makes importing backups of WC extremely slow. You can also see how EAV fails WooCommerce because they had to add their own tables for storing order items: wp_woocommerce_order_items, wp_woocommerce_order_itemmeta.

You can find all the custom post types in your installation by executing this query: 

SELECT DISTINCT post_type FROM wp_posts;

From this, we can see that WooCommerce stores orders with the post type shop_order. To get the most basic information about orders we can execute this query: 

SELECT * FROM wp_posts WHERE post_type = 'shop_order' LIMIT 3;

But, this only reveals information like the date and time of the order and the database ID. To get anything usefull we have to join against wp_postmeta.

Exporting Customer Information

I'm realizing how long this blog post would be if I tried to explain everything step by step. So, I'm just going to give you the SQL to export customer information and you can play with it from there. 

SELECT
  A.ID as order_id
, B.meta_value as b_first_name
, C.meta_value as b_last_name
, D.meta_value as b_address_1
, E.meta_value as b_address_2
, F.meta_value as b_country
, G.meta_value as b_state
, H.meta_value as b_city
, I.meta_value as b_postcode
, J.meta_value as b_user_id
, K.user_email as b_email

FROM wp_posts as A
LEFT JOIN wp_postmeta B 
  ON A.id = B.post_id AND B.meta_key = '_billing_first_name'

LEFT JOIN wp_postmeta C
  ON A.id = C.post_id AND C.meta_key = '_billing_last_name'

LEFT JOIN wp_postmeta D
  ON A.id = D.post_id AND D.meta_key = '_billing_address_1'

LEFT JOIN wp_postmeta E
  ON A.id = E.post_id AND E.meta_key = '_billing_address_2'

LEFT JOIN wp_postmeta F
  ON A.id = F.post_id AND F.meta_key = '_billing_country'

LEFT JOIN wp_postmeta G
  ON A.id = G.post_id AND G.meta_key = '_billing_state'

LEFT JOIN wp_postmeta H
  ON A.id = H.post_id AND H.meta_key = '_billing_city'

LEFT JOIN wp_postmeta I
  ON A.id = I.post_id AND I.meta_key = '_billing_postcode'

LEFT JOIN wp_postmeta J
  ON A.id = J.post_id AND J.meta_key = '_customer_user'

LEFT JOIN wp_users K
  ON J.meta_value = K.ID

WHERE A.post_type = 'shop_order'
AND   A.post_status = 'wc-completed';

Want only orders within the last day? Add: 

AND   A.post_date_gmt >= DATE_SUB(NOW(), INTERVAL 1 DAY);

Want to remove duplicate orders and only see unique user rows? Group by wp_users.ID 

GROUP BY K.ID

As you can see, we must join the same table (wp_postmeta) multiple times, each time giving it a different alais and finding a different key. This is like taking a very long and thin result set and turning it into a wide result set.

WooCommerce Opt-in Mailing List

The usual purpose of exporting customer data is to import the data into a mailing list. We still don't have a specific opt-in, we could assume opt-in and let people opt-out.

This gist could give you a starting point for recording your own opt-in checkbox on the checkout page. This can either be added to your theme's functions.php file, or developed into a stand alone plugin.

Can you see how you would export a new field saved with update_post_meta($order_id, 'key', 'value') ?

Logo By WooThemes - http://www.woothemes.com/style-guide/, CC BY 3.0, https://commons.wikimedia.org/w/index.php?curid=39823668