WooCommerce Analytics data is stored separately from the order itself, which means it is possible for analytics records to remain even when the corresponding order data is missing or no longer available.
These orphaned analytics records can make it harder to troubleshoot discrepancies in WooCommerce Analytics, especially when investigating old, deleted, or migrated orders.
In this Woo Dev Patch, we’ll use a simple SQL query to identify analytics records that no longer have a corresponding WooCommerce order. The same approach can also be adapted to check different analytics lookup tables and investigate inconsistencies in WooCommerce’s analytics data.
Analytics Data Storage
- wp_wc_order_stats – stores basic order statistics such as the number of items, order totals, payment date and more.
- wp_wc_order_product_lookup – stores product level order details such as product IDs, quantities and revenue amounts.
- wp_wc_order_tax_lookup – stores order tax details.
- wp_wc_order_coupon_lookup – stores order coupon details.
Each WooCommerce Analytics table stores data related to an order, with the order_id column linking the analytics record back to the corresponding order in the WooCommerce order tables. WooCommerce uses the data in these tables to generate different Analytics reports.
Find Orphaned Records with SQL
The following queries find order IDs that have a record in wc_order_stats but no corresponding order record.
HPOS:
SELECT stats.order_id
FROM `wp_wc_order_stats` AS stats
LEFT JOIN `wp_wc_orders` as orders
ON orders.id = stats.order_id
WHERE orders.id IS NULL
Legacy:
SELECT stats.order_id
FROM `wp_wc_order_stats` AS stats
LEFT JOIN `wp_posts` as orders
ON orders.ID = stats.order_id
AND orders.post_type = 'shop_order'
WHERE orders.ID IS NULL
The same approach can be used to check the other WooCommerce Analytics tables. Replace wp_ with the table prefix used by your WordPress installation.
Count Orphaned Records
If you only need to determine how many orphaned records exist, you can use COUNT() instead.
HPOS:
SELECT COUNT( stats.order_id )
FROM `wp_wc_order_stats` AS stats
LEFT JOIN `wp_wc_orders` as orders
ON orders.id = stats.order_id
WHERE orders.id IS NULL
Legacy:
SELECT COUNT( stats.order_id )
FROM `wp_wc_order_stats` AS stats
LEFT JOIN `wp_posts` as orders
ON orders.ID = stats.order_id
AND orders.post_type = 'shop_order'
WHERE orders.ID IS NULL
Common Mistake
If you identify orders where analytics data is present but the corresponding order data is not, don’t assume that the analytics records can be safely deleted.
First check whether the order data was migrated, archived, exported, or intentionally cleaned up.
It is always advisable to test SQL queries, especially DELETE queries, on a staging or development site before running them in production. Always take an appropriate database backup before making destructive changes.
HPOS Compatibility
The order tables used by WooCommerce differ between legacy order storage and High-Performance Order Storage (HPOS). As a result, the table used to check whether a corresponding order exists needs to change depending on the order storage system being used.
The Analytics tables themselves remain separate from the order storage tables, and their order_id column is used to associate analytics data with the corresponding order.
Tested with:
- WordPress 7.1.0
- WooCommerce 11.1.0