ClickCartPro: Extracting Sales Figures

December 31st, 2008 by Richy B. Leave a reply »

If you want to get an idea of the stock you’ve sold using the ClickCartPro UK e-commerce software, then you may find the following SQL query useful. Run in phpMySQL to be able to export the data into a spreadsheet.

SELECT SUM(itemquan) as itemssold, count(*) AS timesordered, itemname, itemnum, itemopts, SUM(itemquan)/COUNT(*) AS average FROM `gbu0_orderitems` WHERE FROM_UNIXTIME(epochorder) BETWEEN ‘2007-01-01’ and ‘2008-12-31’ GROUP BY itemnum,itemopts ORDER BY itemssold DESC

What does this mean?
Well, “SUM(itemquan) AS itemssold” grabs the number of items sold, “count(*) AS timesordered” grabs the number of times that item has actually been ordered (as an order could be for 2 or more items), “itemname, itemnum, itemopts” grabs the items name, item number and the options ordered (handy for sizes), “SUM(itemquan)/COUNT(*) AS average” works out the average number of items per order, “FROM `gbu0_orderitems`” says to look at the order table (ok, I’m not distinguishing between cancelled, pending and completed orders at the moment), “FROM_UNIXTIME(epochorder) BETWEEN ‘2007-01-01’ and ‘2008-12-31′” says to only include orders placed between the 1st of January 2007 and the 31st of December 2008 and “GROUP BY itemnum,itemopts ORDER BY itemssold DESC” means group the items for the counts by the item number and options and then order everything by the number of times sold in decending order.

This post is over 6 months old.

This means that, despite my best intentions, it may no longer be accurate.

This blog holds over 12 years of archived content - during that time, I may have changed my opinion of something, technology will have advanced (and old "best standards" may no longer be the case), my technology "know how" has improved etc etc - it would probably take me a considerable amount of time to update all the archival entries: and defeat the point of keeping them anyway.

Please take these posts for what they are: a brief look into my past, my history, my journey and "caveat emptor".

Comments are closed.

%d bloggers like this: