Where is WooCommerce order shipping methods stored

Question

I need to create statistic about shipping methods used. I know that there a method get_shipping_methods() but it takes a very long time to use it with tens of thousands of orders. I would like to create a SQL query to get this faster, but I can't find how it is stored in the latest HPOS WC tables. I need to get monthly counts of orders per shipping method. I would appreciate any hint.

Answer 1

Here's a sample SQL query that demonstrates this approach:

SELECT
    MONTH(p.post_date) AS month,
    pm.meta_value AS shipping_method,
    COUNT(*) AS order_count
FROM
    wp_posts p
JOIN
    wp_postmeta pm ON p.ID = pm.post_id
JOIN
    wp_woocommerce_order_items oi ON p.ID = oi.order_id
WHERE
    p.post_type = 'shop_order'
    AND pm.meta_key = '_shipping_method'
    AND p.post_date >= '2021-01-01'
    AND p.post_date < '2022-01-01'
GROUP BY
    month,
    shipping_method

Replace wp_ with the appropriate table prefix used in your WooCommerce setup. Adjust the date range in the WHERE clause (>= '2021-01-01' and < '2022-01-01') to match your desired timeframe.