Export WooCommerce Customers with Addresses from WordPress for Shopify

Customer export

There’s a simple way to export your customer information from WooCommerce without using a plugin including their shipping address and phone number into a csv. This is particularly handy if you’re looking to export WooCommerce customers to import them into Shopify.

We’ll get this done using a query in phpMyAdmin on the WordPress database. But before we get to the query, make sure you have access to phpMyAdmin. If you’re using cPanel hosting like on SiteGround, you can find it in the databases section.

phpMyAdmin in cPanel

Next make sure of your database names. With phpMyAdmin open click on your database name to expand the names of your tables.

phpMyAdmin WordPress db

Look for your wp_users table and also see if you have something crazy like wp_yoursitename_users or wp_someotherstring_users. If you have both and have every imported or moved your site then it’s likely that you’ll need to update the query below to your longer named users table. Same goes with wp_usermeta table.

Now before you run any query on your DB you’ll want to make a backup. Hopefully your host has this ability but you may also want to do an export of your WordPress DB for good measure.


SELECT
  u.ID,
  u.user_email,
  um1.meta_value AS first_name,
  um2.meta_value AS last_name,
  um3.meta_value AS shipping_first_name,
  um4.meta_value AS shipping_last_name,
  um5.meta_value AS shipping_company,
  um6.meta_value AS shipping_address_1,
  um7.meta_value AS shipping_address_2,
  um8.meta_value AS shipping_city,  
  um9.meta_value AS shipping_state,
  um10.meta_value AS shipping_postcode,
  um11.meta_value AS shipping_country,
  um12.meta_value AS billing_phone
FROM wp_users u
  LEFT JOIN wp_usermeta um1 ON u.ID = um1.user_id AND um1.meta_key = 'first_name'
  LEFT JOIN wp_usermeta um2 ON u.ID = um2.user_id AND um2.meta_key = 'last_name'
  LEFT JOIN wp_usermeta um3 ON u.ID = um3.user_id AND um3.meta_key = 'shipping_first_name'
  LEFT JOIN wp_usermeta um4 ON u.ID = um4.user_id AND um4.meta_key = 'shipping_last_name'
  LEFT JOIN wp_usermeta um5 ON u.ID = um5.user_id AND um5.meta_key = 'shipping_company'
  LEFT JOIN wp_usermeta um6 ON u.ID = um6.user_id AND um6.meta_key = 'shipping_address_1'
  LEFT JOIN wp_usermeta um7 ON u.ID = um7.user_id AND um7.meta_key = 'shipping_address_2'
  LEFT JOIN wp_usermeta um8 ON u.ID = um8.user_id AND um8.meta_key = 'shipping_city'
  LEFT JOIN wp_usermeta um9 ON u.ID = um9.user_id AND um9.meta_key = 'shipping_state'
  LEFT JOIN wp_usermeta um10 ON u.ID = um10.user_id AND um10.meta_key = 'shipping_postcode'
  LEFT JOIN wp_usermeta um11 ON u.ID = um11.user_id AND um11.meta_key = 'shipping_country'
  LEFT JOIN wp_usermeta um12 ON u.ID = um12.user_id AND um12.meta_key = 'billing_phone'
;

To run the query in phpMyAdmin, paste your modified code here:

where to paste your phpMyAdmin query

And then press the “go” button if you’re sure you have a db backup.

You can then review the table, make adjustments to your query if needed and export as a csv file from the bottom of the page in phpMyAdmin.

For Shopify, make sure you adjusted your heading names in the csv in your favorite spreadsheet editor prior to import. You can download the customer import template from Shopify to check them.

Leave a Comment