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!
WooCommerce: total sales generated by each coupon code
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
* @community https://businessbloomer.com/club/
*/
// -------------------------
// 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 ) );
}
}
This code still works, unless you report otherwise. To exclude conflicts, temporarily switch to the Storefront theme, disable all plugins except WooCommerce, and test the snippet again: WooCommerce troubleshooting 101
Related content
WooCommerce: Add Second Description @ Product Category Pages In terms of SEO, if you’re trying to rank your product category pages, you really need to make the most of the default WooCommerce product category “description” and “thumbnail”. Most themes, if compatible with WooCommerce, will show this content right below the product category name and above products. Nothing new so far. But what if […]
WooCommerce: How to Add a Custom Checkout Field Let’s imagine you want to add a custom checkout field (and not an additional billing or shipping field) on the WooCommerce Checkout page. For example, it might be a customer licence number – this has got nothing to do with billing and nothing to do with shipping. Ideally, this custom field could show above the […]
WooCommerce: Get Order Data (total, items, etc) From $order Object As a WooCommerce development freelancer, every day I repeat many coding operations that make me waste time. One of them is: “How to get ____ if I have the $order variable/object?“. For example, “How can I get the order total“? Or “How can I get the order items“? Or maybe the order dates, customer ID, […]
WooCommerce: Add Content to the Thank You Page A client of mine wanted to add some text to the thank you page, the page that customers see after they place an order via the default WooCommerce Checkout page. In this case scenario, they wanted to add a special coupon discount in order to entice buyers to go back to the website and buy […]
WooCommerce: Allow Users to Edit Processing Orders How can WooCommerce customers edit an order they just placed and paid for? I swear I looked on search engine results and other places before coming to the conclusion I needed to code this myself. For example, a user might want to change the delivery date (if you provide this on the checkout page). Or […]
Rodolfo Melogli
Business Bloomer Founder
Author, WooCommerce expert and WordCamp speaker, Rodolfo has worked as an independent WooCommerce freelancer since 2011. His goal is to help entrepreneurs and developers overcome their WooCommerce nightmares. Rodolfo loves travelling, chasing tennis & soccer balls and, of course, wood fired oven pizza. Follow @rmelogli
22 thoughts on “WooCommerce: Calculate Sales by Coupon Code”
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
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
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!
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
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
Questions? Feedback? Customization? Leave your comment now! _____
If you are writing code, please wrap it like so: [php]code_here[/php]. Failure to complying with this, as well as going off topic or not using the English language will result in comment disapproval. You should expect a reply in about 2 weeks - this is a popular blog but I need to get paid work done first. Please consider joining the Business Bloomer Club to get quick WooCommerce support. Thank you!
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