How to read number of sold products per month per product?

[carsten888]

I keep track of my sales in a spreadsheet, so each month I want to copy paste the number of sales per product.

I used to do this with a hack in the paypals plugin (paypals is my only active payment method), but with the last update, it stopped working.

So I'm checking the admin-product-page (tab 'product status') where it shows how many orders there have been. But thats orders, not actually sold products, so I can't use that.
Then I checked in the database in table 'products' where there is a column 'product_sales'. But that contains numbers which don't make much sense. One product I know I sold 20 of last month is on 7. So one would think it has just started counting with the last update to VM 2.6.10, but then there is another product which is sold thousands of times, and that was not in the last 3 weeks. So I don't know whats up with that table column.

Anyway, how to read how many is sold per product?

[AH]

See my reply #1 from this post

http://forum.virtuemart.net/index.php?topic=125441.msg429565#msg429565


The table you should use is "direct_virtuemart_order_items"

Dont forget to exclude order statuses that are not sold such as Pending or cancelled/refunded

Then build your SQL


You should be able to create yourself some great bespoke reports

I would recommed using heidisql to test your MYSQL statement before setting up in the spreadsheet as it makes it far easier to correct mistakes

You can do direct reporting from excel using ODBC connector and the data import functionality - using pivot tables then really opens up the power of flexible bespoke reporting


It might take a little while to get your head around this, but IMHO it will be worth it!

[carsten888]

thank you. With that I can query the total sold and use that each month to see how many new got sold, write that to another table etc. great.

Would be nice to be able to query by date, but column 'created_on' is not in unix-time, so that won't work. So I will go with the above option.

[AH]

Would be nice to be able to query by date, but column 'created_on' is not in unix-time, so that won't work. So I will go with the above option.

You do not need a new table, the data is there and can be referenced for any period or stuck into a pivot table to allow lots of specific analysis.  So dont go stroing the data separately unless you really have to!

Created on is a DATE_TIME format

http://www.w3schools.com/sql/func_date_format.asp

So you can extract whatever element you wish from it - to get a monthyear combo for example:-

Code Select Expand


select created_on, DATE_FORMAT(created_on,'%y-%m') as yearmonth from direct_virtuemart_orders

[carsten888]

thank you. I will try that.