Cs-cart sqls to get statistical reports

I am sharing the sqls which I wrote to generate reports for the year 2013.  You can tweak it to meet your respective needs.
I have only used them for Cs-cart multivendor edition version 3.x, so if it works for other versions please leave a comment confirming that.

Note :

  • Replace +3:00 and -3:00 as per your timezone
  • Modify co.status IN (‘C’) as per the statuses you are interested in, otherwise just delete it.
  • Modify co.company_id NOT IN (1153) as the companies you are not interested in, otherwise just delete it.
  • I think you can remove this co.is_parent_order = ‘N’ if you are not running multivendor, someone please confirm.

Month wise sales figure

select SUBSTRING(CONVERT_TZ(FROM_UNIXTIME(co.timestamp),'+00:00','+3:00'),6,2) as month, sum(co.total) as total
from
cscart_orders as co
WHERE co.timestamp >= UNIX_TIMESTAMP(CONVERT_TZ('2013-01-01 00:00:00','+00:00','-3:00')) AND co.timestamp <= UNIX_TIMESTAMP(CONVERT_TZ('2013-12-31 23:59:59','+00:00','-3:00')) AND co.is_parent_order = 'N' AND co.status IN ('C') AND co.company_id NOT IN (153)
GROUP BY month

Month wise number of orders

select SUBSTRING(CONVERT_TZ(FROM_UNIXTIME(co.timestamp),'+00:00','+3:00'),6,2) as month, count(*) as total
from
cscart_orders as co
WHERE co.timestamp >= UNIX_TIMESTAMP(CONVERT_TZ('2013-01-01 00:00:00','+00:00','-3:00')) AND co.timestamp <= UNIX_TIMESTAMP(CONVERT_TZ('2013-12-31 23:59:59','+00:00','-3:00')) AND co.is_parent_order = 'N' AND co.status IN ('C') AND co.company_id NOT IN (153)
GROUP BY month

Month wise number of order items

select SUBSTRING(CONVERT_TZ(FROM_UNIXTIME(co.timestamp),'+00:00','+3:00'),6,2) as month, sum(cod.amount) as total
from
cscart_order_details as cod
join cscart_orders as co On cod.order_id=co.order_id
WHERE co.timestamp >= UNIX_TIMESTAMP(CONVERT_TZ('2013-01-01 00:00:00','+00:00','-3:00')) AND co.timestamp <= UNIX_TIMESTAMP(CONVERT_TZ('2013-12-31 23:59:59','+00:00','-3:00')) AND co.is_parent_order = 'N' AND co.status IN ('C') AND co.company_id NOT IN (153)
GROUP BY month

Day wise number of orders

select SUBSTRING(CONVERT_TZ(FROM_UNIXTIME(co.timestamp),'+00:00','+3:00'),1,10) as day, count(*) as total
from
cscart_orders as co
WHERE co.timestamp >= UNIX_TIMESTAMP(CONVERT_TZ('2013-01-01 00:00:00','+00:00','-3:00')) AND co.timestamp <= UNIX_TIMESTAMP(CONVERT_TZ('2013-12-31 23:59:59','+00:00','-3:00')) AND co.is_parent_order = 'N' AND co.status IN ('C') AND co.company_id NOT IN (153)
GROUP BY day

Day wise number of order items

select SUBSTRING(CONVERT_TZ(FROM_UNIXTIME(co.timestamp),'+00:00','+3:00'),1,10) as day, sum(cod.amount) as total
from
cscart_order_details as cod left join cscart_product_descriptions as cpd On cod.product_id=cpd.product_id
join cscart_products as cp On cod.product_id=cp.product_id
join cscart_orders as co On cod.order_id=co.order_id
join cscart_users as cu On co.user_id = cu.user_id
join cscart_payment_descriptions as cpyd On co.payment_id = cpyd.payment_id
WHERE co.timestamp >= UNIX_TIMESTAMP(CONVERT_TZ('2013-01-01 00:00:00','+00:00','-3:00')) AND co.timestamp <= UNIX_TIMESTAMP(CONVERT_TZ('2013-12-31 23:59:59','+00:00','-3:00')) AND co.is_parent_order = 'N' AND co.status IN ('C') AND co.company_id NOT IN (153)
GROUP BY day

Order value frequency

select ROUND(co.total) as total2, count(*) as frequency
from
cscart_orders as co
WHERE co.timestamp >= UNIX_TIMESTAMP(CONVERT_TZ('2013-01-01 00:00:00','+00:00','-3:00')) AND co.timestamp <= UNIX_TIMESTAMP(CONVERT_TZ('2013-12-31 23:59:59','+00:00','-3:00')) AND co.is_parent_order = 'N' AND co.status IN ('C') AND co.company_id NOT IN (153)
GROUP BY total2
ORDER BY total2

Order item price frequency

select ROUND(cod.price, -1) as price2,sum(cod.amount) as frequency
from
cscart_order_details as cod
join cscart_orders as co On cod.order_id=co.order_id
WHERE co.timestamp >= UNIX_TIMESTAMP(CONVERT_TZ('2013-01-01 00:00:00','+00:00','-3:00')) AND co.timestamp <= UNIX_TIMESTAMP(CONVERT_TZ('2013-12-31 23:59:59','+00:00','-3:00')) AND co.is_parent_order = 'N' AND
co.status IN ('C') AND co.company_id NOT IN (153)
GROUP BY price2
ORDER BY price2 ASC

Items per order frequency

select frequency, count(*) as frequency_count from (select count(*) as frequency
from
cscart_order_details as cod
join cscart_orders as co On cod.order_id=co.order_id
WHERE co.timestamp >= UNIX_TIMESTAMP(CONVERT_TZ('2013-01-01 00:00:00','+00:00','-3:00')) AND co.timestamp <= UNIX_TIMESTAMP('2013-12-31 23:59:59') AND co.is_parent_order = 'N' AND
co.status IN ('C') AND co.company_id NOT IN (153)
GROUP BY co.order_id
) as derived
GROUP BY frequency
ORDER BY frequency ASC

Orders per customer

select frequency, count(*) as frequency_count from (select count(*) as frequency
from
cscart_orders as co
WHERE co.timestamp >= UNIX_TIMESTAMP(CONVERT_TZ('2013-01-01 00:00:00','+00:00','-3:00')) AND co.timestamp <= UNIX_TIMESTAMP('2013-12-31 23:59:59') AND co.is_parent_order = 'N' AND
co.status IN ('C') AND co.company_id NOT IN (153)
GROUP BY co.user_id
) as derived
GROUP BY frequency
ORDER BY frequency ASC

Number of items ordered per customer

select frequency, count(*) as frequency_count from (select count(*) as frequency
from
cscart_order_details as cod
join cscart_orders as co On cod.order_id=co.order_id
WHERE co.timestamp >= UNIX_TIMESTAMP(CONVERT_TZ('2013-01-01 00:00:00','+00:00','-3:00')) AND co.timestamp <= UNIX_TIMESTAMP(CONVERT_TZ('2013-12-31 23:59:59','+00:00','-3:00')) AND co.is_parent_order = 'N' AND
co.status IN ('C') AND co.company_id NOT IN (153)
GROUP BY co.order_id
ORDER BY frequency ASC) as derived
GROUP BY frequency

Month wise customer registration

select SUBSTRING(CONVERT_TZ(FROM_UNIXTIME(u.timestamp),'+00:00','+0:00'),6,2) as month, count(*) as total
from
cscart_users as u
WHERE u.timestamp >=  UNIX_TIMESTAMP(CONVERT_TZ('2013-01-01 00:00:00','+00:00','-3:00'))  AND u.timestamp <= UNIX_TIMESTAMP(CONVERT_TZ('2013-12-31 23:59:59','+00:00','-3:00')) AND u.user_type = 'C'
GROUP BY month

Day wise customer registration

select SUBSTRING(CONVERT_TZ(FROM_UNIXTIME(u.timestamp),'+00:00','+0:00'),1,10) as day, count(*) as total
from
cscart_users as u
WHERE u.timestamp >= UNIX_TIMESTAMP(CONVERT_TZ('2013-01-01 00:00:00','+00:00','-3:00')) AND u.timestamp <= UNIX_TIMESTAMP(CONVERT_TZ('2013-12-31 23:59:59','+00:00','-3:00')) AND u.user_type = 'C'
GROUP BY day

Top 100 customers by number of orders

select cu.firstname, cu.lastname, count(*) as frequency, cu.user_id
from
cscart_orders as co join cscart_users as cu On co.user_id = cu.user_id
WHERE co.timestamp >= UNIX_TIMESTAMP(CONVERT_TZ('2013-01-01 00:00:00','+00:00','-3:00')) AND co.timestamp <= UNIX_TIMESTAMP('2013-12-31 23:59:59') AND co.is_parent_order = 'N' AND
co.status IN ('C') AND co.company_id NOT IN (153)
GROUP BY co.user_id
ORDER BY frequency DESC
LIMIT 0,100

Top 100 customers by purchase volume

select cu.firstname, cu.lastname, sum(co.total) as total_purchase, cu.user_id
from
cscart_orders as co join cscart_users as cu On co.user_id = cu.user_id
WHERE co.timestamp >= UNIX_TIMESTAMP(CONVERT_TZ('2013-01-01 00:00:00','+00:00','-3:00')) AND co.timestamp <= UNIX_TIMESTAMP('2013-12-31 23:59:59') AND co.is_parent_order = 'N' AND
co.status IN ('C') AND co.company_id NOT IN (153)
GROUP BY co.user_id
ORDER BY total_purchase DESC
LIMIT 0,100

You can export this data to spreadsheets and create beautiful graphs. I have pasted some of mine below.
Orders per customer Items per order Item price Order value frequency

Enjoy !!!

Posted in CS-Cart, MySql Tagged with: , , , ,
2 comments on “Cs-cart sqls to get statistical reports
  1. jeff says:

    Hi Shikhar;

    thanks so much for the nice sql queries.

    can you please give some tips on how to get yearly sales report where the months are displayed horizontally and the orders are vertically. thanks
    ( i thin kits called pivotal )

  2. Shikhar says:

    Hi Jeff,
    First sql does what you are asking for. If that is not what you are looking for then give me an example.

Leave a Reply

Your email address will not be published. Required fields are marked *

*