How is the WooCommerce customer data stored in the database

In this article we are going to show you how WooCommerce stores the customer data in the database. We’ll go through the main WooCommerce database tables that are used in WordPress to store any customer related information. This includes data, such as name, order history and billing address.

WooCommerce uses a combination of both WordPress database tables and its own custom tables to store its data. However, WooCommerce doesn’t have a dedicated table to store the customer data. The customer data is stored in different database tables, which sometimes might make retrieval of this data challenging.

Additionally, there are two different ways to retrieve customer data based on whether the customer is a guest user or a registered user:

  • Order based customer data – this is the customer data that is stored for each order, it is available for both guest customers and registered users
  • WordPress user data – this data is stored only for the registered WordPress users. As you may know, some of the standard WordPress user fields include the general information, such as email and date registered. WooCommerce also stores the preferred billing/shipping information, which can be used to pre-fill the checkout fields for future orders.

woocommerce customer database

Order based WooCommerce customer data

WooCommerce by default stores the customer data as part of each order. Regardless of whether the customer is a registered user or a guest customer, there will be always a record of the customer data attached to the order.

This means that if one customer makes multiple orders without changing their personal details, there will be a copy of their customer details for each order. While this can create lots of duplicated data, it also allows for providing different personal details (such as billing address or contact number) for each order separately.

Therefore, in order to find the customer details for a particular order, we first need to look at the order and make the link between the order and the customer data. WooCommerce uses a custom post type “shop_order” to store orders. This means that the orders will be stored in the wp_posts  database table with the post_type column set to shop_order. So, we need to use the following query in order to load the WooCommerce orders from the database:

For the sake of simplicity we have only loaded the most important order fields that are stored posts table, such as the order ID and its status. And this is how the results look like:

WooCommerce load orders from database

 

The order ID is the key to finding the customer data linked to the order. WooCommerce stores a large portion of the order related data as post meta. Therefore, once we have the order ID, we can find the customer data by searching the  wp_postmeta table by the order ID.

For example, if we wanted to find the customer data for order with ID 3313, we could use the following database query:

which will give us something like this (not all of the fields are displayed on this screenshot):

WooCommerce customer database table

 

As you can see from this example, each customer field is stored as post meta for the order where the  meta_key  is the name of the field and  meta_value is the actual value of the customer field. Some of the main customer related data that is stored is the billing and shipping information, such as:

  • _billing_first_name / _shipping_first_name
  • _billing_last_name / _shipping_last_name
  • _billing_company / _shipping_company
  • _billing_address_1 / _shipping_address_1
  • _billing_address_2 / _shipping_address_2
  • _billing_city / _shipping_city
  • _billing_state / _shipping_state
  • _billing_postcode / _shipping_postcode
  • _billing_country / _shipping_country
  • _billing_email
  • _billing_phone
  • _customer_user

And if, for example, you wanted to find the general contact details of a customer for a selected order, you could write a query like this:

which will give us the following result:

WooCommerce load personal customer data from database

To summarize, the following graphic illustrates how the customer data is stored in the orders:

WooCommerce order based customer data

WordPress user data

If you have the “Guest checkout” option disabled in WooCommerce, there will be a WordPress user created for each customer. In this case, WooCommerce will use the WordPress user tables to store the customer information:

  • wp_users  – this table will contain the general user information, such as email address and date registered
  • wp_usermeta – preferred billing/shipping information. Basically, WooCommerce saves the order billing/shipping information as user meta. In this way, this information will be available for the future orders and a faster checkout process.

In the previous example you might have noticed that the order meta included a field with a key  _customer_user:

WooCommerce order data linked to user ID

This is how WooCommerce links orders to users. When an order is made by a guest customer, this field’s value would be set to 0. Otherwise, it would contain the ID of the WordPress user, which in this case is 11.

Now, let’s see what kind of data we have stored for the user. As we mentioned earlier, the  wp_users table stores the general WordPress user data. It can be loaded with the following query:

which gives the following result:

Query WooCommerce personal data

Most of the customer information is however stored in the  wp_usermeta table. If we query the user meta table to show us all the results for the selected user:

we’ll find that many of the fields that were present for the order in the  wp_postmeta table, are also saved for the user. This includes all of the billing and shipping information:

Load WooCommerce customer data from user meta

As you may notice, the user field keys are the same as the order field keys, with the only difference that they are not prepended by an underscore. For example, the meta key for the customer’s first name in the order will be _billing_first_name  while the user meta key will be  billing_first_name.

The following graphic illustrates how all this customer data is connected in the database:

WooCommerce registered customer database

 

Searching the WooCommerce database

There is no single method when it comes to searching the WooCommerce database. Your database queries will depend on your requirements and the data that you need to load and search. So, some aspects to consider are:

  • does your WooCommerce setup allow guest users or it’s registered users only
  • do you need to link orders to the customer search results
  • which fields are required in the result

Due to the way the data is structured in the database, the more data you need to load about your customers in your search results, the more tables you’ll need to join in the query.

Let’s see some examples.

1. Searching the order based data

As we already discussed, the order based customer data is available for both registered and guest customers. Therefore, we recommend using this search approach when your shop allows guest checkouts.

Let’s say that we want to search the customers by billing city. Our query will need the following elements:

  1. Query the wp_posts  table and load the posts from type shop_order. This will ensure that we will be searching only meta records for shop orders (and not for another post type that might be using the same meta key). We’ll  also filter the orders to show only the completed ones, as we don’t want to search the failed or cancelled orders.
  2. Join the wp_postmeta to load the _billing_city as a separate column in the results
  3. Join the wp_postmeta again to load the _billing_email as a separate column in the results. In this way we can see the customer email for each record
  4. In the WHERE clause and a condition to filter the orders by billing city
  5. Group the records by email address, so that we have one record per customer. If all of your customers are registered users, we recommend grouping the records by the _customer_id meta field instead, as this is a more accurate way to identify your customers.

And this is how the query will look like:

which will produce the following result:

Search WooCommerce database orders by city

 

2. Searching the WooCommerce user data

Now let’s redo the previous example, but this time we’ll search the user meta, instead of the order meta. Although the process might sound similar, the WooCommerce database query will be completely different in this case. Here is what we need to do:

  1. Our base table will be the  wp_users table. We’ll load user ID and email address from this table
  2. Join the  wp_postmeta table on the _customer_user field, which contains the ID of the user
  3. Join the  wp_posts table so we can make sure that we are loading only records for the completed shop orders
  4. Join on the  wp_usermeta table on to load the billing_city as a separate column in the result
  5. Filter the results by the city column
  6. Group the results by user ID to show one record per customer

Here is an example of one way to build this database query:

And the result would be similar to the one from the previous example:

Search WooCommerce user database

3. Searching the WooCommerce user data with Users Insights

As you can see from the examples above, doing even the most basic WooCommerce database search requires building a quite complex query. This is where Users Insights and its WooCommerce module can come in handy. By using the advanced filters you can easily search the registered user data, such as billing/shipping address, number of orders and lifetime value.

With Users Insights, the example above is as simple as loading the billing address in a custom field and adding a filter by city and order status:

WooCommerce search database tool

You could even combine this search with other filters, for example with the last order date filter:

WooCommerce search database tool

To learn more about all the available WooCommerce fields and filters, head over to our WooCommerce module page.