Creating WooCommerce Analytical report with SQL

Question

I just recently moved to WooCommerce platform and need to build SQL in MySQL to run my own analytical reports. For example, I need report that shows total number of sales per sku, per country in the last 30 days. I explored tables like 'wp_posts', 'wp_postmeta' and found that most fields from products and orders are record-based, not column based which makes the SQL a little more challenging.

Answer 1

Finally built the SQL by myself. Hope it will be useful for other WooCommerce users who don't want to use analytical out-of-the box solutions or need custom reporting.

SELECT 
    sku, count(1)
FROM 
    wp_woocommerce_order_items oi, -- orders table
    wp_posts p, -- woocommerce use posts to keep metadata on the order
    wp_postmeta pm, -- we use this table to filter the completed orders
    wp_postmeta pm1, -- we use this table again for getting the shipping country info
    wp_woocommerce_order_itemmeta oim, -- use this table to get product ids from orders
    (SELECT p.id as product_id, meta_value as sku FROM wp_posts p, wp_postmeta pm  where 
        post_type='product' and post_status='publish' 
        and meta_key='_sku' 
        and p.id=pm.post_id) as sku_table -- building temp sku table from published products that defines relationship between product id and sku
WHERE 
    oim.meta_value=sku_table.product_id AND
    oi.order_id=p.ID AND 
    p.id=pm.post_id AND 
    pm.meta_key='_paid_date' AND  -- here we make sure that the order was completed 
    p.id=pm1.post_id AND 
    order_item_type='line_item' AND -- get only line items but not tax or shipping 
    oi.order_item_id=oim.order_item_id AND
    oim.meta_key='_product_id'  AND
    post_date>'2014-01-01' AND -- limits the report date
    pm1.meta_key='_shipping_country' AND -- get shipping country 
    pm1.meta_value='US' -- limits the country
GROUP BY sku
ORDER BY sku;

Answer 2

Correct me if I'm wrong but I think you missed that each line item also has a quantity in wp_woocommerce_order_itemmeta. So your query is just counting how many orders there are for each SKU and not how many of them were sold.

Answer 3

Finally built the SQL by myself. Hope it will be useful for other WooCommerce users who don't want to use analytical out-of-the box solutions or need custom reporting.

SELECT 
    sku, count(1)
FROM 
    wp_woocommerce_order_items oi, -- orders table
    wp_posts p, -- woocommerce use posts to keep metadata on the order
    wp_postmeta pm, -- we use this table to filter the completed orders
    wp_postmeta pm1, -- we use this table again for getting the shipping country info
    wp_woocommerce_order_itemmeta oim, -- use this table to get product ids from orders
    (SELECT p.id as product_id, meta_value as sku FROM wp_posts p, wp_postmeta pm  where 
        post_type='product' and post_status='publish' 
        and meta_key='_sku' 
        and p.id=pm.post_id) as sku_table -- building temp sku table from published products that defines relationship between product id and sku
WHERE 
    oim.meta_value=sku_table.product_id AND
    oi.order_id=p.ID AND 
    p.id=pm.post_id AND 
    pm.meta_key='_paid_date' AND  -- here we make sure that the order was completed 
    p.id=pm1.post_id AND 
    order_item_type='line_item' AND -- get only line items but not tax or shipping 
    oi.order_item_id=oim.order_item_id AND
    oim.meta_key='_product_id'  AND
    post_date>'2014-01-01' AND -- limits the report date
    pm1.meta_key='_shipping_country' AND -- get shipping country 
    pm1.meta_value='US' -- limits the country
GROUP BY sku
ORDER BY sku;