0

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.

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.