How is the WooCommerce customer data stored in the database

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

A database is a collection of information organized to allow data to be added, retrieved, or modified (via database queries) as necessary by an overall management system.

WooCommerce Database

The WooCommerce database is a structured collection of data that stores and manages data related to an online store powered by WooCommerce. It includes information related to products, orders, customers, and other essential e-commerce elements, ensuring efficient organization and retrieval of data to support the functioning of WooCommerce stores. This database serves as the backbone, providing seamless retrieval and storage of data for website functionality and customization.

WooCommerce Database Structure

Before we start, it is essential to highlight how WooCommerce stores its WooCommerce data, which is not centralized. Instead, this information can be stored among different database tables.

WooCommerce database schema

The WooCommerce database schema outlines the structured data organization, including tables, relationships, and fields within the WordPress database. It serves as a blueprint for storing and accessing essential information like product details, orders, and customer data, ensuring the smooth operation of the e-commerce functionalities.

Since WooCommerce is a plugin that works within the WordPress ecosystem, it works with the existing WordPress database and is introducing its database tables. (So if you ever need to backup the WooCommerce database, you must copy the data from both places).

Here is also an overview of some of the custom tables that the WooCommerce plugin introduces:

Table Name	Description
actionscheduler_actions	Information on actions that Action Scheduler will execute.
actionscheduler_claims	Used to perform tasks in the background
actionscheduler_groups	Action Scheduler groups
actionscheduler_logs	Logs data about Action Scheduler executions.
woocommerce_sessions	Stores customer session data, such as cart content data.
woocommerce_api_keys	API Keys used for the REST API.
woocommerce_attribute_taxonomies	Stores global attribute taxonomy names for products.
woocommerce_downloadable_product_permissions	Product access permissions for downloadable products.
woocommerce_order_items	Stores line items that are associated with orders.
woocommerce_order_itemmeta	Stores metadata about order line items.
woocommerce_tax_rates	It contains the tax rates you define in the admin area.
woocommerce_tax_rate_locations	Store location data associated with the tax rates.
woocommerce_shipping_zones	You created the store’s shipping zones in the settings area.
woocommerce_shipping_zone_locations	Store locations associated with the shipping zones.
woocommerce_shipping_zone_methods	Shipping methods are linked to your shipping zones.
woocommerce_payment_tokens	Customer payment tokens (used by gateways).
woocommerce_payment_tokenmeta	Stores meta data about payment tokens.
woocommerce_log	General logging table (alternative to file-based logging).
wc_webhooks	Stores any webhooks that have been set up on the store.
wc_download_log	Logs user downloads of downloadable products.
wc_product_meta_lookup	Lookup table that indexes order meta data to speed up requests.
wc_tax_rate_classes	Tax classes data.
wc_reserved_stock	Stores reserved stock data.

For example, the WooCommerce product data is stored in the woocommerce_order_items and woocommerce_order_itemmeta tables. But this is not the only place where you can find WooCommerce product data; you can also find some WooCommerce data in some other WordPress database tables like comments (order_note), post meta (shop_order), and posts (shop_order ID).
Your WooCommerce store and WooCommerce extensions and WordPress plugins like WooCommerce subscription, WooCommerce membership, etc, use these database tables.

WooCommerce customer data

WooCommerce uses a combination of both WordPress database tables and custom tables to store its data. However, WooCommerce doesn’t have a dedicated table to keep 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 registered WordPress users. As you may know, some standard WordPress user fields include general information, such as email and date reported. WooCommerce also stores the preferred billing/shipping information, which can be used to pre-fill the checkout fields for future orders.

While this distributed storage approach can make data retrieval complex, the Users Insights plugin offers a streamlined solution. With its advanced filters, user profiles, and export features, Users Insights makes it easier to search, analyze, and organize customer data. This includes retrieving details like billing/shipping addresses, order histories, and other user data, all within a single, unified dashboard inside WordPress.

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, 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. 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 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 simplicity, we have only loaded the most critical order fields stored in the posts table, such as the order ID and its status. And this is what 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 an order with ID 3313, we could use the following MySQL 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 in 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

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, a WordPress user will be 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. WooCommerce saves the order billing/shipping information as user meta. In this way, this information will be available for 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 a guest customer makes an order, 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 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:

However, most of the customer information is 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 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 being that an underscore does not prepend them. 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 to 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 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 how 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 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 we search only meta records for shop orders (and not for another post type that might use the same meta key). We’ll also filter the orders to show only the completed ones, as we don’t want to search for failed or canceled 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 result column. In this way, we can see the customer’s email for each record.
4. In the WHERE clause a condition to filter the orders by billing city
5. Group the records by email address to 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:

1. Our base table will be the wp_users table. We’ll load the 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:

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

The result would be similar to the one from the previous model:

3. Searching the WooCommerce user data with Users Insights

As you can see from the examples above, even the most basic WooCommerce database search requires building quite a complex query. This is where Users Insights and its WooCommerce module can come in handy. Advanced filters allow you to 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 placed an order filter:

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