Magento country wise annual sales report directly from the database
There are many extensions out there that you can use to get country-wise sales reports. However, you can retrieve many more things if you belittle familiar with SQL queries. You can use the below queries to get annual sales figures based on countries.
Detailed Report
SELECT orders.increment_id as id, orders.state, addresses.firstname, addresses.lastname, addresses.country_id, orders.subtotal as subtotal, orders.created_at
FROM sales_flat_order AS orders
JOIN sales_flat_order_address AS addresses
ON addresses.parent_id=orders.entity_id
WHERE relation_child_real_id IS NULL
AND addresses.address_type='shipping'
AND YEAR(orders.created_at) = YEAR(CURDATE())
Summary Report
SELECT addresses.country_id, sum(orders.subtotal) as subtotal
FROM sales_flat_order AS orders
JOIN sales_flat_order_address AS addresses
ON addresses.parent_id=orders.entity_id
WHERE relation_child_real_id IS NULL
AND addresses.address_type='shipping'
AND YEAR(orders.created_at) = YEAR(CURDATE())
GROUP BY addresses.country_id
*Please note that the order values displayed are the subtotals before taxes.
Don't forget to leave a comment if this was helpful 😊
Comments