MG Report Quries

gistfile1.txt

Order Today
====================================
SELECT
	wo.shipping_first_name as 'First Name',
	wo.shipping_last_name as 'Last Name',
	wo.shipping_city as 'City',
	wo.shipping_state as 'State',
	wo.total as 'Total'
FROM wp_woocommerce_orders wo
INNER JOIN wp_posts p on wo.order_id = p.ID
WHERE DATE(p.post_date) = CURDATE()
AND p.post_status IN ('wc-processing', 'wc-awaiting-shipment', 'wc-completed')

Low Stock
====================================
SELECT p.ID as 'ID', p.post_title as 'Name', pm.meta_value as 'SKU', pm2.meta_value as 'Stock'
FROM wp_posts p
	INNER JOIN wp_postmeta pm
		ON p.ID = pm.post_id
	INNER JOIN wp_postmeta pm2
		ON p.ID = pm2.post_id
	AND pm.meta_key = '_sku'
	AND ((pm2.meta_key = '_stock' AND CAST(pm2.meta_value AS SIGNED) BETWEEN '1' AND '4' ))

Out of Stock
====================================
SELECT p.post_title as 'Name', pm.meta_value as 'SKU', pm2.meta_value as 'Stock'
FROM wp_posts p
	INNER JOIN wp_postmeta pm
		ON p.ID = pm.post_id
	INNER JOIN wp_postmeta pm2
		ON p.ID = pm2.post_id
	AND pm.meta_key = '_sku'
	AND ((pm2.meta_key = '_stock' AND pm2.meta_value = '0' ))

Top Markets
====================================
SELECT
	any_value(shipping_state) as 'State',
	any_value( shipping_city) as 'City',
	shipping_postcode as 'Postal Code',
	COUNT(order_id) as 'Orders',
	ROUND(SUM(total), 2) as 'Subtotal'
FROM wp_woocommerce_orders
GROUP BY shipping_postcode
ORDER BY Subtotal
DESC
LIMIT 100