'paid_facebook', 'paid_fb' => 'paid_facebook', 'paid_ig' => 'paid_facebook', 'paid_fb-sitelink' => 'paid_facebook', 'paid_an' => 'paid_facebook', 'paid_{{site_source_name}}' => 'paid_facebook', 'fb' => 'paid_facebook', 'ga' => 'paid_google', 'googleads.g.doubleclick.net' => 'paid_google', 'google_cpc' => 'paid_google', 'tiktok_ads' => 'paid_tiktok', ) ); } private static function safe_sql_literal( $s ) { return '' !== (string) $s && (bool) preg_match( '/^[A-Za-z0-9 _\-\.\{\}]+$/', (string) $s ); } private static function canonical_value( $type, $value ) { $v = trim( str_replace( '+', ' ', (string) $value ) ); if ( 'channel' !== $type ) { return $v; } $map = self::channel_alias_map(); $k = strtolower( $v ); return isset( $map[ $k ] ) ? (string) $map[ $k ] : $v; } private static function canonical_sql( $type, $col ) { $expr = "TRIM(REPLACE({$col},'+',' '))"; if ( 'channel' !== $type ) { return $expr; } $sql = "CASE LOWER({$expr})"; foreach ( self::channel_alias_map() as $from => $to ) { if ( ! self::safe_sql_literal( $from ) || ! self::safe_sql_literal( $to ) ) { continue; } $sql .= " WHEN '" . esc_sql( strtolower( (string) $from ) ) . "' THEN '" . esc_sql( (string) $to ) . "'"; } return $sql . " ELSE {$expr} END"; } public static function ledger_cost_for( $dimension_type, $dimension_value, $win_start, $win_end ) { global $wpdb; if ( ! in_array( $dimension_type, self::LEDGER_DIMS, true ) ) { return 0.0; } if ( ! class_exists( 'AIEO_Attribution_Cost' ) || ! AIEO_Attribution_Cost::table_exists() ) { return 0.0; } $t = AIEO_Attribution_Cost::table_name(); $canon = self::canonical_sql( $dimension_type, 'dimension_value' ); return (float) $wpdb->get_var( $wpdb->prepare( "SELECT COALESCE(SUM(amount),0) FROM `{$t}` WHERE dimension_type=%s AND {$canon} = %s AND period_start <= %s AND period_end >= %s", $dimension_type, self::canonical_value( $dimension_type, $dimension_value ), $win_end, $win_start ) ); } public function get_id() { return 'roi_wizard'; } public function get_icon() { return 'chart-line'; } public function get_label() { return __( 'Revenue against ad spend', 'ai-eshop-optimizer' ); } public function get_group() { return 'attribution'; } public function get_section() { return 'costs_roi'; } public function get_priority() { return 15; } private $utm = null; private function utm() { if ( null === $this->utm && class_exists( 'AIEO_DMM_UTM_Explorer' ) ) { $this->utm = new AIEO_DMM_UTM_Explorer(); } return $this->utm; } public function register_hooks() { add_action( 'wp_ajax_aieo_roi_values', array( $this, 'ajax_values' ) ); add_action( 'wp_ajax_aieo_roi_value_search', array( $this, 'ajax_value_search' ) ); add_action( 'wp_ajax_aieo_roi_type_search', array( $this, 'ajax_type_search' ) ); add_action( 'wp_ajax_aieo_roi_compute', array( $this, 'ajax_compute' ) ); add_action( 'wp_ajax_aieo_roi_cost_save', array( $this, 'ajax_cost_save' ) ); add_action( 'wp_ajax_aieo_roi_cost_delete', array( $this, 'ajax_cost_delete' ) ); add_action( 'wp_ajax_aieo_roi_cost_import', array( $this, 'ajax_cost_import' ) ); add_action( 'wp_ajax_aieo_roi_cost_list', array( $this, 'ajax_cost_list' ) ); add_action( 'wp_ajax_aieo_roi_cost_export', array( $this, 'ajax_cost_export' ) ); if ( $this->utm() ) { $this->utm()->register_hooks(); } } private function guard() { check_ajax_referer( self::NONCE, 'nonce' ); if ( ! current_user_can( self::CAP ) ) { wp_send_json_error( array( 'msg' => 'forbidden' ), 403 ); } } private function dates() { $to = isset( $_POST['to'] ) ? sanitize_text_field( wp_unslash( $_POST['to'] ) ) : ''; $from = isset( $_POST['from'] ) ? sanitize_text_field( wp_unslash( $_POST['from'] ) ) : ''; $d = '/^\d{4}-\d{2}-\d{2}$/'; if ( ! preg_match( $d, $to ) ) { $to = gmdate( 'Y-m-d' ); } if ( ! preg_match( $d, $from ) ) { $from = gmdate( 'Y-m-d', strtotime( $to . ' -30 days' ) ); } return array( $from, $to ); } private function compare_dates( $from, $to, $mode ) { if ( 'prev' === $mode ) { $days = (int) round( ( strtotime( $to ) - strtotime( $from ) ) / 86400 ) + 1; return array( gmdate( 'Y-m-d', strtotime( $from . ' -' . $days . ' days' ) ), gmdate( 'Y-m-d', strtotime( $from . ' -1 day' ) ) ); } if ( 'yoy' === $mode ) { return array( gmdate( 'Y-m-d', strtotime( $from . ' -1 year' ) ), gmdate( 'Y-m-d', strtotime( $to . ' -1 year' ) ) ); } return array( '', '' ); } private function ledger_dim_for( $type ) { return in_array( $type, self::LEDGER_DIMS, true ) ? $type : ''; } public function ajax_values() { $this->guard(); global $wpdb; $type = isset( $_POST['type'] ) ? sanitize_key( $_POST['type'] ) : ''; $p = $wpdb->prefix; $out = array(); if ( 'offer' === $type && self::reads_ledger() ) { $out = self::ledger_kinds(); } elseif ( 'offer' === $type ) { $kind = self::log_kind_sql(); $out = $wpdb->get_col( "SELECT DISTINCT {$kind} AS v FROM `{$p}aieo_dmm_offer_attributions` WHERE offer_type<>'' ORDER BY v" ); } elseif ( in_array( $type, array( 'channel', 'campaign', 'creative' ), true ) ) { $col = array( 'channel' => 'ordersource', 'campaign' => 'orderutmcampaign', 'creative' => 'orderutmcontent' )[ $type ]; $out = $wpdb->get_col( "SELECT DISTINCT `{$col}` v FROM `{$p}aieo_order_stats` WHERE `{$col}` IS NOT NULL AND `{$col}`<>'' ORDER BY v LIMIT 500" ); } wp_send_json_success( array( 'values' => array_values( array_filter( (array) $out ) ) ) ); } public function ajax_value_search() { $this->guard(); global $wpdb; $p = $wpdb->prefix; $types = isset( $_REQUEST['type'] ) ? array_filter( array_map( 'sanitize_key', (array) wp_unslash( $_REQUEST['type'] ) ) ) : array(); $dims = array( 'channel', 'campaign', 'creative', 'offer' ); $types = array_values( array_intersect( $types, $dims ) ); if ( empty( $types ) ) { $types = $dims; } $q = isset( $_REQUEST['q'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['q'] ) ) : ''; $like = '%' . $wpdb->esc_like( $q ) . '%'; $labels = $this->type_options(); $multi = count( $types ) > 1; $out = array(); $seen = array(); foreach ( $types as $type ) { if ( 'offer' === $type && self::reads_ledger() ) { $vals = self::ledger_kinds( $like ); } elseif ( 'offer' === $type ) { $kind = self::log_kind_sql(); $vals = $wpdb->get_col( $wpdb->prepare( "SELECT DISTINCT {$kind} AS v FROM `{$p}aieo_dmm_offer_attributions` WHERE offer_type<>'' AND {$kind} LIKE %s ORDER BY v LIMIT 100", $like ) ); } else { $col = array( 'channel' => 'ordersource', 'campaign' => 'orderutmcampaign', 'creative' => 'orderutmcontent' )[ $type ]; $vals = $wpdb->get_col( $wpdb->prepare( "SELECT DISTINCT `{$col}` FROM `{$p}aieo_order_stats` WHERE `{$col}` IS NOT NULL AND `{$col}`<>'' AND `{$col}` LIKE %s ORDER BY `{$col}` LIMIT 100", $like ) ); if ( in_array( $type, AIEO_Attribution_Cost::DIMENSIONS, true ) && class_exists( 'AIEO_Attribution_Cost' ) && AIEO_Attribution_Cost::table_exists() ) { $ct = AIEO_Attribution_Cost::table_name(); $cvals = $wpdb->get_col( $wpdb->prepare( "SELECT DISTINCT dimension_value FROM `{$ct}` WHERE dimension_type=%s AND dimension_value<>'' AND dimension_value LIKE %s ORDER BY dimension_value LIMIT 100", $type, $like ) ); $vals = array_merge( (array) $vals, (array) $cvals ); } } $suffix = $multi ? ( ' · ' . wp_strip_all_tags( isset( $labels[ $type ] ) ? $labels[ $type ] : $type ) ) : ''; foreach ( (array) $vals as $v ) { $cv = self::canonical_value( $type, $v ); if ( '' === $cv || isset( $seen[ $cv ] ) ) { continue; } $seen[ $cv ] = 1; $out[] = array( 'value' => $cv, 'label' => $cv . $suffix ); } } wp_send_json( array( 'results' => $out ) ); } public function ajax_type_search() { $this->guard(); $q = isset( $_REQUEST['q'] ) ? mb_strtolower( sanitize_text_field( wp_unslash( $_REQUEST['q'] ) ) ) : ''; $out = array(); foreach ( $this->type_options() as $k => $label ) { if ( '' === $q || false !== mb_strpos( mb_strtolower( $label ), $q ) || false !== strpos( $k, $q ) ) { $out[] = array( 'value' => $k, 'label' => $label ); } } wp_send_json( array( 'results' => $out ) ); } public function ajax_compute() { $this->guard(); $types = isset( $_POST['types'] ) ? array_values( array_filter( array_map( 'sanitize_key', (array) wp_unslash( $_POST['types'] ) ) ) ) : array(); if ( empty( $types ) && isset( $_POST['type'] ) && '' !== $_POST['type'] ) { $types = array( sanitize_key( wp_unslash( $_POST['type'] ) ) ); } $types = array_values( array_intersect( $types, array_keys( $this->type_options() ) ) ); if ( empty( $types ) ) { $types = array( 'reco' ); } $values = isset( $_POST['values'] ) ? array_values( array_filter( array_map( 'sanitize_text_field', (array) wp_unslash( $_POST['values'] ) ), 'strlen' ) ) : array(); if ( empty( $values ) && isset( $_POST['value'] ) && '' !== $_POST['value'] ) { $values = array( sanitize_text_field( wp_unslash( $_POST['value'] ) ) ); } list( $from, $to ) = $this->dates(); $labels = $this->type_options(); $blocks = array(); foreach ( $types as $tp ) { $tv = $values; $brows = $this->compute( $tp, $tv, $from, $to ); $blocks[] = array( 'type' => $tp, 'label' => isset( $labels[ $tp ] ) ? $labels[ $tp ] : $tp, 'rows' => $brows, 'reconcile' => $this->reconcile( $tp, $brows, $from, $to ), ); } $compare = isset( $_POST['compare'] ) ? sanitize_key( wp_unslash( $_POST['compare'] ) ) : ''; $cfrom = ''; $cto = ''; if ( in_array( $compare, array( 'prev', 'yoy' ), true ) ) { list( $cfrom, $cto ) = $this->compare_dates( $from, $to, $compare ); foreach ( $blocks as $bi => $b ) { $map = array(); foreach ( $this->compute( $b['type'], $values, $cfrom, $cto ) as $cr ) { $k = isset( $cr['label'] ) ? (string) $cr['label'] : ( isset( $cr['channel'] ) ? (string) $cr['channel'] : '' ); $map[ $k ] = $cr; } foreach ( $blocks[ $bi ]['rows'] as $ri => $r ) { $k = isset( $r['label'] ) ? (string) $r['label'] : ( isset( $r['channel'] ) ? (string) $r['channel'] : '' ); $c = isset( $map[ $k ] ) ? $map[ $k ] : null; $prv = $c ? (float) $c['revenue'] : 0.0; $pro = $c ? (int) $c['orders'] : 0; $blocks[ $bi ]['rows'][ $ri ]['d_orders'] = $pro > 0 ? round( 100 * ( (int) $r['orders'] - $pro ) / $pro, 1 ) : null; $blocks[ $bi ]['rows'][ $ri ]['d_revenue'] = $prv > 0 ? round( 100 * ( (float) $r['revenue'] - $prv ) / $prv, 1 ) : null; } } } global $wpdb; $margin = class_exists( 'AIEO_DMM' ) ? AIEO_DMM::get_gross_margin() : 0.0; $payload = array( 'blocks' => $blocks, 'type' => $blocks[0]['type'], 'rows' => $blocks[0]['rows'], 'from' => $from, 'to' => $to, 'compare' => $compare, 'cfrom' => $cfrom, 'cto' => $cto, 'ops_period' => class_exists( 'AIEO_DMM' ) ? round( AIEO_DMM::get_ops_cost_for_period( $from, $to ), 2 ) : 0, 'ops_monthly' => class_exists( 'AIEO_DMM' ) ? round( AIEO_DMM::get_ops_cost_monthly(), 2 ) : 0, 'store_revenue' => (float) $wpdb->get_var( $wpdb->prepare( "SELECT COALESCE(SUM(orderspend),0) FROM `{$wpdb->prefix}aieo_order_stats` WHERE orderdate >= %s AND orderdate <= %s", $from, $to ) ), 'margin_pct' => class_exists( 'AIEO_DMM' ) ? round( AIEO_DMM::get_gross_margin_pct(), 1 ) : 0, 'breakeven_roas' => $margin > 0 ? round( 1 / $margin, 2 ) : null, ); foreach ( $payload['blocks'] as $bi => $b ) { $payload['blocks'][ $bi ]['table'] = $this->block_table( $bi, $b, $payload ); } wp_send_json_success( $payload ); } private function compute( $type, $values, $from, $to ) { global $wpdb; $p = $wpdb->prefix; $f = $from . ' 00:00:00'; $t = $to . ' 23:59:59'; $rows = array(); if ( 'reco' === $type ) { $r = $wpdb->get_row( $wpdb->prepare( "SELECT COUNT(*) AS orders, COALESCE(SUM(os.orderspend),0) AS revenue FROM ( SELECT DISTINCT e.order_id FROM `{$p}aieo_addcart_events` e INNER JOIN `{$p}aieo_reco_events` r ON r.session_id = e.session_id AND r.target_pid = e.product_id AND r.ts < e.ts_added AND e.ts_added < DATE_ADD(r.ts, INTERVAL 7 DAY) WHERE e.order_id > 0 AND e.ts_added >= %s AND e.ts_added <= %s ) o INNER JOIN `{$p}aieo_order_stats` os ON os.orderid = o.order_id", $f, $t ) ); $rows[] = $this->mk_row( __( 'Recommendation-assisted', 'ai-eshop-optimizer' ), (int) ( $r->orders ?? 0 ), (float) ( $r->revenue ?? 0 ), 0.0, false ); return $rows; } if ( 'reco_lift' === $type ) { $res = $wpdb->get_results( $wpdb->prepare( "SELECT os.ordersource AS channel, COUNT(*) AS orders, COALESCE(SUM(os.orderspend),0) AS revenue, SUM(CASE WHEN o.order_id IS NOT NULL THEN 1 ELSE 0 END) AS reco_orders, COALESCE(SUM(CASE WHEN o.order_id IS NOT NULL THEN os.orderspend ELSE 0 END),0) AS reco_revenue FROM `{$p}aieo_order_stats` os LEFT JOIN ( SELECT DISTINCT e.order_id FROM `{$p}aieo_addcart_events` e INNER JOIN `{$p}aieo_reco_events` r ON r.session_id = e.session_id AND r.target_pid = e.product_id AND r.ts < e.ts_added AND e.ts_added < DATE_ADD(r.ts, INTERVAL 7 DAY) WHERE e.order_id > 0 AND e.ts_added >= %s AND e.ts_added <= %s ) o ON o.order_id = os.orderid WHERE os.orderdate >= %s AND os.orderdate <= %s AND os.ordersource IS NOT NULL AND os.ordersource <> '' GROUP BY os.ordersource ORDER BY revenue DESC LIMIT 100", $f, $t, $from, $to ) ); foreach ( (array) $res as $r ) { $orders = (int) $r->orders; $reco_o = (int) $r->reco_orders; $rows[] = array( 'channel' => (string) $r->channel, 'orders' => $orders, 'revenue' => round( (float) $r->revenue, 2 ), 'reco_orders' => $reco_o, 'reco_revenue' => round( (float) $r->reco_revenue, 2 ), 'lift_pct' => $orders > 0 ? round( 100 * $reco_o / $orders, 1 ) : 0.0, ); } return $rows; } if ( 'offer' === $type && self::reads_ledger() ) { return $this->ledger_offer_rows( $values, $from, $to ); } if ( 'offer' === $type ) { $where = 'fired_at >= %s AND fired_at <= %s'; $args = array( $f, $t ); if ( ! empty( $values ) ) { $types = self::log_types( (array) $values ); $where .= ' AND offer_type IN (' . implode( ',', array_fill( 0, count( $types ), '%s' ) ) . ')'; $args = array_merge( $args, $types ); } $kind = self::log_kind_sql(); $res = $wpdb->get_results( $wpdb->prepare( "SELECT offer_type, COUNT(*) AS orders, SUM(rev) AS revenue, SUM(cost) AS cost FROM ( SELECT {$kind} AS offer_type, order_id, MAX(order_total) AS rev, SUM(COALESCE(actual_cost,0)) AS cost FROM `{$p}aieo_dmm_offer_attributions` WHERE {$where} GROUP BY {$kind}, order_id ) x GROUP BY offer_type ORDER BY revenue DESC", $args ) ); foreach ( (array) $res as $r ) { $rows[] = $this->mk_row( $r->offer_type, (int) $r->orders, (float) $r->revenue, (float) $r->cost, ( (float) $r->cost ) > 0 ); } return $rows; } $col = array( 'channel' => 'ordersource', 'campaign' => 'orderutmcampaign', 'creative' => 'orderutmcontent' )[ $type ] ?? ''; if ( '' === $col ) { return $rows; } $canon = self::canonical_sql( $type, "`{$col}`" ); $where = "`{$col}` IS NOT NULL AND `{$col}`<>'' AND orderdate >= %s AND orderdate <= %s"; $args = array( $from, $to ); if ( ! empty( $values ) ) { $cv = array_values( array_unique( array_map( function ( $v ) use ( $type ) { return self::canonical_value( $type, $v ); }, $values ) ) ); $where .= ' AND ' . $canon . ' IN (' . implode( ',', array_fill( 0, count( $cv ), '%s' ) ) . ')'; $args = array_merge( $args, $cv ); } $res = $wpdb->get_results( $wpdb->prepare( "SELECT {$canon} AS label, COUNT(*) AS orders, COALESCE(SUM(orderspend),0) AS revenue, COUNT(DISTINCT `{$col}`) AS variant_n, SUBSTRING(GROUP_CONCAT(DISTINCT `{$col}` ORDER BY `{$col}` SEPARATOR ', '),1,300) AS variants FROM `{$p}aieo_order_stats` WHERE {$where} GROUP BY {$canon} ORDER BY revenue DESC LIMIT 100", $args ) ); $dim = $this->ledger_dim_for( $type ); foreach ( (array) $res as $r ) { $cost = self::ledger_cost_for( $dim, $r->label, $from, $to ); $row = $this->mk_row( (string) $r->label, (int) $r->orders, (float) $r->revenue, (float) $cost, $cost > 0 ); if ( (int) $r->variant_n > 1 ) { $row['variants'] = (string) $r->variants; } $rows[] = $row; } return $rows; } private static function reads_ledger() { return class_exists( 'AIEO_DMM_Price_Filter' ) && AIEO_DMM_Price_Filter::resolves(); } private static function site_days_gmt( $from, $to ) { $zone = wp_timezone(); $utc = new DateTimeZone( 'UTC' ); $start = new DateTimeImmutable( $from . ' 00:00:00', $zone ); $end = ( new DateTimeImmutable( $to . ' 00:00:00', $zone ) )->modify( '+1 day' ); return array( $start->setTimezone( $utc )->format( 'Y-m-d H:i:s' ), $end->setTimezone( $utc )->format( 'Y-m-d H:i:s' ) ); } private static function log_kind_sql() { return class_exists( 'AIEO_DMM_Offer_Ids' ) ? AIEO_DMM_Offer_Ids::log_kind_sql( 'offer_type' ) : 'offer_type'; } private static function log_types( array $values ) { $types = array(); foreach ( $values as $value ) { foreach ( class_exists( 'AIEO_DMM_Offer_Ids' ) ? AIEO_DMM_Offer_Ids::log_types( (string) $value ) : array( (string) $value ) as $type ) { $types[ $type ] = $type; } } return array_values( $types ); } private static function ledger_kinds( $like = null ) { global $wpdb; if ( ! self::ledger_ready() ) { return array(); } $t = $wpdb->prefix . 'aieo_dmm_offer_redemptions'; if ( null === $like ) { $kinds = (array) $wpdb->get_col( "SELECT DISTINCT offer_kind FROM `{$t}` WHERE offer_kind <> '' AND offer_kind <> 'role_price' ORDER BY offer_kind" ); } else { $kinds = (array) $wpdb->get_col( $wpdb->prepare( "SELECT DISTINCT offer_kind FROM `{$t}` WHERE offer_kind <> '' AND offer_kind <> 'role_price' AND offer_kind LIKE %s ORDER BY offer_kind LIMIT 100", $like ) ); } return AIEO_DMM_Schema_Gate::failed( 'report_roi' ) ? array() : $kinds; } private static function ledger_ready() { if ( ! class_exists( 'AIEO_DMM_Schema_Gate', false ) ) { require_once dirname( __DIR__ ) . '/services/class-aieo-dmm-schema-gate.php'; } return AIEO_DMM_Schema_Gate::ready( AIEO_DMM_Schema_Gate::LEDGER, 'report_roi' ); } private function ledger_offer_rows( $values, $from, $to ) { global $wpdb; if ( ! self::ledger_ready() ) { return array(); } $t = $wpdb->prefix . 'aieo_dmm_offer_redemptions'; list( $facts ) = AIEO_DMM_Conversions_Registry::facts_source(); list( $start, $end ) = self::site_days_gmt( $from, $to ); $where = "r.status IN ('paid', 'partly_refunded') AND r.role = 'applied' AND r.offer_kind <> 'role_price' AND r.created_gmt >= %s AND r.created_gmt < %s"; $args = array( $start, $end ); if ( ! empty( $values ) ) { $where .= ' AND r.offer_kind IN (' . implode( ',', array_fill( 0, count( $values ), '%s' ) ) . ')'; $args = array_merge( $args, array_values( $values ) ); } $res = $wpdb->get_results( $wpdb->prepare( "SELECT x.offer_kind, COUNT(*) AS orders, SUM(x.rev) AS revenue, SUM(x.cost) AS cost, SUM(x.carried) AS carried, SUM(1 - x.has_facts) AS no_facts FROM ( SELECT r.offer_kind, r.order_id, MAX(COALESCE(c.conversion_value, 0) - COALESCE(c.value_refunded, 0)) AS rev, SUM(CASE WHEN r.offer_kind = 'gift' AND r.cost_basis = 'cogs' AND r.cost_goods IS NOT NULL THEN r.cost_goods ELSE r.reduction END) AS cost, SUM(r.reconstructed) AS carried, MAX(CASE WHEN c.id IS NULL THEN 0 ELSE 1 END) AS has_facts FROM `{$t}` r LEFT JOIN {$facts} c ON c.source_key = CONCAT('order:', r.order_id) WHERE {$where} GROUP BY r.offer_kind, r.order_id ) x GROUP BY x.offer_kind ORDER BY revenue DESC", $args ) ); $rows = array(); $carried = 0; $no_facts = 0; if ( AIEO_DMM_Schema_Gate::failed( 'report_roi' ) ) { $res = array(); } foreach ( (array) $res as $r ) { $rows[] = $this->mk_row( (string) $r->offer_kind, (int) $r->orders, (float) $r->revenue, (float) $r->cost, ( (float) $r->cost ) > 0 ); $carried += (int) $r->carried; $no_facts += (int) $r->no_facts; } $notes = AIEO_DMM_Conversions_Registry::ledger_notes( $carried, $no_facts ); if ( $rows && $notes ) { $rows[0]['notes'] = $notes; } return $rows; } private function reconcile( $type, $rows, $from, $to ) { global $wpdb; $dim = $this->ledger_dim_for( $type ); $out = array( 'unattributed_spend' => 0.0, 'unattributed_values' => 0, 'uncosted_revenue' => 0.0, 'uncosted_rows' => 0, 'ledger_total' => 0.0, ); if ( '' === $dim ) { return $out; } foreach ( (array) $rows as $r ) { if ( empty( $r['cost_known'] ) ) { $out['uncosted_revenue'] += (float) $r['revenue']; $out['uncosted_rows']++; } } $out['uncosted_revenue'] = round( $out['uncosted_revenue'], 2 ); if ( ! class_exists( 'AIEO_Attribution_Cost' ) || ! AIEO_Attribution_Cost::table_exists() ) { return $out; } $p = $wpdb->prefix; $ct = AIEO_Attribution_Cost::table_name(); $col = array( 'channel' => 'ordersource', 'campaign' => 'orderutmcampaign', 'creative' => 'orderutmcontent' )[ $type ]; $CO = 'COLLATE utf8mb4_unicode_520_ci'; $canon_l = self::canonical_sql( $dim, 'l.dimension_value' ); $canon_o = self::canonical_sql( $type, "`{$col}`" ); $agg = $wpdb->get_row( $wpdb->prepare( "SELECT COALESCE(SUM(l.amount),0) AS total, COALESCE(SUM(CASE WHEN m.k IS NULL THEN l.amount ELSE 0 END),0) AS orphan, COUNT(DISTINCT CASE WHEN m.k IS NULL THEN l.dimension_value END) AS orphan_values FROM `{$ct}` l LEFT JOIN ( SELECT DISTINCT {$canon_o} AS k FROM `{$p}aieo_order_stats` WHERE `{$col}` IS NOT NULL AND `{$col}`<>'' AND orderdate >= %s AND orderdate <= %s ) m ON m.k {$CO} = ({$canon_l}) {$CO} WHERE l.dimension_type = %s AND l.period_start <= %s AND l.period_end >= %s", $from, $to, $dim, $to, $from ) ); if ( $agg ) { $out['ledger_total'] = round( (float) $agg->total, 2 ); $out['unattributed_spend'] = round( (float) $agg->orphan, 2 ); $out['unattributed_values'] = (int) $agg->orphan_values; } return $out; } private function mk_row( $label, $orders, $revenue, $cost, $cost_known ) { $net = $revenue - $cost; $roas = $cost > 0 ? round( $revenue / $cost, 2 ) : null; $roi = $cost > 0 ? round( 100 * $net / $cost, 1 ) : null; return array( 'label' => $label, 'orders' => $orders, 'revenue' => round( $revenue, 2 ), 'cost' => round( $cost, 2 ), 'net' => round( $net, 2 ), 'roas' => $roas, 'roi_pct' => $roi, 'cost_known' => (bool) $cost_known, ); } public function ajax_cost_save() { $this->guard(); $dim = isset( $_POST['dimension_type'] ) ? sanitize_key( $_POST['dimension_type'] ) : ''; $val = isset( $_POST['dimension_value'] ) ? sanitize_text_field( wp_unslash( $_POST['dimension_value'] ) ) : ''; $ps = isset( $_POST['period_start'] ) ? sanitize_text_field( wp_unslash( $_POST['period_start'] ) ) : ''; $pe = isset( $_POST['period_end'] ) ? sanitize_text_field( wp_unslash( $_POST['period_end'] ) ) : ''; $amt = isset( $_POST['amount'] ) ? (float) $_POST['amount'] : 0.0; $note = isset( $_POST['note'] ) ? sanitize_text_field( wp_unslash( $_POST['note'] ) ) : ''; $ok = class_exists( 'AIEO_Attribution_Cost' ) && AIEO_Attribution_Cost::upsert( $dim, $val, $ps, $pe, $amt, 'manual', $note ); if ( ! $ok ) { wp_send_json_error( array( 'msg' => __( 'Could not save — check the dimension and dates.', 'ai-eshop-optimizer' ) ) ); } wp_send_json_success( array( 'rows' => $this->cost_rows_for_js( $dim ) ) ); } public function ajax_cost_delete() { $this->guard(); $id = isset( $_POST['id'] ) ? (int) $_POST['id'] : 0; $dim = isset( $_POST['dimension_type'] ) ? sanitize_key( $_POST['dimension_type'] ) : ''; if ( $id && class_exists( 'AIEO_Attribution_Cost' ) ) { AIEO_Attribution_Cost::delete( $id ); } wp_send_json_success( array( 'rows' => $this->cost_rows_for_js( $dim ) ) ); } public function ajax_cost_import() { $this->guard(); $csv = isset( $_POST['csv'] ) ? trim( (string) wp_unslash( $_POST['csv'] ) ) : ''; $dim = isset( $_POST['dimension_type'] ) ? sanitize_key( $_POST['dimension_type'] ) : ''; $n = 0; $err = 0; foreach ( preg_split( '/\r\n|\r|\n/', $csv ) as $line ) { $line = trim( $line ); if ( '' === $line ) { continue; } $c = str_getcsv( $line, ',', '"', '' ); if ( isset( $c[0] ) && in_array( strtolower( trim( $c[0] ) ), array( 'dimension_type', 'type' ), true ) ) { continue; } if ( count( $c ) < 5 ) { $err++; continue; } $ok = class_exists( 'AIEO_Attribution_Cost' ) && AIEO_Attribution_Cost::upsert( sanitize_key( $c[0] ), sanitize_text_field( $c[1] ), sanitize_text_field( $c[2] ), sanitize_text_field( $c[3] ), (float) $c[4], 'csv', isset( $c[5] ) ? sanitize_text_field( $c[5] ) : '' ); $ok ? $n++ : $err++; } wp_send_json_success( array( 'msg' => sprintf( __( 'Imported %1$d rows (%2$d skipped).', 'ai-eshop-optimizer' ), $n, $err ), 'rows' => $this->cost_rows_for_js( $dim ), ) ); } public function ajax_cost_list() { $this->guard(); $dim = isset( $_POST['dimension_type'] ) ? sanitize_key( $_POST['dimension_type'] ) : ''; $ps = isset( $_POST['period_start'] ) ? sanitize_text_field( wp_unslash( $_POST['period_start'] ) ) : ''; $pe = isset( $_POST['period_end'] ) ? sanitize_text_field( wp_unslash( $_POST['period_end'] ) ) : ''; $val = isset( $_POST['dimension_value'] ) ? trim( wp_strip_all_tags( (string) wp_unslash( $_POST['dimension_value'] ) ) ) : ''; $rows = $this->cost_rows_for_js( $dim, $ps, $pe, $val ); wp_send_json_success( array( 'rows' => $rows, 'table' => $this->cost_table( $rows ), ) ); } private function cost_table( array $rows ) { $out = array(); foreach ( $rows as $r ) { $value = (string) $r['value']; $out[] = array( '_id' => (string) $r['id'], '_name' => $value, 'value' => $value, 'from' => (string) $r['start'], 'to' => (string) $r['end'], 'amount' => AIEO_Table_Columns::money( $r['amount'] ), 'source' => (string) $r['source'], 'act' => array( array( 'label' => __( 'Delete', 'ai-eshop-optimizer' ), 'destructive' => true, 'attrs' => array( 'data-del' => (string) $r['id'], 'aria-haspopup' => 'dialog', ), ), ), '_s' => array( 'value' => $value, 'from' => (string) $r['start'], 'to' => (string) $r['end'], 'amount' => (float) $r['amount'], 'source' => (string) $r['source'], ), '_text' => $value, ); } return AIEO_Table::local_answer( 'aieo-cost-table', array( array( 'key' => 'value', 'label' => __( 'Value', 'ai-eshop-optimizer' ), 'sort_type' => 'text' ), array( 'key' => 'from', 'label' => __( 'From', 'ai-eshop-optimizer' ), 'type' => 'date', 'sort_type' => 'date' ), array( 'key' => 'to', 'label' => __( 'To', 'ai-eshop-optimizer' ), 'type' => 'date', 'sort_type' => 'date' ), array( 'key' => 'amount', 'label' => __( 'Amount', 'ai-eshop-optimizer' ), 'type' => 'num', 'sort_type' => 'number' ), array( 'key' => 'source', 'label' => __( 'Source', 'ai-eshop-optimizer' ), 'sort_type' => 'text' ), array( 'key' => 'act', 'label' => __( 'Actions', 'ai-eshop-optimizer' ), 'type' => 'actions' ), ), $out, array( 'prefix' => 'cost_', 'size' => 0, 'caption' => __( 'Ad-spend ledger', 'ai-eshop-optimizer' ), ) ); } private static function delta_cell( $cell, $delta ) { if ( null === $delta ) { return $cell; } $cell = is_array( $cell ) ? $cell : array( 'html' => esc_html( (string) $cell ) ); $cell['html'] = $cell['html'] . ' ' . esc_html( '(' . ( $delta >= 0 ? '+' : '' ) . number_format_i18n( (float) $delta, 1 ) . '%)' ); $cell['tone'] = $delta >= 0 ? 'success' : 'destructive'; return $cell; } private static function roas_cell( $roas, $breakeven ) { if ( null === $roas ) { return __( 'n/a', 'ai-eshop-optimizer' ); } return number_format_i18n( (float) $roas, 2 ) . '×'; } private function block_table( $bi, array $b, array $payload ) { $rows = (array) ( $b['rows'] ?? array() ); $id = 'aieo-roi-block-' . ( $bi + 1 ); $opts = array( 'prefix' => 'roi' . ( $bi + 1 ) . '_', 'size' => 0, 'caption' => (string) ( $b['label'] ?? $b['type'] ), ); if ( 'reco_lift' === ( $b['type'] ?? '' ) ) { $out = array(); foreach ( $rows as $i => $r ) { $channel = '' !== (string) ( $r['channel'] ?? '' ) ? (string) $r['channel'] : '(none)'; $out[] = array( '_id' => (string) ( $i + 1 ), 'channel' => $channel, 'orders' => self::delta_cell( number_format_i18n( (int) $r['orders'] ), $r['d_orders'] ?? null ), 'revenue' => self::delta_cell( AIEO_Table_Columns::money( $r['revenue'] ), $r['d_revenue'] ?? null ), 'reco_orders' => number_format_i18n( (int) $r['reco_orders'] ), 'reco_revenue' => AIEO_Table_Columns::money( $r['reco_revenue'] ), 'lift' => array( 'html' => '' . esc_html( number_format_i18n( (float) $r['lift_pct'], 1 ) . '%' ) . '' ), '_s' => array( 'channel' => $channel, 'orders' => (int) $r['orders'], 'revenue' => (float) $r['revenue'], 'reco_orders' => (int) $r['reco_orders'], 'reco_revenue' => (float) $r['reco_revenue'], 'lift' => (float) $r['lift_pct'], ), ); } return AIEO_Table::local_answer( $id, array( array( 'key' => 'channel', 'label' => __( 'Channel (origin)', 'ai-eshop-optimizer' ), 'sort_type' => 'text' ), array( 'key' => 'orders', 'label' => __( 'Orders', 'ai-eshop-optimizer' ), 'type' => 'num', 'sort_type' => 'number' ), array( 'key' => 'revenue', 'label' => __( 'Revenue', 'ai-eshop-optimizer' ), 'type' => 'num', 'sort_type' => 'number' ), array( 'key' => 'reco_orders', 'label' => __( 'Reco-assisted orders', 'ai-eshop-optimizer' ), 'type' => 'num', 'sort_type' => 'number' ), array( 'key' => 'reco_revenue', 'label' => __( 'Reco-assisted revenue', 'ai-eshop-optimizer' ), 'type' => 'num', 'sort_type' => 'number' ), array( 'key' => 'lift', 'label' => __( 'Reco lift', 'ai-eshop-optimizer' ), 'type' => 'num', 'sort_type' => 'number' ), ), $out, $opts + array( 'empty' => esc_html__( 'No data for this period.', 'ai-eshop-optimizer' ) ) ); } $be = isset( $payload['breakeven_roas'] ) && null !== $payload['breakeven_roas'] ? (float) $payload['breakeven_roas'] : null; $na = __( 'n/a', 'ai-eshop-optimizer' ); $out = array(); $t = array( 'orders' => 0, 'revenue' => 0.0, 'cost' => 0.0, 'net' => 0.0, 'costed' => false, ); foreach ( $rows as $i => $r ) { $known = ! empty( $r['cost_known'] ); $t['orders'] += (int) $r['orders']; $t['revenue'] += (float) $r['revenue']; $t['net'] += (float) $r['net']; $t['cost'] += $known ? (float) $r['cost'] : 0.0; $t['costed'] = $t['costed'] || $known; $label = (string) ( $r['label'] ?? '' ); $row = array( '_id' => (string) ( $i + 1 ), 'dim' => $label, 'orders' => self::delta_cell( number_format_i18n( (int) $r['orders'] ), $r['d_orders'] ?? null ), 'revenue' => self::delta_cell( AIEO_Table_Columns::money( $r['revenue'] ), $r['d_revenue'] ?? null ), 'cost' => $known ? AIEO_Table_Columns::money( $r['cost'] ) : '—', 'net' => AIEO_Table_Columns::money( $r['net'] ), 'roas' => self::roas_cell( isset( $r['roas'] ) ? $r['roas'] : null, $be ), 'roi' => null === ( $r['roi_pct'] ?? null ) ? $na : number_format_i18n( (float) $r['roi_pct'], 1 ) . '%', 'troi' => '—', '_s' => array( 'dim' => $label, 'orders' => (int) $r['orders'], 'revenue' => (float) $r['revenue'], 'net' => (float) $r['net'], ), ); if ( $known ) { $row['_s']['cost'] = (float) $r['cost']; } if ( isset( $r['roas'] ) ) { $row['_s']['roas'] = (float) $r['roas']; } if ( isset( $r['roi_pct'] ) ) { $row['_s']['roi'] = (float) $r['roi_pct']; } if ( ! empty( $r['variants'] ) ) { $row['dim'] = array( 'html' => esc_html( $label ), 'tip' => sprintf( __( 'Folded from: %s', 'ai-eshop-optimizer' ), (string) $r['variants'] ), ); } $out[] = $row; } $opts += array( 'empty' => esc_html__( 'No data for this selection / period.', 'ai-eshop-optimizer' ) ); if ( $rows ) { $ops = (float) ( $payload['ops_period'] ?? 0 ); $store = (float) ( $payload['store_revenue'] ?? 0 ); $by_cost = $t['costed'] && $t['cost'] > 0; $share = $store > 0 ? min( 1, $t['revenue'] / $store ) : ( $t['revenue'] > 0 ? 1 : 0 ); $scoped = $ops * $share; $spend = $t['cost'] + $scoped; $true = $spend > 0 ? esc_html( number_format_i18n( round( 1000 * ( $t['revenue'] - $spend ) / $spend ) / 10, 1 ) . '%' ) : esc_html( $na ); $why = $ops > 0 ? sprintf( __( 'Includes %1$s operations overhead: %2$s%% of the %3$s this period, matching this view\'s share of store revenue.', 'ai-eshop-optimizer' ), wp_strip_all_tags( AIEO_Table_Columns::money( $scoped )['html'] ), number_format_i18n( round( 1000 * $share ) / 10, 1 ), wp_strip_all_tags( AIEO_Table_Columns::money( $ops )['html'] ) ) : __( 'No operations overhead configured.', 'ai-eshop-optimizer' ); $opts += array( 'totals_label' => __( 'Total', 'ai-eshop-optimizer' ), 'totals' => array( 'orders' => $t['orders'], 'revenue' => $t['revenue'], 'cost' => $t['costed'] ? $t['cost'] : '—', 'net' => $t['net'], 'troi' => array( 'html' => '' . $true . '' ), ) + ( $by_cost ? array() : array( 'roas' => $na, 'roi' => $na, ) ), ); } $answer = AIEO_Table::local_answer( $id, array( array( 'key' => 'dim', 'label' => (string) ( $b['label'] ?? $b['type'] ), 'sort_type' => 'text' ), array( 'key' => 'orders', 'label' => __( 'Orders', 'ai-eshop-optimizer' ), 'type' => 'num', 'sort_type' => 'number' ), array( 'key' => 'revenue', 'label' => __( 'Revenue', 'ai-eshop-optimizer' ), 'type' => 'num', 'sort_type' => 'number', 'format' => 'money' ), array( 'key' => 'cost', 'label' => __( 'Cost', 'ai-eshop-optimizer' ), 'type' => 'num', 'sort_type' => 'number', 'format' => 'money' ), array( 'key' => 'net', 'label' => __( 'Net', 'ai-eshop-optimizer' ), 'type' => 'num', 'sort_type' => 'number', 'format' => 'money' ), array( 'key' => 'roas', 'label' => __( 'ROAS (gross)', 'ai-eshop-optimizer' ), 'type' => 'num', 'sort_type' => 'number', 'total' => array( 'ratio' => array( 'revenue', 'cost' ), 'decimals' => 2, ), 'format' => array( 'suffix' => '×' ), 'bands' => null !== $be ? array( array( 'below' => $be, 'tone' => 'destructive', 'label' => __( 'below break-even', 'ai-eshop-optimizer' ) ), array( 'tone' => 'success', 'label' => __( 'above break-even', 'ai-eshop-optimizer' ) ), ) : array(), 'tip' => null !== $be ? sprintf( __( 'On GROSS revenue. Break-even is %1$s× at a %2$s%% gross margin.', 'ai-eshop-optimizer' ), number_format_i18n( $be, 2 ), number_format_i18n( (float) ( $payload['margin_pct'] ?? 0 ), 1 ) ) : '', ), array( 'key' => 'roi', 'label' => __( 'ROI %', 'ai-eshop-optimizer' ), 'type' => 'num', 'sort_type' => 'number', 'total' => array( 'ratio' => array( 'net', 'cost' ), 'percent' => true, 'decimals' => 1, ), 'format' => array( 'suffix' => '%' ), ), array( 'key' => 'troi', 'label' => __( 'True ROI', 'ai-eshop-optimizer' ), 'type' => 'num' ), ), $out, $opts ); $notes = isset( $rows[0]['notes'] ) && is_array( $rows[0]['notes'] ) ? $rows[0]['notes'] : array(); if ( $notes ) { $answer['html'] .= '

' . esc_html( implode( ' ', $notes ) ) . '

'; } return $answer; } private function cost_rows_for_js( $dim, $win_start = '', $win_end = '', $value = '' ) { if ( ! class_exists( 'AIEO_Attribution_Cost' ) ) { return array(); } $out = array(); foreach ( AIEO_Attribution_Cost::rows( $dim, 2000, $win_start, $win_end, $value ) as $r ) { $out[] = array( 'id' => (int) $r['id'], 'type' => $r['dimension_type'], 'value' => $r['dimension_value'], 'start' => $r['period_start'], 'end' => $r['period_end'], 'amount' => $r['amount'], 'source' => $r['source'], 'note' => $r['note'], ); } return $out; } public function ajax_cost_export() { $this->guard(); $dim = isset( $_REQUEST['dimension_type'] ) ? sanitize_key( wp_unslash( $_REQUEST['dimension_type'] ) ) : ''; $ps = isset( $_REQUEST['period_start'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['period_start'] ) ) : ''; $pe = isset( $_REQUEST['period_end'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['period_end'] ) ) : ''; $val = isset( $_REQUEST['dimension_value'] ) ? trim( wp_strip_all_tags( (string) wp_unslash( $_REQUEST['dimension_value'] ) ) ) : ''; if ( ! class_exists( 'AIEO_Attribution_Cost' ) ) { wp_die( esc_html__( 'Ad-spend ledger unavailable.', 'ai-eshop-optimizer' ) ); } if ( ! class_exists( '\Rooster\Admin\Export' ) && function_exists( 'rooster_admin_builders' ) ) { rooster_admin_builders(); } if ( ! class_exists( '\Rooster\Admin\Export' ) ) { wp_die( esc_html__( 'XLSX writer unavailable.', 'ai-eshop-optimizer' ) ); } $rows = AIEO_Attribution_Cost::rows( $dim, 10000, $ps, $pe, $val ); $columns = array( 'dimension' => __( 'Dimension', 'ai-eshop-optimizer' ), 'value' => __( 'Value', 'ai-eshop-optimizer' ), 'from' => array( 'label' => __( 'From', 'ai-eshop-optimizer' ), 'type' => 'date' ), 'to' => array( 'label' => __( 'To', 'ai-eshop-optimizer' ), 'type' => 'date' ), 'amount' => array( 'label' => __( 'Amount', 'ai-eshop-optimizer' ), 'format' => array( 'decimals' => 2 ) ), 'currency' => __( 'Currency', 'ai-eshop-optimizer' ), 'source' => __( 'Source', 'ai-eshop-optimizer' ), 'note' => __( 'Note', 'ai-eshop-optimizer' ), 'updated' => array( 'label' => __( 'Updated', 'ai-eshop-optimizer' ), 'type' => 'date' ), ); $lines = array(); foreach ( (array) $rows as $r ) { $lines[] = array( 'dimension' => (string) $r['dimension_type'], 'value' => (string) $r['dimension_value'], 'from' => (string) $r['period_start'], 'to' => (string) $r['period_end'], 'amount' => (float) $r['amount'], 'currency' => (string) $r['currency'], 'source' => (string) $r['source'], 'note' => (string) $r['note'], 'updated' => (string) $r['updated_at'], ); } $fname = 'aieo-ad-spend' . ( '' !== $dim ? '-' . $dim : '-all' ) . ( ( '' !== $ps && '' !== $pe ) ? '-' . $ps . '_' . $pe : '' ) . '.xlsx'; \Rooster\Admin\Export::xlsx( $fname, array( array( 'name' => __( 'Ad-spend ledger', 'ai-eshop-optimizer' ), 'columns' => $columns, 'rows' => $lines ) ) ); exit; } private function type_options() { return array( 'reco' => __( 'Recommendation engine', 'ai-eshop-optimizer' ), 'reco_lift' => __( 'Reco lift per channel', 'ai-eshop-optimizer' ), 'offer' => __( 'Offer / mechanism', 'ai-eshop-optimizer' ), 'channel' => __( 'Marketing channel', 'ai-eshop-optimizer' ), 'campaign' => __( 'Campaign', 'ai-eshop-optimizer' ), 'creative' => __( 'Creative', 'ai-eshop-optimizer' ), ); } private function choice_pickers( $nonce, $pre_type, $pre_value ) { $labels = $this->type_options(); $types = '' !== $pre_type ? array( array( 'value' => $pre_type, 'label' => wp_strip_all_tags( $labels[ $pre_type ] ?? $pre_type ) ) ) : array(); $values = '' !== $pre_value ? array( array( 'value' => $pre_value, 'label' => $pre_value ) ) : array(); $words = array( 'type' => array( __( 'Choose one or more types', 'ai-eshop-optimizer' ), __( 'Recommendation engine (default)', 'ai-eshop-optimizer' ), __( 'Search types…', 'ai-eshop-optimizer' ) ), 'value' => array( __( 'Choose values', 'ai-eshop-optimizer' ), __( '— all detected —', 'ai-eshop-optimizer' ), __( 'Search detected values…', 'ai-eshop-optimizer' ) ), ); $out = array( 'dialogs' => '' ); foreach ( array( 'type' => $types, 'value' => $values ) as $key => $chosen ) { $out[ $key ] = AIEO_DMM_UI::pick_field( 'aieo-roi-' . $key, $words[ $key ][0], $words[ $key ][1], $chosen ); $out['dialogs'] .= \Rooster\Admin\Ui::picker( 'aieo-roi-' . $key, $words[ $key ][0], $chosen, array( 'name' => 'aieo_roi_' . $key . 's', 'search' => $words[ $key ][2], 'empty' => $words[ $key ][1], 'source' => AIEO_DMM_UI::values_source( 'type' === $key ? 'aieo_roi_type_search' : 'aieo_roi_value_search', $nonce ), ) ); } return $out; } public function render_subtab() { if ( ! class_exists( 'AIEO_DMM_Schema_Gate', false ) ) { require_once dirname( __DIR__ ) . '/services/class-aieo-dmm-schema-gate.php'; } echo AIEO_DMM_Schema_Gate::notice_html(); $nonce = wp_create_nonce( self::NONCE ); $def_from = gmdate( 'Y-m-d', strtotime( '-30 days' ) ); $def_to = gmdate( 'Y-m-d' ); $pre_type = isset( $_GET['roi_type'] ) ? sanitize_key( wp_unslash( $_GET['roi_type'] ) ) : ''; $pre_value = isset( $_GET['roi_value'] ) ? trim( wp_strip_all_tags( (string) wp_unslash( $_GET['roi_value'] ) ) ) : ''; if ( ! array_key_exists( $pre_type, $this->type_options() ) ) { $pre_type = ''; $pre_value = ''; } if ( '' !== $pre_type ) { $gf = isset( $_GET['roi_from'] ) ? sanitize_text_field( wp_unslash( $_GET['roi_from'] ) ) : ''; $gt = isset( $_GET['roi_to'] ) ? sanitize_text_field( wp_unslash( $_GET['roi_to'] ) ) : ''; if ( preg_match( '/^\d{4}-\d{2}-\d{2}$/', $gf ) && preg_match( '/^\d{4}-\d{2}-\d{2}$/', $gt ) && $gf <= $gt ) { $def_from = $gf; $def_to = $gt; } } $choices = $this->choice_pickers( $nonce, $pre_type, $pre_value ); $bands = function_exists( 'rooster_admin_band_open' ); if ( $bands ) { rooster_admin_band_open( array( 'cols' => 1, 'key' => 'roi-wizard', ) ); } $this->render_origin_card(); list( $open, $close ) = AIEO_DMM_UI::card( array( 'anchor' => 'roi-wizard-overview-2', 'icon' => 'chart-line', 'title' => __( 'ROI calculator', 'ai-eshop-optimizer' ), 'description' => __( 'Pick a type, an exact value and a date range, then weigh the revenue and attributed sales against the ad spend you entered or imported.', 'ai-eshop-optimizer' ), ) ); echo $open; \Rooster\Admin\States::enqueue(); echo \Rooster\Admin\Guidance::text( __( 'Pick a type, the exact detected value, and a period. Rooster One is your single ROI cockpit: revenue (the order we compute) plus your own recommendation / offer attribution, weighed against ad spend you enter or import.', 'ai-eshop-optimizer' ) ); echo '
'; echo \Rooster\Admin\Ui::filter( __( 'From', 'ai-eshop-optimizer' ), \Rooster\Admin\Ui::input( '', $def_from, array( 'type' => 'date', 'id' => 'aieo-roi-from' ) ), 'aieo-roi-from' ); echo \Rooster\Admin\Ui::filter( __( 'To', 'ai-eshop-optimizer' ), \Rooster\Admin\Ui::input( '', $def_to, array( 'type' => 'date', 'id' => 'aieo-roi-to' ) ), 'aieo-roi-to' ); echo \Rooster\Admin\Ui::filter( __( 'Type', 'ai-eshop-optimizer' ), $choices['type'], 'aieo-roi-type-open' ); echo \Rooster\Admin\Ui::filter( __( 'Exact', 'ai-eshop-optimizer' ), $choices['value'], 'aieo-roi-value-open' ); $compare = \Rooster\Admin\Ui::select( '', array( '' => __( '— off —', 'ai-eshop-optimizer' ), 'prev' => __( 'Previous period', 'ai-eshop-optimizer' ), 'yoy' => __( 'Same period last year', 'ai-eshop-optimizer' ), ), '', array( 'id' => 'aieo-roi-compare' ) ); echo \Rooster\Admin\Ui::filter( __( 'Compare', 'ai-eshop-optimizer' ), $compare, 'aieo-roi-compare' ); echo '
'; echo '
'; echo $choices['dialogs']; echo \Rooster\Admin\States::result_head( '', array( 'id' => 'aieo-roi-result', 'attrs' => array( 'hidden' => true ) ) ); echo '
'; echo ''; echo ''; echo ''; echo ''; AIEO_Table::table_assets( array( 'totals' ) ); echo $close; if ( $bands ) { rooster_admin_band_close(); rooster_admin_band_open( array( 'cols' => 1, 'key' => 'roi-wizard-utm', ) ); } if ( $this->utm() ) { if ( method_exists( $this->utm(), 'render_card' ) ) { $this->utm()->render_card( false ); } else { $this->utm()->render_subtab(); } } if ( $bands ) { rooster_admin_band_close(); rooster_admin_band_open( array( 'cols' => 1, 'key' => 'roi-wizard-ledger', ) ); } list( $open, $close ) = AIEO_DMM_UI::card( array( 'anchor' => 'roi-wizard-ad-spend-ledger', 'icon' => 'scroll-text', 'title' => __( 'Ad-spend ledger', 'ai-eshop-optimizer' ), 'description' => __( 'Enter spend per channel / campaign / creative per period. Bulk option: paste a CSV with the columns dimension_type, dimension_value, period_start, period_end, amount and, optionally, note. Offers carry their real cost automatically; the recommendation engine is revenue-first.', 'ai-eshop-optimizer' ), ) ); echo $open; $dims = array_intersect_key( $this->type_options(), array_flip( array( 'channel', 'campaign', 'creative' ) ) ); $cost_dim = 'channel'; echo '
'; echo \Rooster\Admin\Ui::filter( __( 'From', 'ai-eshop-optimizer' ), \Rooster\Admin\Ui::input( '', '', array( 'type' => 'date', 'id' => 'aieo-cost-from' ) ), 'aieo-cost-from' ); echo \Rooster\Admin\Ui::filter( __( 'To', 'ai-eshop-optimizer' ), \Rooster\Admin\Ui::input( '', '', array( 'type' => 'date', 'id' => 'aieo-cost-to' ) ), 'aieo-cost-to' ); echo \Rooster\Admin\Ui::filter( __( 'Dimension', 'ai-eshop-optimizer' ), \Rooster\Admin\Ui::select( '', $dims, $cost_dim, array( 'id' => 'aieo-cost-dim' ) ), 'aieo-cost-dim' ); $cost_val = \Rooster\Admin\Ui::picker( 'aieo-cost-val', __( 'Choose a value', 'ai-eshop-optimizer' ), array(), array( 'multiple' => false, 'name' => 'aieo_cost_value', 'search' => __( 'Search…', 'ai-eshop-optimizer' ), 'empty' => __( 'pick a value…', 'ai-eshop-optimizer' ), 'source' => AIEO_DMM_UI::values_source( 'aieo_roi_value_search', $nonce, array( 'type' => $cost_dim ) ), ) ); echo \Rooster\Admin\Ui::filter( __( 'Value', 'ai-eshop-optimizer' ), AIEO_DMM_UI::pick_field( 'aieo-cost-val', __( 'Choose a value', 'ai-eshop-optimizer' ), __( 'pick a value…', 'ai-eshop-optimizer' ) ), 'aieo-cost-val-open' ); $symbol = function_exists( 'get_woocommerce_currency_symbol' ) ? html_entity_decode( get_woocommerce_currency_symbol(), ENT_QUOTES, 'UTF-8' ) : ''; echo \Rooster\Admin\Ui::filter( __( 'Amount', 'ai-eshop-optimizer' ), \Rooster\Admin\Ui::input( '', '', array( 'type' => 'number', 'id' => 'aieo-cost-amt', 'step' => 'any', 'short' => true, 'unit' => $symbol ) ), 'aieo-cost-amt' ); echo '
'; echo '
'; echo $cost_val; ob_start(); echo '

' . \Rooster\Admin\Ui::textarea( '', '', array( 'id' => 'aieo-cost-csv', 'rows' => 5, 'code' => true, 'placeholder' => 'channel,paid_facebook,2026-06-01,2026-06-30,500,June FB', 'aria-label' => __( 'Paste CSV to bulk import', 'ai-eshop-optimizer' ) ) ) . '

'; echo '

' . esc_html__( 'Export to Excel (.xlsx)', 'ai-eshop-optimizer' ) . ' ' . esc_html__( '(respects the dimension + From/To filter)', 'ai-eshop-optimizer' ) . '

'; echo \Rooster\Admin\States::result_head( '', array( 'id' => 'aieo-cost-import-result', 'attrs' => array( 'hidden' => true ) ) ); echo \Rooster\Admin\Ui::fold( __( 'Paste CSV to bulk import', 'ai-eshop-optimizer' ), (string) ob_get_clean() ); echo \Rooster\Admin\States::result_head( '', array( 'id' => 'aieo-cost-result', 'attrs' => array( 'hidden' => true ) ) ); echo '
'; echo ''; echo ''; echo ''; $del_ask = \Rooster\Admin\States::confirm( 'aieo-roi-cost-delete-ask', 'replace', __( 'Delete this spend row?', 'ai-eshop-optimizer' ), '

' . sprintf( esc_html__( 'Removes %s from the ad-spend ledger: the ROI figures stop counting its spend.', 'ai-eshop-optimizer' ), esc_html( sprintf( _n( '%s spend row', '%s spend rows', 1, 'ai-eshop-optimizer' ), number_format_i18n( 1 ) ) ) ) . '

', array( 'confirm' => __( 'Delete', 'ai-eshop-optimizer' ) ) ); echo $del_ask; echo $close; if ( $bands ) { rooster_admin_band_close(); } $this->print_js( $nonce, '' !== $pre_type ); } private function print_js( $nonce, $autorun = false ) { $ajax = admin_url( 'admin-ajax.php' ); ?> 0 ? round( 100 / $margin_pct, 2 ) : null; list( $open, $close ) = AIEO_DMM_UI::card( array( 'anchor' => 'roi-wizard-who-drove-the-revenue-rooster-vs-your-own-ca', 'icon' => 'users-round', 'title' => __( 'Rooster vs your own campaigns', 'ai-eshop-optimizer' ), 'description' => __( 'Revenue per initiative, ad spend and gross ROAS, split between the work you created yourself and the work Rooster drafted or proposed.', 'ai-eshop-optimizer' ), 'footnote' => $total_init < 1 ? '' : __( 'Rooster-driven = proposed by the intelligence and accepted by you. Rooster-assisted = you chose the subject, Rooster wrote and optimised it. Revenue uses the same last-click and coupon attribution as everything else in this cockpit, so no group gets a friendlier rule than another. Social posts and boosts carry no revenue of their own (their sales count for the campaign their links name), so the revenue per initiative leaves them out.', 'ai-eshop-optimizer' ), 'flush' => $total_init >= 1, ) ); echo $open; echo \Rooster\Admin\Guidance::text( __( 'Every campaign, push, post and boost is tagged by who created it: the ones you built yourself, the ones Rooster drafted for you, and the ones Rooster proposed and you accepted. Same attribution, same period, same rules — so the comparison is like-for-like.', 'ai-eshop-optimizer' ) ); if ( $total_init < 1 ) { echo \Rooster\Admin\States::state( 'first-use', array( 'title' => __( 'No initiatives measured yet. Once campaigns have run, this card shows the split.', 'ai-eshop-optimizer' ) ) ); echo $close; return; } if ( ! empty( $l['comparable'] ) && null !== $l['multiple'] ) { $money = static function ( $v ) { return function_exists( 'wc_price' ) ? html_entity_decode( wp_strip_all_tags( wc_price( (float) $v ) ), ENT_QUOTES, 'UTF-8' ) : number_format_i18n( (float) $v, 2 ); }; $ahead = (float) $l['multiple'] >= 1; $verdict = $ahead ? sprintf( __( 'The work Rooster proposed or drafted earned %1$s per initiative, against %2$s for the work you built yourself.', 'ai-eshop-optimizer' ), $money( $l['rooster_per_initiative'] ), $money( $l['own_per_initiative'] ) ) : sprintf( __( 'The work you built yourself earned %1$s per initiative, against %2$s for the work Rooster proposed or drafted. Worth reviewing which proposals you accept.', 'ai-eshop-optimizer' ), $money( $l['own_per_initiative'] ), $money( $l['rooster_per_initiative'] ) ); echo \Rooster\Admin\Ui::notice( esc_html( $verdict ), $ahead ? 'success' : 'warning', array( 'inline' => true ) ); } else { $rooster_init = (int) ( $l['rooster_earning'] ?? 0 ); $own_init = (int) ( $l['own_earning'] ?? 0 ); if ( $rooster_init < 1 && $own_init > 0 && (int) ( $l['rooster_initiatives'] ?? 0 ) > 0 ) { $msg = __( 'Rooster\'s work so far is social posts and boosts, which carry no revenue of their own here: their sales count for the campaign their links name. Once a campaign Rooster proposed or drafted has run, this card compares it with your own.', 'ai-eshop-optimizer' ); } elseif ( $rooster_init < 1 && $own_init > 0 ) { $msg = __( 'Nothing Rooster proposed or drafted has run yet, so there is nothing to compare. Accept a proposal from the slate: once it has results, this card shows it against your own campaigns on identical terms.', 'ai-eshop-optimizer' ); } elseif ( $own_init < 1 && $rooster_init > 0 ) { $msg = __( 'Only work Rooster proposed or drafted has run so far, so there is no baseline of your own to compare against yet.', 'ai-eshop-optimizer' ); } else { $msg = __( 'Not enough measured activity yet to compare.', 'ai-eshop-optimizer' ); } echo \Rooster\Admin\Ui::notice( esc_html( $msg ), 'info', array( 'inline' => true ) ); } $aieo_roi_rows = array(); foreach ( $b as $slug => $x ) { if ( (int) $x['initiatives'] < 1 ) { continue; } $aieo_roi_rows[] = array( 'slug' => (string) $slug, 'label' => (string) $x['label'], 'initiatives' => (int) $x['initiatives'], 'reach' => (int) $x['reach'], 'clicks' => (int) $x['clicks'], 'orders' => (int) $x['orders'], 'revenue' => (float) $x['revenue'], 'per_init' => (float) $x['rev_per_initiative'], 'spend' => (float) $x['spend'] > 0 ? (float) $x['spend'] : null, 'roas' => null === $x['roas'] ? null : (float) $x['roas'], ); } AIEO_Table::render( array( 'id' => 'aieo-roi-origin', 'chooser' => true, 'page_id' => $this->get_id(), 'row_key' => 'slug', 'rows' => $aieo_roi_rows, 'class' => 'aieo-roi-origin-split', 'row_class' => static function ( $r ) { return 'manual' !== $r['slug'] ? 'aieo-t-row--info' : ''; }, 'columns' => array( array( 'key' => 'label', 'label' => __( 'Created by', 'ai-eshop-optimizer' ) ), array( 'key' => 'initiatives', 'label' => __( 'Initiatives', 'ai-eshop-optimizer' ), 'type' => 'integer' ), array( 'key' => 'reach', 'label' => __( 'Reach', 'ai-eshop-optimizer' ), 'type' => 'integer' ), array( 'key' => 'clicks', 'label' => __( 'Clicks', 'ai-eshop-optimizer' ), 'type' => 'integer' ), array( 'key' => 'orders', 'label' => __( 'Orders', 'ai-eshop-optimizer' ), 'type' => 'integer' ), array( 'key' => 'revenue', 'label' => __( 'Revenue', 'ai-eshop-optimizer' ), 'type' => 'money' ), array( 'key' => 'per_init', 'label' => __( 'Per initiative', 'ai-eshop-optimizer' ), 'type' => 'money' ), array( 'key' => 'spend', 'label' => __( 'Ad spend', 'ai-eshop-optimizer' ), 'type' => 'money' ), array( 'key' => 'roas', 'label' => __( 'ROAS (gross)', 'ai-eshop-optimizer' ), 'type' => 'decimal', 'format' => array( 'precision' => 2 ), 'suffix' => '×', 'header_tip' => $breakeven ? sprintf( __( 'On GROSS revenue. Break-even is %1$s× at a %2$s%% gross margin.', 'ai-eshop-optimizer' ), number_format_i18n( $breakeven, 2 ), number_format_i18n( $margin_pct, 0 ) ) : '', 'bands' => null !== $breakeven ? array( array( 'tone' => 'danger' ), array( 'min' => $breakeven, 'tone' => 'success' ), ) : array(), ), ), ) ); if ( null !== $breakeven ) { $note = \Rooster\Admin\Guidance::text( sprintf( __( 'ROAS above is on GROSS revenue. At your %2$s%% gross profit margin, break-even is %1$s× — a campaign returning less than that costs more than the profit it earns.', 'ai-eshop-optimizer' ), number_format_i18n( $breakeven, 2 ), number_format_i18n( $margin_pct, 0 ) ), 'essential' ); echo $note; } echo $close; } }