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 tutoraspire.com FREE
* @author Tutor Aspire
* @compatible WooCommerce 5
* @donate $9 https://www.tutoraspire.com
*/
// -------------------------
// 1. Create function that calculates sales based on coupon code
function tutoraspire_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', 'tutoraspire_admin_shop_coupon_sales_column', 9999 );
function tutoraspire_admin_shop_coupon_sales_column( $columns ) {
$columns['totsales'] = 'Total Sales';
return $columns;
}
add_action( 'manage_shop_coupon_posts_custom_column', 'tutoraspire_admin_shop_coupon_sales_column_content', 9999, 2 );
function tutoraspire_admin_shop_coupon_sales_column_content( $column, $coupon_id ) {
if ( $column == 'totsales' ) {
echo tutoraspire_get_sales_by_coupon( get_the_title( $coupon_id ) );
}
}
