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.
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:
SELECT ID, post_author, post_date, post_status, post_type FROM wp_posts
WHERE post_type = 'shop_order'
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:
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:
SELECT * FROM wp_postmeta WHERE post_id = 3313
which will give us something like this (not all of the fields are displayed on this screenshot):
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
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:
SELECT * FROM wp_postmeta
WHERE post_id = 3313
AND meta_key IN ('_billing_first_name', '_billing_last_name', '_billing_email', '_billing_phone', '_billing_city')
which will give us the following result:
To summarize, the following graphic illustrates how the customer data is stored in the orders:
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:
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:
SELECT ID, user_email, user_registered
FROM wp_users WHERE ID = 11
which gives the following result:
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:
SELECT * FROM wp_usermeta
WHERE user_id = 11
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:
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:
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:
- 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.
- Join the wp_postmeta to load the _billing_city as a separate column in the results
- 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
- In the WHERE clause and a condition to filter the orders by billing city
- 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:
SELECT MAX(orders.post_date) AS 'last order date', orders.post_status AS 'status',
cities.meta_value AS 'city', emails.meta_value AS 'email'
FROM wp_posts AS orders
INNER JOIN wp_postmeta AS cities ON orders.ID = cities.post_id
AND cities.meta_key = '_billing_city'
LEFT JOIN wp_postmeta AS emails ON orders.ID = emails.post_id
AND emails.meta_key = '_billing_email'
WHERE orders.post_type='shop_order' AND orders.post_status = 'wc-completed'
AND cities.meta_value = 'Chicago'
GROUP BY emails.meta_value
which will produce the following result:
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:
- Our base table will be the wp_users table. We’ll load user ID and email address from this table
- Join the wp_postmeta table on the _customer_user field, which contains the ID of the user
- Join the wp_posts table so we can make sure that we are loading only records for the completed shop orders
- Join on the wp_usermeta table on to load the billing_city as a separate column in the result
- Filter the results by the city column
- Group the results by user ID to show one record per customer
Here is an example of one way to build this database query:
SELECT users.ID, users.user_email, MAX(orders.post_date) AS 'last order date', orders.post_status AS 'status',
cities.meta_value AS 'city'
FROM wp_users AS users
INNER JOIN wp_postmeta AS customer_ids ON users.ID = customer_ids.meta_value
AND customer_ids.meta_key = '_customer_user'
INNER JOIN wp_posts AS orders ON customer_ids.post_id = orders.ID
AND orders.post_type='shop_order' AND orders.post_status = 'wc-completed'
INNER JOIN wp_usermeta AS cities ON users.ID = cities.user_id
AND cities.meta_key = 'billing_city'
WHERE cities.meta_value = 'Chicago'
GROUP BY users.ID
And the result would be similar to the one from the previous example:
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:
You could even combine this search with other filters, for example with the last order date filter:
To learn more about all the available WooCommerce fields and filters, head over to our WooCommerce module page.