It is sad to say, but in spite of the contemporary well developed and tool-rich marketing system, the main methods to attract customers and increase sales are still quite traditional and involve various promotions, sales, discounts and coupons. Of course, Magento provides a sufficiently rich functionality for analysis and statistics. But, as you know, there are never too many tools. That is why we decided to make another present surprise to our sales managers.
Basically, there is, of course, available a default report on used coupons under the Reports – Sales – Coupons section. But we have decided to push the limits and go further. First of all, we will start with creating a module with a custom grid (check here for more details about creating a module).
We will describe only the main part – that is creating of a grid.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
/** * Prepare grid collection * * @return Mage_Adminhtml_Block_Widget_Grid */ protected function _prepareCollection() { $collection = Mage::getModel('sales/order')->getCollection(); $collection->addAttributeToFilter('coupon_code', array('notnull' => TRUE)); $collection->addAttributeToFilter('coupon_code', array('neq' => '')); $collection->getSelect()->joinLeft( array('salesrule_coupon'=>$collection->getTable('salesrule/coupon')), 'main_table.coupon_code = salesrule_coupon.code', array('salesrule_coupon.rule_id')); $collection->getSelect()->joinLeft( array('salesrule_rule'=>$collection->getTable('salesrule/rule')), 'salesrule_coupon.rule_id = salesrule_rule.rule_id', array('salesrule_rule.name')); $this->setCollection($collection); return parent::_prepareCollection(); } |
The main request is to find all orders where соupоn_соde is specified. This will provide us with the number, amount, status of the order and the used coupon itself. However, coupon code looks not informative enough. That is why let’s also add the name of the coupon: simply join the models salesrule/coupon and salesrule/rule and thus we will get the information about the rule used.
Seems to me, now it looks pretty informative and functional.
Actually, we could already stop here, yet there is still a question to be answered: What should we actually do with all these results?
Let’s add the possibility to instantly go to coupon and order pages.
Not to show the entire listing let me give you just an example for the name of the rule. Indicate your renderer in the grid’s block:
|
1 2 3 4 5 6 7 |
$this->addColumn('name', array( 'header' => Mage::helper('salesrule')->__('Rule Name'), 'align' => 'left', 'index' => 'name', 'renderer' => $this->getNoRenderer()?'':'belvg_couponusage/adminhtml_couponsusage_grid_renderer_rule', 'filter_index' => 'salesrule_rule.name' )); |
And the renderer itself:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<?php class Belvg_CouponUsage_Block_Adminhtml_Couponsusage_Grid_Renderer_Rule extends Mage_Adminhtml_Block_Widget_Grid_Column_Renderer_Abstract { public function render(Varien_Object $row) { $rule_id = $row->getRuleId(); $name = $row->getName(); $rule_url = $this->getUrl('adminhtml/promo_quote/edit', array('id' => $rule_id)); return sprintf('<a href="%s">%s</a>', $rule_url, $name); } } |
Now it looks like to be complete. Let’s see how the module will perform in test runs.
Check out the ready-to-use module on GitHub:
Partner With Us
Looking for a partner to grow your business? We are the right company to bring your webstore to success.
Talk to Andrey
@Mahmoud check the gist https://gist.github.com/pavelnovitsky/cb1a9695568e4c79862537af37594372 where I’ve added customer’s name column
Hi, how i can join customer’s collection to the query to show the customer name please?
Nice extension! I tried to add a column to show the subtotal of order instead of grand total (I don’t want to include shipping costs), but column is blank. I’ve tried these:
$this->addColumn(‘subtotal_amount’, array(
‘header’ => Mage::helper(‘salesrule’)->__(‘Sales Subtotal Amount’),
‘sortable’ => false,
‘type’ => ‘currency’,
‘currency_code’ => $currencyCode,
‘total’ => ‘sum’,
‘index’ => ‘subtotal_amount’,
‘rate’ => $rate,
));
and this:
$this->addColumn(‘subtotal_amount’, array(
‘header’ => Mage::helper(‘sales’)->__(‘Subtotal Amount’),
‘index’ => ‘subtotal_amount’,
‘type’ => ‘currency’,
‘currency’ => ‘order_currency_code’,
));
A million thanks for this. Its a pity that this is not available out of the box in magento. I was stuck doing this, and your blog post certainly helped
Great work
@Dan, all you should do is to join customer’s collection to the query and add column in _prepareColumns() method
Firstly, thank you for posting this, it has been a lifesaver!!!! I just have one quick query – is it possible to also display the customer name? (we are looking to be able to reconcile commission payments to reps based on a coupon code they use).
Your help is greatly appreciated, Dan