WooCommerce: Calculate Sales by Coupon Code

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
 * @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 ) );
    }
}

Where to add custom code?

You should place PHP snippets at the bottom of your child theme functions.php file and CSS at the bottom of its style.css file. Make sure you know what you are doing when editing such files - if you need more guidance, please take a look at my guide "Should I Add Custom Code Via WP Editor, FTP or Code Snippets?" and my video tutorial "Where to Place WooCommerce Customization?"

Does this snippet (still) work?

Please let me know in the comments if everything went as expected. I would be happy to revise the snippet if you report otherwise (please provide screenshots). I have tested this code with Storefront theme, the WooCommerce version listed above and a WordPress-friendly hosting.

If you think this code saved you time & money, feel free to join 17,000+ WooCommerce Weekly subscribers for blog post updates and 250+ Business Bloomer supporters for 365 days of WooCommerce benefits. Thank you in advance!

Need Help with WooCommerce?

Check out these free video tutorials. You can learn how to customize WooCommerce without unnecessary plugins, how to properly configure the WooCommerce plugin settings and even how to master WooCommerce troubleshooting in case of a bug!

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.

22 thoughts on “WooCommerce: Calculate Sales by Coupon Code

  1. 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!

    1. 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

  2. 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 🙂

    1. 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!

      1. 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

        1. Story of my life! Thank you

  3. 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

    1. Nice!

  4. Thanks for the snippets. This saves me a lot of time.

    1. Great!

  5. 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!

    1. 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!

  6. Actually, it didn’t work for me. Latest version.

    1. Hi Stef! What didn’t work exactly?

      1. 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.

  7. How to display 2 coupons?

    1. 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

  8. 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?

    1. Hey John, thanks for your comment! I think I made a mistake in the snippet (now corrected). Can you try changing:

      echo wc_price($total);
      

      with:

      return 'Total sales for coupon "' . $coupon_id . '": ' . wc_price($total);
      

      Let me know 🙂

    2. Thanks Rodolfo,

      Now working perfectly. Is there a way to show the sales for all the coupons codes?

      1. 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? Support? Leave your Comment Now!
_____

If you are writing code, please wrap it between shortcodes: [php]code_here[/php]. Failure to complying with this (as well as going off topic, not writing in English, etc.) will result in comment deletion. 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 BloomerArmada to get blog comment reply priority, ask me 1-to-1 WooCommerce questions and enjoy many more perks. Thank you :)

Your email address will not be published. Required fields are marked *