Magento-1.7 – Direct SQL Query for Coupon Based Orders

eavmagento-1.7MySQL

I am trying to write a SQL query for Magento that will show orders which were paid (non-zero total) and those which were coupon based (zero total). The Coupon-based orders should be divided by the promotion that the coupon was from. Then each row of the report show the following:

The promo name (Shopping cart price rule name)
Number of orders
Percentage of total orders
Average order total

I realize this is could be an extremely complex query, so would be happy with someone pointing out the table(s) that I will need in order to execute this. Any an all help is welcome and thanked =)

Best Answer

This seems to have done the trick

SELECT coupon_rule_name                         AS 'Promotion Used'
     , coupon_code                              AS 'Code Used'
     , COUNT(coupon_code)                       AS 'Times Used / Number of Orders'
     , SUM(subtotal)                            AS 'Cumulative Price'

     , SUM(total_paid)                          AS 'Cumulative Paid with Coupon'
     , AVG(total_paid)                          AS 'Average Order Total (W/  Coupon)'
     , AVG(subtotal)                            AS 'Average Order Total (W/O Coupon)'

     , ABS(SUM(discount_amount))                AS 'Cumulative Savings'

     , (
        SUM(discount_amount) - SUM(total_paid)
       )                                        AS 'Cumulative Loss'

     , CONCAT(ROUND((
        COUNT(coupon_code) / (SELECT COUNT(*) FROM sales_flat_order s)
       ) * 100, 1), '%')                        AS 'Percentage'

FROM     sales_flat_order
WHERE    coupon_code        IS NOT NULL
GROUP BY coupon_code
ORDER BY COUNT(coupon_code) DESC;

Related Solutions

Magento 1.8 – Translate Direct SQL Query into Collection Model Usage

Here's a starting point:

$collection = Mage::getModel('catalog/product')
                ->getCollection()
                ->addIdFilter($productIdsArr)
                ->joinTable(
                    array('prod_categ_assoc' => 'catalog/category_product'), 
                    'product_id=entity_id', 
                    array('categ_id' => 'category_id', 'position'), 
                    null, 
                    'left'
                );

 $result = array();

 // play with the joined columns
 foreach ($collection as $product) {
    $key = $product->getId() . "-" . $product->getCategId();
    $result[$key] = $product->getPosition();
 }

 var_dump($result);

 // check the resulting SQL with this
 var_dump($collection->getSelect()->__toString());

You may have noticed that I used categ_id as an alias to category_id. That's because Mage_Catalog_Model_Product::getCategoryId() is already implemented (and probably used in other places). Better stay safe and use our own attribute - categ_id.

You'll be interested in Mage_Catalog_Model_Resource_Product_Collection::addIdFilter() and Mage_Eav_Model_Entity_Collection_Abstract::joinTable().

PS: You should check the collection query string before the foreach, too. It'll be very different from the one after the loop. Magento hooks in after collection loading with some other logic. Just a tip.

Update

I tested on a vanilla installation, and, if you have a product assigned to more than one category, I think the above query will definitely fail with Object with the same id "<id>" already exist. The collection ID field is the product ID, and that must be unique throughout the collection.

Some 10 minutes later...

Yup, it throws. This is the best I could come up with so that you don't see any SQL in there:

// $collection is the above collection object
$resultArr = $collection->getConnection()->fetchAll($collection->getSelect());

But this collection is not properly set - other SQL logic is applied when you load the collection (remember my tip?), but you can't load it now, because it will throw. Still want convention?

Magento 1.7 – Get SKU and Order ID Through SQL Query

you should use also the following tables and fields in your query: sales_flat_order_item [quote_item_id]

sales_flat_quote_item [product_id]

Best Answer

Related Solutions

Magento 1.8 – Translate Direct SQL Query into Collection Model Usage

Update

Magento 1.7 – Get SKU and Order ID Through SQL Query

Related Topic