How do I find out what user used what coupon code?

Question

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?

Answer 1

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.

Answer 2

Reports > Sales > Coupons in your admin area will let you see how many times a particular discount code was used, the amount of sales generated for and the amount of total discount given for each as well. You can filter it down by day, month, year etc. You can also filter by order status and for a certain date period.

Answer 3

Default report is available in report -> sales -> coupons.

And with script you can find used coupan count and customer details very easily :

$coupon = Mage::getModel('salesrule/coupon/usage');
$coupon->load('code', 'coupon_id');
if($coupon->getId()) {
    $timesUsed = $coupon->getTimesUsed();
    $customer = $coupon->getCustomerId();
    echo $timesUsed;
    echo $customer;
}

Answer 4

I had a similar request from client of ours, they wanted to know what coupon was used if any on a particular order

for now I am doing this manually in db but as soon as I will make up extension for this I will update my question, hope this will help meanwhile

   SELECT sfo.customer_id AS customer, ce.email, GROUP_CONCAT(cev.value SEPARATOR ' ') as name,
sfo.entity_id, sfo.increment_id,scu.times_used,sc.coupon_id,sc.rule_id,sc.code FROM sales_flat_order sfo 
LEFT JOIN salesrule_coupon_usage scu ON sfo.customer_id = scu.customer_id LEFT JOIN salesrule_coupon sc 
ON sc.coupon_id = scu.coupon_id LEFT JOIN customer_entity ce ON ce.entity_id = sfo.customer_id 
LEFT JOIN customer_entity_varchar cev ON cev.entity_id = sfo.customer_id WHERE (cev.attribute_id IN (5,7) 
OR cev.attribute_id IS NULL) GROUP BY sfo.increment_id ORDER BY sfo.increment_id;

some lines might be full of nulls this is more likely guest customers and some will have nulls in coupon info, these will be orders where coupons were not used

Answer 5

There's an open source coupon module which I have used (Magento 1 - EE) https://github.com/pavelnovitsky/CouponUsage

It creates a new admin grid:

Admin > Promotions > Coupon Usage   

which links coupons to orders. I can confirm that it works well, even though it's a few years old now.

Answer 6

In a Magento ce-1.8.1.0 shop I found the database field applied_rule_ids in the table sales_flat_order. Looks like this matches the id from the Shopping Cart Price Rules page.

Could be useful if you have generated coupon codes:

`SELECT COUNT(*) FROM sales_flat_order where FIND_IN_SET('1', applied_rule_ids)

Answer 7

I have a coupon code that has a Uses Per Customer value of 1, and I'm not able to use it on my customer account. So I queries the sales_flat_order table as the other answers suggest to see how times I've used this code, and there was no order of mine in that table that shows I used the coupon code in question. I had to dig through the code and found in Mage_SalesRule_Model_Validator::_canProcessRule() it actually checks the tables salesrule_coupon_usage and salesrule_customer ..

select * from production.mage_salesrule_customer
where customer_id = 58394  

This query above showed I had used the coupon one time. I do not know why it shows I used it once, and why there is no record of my use in the orders table (yet), but I hope this helps somebody else that was struggling to figure this out.

Answer 8

For magento 2, please run this sql to get customer email, coupon code, discount amount, grand total and increment id

select a.customer_email, a.coupon_code, b.increment_id, a.discount_amount, 
a.grand_total from `sales_order` as a, `sales_order_grid` as b where 
coupon_code is not null AND a.`entity_id` = b.`entity_id`