A BloomerArmada fan had a nice challenge with a client – how to display the total amount of sales generated by a given coupon code?
So I managed to create this snippet, which adds a brand new column to the WooCommerce Coupon table view with “total sales” value in it for each coupon code – enjoy!
PHP Snippet: Show Total Sales by Coupon Code @ WooCommerce > Marketing > Coupons
/**
* @snippet Total Sales By Coupon @ WooCommerce Admin
* @how-to Get CustomizeWoo.com FREE
* @author Rodolfo Melogli
* @compatible WooCommerce 5
* @donate $9 https://businessbloomer.com/bloomer-armada/
*/
// -------------------------
// 1. Create function that calculates sales based on coupon code
function bbloomer_get_sales_by_coupon( $coupon_code ) {
global $wpdb;
$total = $wpdb->get_var( "
SELECT SUM(pm.meta_value)
FROM $wpdb->posts p
INNER JOIN {$wpdb->prefix}postmeta as pm ON p.ID = pm.post_id
INNER JOIN {$wpdb->prefix}woocommerce_order_items as oi ON p.ID = oi.order_id
WHERE p.post_type = 'shop_order'
AND pm.meta_key = '_order_total'
AND p.post_status IN ( 'wc-completed', 'wc-processing')
AND oi.order_item_type = 'coupon'
AND oi.order_item_name LIKE '" . $coupon_code . "'
" );
return wc_price( $total );
}
// -------------------------
// 2. Add new column to WooCommerce Coupon admin table with total sales
add_filter( 'manage_edit-shop_coupon_columns', 'bbloomer_admin_shop_coupon_sales_column', 9999 );
function bbloomer_admin_shop_coupon_sales_column( $columns ) {
$columns['totsales'] = 'Total Sales';
return $columns;
}
add_action( 'manage_shop_coupon_posts_custom_column', 'bbloomer_admin_shop_coupon_sales_column_content', 9999, 2 );
function bbloomer_admin_shop_coupon_sales_column_content( $column, $coupon_id ) {
if ( $column == 'totsales' ) {
echo bbloomer_get_sales_by_coupon( get_the_title( $coupon_id ) );
}
}
Hi,
the snippet is very useful but in total is included shipping costo, it’s possible to have the total without shipping cost?
Thank you!
Hello Simone, thanks for that! I believe you also need to get ‘_order_shipping’ from the DB, and then your total would be calculated as the difference between ‘_order_total’ and ‘_order_shipping’. Totally doable
woodmart theme – this instantly crashed my site!
I couldn’t get into admin at all and had to remotely deactivate all plugins then I had to go to phpMyAdmin on my host site and find it (a bit daunting tbh!)
I was using the “code snippets” plugin to add the code – I had done this with several of your other snippets with no problem! I am sure it is another great snippet on the right theme 🙂
Amy, thanks for your comment! You’re right, this was not working any longer. I’ve now completely revamped the snippet and decided to move the “total sales” in the coupon table view. Hope you like it!
hi
Sorry I didn’t check back earlier.
I have put the revised version in and it works perfectly, thank you.
As a non-techie person, I find it strange how some snippets work perfectly, then don’t, some don’t work on some themes etc!
I *am* getting more techie though!
Brilliant work!
Amy
Story of my life! Thank you
This is a much more concise solution, where you don’t need to loop through all orders:
https://stackoverflow.com/questions/42769306/how-to-retrieve-a-list-of-woocommerce-orders-which-use-a-particular-coupon
Nice!
Thanks for the snippets. This saves me a lot of time.
Great!
Hi Rodolfo,
Thanks for all of the snippets you have provided! We’re running this one on a client site that uses Woocommerce Subscriptions and sells some products that automatically renew on a monthly or weekly basis. Recently, we noticed that renewal payments weren’t processing unless we turned this snippet off. We also couldn’t change our subscriptions’ statuses without receiving timeout errors when this snippet was active. Any ideas about what might be going on? Thanks again for all of your insights into Woocommerce!
Hi Laura – I guess you’ve customized the snippet to return multiple coupon codes and their totals? Maybe there is something wrong in your code that needs troubleshooting in this case, but I’m afraid it’s custom work. If you’d like to get a quote, feel free to contact me here. Thanks a lot for your understanding!
Actually, it didn’t work for me. Latest version.
Hi Stef! What didn’t work exactly?
I did not work for me either: I do not see it and there is error:
Deprecated: WC_Order_Item_Coupon::offsetGet sa od verzie 4.4.0 nepoužíva bez dostupnej alternatívy. in /wp-includes/functions.php on line 4783
which mean that there is no alternative for the Deprecated command.
Tried with this also? https://www.businessbloomer.com/woocommerce-calculate-sales-coupon-code/#comment-383862
How to display 2 coupons?
Hey Nghia, thanks so much for your comment! Yes, this is possible – but unfortunately this is custom work and I cannot provide a complementary solution here via the blog comments. Thanks a lot for your understanding! ~R
Hi Rodolfo,
Thanks for the new snippet. I have tested it but I seem to have an issue. Please see the screenshot: https://imgur.com/a/3RCFf
I copied your code snipped and only changed the coupon code here:
‘description’ => bbloomer_get_sales_by_coupon(‘barmada’), //change coupon code here
I don’t see any reason for this to happen, have you had this issue?
Hey John, thanks for your comment! I think I made a mistake in the snippet (now corrected). Can you try changing:
with:
Let me know 🙂
Thanks Rodolfo,
Now working perfectly. Is there a way to show the sales for all the coupons codes?
Brilliant 🙂 Yes, this is possible – but unfortunately this is custom work and I cannot provide a complementary solution here on the blog. Thanks a lot for your understanding! ~R