Home

/

Blog

/

10 WooCommerce SQL Queries Every Store Owner Needs

Content

10 WooCommerce SQL Queries Every Store Owner Needs

10 WooCommerce SQL Queries Every Store Owner Needs

10 WooCommerce SQL Queries Every Store Owner Needs

Running a WooCommerce store means dealing with data. Orders, customers, products, coupons, refunds. Your WordPress dashboard gives you the basics, but when you need specific answers, the built-in reports fall short.

These 10 queries give you direct access to the insights that actually matter for your business.

Your WooCommerce Database Tables

Table

Purpose

wp_wc_orders

Order records (WC 8.0+)

wp_wc_order_stats

Order statistics and totals

wp_wc_order_product_lookup

Products within each order

wp_wc_customer_lookup

Customer data

wp_posts + wp_postmeta

Legacy order storage

Query 1: Total Revenue This Month

Ask: "What's my total revenue this month?"

SELECT 
    SUM(total_sales) as revenue,
    COUNT(*) as order_count,
    AVG(total_sales) as avg_order_value
FROM wp_wc_order_stats
WHERE status IN (wc-completed, wc-processing)
  AND date_created >= DATE_FORMAT(NOW(), %Y-%m-01);

Query 2: Top 10 Best-Selling Products

SELECT 
    p.post_title as product_name,
    SUM(ol.product_qty) as units_sold,
    SUM(ol.product_net_revenue) as revenue
FROM wp_wc_order_product_lookup ol
JOIN wp_posts p ON ol.product_id = p.ID
JOIN wp_wc_order_stats os ON ol.order_id = os.order_id
WHERE os.status IN (wc-completed, wc-processing)
  AND os.date_created >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY ol.product_id
ORDER BY units_sold DESC
LIMIT 10;

Query 3: Customer Lifetime Value (Top Customers)

SELECT 
    cl.first_name, cl.last_name, cl.email,
    COUNT(os.order_id) as total_orders,
    SUM(os.total_sales) as lifetime_value,
    MIN(os.date_created) as first_order,
    MAX(os.date_created) as last_order
FROM wp_wc_customer_lookup cl
JOIN wp_wc_order_stats os ON cl.customer_id = os.customer_id
WHERE os.status IN (wc-completed, wc-processing)
GROUP BY cl.customer_id
ORDER BY lifetime_value DESC
LIMIT 20;

Query 4: Abandoned Carts (Pending Orders)

SELECT 
    os.order_id, os.total_sales, os.date_created,
    cl.email, cl.first_name
FROM wp_wc_order_stats os
LEFT JOIN wp_wc_customer_lookup cl ON os.customer_id = cl.customer_id
WHERE os.status = wc-pending
  AND os.date_created >= DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY os.total_sales DESC;

Query 5: Revenue by Day (Last 30 Days)

SELECT 
    DATE(date_created) as order_date,
    COUNT(*) as orders,
    SUM(total_sales) as daily_revenue
FROM wp_wc_order_stats
WHERE status IN (wc-completed, wc-processing)
  AND date_created >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(date_created)
ORDER BY order_date DESC;

Query 6: Which Coupons Actually Work?

SELECT 
    p.post_title as coupon_code,
    COUNT(DISTINCT os.order_id) as times_used,
    SUM(os.total_sales) as total_revenue
FROM wp_posts p
JOIN wp_wc_order_coupon_lookup ocl ON p.ID = ocl.coupon_id
JOIN wp_wc_order_stats os ON ocl.order_id = os.order_id
WHERE p.post_type = shop_coupon
  AND os.status IN (wc-completed, wc-processing)
GROUP BY p.ID
ORDER BY times_used DESC;

Query 7: Products That Never Sold

SELECT p.ID, p.post_title, p.post_date
FROM wp_posts p
LEFT JOIN wp_wc_order_product_lookup ol ON p.ID = ol.product_id
WHERE p.post_type = product
  AND p.post_status = publish
  AND ol.order_id IS NULL
ORDER BY p.post_date ASC;

Query 8: Refund Rate by Product

SELECT 
    p.post_title,
    COUNT(CASE WHEN os.status = wc-completed THEN 1 END) as completed,
    COUNT(CASE WHEN os.status = wc-refunded THEN 1 END) as refunded,
    ROUND(
        COUNT(CASE WHEN os.status = wc-refunded THEN 1 END) * 100.0 / 
        NULLIF(COUNT(*), 0), 1
    ) as refund_rate
FROM wp_wc_order_product_lookup ol
JOIN wp_posts p ON ol.product_id = p.ID
JOIN wp_wc_order_stats os ON ol.order_id = os.order_id
GROUP BY ol.product_id
HAVING COUNT(*) >= 5
ORDER BY refund_rate DESC;

Query 9: Revenue by Country

SELECT 
    cl.country,
    COUNT(DISTINCT cl.customer_id) as customers,
    COUNT(os.order_id) as orders,
    SUM(os.total_sales) as revenue
FROM wp_wc_customer_lookup cl
JOIN wp_wc_order_stats os ON cl.customer_id = os.customer_id
WHERE os.status IN (wc-completed, wc-processing)
GROUP BY cl.country
ORDER BY revenue DESC
LIMIT 15;

Query 10: Month-over-Month Growth

SELECT 
    DATE_FORMAT(date_created, %Y-%m) as month,
    COUNT(*) as orders,
    SUM(total_sales) as revenue,
    AVG(total_sales) as avg_order_value
FROM wp_wc_order_stats
WHERE status IN (wc-completed, wc-processing)
  AND date_created >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
GROUP BY DATE_FORMAT(date_created, %Y-%m)
ORDER BY month DESC;

Stop Writing These Queries by Hand

With AI2SQL, you describe what you want in plain English:

  • "Show me this month's revenue by product category" → done

  • "Find customers who ordered more than 3 times" → done

  • "Compare coupon performance for Black Friday" → done

No need to memorize table names, remember JOIN syntax, or worry about WooCommerce version differences. Try it free at ai2sql.io and unlock the data hiding in your WooCommerce database.

Share this

More Articles

More Articles

More Articles

TOOLS

Build an OpenClaw Agent Team in 15 Minutes (2026)

Build an OpenClaw Agent Team in 15 Minutes (2026)

Feb 5, 2026

TOOLS

OpenClaw AI Assistant: Local 24/7 Automation Guide 2026

OpenClaw AI Assistant: Local 24/7 Automation Guide 2026

Feb 4, 2026

TOOLS

SQL WITH Clause (CTE): Complete Guide with Examples

SQL WITH Clause (CTE): Complete Guide with Examples

Jan 14, 2026

TOOLS

MySQL to PostgreSQL Migration: Complete 2026 Guide with Syntax Conversion

MySQL to PostgreSQL Migration: Complete 2026 Guide with Syntax Conversion

Jan 14, 2026

TOOLS

SQL vs Excel: When Should You Make the Switch? [2026]

SQL vs Excel: When Should You Make the Switch? [2026]

Jan 14, 2026