How to get abandon cart details in Magento

[sandeep_salim]

Hi Team,

I have a request to create an abandon cart email with details like product name,Product link,Product image and a link directly to the user cart and other relevant details.

Can anyone show some leads on which tables ill be able to lookup on and how can i achieve it via GetResponse.

Below is the query which i use now,

SELECT 
       "main_table"."customer_id"
	   ,CONCAT_WS(' ', cust_fname.value, cust_lname.value) AS "customer_name"
	   ,"cust_fname"."value" AS "firstname"
       ,"cust_lname"."value" AS "lastname"
       ,"cust_email"."email","main_table"."entity_id"
	   ,"main_table"."store_id","main_table"."created_at","main_table"."updated_at"
	   ,"main_table"."items_count", "main_table"."grand_total"
      ,(main_table.base_subtotal_with_discount * main_table.base_to_global_rate) AS "subtotal"
FROM dl_magento.sales_flat_quote AS "main_table"
  INNER JOIN dl_magento.customer_entity AS "cust_email" ON cust_email.entity_id = main_table.customer_id
  INNER JOIN dl_magento.customer_entity_varchar AS "cust_fname"
    ON cust_fname.entity_id = main_table.customer_id AND cust_fname.attribute_id = 5
  INNER JOIN dl_magento.customer_entity_varchar AS "cust_lname"
    ON cust_lname.entity_id = main_table.customer_id AND cust_lname.attribute_id = 7
WHERE (items_count != '0') AND (main_table.is_active = '1')
 AND (main_table.created_at >= '2014-06-15 00:00:00') ORDER BY updated_at DESC

 note: Im using Python and PostgreSQL.

 Thanks ,

Sandy 

[Tarandeep Singh · Accepted solution]

@sandeep_salimNot sure why you are using Python and PostGre for the same. I would recommend to buy an extension and use for the functionality you need. Such extension will come with many more functionalities. Implementing the same thing would require good amount of implementation+testing etc.

However, still you want to continue the route you have taken then look into Quote & Quote items table to start with.

Problem solved? Please give 'Kudos' and accept 'Answer as Solution'.

[sandeep_salim]

Hi Taran,

Good morning!Thanks a lot for your reply.

I'm trying to build a SQL query which fetches al the details and insert it into my postgres database and from there using the Get Response" API for other services.

I go the details from quote and Quote Items table as you mentioned, but i want the details like PRODUCT IMAGE,PRODUCT URL and the link to the customers cart.SO can you please help me in identifying which all tables i have to look for getting these details.

Thanks in Advance

Sandeep