prefix . 'aieo_msg_stats'; } public static function ensure_schema() { if ( get_option( self::SCHEMA_OPTION, '' ) === self::SCHEMA_VERSION ) { return; } if ( ! AIEO_Schema_Lock::acquire( self::SCHEMA_OPTION ) ) { return; } global $wpdb; $t = self::msg_stats_table(); $charset = aieo_get_charset_collate(); $wpdb->query( "CREATE TABLE IF NOT EXISTS `{$t}` ( stat_date date NOT NULL, template_id bigint(20) UNSIGNED NOT NULL, trigger_id varchar(40) NOT NULL DEFAULT '', channel varchar(10) NOT NULL DEFAULT 'push', segment_id varchar(40) NOT NULL DEFAULT '*', variant_label varchar(20) NOT NULL DEFAULT '', sent int(10) UNSIGNED NOT NULL DEFAULT 0, accepted int(10) UNSIGNED NOT NULL DEFAULT 0, clicked int(10) UNSIGNED NOT NULL DEFAULT 0, dismissed int(10) UNSIGNED NOT NULL DEFAULT 0, converted_strict int(10) UNSIGNED NOT NULL DEFAULT 0, revenue_strict decimal(14,2) NOT NULL DEFAULT 0, converted_window int(10) UNSIGNED NOT NULL DEFAULT 0, revenue_window decimal(14,2) NOT NULL DEFAULT 0, updated_at datetime NOT NULL, PRIMARY KEY (stat_date, template_id), KEY idx_trigger_channel (trigger_id, channel, stat_date) ) {$charset};" ); if ( self::table_exists( $t ) ) { update_option( self::SCHEMA_OPTION, self::SCHEMA_VERSION, false ); } } public static function ensure_scheduled() { if ( function_exists( 'as_next_scheduled_action' ) ) { if ( false !== as_next_scheduled_action( self::HOOK, array(), self::GROUP ) ) { wp_clear_scheduled_hook( self::HOOK ); return; } if ( function_exists( 'as_schedule_recurring_action' ) ) { wp_clear_scheduled_hook( self::HOOK ); as_schedule_recurring_action( self::next_run_ts(), DAY_IN_SECONDS, self::HOOK, array(), self::GROUP, true ); return; } } if ( ! wp_next_scheduled( self::HOOK ) ) { wp_schedule_event( self::next_run_ts(), 'daily', self::HOOK ); } } private static function next_run_ts() { try { $tz = wp_timezone(); $parts = explode( ':', self::RUN_AT ); $next = ( new DateTimeImmutable( 'now', $tz ) )->setTime( (int) $parts[0], (int) ( $parts[1] ?? 0 ), 0 ); if ( $next->getTimestamp() <= time() ) { $next = $next->modify( '+1 day' ); } return $next->getTimestamp(); } catch ( \Throwable $e ) { return time() + HOUR_IN_SECONDS; } } public static function run() { if ( class_exists( 'AIEO_DMM_Module_Base' ) && ! AIEO_DMM_Module_Base::is_aieo_pro_user() ) { return; } global $wpdb; $lock = AIEO_Lock_Name::for_shop( 'aieo_bandit_run' ); if ( 1 !== (int) $wpdb->get_var( $wpdb->prepare( 'SELECT GET_LOCK(%s, 0)', $lock ) ) ) { return; } try { self::ensure_schema(); self::run_conversion_join(); self::run_rollup( gmdate( 'Y-m-d', time() - self::REROLL_DAYS * DAY_IN_SECONDS ), gmdate( 'Y-m-d', time() + DAY_IN_SECONDS ) ); self::run_bandit(); } finally { $wpdb->query( $wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', $lock ) ); } } public static function run_now() { global $wpdb; $lock = AIEO_Lock_Name::for_shop( 'aieo_bandit_run' ); if ( 1 !== (int) $wpdb->get_var( $wpdb->prepare( 'SELECT GET_LOCK(%s, 5)', $lock ) ) ) { return array( 'conversions' => 0, 'reweighted' => 0, 'skipped' => 'locked' ); } try { self::ensure_schema(); $c = self::run_conversion_join(); self::run_rollup( gmdate( 'Y-m-d', time() - self::REROLL_DAYS * DAY_IN_SECONDS ), gmdate( 'Y-m-d', time() + DAY_IN_SECONDS ) ); $b = self::run_bandit(); return array( 'conversions' => $c, 'reweighted' => $b ); } finally { $wpdb->query( $wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', $lock ) ); } } public static function run_conversion_join() { global $wpdb; $disp = $wpdb->prefix . 'aieo_push_dispatch'; if ( ! self::table_exists( $disp ) ) { return 0; } $stamped = 0; $done = array(); $win = (int) apply_filters( 'aieo_bandit_conversion_window_days', self::CONV_WINDOW ); if ( function_exists( 'aieo_coupon_grants_table' ) ) { $grants = aieo_coupon_grants_table(); if ( self::table_exists( $grants ) ) { $rows = $wpdb->get_results( $wpdb->prepare( "SELECT g.redeemed_order_id, g.redeemed_at, g.source, g.wp_user_id, g.channel FROM `{$grants}` g WHERE g.status = 'redeemed' AND g.redeemed_order_id > 0 AND g.wp_user_id > 0 AND g.redeemed_at >= (UTC_TIMESTAMP() - INTERVAL %d DAY) ORDER BY g.redeemed_at DESC LIMIT %d", self::WINDOW_DAYS, self::BATCH + 1 ), ARRAY_A ); self::flag_truncation( 'strict', count( $rows ) ); foreach ( array_slice( (array) $rows, 0, self::BATCH ) as $g ) { $oid = (int) $g['redeemed_order_id']; if ( isset( $done[ $oid ] ) || self::order_attributed( $oid ) ) { continue; } $did = (int) $wpdb->get_var( $wpdb->prepare( "SELECT dispatch_id FROM `{$disp}` WHERE trigger_id = %s AND user_id = %d AND channel = %s AND state IN ('sent','clicked','dismissed') AND sent_at IS NOT NULL AND sent_at <= %s AND sent_at >= (%s - INTERVAL %d DAY) AND converted_order_id = 0 ORDER BY sent_at DESC LIMIT 1", (string) $g['source'], (int) $g['wp_user_id'], (string) $g['channel'], (string) $g['redeemed_at'], (string) $g['redeemed_at'], self::WINDOW_DAYS ) ); if ( $did > 0 ) { $rev = self::order_revenue( $oid ); if ( null !== $rev ) { $wpdb->update( $disp, array( 'converted_order_id' => $oid, 'converted_at' => (string) $g['redeemed_at'], 'revenue' => $rev, 'attribution_confidence' => 'grant' ), array( 'dispatch_id' => $did ), array( '%d', '%s', '%f', '%s' ), array( '%d' ) ); $done[ $oid ] = 1; $stamped++; } } } } } $clicked = $wpdb->get_results( $wpdb->prepare( "SELECT dispatch_id, user_id, sent_at FROM `{$disp}` WHERE state = 'clicked' AND user_id > 0 AND converted_order_id = 0 AND sent_at >= (UTC_TIMESTAMP() - INTERVAL %d DAY) ORDER BY sent_at DESC LIMIT %d", $win + 1, self::BATCH + 1 ), ARRAY_A ); self::flag_truncation( 'loose', count( $clicked ) ); foreach ( array_slice( (array) $clicked, 0, self::BATCH ) as $d ) { $order = self::first_paid_order( (int) $d['user_id'], (string) $d['sent_at'], $win ); if ( ! $order ) { continue; } $oid = (int) $order['id']; if ( isset( $done[ $oid ] ) || self::order_attributed( $oid ) ) { continue; } $rev = self::order_revenue( $oid ); if ( null !== $rev ) { $wpdb->update( $disp, array( 'converted_order_id' => $oid, 'converted_at' => (string) $order['paid_at'], 'revenue' => $rev, 'attribution_confidence' => 'window' ), array( 'dispatch_id' => (int) $d['dispatch_id'] ), array( '%d', '%s', '%f', '%s' ), array( '%d' ) ); $done[ $oid ] = 1; $stamped++; } } return $stamped; } private static function order_attributed( $order_id ) { global $wpdb; $disp = $wpdb->prefix . 'aieo_push_dispatch'; return (int) $wpdb->get_var( $wpdb->prepare( "SELECT 1 FROM `{$disp}` WHERE converted_order_id = %d LIMIT 1", (int) $order_id ) ) === 1; } private static function order_revenue( $order_id ) { if ( ! function_exists( 'wc_get_order' ) ) { return null; } $order = wc_get_order( (int) $order_id ); if ( ! $order || ! $order->get_date_paid() ) { return null; } $net = (float) $order->get_total() - (float) $order->get_total_tax() - (float) $order->get_shipping_total(); return max( 0, round( $net, 2 ) ); } private static function first_paid_order( $user_id, $after, $window_days ) { if ( ! function_exists( 'wc_get_orders' ) ) { return null; } $after_ts = strtotime( (string) $after . ' UTC' ); if ( ! $after_ts ) { return null; } $orders = wc_get_orders( array( 'customer_id' => (int) $user_id, 'status' => array( 'processing', 'completed' ), 'date_paid' => ( $after_ts ) . '...' . ( $after_ts + $window_days * DAY_IN_SECONDS ), 'orderby' => 'date_paid', 'order' => 'ASC', 'limit' => 1, 'return' => 'objects', ) ); if ( empty( $orders ) ) { return null; } $o = $orders[0]; $paid = $o->get_date_paid(); return array( 'id' => $o->get_id(), 'paid_at' => $paid ? gmdate( 'Y-m-d H:i:s', $paid->getTimestamp() ) : gmdate( 'Y-m-d H:i:s', $after_ts ) ); } private static function flag_truncation( $which, $count ) { if ( $count > self::BATCH ) { update_option( 'aieo_bandit_join_truncated', array( 'which' => $which, 'at' => gmdate( 'Y-m-d H:i:s' ) ), false ); } } public static function run_rollup( $start_date, $end_date = null ) { global $wpdb; $disp = $wpdb->prefix . 'aieo_push_dispatch'; $tmpl = $wpdb->prefix . 'aieo_push_templates'; $ms = self::msg_stats_table(); if ( ! self::table_exists( $disp ) || ! self::table_exists( $tmpl ) || ! self::table_exists( $ms ) ) { return; } $start = $start_date . ' 00:00:00'; $end = $end_date ? ( $end_date . ' 00:00:00' ) : gmdate( 'Y-m-d H:i:s', strtotime( $start_date . ' 00:00:00 UTC' ) + DAY_IN_SECONDS ); $now = gmdate( 'Y-m-d H:i:s' ); $wpdb->query( $wpdb->prepare( "INSERT INTO `{$ms}` (stat_date, template_id, trigger_id, channel, segment_id, variant_label, sent, accepted, clicked, dismissed, converted_strict, revenue_strict, converted_window, revenue_window, updated_at) SELECT DATE(d.sent_at), d.template_id, MIN(t.trigger_id), MIN(t.channel), MIN(t.segment_id), MIN(t.variant_label), COUNT(*), COUNT(*), SUM(d.state = 'clicked'), SUM(d.state = 'dismissed'), SUM(d.converted_order_id > 0 AND d.attribution_confidence IN ('grant','session')), SUM(CASE WHEN d.attribution_confidence IN ('grant','session') THEN d.revenue ELSE 0 END), SUM(d.converted_order_id > 0 AND d.attribution_confidence = 'window'), SUM(CASE WHEN d.attribution_confidence = 'window' THEN d.revenue ELSE 0 END), %s FROM `{$disp}` d JOIN `{$tmpl}` t ON t.template_id = d.template_id WHERE d.template_id > 0 AND d.sent_at >= %s AND d.sent_at < %s AND d.state IN ('sent','clicked','dismissed') GROUP BY d.template_id, DATE(d.sent_at) ON DUPLICATE KEY UPDATE trigger_id=VALUES(trigger_id), channel=VALUES(channel), segment_id=VALUES(segment_id), variant_label=VALUES(variant_label), sent=VALUES(sent), accepted=VALUES(accepted), clicked=VALUES(clicked), dismissed=VALUES(dismissed), converted_strict=VALUES(converted_strict), revenue_strict=VALUES(revenue_strict), converted_window=VALUES(converted_window), revenue_window=VALUES(revenue_window), updated_at=VALUES(updated_at)", $now, $start, $end ) ); if ( $wpdb->last_error ) { update_option( 'aieo_bandit_last_error', 'rollup: ' . $wpdb->last_error, false ); } } public static function run_bandit() { global $wpdb; $tmpl = $wpdb->prefix . 'aieo_push_templates'; $ms = self::msg_stats_table(); if ( ! self::table_exists( $tmpl ) || ! self::table_exists( $ms ) ) { return 0; } $rows = $wpdb->get_results( "SELECT template_id, trigger_id, channel, segment_id, variant_label, weight, updated_at FROM `{$tmpl}` WHERE is_active = 1", ARRAY_A ); $pools = array(); foreach ( (array) $rows as $r ) { $pools[ $r['trigger_id'] . '|' . $r['channel'] . '|' . $r['segment_id'] ][] = $r; } $reweighted = 0; $window_start = gmdate( 'Y-m-d', time() - self::WINDOW_DAYS * DAY_IN_SECONDS ); $recos = array(); foreach ( $pools as $pool ) { if ( count( $pool ) < 2 ) { continue; } $arms = array(); $totacc = 0; foreach ( $pool as $tpl ) { $edit = strtotime( (string) $tpl['updated_at'] . ' UTC' ); $since = max( $window_start, gmdate( 'Y-m-d', $edit + DAY_IN_SECONDS ) ); $agg = $wpdb->get_row( $wpdb->prepare( "SELECT COALESCE(SUM(accepted),0) a, COALESCE(SUM(clicked),0) c, COALESCE(SUM(converted_strict),0) cv FROM `{$ms}` WHERE template_id = %d AND stat_date >= %s", (int) $tpl['template_id'], $since ), ARRAY_A ); $a = (int) ( $agg['a'] ?? 0 ); $c = min( $a, (int) ( $agg['c'] ?? 0 ) ); $cv = (int) ( $agg['cv'] ?? 0 ); $arms[ (int) $tpl['template_id'] ] = array( 'a' => $a, 'c' => $c, 'cv' => $cv ); $totacc += $a; } if ( 0 === $totacc ) { foreach ( array_keys( $arms ) as $tid ) { $reweighted += self::write_weight( $tid, 50 ); } continue; } $alloc = self::allocate( $arms ); foreach ( $arms as $tid => $arm ) { $p = $alloc[ $tid ] ?? 0.0; $w = max( self::WEIGHT_FLOOR, (int) round( 100 * $p ) ); if ( $w > 40 && self::conv_vetoed( $tid, $arms ) ) { $w = 40; } $reweighted += self::write_weight( $tid, min( 100, $w ) ); if ( $arm['a'] >= self::DEACT_N && $p < self::DEACT_P ) { $recos[] = (int) $tid; } } } update_option( 'aieo_bandit_deactivation_recos', array_values( array_unique( $recos ) ), false ); update_option( 'aieo_bandit_last_run', gmdate( 'Y-m-d H:i:s' ), false ); return $reweighted; } private static function write_weight( $template_id, $weight ) { global $wpdb; $tmpl = $wpdb->prefix . 'aieo_push_templates'; $n = $wpdb->query( $wpdb->prepare( "UPDATE `{$tmpl}` SET weight = %d, updated_at = updated_at WHERE template_id = %d AND weight <> %d", (int) $weight, (int) $template_id, (int) $weight ) ); return ( $n > 0 ) ? 1 : 0; } private static function conv_vetoed( $tid, $arms ) { $mine = $arms[ $tid ]; if ( $mine['a'] < self::DEACT_N ) { return false; } $n1 = max( 1, $mine['a'] ); $p1 = $mine['cv'] / $n1; foreach ( $arms as $id => $arm ) { if ( $id === $tid || $arm['a'] < self::DEACT_N || $arm['cv'] < 5 ) { continue; } $n2 = max( 1, $arm['a'] ); $p2 = $arm['cv'] / $n2; if ( $p2 <= $p1 ) { continue; } $pp = ( $mine['cv'] + $arm['cv'] ) / ( $n1 + $n2 ); $se = sqrt( $pp * ( 1 - $pp ) * ( 1 / $n1 + 1 / $n2 ) ); if ( $se > 0 && ( ( $p2 - $p1 ) / $se ) > 1.64 ) { return true; } } return false; } private static function allocate( $arms, $draws = self::DRAWS ) { $ids = array_keys( $arms ); $wins = array_fill_keys( $ids, 0 ); for ( $i = 0; $i < $draws; $i++ ) { $best = $ids[0]; $bv = -1.0; foreach ( $arms as $id => $arm ) { $alpha = 1.0 + $arm['c']; $beta = 1.0 + max( 0, $arm['a'] - $arm['c'] ); $s = self::rand_beta( $alpha, $beta ); if ( $s > $bv ) { $bv = $s; $best = $id; } } $wins[ $best ]++; } $out = array(); foreach ( $wins as $id => $w ) { $out[ $id ] = $w / $draws; } return $out; } private static function rand_unit() { return ( mt_rand( 1, mt_getrandmax() - 1 ) ) / mt_getrandmax(); } private static function rand_normal() { $u1 = self::rand_unit(); $u2 = self::rand_unit(); return sqrt( -2.0 * log( $u1 ) ) * cos( 2.0 * M_PI * $u2 ); } private static function rand_gamma( $k ) { if ( $k < 1.0 ) { return self::rand_gamma( $k + 1.0 ) * pow( self::rand_unit(), 1.0 / $k ); } $d = $k - 1.0 / 3.0; $c = 1.0 / sqrt( 9.0 * $d ); for ( $guard = 0; $guard < 1000; $guard++ ) { do { $x = self::rand_normal(); $v = pow( 1.0 + $c * $x, 3 ); } while ( $v <= 0 ); $u = self::rand_unit(); $x2 = $x * $x; if ( $u < 1.0 - 0.0331 * $x2 * $x2 ) { return $d * $v; } if ( log( $u ) < 0.5 * $x2 + $d * ( 1.0 - $v + log( $v ) ) ) { return $d * $v; } } return $d; } private static function rand_beta( $a, $b ) { $g1 = self::rand_gamma( $a ); $g2 = self::rand_gamma( $b ); $sum = $g1 + $g2; return ( $sum > 0 ) ? ( $g1 / $sum ) : 0.5; } private static function table_exists( $t ) { global $wpdb; return (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $t ) ) === $t; } } }