Jump to content

Abandoned Carts Mysql Query


Recommended Posts

Greetings,

 

My client needs a report using MySQL , this report must have all the abandoned carts from the store , I dont know what field or condition should I have to use to obtain this query.

 

Any advice or comment would be appreciated.

 

Thanks.

Link to comment
Share on other sites

You basically want all the carts from the ps_cart table that do not have a corresponding record in the ps_orders table (carts without a linked order is an abandoned cart) The query below will give you those rows.

SELECT c.* 
FROM ps_cart c
LEFT JOIN ps_orders o ON ( c.id_cart = o.id_cart ) 
WHERE o.id_order IS NULL 
ORDER BY c.id_cart ASC
Link to comment
Share on other sites

  • 7 months later...
  • 5 years later...

This request gives us all abandoned carts without date limit, if you want you can give an interval of time to execute this one:

select c.email , a.id_cart,a.date_add,
sum(if((IFNULL(cp.quantity,0) || ifnull(p.price,0)), cp.quantity*p.price,null)) as price
 from ps_cart as a 
 LEFT JOIN ps_customer c ON (c.id_customer = a.id_customer)
 LEFT JOIN ps_currency cu ON (cu.id_currency = a.id_currency) 
 LEFT JOIN ps_carrier ca ON (ca.id_carrier = a.id_carrier) 
 LEFT JOIN ps_orders o ON (o.id_cart = a.id_cart) 
  LEFT JOIN ps_cart_product cp ON (cp.id_cart = a.id_cart) 
  LEFT JOIN ps_product p ON (cp.id_product = p.id_product) 

 LEFT JOIN ( SELECT id_guest FROM ps_connections WHERE TIME_TO_SEC(TIMEDIFF(CURRENT_TIMESTAMP, date_add)) < 1800 LIMIT 1 ) AS co
 ON co.id_guest = a.id_guest
 where  isnull(id_order)
 group by id_cart;

 

It works for me  :) 

Edited by nawres (see edit history)
Link to comment
Share on other sites

  • 5 months later...

Hi, 

one small correction here to the SQL query, which will return correctly the price of combinations also not only the main product's price:

select c.email , a.id_cart,a.date_add,
/*sum(if((IFNULL(cp.quantity,0) || ifnull(p.price,0)), cp.quantity*p.price,null)) as price,*/
 if( sum(if((IFNULL(cp.quantity,0) || ifnull(p.price,0)), cp.quantity*p.price,null))+0 = 0 , sum(if((IFNULL(cp.quantity,0) || ifnull(p.price,0)), cp.quantity*pa.price,null))  , sum(if((IFNULL(cp.quantity,0) || ifnull(p.price,0)), cp.quantity*p.price,null)) ) as price
 from ps_cart as a 
 LEFT JOIN ps_customer c ON (c.id_customer = a.id_customer)
 LEFT JOIN ps_currency cu ON (cu.id_currency = a.id_currency) 
 LEFT JOIN ps_carrier ca ON (ca.id_carrier = a.id_carrier) 
 LEFT JOIN ps_orders o ON (o.id_cart = a.id_cart) 
  LEFT JOIN ps_cart_product cp ON (cp.id_cart = a.id_cart) 
  LEFT JOIN ps_product p ON (cp.id_product = p.id_product) 
LEFT JOIN ps_product_attribute pa ON (pa.id_product = p.id_product  AND pa.id_product_attribute = cp.id_product_attribute )
 LEFT JOIN ( SELECT id_guest FROM ps_connections WHERE TIME_TO_SEC(TIMEDIFF(CURRENT_TIMESTAMP, date_add)) < 1800 LIMIT 1 ) AS co
 ON co.id_guest = a.id_guest
 where  isnull(id_order)
 group by id_cart;

Kind regards, Leo.

Link to comment
Share on other sites

  • 1 year later...

Hello @Prestachamps & @nawres

thanks for the query it is a good start but I don't get the product attribute price I  still get the main product price. (tried both queries)
Do you have any idea how to get the price of the product attribute chosen in the cart ?

Actually I would like the query to display what we see in the admin cart page

Thanks

Link to comment
Share on other sites

Hi.

Does the client want to see the same overview as in the "Shopping Carts" section? Why is a sql query needed? Isn't it easier to use your own module? Prestashop includes many functions that will return results. The module can contain a clear table including export to csv.

Rules, discounts, etc. can also be set in the cart, and it is almost impossible to get it using sql.

Link to comment
Share on other sites

Hi 

actually I want to export carts infos on a monthly basis to make statistics.
Moreover I need to delete some customer ids from the list.

Here are what I would need
is guest or customer name
is abandonned / order id
product(s) amount / order amount
cart date 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...