No sales reports?

[cjrhoads]

I have searched the documentation and the forums, and I'm rather shocked to discover that presta shop doesn't appear to have even simple sales reports. How do people who use prestashop keep track of their sales? Am I just missing it? Is it just assumed everyone knows how to write SQL queries to get a list of everything that was sold and to whom?

CJ

[NemoPS]

no sales reports? There is a whole section under "statistics", have you seen that?

[cjrhoads]

Yes, I have spent hours going through all the "stats", but the report I need (that I think anyone with a Prestashop would need) is a report that lists all the products purchased along with the name, address, and phone number of the customers, quantities, and how it was paid for within a certain time period.  The stats only gives summary statistics.  I can get "best customer" and "best products", but not apparently by timeframe and none of the details I need to actually run the shop (i.e. get a list of what was purchased each day or week in order to send the customers their products).   If I knew who purchased, I suppose I could go into the order module and look up individually what they purchased - but since I can't get any kind of report, I don't know who purchased what.  I keep thinking I must be missing something.  I also need to download a monthly report to enter the info into my accounting system, and I can't seem to find that anywhere, either.

 

There were a few other questions about this in the forums, and the answers always seemed to be "there is no module or screen to do that".  I find that hard to believe.  If that is true, how to people who actually run these shops find out how and when to send their customers the products they purchased?  How do they enter info into their accounting systems to track income, gross profits, and cost of goods?  Do they all know how to write SQL queries?  Do they hire someone to write custom reports?

 

There doesn't seem to be even a paid module for this.  There are paid modules to report sales tax, but since I don't even enable the sales tax component (we are a non-profit) I doubt that would give me what I need to know.

 

Can anyone explain what I am missing?

 

Much appreciated,

CJ Rhoads

[A-Z Hosting]

CJ,

 

I think what you are looking for is "Orders" and you can find those under the orders menu item and orders submenu. Orders > Orders should give you a good list of who registered and purchased what. As for importing well that's another issue. Depending on what accounting software you use there may be a contribution for an export file of each order. As I recall there used to be an Order Manager as well that took care of imports and shipping too.  Not sure if these are still available for 1.6, or whether you even have 1.6 since you didn't give us that information.

 

Regards,

 

Wil 

[ZikmaSolutions]

If you dont see orders in stats, you must generate invoice for each product u sold.  So you if your order is completed go to Catalog -> Orders -> open order and click (GENERATE INVOICE).

In order list u must have 2 icons (truck and calculator) (Shipping and invoice)

[cjrhoads]

Thanks for your reply.  I appreciate it.

 

We do know how to generate and save the invoices, but I was hoping for a report so that we didn't have to actually open and look at each individual page for each individual invoice in order to know what we sold.  But apparently, a report with customer name, order number, and product name, quantity and payment information doesn't exist in Prestashop.  Furthermore, there doesn't appear to even be a table in the dataset that we can query that would give us that information. 

 

We may end up switching to something else, but we will see.

 

Thanks again.

 

Warmest regards

CJ Rhoads

[Nickless]

I totally agree. Prestashop is a nice CMS but it is horrible when it comes to a sales and/or tax report. A big disappointment and an ongoing annoyance.

There are (expensive) payed addons but they are most about tax and profits so that wont be useful for you.

In a discussion some time ago it was suggested that Prestashop deliberately doesn't support this because of a partnership with Avalara...

[cjrhoads]

Could be.

 

I must mention that in another thread, someone shared with me the key table that I was missing.  I can now get the sales information through a query so I don't have to look up each and every one.

 

If anyone is interested, here's the query:

SELECT ps_customer.company, ps_customer.firstname, ps_customer.lastname, ps_customer.email, ps_order_detail.product_name, ps_supplier.name, ps_order_detail.product_price, ps_order_detail.total_price_tax_incl, ps_orders.payment, ps_orders.date_add, ps_orders.invoice_date, ps_orders.delivery_date, ps_order_state_lang.name, ps_address.address1, ps_address.address2, ps_address.city, ps_state.iso_code, ps_address.postcode, ps_address.phone, ps_address.phone_mobile, ps_order_detail.product_reference, ps_orders.reference, ps_order_detail.id_order_detail, ps_order_detail.id_order, ps_order_invoice.id_order_invoice, ps_orders.id_customer, ps_product.id_supplier, ps_product.id_manufacturer, ps_order_state_lang.id_order_state, ps_address.id_state
FROM ((ps_supplier INNER JOIN ((((ps_orders INNER JOIN ps_order_detail ON ps_orders.id_order = ps_order_detail.id_order) INNER JOIN (ps_address INNER JOIN ps_customer ON ps_address.id_customer = ps_customer.id_customer) ON ps_orders.id_customer = ps_customer.id_customer) INNER JOIN ps_product ON ps_order_detail.product_id = ps_product.id_product) INNER JOIN ps_order_invoice ON ps_order_detail.id_order_invoice = ps_order_invoice.id_order_invoice) ON ps_supplier.id_supplier = ps_product.id_supplier) INNER JOIN ps_order_state_lang ON ps_orders.current_state = ps_order_state_lang.id_order_state) INNER JOIN ps_state ON ps_address.id_state = ps_state.id_state
WHERE ((Not (ps_order_state_lang.name)="test item"));

 

(Note: this query is not meant to be copied and pasted verbatim, but just helpful if you already know how to do an SQL query and just don't know which tables to join.  For example, I have a  "test item" status that is not part of the default, so the WHERE clause won't do you any good.  (Probably wouldn't hurt, but just to let you know I have not "sanitized" the example for general use.)

Hope that's helpful. 

[Omk]

I can't believe that this is the situation! Prestashop has all the bells and whistles, but not a monthly sales report? Not tax report? Not product mix report? Seriously?

What a retarded system is this?

[cjrhoads]

I can't believe that this is the situation! Prestashop has all the bells and whistles, but not a monthly sales report? Not tax report? Not product mix report? Seriously?

 

What a retarded system is this?

Well, I decided to stick with it once I figured out how to write sql queries to get the info I needed.  I think someone else said it; this free shopping cart system is highly capable with lots of features but often provides just the bare bones.  To get full functionality, you either have to be a good enough programmer to write the PHP statements, a good enough database person to write the SQL statements, or pay for one of the add ons that does it for you.  I consider it a fair trade, as Prestashop still has a lot of capability that is not found in other free systems.

[lhalda]

None of these worked for me; for future reference here is my custom query for report of up to last 1000 products sold:

SELECT d.product_name AS `Product name`, c.firstname AS `Name`, c.lastname AS `Surname`, c.email AS `e-mail`, c.birthday AS `Born`, d.product_quantity AS `Product quantity`, d.product_price AS `Product price`, o.reference AS `Order reference`, o.payment AS `Order payment`, c.id_customer AS `Customer ID`, d.id_order_detail AS `Product order ID`, d.product_id AS `Product ID`, o.id_order AS `Order ID`, o.total_discounts_tax_incl AS `Order discount`, o.total_paid_tax_incl AS `Paid for order`, o.date_upd AS `Payment date`, c.newsletter AS `Wants newsletter`, c.optin AS `Wants partners ads`, c.date_add AS `Reg date`, c.date_upd AS `Profile update date`
FROM (SELECT * FROM `ps_orders` WHERE `current_state` = 2 LIMIT 0, 1000) o
INNER JOIN `ps_order_detail` d ON o.id_order = d.id_order
LEFT JOIN `ps_customer` c ON o.id_customer = c.id_customer
ORDER BY o.`id_order` DESC

Hope it helps someone

Working in PS 1.6.1.3

 

It's not beautiful or optimized, don't hate my beginner's SQL I have machine with enough power and e-shop with low enough turnover for such query not to hang the server the least bit. However for some heavy workload it'd probably be better to have this checked by some professional to have it optimized for performance.

Edited March 26, 2016 by lhalda (see edit history)