Commit 0c643cf68aa for woocommerce
commit 0c643cf68aa3f5e6b416116c99ed6f45caff1763
Author: Ján Mikláš <neosinner@gmail.com>
Date: Tue Aug 11 14:50:37 2026 +0200
Fix refunds reported as returning customers in Analytics > Orders (#67565)
* fix(analytics): report refunds with the customer type of the refunded order
Refunds are written to wc_order_stats with a NULL returning_customer, since
they should not count towards returning customer counts. The orders report
mapped that column to a customer type with `WHEN returning_customer = 0 THEN
'new' ELSE 'returning'`, so the NULL of every refund row fell through to
'returning' — a refund of a customer's first order was shown as "Returning"
next to the "New" order it refunded.
Resolve the customer type of refund rows from the refunded (parent) order by
joining the order stats table to itself, and apply the same fallback to the
customer_type filter so the filtered list agrees with the type shown per row.
The join is only added when the customer type is selected or filtered on, so
reports that ask for neither are not charged for it.
Joining the order stats table to itself makes bare column names in ORDER BY
ambiguous, so order by columns of that table are now qualified with the table
name. Extension supplied order by columns are left untouched.
Fixes #33410.
* test(analytics): cover the customer type reported for refunds
Add coverage for a refund of a guest customer's first order being reported as
new, a refund of a later order still being reported as returning, and the
customer type filter keeping refunds together with the order they refund.
* changelog: refund customer type in Analytics > Orders
* fix(analytics): qualify the parent_id predicate of the refunds filter
The refunds filter of the reports data store emitted a bare `parent_id`
predicate, which is ambiguous as soon as a report joins another table that
carries the same column — as the orders report now does when it resolves the
customer type of refund rows from the refunded order.
* fix(analytics): keep the customer type filter matching orders only
Resolving the customer type of refunds from the refunded order changes what
the orders report table displays, but the customer type filter of the orders
stats endpoint still matches on the refund's own NULL value. Applying the
fallback to the table's filter alone would list refunds that the summary and
chart above the table leave out of their totals, so the filter keeps matching
orders only and the join is added only when the column is selected.
* fix(analytics): resolve the refund customer type without joining the stats table
Joining a second copy of the order stats table put every one of its columns in
scope twice, so unqualified column names became ambiguous. That affected the
report's own ORDER BY and refunds predicates, and, more importantly, any
callback on the public woocommerce_analytics_clauses_*_orders_subquery filters
that adds an unqualified base table predicate — those would have started
failing with "Column ... is ambiguous" on stores running such extensions.
Read the parent order's returning_customer through a correlated subquery in the
select list instead. The query keeps a single order stats table in scope, so no
column becomes ambiguous, and the subquery is confined to the rows of the page
being returned rather than every row the report counts.
diff --git a/plugins/woocommerce/changelog/33410-fix-refund-customer-type-analytics b/plugins/woocommerce/changelog/33410-fix-refund-customer-type-analytics
new file mode 100644
index 00000000000..6da43c97c31
--- /dev/null
+++ b/plugins/woocommerce/changelog/33410-fix-refund-customer-type-analytics
@@ -0,0 +1,4 @@
+Significance: patch
+Type: fix
+
+Analytics > Orders: report refunds with the customer type of the order they refund, instead of always reporting them as returning customers.
diff --git a/plugins/woocommerce/src/Admin/API/Reports/Orders/DataStore.php b/plugins/woocommerce/src/Admin/API/Reports/Orders/DataStore.php
index 14bcd62f20e..7af8f0f9e73 100644
--- a/plugins/woocommerce/src/Admin/API/Reports/Orders/DataStore.php
+++ b/plugins/woocommerce/src/Admin/API/Reports/Orders/DataStore.php
@@ -99,6 +99,17 @@ class DataStore extends ReportsDataStore implements DataStoreInterface {
*/
protected function assign_report_columns() {
$table_name = self::get_db_table_name();
+
+ /*
+ * Refunds are stored with a NULL returning_customer, as they should not count towards
+ * returning customer counts, so fall back to the value of the refunded (parent) order.
+ *
+ * This is a subquery rather than a join, so that the query keeps a single order stats table
+ * in scope. Joining a second copy of it would make every one of its columns ambiguous for
+ * the unqualified column names that callbacks on the woocommerce_analytics_clauses_*_orders_subquery
+ * filters may use.
+ */
+ $returning_customer = "COALESCE( {$table_name}.returning_customer, ( SELECT customer_type_parent_stats.returning_customer FROM {$table_name} customer_type_parent_stats WHERE customer_type_parent_stats.order_id = {$table_name}.parent_id ) )";
// Avoid ambiguous columns in SQL query.
$this->report_columns = array(
'order_id' => "DISTINCT {$table_name}.order_id",
@@ -112,7 +123,7 @@ class DataStore extends ReportsDataStore implements DataStoreInterface {
'net_total' => "{$table_name}.net_total",
'total_sales' => "{$table_name}.total_sales",
'num_items_sold' => "{$table_name}.num_items_sold",
- 'customer_type' => "(CASE WHEN {$table_name}.returning_customer = 0 THEN 'new' ELSE 'returning' END) as customer_type",
+ 'customer_type' => "(CASE WHEN {$returning_customer} = 0 THEN 'new' ELSE 'returning' END) as customer_type",
);
}
diff --git a/plugins/woocommerce/tests/legacy/unit-tests/woocommerce-admin/reports/class-wc-tests-reports-orders.php b/plugins/woocommerce/tests/legacy/unit-tests/woocommerce-admin/reports/class-wc-tests-reports-orders.php
index ae6b4dc7109..4855a8e9351 100644
--- a/plugins/woocommerce/tests/legacy/unit-tests/woocommerce-admin/reports/class-wc-tests-reports-orders.php
+++ b/plugins/woocommerce/tests/legacy/unit-tests/woocommerce-admin/reports/class-wc-tests-reports-orders.php
@@ -328,4 +328,177 @@ class WC_Admin_Tests_Reports_Orders extends WC_Unit_Test_Case {
$this->assertEquals( 1, $data->total );
$this->assertEquals( $order_2->get_id(), $data->data[0]['order_id'] );
}
+
+ /**
+ * Creates a completed order for a guest customer, using a billing email that is not attached to
+ * any registered user.
+ *
+ * @param WC_Product $product Product to add to the order.
+ * @param string $email Billing email used to identify the guest customer.
+ * @return WC_Order
+ */
+ private function create_guest_order( $product, $email ) {
+ $order = WC_Helper_Order::create_order( 0, $product );
+ $order->set_billing_email( $email );
+ $order->set_total( 25 );
+ $order->set_status( OrderStatus::COMPLETED );
+ $order->save();
+
+ return $order;
+ }
+
+ /**
+ * Returns the customer type of every row in the report, keyed by order ID.
+ *
+ * @param WC_Order $order Order used to derive the reporting time frame.
+ * @param array $args Extra query arguments.
+ * @return array
+ */
+ private function get_customer_types_by_order_id( $order, $args = array() ) {
+ $data_store = new OrdersDataStore();
+ $data = $data_store->get_data(
+ array_merge(
+ array(
+ 'after' => gmdate( 'Y-m-d H:00:00', $order->get_date_created()->getOffsetTimestamp() ),
+ 'before' => gmdate( 'Y-m-d H:59:59', $order->get_date_created()->getOffsetTimestamp() ),
+ ),
+ $args
+ )
+ );
+
+ return wp_list_pluck( $data->data, 'customer_type', 'order_id' );
+ }
+
+ /**
+ * @testdox Should report a refund of a customer's first order as a new customer.
+ *
+ * Refunds are stored without a customer type of their own, so they should report the customer
+ * type of the order they refund instead of always being reported as returning.
+ *
+ * See: https://github.com/woocommerce/woocommerce/issues/33410.
+ */
+ public function test_refund_of_first_order_is_reported_as_new_customer() {
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $simple_product = new WC_Product_Simple();
+ $simple_product->set_name( 'Simple Product' );
+ $simple_product->set_regular_price( 25 );
+ $simple_product->save();
+
+ $order = $this->create_guest_order( $simple_product, 'guest-33410@example.org' );
+
+ $refund = wc_create_refund(
+ array(
+ 'amount' => 25,
+ 'order_id' => $order->get_id(),
+ )
+ );
+
+ WC_Helper_Queue::run_all_pending( 'wc-admin-data' );
+
+ $customer_types = $this->get_customer_types_by_order_id( $order );
+
+ $this->assertEquals( 'new', $customer_types[ $order->get_id() ], 'A guest customer\'s first order should be reported as new' );
+ $this->assertEquals( 'new', $customer_types[ $refund->get_id() ], 'A refund should be reported with the customer type of the refunded order' );
+ }
+
+ /**
+ * @testdox Should report a refund of a returning customer's order as a returning customer.
+ */
+ public function test_refund_of_later_order_is_reported_as_returning_customer() {
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $simple_product = new WC_Product_Simple();
+ $simple_product->set_name( 'Simple Product' );
+ $simple_product->set_regular_price( 25 );
+ $simple_product->save();
+
+ $first_order = $this->create_guest_order( $simple_product, 'guest-33410-returning@example.org' );
+ $second_order = $this->create_guest_order( $simple_product, 'guest-33410-returning@example.org' );
+
+ $refund = wc_create_refund(
+ array(
+ 'amount' => 25,
+ 'order_id' => $second_order->get_id(),
+ )
+ );
+
+ WC_Helper_Queue::run_all_pending( 'wc-admin-data' );
+
+ $customer_types = $this->get_customer_types_by_order_id( $first_order );
+
+ $this->assertEquals( 'new', $customer_types[ $first_order->get_id() ], 'The first order should be reported as new' );
+ $this->assertEquals( 'returning', $customer_types[ $second_order->get_id() ], 'The second order should be reported as returning' );
+ $this->assertEquals( 'returning', $customer_types[ $refund->get_id() ], 'A refund should be reported with the customer type of the refunded order' );
+ }
+
+ /**
+ * The customer type filter still matches orders only, so that the report table keeps agreeing
+ * with the totals from the orders stats endpoint, which excludes refunds from that filter too.
+ *
+ * @testdox Should match only orders, not their refunds, when filtering by customer type.
+ */
+ public function test_customer_type_filter_matches_orders_only() {
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $simple_product = new WC_Product_Simple();
+ $simple_product->set_name( 'Simple Product' );
+ $simple_product->set_regular_price( 25 );
+ $simple_product->save();
+
+ $order = $this->create_guest_order( $simple_product, 'guest-33410-filter@example.org' );
+
+ wc_create_refund(
+ array(
+ 'amount' => 25,
+ 'order_id' => $order->get_id(),
+ )
+ );
+
+ WC_Helper_Queue::run_all_pending( 'wc-admin-data' );
+
+ $new_customer_rows = $this->get_customer_types_by_order_id( $order, array( 'customer_type' => 'new' ) );
+
+ $this->assertEqualSets(
+ array( $order->get_id() ),
+ array_keys( $new_customer_rows ),
+ 'Filtering by new customers should return the order without its refund'
+ );
+
+ $returning_customer_rows = $this->get_customer_types_by_order_id( $order, array( 'customer_type' => 'returning' ) );
+
+ $this->assertEmpty( $returning_customer_rows, 'Filtering by returning customers should not return the order or its refund' );
+ }
+
+ /**
+ * @testdox Should report the customer type of refunds when filtering by refunds.
+ */
+ public function test_refunds_filter_returns_refunds_with_their_customer_type() {
+ WC_Helper_Reports::reset_stats_dbs();
+
+ $simple_product = new WC_Product_Simple();
+ $simple_product->set_name( 'Simple Product' );
+ $simple_product->set_regular_price( 25 );
+ $simple_product->save();
+
+ $order = $this->create_guest_order( $simple_product, 'guest-33410-refunds@example.org' );
+
+ $refund = wc_create_refund(
+ array(
+ 'amount' => 25,
+ 'order_id' => $order->get_id(),
+ )
+ );
+
+ WC_Helper_Queue::run_all_pending( 'wc-admin-data' );
+
+ $refund_rows = $this->get_customer_types_by_order_id( $order, array( 'refunds' => 'all' ) );
+
+ $this->assertEqualSets( array( $refund->get_id() ), array_keys( $refund_rows ), 'Filtering by refunds should return the refund only' );
+ $this->assertEquals( 'new', $refund_rows[ $refund->get_id() ], 'A refund should be reported with the customer type of the refunded order' );
+
+ $order_rows = $this->get_customer_types_by_order_id( $order, array( 'refunds' => 'none' ) );
+
+ $this->assertEqualSets( array( $order->get_id() ), array_keys( $order_rows ), 'Excluding refunds should return the order only' );
+ }
}