How to Query WooCommerce Purchase Data using SQL in WordPress

It’s not too difficult to retrieve WooCommerce order data from the WordPress database. Today, I want to share with you my experience in solving this problem. Since I’m a front-end developer, I’m not very familiar with queries yet. I hope you understand my situation. If you have any better approaches, please feel free to leave a comment.

It was a cloudy afternoon when I was asked to tackle this query task. It was a simple task.

“I want to identify the users who made more than 50 purchases between 2020 and 2023.”

— Book Publishing Manager

So, I began to build my query.

Design the structure of the query

I have divided my query into four parts:

1. OrderInfo: This part gathers order information from January 1, 2020, to December 31, 2023.
2. CustomerPurchase: This section fetches product information based on the data in the ‘OrderInfo’ table.
3. CustomerPurchaseCount: This portion calculates the number of purchases made by each customer.
4. The final step is to output the email addresses along with the respective number of purchases made by each customer.”

From here on, I will explain each one step-by-step. I will use footnotes to explain the code.

OrderInfo

/* First, we create a temporary table named 'OrderInfo' 
** to gather order information from January 1, 2020, 
** to December 31, 2023. 
*/    
WITH OrderInfo AS (
    SELECT
        posts.ID AS order_id,  -- Order ID
        posts.post_date AS order_date,  -- Order Date
        postmeta.meta_value AS customer_id  -- Customer ID
    FROM
        wp_posts AS posts  -- Fetch information from the wp_posts table
    JOIN
        wp_postmeta AS postmeta ON posts.ID = postmeta.post_id  -- Join with wp_postmeta table to get additional information
    WHERE
        posts.post_type = 'shop_order'  -- Filter only the shop order information
        AND posts.post_date BETWEEN '2020-01-01' AND '2023-12-31'  -- Get data within the specified date range
        AND postmeta.meta_key = '_customer_user'  -- Specify the key to fetch customer information
),
CustomerPurchase AS (
  ...
),
CustomerPurchaseCount AS (
  ...
)
...

CustomerPurchase

WITH OrderInfo AS (
  ...
),
/* We then create another temporary table named 'CustomerPurchase'
** to fetch product information based on the data
** in the 'OrderInfo' table. 
*/
CustomerPurchase AS (
    SELECT
        order_item_meta_product.meta_value AS product_id,  -- Product ID
        OrderInfo.customer_id,  -- Customer ID
        OrderInfo.order_date  -- Order Date
    FROM
        wp_woocommerce_order_items AS order_items  -- Fetch information from the order items table
    JOIN
        wp_woocommerce_order_itemmeta AS order_item_meta_product ON order_items.order_item_id = order_item_meta_product.order_item_id  -- Join with the product metadata table
        AND order_item_meta_product.meta_key = '_product_id'  -- Specify the key to fetch product ID
    JOIN
        OrderInfo ON OrderInfo.order_id = order_items.order_id  -- Join with 'OrderInfo' table to get order details
),
CustomerPurchaseCount AS (
  ...
)
...

CustomerPurchaseCount

WITH OrderInfo AS (
  ...
),
CustomerPurchase AS (
  ...
),
-- Next, we calculate the number of purchases made by each customer.
CustomerPurchaseCount AS (
    SELECT
        customer_id,  -- Customer ID
        COUNT(*) AS purchase_count  -- Calculate the number of purchases for each customer
    FROM
        CustomerPurchase  -- Fetch information from the 'CustomerPurchase' table
    GROUP BY
        customer_id  -- Group data by customer ID
    HAVING
        COUNT(*) >= 50  -- Select only customers who have made more than 50 purchases
)
...

Set the Final output

WITH OrderInfo AS (
  ...
),
CustomerPurchase AS (
  ...
),
CustomerPurchaseCount AS (
    ...
)
-- Finally, we output the email addresses and the number of purchases.
SELECT
    users.user_email,  -- Customer Email Address
    CustomerPurchaseCount.purchase_count  -- Number of purchases made by the customer
FROM
    CustomerPurchaseCount  -- Fetch information from the 'CustomerPurchaseCount' table
JOIN
    wp_users AS users ON CustomerPurchaseCount.customer_id = users.ID;  -- Join with wp_users table to get email information

Result

After executing all of these queries, we can obtain the information we need. Not too difficult, right?

result example

Full Query

-- First, we create a temporary table named 'OrderInfo' to gather order information from January 1, 2020, to December 31, 2023.
WITH OrderInfo AS (
    SELECT
        posts.ID AS order_id,  -- Order ID
        posts.post_date AS order_date,  -- Order Date
        postmeta.meta_value AS customer_id  -- Customer ID
    FROM
        wp_posts AS posts  -- Fetch information from the wp_posts table
    JOIN
        wp_postmeta AS postmeta ON posts.ID = postmeta.post_id  -- Join with wp_postmeta table to get additional information
    WHERE
        posts.post_type = 'shop_order'  -- Filter only the shop order information
        AND posts.post_date BETWEEN '2020-01-01' AND '2023-12-31'  -- Get data within the specified date range
        AND postmeta.meta_key = '_customer_user'  -- Specify the key to fetch customer information
),
-- We then create another temporary table named 'CustomerPurchase' to fetch product information based on the data in the 'OrderInfo' table.
CustomerPurchase AS (
    SELECT
        order_item_meta_product.meta_value AS product_id,  -- Product ID
        OrderInfo.customer_id,  -- Customer ID
        OrderInfo.order_date  -- Order Date
    FROM
        wp_woocommerce_order_items AS order_items  -- Fetch information from the order items table
    JOIN
        wp_woocommerce_order_itemmeta AS order_item_meta_product ON order_items.order_item_id = order_item_meta_product.order_item_id  -- Join with the product metadata table
        AND order_item_meta_product.meta_key = '_product_id'  -- Specify the key to fetch product ID
    JOIN
        OrderInfo ON OrderInfo.order_id = order_items.order_id  -- Join with 'OrderInfo' table to get order details
),
-- Next, we calculate the number of purchases made by each customer.
CustomerPurchaseCount AS (
    SELECT
        customer_id,  -- Customer ID
        COUNT(*) AS purchase_count  -- Calculate the number of purchases for each customer
    FROM
        CustomerPurchase  -- Fetch information from the 'CustomerPurchase' table
    GROUP BY
        customer_id  -- Group data by customer ID
    HAVING
        COUNT(*) >= 50  -- Select only customers who have made more than 50 purchases
)
-- Finally, we output the email addresses and the number of purchases.
SELECT
    users.user_email,  -- Customer Email Address
    CustomerPurchaseCount.purchase_count  -- Number of purchases made by the customer
FROM
    CustomerPurchaseCount  -- Fetch information from the 'CustomerPurchaseCount' table
JOIN
    wp_users AS users ON CustomerPurchaseCount.customer_id = users.ID;  -- Join with wp_users table to get email information