Top customers by spend in WooCommerce
Rank WooCommerce customers by total spend across their paid orders.
WooCommerce · PostgreSQL
SELECT em.meta_value AS email,
COUNT(*) AS orders,
ROUND(SUM(CAST(tot.meta_value AS DECIMAL(12,2))), 2) AS total_spend
FROM wp_posts p
JOIN wp_postmeta em
ON p.ID = em.post_id AND em.meta_key = '_billing_email'
JOIN wp_postmeta tot
ON p.ID = tot.post_id AND tot.meta_key = '_order_total'
WHERE p.post_type = 'shop_order'
AND p.post_status IN ('wc-completed', 'wc-processing')
GROUP BY em.meta_value
ORDER BY total_spend DESC
LIMIT 20;How it works
Joins order billing-email and order-total meta to total revenue per customer email. Works without a separate customers table.
Run this on your own database
BeQuery mirrors your WooCommerce MySQL database to an isolated PostgreSQL copy, so you can run queries like this one without ever touching production.
Start free No credit card