Magento – How to find out what user used what coupon code

couponmagento-1promotions

I have a few coupon codes in my store, and I’d like to be able to track what code a user may have utilised. Inversely, is there a way to see how many times a particular code was used?

Best Answer

Which customers used which coupons:

I usually shy away from raw db queries, but in this case I will make an exception:

select customer_email, group_concat(distinct sfo.coupon_code) from sales_flat_order sfo
where coupon_code is not null
group by customer_email;

You could do the same with the Magento ORM - will formulate a writeup on how to do that and edit afterward and here's how:

$coll = Mage::getModel('sales/order')->getCollection()
    ->getSelect()->reset(Zend_Db_Select::COLUMNS)
    ->columns(array('customer_email',new Zend_Db_Expr('group_concat(distinct coupon_code)')))
    ->where(new Zend_Db_Expr('coupon_code is not null'))
    ->group(array('customer_email'));

How many times a coupon was used:

As already pointed out in another answer this is in a report. At the most basic level the query for that would be:

select coupon_code,count(coupon_code) from sales_flat_order
group by coupon_code;

The ORM-centric way of handling this is also fairly straightforward:

$coll = Mage::getModel('sales/order')->getCollection()
    ->getSelect()->reset(Zend_Db_Select::COLUMNS)
    ->columns(array('coupon_code',new Zend_Db_Expr('count(coupon_code)')))
    ->group(array('coupon_code'));

Note that this does not take into account order status or invoiced payment.

Related Topic