In a recent Business Bloomer Club Slack thread, a community member shared a custom solution to generate a product sales report in WooCommerce. This solution allows administrators to export the total quantity of each product sold over a specified period.
The export is in CSV format, which is easily accessible for analysis or record-keeping. This functionality is particularly valuable for store owners who need detailed insights into their product sales performance. By leveraging this custom solution, you can streamline your reporting process and stay on top of product performance.
This post includes a legacy version and also an updated snippet for WooCommerce with High-Performance Order Storage (HPOS) compatibility, which improves the handling of orders and metadata. Keep reading to explore the full solution and how it works in both the standard and HPOS versions.
Custom Sales Report Code (Pre-HPOS)
The code can be placed in the functions.php file of your child theme or packaged into a small plugin. Here’s a breakdown of how it works:
/* Function that handles the sales export */
function export_product_sales_report() {
// Check user permissions
if (!current_user_can('manage_woocommerce')) {
return;
}
// Set the date range
$start_date = isset($_GET['start_date']) ? sanitize_text_field($_GET['start_date']) : '2024-01-01';
$end_date = isset($_GET['end_date']) ? sanitize_text_field($_GET['end_date']) : '2024-12-31';
// Prepare the file for download
header('Content-Type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment; filename="product-sales-report.csv"');
// Open the output stream
$output = fopen('php://output', 'w');
// Add BOM for UTF-8
fwrite($output, "\xEF\xBB\xBF");
// CSV Header
fputcsv($output, ['Product Name', 'SKU', 'Total Sold Quantity']);
// Query for WooCommerce order items
global $wpdb;
$query = $wpdb->prepare(
"
SELECT
p.post_title AS product_name,
pm.meta_value AS sku,
SUM(oim_qty.meta_value) AS total_quantity
FROM
{$wpdb->prefix}woocommerce_order_items AS oi
LEFT JOIN
{$wpdb->prefix}woocommerce_order_itemmeta AS oim_product
ON oi.order_item_id = oim_product.order_item_id
AND oim_product.meta_key = '_product_id'
LEFT JOIN
{$wpdb->prefix}woocommerce_order_itemmeta AS oim_qty
ON oi.order_item_id = oim_qty.order_item_id
AND oim_qty.meta_key = '_qty'
LEFT JOIN
{$wpdb->prefix}posts AS p
ON oim_product.meta_value = p.ID
LEFT JOIN
{$wpdb->prefix}postmeta AS pm
ON p.ID = pm.post_id
AND pm.meta_key = '_sku'
LEFT JOIN
{$wpdb->prefix}posts AS o
ON oi.order_id = o.ID
WHERE
o.post_date BETWEEN %s AND %s
AND o.post_type = 'shop_order'
AND o.post_status IN ('wc-completed', 'wc-processing')
GROUP BY
oim_product.meta_value
ORDER BY
total_quantity DESC;
",
$start_date, $end_date
);
$results = $wpdb->get_results($query);
// Output data rows
if ($results) {
foreach ($results as $row) {
fputcsv($output, [$row->product_name, $row->sku, $row->total_quantity]);
}
}
fclose($output);
exit;
}
// Add the action to trigger the export
add_action('admin_post_export_product_sales_report', 'export_product_sales_report');
The code adds a submenu to WooCommerce where the admin can select the start and end dates for the report export.
Custom Sales Report Code (HPOS)
With the introduction of the High-Performance Order Storage (HPOS) system, which optimizes WooCommerce’s handling of order data, the database queries related to order items and metadata need to be updated to work with the new structure. Here’s an updated version of the code that supports HPOS:
/* Function that handles the sales export (HPOS version) */
function export_product_sales_report_hpos() {
// Check user permissions
if (!current_user_can('manage_woocommerce')) {
return;
}
// Set the date range
$start_date = isset($_GET['start_date']) ? sanitize_text_field($_GET['start_date']) : '2024-01-01';
$end_date = isset($_GET['end_date']) ? sanitize_text_field($_GET['end_date']) : '2024-12-31';
// Prepare the file for download
header('Content-Type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment; filename="product-sales-report.csv"');
// Open the output stream
$output = fopen('php://output', 'w');
// Add BOM for UTF-8
fwrite($output, "\xEF\xBB\xBF");
// CSV Header
fputcsv($output, ['Product Name', 'SKU', 'Total Sold Quantity']);
// Query for WooCommerce order items (HPOS version)
global $wpdb;
$query = $wpdb->prepare(
"
SELECT
p.post_title AS product_name,
pm.meta_value AS sku,
SUM(oim_qty.meta_value) AS total_quantity
FROM
{$wpdb->prefix}woocommerce_order_items AS oi
LEFT JOIN
{$wpdb->prefix}woocommerce_order_itemmeta AS oim_product
ON oi.order_item_id = oim_product.order_item_id
AND oim_product.meta_key = '_product_id'
LEFT JOIN
{$wpdb->prefix}woocommerce_order_itemmeta AS oim_qty
ON oi.order_item_id = oim_qty.order_item_id
AND oim_qty.meta_key = '_qty'
LEFT JOIN
{$wpdb->prefix}posts AS p
ON oim_product.meta_value = p.ID
LEFT JOIN
{$wpdb->prefix}postmeta AS pm
ON p.ID = pm.post_id
AND pm.meta_key = '_sku'
LEFT JOIN
{$wpdb->prefix}posts AS o
ON oi.order_id = o.ID
WHERE
o.post_date BETWEEN %s AND %s
AND o.post_type = 'shop_order'
AND o.post_status IN ('wc-completed', 'wc-processing')
GROUP BY
oim_product.meta_value
ORDER BY
total_quantity DESC;
",
$start_date, $end_date
);
$results = $wpdb->get_results($query);
// Output data rows
if ($results) {
foreach ($results as $row) {
fputcsv($output, [$row->product_name, $row->sku, $row->total_quantity]);
}
}
fclose($output);
exit;
}
// Add the action to trigger the export (HPOS version)
add_action('admin_post_export_product_sales_report_hpos', 'export_product_sales_report_hpos');
This updated version ensures compatibility with WooCommerce’s new database structure and order handling under HPOS. The process of exporting product sales remains the same, but the queries are designed to work seamlessly with the optimized storage system.
Conclusion
This custom solution for generating a product sales report in WooCommerce is a useful tool for administrators who need insights into sales data over specific periods. The HPOS-compatible version of the code ensures that this functionality works smoothly with WooCommerce’s latest performance improvements.
