Best WordPress and WooCommerce Black Friday & Cyber Monday 2024 Deals
November 13, 2024
Table of Contents
One of the sales reports providing useful information for store managers to make better decisions about supplying the products needed by customers is the sales report by product category. Reviewing this report shows how much the products of one or more specific categories have sold or in which region they experience more sales.
If store managers want to get WooCommerce product category sales report in WooCommerce, they can use one of the following two methods:
- Get product category sales reports programmatically.
- Get category sales reports with the WooCommerce report plugin.
In this post, we have provided a step-by-step guide about generating WooCommerce sales reports by product category with both methods.
What is a sales report by product category?
Sales report by category provides comprehensive information about the orders placed in an online store related to one or more product categories separately. Generally, this report lists the product categories in a table. It reports other information like the number of orders, the number of products, the total sales amount, and others related to those categories in different table columns.
By analyzing the data of this report, you will be able to check information such as sales amount, sales quantity, sales price, and total sales amount, making it easier to manage to supply products and improve customer loyalty by identifying top-selling products.
Now let’s see how you can get WooCommerce product category sales reports with and without a plugin.
Get product category sales reports programmatically
One of the easiest methods to generate sales reports by product category is running an SQL query in phpMyAdmin. The phpMyAdmin makes it easy to have access to the WordPress database and run queries to generate WooCommerce sales reports.
In the following, you can find a PHP code to get product category sales report programmatically in WooCommerce.
SELECT IF ( (woocommerce_order_itemmeta.meta_key = '_fee_amount'), 1, 0) AS fee, DATE_FORMAT(it_posts.post_date,'%m/%d/%Y') AS order_date, it_woocommerce_order_items.order_id AS order_id, it_woocommerce_order_items.order_item_name AS product_name, it_woocommerce_order_items.order_item_id AS order_item_id, woocommerce_order_itemmeta.meta_value AS woocommerce_order_itemmeta_meta_value, (it_woocommerce_order_itemmeta2.meta_value/it_woocommerce_order_itemmeta3.meta_value) AS sold_rate, IF ( (woocommerce_order_itemmeta.meta_key = '_fee_amount'), woocommerce_order_itemmeta.meta_value , (it_woocommerce_order_itemmeta4.meta_value/it_woocommerce_order_itemmeta3.meta_value))AS product_rate, IF ( (woocommerce_order_itemmeta.meta_key = '_fee_amount'), woocommerce_order_itemmeta.meta_value , (it_woocommerce_order_itemmeta4.meta_value))AS item_amount, (it_woocommerce_order_itemmeta2.meta_value) AS item_net_amount, (it_woocommerce_order_itemmeta4.meta_value - it_woocommerce_order_itemmeta2.meta_value) AS item_discount, it_woocommerce_order_itemmeta2.meta_value AS total_price, count(it_woocommerce_order_items.order_item_id) AS product_quentity, woocommerce_order_itemmeta.meta_value AS product_id, woocommerce_order_itemmeta_var.meta_value AS variation_id ,it_woocommerce_order_itemmeta3.meta_value AS 'product_quantity' ,it_posts.post_status AS post_status ,it_posts.post_status AS order_status FROM gbQDaFZ_12_woocommerce_order_items as it_woocommerce_order_items LEFT JOIN gbQDaFZ_12_posts as it_posts ON it_posts.ID=it_woocommerce_order_items.order_id LEFT JOIN gbQDaFZ_12_woocommerce_order_itemmeta as woocommerce_order_itemmeta ON woocommerce_order_itemmeta.order_item_id = it_woocommerce_order_items.order_item_id LEFT JOIN gbQDaFZ_12_woocommerce_order_itemmeta as woocommerce_order_itemmeta_var ON woocommerce_order_itemmeta_var.order_item_id = it_woocommerce_order_items.order_item_id LEFT JOIN gbQDaFZ_12_woocommerce_order_itemmeta as it_woocommerce_order_itemmeta2 ON it_woocommerce_order_itemmeta2.order_item_id = it_woocommerce_order_items.order_item_id LEFT JOIN gbQDaFZ_12_woocommerce_order_itemmeta as it_woocommerce_order_itemmeta3 ON it_woocommerce_order_itemmeta3.order_item_id = it_woocommerce_order_items.order_item_id LEFT JOIN gbQDaFZ_12_woocommerce_order_itemmeta as it_woocommerce_order_itemmeta4 ON it_woocommerce_order_itemmeta4.order_item_id = it_woocommerce_order_items.order_item_id AND it_woocommerce_order_itemmeta4.meta_key='_line_subtotal' LEFT JOIN gbQDaFZ_12_term_relationships as it_term_relationships ON it_term_relationships.object_id = woocommerce_order_itemmeta.meta_value LEFT JOIN gbQDaFZ_12_term_taxonomy as term_taxonomy ON term_taxonomy.term_taxonomy_id = it_term_relationships.term_taxonomy_id Where ((woocommerce_order_itemmeta.meta_key = '_product_id' AND it_woocommerce_order_itemmeta3.meta_key='_qty') OR (woocommerce_order_itemmeta.meta_key = '_fee_amount')) AND woocommerce_order_itemmeta_var.meta_key = '_variation_id' AND it_woocommerce_order_itemmeta2.meta_key='_line_total' AND DATE(it_posts.post_date) BETWEEN STR_TO_DATE('2023-02-01', '%Y-%m-%d') and STR_TO_DATE('2024-02-14', '%Y-%m-%d') AND term_taxonomy.taxonomy LIKE('product_cat') AND term_taxonomy.term_id IN (33,25) AND it_posts.post_status IN ('wc-processing','wc-on-hold','wc-completed') AND it_posts.post_parent = '' GROUP BY it_woocommerce_order_items.order_item_id ORDER BY order_id DESCTo customize this code and instruct it to show the information you need related to specific categories, you can easily replace the Category IDs in the line illustrated below:
Then, after running the query, you will see a report listing the following information in different columns of the table:
- Order Date
- Order ID
- Product Name
- Order item ID
- Sold Rate
- Product Rate
- Item Amount
- Item Net Amount
- Item Discount
- Total Price
- Product Quantity
How to run SQL code in PHPmyadmin?
You need to run this SQL query with phpMyAdmin by following the below instructions:
- Log in to phpMyAdmin to access the database.
- Click on the database name of your WordPress website in the left column.
- Select the SQL tab on the top of the screen.
- Look for the database name again above the text field, and confirm this is the correct database you intend to run a query on.
- Write or paste the query in the text field.
- Press the GO button.
- Some queries will require a second confirmation to run.
Caution: If running SQL queries, you may change or add data to your database. So, it makes sense to back up your WordPress database before running the code.
If you have no experience in working with PHPMyAdmin, please ask an expert to do this task and run the SQL query or use the WooCommerce report plugin instead. This plugin helps you to generate any type of sales report without coding.
Use the WooCommerce report plugin to get sales report by product category
The WooCommerce report plugin is a great tool for generating more than 50 WooCommerce sales reports quickly and easily. It has some default options for creating different sales report with one click and provides a lot of useful features for filtering, customizing and even exporting the generated reports.
We prepared a step-by-step guide to show you how easy is to get a WooCommerce sale report by product category with this plugin.
Step 1: Install WooCommerce report plugin
Like other WooCommerce plugins, you need to download and install the .Zip file of the WooCommerce report plugin on your WordPress website.
You can find a comprehensive guide about installation and activation of the plugin, here.
WooCommerce Report Plugin
The easy way to get WooCommerce product category sales report
After activation, the iT Woo Report menu will be added to your WordPress dashboard which leads you to the main page of the WooCommerce report plugin.
Step 2: Get WooCommerce product category sales report
On the main page of the plugin, you have access to the preset reports in the left panel. To get the category sales report WooCommerce, you need to navigate to the below address:
Orders > All orders
On the “All Orders” page, there is a Search icon designed to let you open the Filter Form and use it for filtering the report based on your needs.
To get a purchase report by product category, first, you need to set a date range in the Date From and Date To fields.
Then, locate the Category field and select one or more categories from the list to receive the reports related to them:
For example, to generate WooCommerce product category sales report for Accessories in the last year, we set the fields in the Filter Form as below:
- Date From: 2023-02-01
- Date To: 2024-02-01
- Category: Accessories
By pressing the Search button, the plugin will create the report like the below:
In the category sales report WooCommerce, you can find all the information you need in the separate columns of the table, including:
- Order ID, Order Status, Order Date
- Name, Last Name, Email, Customer, Phone No.
- Sate, Country
- Payment Method
- Category, Products, Variation
- Ordered Quantity
- Product Amount, Product Discount, Net Amount, Shipping Amount
- Invoice Action
- SKU
- Rate
- Coupon Code
You can also review a Summary report displaying the Total Order Count, Total Product Count, Total Quantity, Product Amount, Discount Amount, and Net Amount of the Category you have filtered in the Search form.
Extra features of sales report by product category
The wooCommerce report plugin comes with extra features enabling you to search, customize, or export the report very fast and smoothly.
Continue reading to get familiar with these amazing features in the WooCommerce sale report by product category.
Customize the table columns of the purchase report by product category
One of the advantages of the WooCommerce report plugin is the ability to customize the columns of the table report. After generating a category sales report WooCommerce, you can open the dropdown list of Select Columns designed above the table and mark the columns you need.
For example, we marked some columns as illustrated in the above picture and the result looks like below:
Export sales report by product category
If you want to export WooCommerce sale report by product category, you can simply click on the Download icon on top of the table and choose one of the file formats including:
- Excel
- CSV
It is also possible to have a hard copy of the report by selecting the Print option in the list.
Search in the purchase report by product category
To help store managers review their preferred information in the WooCommerce product category sales report, we designed a Search box on the top of the table. You can search any value like User name, Email, Order ID, Country, etc. to see the related report in the table.
For example, if you want to see which categories have been ordered by Horton, type Horton in the Search box:
So, the Plugin will filter the report and show the related information in the table as illustrated above.
WooCommerce Report Plugin
The easy way to get WooCommerce product category sales report
Why do store managers need sales reports by category?
Store managers generate sales reports by product category to:
- Analyze which product categories had the most sales in a date range.
- Have an insight into the most favorable categories in their online store.
- Plan marketing strategies and promotions for the categories with less customer attention to boost their sales.
- Predict which product categories need to be supplied more in the future.
Conclusion
Analyzing the WooCommerce product category sales report allows you to better understand the products that customers buy the most and the reason for this behavior. By doing this, it becomes much easier to identify your most profitable products, and providing these products to your loyal customers can be the key to increasing your overall profitability. To check the information related to the WooCommerce category sales report, you can use the WooCommerce report plugin, which provides you with amazing features to prepare different types of sales reports with one click.
