Magento find number of times a coupon has been used

Posted by Damodar Bashyal on October 13, 2014

 

Magento was not recording correct number of times a shopping cart coupon was used. When I ran below query in database, it was giving wrong information as far as I know.

mysql> select u.coupon_id, c.code, count(*) as total from salesrule_coupon_usage u, salesrule_coupon c where u.coupon_id=c.coupon_id and u.coupon_id IN (119, 132, 91) group by u.coupon_id;

May be I was wrong to use that query to find usage :), so tried another query, which gave me better report.

mysql> select UCASE(coupon_code) as `Coupon Code`, status as `Order Status`, count(*) as `Times Used` from sales_flat_order where coupon_code = 'APPLE14' group by coupon_code, status;

And this is what I got.

Magento coupon usage count

I also, wrote quick shell script to test, which looks like this:

<?php
/*
 * author : Damodar Bashyal
 * url: http://twitter.com/dbashyal
 * usage: php couponusage.php --coupon 'coupon1,coupon2,coupon3'
 */
require_once 'abstract.php';

class DSE_Shell_coupon_usage extends Mage_Shell_Abstract
{
    public function run()
    {
        $coupons = $this->getArg('coupon');
        if(!$coupons){
            echo "\nNo coupon code provided.\n";
            return;
        }
        $coupon_code = explode(',', $coupons);

        /** @var $coupon Mage_SalesRule_Model_Coupon */
        $coupon = Mage::getModel('salesrule/coupon');
        $collection = $coupon->getCollection();
        $collection->addFieldToFilter('code', array('in' => $coupon_code));

        echo "\nCoupons and usage count:\n------------------------";
        foreach($collection as $coupon){
            $times = 0;
            if ($coupon->getId()) {
                $times = $coupon->getTimesUsed();
            }
            echo "\n" . $coupon->getCode() . ": used {$times} times.";
        }
        echo "\n-- Complete!!! \n\n";
        return;
    }
}

$shell = new DSE_Shell_coupon_usage();
$shell->run();

Bonus:

Set all sales rules that already expired to inactive. CURDATE() returns current date in mysql.

update salesrule set is_active=0 where to_date < CURDATE() and to_date IS NOT NULL and is_active=1;

Set all catalogue rules that already expired to inactive.

update catalogrule set is_active=0 where to_date < CURDATE() and is_active = 1;

Please comment below with your tricks that worked.

zwangerschapsjurken posted on - Tuesday 16th of December 2014 08:09:55 AM

Great post, I had a similar problem when checking my coupon codes usage. Thanks!
 
not published on website


QR Code: Magento find number of times a coupon has been used