June 24, 2022March 17, 2023 by Users Insights Team

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.

A database is a collection of information that has been organized in such a way allowing for data to be added, retrieved or modified (via database queries) as necessary by an overall management system.

WooCommerce Database Structure

Before we start, it is important to highlight that the way WooCommerce stores its WooCommerce data is not centralized. Rather this information can be stored among different database tables. Since WooCommerce is a plugin that works within the WordPress eco-system it works with the already existing WordPress database as well as it is introducing its own database tables. (So if you ever need to backup WooCommerce database you will need to 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 will be executed by Action Scheduler.
`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 carts 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 which are associated with orders.
`woocommerce_order_itemmeta`	Stores meta data about order line items.
`woocommerce_tax_rates`	Contains tax rates you define in the admin area.
`woocommerce_tax_rate_locations`	Stores locations data associated with the tax rates.
`woocommerce_shipping_zones`	Stores shipping zones you created in the settings area.
`woocommerce_shipping_zone_locations`	Stores locations associated with the shipping zones.
`woocommerce_shipping_zone_methods`	Shipping methods 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 setup 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 products 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 products data, you can also find some WooCommerce data in some other WordPress database tables like: comments (order_note), postmeta (shop_order) and posts (shop_order ID).
These database tables are not only used by your WooCommerce store but also by WooCommerce extensions and WordPress plugin like WooCommerce subscription, WooCommerce membership etc.

WooCommerce customer data

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.

Unlock the Full Potential of Your WooCommerce Customer Data

Find out more

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:

1 2	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 mysql database query:

1	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
_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:

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:

1 2	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:

1 2	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.

in Tutorials