custom query to add billing phone field to admin customer list

Hello @wp_dummy,

I understand your need for such a feature. As mentioned in the GitHub report you attached, this is an enhancement request so it might take some time to implement natively.

Since this is a fairly complex development topic I’m going to leave it open for a bit to see if anyone is able to chime in to help you out.

I can also recommend the WooCommerce Developer Resources Portal for resources on developing for WooCommerce.

You can also visit the WooCommerce Facebook group or the #developers channel of the WooCommerce Community Slack. We’re lucky to have a great community of open-source developers for WooCommerce, and many of our developers hang out there, as well.

Cheers!

I got it working.

This is the snippet that I come up with if someone interested.

add_filter( 'woocommerce_admin_report_columns', 'customized_table_columns', 10, 3 );
function customized_table_columns( $report_columns, $context, $table_name ) {

    if ( $context !== 'customers' ) {
        return $report_columns;
    }
	global $wpdb;
	$statbl = $wpdb->prefix.'wc_order_stats';
	$orders_count         = 'SUM( CASE WHEN '.$statbl.'.parent_id = 0 THEN 1 ELSE 0 END )';
	$total_spend          = 'SUM( '.$statbl.'.total_sales )';
	$narr = array(
			'id'               => "{$table_name}.customer_id as id",
			'user_id'          => $table_name.'.user_id',
			'username'         => $table_name.'.username',
			'name'             => "CONCAT_WS( ' ', {$table_name}.first_name, {$table_name}.last_name ) as name", 
			'email'            => $table_name.'.email',
			'phone'			   => "m.meta_value as phone",
			'country'          => $table_name.'.country',
			'city'             => $table_name.'.city',
			'state'            => $table_name.'.state',
			'postcode'         => $table_name.'.postcode',
			'date_registered'  => $table_name.'.date_registered',
			'date_last_active' => 'IF( '.$table_name.'.date_last_active <= "0000-00-00 00:00:00", NULL, '.$table_name.'.date_last_active ) AS date_last_active',
			'date_last_order'  => "MAX( {$wpdb->prefix}wc_order_stats.date_created ) as date_last_order",
			'orders_count'     => "{$orders_count} as orders_count",
			'total_spend'      => "{$total_spend} as total_spend",
			'avg_order_value'  => "CASE WHEN {$orders_count} = 0 THEN NULL ELSE {$total_spend} / {$orders_count} END AS avg_order_value",
		);
  
    return $narr;
}

add_filter( 'woocommerce_analytics_clauses_join_customers_subquery', 'add_join_subquery' );
function add_join_subquery($clauses){
	global $wpdb;
	$tbl = $wpdb->usermeta;
	$ctbl = $wpdb->prefix.'wc_customer_lookup';
	$order_stats_table_name = $wpdb->prefix . 'wc_order_stats';
	$clauses[] = "JOIN {$tbl} m ON {$ctbl}.customer_id = m.user_id AND m.meta_key = 'billing_phone'";
	return $clauses;

}

Hi @wp_dummy

Glad to know you were able to sorted it out. I am going to mark this thread as resolved, if you have any new questions, please create a new ticket.

Cheers!

I tried to insert this code at functions.php but didn’t worked.

Anyone help me?

Hi @tiagojdi

Thanks for reaching out!

I understand that you used the code snippet above, however, it is not working as expected on your end, correct?

This is a bit of a complicated topic that would need some customization to address. Unfortunately, custom coding is not something we can assist with directly. However, I’ll keep this thread open for a bit to see if anyone from the community can lend a hand.

If you have any other questions related to development or custom coding, don’t hesitate to reach out to some of the great resources we have available for support. The WooCommerce community is filled with talented open-source developers, and many of them are active on the channels listed below:

• WooCommerce Developer Resources Portal
• WooCommerce Advanced Facebook group
• WooCommerce Developer Slack Channel
• Hire a WooCommerce Expert

Hope this helps!

@xue28 this is just the php part of the customization. You will still need the js (react) side of codes.

the source code of the js is like this:

import { addFilter } from '@wordpress/hooks';


const addTableColumn = reportTableData => {
    if ( 'customers' !== reportTableData.endpoint ) {
        return reportTableData;
    }

	reportTableData.headers.push({label:'Phone', key:'phone'})


    const newRows = reportTableData.rows.map( ( row, index ) => {
        const item = reportTableData.items.data[ index ];
		
		let pobj = {
			  display: item.phone,
               value: item.phone,
		}
		row.push(pobj)
        return row ;
    } );

    reportTableData.rows = newRows;
 
    return reportTableData;
};
 addFilter( 'woocommerce_admin_report_table', 'append-customer-phone', addTableColumn );

But in order this to works, you need to setup your js file correctly. This depends on how your file structure, which a rather big topic to discuss here. You can read from here https://developer.woocommerce.com/2020/02/20/extending-wc-admin-reports/ on how to create an extension for woocommerce.