9

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?

  • 1
    Is there anything I can do to help you close this out? Let me know if any of the below solutions helped. – philwinkle Nov 22 '13 at 20:22
9

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.

  • Where to add this code to make work " Which customers used which coupons" in magento 1.9 – Jai Mar 30 '16 at 10:48
  • This is a mater of running from a PHP script within the context of the Magento store. – philwinkle Mar 30 '16 at 12:58
  • right and I am asking location of file. You can post what you have done to explain me. – Jai Mar 30 '16 at 18:14
  • Isn't that then not a coupon? It's just a promo? I think that is kind of obvious given the question was specifically about codes. – philwinkle Feb 21 '17 at 17:19
6

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.

  • Thanks Blair! Do you know if there is a way to see which one of my users used a specific code? – Christina Rule Nov 13 '13 at 21:54
  • 1
    There's nothing in the default functionality of Magento to do this. (Other than looking at each order individually). But the information could easily be pulled into which ever page in the admin area you want. The data is stored in a database table after all. So for example, you have Reports > Customers> Customers by order totals, this would be a could place to pull this into. Just creating a column called "coupon" or the like, and inserting the code in where used. – MagentoMac Nov 13 '13 at 22:00
  • Something like this module below will also allow you to add "coupon code" to the Sales > Orders page, amasty.com/better-order-management.html which I think is exactly what you are looking for? – MagentoMac Nov 14 '13 at 10:15
2

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;
}
  • On which location to put this code? – Jai Mar 30 '16 at 10:59
  • In your code, how does the $coupon have a specific customer tied to the object? Seems like you're missing something. – Nick Rolando Aug 23 '18 at 18:05
1

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

  • On which location to put your code? – Jai Mar 30 '16 at 11:00
0

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.

0

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)

  • 1
    it's comma separated because there might be several rules involved. So better use FIND_IN_SET() instead of = – Fabian Schmengler Aug 1 '16 at 15:28
0

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.

0

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`

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.