UPDATE 11/5/2015: A lot has changed in WooCommerce since this article was published so I have updated a few of the functions below so they are up to date with the latest version 🙂 – if you’re looking for how to deal with order data, check out this article.
Perhaps you have to display some stats on the frontend and need the total sales number to use with some math. Here is how I found out how to tap into the data provided by WooCommerce’s admin reporting functionality.
Data Points:
- Total Sales
- Total Orders
- Total of All Shipping Costs
- Total of All Discounts Used
- Total of All Items Ordered
- Total Sales Per Item
WooCommerce comes with built-in reporting in the WordPress admin dashboard. The admin reporting code can be found in woocommerce/admin/woocommerce-admin-reports.php. If you have been selling on WooCommerce for awhile, you should be familiar with the dashboard reporting screen:
How to Get Total Sales Number
The following code will query your WordPress database and retreive the total sales of your site. Be sure to update the function name so it’s unique.
function my1234_get_total_sales() {
global $wpdb;
$order_totals = apply_filters( 'woocommerce_reports_sales_overview_order_totals', $wpdb->get_row( "
SELECT SUM(meta.meta_value) AS total_sales, COUNT(posts.ID) AS total_orders FROM {$wpdb->posts} AS posts
LEFT JOIN {$wpdb->postmeta} AS meta ON posts.ID = meta.post_id
WHERE meta.meta_key = '_order_total'
AND posts.post_type = 'shop_order'
AND posts.post_status IN ( '" . implode( "','", array( 'wc-completed', 'wc-processing', 'wc-on-hold' ) ) . "' )
" ) );
return absint( $order_totals->total_sales);
}
Once this is in a custom functionality plugin, or if it has to be, your theme’s functions.php file then you are ready to use it. Just echo it out within your template files or wherever you need it to show.
Get Total of All Shipping Costs
$shipping_total = apply_filters( 'woocommerce_reports_sales_overview_shipping_total', $wpdb->get_var( "
SELECT SUM(meta.meta_value) AS total_sales FROM {$wpdb->posts} AS posts
LEFT JOIN {$wpdb->postmeta} AS meta ON posts.ID = meta.post_id
LEFT JOIN {$wpdb->term_relationships} AS rel ON posts.ID=rel.object_ID
LEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )
LEFT JOIN {$wpdb->terms} AS term USING( term_id )
WHERE meta.meta_key = '_order_shipping'
AND posts.post_type = 'shop_order'
AND posts.post_status = 'publish'
AND tax.taxonomy = 'shop_order_status'
AND term.slug IN ('" . implode( "','", apply_filters( 'woocommerce_reports_order_statuses', array( 'completed', 'processing', 'on-hold' ) ) ) . "')
" ) );
Total of All Discounts Used
$discount_total = apply_filters( 'woocommerce_reports_sales_overview_discount_total', $wpdb->get_var( "
SELECT SUM(meta.meta_value) AS total_sales FROM {$wpdb->posts} AS posts
LEFT JOIN {$wpdb->postmeta} AS meta ON posts.ID = meta.post_id
LEFT JOIN {$wpdb->term_relationships} AS rel ON posts.ID=rel.object_ID
LEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )
LEFT JOIN {$wpdb->terms} AS term USING( term_id )
WHERE meta.meta_key IN ('_order_discount', '_cart_discount')
AND posts.post_type = 'shop_order'
AND posts.post_status = 'publish'
AND tax.taxonomy = 'shop_order_status'
AND term.slug IN ('" . implode( "','", apply_filters( 'woocommerce_reports_order_statuses', array( 'completed', 'processing', 'on-hold' ) ) ) . "')
" ) );
Total of All Items Ordered
$order_items = apply_filters( 'woocommerce_reports_sales_overview_order_items', absint( $wpdb->get_var( "
SELECT SUM( order_item_meta.meta_value )
FROM {$wpdb->prefix}woocommerce_order_items as order_items
LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id
LEFT JOIN {$wpdb->posts} AS posts ON order_items.order_id = posts.ID
LEFT JOIN {$wpdb->term_relationships} AS rel ON posts.ID = rel.object_ID
LEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )
LEFT JOIN {$wpdb->terms} AS term USING( term_id )
WHERE term.slug IN ('" . implode( "','", apply_filters( 'woocommerce_reports_order_statuses', array( 'completed', 'processing', 'on-hold' ) ) ) . "')
AND posts.post_status = 'publish'
AND tax.taxonomy = 'shop_order_status'
AND order_items.order_item_type = 'line_item'
AND order_item_meta.meta_key = '_qty'
" ) ) );
Total Sales Per Item
The following code will return an array of objects containing product ids and their corresponding total sales.
$order_items = apply_filters( 'woocommerce_reports_top_earners_order_items', $wpdb->get_results( "
SELECT order_item_meta_2.meta_value as product_id, SUM( order_item_meta.meta_value ) as line_total FROM {$wpdb->prefix}woocommerce_order_items as order_items
LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id
LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_item_meta_2 ON order_items.order_item_id = order_item_meta_2.order_item_id
LEFT JOIN {$wpdb->posts} AS posts ON order_items.order_id = posts.ID
LEFT JOIN {$wpdb->term_relationships} AS rel ON posts.ID = rel.object_ID
LEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )
LEFT JOIN {$wpdb->terms} AS term USING( term_id )
WHERE posts.post_type = 'shop_order'
AND posts.post_status = 'publish'
AND tax.taxonomy = 'shop_order_status'
AND term.slug IN ('" . implode( "','", apply_filters( 'woocommerce_reports_order_statuses', array( 'completed', 'processing', 'on-hold' ) ) ) . "')
AND order_items.order_item_type = 'line_item'
AND order_item_meta.meta_key = '_line_total'
AND order_item_meta_2.meta_key = '_product_id'
GROUP BY order_item_meta_2.meta_value
" ));
.. then using the array returned above you could loop through the results and do basic math to get your totals:
$totalFoodDonations = 0;
$foodProducts = array(48, 61, 103);
//Gather Total for Food Items
foreach ($order_items as $item) {
if (in_array($item->product_id, $foodProducts)) {
$totalFoodDonations = $item->line_total + $totalFoodDonations;
}
}
Know of a better way to do this? Please share! Use the comments below to describe how you grab the total sales, orders and additional reporting data from WooCommerce.
Please keep in mind I have not done any security testing on this code but I believe it’s secure enough.
