Determine X-Cart Product Sales Statistics

[Xenia]

This mysql query can be used in phpMyAdmin or as part of a PHP script to evaluate your X-Cart product database and determine the numbers of sales a product has generated along with the first and last purchase dates.

This information can be very useful for determining which products you may want to put more marketing focus on, and those which you may want to shelve altogether.

SELECT p.productid, p.product, COUNT( od.orderid ) AS sales,
FROM_UNIXTIME( MIN( o.date ) , '%Y %M %e' ) AS first_order,
FROM_UNIXTIME( MAX( o.date ) , '%Y %M %e' ) AS last_order
FROM xcart_products AS p
LEFT JOIN xcart_order_details AS od ON p.productid = od.productid
LEFT JOIN xcart_orders AS o ON o.orderid = od.orderid
WHERE p.forsale = 'Y'
GROUP BY od.productid
ORDER BY sales DESC , p.productid DESC