Magento: My SQL Queries

The Magento backend doesn’t provide all informations and there are some useful informations in the database.

You can send an email to the user who have abandoned the shopping cart and offer them a discount. The email information is available only for the registered users or the users into the registration process.

You will find below my favorite SQL Queries : there are under the format of shell script and you can modify them for your environment.

  1. Content of abandoned shopping cart of registered users
    #!/bin/sh
    mysql -uroot -p database --execute "select q.entity_id,cb.name,cb.code,q.grand_total,q.created_at,q.customer_prefix,q.customer_firstname,q.customer_lastname,q.customer_email,q.coupon_code,q.remote_ip,q.checkout_method,qi.sku,qi.name from sales_flat_quote as q left join sales_flat_quote_item as qi on qi.quote_id=q.entity_id left join core_store as c on c.store_id=q.store_id inner join core_website as cb on c.website_id=cb.website_id where customer_email is not null order by q.created_at;"
    
  2. Content of abandoned shopping cart of guest users
    #!/bin/sh
    mysql -uroot -p database --execute "select q.entity_id,cb.name,cb.code,q.grand_total,q.created_at,q.coupon_code,q.remote_ip,q.checkout_method,qi.sku,qi.name from sales_flat_quote as q left join sales_flat_quote_item as qi on qi.quote_id=q.entity_id left join core_store as c on c.store_id=q.store_id inner join core_website as cb on c.website_id=cb.website_id where customer_email is null order by q.created_at ;"
    
  3. Address detail of an abandoned shopping cart of registered users
    #!/bin/sh
    if [ $# -eq 1 ]
    then
     mysql -uroot -p database --execute "select quote_id,created_at,updated_at,firstname,lastname,street,postcode,city,telephone,weight,grand_total,customer_notes,discount_description,discount_amount from sales_flat_quote_address where quote_id=$1"
    else
     echo "Usage: $0 entity_id"
    fi
    
  4. Detail on users registered thru the Inchoo SocialConnect Magento module
    #!/bin/sh
    mysql -uroot -p database --execute "select email,updated_at,core_website.name,core_store.code,attribute_code,value from eav_attribute inner join customer_entity_text on eav_attribute.attribute_id=customer_entity_text.attribute_id inner join customer_entity on customer_entity_text.entity_id=customer_entity.entity_id inner join core_store on customer_entity.store_id=core_store.store_id inner join core_website on core_store.website_id=core_website.website_id where eav_attribute.attribute_code like "inchoo%id";"
    

To use my SQL queries, just create a file with my code, change the database name and change the permission of the file (i.e. chmod 755 file).

Don’t hesitate to post a comment !

Et voila,

Nicolas Portais
Author Photographer
http://www.mystockphoto.fr/
http://photos-art.pro/

Ce contenu a été publié dans Magento, avec comme mot(s)-clé(s) , , , , , , , , , , , , , , , , . Vous pouvez le mettre en favoris avec ce permalien.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Captcha (solve the arithmetic equation) * Time limit is exhausted. Please reload CAPTCHA.