December 31, 2008 – Richy's Random Ramblings

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.

Here’s more a reminder to myself and all figures are based off “base rates” currently provided by the various Payment gateways. Check the figures yourself before using them as they may have changed. I can’t accept any liability for how you use this information.

Paypal charge a fixed fee of £0.20 and 3.4% of each transaction. To figure out the gross figure from the net, use the formula: (net+0.20)/(1-(3.4/100)).

Click and Buy charge a fixed fee of £0.35 and 1.9% of each transaction.

Google Checkout charges a fixed fee of £0.15 and 1.5% of each transaction.

NoChex’s Seller Account charges a fixed fee of £0.20 and 2.9% of each transaction.

Worldpay IMA charges a fixed fee of £0.15 and 3.35% of each transaction (along with a £15 monthly fee and £75 setup fee) [note this is for their current “New Business Deal”, normally they charge £30 per month with a £200 setup fee and £4.50% on credit/charge cards and a flat £0.50 on UK Debit Cards: along with £0.06 per transaction for Fraud Detection]

SecureTrading charges 3.29% on each transaction (subject to a minimum fee of £15 per month with a further £15 per month service fee and a one-off setup fee of £300)

Day: 31 December 2008

ClickCartPro: Extracting Sales Figures

Paypal, ClickAndBuy, Google Checkout, NoChex and Worldpay Fees