get_total() + (float) $item->get_total_tax();
}
function acreports_get_order_subtotal_gross( $order ) {
$cart_tax = ( method_exists( $order, 'get_cart_tax' ) ? (float) $order->get_cart_tax() : 0 );
return (float) $order->get_subtotal() + $cart_tax;
}
function acreports_get_order_discount_gross( $order ) {
$discount_tax = ( method_exists( $order, 'get_discount_tax' ) ? (float) $order->get_discount_tax() : 0 );
return (float) $order->get_discount_total() + $discount_tax;
}
function acreports_get_order_shipping_gross( $order ) {
$shipping_tax = ( method_exists( $order, 'get_shipping_tax' ) ? (float) $order->get_shipping_tax() : 0 );
return (float) $order->get_shipping_total() + $shipping_tax;
}
function acreports_sort_stats_by_quantity_total( $first_stat, $second_stat ) {
$first_quantity = ( isset( $first_stat['quantity'] ) ? (float) $first_stat['quantity'] : 0 );
$second_quantity = ( isset( $second_stat['quantity'] ) ? (float) $second_stat['quantity'] : 0 );
if ( $first_quantity === $second_quantity ) {
$first_total = ( isset( $first_stat['total'] ) ? (float) $first_stat['total'] : 0 );
$second_total = ( isset( $second_stat['total'] ) ? (float) $second_stat['total'] : 0 );
if ( $first_total === $second_total ) {
return 0;
}
return ( $first_total < $second_total ? 1 : -1 );
}
return ( $first_quantity < $second_quantity ? 1 : -1 );
}
function acreports_get_report_order_statuses( $status ) {
if ( 'all' === $status || empty( $status ) ) {
$statuses = array_keys( wc_get_order_statuses() );
return array_values( array_diff( $statuses, array('trash', 'wc-trash') ) );
}
return $status;
}
function acreports_find_user_by_customer_identifier( $identifier ) {
global $wpdb;
$identifier = trim( wp_strip_all_tags( (string) $identifier ) );
if ( '' === $identifier ) {
return false;
}
if ( is_numeric( $identifier ) ) {
$user = get_user_by( 'id', absint( $identifier ) );
if ( $user ) {
return $user;
}
}
$user = get_user_by( 'login', $identifier );
if ( $user ) {
return $user;
}
if ( is_email( $identifier ) ) {
$user = get_user_by( 'email', $identifier );
if ( $user ) {
return $user;
}
}
$users = get_users( array(
'number' => 1,
'search' => '*' . esc_attr( $identifier ) . '*',
'search_columns' => array(
'user_login',
'user_email',
'display_name',
'user_nicename'
),
) );
if ( !empty( $users ) ) {
return $users[0];
}
$like = '%' . $wpdb->esc_like( $identifier ) . '%';
if ( acreports_customer_lookup_table_exists() ) {
$user_id = $wpdb->get_var( $wpdb->prepare(
"SELECT u.ID\n FROM {$wpdb->users} u\n INNER JOIN {$wpdb->prefix}wc_customer_lookup cl ON cl.user_id = u.ID\n WHERE cl.first_name LIKE %s\n OR cl.last_name LIKE %s\n OR CONCAT_WS(' ', cl.first_name, cl.last_name) LIKE %s\n OR cl.email LIKE %s\n ORDER BY cl.orders_count DESC, cl.total_spent DESC, u.ID DESC\n LIMIT 1",
$like,
$like,
$like,
$like
) );
if ( $user_id ) {
return get_user_by( 'id', $user_id );
}
}
$user_id = $wpdb->get_var( $wpdb->prepare(
"SELECT DISTINCT u.ID\n FROM {$wpdb->users} u\n LEFT JOIN {$wpdb->usermeta} first_name ON first_name.user_id = u.ID AND first_name.meta_key = 'first_name'\n LEFT JOIN {$wpdb->usermeta} last_name ON last_name.user_id = u.ID AND last_name.meta_key = 'last_name'\n LEFT JOIN {$wpdb->usermeta} billing_first_name ON billing_first_name.user_id = u.ID AND billing_first_name.meta_key = 'billing_first_name'\n LEFT JOIN {$wpdb->usermeta} billing_last_name ON billing_last_name.user_id = u.ID AND billing_last_name.meta_key = 'billing_last_name'\n WHERE first_name.meta_value LIKE %s\n OR last_name.meta_value LIKE %s\n OR CONCAT_WS(' ', first_name.meta_value, last_name.meta_value) LIKE %s\n OR billing_first_name.meta_value LIKE %s\n OR billing_last_name.meta_value LIKE %s\n OR CONCAT_WS(' ', billing_first_name.meta_value, billing_last_name.meta_value) LIKE %s\n ORDER BY u.ID DESC\n LIMIT 1",
$like,
$like,
$like,
$like,
$like,
$like
) );
return ( $user_id ? get_user_by( 'id', $user_id ) : false );
}
/*
* Customer identity
*
* A customer is either a registered WordPress user, or a guest who checked out
* without an account and is identified only by the billing email on their orders.
* Every report query is built from a "customer context" so both are treated the same.
*/
function acreports_customer_lookup_table_exists() {
static $exists = null;
if ( null !== $exists ) {
return $exists;
}
global $wpdb;
$lookup_table = $wpdb->prefix . 'wc_customer_lookup';
$exists = $lookup_table === $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $lookup_table ) );
return $exists;
}
/**
* Whether guest orders placed with a registered customer's email should be
* included in that customer's report. On by default so no order is left out of
* every report, which is how WooCommerce Analytics attributes guest orders too.
*/
function acreports_should_merge_guest_orders() {
$merge = 'no' !== acreports_get_option( 'merge_guest_orders', 'yes' );
return (bool) apply_filters( 'acreports_merge_guest_orders', $merge );
}
function acreports_get_empty_customer_context() {
return array(
'found' => false,
'user' => false,
'user_id' => 0,
'email' => '',
'is_guest' => false,
'display_name' => '',
'first_name' => '',
'username' => '',
'identifier' => '',
'order' => null,
);
}
/**
* Resolve a guest customer from a billing email address.
*
* Returns false when the address belongs to a registered account, or when no
* guest order exists for it. Requiring a real order also means the email based
* actions (coupons, emails) can never target an arbitrary address.
*/
function acreports_get_guest_customer_by_email( $email ) {
$email = sanitize_email( (string) $email );
if ( !$email || !is_email( $email ) ) {
return false;
}
// A registered account owns this address, so it is not a guest customer.
if ( get_user_by( 'email', $email ) ) {
return false;
}
$orders = wc_get_orders( array(
'limit' => 1,
'orderby' => 'date',
'order' => 'DESC',
'status' => acreports_get_report_order_statuses( 'all' ),
'customer' => array(array(0, $email)),
) );
if ( empty( $orders ) ) {
return false;
}
$order = $orders[0];
$first_name = $order->get_billing_first_name();
$last_name = $order->get_billing_last_name();
$display_name = trim( $first_name . ' ' . $last_name );
return array(
'found' => true,
'user' => false,
'user_id' => 0,
'email' => $email,
'is_guest' => true,
'display_name' => ( '' !== $display_name ? $display_name : $email ),
'first_name' => $first_name,
'username' => '',
'identifier' => $email,
'order' => $order,
);
}
/**
* Find a guest customer by name or partial email, using the WooCommerce
* customer lookup table where guests are the rows without a user_id.
*/
function acreports_find_guest_customer_by_identifier( $identifier ) {
global $wpdb;
$identifier = trim( wp_strip_all_tags( (string) $identifier ) );
if ( '' === $identifier || !acreports_customer_lookup_table_exists() ) {
return false;
}
$like = '%' . $wpdb->esc_like( $identifier ) . '%';
$email = $wpdb->get_var( $wpdb->prepare(
"SELECT cl.email\n FROM {$wpdb->prefix}wc_customer_lookup cl\n WHERE cl.user_id IS NULL\n AND cl.email <> ''\n AND (\n cl.email LIKE %s\n OR cl.first_name LIKE %s\n OR cl.last_name LIKE %s\n OR CONCAT_WS(' ', cl.first_name, cl.last_name) LIKE %s\n )\n ORDER BY cl.date_last_active DESC\n LIMIT 1",
$like,
$like,
$like,
$like
) );
return ( $email ? acreports_get_guest_customer_by_email( $email ) : false );
}
function acreports_build_user_customer_context( $user ) {
$first_name = trim( (string) $user->first_name );
if ( '' === $first_name ) {
$first_name = trim( (string) get_user_meta( $user->ID, 'billing_first_name', true ) );
}
return array(
'found' => true,
'user' => $user,
'user_id' => (int) $user->ID,
'email' => sanitize_email( $user->user_email ),
'is_guest' => false,
'display_name' => ( $user->display_name ? $user->display_name : $user->user_login ),
'first_name' => $first_name,
'username' => $user->user_login,
'identifier' => $user->user_login,
'order' => null,
);
}
/**
* Resolve whatever the request gave us (user ID, guest email, or free text)
* into a single customer context.
*/
function acreports_resolve_customer_context( $args = array() ) {
$args = wp_parse_args( $args, array(
'user_id' => 0,
'customer_email' => '',
'identifier' => '',
) );
$context = acreports_get_empty_customer_context();
$identifier = trim( wp_strip_all_tags( (string) $args['identifier'] ) );
$customer_email = sanitize_email( (string) $args['customer_email'] );
$user_id = absint( $args['user_id'] );
// 1. An explicit user ID from the customers list or the users table link.
if ( $user_id ) {
$user = get_user_by( 'id', $user_id );
if ( $user ) {
return acreports_build_user_customer_context( $user );
}
}
// 2. An explicit email, which is how guests are addressed everywhere.
if ( $customer_email && is_email( $customer_email ) ) {
$guest = acreports_get_guest_customer_by_email( $customer_email );
if ( $guest ) {
return $guest;
}
$user = get_user_by( 'email', $customer_email );
if ( $user ) {
return acreports_build_user_customer_context( $user );
}
}
// 3. Free text typed into the search field. Registered accounts win, so
// existing lookups keep behaving exactly as they did before.
if ( '' !== $identifier ) {
$user = acreports_find_user_by_customer_identifier( $identifier );
if ( $user ) {
return acreports_build_user_customer_context( $user );
}
$guest = acreports_get_guest_customer_by_email( $identifier );
if ( !$guest ) {
$guest = acreports_find_guest_customer_by_identifier( $identifier );
}
if ( $guest ) {
return $guest;
}
}
$context['identifier'] = ( '' !== $identifier ? $identifier : $customer_email );
return $context;
}
/**
* Build the wc_get_orders() arguments that scope a query to one customer.
*
* Guests are matched on billing email with no customer account attached, so a
* guest report can never pick up a registered customer's orders. Registered
* customers optionally also match on their email, which pulls in orders they
* placed as a guest before creating the account.
*/
function acreports_get_customer_order_query_args( $context ) {
if ( empty( $context['found'] ) ) {
return array();
}
if ( !empty( $context['is_guest'] ) ) {
return ( empty( $context['email'] ) ? array() : array(
'customer' => array(array(0, $context['email'])),
) );
}
if ( empty( $context['user_id'] ) ) {
return array();
}
$customer = array((int) $context['user_id']);
if ( acreports_should_merge_guest_orders() && !empty( $context['email'] ) && is_email( $context['email'] ) ) {
$customer[] = $context['email'];
}
return array(
'customer' => $customer,
);
}
/**
* Billing and shipping details for the report header. Guests have no profile
* fields, so their details come from their most recent order.
*/
function acreports_get_customer_profile_details( $context ) {
$address_keys = array(
'company',
'address_1',
'address_2',
'city',
'postcode',
'country',
'state'
);
$details = array(
'billing' => array(),
'shipping' => array(),
'phone' => '',
);
if ( !empty( $context['is_guest'] ) ) {
$order = ( !empty( $context['order'] ) ? $context['order'] : null );
if ( !$order ) {
return $details;
}
foreach ( $address_keys as $key ) {
$billing_getter = 'get_billing_' . $key;
$shipping_getter = 'get_shipping_' . $key;
$details['billing'][$key] = ( method_exists( $order, $billing_getter ) ? $order->{$billing_getter}() : '' );
$details['shipping'][$key] = ( method_exists( $order, $shipping_getter ) ? $order->{$shipping_getter}() : '' );
}
$details['phone'] = $order->get_billing_phone();
return $details;
}
if ( empty( $context['user_id'] ) ) {
return $details;
}
foreach ( $address_keys as $key ) {
$details['billing'][$key] = get_user_meta( $context['user_id'], 'billing_' . $key, true );
$details['shipping'][$key] = get_user_meta( $context['user_id'], 'shipping_' . $key, true );
}
$details['phone'] = get_user_meta( $context['user_id'], 'billing_phone', true );
return $details;
}
function acreports_get_percentage( $part, $whole, $decimals = 1 ) {
$whole = (float) $whole;
if ( $whole <= 0 ) {
return 0;
}
return round( (float) $part / $whole * 100, $decimals );
}
function acreports_prepare_sql( $sql, $params ) {
global $wpdb;
if ( empty( $params ) ) {
return $sql;
}
array_unshift( $params, $sql );
return call_user_func_array( array($wpdb, 'prepare'), $params );
}
/**
* Upper bound on how many orders one report or export will read.
*
* A report is built by walking every matching order, so an unbounded store-wide
* report can exhaust the request. Filter to 0 to remove the cap.
*/
function acreports_get_max_report_orders() {
return max( 0, (int) apply_filters( 'acreports_max_report_orders', 10000 ) );
}
/**
* How many orders to load per batch when walking a report.
*
* Bigger batches mean fewer queries but more orders held in memory at once.
* Measured on a customer with ~920 orders: 50 => 351 queries / 118MB peak,
* 100 => 203 / 130MB, 250 => 95 / 146MB. 100 keeps the query count two orders
* of magnitude below the old per-order reads without the memory of 250.
*/
function acreports_get_report_batch_size() {
return max( 1, (int) apply_filters( 'acreports_report_batch_size', 100 ) );
}
/**
* Neutralise spreadsheet formula injection before a value is written to CSV.
*
* Order notes and billing names are supplied by customers, so a value starting
* with a formula character would execute when the export is opened in Excel or
* Sheets.
*/
function acreports_escape_csv_value( $value ) {
if ( null === $value ) {
return '';
}
$value = (string) $value;
if ( '' === $value ) {
return $value;
}
if ( in_array( $value[0], array(
'=',
'+',
'-',
'@',
"\t",
"\r"
), true ) ) {
return "'" . $value;
}
return $value;
}
/**
* Write one CSV row, escaping every value against formula injection.
*/
function acreports_fputcsv( $handle, $row ) {
return fputcsv( $handle, array_map( 'acreports_escape_csv_value', (array) $row ) );
}
/**
* Send CSV download headers.
*
* The file name is built from request input, so it is stripped of anything that
* could break out of the Content-Disposition header.
*/
function acreports_send_csv_headers( $filename ) {
$filename = sanitize_file_name( $filename );
if ( '' === $filename ) {
$filename = 'export.csv';
}
nocache_headers();
header( 'Content-Type: text/csv; charset=utf-8' );
header( 'Content-Disposition: attachment; filename="' . $filename . '"' );
}
/**
* The most recent order notes across a set of orders.
*
* One query for the whole set, rather than a note lookup per order inside the
* report loop. Expects $order_ids newest first, and only considers the newest
* few hundred so the IN() list stays a sane size.
*/
function acreports_get_recent_order_notes( $order_ids, $limit = 10 ) {
global $wpdb;
$order_ids = array_values( array_unique( array_filter( array_map( 'absint', (array) $order_ids ) ) ) );
if ( empty( $order_ids ) ) {
return array();
}
$order_ids = array_slice( $order_ids, 0, 500 );
$limit = max( 1, (int) $limit );
$placeholders = implode( ', ', array_fill( 0, count( $order_ids ), '%d' ) );
$params = array_merge( $order_ids, array($limit) );
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $placeholders holds only %d placeholders, one per order ID, and every value is bound by acreports_prepare_sql().
$notes = $wpdb->get_results( acreports_prepare_sql( "SELECT comment_post_ID, comment_date, comment_content\n FROM {$wpdb->comments}\n WHERE comment_post_ID IN ({$placeholders})\n AND comment_type = 'order_note'\n ORDER BY comment_date DESC, comment_ID DESC\n LIMIT %d", $params ) );
// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
return ( is_array( $notes ) ? $notes : array() );
}
/**
* Order attribution is stored as order meta under a filterable prefix, so read
* the prefix the same way WooCommerce writes it rather than hard coding it.
*/
function acreports_get_order_attribution_meta_prefix() {
$prefix = (string) apply_filters( 'wc_order_attribution_tracking_field_prefix', 'wc_order_attribution_' );
return '_' . trim( $prefix, '_' ) . '_';
}
/**
* Build the readable origin for an order, matching the label WooCommerce shows
* on the order screen. Core builds this in a private method, so the logic is
* repeated here, filters included, to keep both screens saying the same thing.
*/
function acreports_get_order_attribution_origin_label( $source_type, $source ) {
switch ( $source_type ) {
case 'utm':
/* translators: %s: the traffic source, for example a utm_source value. */
$label = __( 'Source: %s', 'advanced-customer-reports-woo' );
break;
case 'organic':
/* translators: %s: the search engine the customer arrived from. */
$label = __( 'Organic: %s', 'advanced-customer-reports-woo' );
break;
case 'referral':
/* translators: %s: the site that referred the customer. */
$label = __( 'Referral: %s', 'advanced-customer-reports-woo' );
break;
case 'typein':
$label = '';
$source = __( 'Direct', 'advanced-customer-reports-woo' );
break;
case 'mobile_app':
$label = '';
$source = __( 'Mobile app', 'advanced-customer-reports-woo' );
break;
case 'admin':
$label = '';
$source = __( 'Web admin', 'advanced-customer-reports-woo' );
break;
case 'pos':
$label = '';
$source = __( 'Point of Sale', 'advanced-customer-reports-woo' );
break;
default:
$label = '';
$source = __( 'Unknown', 'advanced-customer-reports-woo' );
break;
}
$formatted_source = apply_filters( 'wc_order_attribution_origin_formatted_source', ucfirst( trim( (string) $source, '()' ) ), $source );
$label = (string) apply_filters(
'wc_order_attribution_origin_label',
$label,
$source_type,
$source,
$formatted_source
);
if ( false === strpos( $label, '%' ) ) {
return $formatted_source;
}
// A source type that expects a source but has none would otherwise render
// as a dangling "Source:" with nothing after it.
if ( '' === trim( (string) $formatted_source ) ) {
return __( 'Unknown', 'advanced-customer-reports-woo' );
}
return sprintf( $label, $formatted_source );
}
/**
* Read the attribution recorded against a single order.
*
* Returns an empty array when there is nothing recorded, which is the case for
* orders placed before WooCommerce 8.5 and for any order whose visitor carried
* no attribution data. Those are counted separately in the report so that a
* missing record is never presented as a real "Unknown" origin.
*/
function acreports_get_order_attribution( $order ) {
if ( !$order || !is_a( $order, 'WC_Abstract_Order' ) || !method_exists( $order, 'get_meta' ) ) {
return array();
}
$prefix = acreports_get_order_attribution_meta_prefix();
$fields = array(
'source_type',
'referrer',
'utm_campaign',
'utm_source',
'utm_medium',
'utm_content',
'utm_id',
'utm_term',
'utm_source_platform',
'utm_creative_format',
'utm_marketing_tactic',
'session_entry',
'session_start_time',
'session_pages',
'session_count',
'device_type'
);
$attribution = array();
foreach ( $fields as $field ) {
$value = $order->get_meta( $prefix . $field );
if ( is_array( $value ) || is_object( $value ) || null === $value || '' === $value ) {
continue;
}
$attribution[$field] = (string) $value;
}
if ( empty( $attribution ) ) {
return array();
}
$attribution['origin'] = acreports_get_order_attribution_origin_label( ( isset( $attribution['source_type'] ) ? $attribution['source_type'] : '' ), ( isset( $attribution['utm_source'] ) ? $attribution['utm_source'] : '' ) );
return $attribution;
}
/**
* Fold one order into an attribution breakdown, keyed by origin, campaign, or
* device. $extra carries the display values for the row the key stands for.
*/
function acreports_add_attribution_bucket(
&$buckets,
$key,
$order_total,
$extra = array()
) {
$key = (string) $key;
if ( '' === $key ) {
return;
}
if ( !isset( $buckets[$key] ) ) {
$buckets[$key] = array_merge( array(
'orders' => 0,
'spend' => 0,
), $extra );
}
$buckets[$key]['orders'] += 1;
$buckets[$key]['spend'] += (float) $order_total;
}
/**
* How many rows the attribution breakdowns show.
*
* A store-wide report walks up to acreports_get_max_report_orders() orders, and
* referring domains and campaign tags are effectively unbounded, so the tables
* show the strongest performers and state how many were left out rather than
* rendering a row per referrer.
*/
function acreports_get_attribution_row_limit() {
return max( 1, (int) apply_filters( 'acreports_attribution_row_limit', 25 ) );
}
function acreports_sort_attribution_buckets( $first_bucket, $second_bucket ) {
$first_orders = ( isset( $first_bucket['orders'] ) ? (int) $first_bucket['orders'] : 0 );
$second_orders = ( isset( $second_bucket['orders'] ) ? (int) $second_bucket['orders'] : 0 );
if ( $first_orders === $second_orders ) {
$first_spend = ( isset( $first_bucket['spend'] ) ? (float) $first_bucket['spend'] : 0 );
$second_spend = ( isset( $second_bucket['spend'] ) ? (float) $second_bucket['spend'] : 0 );
if ( $first_spend === $second_spend ) {
return 0;
}
return ( $first_spend < $second_spend ? 1 : -1 );
}
return ( $first_orders < $second_orders ? 1 : -1 );
}
function acreports_get_order_stats_where_sql(
$start_date,
$end_date,
$status,
&$params
) {
$where = array('1=1');
if ( $start_date ) {
$where[] = 'date_created >= %s';
$params[] = $start_date . ' 00:00:00';
}
if ( $end_date ) {
$where[] = 'date_created <= %s';
$params[] = $end_date . ' 23:59:59';
}
$statuses = ( is_array( $status ) ? $status : array($status) );
$statuses = array_values( array_filter( array_map( 'sanitize_text_field', $statuses ) ) );
if ( !empty( $statuses ) && !in_array( 'all', $statuses, true ) ) {
$statuses = array_map( function ( $order_status ) {
return ( 0 === strpos( $order_status, 'wc-' ) ? $order_status : 'wc-' . $order_status );
}, $statuses );
$where[] = 'status IN (' . implode( ', ', array_fill( 0, count( $statuses ), '%s' ) ) . ')';
$params = array_merge( $params, $statuses );
}
return implode( ' AND ', $where );
}
function acreports_order_stats_table_exists() {
static $exists = null;
if ( null !== $exists ) {
return $exists;
}
global $wpdb;
$order_stats_table = $wpdb->prefix . 'wc_order_stats';
$exists = $order_stats_table === $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $order_stats_table ) );
return $exists;
}
/**
* How many spend rankings to keep in one window's rank cache.
*
* The rankings live in a single transient per date window rather than one per
* customer, so this bounds the size of that row.
*/
function acreports_get_benchmark_rank_cache_size() {
return max( 1, (int) apply_filters( 'acreports_benchmark_rank_cache_size', 200 ) );
}
/**
* Whole-store totals for the report's date window.
*
* Cached on the window alone. This does not depend on which customer is being
* looked at, so every report opened in the same hour reuses one result instead
* of re-aggregating wc_order_stats per customer.
*/
function acreports_get_store_benchmark_summary(
$start_date,
$end_date,
$status,
$cache_ttl
) {
global $wpdb;
$order_stats_table = $wpdb->prefix . 'wc_order_stats';
$cache_key = 'acreports_benchmarks_sum_' . md5( (string) wp_json_encode( array($start_date, $end_date, $status) ) );
if ( $cache_ttl > 0 ) {
$cached = get_transient( $cache_key );
if ( is_array( $cached ) ) {
return $cached;
}
}
$params = array();
$where_sql = acreports_get_order_stats_where_sql(
$start_date,
$end_date,
$status,
$params
);
$summary_sql = "SELECT COUNT(order_id) AS total_orders,\n COALESCE(SUM(total_sales), 0) AS total_sales,\n COUNT(DISTINCT CASE WHEN customer_id > 0 THEN customer_id END) AS customer_count,\n COALESCE(AVG(total_sales), 0) AS average_order_value\n FROM {$order_stats_table}\n WHERE {$where_sql}";
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- The query is built from literals and placeholders only; every value is bound by acreports_prepare_sql().
$summary = $wpdb->get_row( acreports_prepare_sql( $summary_sql, $params ), ARRAY_A );
if ( !$summary ) {
return array();
}
$summary = array(
'total_orders' => max( 0, (int) $summary['total_orders'] ),
'total_sales' => (float) $summary['total_sales'],
'customer_count' => max( 0, (int) $summary['customer_count'] ),
'average_order_value' => (float) $summary['average_order_value'],
);
if ( $cache_ttl > 0 ) {
set_transient( $cache_key, $summary, $cache_ttl );
}
return $summary;
}
/**
* How many customers out-spent this one over the window.
*
* This genuinely varies with the customer's total, so the results are memoised
* in one map per window rather than a transient per customer, which would leave
* a row behind for every customer whose report was ever opened.
*/
function acreports_get_store_benchmark_rank(
$start_date,
$end_date,
$status,
$customer_total,
$cache_ttl
) {
global $wpdb;
$order_stats_table = $wpdb->prefix . 'wc_order_stats';
$cache_key = 'acreports_benchmarks_rank_' . md5( (string) wp_json_encode( array($start_date, $end_date, $status) ) );
$total_key = (string) round( (float) $customer_total, 2 );
$ranks = ( $cache_ttl > 0 ? get_transient( $cache_key ) : false );
if ( !is_array( $ranks ) ) {
$ranks = array();
}
if ( array_key_exists( $total_key, $ranks ) ) {
return (int) $ranks[$total_key];
}
$rank_params = array();
$rank_where_sql = acreports_get_order_stats_where_sql(
$start_date,
$end_date,
$status,
$rank_params
);
$rank_params[] = (float) $customer_total;
$rank_sql = "SELECT COUNT(*) FROM (\n SELECT customer_id, SUM(total_sales) AS customer_total\n FROM {$order_stats_table}\n WHERE {$rank_where_sql} AND customer_id > 0\n GROUP BY customer_id\n HAVING customer_total > %f\n ) ranked_customers";
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- The query is built from literals and placeholders only; every value is bound by acreports_prepare_sql().
$customers_above = (int) $wpdb->get_var( acreports_prepare_sql( $rank_sql, $rank_params ) );
if ( $cache_ttl > 0 ) {
$ranks[$total_key] = $customers_above;
// Oldest entries fall off the front, so a store with a lot of distinct
// customer totals cannot grow this row without bound.
if ( count( $ranks ) > acreports_get_benchmark_rank_cache_size() ) {
$ranks = array_slice(
$ranks,
-acreports_get_benchmark_rank_cache_size(),
null,
true
);
}
set_transient( $cache_key, $ranks, $cache_ttl );
}
return $customers_above;
}
function acreports_get_store_benchmarks(
$start_date,
$end_date,
$status,
$customer_total = 0
) {
if ( !acreports_order_stats_table_exists() ) {
return array();
}
// These are whole-store aggregates over wc_order_stats, including a grouped
// subquery for the spend ranking. They barely move between page loads, so
// they are cached rather than recomputed every time a report is opened.
$cache_ttl = max( 0, (int) apply_filters( 'acreports_store_benchmarks_cache_ttl', HOUR_IN_SECONDS ) );
$summary = acreports_get_store_benchmark_summary(
$start_date,
$end_date,
$status,
$cache_ttl
);
if ( empty( $summary ) ) {
return array();
}
$customer_count = $summary['customer_count'];
$total_orders = $summary['total_orders'];
$total_sales = $summary['total_sales'];
$customers_above = null;
$top_percent = null;
if ( $customer_count > 0 && $customer_total > 0 ) {
$customers_above = acreports_get_store_benchmark_rank(
$start_date,
$end_date,
$status,
$customer_total,
$cache_ttl
);
$top_percent = round( ($customers_above + 1) / $customer_count * 100, 1 );
}
return array(
'total_orders' => $total_orders,
'total_sales' => $total_sales,
'customer_count' => $customer_count,
'average_order_value' => $summary['average_order_value'],
'average_lifetime_value' => ( $customer_count ? $total_sales / $customer_count : 0 ),
'average_orders_per_customer' => ( $customer_count ? $total_orders / $customer_count : 0 ),
'customers_above' => $customers_above,
'top_percent' => $top_percent,
);
}
function acreports_get_average_order_gap_days( $order_timestamps ) {
$order_timestamps = array_values( array_unique( array_map( 'absint', $order_timestamps ) ) );
sort( $order_timestamps );
if ( count( $order_timestamps ) < 2 ) {
return null;
}
$total_gap_days = 0;
$timestamp_count = count( $order_timestamps );
for ($index = 1; $index < $timestamp_count; $index++) {
$total_gap_days += max( 0, ($order_timestamps[$index] - $order_timestamps[$index - 1]) / DAY_IN_SECONDS );
}
return round( $total_gap_days / ($timestamp_count - 1), 1 );
}
/**
* The segments an RFM analysis can place a customer in.
*
* The keys are what the customers list filters and sorts on, so they must stay
* in step with the keys acreports_get_rfm_analysis() returns.
*
* @return array Segment key => translated label.
*/
function acreports_get_rfm_segment_labels() {
return array(
'vip' => __( 'VIP', 'advanced-customer-reports-woo' ),
'loyal' => __( 'Loyal', 'advanced-customer-reports-woo' ),
'active' => __( 'Active', 'advanced-customer-reports-woo' ),
'new' => __( 'New Customer', 'advanced-customer-reports-woo' ),
'discount_driven' => __( 'Discount Driven', 'advanced-customer-reports-woo' ),
'at_risk' => __( 'At Risk', 'advanced-customer-reports-woo' ),
'dormant' => __( 'Dormant', 'advanced-customer-reports-woo' ),
'needs_review' => __( 'Needs Review', 'advanced-customer-reports-woo' ),
'no_orders' => __( 'No Orders', 'advanced-customer-reports-woo' ),
);
}
/**
* Score a customer on recency, frequency and spend, and name their segment.
*
* The customers list applies these same rules in SQL, so it can filter and
* sort every customer by segment without building each report (see
* ACReports_Customers_Table::get_segmented_from__premium_only()). Change the
* two together, or the list and the report will disagree.
*
* @param int $total_orders Orders in the report.
* @param float $total_spent Total spent across those orders.
* @param int $days_since_last_order Whole days since the latest order.
* @param float $average_lifetime_value Store average lifetime value, or 0 if unknown.
* @param float|null $average_days_between_orders Average gap between orders in days, or null.
* @param float $coupon_order_rate Percentage of orders that used a coupon.
* @param float $refund_rate Percentage of orders that were refunded.
* @return array {segment, segment_key, score, recency, frequency, monetary, summary, signals}
*/
function acreports_get_rfm_analysis(
$total_orders,
$total_spent,
$days_since_last_order,
$average_lifetime_value,
$average_days_between_orders,
$coupon_order_rate,
$refund_rate
) {
$segment_labels = acreports_get_rfm_segment_labels();
if ( !$total_orders ) {
return array(
'segment' => $segment_labels['no_orders'],
'segment_key' => 'no_orders',
'score' => 0,
'recency' => 0,
'frequency' => 0,
'monetary' => 0,
'summary' => __( 'No completed purchase pattern yet.', 'advanced-customer-reports-woo' ),
'signals' => array(),
);
}
if ( $days_since_last_order <= 30 ) {
$recency_score = 5;
} elseif ( $days_since_last_order <= 60 ) {
$recency_score = 4;
} elseif ( $days_since_last_order <= 120 ) {
$recency_score = 3;
} elseif ( $days_since_last_order <= 240 ) {
$recency_score = 2;
} else {
$recency_score = 1;
}
if ( $total_orders >= 10 ) {
$frequency_score = 5;
} elseif ( $total_orders >= 6 ) {
$frequency_score = 4;
} elseif ( $total_orders >= 3 ) {
$frequency_score = 3;
} elseif ( $total_orders >= 2 ) {
$frequency_score = 2;
} else {
$frequency_score = 1;
}
if ( $average_lifetime_value > 0 ) {
$value_ratio = $total_spent / $average_lifetime_value;
if ( $value_ratio >= 3 ) {
$monetary_score = 5;
} elseif ( $value_ratio >= 2 ) {
$monetary_score = 4;
} elseif ( $value_ratio >= 1 ) {
$monetary_score = 3;
} elseif ( $value_ratio >= 0.5 ) {
$monetary_score = 2;
} else {
$monetary_score = 1;
}
} else {
$monetary_score = min( 5, max( 1, (int) ceil( $total_spent / 250 ) ) );
}
$score = (int) round( ($recency_score * 0.4 + $frequency_score * 0.3 + $monetary_score * 0.3) / 5 * 100 );
$is_overdue = $average_days_between_orders && $days_since_last_order > $average_days_between_orders * 1.75;
if ( $refund_rate >= 30 ) {
$segment_key = 'needs_review';
$summary = __( 'High refund activity means this account deserves closer attention.', 'advanced-customer-reports-woo' );
} elseif ( $days_since_last_order > 365 ) {
$segment_key = 'dormant';
$summary = __( 'This customer has not ordered for over a year.', 'advanced-customer-reports-woo' );
} elseif ( $is_overdue ) {
$segment_key = 'at_risk';
$summary = __( 'This customer is beyond their usual reorder rhythm.', 'advanced-customer-reports-woo' );
} elseif ( $score >= 85 && $total_orders >= 3 ) {
$segment_key = 'vip';
$summary = __( 'High-value customer with strong recency, frequency, and spend.', 'advanced-customer-reports-woo' );
} elseif ( $score >= 70 ) {
$segment_key = 'loyal';
$summary = __( 'Reliable repeat customer with healthy purchase activity.', 'advanced-customer-reports-woo' );
} elseif ( $total_orders <= 1 && $days_since_last_order <= 45 ) {
$segment_key = 'new';
$summary = __( 'Recent first-time customer worth nurturing into a repeat buyer.', 'advanced-customer-reports-woo' );
} elseif ( $coupon_order_rate >= 60 ) {
$segment_key = 'discount_driven';
$summary = __( 'Often buys with coupons, so discount strategy matters here.', 'advanced-customer-reports-woo' );
} else {
$segment_key = 'active';
$summary = __( 'Customer has useful order history but no urgent risk signal.', 'advanced-customer-reports-woo' );
}
return array(
'segment' => $segment_labels[$segment_key],
'segment_key' => $segment_key,
'score' => $score,
'recency' => $recency_score,
'frequency' => $frequency_score,
'monetary' => $monetary_score,
'summary' => $summary,
'signals' => array(
/* translators: %d: recency score out of 5. */
sprintf( __( 'Recency score: %d/5', 'advanced-customer-reports-woo' ), $recency_score ),
/* translators: %d: frequency score out of 5. */
sprintf( __( 'Frequency score: %d/5', 'advanced-customer-reports-woo' ), $frequency_score ),
/* translators: %d: monetary score out of 5. */
sprintf( __( 'Monetary score: %d/5', 'advanced-customer-reports-woo' ), $monetary_score ),
),
);
}
function acreports_get_cross_sell_suggestions( $product_stats, $limit = 6 ) {
$purchased_product_ids = array_values( array_filter( array_map( 'absint', array_keys( $product_stats ) ) ) );
$purchased_lookup = array_flip( $purchased_product_ids );
$suggestions = array();
foreach ( array_slice( $purchased_product_ids, 0, 10 ) as $source_product_id ) {
$source_product = wc_get_product( $source_product_id );
if ( !$source_product ) {
continue;
}
$candidate_ids = array_merge( $source_product->get_cross_sell_ids(), $source_product->get_upsell_ids() );
foreach ( $candidate_ids as $candidate_id ) {
$candidate_id = absint( $candidate_id );
if ( !$candidate_id || isset( $purchased_lookup[$candidate_id] ) || isset( $suggestions[$candidate_id] ) ) {
continue;
}
$candidate_product = wc_get_product( $candidate_id );
if ( !$candidate_product ) {
continue;
}
$suggestions[$candidate_id] = array(
'id' => $candidate_id,
'title' => $candidate_product->get_name(),
'price' => $candidate_product->get_price_html(),
'source_id' => $source_product_id,
'source_title' => $source_product->get_name(),
);
if ( count( $suggestions ) >= $limit ) {
return array_values( $suggestions );
}
}
}
return array_values( $suggestions );
}
function acreports_render_pro_locked_panel( $title, $description ) {
?>
user_email ) ) {
return '';
}
return 'mailto:' . sanitize_email( $user->user_email ) . '?subject=' . rawurlencode( $subject ) . '&body=' . rawurlencode( $body );
}
function acreports_get_recommended_coupon_summary( $coupon, $customer, $coupon_type = '' ) {
if ( !$coupon instanceof WC_Coupon ) {
$coupon = new WC_Coupon($coupon);
}
if ( !$coupon->get_id() ) {
return null;
}
$created_at = $coupon->get_meta( '_acreports_recommended_action_created_at' );
$created_timestamp = ( $created_at ? strtotime( $created_at ) : false );
if ( !$created_timestamp && $coupon->get_date_created() ) {
$created_timestamp = $coupon->get_date_created()->getTimestamp();
}
$expires_at = $coupon->get_date_expires();
$has_customer = !empty( $customer['found'] );
$username = $coupon->get_meta( '_acreports_recommended_action_username' );
if ( !$username && $has_customer ) {
$username = ( '' !== $customer['username'] ? $customer['username'] : $customer['email'] );
}
$config = acreports_get_recommended_coupon_config( $coupon_type );
$renew_after_days = ( !empty( $config['renew_after_days'] ) ? absint( $config['renew_after_days'] ) : (( !empty( $config['expires_days'] ) ? absint( $config['expires_days'] ) : 30 )) );
$current_timestamp = current_time( 'timestamp' );
$expires_timestamp = ( $expires_at ? $expires_at->getTimestamp() : 0 );
$usage_count = (int) $coupon->get_usage_count();
$usage_limit = (int) $coupon->get_usage_limit();
$age_days = ( $created_timestamp ? max( 0, (int) floor( ($current_timestamp - $created_timestamp) / DAY_IN_SECONDS ) ) : 0 );
$is_expired = $expires_timestamp && $expires_timestamp < $current_timestamp;
$is_used = $usage_limit > 0 && $usage_count >= $usage_limit;
$is_stale = $created_timestamp && $age_days >= $renew_after_days;
$is_active = !$is_expired && !$is_used && !$is_stale;
$status_label = __( 'Existing coupon', 'advanced-customer-reports-woo' );
$renewal_reason = '';
if ( $is_used ) {
$status_label = __( 'Previous coupon used', 'advanced-customer-reports-woo' );
$renewal_reason = __( 'This coupon has already been used, so a new recommendation can be generated.', 'advanced-customer-reports-woo' );
} elseif ( $is_expired ) {
$status_label = __( 'Previous coupon expired', 'advanced-customer-reports-woo' );
$renewal_reason = __( 'This coupon has expired, so a new recommendation can be generated.', 'advanced-customer-reports-woo' );
} elseif ( $is_stale ) {
$status_label = __( 'Ready for new coupon', 'advanced-customer-reports-woo' );
/* translators: 1: number of days since the coupon was created, 2: length of the renewal window in days. */
$renewal_reason = sprintf( __( 'Created %1$d days ago; renewal window is %2$d days.', 'advanced-customer-reports-woo' ), $age_days, $renew_after_days );
}
$amount = $coupon->get_amount();
$site_name = get_bloginfo( 'name' );
/* translators: %s: site name. */
$signoff = sprintf( __( "Thanks,\nThe %s team", 'advanced-customer-reports-woo' ), $site_name );
/* translators: %s: site name. */
$email_subject = sprintf( __( 'Your private offer from %s', 'advanced-customer-reports-woo' ), $site_name );
$email_body = sprintf(
/* translators: 1: customer first name, 2: site name, 3: coupon code, 4: email sign-off. */
__( "Hi %1\$s,\n\nThank you for shopping with %2\$s. To say thanks, here's a private code you can use on your next order:\n\n %3\$s\n\nIt's reserved for you, so feel free to use it whenever you're ready.\n\n%4\$s", 'advanced-customer-reports-woo' ),
( $has_customer ? ( trim( (string) $customer['first_name'] ) !== '' ? $customer['first_name'] : $customer['display_name'] ) : '' ),
$site_name,
$coupon->get_code(),
$signoff
);
return array(
'id' => $coupon->get_id(),
'code' => $coupon->get_code(),
'amount' => $amount,
'discount_type' => $coupon->get_discount_type(),
'username' => $username,
'created_at' => ( $created_timestamp ? date_i18n( get_option( 'date_format' ), $created_timestamp ) : '' ),
'expires_at' => ( $expires_at ? date_i18n( get_option( 'date_format' ), $expires_at->getTimestamp() ) : '' ),
'usage_count' => $usage_count,
'usage_limit' => $usage_limit,
'age_days' => $age_days,
'renew_after_days' => $renew_after_days,
'is_active' => $is_active,
'is_expired' => $is_expired,
'is_used' => $is_used,
'is_stale' => $is_stale,
'status_label' => $status_label,
'renewal_reason' => $renewal_reason,
'edit_url' => admin_url( 'post.php?post=' . absint( $coupon->get_id() ) . '&action=edit' ),
'email_subject' => ( $has_customer ? $email_subject : '' ),
'email_body' => ( $has_customer ? $email_body : '' ),
'coupon_type' => $coupon_type,
);
}
function acreports_get_existing_recommended_coupon( $customer, $coupon_type ) {
if ( empty( $customer['found'] ) || !is_email( $customer['email'] ) || !class_exists( 'WC_Coupon' ) ) {
return null;
}
$customer_email = strtolower( sanitize_email( $customer['email'] ) );
$customer_user_id = (int) $customer['user_id'];
// Called once per recommended action button, so the result is memoised for
// the rest of the request.
static $lookup_cache = array();
$cache_key = $customer_user_id . '|' . $customer_email . '|' . $coupon_type;
if ( array_key_exists( $cache_key, $lookup_cache ) ) {
return $lookup_cache[$cache_key];
}
// Match on the customer as well as the coupon type. Filtering by type alone
// meant that once a store had more coupons of a type than the query window,
// a customer's own coupon fell out of the results and a duplicate was
// generated for them.
$customer_clauses = array(
'relation' => 'OR',
);
if ( $customer_user_id > 0 ) {
$customer_clauses[] = array(
'key' => '_acreports_recommended_action_user_id',
'value' => $customer_user_id,
);
}
$customer_clauses[] = array(
'key' => '_acreports_recommended_action_customer_email',
'value' => $customer_email,
);
$coupon_ids = get_posts( array(
'post_type' => 'shop_coupon',
'post_status' => array(
'publish',
'draft',
'pending',
'private'
),
'fields' => 'ids',
'posts_per_page' => 20,
'orderby' => 'date',
'order' => 'DESC',
'no_found_rows' => true,
'update_post_term_cache' => false,
'meta_query' => array(
'relation' => 'AND',
array(
'key' => '_acreports_recommended_action_type',
'value' => sanitize_key( $coupon_type ),
),
$customer_clauses,
),
) );
if ( empty( $coupon_ids ) ) {
$lookup_cache[$cache_key] = null;
return null;
}
$previous_coupon = null;
foreach ( $coupon_ids as $coupon_id ) {
$coupon = new WC_Coupon($coupon_id);
if ( !$coupon->get_id() ) {
continue;
}
$coupon_user_id = absint( $coupon->get_meta( '_acreports_recommended_action_user_id' ) );
$coupon_email = strtolower( sanitize_email( (string) $coupon->get_meta( '_acreports_recommended_action_customer_email' ) ) );
$email_restrictions = array_map( 'strtolower', array_map( 'sanitize_email', $coupon->get_email_restrictions() ) );
// Guests have no user ID, so they are matched on email alone.
$matches_user = $customer_user_id > 0 && $coupon_user_id === $customer_user_id;
$matches_email = in_array( $customer_email, $email_restrictions, true ) || $coupon_email && $coupon_email === $customer_email;
if ( $matches_user || $matches_email ) {
$coupon_summary = acreports_get_recommended_coupon_summary( $coupon, $customer, $coupon_type );
if ( !empty( $coupon_summary['is_active'] ) ) {
$lookup_cache[$cache_key] = $coupon_summary;
return $coupon_summary;
}
if ( !$previous_coupon ) {
$previous_coupon = $coupon_summary;
}
}
}
$lookup_cache[$cache_key] = $previous_coupon;
return $previous_coupon;
}
function acreports_get_recommended_coupon_button(
$customer,
$label,
$coupon_type,
$is_primary = false
) {
$button = array(
'type' => 'coupon',
'label' => $label,
'coupon_type' => $coupon_type,
);
if ( $is_primary ) {
$button['primary'] = true;
}
$existing_coupon = acreports_get_existing_recommended_coupon( $customer, $coupon_type );
if ( $existing_coupon ) {
if ( !empty( $existing_coupon['is_active'] ) ) {
$button['existing_coupon'] = $existing_coupon;
} else {
$config = acreports_get_recommended_coupon_config( $coupon_type );
$button['previous_coupon'] = $existing_coupon;
/* translators: %s: coupon type label, for example "loyalty". */
$button['label'] = sprintf( __( 'Create new %s coupon', 'advanced-customer-reports-woo' ), ( $config && !empty( $config['label'] ) ? $config['label'] : __( 'recommended', 'advanced-customer-reports-woo' ) ) );
}
}
return $button;
}
function acreports_render_existing_recommended_coupon( $existing_coupon ) {
if ( empty( $existing_coupon['code'] ) ) {
return;
}
$meta_parts = array();
if ( !empty( $existing_coupon['created_at'] ) ) {
/* translators: %s: date the coupon was created. */
$meta_parts[] = sprintf( __( 'Created %s', 'advanced-customer-reports-woo' ), $existing_coupon['created_at'] );
}
if ( !empty( $existing_coupon['username'] ) ) {
/* translators: %s: username the coupon is assigned to. */
$meta_parts[] = sprintf( __( 'Assigned to %s', 'advanced-customer-reports-woo' ), $existing_coupon['username'] );
}
if ( !empty( $existing_coupon['expires_at'] ) ) {
/* translators: %s: date the coupon expires. */
$meta_parts[] = sprintf( __( 'Expires %s', 'advanced-customer-reports-woo' ), $existing_coupon['expires_at'] );
}
if ( !empty( $existing_coupon['usage_limit'] ) ) {
/* translators: 1: number of times the coupon has been used, 2: coupon usage limit. */
$meta_parts[] = sprintf( __( 'Used %1$d/%2$d', 'advanced-customer-reports-woo' ), $existing_coupon['usage_count'], $existing_coupon['usage_limit'] );
}
?>
= 25 || $status_issue_rate >= 20 ) {
$actions[] = array(
'priority' => 100,
'tone' => 'danger',
'icon' => 'dashicons-warning',
'title' => __( 'Review service recovery', 'advanced-customer-reports-woo' ),
'summary' => sprintf( __( 'Refunds/status issues are elevated: %1$s%% refunded and %2$s%% cancelled or failed. Check the order history before sending another offer.', 'advanced-customer-reports-woo' ), number_format_i18n( $refund_rate, 1 ), number_format_i18n( $status_issue_rate, 1 ) ),
'reason' => __( 'High friction customers are better handled with support-first outreach.', 'advanced-customer-reports-woo' ),
'buttons' => array(
array(
'type' => 'switch',
'label' => __( 'Review orders', 'advanced-customer-reports-woo' ),
'tab' => 'orders',
'primary' => true,
),
/* translators: 1: customer first name, 2: site name, 3: email sign-off. */
array(
'type' => 'email_form',
'label' => __( 'Email customer', 'advanced-customer-reports-woo' ),
'subject' => __( 'Following up on your recent order', 'advanced-customer-reports-woo' ),
'body' => sprintf(
__( "Hi %1\$s,\n\nI wanted to reach out personally to make sure your recent order with %2\$s went smoothly. If anything wasn't quite right, just reply to this email and we'll put it right straight away.\n\n%3\$s", 'advanced-customer-reports-woo' ),
$customer_name,
$site_name,
$signoff
),
),
),
);
}
if ( $days_since_last_order > 180 || $is_overdue ) {
/* translators: %d: number of days. */
$reorder_signal = ( $is_overdue ? sprintf( __( 'Expected reorder is overdue by %d days.', 'advanced-customer-reports-woo' ), $overdue_days ) : sprintf( __( 'Last order was %d days ago.', 'advanced-customer-reports-woo' ), $days_since_last_order ) );
$winback_existing = acreports_get_existing_recommended_coupon( $customer, 'winback' );
$winback_code = ( $winback_existing && !empty( $winback_existing['is_active'] ) ? strtoupper( $winback_existing['code'] ) : '[COUPON CODE]' );
$winback_coupon_button = array(
'type' => 'coupon',
'label' => __( 'Create win-back coupon', 'advanced-customer-reports-woo' ),
'coupon_type' => 'winback',
'primary' => true,
);
if ( $winback_existing ) {
if ( !empty( $winback_existing['is_active'] ) ) {
$winback_coupon_button['existing_coupon'] = $winback_existing;
} else {
$winback_coupon_button['previous_coupon'] = $winback_existing;
$winback_coupon_button['label'] = __( 'Create new win-back coupon', 'advanced-customer-reports-woo' );
}
}
$actions[] = array(
'priority' => ( $days_since_last_order > 365 ? 95 : 90 ),
'tone' => 'warning',
'icon' => 'dashicons-megaphone',
'title' => __( 'Send a win-back offer', 'advanced-customer-reports-woo' ),
'summary' => sprintf( __( 'This customer is drifting from their normal purchase pattern. %s', 'advanced-customer-reports-woo' ), $reorder_signal ),
'reason' => ( $average_days_between_orders ? sprintf( __( 'Average gap between orders is %s days.', 'advanced-customer-reports-woo' ), number_format_i18n( $average_days_between_orders, 1 ) ) : __( 'No reliable repeat rhythm yet, but recency is low.', 'advanced-customer-reports-woo' ) ),
'buttons' => array(
$winback_coupon_button,
/* translators: %s in the subject: site name. In the body, 1: customer first name, 2: site name, 3: coupon code, 4: email sign-off. */
array(
'type' => 'email_form',
'label' => __( 'Compose email', 'advanced-customer-reports-woo' ),
'subject' => sprintf( __( 'We miss you at %s', 'advanced-customer-reports-woo' ), $site_name ),
'body' => sprintf(
__( "Hi %1\$s,\n\nIt's been a while since your last order with %2\$s and we'd love to see you back.\n\nHere's an exclusive discount code just for you — use it on your next order:\n\n %4\$s\n\nDon't hesitate to reply if there's anything we can help with.\n\n%3\$s", 'advanced-customer-reports-woo' ),
$customer_name,
$site_name,
$signoff,
$winback_code
),
),
),
);
}
if ( $total_orders <= 1 && $days_since_last_order <= 90 ) {
$actions[] = array(
'priority' => 82,
'tone' => 'success',
'icon' => 'dashicons-heart',
'title' => __( 'Nurture the second order', 'advanced-customer-reports-woo' ),
'summary' => __( 'This looks like an early-stage customer. A focused second-order nudge can turn the first purchase into a repeat habit.', 'advanced-customer-reports-woo' ),
'reason' => sprintf( _n(
'%d order recorded so far.',
'%d orders recorded so far.',
$total_orders,
'advanced-customer-reports-woo'
), $total_orders ),
'buttons' => array(acreports_get_recommended_coupon_button(
$customer,
__( 'Create second-order coupon', 'advanced-customer-reports-woo' ),
'second_order',
true
), array(
'type' => 'switch',
'label' => __( 'View next products', 'advanced-customer-reports-woo' ),
'tab' => 'products',
'product_tab' => 'product-tab-4',
)),
);
}
if ( $rfm_score >= 85 && $total_orders >= 3 || null !== $top_percent && (float) $top_percent <= 15 ) {
/* translators: %s: percentage of customers this customer outspends, %d: customer health score. */
$rank_summary = ( null !== $top_percent ? sprintf( __( 'Spend rank: top %s%% of customers.', 'advanced-customer-reports-woo' ), number_format_i18n( (float) $top_percent, 1 ) ) : sprintf( __( 'Health score: %d.', 'advanced-customer-reports-woo' ), $rfm_score ) );
$actions[] = array(
'priority' => 78,
'tone' => 'success',
'icon' => 'dashicons-star-filled',
'title' => __( 'Reward high-value loyalty', 'advanced-customer-reports-woo' ),
'summary' => sprintf( __( 'This customer is one of the stronger accounts in the report. %s', 'advanced-customer-reports-woo' ), $rank_summary ),
'reason' => sprintf( __( 'Lifetime spend is %1$s across %2$d orders.', 'advanced-customer-reports-woo' ), html_entity_decode( wp_strip_all_tags( wc_price( $total_spent ) ), ENT_QUOTES | ENT_HTML5, 'UTF-8' ), $total_orders ),
'buttons' => array(acreports_get_recommended_coupon_button(
$customer,
__( 'Create loyalty coupon', 'advanced-customer-reports-woo' ),
'loyalty',
true
), array(
'type' => 'email_form',
'label' => __( 'Send thank-you', 'advanced-customer-reports-woo' ),
'subject' => sprintf( __( 'A thank-you from %s', 'advanced-customer-reports-woo' ), $site_name ),
'body' => sprintf(
__( "Hi %1\$s,\n\nI just wanted to send a quick note to say thank you for being one of our most valued customers at %2\$s. Your continued support genuinely means a lot to our small team.\n\nIf there's ever anything we can do to make your experience even better, please reply to this email and let me know.\n\n%3\$s", 'advanced-customer-reports-woo' ),
$customer_name,
$site_name,
$signoff
),
)),
);
}
if ( $discount_order_rate >= 60 ) {
$actions[] = array(
'priority' => 70,
'tone' => 'warning',
'icon' => 'dashicons-tickets-alt',
'title' => __( 'Review coupon dependency', 'advanced-customer-reports-woo' ),
'summary' => sprintf( __( 'Coupons appear on %s%% of this customer\'s orders, so discount strategy matters here.', 'advanced-customer-reports-woo' ), number_format_i18n( $discount_order_rate, 1 ) ),
'reason' => __( 'Check existing coupon history before adding another incentive.', 'advanced-customer-reports-woo' ),
'buttons' => array(array(
'type' => 'switch',
'label' => __( 'Review coupons', 'advanced-customer-reports-woo' ),
'tab' => 'coupons',
'primary' => true,
), array(
'type' => 'switch',
'label' => __( 'Review products', 'advanced-customer-reports-woo' ),
'tab' => 'products',
)),
);
}
if ( $has_cross_sell_suggestions ) {
$cross_sell_summary = ( null !== $first_latest_order_delta && $first_latest_order_delta < 0 ? __( 'Latest order value is lower than the first order, so cross-sells may help rebuild basket size.', 'advanced-customer-reports-woo' ) : __( 'There are linked cross-sells or upsells based on products this customer already bought.', 'advanced-customer-reports-woo' ) );
$product_lines = array();
foreach ( array_slice( $cross_sell_suggestions, 0, 4 ) as $suggestion ) {
$product_id = absint( $suggestion['id'] );
$product_url = get_permalink( $product_id );
$product_title = $suggestion['title'];
$product_lines[] = '- ' . $product_title . (( $product_url ? ' ' . $product_url : '' ));
}
$product_list = implode( "\n", $product_lines );
$actions[] = array(
'priority' => 62,
'tone' => 'info',
'icon' => 'dashicons-products',
'title' => __( 'Suggest the next best product', 'advanced-customer-reports-woo' ),
'summary' => $cross_sell_summary,
'reason' => ( $rfm_segment ? sprintf( __( 'Current segment: %s.', 'advanced-customer-reports-woo' ), $rfm_segment ) : __( 'Uses configured product cross-sells and upsells.', 'advanced-customer-reports-woo' ) ),
'buttons' => array(
array(
'type' => 'switch',
'label' => __( 'Open suggestions', 'advanced-customer-reports-woo' ),
'tab' => 'products',
'product_tab' => 'product-tab-4',
'primary' => true,
),
/* translators: 1: customer first name, 2: site name, 3: email sign-off, 4: list of suggested products. */
array(
'type' => 'email_form',
'label' => __( 'Compose email', 'advanced-customer-reports-woo' ),
'subject' => __( 'A few picks I thought you might like', 'advanced-customer-reports-woo' ),
'body' => sprintf(
__( "Hi %1\$s,\n\nBased on what you've ordered from %2\$s before, I thought you might enjoy these:\n\n%4\$s\n\nFeel free to reply if you have any questions about any of these.\n\n%3\$s", 'advanced-customer-reports-woo' ),
$customer_name,
$site_name,
$signoff,
$product_list
),
),
),
);
}
if ( empty( $actions ) ) {
$actions[] = array(
'priority' => 50,
'tone' => 'info',
'icon' => 'dashicons-email-alt2',
'title' => __( 'Send a relationship check-in', 'advanced-customer-reports-woo' ),
'summary' => __( 'No urgent risk signal stands out. A light-touch check-in is the best next action.', 'advanced-customer-reports-woo' ),
'reason' => ( $rfm_segment ? sprintf( __( 'Current segment: %s.', 'advanced-customer-reports-woo' ), $rfm_segment ) : __( 'Customer activity looks steady.', 'advanced-customer-reports-woo' ) ),
'buttons' => array(
/* translators: 1: customer first name, 2: site name, 3: email sign-off. */
array(
'type' => 'email_form',
'label' => __( 'Compose email', 'advanced-customer-reports-woo' ),
'subject' => __( 'Just checking in', 'advanced-customer-reports-woo' ),
'body' => sprintf(
__( "Hi %1\$s,\n\nI just wanted to drop you a quick note from %2\$s to see how you're getting on, and to check there's nothing we can help with.\n\nFeel free to reply if there's anything you'd like to ask.\n\n%3\$s", 'advanced-customer-reports-woo' ),
$customer_name,
$site_name,
$signoff
),
'primary' => true,
),
array(
'type' => 'switch',
'label' => __( 'Review history', 'advanced-customer-reports-woo' ),
'tab' => 'orders',
),
),
);
}
usort( $actions, function ( $first_action, $second_action ) {
return $second_action['priority'] <=> $first_action['priority'];
} );
return array_slice( $actions, 0, 4 );
}
function acreports_render_recommended_actions( $customer, $actions ) {
$has_customer = !empty( $customer['found'] );
?>
>
can_use_premium_code() ) {
?>
' . esc_html__( 'No monthly trend data available for this report.', 'advanced-customer-reports-woo' ) . '
';
return;
}
$chart_months = array_slice(
$monthly_stats,
-12,
null,
true
);
$max_total = 0;
foreach ( $chart_months as $month_stats ) {
$max_total = max( $max_total, (float) $month_stats['gross'] );
}
?>
$month_stats ) {
$bar_height = ( $max_total > 0 ? max( 8, round( (float) $month_stats['gross'] / $max_total * 100 ) ) : 0 );
?>
';
echo '
No customer found for "' . esc_html( $searched_for ) . '". ';
echo '';
exit;
}
if ( $has_customer ) {
// Fetch WooCommerce Billing Address. Guests have no profile fields, so
// their details are read from their most recent order instead.
$profile_details = acreports_get_customer_profile_details( $customer );
$address = $profile_details['billing'];
$shipping_address = $profile_details['shipping'];
$phone = $profile_details['phone'];
$formatted_address = WC()->countries->get_formatted_address( $address );
$formatted_shipping_address = WC()->countries->get_formatted_address( $shipping_address );
if ( !$formatted_address ) {
$formatted_address = $formatted_address;
}
?>
';
// Left Section Wrapper
echo '';
// Left Side
echo '
';
echo '
' . get_avatar(
( $is_guest ? $customer_email : $user->ID ),
128,
'identicon',
$customer['display_name']
) . '
';
echo '
';
// Left Side2
echo '
';
if ( $is_guest ) {
echo '
' . esc_html( $customer['display_name'] ) . ' ' . esc_html__( 'Guest', 'advanced-customer-reports-woo' ) . ' ';
} else {
echo '
';
}
echo '
';
if ( $customer_email ) {
echo '
' . esc_html__( 'Email:', 'advanced-customer-reports-woo' ) . ' ' . esc_html( $customer_email ) . '
';
}
if ( $phone ) {
echo '
' . esc_html__( 'Phone:', 'advanced-customer-reports-woo' ) . ' ' . esc_html( $phone ) . '
';
}
if ( $is_guest ) {
echo '
' . esc_html__( 'Checked out as a guest, with no customer account on this store.', 'advanced-customer-reports-woo' ) . '
';
} else {
$date_registered = $user->user_registered;
$date_registered = date_i18n( 'F jS, Y', strtotime( $date_registered ) );
echo '
' . esc_html__( 'Registered on', 'advanced-customer-reports-woo' ) . ': ' . esc_html( $date_registered ) . '
';
}
echo '
';
echo '
';
// Right Side
echo '
';
echo '
';
echo 'Billing Address: ';
echo wp_kses_post( $formatted_address );
echo '
';
echo '
';
// Right Side
echo '
';
echo '
';
echo 'Shipping Address: ';
echo wp_kses_post( $formatted_shipping_address );
echo '
';
echo '
';
echo '
';
echo '
Showing report for all customers. ';
echo '';
}
// Get the report data based on the selected date range
$date_range = ( isset( $_POST['date_range'] ) ? sanitize_text_field( wp_unslash( $_POST['date_range'] ) ) : null );
$start_date = ( isset( $_POST['start_date'] ) ? sanitize_text_field( wp_unslash( $_POST['start_date'] ) ) : null );
$end_date = ( isset( $_POST['end_date'] ) ? sanitize_text_field( wp_unslash( $_POST['end_date'] ) ) : null );
if ( acreports_fs()->can_use_premium_code() ) {
if ( $start_date && $end_date ) {
$start_date = date_i18n( 'Y-m-d', strtotime( $start_date ) );
$end_date = date_i18n( 'Y-m-d', strtotime( $end_date ) );
} else {
$start_date = null;
$end_date = date_i18n( 'Y-m-d' );
}
} else {
if ( $date_range == 'this_month' ) {
$today = new DateTime();
$first_day = new DateTime($today->format( 'Y-m-01' ));
$last_day = new DateTime($today->format( 'Y-m-t' ));
$start_date = $first_day->format( 'Y-m-d' );
$end_date = $last_day->format( 'Y-m-d' );
} else {
if ( $date_range == 'last_month' ) {
$today = new DateTime();
$first_day = new DateTime($today->format( 'Y-m-01' ));
$last_day = new DateTime($today->format( 'Y-m-t' ));
$first_day->modify( '-1 month' );
$last_day->modify( '-1 month' );
$start_date = $first_day->format( 'Y-m-d' );
$end_date = $last_day->format( 'Y-m-d' );
} else {
if ( $date_range == 'all_time' ) {
$start_date = null;
$end_date = date_i18n( 'Y-m-d' );
} else {
$start_date = null;
$end_date = date_i18n( 'Y-m-d' );
}
}
}
}
if ( acreports_fs()->can_use_premium_code() ) {
// Status picker
$status = ( isset( $_POST['status'] ) ? sanitize_text_field( wp_unslash( $_POST['status'] ) ) : 'all' );
} else {
$status = 'all';
}
$status = acreports_get_report_order_statuses( $status );
// Sorting by ID gives a stable, unique key, so pages of orders cannot
// overlap or skip rows however the report data is read.
$order_query_args = array(
'date_after' => ( $start_date ? $start_date . ' 00:00:00' : '' ),
'date_before' => ( $end_date ? $end_date . ' 23:59:59' : '' ),
'orderby' => 'ID',
'order' => 'DESC',
'status' => ( $status ? $status : 'all' ),
);
$order_query_args = array_merge( $order_query_args, acreports_get_customer_order_query_args( $customer ) );
// Every figure below is compiled in one pass over the matching orders, read
// from the WooCommerce Analytics tables where they cover the whole set and
// from the orders themselves where they do not.
$report_data = acreports_get_report_data( $order_query_args );
$max_report_orders = acreports_get_max_report_orders();
$order_ids = $report_data['order_ids'];
$orders_truncated = $report_data['orders_truncated'];
$total_orders = $report_data['total_orders'];
$total_spent = $report_data['total_spent'];
$total_refunded = $report_data['total_refunded'];
$total_discount = $report_data['total_discount'];
$total_shipping = $report_data['total_shipping'];
$total_tax = $report_data['total_tax'];
$largest_order_total = $report_data['largest_order_total'];
$smallest_order_total = $report_data['smallest_order_total'];
$total_products = $report_data['total_products'];
$first_order_date = $report_data['first_order_date'];
$last_order_date = $report_data['last_order_date'];
$first_order_summary = $report_data['first_order_summary'];
$last_order_summary = $report_data['last_order_summary'];
$order_timestamps = $report_data['order_timestamps'];
$status_amounts = $report_data['status_amounts'];
$payment_method_amounts = $report_data['payment_method_amounts'];
$payment_method_spend = $report_data['payment_method_spend'];
$payment_method_count = $report_data['payment_method_count'];
$product_stats = $report_data['product_stats'];
$category_stats = $report_data['category_stats'];
$product_affinity = $report_data['product_affinity'];
$coupon_stats = $report_data['coupon_stats'];
$shipping_method_amounts = $report_data['shipping_method_amounts'];
$shipping_method_spend = $report_data['shipping_method_spend'];
$shipping_addresses = $report_data['shipping_addresses'];
$monthly_stats = $report_data['monthly_stats'];
$coupon_order_count = $report_data['coupon_order_count'];
$refunded_order_count = $report_data['refunded_order_count'];
$cancelled_order_count = $report_data['cancelled_order_count'];
$failed_order_count = $report_data['failed_order_count'];
$attribution_origins = $report_data['attribution_origins'];
$attribution_campaigns = $report_data['attribution_campaigns'];
$attribution_devices = $report_data['attribution_devices'];
$attribution_order_count = $report_data['attribution_order_count'];
$order_notes = acreports_get_recent_order_notes( $order_ids, 10 );
uasort( $product_stats, 'acreports_sort_stats_by_quantity_total' );
uasort( $category_stats, 'acreports_sort_stats_by_quantity_total' );
uasort( $coupon_stats, 'acreports_sort_stats_by_quantity_total' );
uasort( $attribution_origins, 'acreports_sort_attribution_buckets' );
uasort( $attribution_campaigns, 'acreports_sort_attribution_buckets' );
uasort( $attribution_devices, 'acreports_sort_attribution_buckets' );
ksort( $monthly_stats );
// First touch is how the customer was won, latest touch is what brought
// them back most recently. They are frequently not the same channel.
$first_touch_attribution = ( $first_order_summary && !empty( $first_order_summary['attribution'] ) ? $first_order_summary['attribution'] : array() );
$last_touch_attribution = ( $last_order_summary && !empty( $last_order_summary['attribution'] ) ? $last_order_summary['attribution'] : array() );
$attribution_unrecorded_count = max( 0, $total_orders - $attribution_order_count );
$attribution_row_limit = acreports_get_attribution_row_limit();
$attribution_origins_shown = array_slice(
$attribution_origins,
0,
$attribution_row_limit,
true
);
$attribution_origins_hidden = count( $attribution_origins ) - count( $attribution_origins_shown );
$attribution_campaigns_shown = array_slice(
$attribution_campaigns,
0,
$attribution_row_limit,
true
);
$attribution_campaigns_hidden = count( $attribution_campaigns ) - count( $attribution_campaigns_shown );
$average_order_value = ( $total_orders ? $total_spent / $total_orders : 0 );
$average_items_per_order = ( $total_orders ? round( $total_products / $total_orders, 2 ) : 0 );
$average_days_between_orders = acreports_get_average_order_gap_days( $order_timestamps );
$days_ago = ( $last_order_date ? $last_order_date->diff( new DateTime() )->days : 0 );
$net_revenue = max( 0, $total_spent - $total_refunded );
$discount_order_rate = acreports_get_percentage( $coupon_order_count, $total_orders );
$refund_rate = acreports_get_percentage( $refunded_order_count, $total_orders );
$status_issue_rate = acreports_get_percentage( $cancelled_order_count + $failed_order_count, $total_orders );
$expected_next_order_date = null;
$days_until_next_order = null;
if ( $last_order_date && $average_days_between_orders ) {
$expected_next_order_date = clone $last_order_date;
$expected_next_order_date->modify( '+' . max( 1, (int) ceil( $average_days_between_orders ) ) . ' days' );
$days_until_next_order = (int) floor( ($expected_next_order_date->getTimestamp() - current_time( 'timestamp' )) / DAY_IN_SECONDS );
}
$store_benchmarks = ( acreports_fs()->can_use_premium_code() ? acreports_get_store_benchmarks(
$start_date,
$end_date,
$status,
$total_spent
) : array() );
$rfm_analysis = ( acreports_fs()->can_use_premium_code() ? acreports_get_rfm_analysis(
$total_orders,
$total_spent,
$days_ago,
( !empty( $store_benchmarks['average_lifetime_value'] ) ? $store_benchmarks['average_lifetime_value'] : 0 ),
$average_days_between_orders,
$discount_order_rate,
$refund_rate
) : null );
$cross_sell_suggestions = ( acreports_fs()->can_use_premium_code() ? acreports_get_cross_sell_suggestions( $product_stats ) : array() );
$first_latest_order_delta = null;
if ( $first_order_summary && $last_order_summary && (int) $first_order_summary['id'] !== (int) $last_order_summary['id'] ) {
$first_latest_order_delta = $last_order_summary['total'] - $first_order_summary['total'];
}
$recommended_actions = ( acreports_fs()->can_use_premium_code() ? acreports_get_customer_recommended_actions( $customer, array(
'total_orders' => $total_orders,
'total_spent' => $total_spent,
'days_since_last_order' => $days_ago,
'average_days_between_orders' => $average_days_between_orders,
'days_until_next_order' => $days_until_next_order,
'discount_order_rate' => $discount_order_rate,
'refund_rate' => $refund_rate,
'status_issue_rate' => $status_issue_rate,
'rfm_score' => ( $rfm_analysis ? $rfm_analysis['score'] : 0 ),
'rfm_segment' => ( $rfm_analysis ? $rfm_analysis['segment'] : '' ),
'top_percent' => ( !empty( $store_benchmarks ) ? $store_benchmarks['top_percent'] : null ),
'has_cross_sell_suggestions' => !empty( $cross_sell_suggestions ),
'cross_sell_suggestions' => $cross_sell_suggestions,
'first_latest_order_delta' => $first_latest_order_delta,
) ) : array() );
// Say so rather than quietly presenting partial totals as if they were complete.
if ( $orders_truncated ) {
echo '' . esc_html( sprintf(
/* translators: %s: number of orders included in the report. */
__( 'This report covers the most recent %s orders. Narrow the date range, or pick a single customer, for totals across every order.', 'advanced-customer-reports-woo' ),
number_format_i18n( $max_report_orders )
) ) . '
';
// Total Orders
echo '
' . esc_html__( 'Total Orders:', 'advanced-customer-reports-woo' ) . '
' . esc_html( $total_orders ) . '
';
// Total Spent
echo '
' . esc_html__( 'Total Spent:', 'advanced-customer-reports-woo' ) . '
' . wp_kses_post( wc_price( $total_spent ) ) . '
';
// Total Products
echo '
' . esc_html__( 'Total Items:', 'advanced-customer-reports-woo' ) . '
' . esc_html( esc_html( $total_products ) ) . '
';
echo '
';
// Last Order Date
echo '
' . esc_html__( 'Last Order:', 'advanced-customer-reports-woo' ) . ' ';
if ( !$total_orders || !$last_order_date ) {
echo esc_html__( 'No orders', 'advanced-customer-reports-woo' );
} else {
if ( $days_ago <= 0 ) {
echo ' Today';
} else {
if ( $days_ago == 1 ) {
echo ' Yesterday';
} else {
echo ' ' . esc_html( $days_ago ) . ' days ago';
}
}
}
echo '
';
// Largest Order
echo '
' . esc_html__( 'Largest Order:', 'advanced-customer-reports-woo' ) . ' ';
if ( $total_orders ) {
echo wp_kses_post( wc_price( $largest_order_total ) );
} else {
echo 0;
}
echo '
';
// Smallest Order
echo '
' . esc_html__( 'Smallest Order:', 'advanced-customer-reports-woo' ) . ' ';
if ( $total_orders ) {
echo wp_kses_post( wc_price( $smallest_order_total ) );
} else {
echo 0;
}
echo '
';
echo '
';
// AOV
echo '
' . esc_html__( 'Average Order Value:', 'advanced-customer-reports-woo' ) . '
' . (( $total_orders ? wp_kses_post( wc_price( $average_order_value ) ) : 0 )) . '
';
// Average Time Between Orders
echo '
' . esc_html__( 'Average Time Between Orders:', 'advanced-customer-reports-woo' ) . ' ';
if ( $average_days_between_orders ) {
echo esc_html( $average_days_between_orders ) . ' ' . esc_html__( 'days', 'advanced-customer-reports-woo' );
} else {
echo 0;
}
echo '
';
// Average Items per Order
echo '
' . esc_html__( 'Average Items Per Order:', 'advanced-customer-reports-woo' ) . '
' . (( $total_orders ? esc_html( $average_items_per_order ) : 0 )) . '
';
echo '
= 0 ? '+' : '-' )) . wc_price( abs( $first_latest_order_delta ) ) ) );
?>
can_use_premium_code() && $rfm_analysis ) {
?>
format( 'M j, Y' ) ) : esc_html__( 'Not enough repeat data', 'advanced-customer-reports-woo' ) );
?>
can_use_premium_code() && !empty( $store_benchmarks ) ) {
?>
$amount ) {
$status_parts[] = "
" . ucfirst( esc_html( $status ) ) . ": " . esc_html( $amount ) . " ";
}
echo wp_kses_post( implode( ' ', $status_parts ) );
?>
can_use_premium_code() ) {
?>
is_free_plan() ) {
?>disabled>
can_use_premium_code() ) {
?>
'acreports-order-activity',
'username' => $username,
'customer_email' => $customer_email,
);
if ( $start_date ) {
$activity_page_args['start_date'] = $start_date;
$activity_page_args['end_date'] = $end_date;
}
$activity_page = add_query_arg( array_filter( $activity_page_args ), admin_url( 'admin.php' ) );
?>
→
can_use_premium_code() ) {
acreports_render_pro_locked_panel( __( 'Subscriptions, recurring revenue and churn', 'advanced-customer-reports-woo' ), __( 'See every subscription this customer has had, what they are worth each month, their renewals and failed payments, and when they ended.', 'advanced-customer-reports-woo' ) );
}
?>
$stats ) {
if ( !$stats['quantity'] || !$stats['total'] || !$product_id ) {
continue;
}
?>
# format( 'M j, Y' ) ) : esc_html__( 'N/A', 'advanced-customer-reports-woo' ) );
?>
$stats ) {
if ( !$stats['quantity'] || !$stats['total'] ) {
continue;
}
?>
# name );
?>
can_use_premium_code() ) {
?>
count( $top_product_affinity ) ) {
echo esc_html( sprintf(
/* translators: %s: number of product pairs shown. */
__( 'The table below shows the top %s product pairings bought together.', 'advanced-customer-reports-woo' ),
number_format_i18n( count( $top_product_affinity ) )
) );
} else {
echo esc_html__( 'The table below shows which products are frequently bought together.', 'advanced-customer-reports-woo' );
}
?>
";
echo "
Product Pair Frequency ";
foreach ( $top_product_affinity as $pair_key => $count ) {
list( $product_id1, $product_id2 ) = explode( '-', $pair_key );
$product_id1 = absint( $product_id1 );
$product_id2 = absint( $product_id2 );
$product_name1 = get_the_title( $product_id1 );
$product_name2 = get_the_title( $product_id2 );
echo "
" . esc_html( $product_name1 ) . " + " . esc_html( $product_name2 ) . " " . esc_html( $count ) . " ";
}
echo "";
?>
can_use_premium_code() ) {
?>
$stats ) {
if ( empty( $stats['order_count'] ) || !$stats['total'] ) {
continue;
}
?>
format( 'M j, Y' ) );
?>
can_use_premium_code() ) {
?>
0 ) {
?>
can_use_premium_code() ) {
?>
$amount ) {
if ( !$amount ) {
continue;
}
// If method exists
if ( isset( WC()->payment_gateways->payment_gateways()[$payment_method] ) ) {
$method_full_name = WC()->payment_gateways->payment_gateways()[$payment_method]->title;
} else {
$method_full_name = $payment_method;
}
if ( !$method_full_name ) {
$method_full_name = $payment_method;
}
if ( !$method_full_name ) {
$method_full_name = 'N/A';
}
$spend = $payment_method_spend[$payment_method];
$count = $payment_method_count[$payment_method];
?>
$date ) {
if ( !$date['total_orders'] ) {
continue;
}
if ( !$address ) {
$address = 'N/A';
}
?>
format( 'M j, Y' ) );
?>
$amount ) {
if ( !$amount ) {
continue;
}
$spend = $shipping_method_spend[$shipping_method];
if ( !$shipping_method ) {
$shipping_method = 'N/A';
}
?>
can_use_premium_code() && $is_guest ) {
?>
can_use_premium_code() ) {
?>
' . esc_html__( 'No customer found.', 'advanced-customer-reports-woo' ) . '
';
wp_die();
}
// Orders Pagination
$orders_per_page = 10;
// Query to get paginated orders
$orders_query_args = [
'date_after' => ( $start_date ? $start_date . ' 00:00:00' : '' ),
'date_before' => ( $end_date ? $end_date . ' 23:59:59' : '' ),
'limit' => $orders_per_page,
'page' => $page,
'paginate' => true,
'orderby' => 'date',
'order' => 'DESC',
'status' => $status,
];
$orders_query_args = array_merge( $orders_query_args, acreports_get_customer_order_query_args( $customer ) );
$orders_query = wc_get_orders( $orders_query_args );
$orders = $orders_query->orders;
$total_orders_count = (int) $orders_query->total;
$total_pages = max( 1, (int) $orders_query->max_num_pages );
ob_start();
?>
can_use_premium_code() ) {
?>
is_free_plan() ) {
?>disabled>
can_use_premium_code() ) {
?>
get_id();
$order_date = $order->get_date_created();
$order_total = $order->get_total();
$order_attribution = acreports_get_order_attribution( $order );
?>
# format( 'M j, Y' ) );
?>
format( 'H:i:s' ) );
?>
get_status() ) );
?>
get_total_tax() ) );
?>
esc_like( $term ) . '%';
if ( acreports_customer_lookup_table_exists() ) {
$results = $wpdb->get_results( $wpdb->prepare(
"\n SELECT DISTINCT u.ID, u.user_login, u.display_name, u.user_email, cl.first_name, cl.last_name\n FROM {$wpdb->users} u\n LEFT JOIN {$wpdb->prefix}wc_customer_lookup cl ON cl.user_id = u.ID\n WHERE u.user_login LIKE %s\n OR u.user_email LIKE %s\n OR u.display_name LIKE %s\n OR u.user_nicename LIKE %s\n OR cl.first_name LIKE %s\n OR cl.last_name LIKE %s\n OR CONCAT_WS(' ', cl.first_name, cl.last_name) LIKE %s\n OR cl.email LIKE %s\n ORDER BY cl.orders_count DESC, cl.total_spent DESC, u.display_name ASC\n LIMIT 20",
$like,
$like,
$like,
$like,
$like,
$like,
$like,
$like
) );
} else {
$results = get_users( array(
'number' => 20,
'search' => '*' . esc_attr( $term ) . '*',
'search_columns' => array(
'user_login',
'user_email',
'display_name',
'user_nicename'
),
) );
}
// Guests only exist in the customer lookup table, as the rows with no user_id.
$guest_results = array();
if ( acreports_customer_lookup_table_exists() ) {
$guest_results = $wpdb->get_results( $wpdb->prepare(
"\n SELECT cl.email, cl.first_name, cl.last_name\n FROM {$wpdb->prefix}wc_customer_lookup cl\n WHERE cl.user_id IS NULL\n AND cl.email <> ''\n AND (\n cl.email LIKE %s\n OR cl.first_name LIKE %s\n OR cl.last_name LIKE %s\n OR CONCAT_WS(' ', cl.first_name, cl.last_name) LIKE %s\n )\n ORDER BY cl.date_last_active DESC\n LIMIT 20",
$like,
$like,
$like,
$like
) );
}
$response = [];
foreach ( $results as $result ) {
$name_parts = array();
if ( !empty( $result->first_name ) ) {
$name_parts[] = $result->first_name;
}
if ( !empty( $result->last_name ) ) {
$name_parts[] = $result->last_name;
}
$customer_name = trim( implode( ' ', $name_parts ) );
if ( '' === $customer_name && !empty( $result->display_name ) ) {
$customer_name = $result->display_name;
}
$label = ( $customer_name ? $customer_name . ' (' . $result->user_login . ')' : $result->user_login );
if ( !empty( $result->user_email ) ) {
$label .= ' - ' . $result->user_email;
}
$response[] = [
'label' => $label,
'value' => $result->user_login,
'id' => $result->ID,
'email' => ( !empty( $result->user_email ) ? $result->user_email : '' ),
'is_guest' => false,
];
}
foreach ( $guest_results as $guest ) {
$guest_email = sanitize_email( $guest->email );
if ( !$guest_email || !is_email( $guest_email ) ) {
continue;
}
$guest_name = trim( trim( (string) $guest->first_name ) . ' ' . trim( (string) $guest->last_name ) );
$label = ( $guest_name ? $guest_name . ' - ' . $guest_email : $guest_email );
$response[] = [
'label' => sprintf( __( '%s (guest)', 'advanced-customer-reports-woo' ), $label ),
'value' => $guest_email,
'id' => 0,
'email' => $guest_email,
'is_guest' => true,
];
}
wp_send_json( $response );
}
 );
?>)
Loading Orders... );
?>)