prefix . self::CI_REBUILD_LOCK;
}
public static function acquire_ci_rebuild_lock($wait = 0) {
global $wpdb;
return 1 === (int) $wpdb->get_var($wpdb->prepare('SELECT GET_LOCK(%s, %d)', self::ci_rebuild_lock_name(), (int) $wait));
}
public static function release_ci_rebuild_lock() {
global $wpdb;
$wpdb->query($wpdb->prepare('SELECT RELEASE_LOCK(%s)', self::ci_rebuild_lock_name()));
}
public function get_id() { return 'surveys'; }
public function get_icon() { return 'circle-question-mark'; }
public function get_label() { return __('Why Customers Buy', 'ai-eshop-optimizer'); }
public function get_priority() { return 16; }
public function get_group() { return 'customer_profiling'; }
public function is_pro_locked() { return true; }
public function get_pro_promo() {
return array(
'title' => __('Customer Intelligence Surveys', 'ai-eshop-optimizer'),
'description' => __('Capture WHY customers buy — first-party signal that feeds the needs taxonomy and the recommendation engine. Each survey response makes the next recommendation, the next ad, and the next newsletter measurably more relevant.', 'ai-eshop-optimizer'),
'bullets' => array(
__('Post-purchase, post-delivery, and lapsed-customer survey flows.', 'ai-eshop-optimizer'),
__('Responses auto-tagged onto the customer + product so they enrich recommendations.', 'ai-eshop-optimizer'),
__('No third-party SaaS — your customer data stays inside WooCommerce.', 'ai-eshop-optimizer'),
),
);
}
public function register_hooks() {
if (is_admin()) {
add_action('admin_enqueue_scripts', function () {
if ((isset($_GET['page']) ? sanitize_text_field(wp_unslash($_GET['page'])) : '') === 'ai-eshop-dmm' && (isset($_GET['dmm_tab']) ? sanitize_text_field(wp_unslash($_GET['dmm_tab'])) : '') === 'surveys') {
$css = 'assets/css/aieo-surveys-admin.css';
wp_enqueue_style('aieo-surveys-admin', AIEO_URI . $css, array(), file_exists(AIEO_DIR . $css) ? (string) filemtime(AIEO_DIR . $css) : '1');
}
});
}
add_action('plugins_loaded', array($this, 'maybe_install'), 25);
add_action('plugins_loaded', array($this, 'maybe_schedule_cron'), 26);
add_action('wp_ajax_aieo_dmm_surveys_recompute', array($this, 'ajax_recompute'));
add_action('wp_ajax_aieo_dmm_surveys_ingest', array($this, 'ajax_ingest'));
add_action('wp_ajax_aieo_dmm_surveys_status', array($this, 'ajax_status'));
add_action('wp_ajax_aieo_dmm_surveys_bootstrap_ci', array($this, 'ajax_bootstrap_ci'));
add_action('wp_ajax_aieo_dmm_surveys_verify_prices', array($this, 'ajax_verify_prices'));
add_action('wp_ajax_aieo_dmm_surveys_fix_prices', array($this, 'ajax_fix_prices'));
add_action('gform_after_submission', array($this, 'on_gform_submission'), 20, 2);
add_action('gform_after_save_form', array($this, 'on_gform_form_saved'), 20, 2);
add_action('gform_post_update_form_meta', array($this, 'on_gform_form_meta_updated'), 20, 3);
add_action('aieo_survey_register_changed', array($this, 'on_register_changed'));
add_action(self::CRON_INGEST_HOOK, array($this, 'cron_run_nightly'));
add_action(self::CRON_ASYNC_HOOK, array($this, 'on_async_run'), 10, 1);
add_action(self::CRON_TASTE_HOOK, array($this, 'cron_taste_vectors_tick'));
}
public function on_gform_form_saved($form, $is_new = false) {
unset($is_new);
$this->rebuild_sprocs_for_form(is_array($form) && isset($form['id']) ? (int) $form['id'] : 0);
}
public function on_gform_form_meta_updated($meta, $form_id, $meta_name = '') {
unset($meta, $meta_name);
$this->rebuild_sprocs_for_form((int) $form_id);
}
private function rebuild_sprocs_for_form($form_id) {
static $done = array();
$form_id = (int) $form_id;
if ($form_id <= 0 || isset($done[$form_id])) {
return;
}
if (self::FORM_POST_ORDER !== $form_id && self::FORM_POST_DELIVERY !== $form_id) {
return;
}
$done[$form_id] = true;
if (class_exists('GFFormsModel') && method_exists('GFFormsModel', 'flush_current_forms')) {
GFFormsModel::flush_current_forms();
}
$this->ensure_sprocs();
}
public function on_register_changed() {
static $queued = false;
if ($queued) {
return;
}
$queued = true;
add_action('shutdown', function () {
if (class_exists('GFFormsModel') && method_exists('GFFormsModel', 'flush_current_forms')) {
GFFormsModel::flush_current_forms();
}
$this->ensure_sprocs();
}, 50);
}
public function enqueue_admin_assets() {
}
public function ensure_customer_intelligence_table() {
global $wpdb;
$tbl_ci = $wpdb->prefix . 'aieo_customer_intelligence';
if (function_exists('aieo_dmm_table_exists')) {
if (aieo_dmm_table_exists($tbl_ci)) { return true; }
} elseif ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tbl_ci)) === $tbl_ci) {
return true;
}
$schema_file = AIEO_DIR . 'includes/dmm/sql/push-schema.php';
if (!file_exists($schema_file)) { return false; }
require_once $schema_file;
if (function_exists('aieo_push_install_schema')) {
delete_option('aieo_push_schema_version');
aieo_push_install_schema();
}
return (string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tbl_ci)) === $tbl_ci;
}
public function ensure_sprocs() {
global $wpdb;
$file = AIEO_DIR . 'includes/dmm/sql/surveys-sprocs.php';
if ( ! file_exists( $file ) ) {
return false;
}
require_once $file;
if ( ! function_exists( 'aieo_surveys_install_sprocs' ) ) {
return false;
}
aieo_surveys_install_sprocs();
$ok = 2 === (int) $wpdb->get_var(
"SELECT COUNT(*) FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = DATABASE()
AND ROUTINE_TYPE = 'PROCEDURE'
AND ROUTINE_NAME IN ('aieo_sp_ingest_survey_responses','aieo_sp_extract_survey_intelligence')"
);
if ( $ok && function_exists( 'aieo_debug_log' ) ) {
aieo_debug_log( 'AIEO_Surveys: stored procedures were missing and have been re-created.' );
}
return $ok;
}
private function gf_entries_exist() {
global $wpdb;
static $exists = null;
if (null === $exists) {
$table = $wpdb->prefix . 'gf_entry';
$exists = $table === $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($table)));
}
return $exists;
}
private function call_survey_sproc( $sql ) {
global $wpdb;
$wpdb->last_error = '';
$wpdb->query( $sql );
$err = (string) $wpdb->last_error;
if ( '' === $err ) {
return true;
}
if ( false === stripos( $err, 'does not exist' ) ) {
return false;
}
if ( function_exists( 'aieo_debug_log' ) ) {
aieo_debug_log( 'AIEO_Surveys: ' . $sql . ' failed (' . $err . ') — installing sprocs and retrying once.' );
}
if ( ! $this->ensure_sprocs() ) {
return false;
}
$wpdb->last_error = '';
$wpdb->query( $sql );
return '' === (string) $wpdb->last_error;
}
public function maybe_install() {
$this->ensure_customer_intelligence_table();
$current = (string) get_option(self::SCHEMA_OPTION, '');
if ($current === self::SCHEMA_VERSION) {
return;
}
if (!AIEO_Schema_Lock::acquire(self::SCHEMA_OPTION)) {
return;
}
global $wpdb;
$base = AIEO_DIR . 'includes/dmm/sql/';
require_once $base . 'surveys-schema.php';
require_once $base . 'surveys-sprocs.php';
if ($current === '1') {
$tbl_si = $wpdb->prefix . 'aieo_survey_intelligence';
$pk = (string) $wpdb->get_var($wpdb->prepare(
"SELECT COLUMN_NAME FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_KEY = 'PRI' LIMIT 1",
$tbl_si
));
if ($pk !== '' && strtolower($pk) !== 'eponymous_id') {
$aside = $tbl_si . '_v1';
$wpdb->query("DROP TABLE IF EXISTS {$aside}");
$wpdb->query("RENAME TABLE {$tbl_si} TO {$aside}");
if (function_exists('aieo_debug_log')) {
aieo_debug_log('AIEO Surveys: v1 intelligence table had PRIMARY KEY ' . $pk . '; preserved as ' . $aside . ' and rebuilt on the current shape.');
}
}
}
if (function_exists('aieo_surveys_install_schema')) { aieo_surveys_install_schema(); }
if (function_exists('aieo_surveys_install_sprocs')) { aieo_surveys_install_sprocs(); }
update_option(self::SCHEMA_OPTION, self::SCHEMA_VERSION, false);
}
public function maybe_schedule_cron() {
if (!wp_next_scheduled(self::CRON_INGEST_HOOK)) {
$tz = new \DateTimeZone('Europe/Athens');
$next = new \DateTime('today 03:00', $tz);
if ($next->getTimestamp() <= time()) {
$next->modify('+1 day');
}
wp_schedule_event($next->getTimestamp(), 'daily', self::CRON_INGEST_HOOK);
}
if (!wp_next_scheduled(self::CRON_TASTE_HOOK)) {
wp_schedule_event(time() + 60, 'hourly', self::CRON_TASTE_HOOK);
}
}
public function cron_taste_vectors_tick() {
try {
$this->compute_taste_vectors_batch(50);
} catch (\Throwable $e) {
if (function_exists('aieo_debug_log')) {
aieo_debug_log('AIEO_Surveys taste-vector tick failed: ' . $e->getMessage());
}
}
}
public function cron_run_nightly() {
global $wpdb;
$this->maybe_install();
$started = microtime(true);
if ($this->gf_entries_exist()) {
$this->call_survey_sproc('CALL aieo_sp_ingest_survey_responses()');
}
$this->call_survey_sproc('CALL aieo_sp_extract_survey_intelligence()');
$this->compute_purchase_metrics();
$duration = round(microtime(true) - $started, 2);
if (function_exists('aieo_debug_log')) {
aieo_debug_log('AIEO_Surveys nightly ingest+extract completed in ' . $duration . 's');
}
}
public function on_gform_submission($entry, $form) {
if (!is_array($entry) || !is_array($form)) { return; }
$form_id = (int) ($form['id'] ?? 0);
if ($form_id !== self::FORM_POST_ORDER && $form_id !== self::FORM_POST_DELIVERY) {
return;
}
$this->maybe_install();
if ($form_id === self::FORM_POST_ORDER) {
$this->ingest_f18_entry($entry);
} else {
$this->ingest_f20_entry($entry);
}
}
private function ingest_f18_entry($entry) {
global $wpdb;
$tbl_sr = $wpdb->prefix . 'aieo_survey_responses';
$email = strtolower(trim((string) ($entry['92'] ?? '')));
$order_id = (int) ($entry['15'] ?? 0);
$customer_id= (int) ($entry['16'] ?? 0);
$resolved = $this->resolve_eponymous_for_order($order_id);
if ($resolved['eponymous_id'] === 0 && $email !== '') {
$resolved = $this->resolve_eponymous_for_email($email);
}
$ux_keys = array('21','22','23','24','25','26','27','29','30','31','33','47');
$ux_vals = array();
foreach ($ux_keys as $k) {
$v = (string) ($entry[$k] ?? '');
if (preg_match('/^[1-5]$/', $v)) { $ux_vals[] = (int) $v; }
}
$ux_avg = !empty($ux_vals) ? round(array_sum($ux_vals) / count($ux_vals), 2) : 0;
$is_pro = (string) ($entry['51'] ?? '');
$disc_store = $this->any_checkbox_set($entry, '44');
$disc_google = $this->any_checkbox_set($entry, '45');
$disc_ad = $this->any_checkbox_set($entry, '73');
$disc_wom = $this->any_checkbox_set($entry, '43');
$row = array(
'form_id' => self::FORM_POST_ORDER,
'gf_entry_id' => (int) ($entry['id'] ?? 0),
'order_id' => $order_id,
'customer_id' => $customer_id,
'eponymous_id' => (int) $resolved['eponymous_id'],
'wp_user_id' => (int) ($resolved['wp_user_id'] ?: $customer_id),
'email' => $email,
'submitted_at' => (string) ($entry['date_created'] ?? current_time('mysql', true)),
'f18_nps' => $this->int_or_zero($entry['18'] ?? ''),
'f18_satisfaction' => $this->int_or_zero($entry['7'] ?? ''),
'f18_site_ux_avg' => $ux_avg,
'f18_is_pro' => $this->yes_no($is_pro),
'f18_priorities_raw' => (string) ($entry['8'] ?? ''),
'f18_disc_store' => $disc_store,
'f18_disc_google' => $disc_google,
'f18_disc_ad' => $disc_ad,
'f18_disc_wom' => $disc_wom,
'f18_eng_seminar' => $this->yes_no($entry['35'] ?? ''),
'f18_eng_blog' => $this->yes_no($entry['38'] ?? ''),
'f18_eng_fastfinder' => $this->yes_no($entry['36'] ?? ''),
'f18_social_login' => $this->yes_no($entry['37'] ?? ''),
'f18_shipping_concern' => $this->yes_no($entry['39'] ?? ''),
'f18_will_followup' => $this->yes_no($entry['70'] ?? ''),
'f18_open_feedback' => (string) ($entry['12'] ?? ''),
);
if ((bool) apply_filters('aieo_survey_require_token', false) && ! self::survey_token_valid($entry, $order_id, $email)) {
return;
}
$this->upsert_response($tbl_sr, $row);
$nps = (int) $row['f18_nps'];
$class = ($nps >= 4 && $nps <= 5) ? 'promoter' : (($nps >= 1 && $nps <= 2) ? 'detractor' : '');
if ('' !== $class) {
$by_email = ('' !== $email) ? $this->resolve_eponymous_for_email($email) : array('eponymous_id' => 0, 'wp_user_id' => 0);
$verified = array('eponymous_id' => 0, 'wp_user_id' => 0);
if ((int) $resolved['eponymous_id'] > 0 && (int) $by_email['eponymous_id'] === (int) $resolved['eponymous_id']) {
$verified = $resolved;
} elseif ((int) $resolved['eponymous_id'] === 0 && (int) $by_email['eponymous_id'] > 0) {
$verified = $by_email;
}
if ((int) $verified['eponymous_id'] > 0) {
$this->live_upsert_survey_intelligence($verified, $row, $class);
}
if (class_exists('AIEO_Workflow_Runtime')) {
AIEO_Workflow_Runtime::on_survey_signal($class, array('user_id' => (int) $verified['wp_user_id'], 'email' => $email));
}
}
}
private function live_upsert_survey_intelligence(array $resolved, array $row, $nps_class) {
global $wpdb;
$t = $wpdb->prefix . 'aieo_survey_intelligence';
$submitted = (string) ($row['submitted_at'] ?? '');
$wpdb->query($wpdb->prepare(
"INSERT INTO `{$t}`
(eponymous_id, wp_user_id, email, f18_entry_id, f18_submitted_at, nps_score, nps_class, shipping_price_concern)
VALUES (%d, %d, %s, %d, %s, %d, %s, %d)
ON DUPLICATE KEY UPDATE
wp_user_id = VALUES(wp_user_id), email = VALUES(email),
f18_entry_id = VALUES(f18_entry_id), f18_submitted_at = VALUES(f18_submitted_at),
nps_score = VALUES(nps_score), nps_class = VALUES(nps_class),
shipping_price_concern = VALUES(shipping_price_concern), updated_at = UTC_TIMESTAMP()",
(int) $resolved['eponymous_id'],
(int) $resolved['wp_user_id'],
(string) ($row['email'] ?? ''),
(int) ($row['gf_entry_id'] ?? 0),
'' !== $submitted ? $submitted : current_time('mysql', true),
(int) ($row['f18_nps'] ?? 0),
(string) $nps_class,
(int) ($row['f18_shipping_concern'] ?? 0)
));
}
public static function survey_token($order_id, $email) {
return substr(
hash_hmac('sha256', 'aieo_stok|' . (int) $order_id . '|' . strtolower(trim((string) $email)), wp_salt('auth')),
0,
24
);
}
public static function signed_survey_url($url, $order_id, $email) {
return add_query_arg(
array(
'order' => (int) $order_id,
'email' => rawurlencode(strtolower(trim((string) $email))),
'aieo_stok' => self::survey_token($order_id, $email),
),
(string) $url
);
}
private static function survey_token_valid($entry, $order_id, $email) {
$src = is_array($entry) ? (string) ($entry['source_url'] ?? '') : '';
$tok = '';
if ('' !== $src) {
$qs = (string) wp_parse_url($src, PHP_URL_QUERY);
if ('' !== $qs) {
parse_str($qs, $q);
$tok = isset($q['aieo_stok']) ? (string) $q['aieo_stok'] : '';
}
}
if ('' === $tok) {
return false;
}
return hash_equals(self::survey_token((int) $order_id, (string) $email), $tok);
}
private function ingest_f20_entry($entry) {
global $wpdb;
$tbl_sr = $wpdb->prefix . 'aieo_survey_responses';
$email = strtolower(trim((string) ($entry['17'] ?? '')));
if ($email === '') { return; }
$resolved = $this->resolve_eponymous_for_email($email);
$gift_pref_raw = (string) ($entry['151'] ?? '');
$gift_pref = ('' !== trim($gift_pref_raw) && class_exists('AIEO_Survey_Choice_Register'))
? AIEO_Survey_Choice_Register::code_for($gift_pref_raw)
: '';
$row = array(
'form_id' => self::FORM_POST_DELIVERY,
'gf_entry_id' => (int) ($entry['id'] ?? 0),
'eponymous_id' => (int) $resolved['eponymous_id'],
'wp_user_id' => (int) $resolved['wp_user_id'],
'email' => $email,
'submitted_at' => (string) ($entry['date_created'] ?? current_time('mysql', true)),
'f20_delivery_speed' => (string) ($entry['21'] ?? ''),
'f20_packaging' => $this->int_or_zero($entry['101'] ?? ''),
'f20_items_clean' => $this->yes_no($entry['103'] ?? ''),
'f20_will_repurchase' => $this->yes_no($entry['109'] ?? ''),
'f20_gift_pref' => $gift_pref,
'f20_hair_color' => (string) ($entry['123'] ?? ''),
'f20_eye_color' => (string) ($entry['124'] ?? ''),
'f20_skin_type' => (string) ($entry['125'] ?? ''),
'f20_silhouette' => (string) ($entry['126'] ?? ''),
'f20_height' => (string) ($entry['128'] ?? ''),
);
$this->upsert_response($tbl_sr, $row);
}
private function upsert_response($tbl, array $row) {
global $wpdb;
$cols = array_keys($row);
$place = implode(', ', array_fill(0, count($cols), '%s'));
$assigns = array();
foreach ($cols as $c) {
if ($c === 'form_id' || $c === 'gf_entry_id') { continue; }
$assigns[] = $c . ' = VALUES(' . $c . ')';
}
$sql = "INSERT INTO {$tbl} (" . implode(',', $cols) . ") VALUES ({$place})
ON DUPLICATE KEY UPDATE " . implode(', ', $assigns) . ", updated_at = UTC_TIMESTAMP()";
$wpdb->query($wpdb->prepare($sql, ...array_values($row)));
}
private function resolve_eponymous_for_order($order_id) {
global $wpdb;
$order_id = (int) $order_id;
if ($order_id <= 0) { return array('eponymous_id' => 0, 'wp_user_id' => 0); }
$tables = $this->discover_order_snapshot_tables();
foreach ($tables as $t) {
$epo = (int) $wpdb->get_var($wpdb->prepare(
"SELECT EponymousID FROM `{$t}` WHERE OrderId = %d AND EponymousID > 0 LIMIT 1",
$order_id
));
if ($epo > 0) {
$tbl_ci = $wpdb->prefix . 'aieo_customer_intelligence';
$wp_user_id = (int) $wpdb->get_var($wpdb->prepare(
"SELECT wp_user_id FROM {$tbl_ci} WHERE eponymous_id = %d LIMIT 1",
$epo
));
return array('eponymous_id' => $epo, 'wp_user_id' => $wp_user_id);
}
}
return array('eponymous_id' => 0, 'wp_user_id' => 0);
}
private function resolve_eponymous_for_email($email) {
global $wpdb;
if ($email === '') { return array('eponymous_id' => 0, 'wp_user_id' => 0); }
$tbl_ci = $wpdb->prefix . 'aieo_customer_intelligence';
$row = $wpdb->get_row($wpdb->prepare(
"SELECT eponymous_id, wp_user_id
FROM {$tbl_ci}
WHERE LOWER(TRIM(email)) COLLATE utf8mb4_unicode_ci
= %s COLLATE utf8mb4_unicode_ci
LIMIT 1",
$email
), ARRAY_A);
if (!$row) { return array('eponymous_id' => 0, 'wp_user_id' => 0); }
return array(
'eponymous_id' => (int) $row['eponymous_id'],
'wp_user_id' => (int) $row['wp_user_id'],
);
}
private function int_or_zero($v) {
$v = (string) $v;
return ($v !== '' && ctype_digit($v)) ? (int) $v : 0;
}
private function yes_no($v) {
$v = trim((string) $v);
if ('1' === $v) {
return 1;
}
if ('' === $v || !class_exists('AIEO_Survey_Choice_Register')) {
return 0;
}
return 'yes' === AIEO_Survey_Choice_Register::code_for($v) ? 1 : 0;
}
private function any_checkbox_set(array $entry, $base_id) {
foreach ($entry as $k => $v) {
if ($v === '' || $v === null) { continue; }
if ($k === $base_id || strpos((string) $k, $base_id . '.') === 0) {
return 1;
}
}
return 0;
}
private function discover_order_snapshot_tables() {
global $wpdb;
$like = $wpdb->esc_like($wpdb->prefix . 'aieo_') . '%' . $wpdb->esc_like('_orders');
$found = $wpdb->get_col($wpdb->prepare('SHOW TABLES LIKE %s', $like));
$monthly_re = '/^' . preg_quote($wpdb->prefix, '/') . 'aieo_\d{4}_\d{2}_orders$/';
$valid = array();
foreach ((array) $found as $t) {
if ($t === $wpdb->prefix . 'aieo_temp_orders' || preg_match($monthly_re, (string) $t)) {
$valid[] = (string) $t;
}
}
return $valid;
}
public static function priority_taxonomy() {
$labels = array();
if (class_exists('AIEO_Survey_Blueprint')) {
$q = AIEO_Survey_Blueprint::question('priorities');
if ($q) {
$labels = $q['choices'];
}
}
foreach (array('price', 'quality', 'shipping_cost', 'know_products', 'store_trust',
'service_speed', 'support', 'site_quality', 'physical_store') as $slug) {
if (!isset($labels[$slug]) || '' === trim((string) $labels[$slug])) {
$labels[$slug] = $slug;
}
}
$labels['general'] = __('No clear signal', 'ai-eshop-optimizer');
return $labels;
}
public static function get_audience_priority_distribution( $audience_key = 'all' ) {
global $wpdb;
$tbl_ci = $wpdb->prefix . 'aieo_customer_intelligence';
$tbl_um = $wpdb->usermeta;
$col_exists = (string) $wpdb->get_var($wpdb->prepare(
"SELECT COLUMN_NAME FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s LIMIT 1",
$tbl_ci,
'inferred_top_priority'
));
if ($col_exists === '') {
return array(
'totals' => array(),
'by_source' => array(),
'audience_key' => (string) $audience_key,
'error' => 'inferred_priorities columns not yet installed',
);
}
$where = '';
$args = array();
if ($audience_key !== 'all' && $audience_key !== '') {
$where = " INNER JOIN {$tbl_um} um
ON um.user_id = ci.wp_user_id
AND um.meta_key = %s AND um.meta_value = %s ";
$args[] = class_exists('AIEO_Identity_Bridge')
? \AIEO_Identity_Bridge::META_AUDIENCE
: 'aieo_audience_key';
$args[] = $audience_key;
}
$sql = "SELECT
ci.inferred_top_priority AS slug,
ci.inferred_priorities_source AS source,
COUNT(*) AS n,
AVG(ci.inferred_priorities_confidence) AS avg_confidence
FROM {$tbl_ci} ci
{$where}
WHERE ci.inferred_top_priority IS NOT NULL
GROUP BY ci.inferred_top_priority, ci.inferred_priorities_source";
$rows = !empty($args)
? $wpdb->get_results($wpdb->prepare($sql, $args), ARRAY_A)
: $wpdb->get_results($sql, ARRAY_A);
$totals = array();
$by_source = array();
$tax = self::priority_taxonomy();
if (is_array($rows)) {
foreach ($rows as $r) {
$slug = (string) $r['slug'];
$source = (string) $r['source'];
$n = (int) $r['n'];
$conf = round((float) $r['avg_confidence'], 2);
$totals[$slug] = ($totals[$slug] ?? 0) + $n;
if (!isset($by_source[$source])) { $by_source[$source] = array(); }
$by_source[$source][] = array(
'priority_slug' => $slug,
'priority_label' => $tax[$slug] ?? $slug,
'customers' => $n,
'avg_confidence' => $conf,
);
}
}
arsort($totals, SORT_NUMERIC);
$ranked = array();
foreach ($totals as $slug => $n) {
$ranked[] = array(
'priority_slug' => $slug,
'priority_label' => $tax[$slug] ?? $slug,
'customers' => (int) $n,
);
}
return array(
'audience_key' => (string) $audience_key,
'totals' => $ranked,
'by_source' => $by_source,
);
}
public static function get_customer_priority_profile( $user_id ) {
$user_id = (int) $user_id;
if ($user_id <= 0) { return null; }
global $wpdb;
$tbl_ci = $wpdb->prefix . 'aieo_customer_intelligence';
$row = $wpdb->get_row($wpdb->prepare(
"SELECT inferred_top_priority, inferred_priorities_json,
inferred_priorities_confidence, inferred_priorities_source,
inferred_priorities_updated_at, price_band, discount_share,
top_brand_id, top_brand_share, segments_json, eponymous_id
FROM {$tbl_ci}
WHERE wp_user_id = %d
ORDER BY updated_at DESC LIMIT 1",
$user_id
), ARRAY_A);
if (!$row) { return null; }
$tax = self::priority_taxonomy();
$slug = (string) $row['inferred_top_priority'];
return array(
'customer_id' => $user_id,
'pseudonym' => 'Customer #' . $user_id,
'priority_slug' => $slug,
'priority_label' => $tax[$slug] ?? $slug,
'confidence' => (float) $row['inferred_priorities_confidence'],
'source' => (string) $row['inferred_priorities_source'],
'updated_at' => (string) $row['inferred_priorities_updated_at'],
'priorities_json' => (string) $row['inferred_priorities_json'],
'price_band' => (isset($row['price_band']) && (int) $row['price_band'] > 0) ? (int) $row['price_band'] : null,
'discount_share' => isset($row['discount_share']) ? (float) $row['discount_share'] : 0.0,
'top_brand_id' => (int) $row['top_brand_id'],
'top_brand_share' => isset($row['top_brand_share']) ? (float) $row['top_brand_share'] : 0.0,
'segments_json' => (string) ($row['segments_json'] ?? ''),
);
}
public static function find_customer_lookalikes( $user_id, $n = 10 ) {
$user_id = (int) $user_id;
$n = max(1, min(50, (int) $n));
if ($user_id <= 0) { return array(); }
global $wpdb;
$tbl_ci = $wpdb->prefix . 'aieo_customer_intelligence';
$anchor = $wpdb->get_row($wpdb->prepare(
"SELECT * FROM {$tbl_ci} WHERE wp_user_id = %d LIMIT 1",
$user_id
), ARRAY_A);
if (!$anchor) { return array(); }
$rows = $wpdb->get_results($wpdb->prepare(
"SELECT * FROM {$tbl_ci}
WHERE wp_user_id <> %d
ORDER BY updated_at DESC
LIMIT 5000",
$user_id
), ARRAY_A);
if (empty($rows)) { return array(); }
$instance = self::get_instance_or_null();
if ($instance === null) { return array(); }
$anchor_vec = $instance->build_knn_feature_vector($anchor);
$distances = array();
foreach ($rows as $r) {
$vec = $instance->build_knn_feature_vector($r);
$d = 0.0;
foreach ($anchor_vec as $k => $v) {
$other = $vec[$k] ?? 0.0;
$d += ($v - $other) * ($v - $other);
}
$distances[(int) $r['wp_user_id']] = sqrt($d);
}
asort($distances, SORT_NUMERIC);
$out = array();
$i = 0;
foreach ($distances as $uid => $dist) {
$out[] = array(
'user_id' => (int) $uid,
'pseudonym' => 'Customer #' . (int) $uid,
'distance' => round((float) $dist, 4),
);
if (++$i >= $n) { break; }
}
return $out;
}
private static function get_instance_or_null() {
if (!class_exists('AIEO_DMM')) { return null; }
$dmm = AIEO_DMM::instance();
if (!$dmm) { return null; }
$mod = $dmm->get_module('surveys');
return $mod instanceof self ? $mod : null;
}
public function compute_inferred_priorities_heuristic() {
global $wpdb;
$tbl_ci = $wpdb->prefix . 'aieo_customer_intelligence';
$col_exists = (string) $wpdb->get_var($wpdb->prepare(
"SELECT COLUMN_NAME FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s
LIMIT 1",
$tbl_ci,
'inferred_top_priority'
));
if ($col_exists === '') {
if (function_exists('aieo_debug_log')) {
aieo_debug_log('AIEO_Surveys heuristic: inferred_top_priority column missing on ' . $tbl_ci . ' — schema v7 install likely did not fire. Check Web Push module maybe_install.');
}
delete_option('aieo_push_schema_version');
if (class_exists('AIEO_DMM_Web_Push') && method_exists('AIEO_DMM_Web_Push', 'instance')) {
$wp = AIEO_DMM_Web_Push::instance();
if (method_exists($wp, 'maybe_install')) {
$wp->maybe_install();
}
}
$col_exists = (string) $wpdb->get_var($wpdb->prepare(
"SELECT COLUMN_NAME FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s
LIMIT 1",
$tbl_ci,
'inferred_top_priority'
));
if ($col_exists === '') {
if (function_exists('aieo_debug_log')) {
aieo_debug_log('AIEO_Surveys heuristic: column STILL missing after forced re-install. Manual ALTER needed.');
}
return false;
}
}
$sql = "UPDATE {$tbl_ci} ci
SET
ci.inferred_top_priority = CASE
WHEN JSON_SEARCH(IFNULL(segments_json,'[]'), 'one', 'newbie') IS NOT NULL THEN 'know_products'
WHEN JSON_SEARCH(IFNULL(segments_json,'[]'), 'one', 'replenisher') IS NOT NULL THEN 'know_products'
WHEN JSON_SEARCH(IFNULL(segments_json,'[]'), 'one', 'loyalist') IS NOT NULL THEN 'store_trust'
WHEN JSON_SEARCH(IFNULL(segments_json,'[]'), 'one', 'brand_devoted') IS NOT NULL THEN 'store_trust'
WHEN JSON_SEARCH(IFNULL(segments_json,'[]'), 'one', 'vip') IS NOT NULL THEN 'quality'
WHEN aov > 0 AND aov < 30 AND orders_count >= 2 THEN 'price'
WHEN aov >= 60 AND orders_count >= 2 THEN 'quality'
ELSE 'general'
END,
ci.inferred_priorities_confidence = CASE
WHEN JSON_SEARCH(IFNULL(segments_json,'[]'), 'one', 'newbie') IS NOT NULL THEN 0.90
WHEN JSON_SEARCH(IFNULL(segments_json,'[]'), 'one', 'replenisher') IS NOT NULL THEN 0.85
WHEN JSON_SEARCH(IFNULL(segments_json,'[]'), 'one', 'loyalist') IS NOT NULL THEN 0.95
WHEN JSON_SEARCH(IFNULL(segments_json,'[]'), 'one', 'brand_devoted') IS NOT NULL THEN 0.85
WHEN JSON_SEARCH(IFNULL(segments_json,'[]'), 'one', 'vip') IS NOT NULL THEN 0.70
WHEN aov > 0 AND aov < 30 AND orders_count >= 2 THEN 0.55
WHEN aov >= 60 AND orders_count >= 2 THEN 0.55
ELSE 0.00
END,
ci.inferred_priorities_json = CONCAT(
'{\"basis\":\"',
CASE
WHEN JSON_SEARCH(IFNULL(segments_json,'[]'), 'one', 'newbie') IS NOT NULL THEN 'segment:newbie'
WHEN JSON_SEARCH(IFNULL(segments_json,'[]'), 'one', 'replenisher') IS NOT NULL THEN 'segment:replenisher'
WHEN JSON_SEARCH(IFNULL(segments_json,'[]'), 'one', 'loyalist') IS NOT NULL THEN 'segment:loyalist'
WHEN JSON_SEARCH(IFNULL(segments_json,'[]'), 'one', 'brand_devoted') IS NOT NULL THEN 'segment:brand_devoted'
WHEN JSON_SEARCH(IFNULL(segments_json,'[]'), 'one', 'vip') IS NOT NULL THEN 'segment:vip'
WHEN aov > 0 AND aov < 30 AND orders_count >= 2 THEN 'aov_low_repeat'
WHEN aov >= 60 AND orders_count >= 2 THEN 'aov_high_repeat'
ELSE 'no_signal'
END,
'\"}'
),
ci.inferred_priorities_source = 'heuristic_v3',
ci.inferred_priorities_updated_at = UTC_TIMESTAMP()";
$wpdb->query($sql);
return true;
}
public function validate_knn_leave_one_out() {
$cache_key = 'aieo_surveys_knn_loo_eval';
$cached = get_transient($cache_key);
if (is_array($cached) && isset($cached['total'])) {
return $cached;
}
global $wpdb;
$tbl_ci = $wpdb->prefix . 'aieo_customer_intelligence';
$tbl_si = $wpdb->prefix . 'aieo_survey_intelligence';
$rows = $wpdb->get_results(
"SELECT ci.eponymous_id, ci.ltv_total, ci.aov, ci.orders_count, ci.recency_days,
ci.top_brand_share, ci.monetary_band, ci.frequency_band, ci.recency_band,
ci.discount_share, ci.price_band,
ci.sole_order_share, ci.avg_jlw, ci.avg_vlw,
ci.core_needs_json,
ci.emails_sent_lifetime, ci.open_rate_lifetime,
ci.click_rate_lifetime, ci.days_since_last_engagement,
ci.taste_vector,
ci.segments_json, ci.dormancy_cohort, si.personal_priorities
FROM {$tbl_ci} ci
JOIN {$tbl_si} si ON si.eponymous_id = ci.eponymous_id
WHERE si.f18_entry_id > 0
AND si.personal_priorities IS NOT NULL AND si.personal_priorities <> ''",
ARRAY_A
);
if (empty($rows)) {
return array('total' => 0, 'top1' => 0, 'top3' => 0, 'top1_pct' => 0, 'top3_pct' => 0);
}
$grank_to_label = $this->get_f18_priority_map();
if (empty($grank_to_label)) {
return array('total' => 0, 'top1' => 0, 'top3' => 0, 'top1_pct' => 0, 'top3_pct' => 0);
}
$label_to_slug = $this->build_label_to_slug_map($grank_to_label);
$default_csv = implode(',', array_keys($grank_to_label));
$labeled = array();
foreach ($rows as $r) {
$cleaned = preg_replace('/\s+/', '', (string) $r['personal_priorities']);
if ($cleaned === $default_csv) { continue; }
$slugs = array();
foreach (explode(',', (string) $r['personal_priorities']) as $g) {
$g = trim($g);
if (!isset($grank_to_label[$g])) { continue; }
$label = $grank_to_label[$g];
if (isset($label_to_slug[$label])) { $slugs[] = $label_to_slug[$label]; }
}
if (empty($slugs)) { continue; }
$needs = !empty($r['core_needs_json']) ? json_decode((string) $r['core_needs_json'], true) : array();
$taste_raw = $this->unpack_taste_vector($r['taste_vector'] ?? null);
$taste_norm = $taste_raw === null ? null : $this->normalize_vector($taste_raw);
$labeled[] = array(
'epo' => (int) $r['eponymous_id'],
'vec' => $this->build_knn_feature_vector($r),
'needs' => is_array($needs) ? $needs : array(),
'taste' => $taste_norm,
'top1' => $slugs[0],
'top3' => array_slice($slugs, 0, 3),
);
}
$n = count($labeled);
if ($n < 50) {
return array('total' => 0, 'top1' => 0, 'top3' => 0, 'top1_pct' => 0, 'top3_pct' => 0);
}
$LOO_MAX = 400;
if ($n > $LOO_MAX) {
$idx = range(0, $n - 1);
shuffle($idx);
$sampled = array();
foreach (array_slice($idx, 0, $LOO_MAX) as $kk) { $sampled[] = $labeled[$kk]; }
$labeled = $sampled;
$n = count($labeled);
}
$k = min(20, $n - 1);
$NEEDS_WEIGHT = 3.0;
$TASTE_WEIGHT = 4.0;
$top1_hits = 0;
$top3_hits = 0;
for ($i = 0; $i < $n; $i++) {
$self = $labeled[$i];
$self_needs = $self['needs'];
$self_taste = $self['taste'];
$dim = count($self['vec']);
$distances = array();
for ($j = 0; $j < $n; $j++) {
if ($j === $i) { continue; }
$other = $labeled[$j];
$d = 0.0;
for ($f = 0; $f < $dim; $f++) {
$delta = $self['vec'][$f] - $other['vec'][$f];
$d += $delta * $delta;
}
if (!empty($self_needs) && !empty($other['needs'])) {
$cos = $this->needs_cosine_similarity($self_needs, $other['needs']);
$needs_dist = 1.0 - $cos;
$d += pow($needs_dist * $NEEDS_WEIGHT, 2);
}
if ($self_taste !== null && $other['taste'] !== null) {
$tcos = $this->dot_product($self_taste, $other['taste']);
$taste_dist = 1.0 - $tcos;
$d += pow($taste_dist * $TASTE_WEIGHT, 2);
}
$distances[$j] = $d;
}
asort($distances);
$top_idx = array_slice($distances, 0, $k, true);
$vote = array();
foreach ($top_idx as $j => $sq_dist) {
$weight = 1.0 / (1.0 + sqrt($sq_dist));
foreach ($labeled[$j]['top3'] as $rank_idx => $slug) {
$rank_w = (3 - $rank_idx) / 3.0;
if (!isset($vote[$slug])) { $vote[$slug] = 0.0; }
$vote[$slug] += $weight * $rank_w;
}
}
if (empty($vote)) { continue; }
arsort($vote);
$predicted = key($vote);
if ($predicted === $self['top1']) { $top1_hits++; }
if (in_array($predicted, $self['top3'], true)) { $top3_hits++; }
}
$result = array(
'total' => $n,
'top1' => $top1_hits,
'top3' => $top3_hits,
'top1_pct' => $n > 0 ? round(100 * $top1_hits / $n, 1) : 0,
'top3_pct' => $n > 0 ? round(100 * $top3_hits / $n, 1) : 0,
);
set_transient($cache_key, $result, DAY_IN_SECONDS);
return $result;
}
public function compute_position_signals_from_temp_orders() {
global $wpdb;
$tbl_ci = $wpdb->prefix . 'aieo_customer_intelligence';
$snapshots = $this->discover_order_snapshot_tables();
if (empty($snapshots)) { return false; }
$parts = array();
foreach ($snapshots as $t) {
$parts[] = "SELECT EponymousID, OrderId, ProductId, ItemSequence, OrderSize, "
. "JourneyLinearWeight, LineValue, OrderItemsTotal "
. "FROM `{$t}` WHERE EponymousID > 0 AND ProductId > 0 AND ItemSequence > 0";
}
$union = implode(' UNION ALL ', $parts);
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_pos_lines");
$wpdb->query(
"CREATE TEMPORARY TABLE _aieo_pos_lines (
EponymousID BIGINT UNSIGNED NOT NULL,
OrderId BIGINT UNSIGNED NOT NULL,
ProductId BIGINT UNSIGNED NOT NULL,
ItemSequence INT UNSIGNED NOT NULL,
OrderSize INT UNSIGNED NOT NULL,
JourneyLinearWeight DECIMAL(10,6) NULL,
LineValue DECIMAL(12,4) NULL,
OrderItemsTotal DECIMAL(12,4) NULL,
KEY idx_epo (EponymousID),
KEY idx_epo_prod (EponymousID, ProductId)
) ENGINE=MEMORY"
);
$wpdb->query("INSERT INTO _aieo_pos_lines SELECT * FROM ({$union}) raw");
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_pos_scalars");
$wpdb->query(
"CREATE TEMPORARY TABLE _aieo_pos_scalars (
EponymousID BIGINT UNSIGNED PRIMARY KEY,
total_orders INT UNSIGNED NOT NULL DEFAULT 0,
sole_orders INT UNSIGNED NOT NULL DEFAULT 0,
avg_jlw DECIMAL(5,4) NOT NULL DEFAULT 0,
avg_vlw DECIMAL(5,4) NOT NULL DEFAULT 0
) ENGINE=MEMORY"
);
$wpdb->query(
"INSERT INTO _aieo_pos_scalars (EponymousID, total_orders, sole_orders, avg_jlw, avg_vlw)
SELECT
EponymousID,
COUNT(DISTINCT OrderId) AS total_orders,
COUNT(DISTINCT IF(OrderSize = 1, OrderId, NULL)) AS sole_orders,
ROUND(AVG(IFNULL(JourneyLinearWeight, ItemSequence / NULLIF(OrderSize, 0))), 4) AS avg_jlw,
ROUND(AVG(LineValue / NULLIF(OrderItemsTotal, 0)), 4) AS avg_vlw
FROM _aieo_pos_lines
GROUP BY EponymousID"
);
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_pos_top");
$wpdb->query(
"CREATE TEMPORARY TABLE _aieo_pos_top (
EponymousID BIGINT UNSIGNED PRIMARY KEY,
top_sole_product_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
top_first_product_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
top_last_product_id BIGINT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=MEMORY"
);
$wpdb->query(
"INSERT INTO _aieo_pos_top (EponymousID, top_sole_product_id, top_first_product_id, top_last_product_id)
SELECT
EponymousID,
MAX(IF(class = 'sole' AND rn = 1, ProductId, 0)) AS top_sole_product_id,
MAX(IF(class = 'first' AND rn = 1, ProductId, 0)) AS top_first_product_id,
MAX(IF(class = 'last' AND rn = 1, ProductId, 0)) AS top_last_product_id
FROM (
SELECT
EponymousID, ProductId, class, hits,
ROW_NUMBER() OVER (PARTITION BY EponymousID, class ORDER BY hits DESC, ProductId ASC) AS rn
FROM (
SELECT EponymousID, ProductId, 'sole' AS class, COUNT(*) AS hits
FROM _aieo_pos_lines
WHERE OrderSize = 1
GROUP BY EponymousID, ProductId
UNION ALL
SELECT EponymousID, ProductId, 'first' AS class, COUNT(*) AS hits
FROM _aieo_pos_lines
WHERE ItemSequence = 1 AND OrderSize > 1
GROUP BY EponymousID, ProductId
UNION ALL
SELECT EponymousID, ProductId, 'last' AS class, COUNT(*) AS hits
FROM _aieo_pos_lines
WHERE ItemSequence = OrderSize AND OrderSize > 1
GROUP BY EponymousID, ProductId
) class_counts
) ranked
WHERE rn = 1
GROUP BY EponymousID"
);
$wpdb->query(
"UPDATE {$tbl_ci} ci
LEFT JOIN _aieo_pos_scalars ps ON ps.EponymousID = ci.eponymous_id
LEFT JOIN _aieo_pos_top pt ON pt.EponymousID = ci.eponymous_id
SET
ci.sole_order_share = IFNULL(ps.sole_orders / NULLIF(ps.total_orders, 0), 0),
ci.avg_jlw = IFNULL(ps.avg_jlw, 0),
ci.avg_vlw = IFNULL(ps.avg_vlw, 0),
ci.top_sole_product_id = IFNULL(pt.top_sole_product_id, 0),
ci.top_first_product_id = IFNULL(pt.top_first_product_id, 0),
ci.top_last_product_id = IFNULL(pt.top_last_product_id, 0)"
);
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_pos_lines");
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_pos_scalars");
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_pos_top");
return array(
'with_sole_share' => (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl_ci} WHERE sole_order_share > 0"),
'pure_intent_customers' => (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl_ci} WHERE sole_order_share >= 0.8"),
'avg_sole_share' => (float) $wpdb->get_var("SELECT ROUND(AVG(sole_order_share), 4) FROM {$tbl_ci} WHERE sole_order_share > 0"),
);
}
public function compute_tnp_engagement_signals() {
global $wpdb;
$tbl_ci = $wpdb->prefix . 'aieo_customer_intelligence';
$tnp = $wpdb->prefix . 'newsletter';
$exists = (string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tnp)) === $tnp;
if (!$exists) {
return array('skipped' => 'tnp_not_installed');
}
$has_subs = (int) $wpdb->get_var("SELECT 1 FROM {$tnp} LIMIT 1");
$has_emails = (int) $wpdb->get_var("SELECT 1 FROM {$tbl_ci} WHERE email <> '' LIMIT 1");
if (!$has_subs || !$has_emails) {
return array('skipped' => 'no_data', 'has_subs' => $has_subs, 'has_emails' => $has_emails);
}
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_tnp_bridge");
$wpdb->query(
"CREATE TEMPORARY TABLE _aieo_tnp_bridge (
eponymous_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
tnp_subscriber_id INT UNSIGNED NOT NULL,
tnp_status CHAR(1) NOT NULL,
KEY idx_subscriber (tnp_subscriber_id)
) ENGINE=MEMORY"
);
$tnp_email_collation = (string) $wpdb->get_var($wpdb->prepare(
"SELECT COLLATION_NAME FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = %s
AND COLUMN_NAME = 'email'",
$tnp
));
if ($tnp_email_collation === '') { $tnp_email_collation = 'utf8mb4_unicode_520_ci'; }
$tnp_email_collation = preg_replace('/[^a-z0-9_]/i', '', $tnp_email_collation);
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_ci_emails");
$wpdb->query(
"CREATE TEMPORARY TABLE _aieo_ci_emails (
eponymous_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
email VARCHAR(190) NOT NULL COLLATE {$tnp_email_collation},
KEY idx_email (email)
) ENGINE=MEMORY"
);
$wpdb->query(
"INSERT INTO _aieo_ci_emails (eponymous_id, email)
SELECT eponymous_id, email FROM {$tbl_ci} WHERE email <> ''"
);
$wpdb->query(
"INSERT INTO _aieo_tnp_bridge (eponymous_id, tnp_subscriber_id, tnp_status)
SELECT c.eponymous_id, n.id, n.status
FROM _aieo_ci_emails c
JOIN {$wpdb->prefix}newsletter n ON n.email = c.email
ON DUPLICATE KEY UPDATE
tnp_subscriber_id = VALUES(tnp_subscriber_id),
tnp_status = VALUES(tnp_status)"
);
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_ci_emails");
$thirty_days_ago_unix = time() - (30 * 86400);
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_tnp_sent_agg");
$wpdb->query(
"CREATE TEMPORARY TABLE _aieo_tnp_sent_agg (
tnp_subscriber_id INT UNSIGNED NOT NULL PRIMARY KEY,
sent_lifetime INT UNSIGNED NOT NULL DEFAULT 0,
opened_lifetime INT UNSIGNED NOT NULL DEFAULT 0,
sent_30d SMALLINT UNSIGNED NOT NULL DEFAULT 0,
opened_30d SMALLINT UNSIGNED NOT NULL DEFAULT 0,
last_open_unix INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=MEMORY"
);
$wpdb->query($wpdb->prepare(
"INSERT INTO _aieo_tnp_sent_agg
(tnp_subscriber_id, sent_lifetime, opened_lifetime, sent_30d, opened_30d, last_open_unix)
SELECT
s.user_id,
COUNT(*) AS sent_lifetime,
SUM(IF(s.open > 0, 1, 0)) AS opened_lifetime,
SUM(IF(s.time >= %d, 1, 0)) AS sent_30d,
SUM(IF(s.time >= %d AND s.open > 0, 1, 0)) AS opened_30d,
MAX(IF(s.open > 0, s.time, 0)) AS last_open_unix
FROM {$wpdb->prefix}newsletter_sent s
JOIN _aieo_tnp_bridge b ON b.tnp_subscriber_id = s.user_id
GROUP BY s.user_id",
$thirty_days_ago_unix,
$thirty_days_ago_unix
));
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_tnp_click_agg");
$wpdb->query(
"CREATE TEMPORARY TABLE _aieo_tnp_click_agg (
tnp_subscriber_id INT UNSIGNED NOT NULL PRIMARY KEY,
clicked_lifetime INT UNSIGNED NOT NULL DEFAULT 0,
last_click_at DATETIME NULL
) ENGINE=MEMORY"
);
$wpdb->query(
"INSERT INTO _aieo_tnp_click_agg (tnp_subscriber_id, clicked_lifetime, last_click_at)
SELECT
st.user_id,
COUNT(DISTINCT st.email_id) AS clicked_lifetime,
MAX(st.created) AS last_click_at
FROM {$wpdb->prefix}newsletter_stats st
JOIN _aieo_tnp_bridge b ON b.tnp_subscriber_id = st.user_id
WHERE st.user_id > 0
GROUP BY st.user_id"
);
$wpdb->query(
"UPDATE {$tbl_ci} ci
LEFT JOIN _aieo_tnp_bridge b ON b.eponymous_id = ci.eponymous_id
LEFT JOIN _aieo_tnp_sent_agg sa ON sa.tnp_subscriber_id = b.tnp_subscriber_id
LEFT JOIN _aieo_tnp_click_agg ca ON ca.tnp_subscriber_id = b.tnp_subscriber_id
SET
ci.tnp_subscriber_id = IFNULL(b.tnp_subscriber_id, 0),
ci.tnp_status = IFNULL(b.tnp_status, ''),
ci.emails_sent_lifetime = IFNULL(sa.sent_lifetime, 0),
ci.emails_opened_lifetime = IFNULL(sa.opened_lifetime, 0),
ci.emails_clicked_lifetime= IFNULL(ca.clicked_lifetime, 0),
ci.emails_sent_30d = IFNULL(sa.sent_30d, 0),
ci.emails_opened_30d = IFNULL(sa.opened_30d, 0),
ci.open_rate_lifetime = IFNULL(ROUND(sa.opened_lifetime / NULLIF(sa.sent_lifetime, 0), 4), 0),
ci.open_rate_30d = IFNULL(ROUND(sa.opened_30d / NULLIF(sa.sent_30d, 0), 4), 0),
ci.click_rate_lifetime = IFNULL(ROUND(ca.clicked_lifetime / NULLIF(sa.sent_lifetime, 0), 4), 0),
ci.last_email_open_at = IF(IFNULL(sa.last_open_unix, 0) > 0, DATE_ADD('1970-01-01 00:00:00', INTERVAL sa.last_open_unix SECOND), NULL),
ci.last_email_click_at = ca.last_click_at,
ci.days_since_last_engagement = LEAST(
65535,
GREATEST(
0,
DATEDIFF(
UTC_TIMESTAMP(),
GREATEST(
IF(IFNULL(sa.last_open_unix, 0) > 0, DATE_ADD('1970-01-01 00:00:00', INTERVAL sa.last_open_unix SECOND), '1970-01-01'),
IFNULL(ca.last_click_at, '1970-01-01')
)
)
)
)"
);
$wpdb->query(
"UPDATE {$tbl_ci}
SET days_since_last_engagement = 65535
WHERE last_email_open_at IS NULL AND last_email_click_at IS NULL"
);
$stats = array(
'matched_subscribers' => (int) $wpdb->get_var("SELECT COUNT(DISTINCT email) FROM {$tbl_ci} WHERE email <> '' AND tnp_subscriber_id > 0"),
'engaged_lifetime' => (int) $wpdb->get_var("SELECT COUNT(DISTINCT email) FROM {$tbl_ci} WHERE email <> '' AND emails_opened_lifetime > 0"),
'engaged_30d' => (int) $wpdb->get_var("SELECT COUNT(DISTINCT email) FROM {$tbl_ci} WHERE email <> '' AND emails_opened_30d > 0"),
'avg_open_rate_lifetime' => (float) $wpdb->get_var("SELECT ROUND(AVG(open_rate_lifetime), 4) FROM {$tbl_ci} WHERE emails_sent_lifetime >= 3"),
'avg_click_rate_lifetime' => (float) $wpdb->get_var("SELECT ROUND(AVG(click_rate_lifetime), 4) FROM {$tbl_ci} WHERE emails_sent_lifetime >= 3"),
);
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_tnp_click_agg");
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_tnp_sent_agg");
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_tnp_bridge");
return $stats;
}
public function compute_core_needs_from_top_products() {
global $wpdb;
$tbl_ci = $wpdb->prefix . 'aieo_customer_intelligence';
$tbl_tr = $wpdb->prefix . 'term_relationships';
$tbl_tt = $wpdb->prefix . 'term_taxonomy';
$has_pa_need = (int) $wpdb->get_var(
$wpdb->prepare("SELECT COUNT(*) FROM {$tbl_tt} WHERE taxonomy = %s", 'pa_need')
);
if ($has_pa_need === 0) { return false; }
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_core_needs");
$wpdb->query(
"CREATE TEMPORARY TABLE _aieo_core_needs (
eponymous_id BIGINT UNSIGNED NOT NULL,
need_term_id BIGINT UNSIGNED NOT NULL,
weight TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (eponymous_id, need_term_id),
KEY idx_epo (eponymous_id)
) ENGINE=MEMORY"
);
foreach (array(
array('column' => 'top_sole_product_id', 'weight' => 3),
array('column' => 'top_first_product_id', 'weight' => 2),
array('column' => 'top_last_product_id', 'weight' => 1),
) as $pass) {
$wpdb->query(
"INSERT INTO _aieo_core_needs (eponymous_id, need_term_id, weight)
SELECT ci.eponymous_id, tt.term_id, %d
FROM {$tbl_ci} ci
JOIN {$tbl_tr} tr ON tr.object_id = ci.{$pass['column']}
JOIN {$tbl_tt} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id AND tt.taxonomy = 'pa_need'
WHERE ci.{$pass['column']} > 0
ON DUPLICATE KEY UPDATE weight = weight + %d"
);
}
$wpdb->query("DELETE FROM _aieo_core_needs");
foreach (array(
array('column' => 'top_sole_product_id', 'weight' => 3),
array('column' => 'top_first_product_id', 'weight' => 2),
array('column' => 'top_last_product_id', 'weight' => 1),
) as $pass) {
$wpdb->query($wpdb->prepare(
"INSERT INTO _aieo_core_needs (eponymous_id, need_term_id, weight)
SELECT ci.eponymous_id, tt.term_id, %d
FROM {$tbl_ci} ci
JOIN {$tbl_tr} tr ON tr.object_id = ci.{$pass['column']}
JOIN {$tbl_tt} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id AND tt.taxonomy = 'pa_need'
WHERE ci.{$pass['column']} > 0
ON DUPLICATE KEY UPDATE weight = weight + %d",
$pass['weight'],
$pass['weight']
));
}
$wpdb->query(
"UPDATE {$tbl_ci} ci
LEFT JOIN (
SELECT
eponymous_id,
CONCAT('{',
GROUP_CONCAT(CONCAT('\"', need_term_id, '\":', weight) ORDER BY weight DESC, need_term_id ASC SEPARATOR ','),
'}') AS json
FROM _aieo_core_needs
GROUP BY eponymous_id
) cn ON cn.eponymous_id = ci.eponymous_id
SET ci.core_needs_json = IFNULL(cn.json, NULL)"
);
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_core_needs");
return array(
'customers_with_needs' => (int) $wpdb->get_var(
"SELECT COUNT(*) FROM {$tbl_ci} WHERE core_needs_json IS NOT NULL AND core_needs_json <> ''"
),
);
}
public function compute_discount_and_price_from_wc() {
global $wpdb;
$tbl_ci = $wpdb->prefix . 'aieo_customer_intelligence';
$tbl_oi = $wpdb->prefix . 'woocommerce_order_items';
$tbl_oim = $wpdb->prefix . 'woocommerce_order_itemmeta';
if ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tbl_oi)) !== $tbl_oi) {
return false;
}
$snapshots = $this->discover_order_snapshot_tables();
if (empty($snapshots)) { return false; }
$parts = array();
foreach ($snapshots as $t) {
$parts[] = "SELECT DISTINCT OrderId, EponymousID FROM `{$t}` WHERE OrderId > 0 AND EponymousID IS NOT NULL AND EponymousID > 0";
}
$union = implode(' UNION ALL ', $parts);
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_epo_orders");
$wpdb->query(
"CREATE TEMPORARY TABLE _aieo_epo_orders (
OrderId BIGINT UNSIGNED NOT NULL,
EponymousID BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (OrderId, EponymousID),
KEY idx_epo (EponymousID)
) ENGINE=MEMORY"
);
$wpdb->query(
"INSERT IGNORE INTO _aieo_epo_orders (OrderId, EponymousID)
SELECT OrderId, EponymousID FROM ({$union}) raw"
);
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_wc_prices");
$wpdb->query(
"CREATE TEMPORARY TABLE _aieo_wc_prices (
EponymousID BIGINT UNSIGNED NOT NULL PRIMARY KEY,
discounted_lines INT UNSIGNED NOT NULL DEFAULT 0,
total_lines INT UNSIGNED NOT NULL DEFAULT 0,
avg_unit_price DECIMAL(10,2) NOT NULL DEFAULT 0
) ENGINE=MEMORY"
);
$wpdb->query(
"INSERT INTO _aieo_wc_prices (EponymousID, discounted_lines, total_lines, avg_unit_price)
SELECT
ep.EponymousID,
SUM(IF(
CAST(IFNULL(oim_lt.meta_value, '0') AS DECIMAL(12,4))
<
CAST(IFNULL(oim_ls.meta_value, '0') AS DECIMAL(12,4))
AND CAST(IFNULL(oim_ls.meta_value, '0') AS DECIMAL(12,4)) > 0,
1, 0)) AS discounted_lines,
COUNT(*) AS total_lines,
AVG(
IF(
CAST(IFNULL(oim_q.meta_value, '0') AS DECIMAL(12,4)) > 0
AND CAST(IFNULL(oim_ls.meta_value, '0') AS DECIMAL(12,4)) > 0,
CAST(oim_ls.meta_value AS DECIMAL(12,4))
/ CAST(oim_q.meta_value AS DECIMAL(12,4)),
NULL
)
) AS avg_unit_price
FROM _aieo_epo_orders ep
JOIN {$tbl_oi} oi
ON oi.order_id = ep.OrderId AND oi.order_item_type = 'line_item'
LEFT JOIN {$tbl_oim} oim_ls
ON oim_ls.order_item_id = oi.order_item_id AND oim_ls.meta_key = '_line_subtotal'
LEFT JOIN {$tbl_oim} oim_lt
ON oim_lt.order_item_id = oi.order_item_id AND oim_lt.meta_key = '_line_total'
LEFT JOIN {$tbl_oim} oim_q
ON oim_q.order_item_id = oi.order_item_id AND oim_q.meta_key = '_qty'
GROUP BY ep.EponymousID"
);
$wpdb->query(
"UPDATE {$tbl_ci} ci
LEFT JOIN _aieo_wc_prices wc ON wc.EponymousID = ci.eponymous_id
SET
ci.discount_share = IFNULL(wc.discounted_lines / NULLIF(wc.total_lines, 0), 0),
ci.avg_item_price = IFNULL(wc.avg_unit_price, 0)"
);
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_price_band");
$wpdb->query(
"CREATE TEMPORARY TABLE _aieo_price_band (
eponymous_id BIGINT UNSIGNED PRIMARY KEY,
pb TINYINT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=MEMORY"
);
$wpdb->query(
"INSERT INTO _aieo_price_band (eponymous_id, pb)
SELECT eponymous_id,
NTILE(5) OVER (ORDER BY avg_item_price ASC, eponymous_id ASC)
FROM {$tbl_ci}
WHERE avg_item_price > 0"
);
$wpdb->query(
"UPDATE {$tbl_ci} ci
LEFT JOIN _aieo_price_band pb ON pb.eponymous_id = ci.eponymous_id
SET ci.price_band = IFNULL(pb.pb, 0)"
);
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_wc_prices");
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_epo_orders");
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_price_band");
return array(
'with_discount_data' => (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl_ci} WHERE discount_share > 0"),
'with_price_band' => (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl_ci} WHERE price_band > 0"),
'avg_discount_share' => (float) $wpdb->get_var("SELECT ROUND(AVG(discount_share), 4) FROM {$tbl_ci} WHERE discount_share > 0"),
);
}
public function compute_inferred_priorities_knn() {
global $wpdb;
$tbl_ci = $wpdb->prefix . 'aieo_customer_intelligence';
$tbl_si = $wpdb->prefix . 'aieo_survey_intelligence';
@set_time_limit(0);
$rows = $wpdb->get_results(
"SELECT eponymous_id, ltv_total, aov, orders_count, recency_days,
top_brand_share, monetary_band, frequency_band, recency_band,
discount_share, price_band,
sole_order_share, avg_jlw, avg_vlw,
core_needs_json,
emails_sent_lifetime, open_rate_lifetime,
click_rate_lifetime, days_since_last_engagement,
taste_vector,
segments_json, dormancy_cohort
FROM {$tbl_ci}",
ARRAY_A
);
if (empty($rows)) { return false; }
$features = array();
$customer_needs = array();
$customer_taste = array();
foreach ($rows as $r) {
$epo = (int) $r['eponymous_id'];
$features[$epo] = $this->build_knn_feature_vector($r);
$needs = !empty($r['core_needs_json']) ? json_decode((string) $r['core_needs_json'], true) : array();
$customer_needs[$epo] = is_array($needs) ? $needs : array();
$taste_raw = $this->unpack_taste_vector($r['taste_vector'] ?? null);
$customer_taste[$epo] = $taste_raw === null ? null : $this->normalize_vector($taste_raw);
}
$grank_to_label = $this->get_f18_priority_map();
if (empty($grank_to_label)) { return false; }
$label_to_slug = $this->build_label_to_slug_map($grank_to_label);
$default_csv = implode(',', array_keys($grank_to_label));
$f18_rows = $wpdb->get_results(
"SELECT eponymous_id, personal_priorities
FROM {$tbl_si}
WHERE f18_entry_id > 0
AND personal_priorities IS NOT NULL AND personal_priorities <> ''",
ARRAY_A
);
$labeled = array();
foreach ($f18_rows as $r) {
$epo = (int) $r['eponymous_id'];
if (!isset($features[$epo])) { continue; }
$cleaned = preg_replace('/\s+/', '', (string) $r['personal_priorities']);
if ($cleaned === $default_csv) { continue; }
$slugs = array();
foreach (explode(',', (string) $r['personal_priorities']) as $g) {
$g = trim($g);
if (!isset($grank_to_label[$g])) { continue; }
$label = $grank_to_label[$g];
if (isset($label_to_slug[$label])) { $slugs[] = $label_to_slug[$label]; }
}
if (empty($slugs)) { continue; }
$labeled[$epo] = array(
'top1' => $slugs[0],
'top3' => array_slice($slugs, 0, 3),
);
}
if (count($labeled) < 50) {
return false;
}
$labeled_epos = array_keys($labeled);
$labeled_vectors = array();
$labeled_needs = array();
$labeled_taste = array();
foreach ($labeled_epos as $l_epo) {
$labeled_vectors[] = $features[$l_epo];
$labeled_needs[] = isset($customer_needs[$l_epo]) ? $customer_needs[$l_epo] : array();
$labeled_taste[] = isset($customer_taste[$l_epo]) ? $customer_taste[$l_epo] : null;
}
$n_labeled = count($labeled_epos);
$k = min(20, $n_labeled);
$NEEDS_WEIGHT = 3.0;
$TASTE_WEIGHT = 4.0;
$results = array();
$sub_total = count($features);
$sub_processed = 0;
$sub_started_at = microtime(true);
$sub_last_write = $sub_started_at;
$sub_status_seed = get_option(self::STATUS_OPTION, array());
foreach ($features as $epo => $vec) {
if (isset($labeled[$epo])) {
$results[$epo] = array(
'slug' => $labeled[$epo]['top1'],
'conf' => 1.00,
'basis' => 'self_explicit',
);
continue;
}
$dim = count($vec);
$self_needs = isset($customer_needs[$epo]) ? $customer_needs[$epo] : array();
$self_taste = isset($customer_taste[$epo]) ? $customer_taste[$epo] : null;
$distances = array();
foreach ($labeled_vectors as $idx => $lvec) {
$d = 0.0;
for ($i = 0; $i < $dim; $i++) {
$delta = $vec[$i] - $lvec[$i];
$d += $delta * $delta;
}
$other_needs = $labeled_needs[$idx];
if (!empty($self_needs) && !empty($other_needs)) {
$cos = $this->needs_cosine_similarity($self_needs, $other_needs);
$needs_dist = 1.0 - $cos;
$d += pow($needs_dist * $NEEDS_WEIGHT, 2);
}
$other_taste = $labeled_taste[$idx];
if ($self_taste !== null && $other_taste !== null) {
$tcos = $this->dot_product($self_taste, $other_taste);
$taste_dist = 1.0 - $tcos;
$d += pow($taste_dist * $TASTE_WEIGHT, 2);
}
$distances[$idx] = $d;
}
asort($distances);
$top_idx = array_slice($distances, 0, $k, true);
$vote = array();
foreach ($top_idx as $idx => $sq_dist) {
$weight = 1.0 / (1.0 + sqrt($sq_dist));
$l_epo = $labeled_epos[$idx];
foreach ($labeled[$l_epo]['top3'] as $rank_idx => $slug) {
$rank_w = (3 - $rank_idx) / 3.0;
if (!isset($vote[$slug])) { $vote[$slug] = 0.0; }
$vote[$slug] += $weight * $rank_w;
}
}
if (empty($vote)) {
$results[$epo] = array('slug' => 'general', 'conf' => 0.00, 'basis' => 'no_vote');
$sub_processed++;
} else {
arsort($vote);
$total = array_sum($vote);
$top_slug = key($vote);
$top_score = current($vote);
$conf = $total > 0 ? round($top_score / $total, 2) : 0;
$results[$epo] = array('slug' => $top_slug, 'conf' => $conf, 'basis' => 'knn_' . $k);
$sub_processed++;
}
$now = microtime(true);
if (($now - $sub_last_write) >= 30) {
$cur = get_option(self::STATUS_OPTION, $sub_status_seed);
if (is_array($cur)) {
$cur['subprogress'] = array(
'processed' => $sub_processed,
'total' => $sub_total,
'label' => __('Customers scanned (k-NN)', 'ai-eshop-optimizer'),
'elapsed' => (int) round($now - $sub_started_at),
);
update_option(self::STATUS_OPTION, $cur, false);
}
$sub_last_write = $now;
}
}
$cur = get_option(self::STATUS_OPTION, $sub_status_seed);
if (is_array($cur)) {
$cur['subprogress'] = array(
'processed' => $sub_processed,
'total' => $sub_total,
'label' => __('Customers scanned (k-NN)', 'ai-eshop-optimizer'),
'elapsed' => (int) round(microtime(true) - $sub_started_at),
);
update_option(self::STATUS_OPTION, $cur, false);
}
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_knn_results");
$wpdb->query(
"CREATE TEMPORARY TABLE _aieo_knn_results (
eponymous_id BIGINT UNSIGNED PRIMARY KEY,
slug VARCHAR(40) NOT NULL,
conf DECIMAL(3,2) NOT NULL DEFAULT 0,
basis VARCHAR(40) NOT NULL DEFAULT ''
) ENGINE=MEMORY"
);
$chunks = array_chunk($results, 1000, true);
foreach ($chunks as $chunk) {
$values = array();
$args = array();
foreach ($chunk as $epo => $r) {
$values[] = '(%d, %s, %f, %s)';
$args[] = (int) $epo;
$args[] = (string) $r['slug'];
$args[] = (float) $r['conf'];
$args[] = (string) $r['basis'];
}
$sql = "INSERT INTO _aieo_knn_results (eponymous_id, slug, conf, basis) VALUES "
. implode(',', $values);
$wpdb->query($wpdb->prepare($sql, ...$args));
}
$wpdb->query(
"UPDATE {$tbl_ci} ci
JOIN _aieo_knn_results r ON r.eponymous_id = ci.eponymous_id
SET ci.inferred_top_priority = r.slug,
ci.inferred_priorities_confidence = r.conf,
ci.inferred_priorities_json = CONCAT('{\"basis\":\"', r.basis, '\"}'),
ci.inferred_priorities_source = 'knn',
ci.inferred_priorities_updated_at = UTC_TIMESTAMP()"
);
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_knn_results");
delete_transient('aieo_surveys_knn_loo_eval');
return array(
'labeled' => count($labeled),
'total' => count($features),
'k' => $k,
);
}
private function needs_cosine_similarity(array $a, array $b) {
if (empty($a) || empty($b)) { return 0.0; }
$dot = 0.0; $mag_a_sq = 0.0; $mag_b_sq = 0.0;
foreach ($a as $k => $v) {
$v = (float) $v;
$mag_a_sq += $v * $v;
if (isset($b[$k])) {
$dot += $v * (float) $b[$k];
}
}
foreach ($b as $v) {
$v = (float) $v;
$mag_b_sq += $v * $v;
}
if ($mag_a_sq <= 0 || $mag_b_sq <= 0) { return 0.0; }
return $dot / sqrt($mag_a_sq * $mag_b_sq);
}
private function normalize_vector(array $v) {
$sum = 0.0;
foreach ($v as $x) { $sum += $x * $x; }
if ($sum <= 0) { return $v; }
$inv = 1.0 / sqrt($sum);
$out = array();
foreach ($v as $x) { $out[] = $x * $inv; }
return $out;
}
private function dot_product(array $a, array $b) {
$n = min(count($a), count($b));
$d = 0.0;
for ($i = 0; $i < $n; $i++) {
$d += (float) $a[$i] * (float) $b[$i];
}
return $d;
}
private function dense_cosine_similarity(array $a, array $b) {
$n = min(count($a), count($b));
if ($n === 0) { return 0.0; }
$dot = 0.0; $ma = 0.0; $mb = 0.0;
for ($i = 0; $i < $n; $i++) {
$av = (float) $a[$i];
$bv = (float) $b[$i];
$dot += $av * $bv;
$ma += $av * $av;
$mb += $bv * $bv;
}
if ($ma <= 0 || $mb <= 0) { return 0.0; }
return $dot / sqrt($ma * $mb);
}
private function unpack_taste_vector($blob) {
if (empty($blob)) { return null; }
$bytes = strlen($blob);
if ($bytes < 4 || ($bytes % 4) !== 0) { return null; }
$arr = unpack('g*', $blob);
if ($arr === false) { return null; }
return array_values($arr);
}
private function pack_taste_vector(array $vec) {
if (empty($vec)) { return null; }
return pack('g*', ...array_map('floatval', $vec));
}
public function compute_taste_vectors_batch($batch_size = 50) {
global $wpdb;
$tbl_ci = $wpdb->prefix . 'aieo_customer_intelligence';
$tbl_emb = $wpdb->prefix . 'aieo_chat_embeddings';
@set_time_limit(0);
if ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tbl_emb)) !== $tbl_emb) {
return array('skipped' => 'embeddings_table_missing');
}
$limit_clause = $batch_size > 0 ? 'LIMIT ' . (int) $batch_size : '';
$candidates = $wpdb->get_results(
"SELECT eponymous_id,
top_sole_product_id, top_first_product_id, top_last_product_id
FROM {$tbl_ci}
WHERE (top_sole_product_id > 0 OR top_first_product_id > 0 OR top_last_product_id > 0)
AND (taste_vector IS NULL
OR taste_vector_updated_at IS NULL
OR (last_order_at IS NOT NULL AND last_order_at > taste_vector_updated_at))
ORDER BY last_order_at DESC
{$limit_clause}",
ARRAY_A
);
$stats = array(
'candidates' => count($candidates),
'vectorised' => 0,
'skipped_no_embeddings' => 0,
);
if (empty($candidates)) { return $stats; }
$engine = get_option('aieo_chat_embedding_model', 'voyage-2');
$all_pids = array();
foreach ($candidates as $c) {
foreach (array('top_sole_product_id', 'top_first_product_id', 'top_last_product_id') as $col) {
$pid = (int) $c[$col];
if ($pid > 0) { $all_pids[$pid] = true; }
}
}
$all_pids = array_keys($all_pids);
$embeddings_by_pid = array();
$chunk_size = 500;
foreach (array_chunk($all_pids, $chunk_size) as $chunk) {
$placeholders = implode(',', array_fill(0, count($chunk), '%d'));
$sql = "SELECT post_id, embedding
FROM {$tbl_emb}
WHERE post_id IN ({$placeholders})
AND engine_type = %s
AND embedding IS NOT NULL
AND LENGTH(embedding) > 100";
$args = array_merge($chunk, array($engine));
$rows = $wpdb->get_results($wpdb->prepare($sql, ...$args), ARRAY_A);
foreach ($rows as $r) {
$vec = $this->decode_chat_embedding((string) $r['embedding']);
if (is_array($vec) && !empty($vec)) {
$embeddings_by_pid[(int) $r['post_id']] = $vec;
}
}
}
$rows_to_write = array();
foreach ($candidates as $c) {
$vecs = array();
foreach (array('top_sole_product_id', 'top_first_product_id', 'top_last_product_id') as $col) {
$pid = (int) $c[$col];
if ($pid > 0 && isset($embeddings_by_pid[$pid])) {
$vecs[] = $embeddings_by_pid[$pid];
}
}
if (empty($vecs)) {
$stats['skipped_no_embeddings']++;
continue;
}
$avg = $this->average_dense_vectors($vecs);
$blob = $this->pack_taste_vector($avg);
if ($blob === null) { continue; }
$rows_to_write[(int) $c['eponymous_id']] = $blob;
$stats['vectorised']++;
}
if (!empty($rows_to_write)) {
$now = current_time('mysql', true);
foreach ($rows_to_write as $epo => $blob) {
$wpdb->query($wpdb->prepare(
"UPDATE {$tbl_ci}
SET taste_vector = %s, taste_vector_updated_at = %s
WHERE eponymous_id = %d",
$blob, $now, $epo
));
}
}
return $stats;
}
private function decode_chat_embedding($raw) {
if (empty($raw)) { return null; }
$decoded = base64_decode($raw, true);
if ($decoded === false) { return null; }
$decompressed = @gzuncompress($decoded);
if ($decompressed === false || $decompressed === '') { return null; }
$unpacked = @unpack('f*', $decompressed);
if (!is_array($unpacked)) { return null; }
return array_values($unpacked);
}
private function average_dense_vectors(array $vecs) {
$count = count($vecs);
if ($count === 0) { return array(); }
if ($count === 1) { return $vecs[0]; }
$dim = count($vecs[0]);
$sum = array_fill(0, $dim, 0.0);
foreach ($vecs as $v) {
$vd = min($dim, count($v));
for ($i = 0; $i < $vd; $i++) {
$sum[$i] += (float) $v[$i];
}
}
for ($i = 0; $i < $dim; $i++) {
$sum[$i] /= $count;
}
return $sum;
}
private function build_knn_feature_vector(array $r) {
$segs = !empty($r['segments_json']) ? json_decode((string) $r['segments_json'], true) : array();
$segs = is_array($segs) ? $segs : array();
$segs_set = array_flip($segs);
$discount_share = (float) ($r['discount_share'] ?? 0);
$emails_sent = (int) ($r['emails_sent_lifetime'] ?? 0);
$open_rate = (float) ($r['open_rate_lifetime'] ?? 0);
$click_rate = (float) ($r['click_rate_lifetime'] ?? 0);
$days_since = (int) ($r['days_since_last_engagement'] ?? 65535);
$engagement_recency = 1.0 - min(1.0, $days_since / 365.0);
$sent_breadth = log10($emails_sent + 1) / 3.0;
$cohort_scale = array(
'' => 0.0,
'drifting' => 0.25,
'lapsed' => 0.50,
'long_dormant' => 0.75,
'lost' => 1.0,
);
$dormancy = $cohort_scale[(string) ($r['dormancy_cohort'] ?? '')] ?? 0.0;
return array(
(float) log10(((float) $r['ltv_total']) + 1),
(float) log10(((float) $r['aov']) + 1),
(float) log10(((int) $r['orders_count']) + 1),
min(3.0, ((int) $r['recency_days']) / 365.0),
(float) $r['top_brand_share'],
((int) $r['monetary_band']) / 5.0,
((int) $r['frequency_band']) / 5.0,
((int) $r['recency_band']) / 5.0,
isset($segs_set['vip']) ? 1.0 : 0.0,
isset($segs_set['loyalist']) ? 1.0 : 0.0,
isset($segs_set['newbie']) ? 1.0 : 0.0,
isset($segs_set['replenisher']) ? 1.0 : 0.0,
isset($segs_set['brand_devoted']) ? 1.0 : 0.0,
$dormancy,
isset($segs_set['overdue']) ? 1.0 : 0.0,
isset($segs_set['discount_hunter']) ? 1.0 : 0.0,
$discount_share * 2.0,
((int) ($r['price_band'] ?? 0)) / 5.0,
((float) ($r['sole_order_share'] ?? 0)) * 2.0,
((float) ($r['avg_jlw'] ?? 0)),
((float) ($r['avg_vlw'] ?? 0)),
$sent_breadth,
$open_rate,
$click_rate * 1.5,
$engagement_recency,
);
}
public function backfill_eponymous_via_order_id() {
global $wpdb;
$tables = $this->discover_order_snapshot_tables();
if (empty($tables)) { return false; }
$tbl_sr = $wpdb->prefix . 'aieo_survey_responses';
$tbl_ci = $wpdb->prefix . 'aieo_customer_intelligence';
$parts = array();
foreach ($tables as $t) {
$parts[] = "SELECT DISTINCT OrderId, EponymousID FROM `{$t}` WHERE OrderId > 0 AND EponymousID IS NOT NULL AND EponymousID > 0";
}
$union = implode(' UNION ALL ', $parts);
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_order_to_epo");
$wpdb->query(
"CREATE TEMPORARY TABLE _aieo_order_to_epo (
OrderId BIGINT UNSIGNED NOT NULL PRIMARY KEY,
EponymousID BIGINT UNSIGNED NOT NULL
) ENGINE=MEMORY"
);
$wpdb->query(
"INSERT IGNORE INTO _aieo_order_to_epo (OrderId, EponymousID)
SELECT OrderId, EponymousID FROM ({$union}) raw"
);
$wpdb->query(
"UPDATE {$tbl_sr} sr
JOIN _aieo_order_to_epo o ON o.OrderId = sr.order_id
SET sr.eponymous_id = o.EponymousID
WHERE sr.order_id > 0 AND sr.eponymous_id = 0"
);
$wpdb->query(
"UPDATE {$tbl_sr} sr
JOIN {$tbl_ci} ci ON ci.eponymous_id = sr.eponymous_id
SET sr.wp_user_id = ci.wp_user_id
WHERE sr.eponymous_id > 0 AND sr.wp_user_id = 0 AND ci.wp_user_id > 0"
);
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_order_to_epo");
return true;
}
public function compute_purchase_metrics() {
global $wpdb;
$tables = $this->discover_order_snapshot_tables();
if (empty($tables)) { return false; }
$tbl_si = $wpdb->prefix . 'aieo_survey_intelligence';
$parts = array();
foreach ($tables as $t) {
$parts[] = "SELECT EponymousID, OrderDate, OrderId, LineValue FROM `{$t}` WHERE EponymousID > 0";
}
$union = implode(' UNION ALL ', $parts);
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_surv_orders");
$wpdb->query(
"CREATE TEMPORARY TABLE _aieo_surv_orders (
EponymousID BIGINT UNSIGNED NOT NULL,
OrderDate DATETIME NOT NULL,
OrderTotal DECIMAL(12,2) NOT NULL,
KEY idx_epo_date (EponymousID, OrderDate)
) ENGINE=MEMORY"
);
$wpdb->query(
"INSERT INTO _aieo_surv_orders (EponymousID, OrderDate, OrderTotal)
SELECT EponymousID, MIN(OrderDate), SUM(IFNULL(LineValue,0))
FROM ({$union}) raw
GROUP BY EponymousID, OrderId"
);
$wpdb->query(
"UPDATE {$tbl_si} si
LEFT JOIN (
SELECT
ao.EponymousID,
SUM(CASE WHEN ao.OrderDate < si2.f18_submitted_at THEN 1 ELSE 0 END) AS ob,
SUM(CASE WHEN ao.OrderDate < si2.f18_submitted_at THEN ao.OrderTotal ELSE 0 END) AS sb,
SUM(CASE WHEN ao.OrderDate >= si2.f18_submitted_at THEN 1 ELSE 0 END) AS oa,
SUM(CASE WHEN ao.OrderDate >= si2.f18_submitted_at THEN ao.OrderTotal ELSE 0 END) AS sa
FROM _aieo_surv_orders ao
JOIN {$tbl_si} si2 ON si2.eponymous_id = ao.EponymousID
WHERE si2.f18_submitted_at IS NOT NULL
GROUP BY ao.EponymousID
) agg ON agg.EponymousID = si.eponymous_id
SET si.orders_before_survey = IFNULL(agg.ob, 0),
si.spend_before_survey = IFNULL(agg.sb, 0),
si.orders_after_survey = IFNULL(agg.oa, 0),
si.spend_after_survey = IFNULL(agg.sa, 0)"
);
$wpdb->query("DROP TEMPORARY TABLE IF EXISTS _aieo_surv_orders");
return true;
}
public function ajax_recompute() {
AIEO_DMM::verify_ajax_request();
if (!current_user_can('manage_options')) {
wp_send_json_error(array(
'message' => __('Recompute requires administrator privileges.', 'ai-eshop-optimizer'),
), 403);
}
$resp = $this->kick_off_async('recompute');
if ($resp === 'busy') {
wp_send_json_error(array('message' => __('Another job is already running — wait for it to finish.', 'ai-eshop-optimizer')));
}
wp_send_json_success(array(
'message' => __('Recompute queued — running in background…', 'ai-eshop-optimizer'),
'kind' => 'recompute',
'queued' => true,
));
}
public function ajax_ingest() {
AIEO_DMM::verify_ajax_request();
$resp = $this->kick_off_async('ingest');
if ($resp === 'busy') {
wp_send_json_error(array('message' => __('Another job is already running — wait for it to finish.', 'ai-eshop-optimizer')));
}
wp_send_json_success(array(
'message' => __('Ingest queued — running in background…', 'ai-eshop-optimizer'),
'kind' => 'ingest',
'queued' => true,
));
}
public function ajax_status() {
AIEO_DMM::verify_ajax_request();
$status = get_option(self::STATUS_OPTION, array(
'kind' => '', 'state' => 'idle', 'started' => 0, 'finished' => 0, 'message' => '',
));
wp_send_json_success($status);
}
public function ajax_bootstrap_ci() {
AIEO_DMM::verify_ajax_request();
$created = $this->ensure_customer_intelligence_table();
if ($created) {
wp_send_json_success(array('message' => __('Table created. Now open the Web Push tab and run "Recompute customer intelligence" to populate it.', 'ai-eshop-optimizer')));
}
wp_send_json_error(array('message' => __('Could not create wp_aieo_customer_intelligence — check debug.log.', 'ai-eshop-optimizer')));
}
public function ajax_verify_prices() {
AIEO_DMM::verify_ajax_request();
$result = $this->verify_price_historical_status();
if (!$result['ok']) {
wp_send_json_error(array('message' => $result['error'] ?? 'Unknown error.'));
}
wp_send_json_success(array('rows' => $result['rows']));
}
public function ajax_fix_prices() {
AIEO_DMM::verify_ajax_request();
@set_time_limit(0);
@ignore_user_abort(true);
$start = microtime(true);
try {
$result = $this->compute_discount_and_price_from_wc();
} catch (\Throwable $e) {
wp_send_json_error(array('message' => sprintf( __('Failed: %s', 'ai-eshop-optimizer'), $e->getMessage() )));
}
if ($result === false) {
wp_send_json_error(array('message' => __('No order snapshot tables found OR wp_woocommerce_order_items missing. Check Web Push CI is populated and WC tables exist.', 'ai-eshop-optimizer')));
}
$duration = round(microtime(true) - $start, 2);
wp_send_json_success(array(
'message' => sprintf(
__('Computed from WC line items in %4$ss — %1$d customers with discount data, %2$d with price_band assigned, avg discount_share = %3$s.', 'ai-eshop-optimizer'),
(int) $result['with_discount_data'],
(int) $result['with_price_band'],
number_format_i18n((float) $result['avg_discount_share'], 4),
$duration
),
'stats' => $result,
));
}
private function kick_off_async($kind) {
$this->maybe_install();
$current = get_option(self::STATUS_OPTION, array());
$cur_state = is_array($current) ? (string) ($current['state'] ?? 'idle') : 'idle';
$cur_started = is_array($current) ? (int) ($current['started'] ?? 0) : 0;
$stale_after = 20 * MINUTE_IN_SECONDS;
if (($cur_state === 'queued' || $cur_state === 'running') && (time() - $cur_started) < $stale_after) {
return 'busy';
}
update_option(self::STATUS_OPTION, array(
'kind' => $kind,
'state' => 'queued',
'started' => time(),
'finished' => 0,
'message' => '',
), false);
if (!wp_next_scheduled(self::CRON_ASYNC_HOOK, array($kind))) {
wp_schedule_single_event(time() - 1, self::CRON_ASYNC_HOOK, array($kind));
}
if (function_exists('spawn_cron')) {
spawn_cron();
}
return 'queued';
}
public function on_async_run($kind) {
global $wpdb;
@set_time_limit(0);
@ignore_user_abort(true);
$this->maybe_install();
$start = microtime(true);
$status = array(
'kind' => $kind,
'state' => 'running',
'started' => time(),
'finished' => 0,
'message' => '',
'stats' => array(),
);
update_option(self::STATUS_OPTION, $status, false);
$ci_lock_held = false;
try {
if ($kind === 'ingest') {
$tbl_sr = $wpdb->prefix . 'aieo_survey_responses';
$rows_before = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl_sr}");
$t_a = microtime(true);
if ($this->gf_entries_exist()) {
$this->call_survey_sproc('CALL aieo_sp_ingest_survey_responses()');
}
$dur_a = round(microtime(true) - $t_a, 2);
$t_b = microtime(true);
try {
$this->backfill_eponymous_via_order_id();
} catch (\Throwable $e) {
if (function_exists('aieo_debug_log')) {
aieo_debug_log('AIEO_Surveys backfill_eponymous_via_order_id failed: ' . $e->getMessage());
}
}
$dur_b = round(microtime(true) - $t_b, 2);
$rows_after = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl_sr}");
$unresolved = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl_sr} WHERE eponymous_id = 0");
$duration = round(microtime(true) - $start, 2);
$status['message'] = sprintf(
__('Ingested %1$d responses (+%2$d new, %3$d unresolved) in %4$ss — pivot %5$ss, backfill %6$ss.', 'ai-eshop-optimizer'),
$rows_after, max(0, $rows_after - $rows_before), $unresolved, $duration, $dur_a, $dur_b
);
$status['stats'] = compact('rows_before', 'rows_after', 'unresolved', 'duration', 'dur_a', 'dur_b');
} elseif ($kind === 'recompute') {
if (!self::acquire_ci_rebuild_lock(120)) {
throw new \RuntimeException(
__('Another customer-intelligence rebuild is already running (Web Push → Recompute now). Wait for it to finish and try again.', 'ai-eshop-optimizer')
);
}
$ci_lock_held = true;
$tbl_si = $wpdb->prefix . 'aieo_survey_intelligence';
$rows_before = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl_si}");
$this->call_survey_sproc('CALL aieo_sp_extract_survey_intelligence()');
$stages = array(
array('key' => 'purchase_metrics', 'label' => __('Order + spend before / after the survey', 'ai-eshop-optimizer'),
'fn' => function () { $this->compute_purchase_metrics(); }),
array('key' => 'discount_price', 'label' => __('Discount share + price band', 'ai-eshop-optimizer'),
'fn' => function () { $this->compute_discount_and_price_from_wc(); }),
array('key' => 'segments', 'label' => __('Segment assignment', 'ai-eshop-optimizer'),
'fn' => function () { aieo_call_sproc( 'CALL aieo_sp_assign_segments()', array( 'AIEO_DMM_Web_Push', 'install_sprocs' ) ); }),
array('key' => 'position_signals', 'label' => __('Position signals (sole / first / last)', 'ai-eshop-optimizer'),
'fn' => function () { $this->compute_position_signals_from_temp_orders(); }),
array('key' => 'core_needs', 'label' => __('Core needs (pa_need aggregation)', 'ai-eshop-optimizer'),
'fn' => function () { $this->compute_core_needs_from_top_products(); }),
array('key' => 'tnp_engagement', 'label' => __('TNP newsletter engagement', 'ai-eshop-optimizer'),
'fn' => function () { $this->compute_tnp_engagement_signals(); }),
array('key' => 'taste_vectors', 'label' => __('Voyage taste vectors (batch 500)', 'ai-eshop-optimizer'),
'fn' => function () { $this->compute_taste_vectors_batch(500); }),
array('key' => 'priority_inference', 'label' => __('Priority inference (k-NN, heuristic fallback)', 'ai-eshop-optimizer'),
'fn' => function () {
$r = $this->compute_inferred_priorities_knn();
if ($r === false) { $this->compute_inferred_priorities_heuristic(); }
}),
);
$total = count($stages);
$stage_stats = array();
foreach ($stages as $idx => $stage) {
$current_started = microtime(true);
$status['progress'] = array(
'current' => $idx + 1,
'total' => $total,
'key' => $stage['key'],
'label' => (string) $stage['label'],
'started' => time(),
);
unset($status['subprogress']);
update_option(self::STATUS_OPTION, $status, false);
try {
call_user_func($stage['fn']);
$stage_stats[$stage['key']] = round(microtime(true) - $current_started, 2);
} catch (\Throwable $e) {
$stage_stats[$stage['key']] = 'error: ' . $e->getMessage();
if (function_exists('aieo_debug_log')) {
aieo_debug_log('AIEO_Surveys recompute stage "' . $stage['key'] . '" failed: ' . $e->getMessage());
}
}
}
$rows_after = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl_si}");
$duration = round(microtime(true) - $start, 2);
$status['message'] = sprintf(
__('Recomputed: %1$d profiles (+%2$d new) in %3$ss.', 'ai-eshop-optimizer'),
$rows_after, max(0, $rows_after - $rows_before), $duration
);
$status['stats'] = compact('rows_before', 'rows_after', 'duration', 'stage_stats');
unset($status['progress']);
} else {
throw new \RuntimeException('Unknown async kind: ' . $kind);
}
$status['state'] = 'done';
} catch (\Throwable $e) {
$status['state'] = 'error';
$status['message'] = $e->getMessage();
if (function_exists('aieo_debug_log')) {
aieo_debug_log('AIEO_Surveys async ' . $kind . ' failed: ' . $e->getMessage());
}
} finally {
if ($ci_lock_held) {
self::release_ci_rebuild_lock();
}
}
$status['finished'] = time();
update_option(self::STATUS_OPTION, $status, false);
}
public function render_subtab() {
global $wpdb;
$tbl_si = $wpdb->prefix . 'aieo_survey_intelligence';
$tbl_sr = $wpdb->prefix . 'aieo_survey_responses';
$exists = (string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tbl_si)) === $tbl_si
&& (string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tbl_sr)) === $tbl_sr;
if (!$exists) {
$close = $this->card_open(array(
'anchor' => 'surveys-dependencies-customer-intelligence-base',
'title' => __('① Dependencies: the three tables', 'ai-eshop-optimizer'),
));
$this->note(esc_html__('Survey tables are being installed. Reload once to finish setup.', 'ai-eshop-optimizer'));
echo $close;
return;
}
$this->band_open(array(
'key' => 'initial-setup',
'id' => 'aieo-card-surveys-initial-setup-run-once-in-order',
'title' => __('Initial setup in four steps', 'ai-eshop-optimizer'),
'description' => __('Explains the one-off setup: steps ① to ④ must be run in order, after which the nightly cron refreshes everything and the buttons become manual top-ups.', 'ai-eshop-optimizer')
. ' ' . __('Steps ① → ② → ③ → ④ each depend on the previous one. After this completes once, the nightly cron at 02:30–03:00 Athens refreshes everything automatically — the buttons below are then just manual on-demand refreshes.', 'ai-eshop-optimizer'),
'cols' => 2,
));
$this->render_dependency_status_card();
$this->render_pipeline_card($tbl_sr, $tbl_si);
$this->render_recompute_card($tbl_si);
$this->render_inferred_priorities_card();
$this->band_close();
$this->band_open(array(
'key' => 'analytics-diagnostics',
'id' => 'aieo-card-surveys-analytics-diagnostics',
'title' => __('Analytics and diagnostics', 'ai-eshop-optimizer'),
'description' => __('Read-only views over the data the four setup steps produce. Refresh on page reload — no buttons to press here.', 'ai-eshop-optimizer'),
'cols' => 1,
));
$this->render_voc_distribution_card($tbl_si);
$this->render_nps_distribution_card($tbl_si);
$this->render_priority_distribution_card($tbl_si);
$this->render_priority_diagnostics_card();
$this->render_position_diagnostics_card();
$this->render_engagement_diagnostics_card();
$this->render_push_engagement_diagnostics_card();
$this->render_taste_vectors_card();
$this->render_loyalty_journey_card($tbl_si);
$this->render_repeat_respondents_card($tbl_sr);
$this->render_metric_distributions_card($tbl_si);
$this->render_actionable_lists_card($tbl_si);
$this->render_competition_signal_card();
$this->render_phase5_todo_card();
$this->band_close();
}
private function render_dependency_status_card() {
global $wpdb;
$tbl_ci = $wpdb->prefix . 'aieo_customer_intelligence';
$tbl_sr = $wpdb->prefix . 'aieo_survey_responses';
$tbl_si = $wpdb->prefix . 'aieo_survey_intelligence';
$exists = function ($t) use ($wpdb) {
return (string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $t)) === $t;
};
$ci_ok = $exists($tbl_ci);
$sr_ok = $exists($tbl_sr);
$si_ok = $exists($tbl_si);
$ci_rows = $ci_ok ? (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl_ci}") : 0;
$sr_rows = $sr_ok ? (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl_sr}") : 0;
$si_rows = $si_ok ? (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl_si}") : 0;
$all_ok = $ci_ok && $sr_ok && $si_ok && $ci_rows > 0;
$bg = $all_ok ? '#ecfdf5' : '#fef2f2';
$border = $all_ok ? '#15803d' : '#b32d2e';
unset($bg, $border);
$close = $this->card_open(array(
'anchor' => 'surveys-dependencies-customer-intelligence-base',
'icon' => 'database',
'title' => __('① Dependencies: the three tables', 'ai-eshop-optimizer'),
'description' => __('Shows whether the three tables this page depends on exist and how many rows each holds, and lets you create or populate the customer intelligence base.', 'ai-eshop-optimizer'),
'col' => 1,
));
echo '
';
$this->tile('wp_aieo_customer_intelligence', $ci_ok ? number_format_i18n($ci_rows) . ' rows' : '— missing —',
__('The master CDP table — one row per known customer (eponymous_id). Built nightly from order snapshots, sessions, wishlist, ratings + this survey data. Powers segmentation, push triggers, AOV/LTV.', 'ai-eshop-optimizer'));
$this->tile('wp_aieo_survey_responses', $sr_ok ? number_format_i18n($sr_rows) . ' rows' : '— missing —',
__('Mirror of every Gravity Forms F18/F20 entry, one row per submission. Created live on form submit and re-synced nightly at 03:00 Athens via the ingestion pipeline.', 'ai-eshop-optimizer'));
$this->tile('wp_aieo_survey_intelligence', $si_ok ? number_format_i18n($si_rows) . ' rows' : '— missing —',
__('Per-customer survey rollup, one row per eponymous_id who responded at least once. Joins F18 + F20 answers, NPS, satisfaction, priorities, plus orders_before/after_survey and spend_before/after_survey.', 'ai-eshop-optimizer'));
echo '
';
if (!$ci_ok) {
$this->note(esc_html__('customer_intelligence is the Web Push module table — Phase 1 of the Rooster One CDP. Without it, eponymous resolution, purchase correlation and inferred priorities all fail. The Surveys module tries to auto-create the schema on every page load, but the table also needs to be populated by the Web Push tab\'s "Recompute customer intelligence" job (which scans your wp_aieo_*_orders snapshots).', 'ai-eshop-optimizer'), 'destructive');
$this->job_block(array(
'id' => 'aieo-surveys-bootstrap-ci-job',
'run' => __('Force-create the table now', 'ai-eshop-optimizer'),
'primary' => false,
'run_attrs' => array('id' => 'aieo-surveys-bootstrap-ci'),
));
echo $this->bootstrap_ci_inline_js();
} elseif ($ci_rows === 0) {
$this->note(esc_html__('customer_intelligence table exists but has 0 rows. The Web Push module needs to scan your wp_aieo_*_orders snapshots to populate the ~33k per-customer rows. Click below to trigger that recompute directly from here, or navigate to the Web Push tab to do it there.', 'ai-eshop-optimizer'), 'warning');
$this->job_block(array(
'id' => 'aieo-surveys-populate-ci-job',
'run' => __('Populate Customer Intelligence (one-time — scans your order snapshots)', 'ai-eshop-optimizer'),
'run_attrs' => array('id' => 'aieo-surveys-populate-ci'),
));
echo $this->populate_ci_inline_js();
}
echo $close;
}
private function populate_ci_inline_js() {
$nonce = wp_create_nonce(AIEO_DMM::NONCE_ACTION);
$ajax = admin_url('admin-ajax.php');
ob_start();
?>
get_var("SELECT COUNT(*) FROM {$tbl_sr} WHERE form_id = 18");
$f20_total = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl_sr} WHERE form_id = 20");
$unresolved = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl_sr} WHERE eponymous_id = 0");
$linked = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl_sr} WHERE eponymous_id > 0");
$linked_unique = (int) $wpdb->get_var("SELECT COUNT(DISTINCT eponymous_id) FROM {$tbl_sr} WHERE eponymous_id > 0");
$last_ingest = (string) $wpdb->get_var("SELECT MAX(ingested_at) FROM {$tbl_sr}");
$next_cron_ts = wp_next_scheduled(self::CRON_INGEST_HOOK);
$close = $this->card_open(array(
'anchor' => 'surveys-ingestion-pipeline-mirror-gravity-forms',
'icon' => 'download',
'title' => __('② Ingestion from Gravity Forms', 'ai-eshop-optimizer'),
'description' => __('Copies the Gravity Forms survey entries into the survey tables and shows how many are matched to customers.', 'ai-eshop-optimizer'),
'col' => 2,
));
$this->guidance(__('Mirrors every Gravity Forms F18 and F20 entry into the survey tables, live on submit and nightly at 03:00, and counts the emails still unmatched.', 'ai-eshop-optimizer'), 'guidance', __('What is mirrored?', 'ai-eshop-optimizer'));
echo '
';
$this->tile(__('F18 mirrored', 'ai-eshop-optimizer'), number_format_i18n($f18_total),
__('Total Gravity Forms F18 (post-order) entries copied into wp_aieo_survey_responses. Should match GF\'s own F18 entry count once the next sync completes.', 'ai-eshop-optimizer'));
$this->tile(__('F20 mirrored', 'ai-eshop-optimizer'), number_format_i18n($f20_total),
__('Total Gravity Forms F20 (post-delivery) entries mirrored. F20 fires after a parcel is delivered and adds satisfaction + will-repurchase signal on top of F18.', 'ai-eshop-optimizer'));
$this->tile(__('Linked customers', 'ai-eshop-optimizer'), number_format_i18n($linked_unique),
__('Distinct eponymous_id values across all mirrored responses — i.e. how many unique customers have submitted at least one survey. Each customer can submit F18 and/or F20 multiple times.', 'ai-eshop-optimizer'));
$this->tile(__('Unresolved emails', 'ai-eshop-optimizer'), number_format_i18n($unresolved),
__('Mirrored responses whose email did NOT match any wp_aieo_customer_intelligence row at ingest time (eponymous_id = 0). Most are typos or guest checkouts; the next ingest cycle retries automatically.', 'ai-eshop-optimizer'));
$this->tile(__('Last ingest', 'ai-eshop-optimizer'),
$last_ingest ? esc_html(human_time_diff(strtotime($last_ingest . ' UTC'))) . ' ' . esc_html__('ago', 'ai-eshop-optimizer') : '—',
__('Most recent time the ingest pipeline wrote a row into wp_aieo_survey_responses. Live submits update this immediately; scheduled cron at 03:00 Athens covers any drift.', 'ai-eshop-optimizer'));
$this->tile(__('Next scheduled', 'ai-eshop-optimizer'),
$next_cron_ts ? esc_html(human_time_diff(time(), $next_cron_ts)) : '—',
__('Time until the next nightly ingest cron (aieo_surveys_ingest_tick) fires. Default schedule is daily 03:00 Athens.', 'ai-eshop-optimizer'));
echo '
';
$this->guidance(__('Responses are mirrored live on every form submit via gform_after_submission, and re-synced nightly at 03:00 Athens. Unresolved rows are entries whose email is not yet in customer_intelligence — they get retried automatically each ingest cycle.', 'ai-eshop-optimizer'), 'guidance', __('When does it run?', 'ai-eshop-optimizer'));
$this->job_block(array(
'id' => 'aieo-surveys-ingest-job',
'run' => __('Ingest now', 'ai-eshop-optimizer'),
'primary' => false,
'run_attrs' => array('id' => 'aieo-surveys-ingest'),
));
echo $this->ingest_inline_js();
echo $close;
}
private function render_recompute_card($tbl_si) {
global $wpdb;
$rows_total = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl_si}");
$with_f18 = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl_si} WHERE f18_entry_id > 0");
$with_f20 = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl_si} WHERE f20_entry_id > 0");
$last = (string) $wpdb->get_var("SELECT MAX(updated_at) FROM {$tbl_si}");
$close = $this->card_open(array(
'anchor' => 'surveys-customer-intelligence-build-merge-orders-sur',
'icon' => 'layers',
'title' => __('③ Build customer profiles', 'ai-eshop-optimizer'),
'description' => __('Builds one profile per customer from their orders, newsletter engagement, taste vectors and survey answers.', 'ai-eshop-optimizer'),
'col' => 1,
));
$this->guidance(__('The full per-customer CDP. Combines order signals (LTV, AOV, RFM, segments, position-based first/last/sole products), newsletter engagement (TNP open/click rates), Voyage taste vectors over signature products, and — when filled — Gravity Forms #18/#20 voice-of-customer data. Built from wp_aieo_temp_orders + the catalog flat table; voice-of-customer rows mirrored live on form submit and re-synced nightly at 03:00 Athens. Drives audience selection, push triggers, newsletter cohorts, and inferred priorities.', 'ai-eshop-optimizer'), 'guidance', __('What is a customer profile?', 'ai-eshop-optimizer'));
$this->guidance(__('Recompute runs the full CDP pipeline: purchase metrics → discount/price → segments → position signals → core needs → TNP engagement → taste vectors → priority inference. The "with F18 / F20" tiles count voice-of-customer overlay; the rest of the intelligence builds even with zero surveys.', 'ai-eshop-optimizer'), 'guidance', __('What does Recompute do?', 'ai-eshop-optimizer'));
echo '
';
$this->tile(__('Profiles with survey data', 'ai-eshop-optimizer'), number_format_i18n($rows_total),
__('Total rows in wp_aieo_survey_intelligence — one per customer who responded to at least one survey (F18 or F20). The denominator for "% F18 / F20 coverage".', 'ai-eshop-optimizer'));
$this->tile(__('… with F18 (post-order)', 'ai-eshop-optimizer'), number_format_i18n($with_f18),
__('Subset of those profiles that have at least one F18 entry (the post-order survey). F18 captures NPS + 7 priority dimensions and seeds priority inference.', 'ai-eshop-optimizer'));
$this->tile(__('… with F20 (post-delivery)', 'ai-eshop-optimizer'), number_format_i18n($with_f20),
__('Subset that have at least one F20 entry (post-delivery survey). F20 adds Overall satisfaction + Site UX + Will-repurchase, completing the VoC composite.', 'ai-eshop-optimizer'));
$this->tile(__('Last build', 'ai-eshop-optimizer'),
$last ? esc_html(human_time_diff(strtotime($last . ' UTC'))) . ' ' . esc_html__('ago', 'ai-eshop-optimizer') : '—',
__('Most recent time "Recompute now" ran or the nightly cron updated wp_aieo_survey_intelligence. Stale = older than ~24h, since cron runs daily 02:30 Athens.', 'ai-eshop-optimizer'));
echo '
';
echo '
';
if (current_user_can('manage_options')) {
$this->job_block(array(
'id' => 'aieo-surveys-recompute-job',
'run' => __('Recompute now', 'ai-eshop-optimizer'),
'run_attrs' => array('id' => 'aieo-surveys-recompute'),
));
} else {
$this->guidance(__('Recompute is administrator-only — it runs the full 8-stage CDP pipeline and takes several minutes. The nightly cron at 02:30 Athens refreshes the data automatically.', 'ai-eshop-optimizer'), 'essential');
}
echo '
';
echo $this->recompute_inline_js();
echo $close;
}
private function render_voc_distribution_card($tbl_si) {
global $wpdb;
$r = $wpdb->get_row("SELECT
COUNT(*) total,
ROUND(AVG(voc_score),1) avg_voc,
SUM(CASE WHEN voc_band = 'high' THEN 1 ELSE 0 END) high_band,
SUM(CASE WHEN voc_band = 'mid' THEN 1 ELSE 0 END) mid_band,
SUM(CASE WHEN voc_band = 'low' THEN 1 ELSE 0 END) low_band
FROM {$tbl_si} WHERE voc_score > 0", ARRAY_A);
if (!$r || (int) $r['total'] === 0) {
return;
}
$close = $this->card_open(array(
'anchor' => 'surveys-voc-composite-score',
'icon' => 'star',
'title' => __('VoC composite score', 'ai-eshop-optimizer'),
));
$this->guidance(__('VoC = 40% NPS + 30% Overall satisfaction + 20% Site-UX avg + 10% Will-repurchase (when F20 present). The triggers and recommendation engine read this directly: high-VoC promoters become referral targets; low-VoC customers route to human service before any automated win-back.', 'ai-eshop-optimizer'));
echo '
';
$this->tile(__('Avg VoC', 'ai-eshop-optimizer'), number_format_i18n((float) $r['avg_voc'], 1) . ' / 100',
__('Mean VoC composite across all responders. VoC = 40% NPS + 30% Overall-Satisfaction + 20% Site-UX + 10% Will-Repurchase. Above 75 is healthy; below 60 signals a service problem.', 'ai-eshop-optimizer'));
$this->tile(__('High-VoC (≥80)', 'ai-eshop-optimizer'), number_format_i18n((int) $r['high_band']),
__('Customers with VoC ≥ 80 — the "advocates" cohort. Use these as the seed audience for referral programs, lookalike modelling, and review requests.', 'ai-eshop-optimizer'));
$this->tile(__('Mid-VoC (50-79)', 'ai-eshop-optimizer'), number_format_i18n((int) $r['mid_band']),
__('Customers with VoC 50–79 — neutral / improvable. Standard nurture audience: regular newsletter, mainstream offers.', 'ai-eshop-optimizer'));
$this->tile(__('Low-VoC (<50)', 'ai-eshop-optimizer'), number_format_i18n((int) $r['low_band']),
__('Customers with VoC < 50 — service-recovery cohort. Suppress these from automated win-back triggers and route to a human CS agent first.', 'ai-eshop-optimizer'));
echo '
';
echo $close;
}
private function render_nps_distribution_card($tbl_si) {
global $wpdb;
$r = $wpdb->get_row("SELECT
SUM(CASE WHEN nps_class = 'promoter' THEN 1 ELSE 0 END) promoters,
SUM(CASE WHEN nps_class = 'passive' THEN 1 ELSE 0 END) passives,
SUM(CASE WHEN nps_class = 'detractor' THEN 1 ELSE 0 END) detractors,
COUNT(*) total
FROM {$tbl_si} WHERE nps_class <> ''", ARRAY_A);
if (!$r || (int) $r['total'] === 0) { return; }
$promoters = (int) $r['promoters'];
$passives = (int) $r['passives'];
$detractors = (int) $r['detractors'];
$total = (int) $r['total'];
$nps = $total > 0 ? round(100 * ($promoters - $detractors) / $total, 1) : 0;
$close = $this->card_open(array(
'anchor' => 'surveys-nps-net-promoter-score',
'icon' => 'gauge',
'title' => __('NPS — Net Promoter Score', 'ai-eshop-optimizer'),
));
echo '
';
$this->tile(__('NPS', 'ai-eshop-optimizer'), ($nps >= 0 ? '+' : '') . number_format_i18n($nps, 1),
__('Net Promoter Score = (% Promoters − % Detractors), range -100 to +100. Above +50 is excellent; +30–50 healthy; below 0 means more haters than fans.', 'ai-eshop-optimizer'));
$this->tile(__('Promoters', 'ai-eshop-optimizer'), number_format_i18n($promoters) . ' (' . round(100 * $promoters / max(1,$total), 1) . '%)',
__('Responders who scored 9 or 10 on "How likely are you to recommend us?" — your most enthusiastic customers, ideal seed for lookalike audiences and referral programs.', 'ai-eshop-optimizer'));
$this->tile(__('Passives', 'ai-eshop-optimizer'), number_format_i18n($passives) . ' (' . round(100 * $passives / max(1,$total), 1) . '%)',
__('Responders who scored 7 or 8 — satisfied but not enthusiastic. Don\'t count toward NPS but are at-risk if a competitor offers something better.', 'ai-eshop-optimizer'));
$this->tile(__('Detractors', 'ai-eshop-optimizer'), number_format_i18n($detractors) . ' (' . round(100 * $detractors / max(1,$total), 1) . '%)',
__('Responders who scored 0–6. Suppressed from automated win-back / referral triggers and routed to human CS — automated nudging detractors makes them angrier.', 'ai-eshop-optimizer'));
echo '
';
$this->guidance(__('Detractors should be excluded from automated win-back triggers and routed to human service. Promoters are the natural seed for lookalike audiences and referral programs.', 'ai-eshop-optimizer'));
echo $close;
}
private function render_metric_distributions_card($tbl_si) {
global $wpdb;
$close = $this->card_open(array(
'anchor' => 'surveys-seven-high-signal-metrics',
'title' => __('Seven high-signal metrics', 'ai-eshop-optimizer'),
'description' => __('How respondents answered the seven survey questions that feed the customer segments and trigger filters.', 'ai-eshop-optimizer'),
));
$this->guidance(__('Each metric extracted from the survey responses. These flow directly into segment definitions and trigger filters.', 'ai-eshop-optimizer'));
echo '
';
$rows = $wpdb->get_results("SELECT overall_satisfaction v, COUNT(*) c FROM {$tbl_si} WHERE overall_satisfaction > 0 GROUP BY overall_satisfaction ORDER BY overall_satisfaction", ARRAY_A);
$this->mini_bar_chart($rows, '#15803d');
echo '
';
echo '
';
echo '
' . esc_html__('Professional / student status (verified)', 'ai-eshop-optimizer') . '
';
$rows = $wpdb->get_results("SELECT
CASE WHEN is_professional_verified=1 THEN 'Yes' ELSE 'No' END v,
COUNT(*) c FROM {$tbl_si} WHERE f18_entry_id > 0 GROUP BY is_professional_verified ORDER BY is_professional_verified DESC", ARRAY_A);
$this->mini_bar_chart($rows, '#7c3aed');
echo '
';
$rows = $wpdb->get_results("SELECT discovery_channel v, COUNT(*) c FROM {$tbl_si} WHERE discovery_channel <> '' GROUP BY discovery_channel ORDER BY c DESC", ARRAY_A);
$this->mini_bar_chart($rows, '#0891b2');
echo '
';
$rows = $wpdb->get_results("SELECT v, c FROM (
SELECT 'Seminar attendee' v, SUM(engagement_seminar) c FROM {$tbl_si} WHERE f18_entry_id > 0
UNION ALL
SELECT 'Blog reader', SUM(engagement_blog) FROM {$tbl_si} WHERE f18_entry_id > 0
UNION ALL
SELECT 'FastFinder user', SUM(engagement_fastfinder) FROM {$tbl_si} WHERE f18_entry_id > 0
UNION ALL
SELECT 'Social login', SUM(social_login) FROM {$tbl_si} WHERE f18_entry_id > 0
) x ORDER BY c DESC", ARRAY_A);
$this->mini_bar_chart($rows, '#ea580c');
echo '
';
$rows = $wpdb->get_results("SELECT
CASE WHEN shipping_price_concern=1 THEN 'Concerned' ELSE 'Not flagged' END v,
COUNT(*) c FROM {$tbl_si} WHERE f18_entry_id > 0 GROUP BY shipping_price_concern ORDER BY shipping_price_concern DESC", ARRAY_A);
$this->mini_bar_chart($rows, '#dc2626');
echo '
';
$rows = $wpdb->get_results("SELECT FLOOR(site_ux_avg) v, COUNT(*) c FROM {$tbl_si} WHERE site_ux_avg > 0 GROUP BY FLOOR(site_ux_avg) ORDER BY FLOOR(site_ux_avg)", ARRAY_A);
$this->mini_bar_chart($rows, '#0d9488');
echo '
';
echo '
';
echo $close;
}
private function render_actionable_lists_card($tbl_si) {
global $wpdb;
$tbl_ci = $wpdb->prefix . 'aieo_customer_intelligence';
$ci_exists = (string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tbl_ci)) === $tbl_ci;
if (!$ci_exists) { return; }
$close = $this->card_open(array(
'anchor' => 'surveys-actionable-lists',
'title' => __('Actionable lists', 'ai-eshop-optimizer'),
'description' => __('Customers your team should contact in person: unhappy big spenders, happy promoters and those who asked for a follow-up.', 'ai-eshop-optimizer'),
));
$this->guidance(__('The three lists worth a personal touch from your team — high-LTV detractors (rescue), high-VoC promoters (referrals), and customers who explicitly opted into the post-delivery follow-up.', 'ai-eshop-optimizer'));
$this->section(__('Detractors with high LTV (rescue priority)', 'ai-eshop-optimizer'));
$rows = $wpdb->get_results(
"SELECT si.email, ci.ltv_total, si.nps_score, si.overall_satisfaction, si.voc_score, si.open_feedback_f18
FROM {$tbl_si} si
JOIN {$tbl_ci} ci ON ci.eponymous_id = si.eponymous_id
WHERE si.nps_class = 'detractor' AND ci.ltv_total > 100
ORDER BY ci.ltv_total DESC LIMIT 10",
ARRAY_A
);
$this->actionable_table($rows, true);
$this->section(__('Top promoters (referral candidates)', 'ai-eshop-optimizer'));
$rows = $wpdb->get_results(
"SELECT si.email, ci.ltv_total, si.nps_score, si.overall_satisfaction, si.voc_score, si.open_feedback_f18
FROM {$tbl_si} si
JOIN {$tbl_ci} ci ON ci.eponymous_id = si.eponymous_id
WHERE si.nps_class = 'promoter' AND si.voc_score >= 90
ORDER BY ci.ltv_total DESC LIMIT 10",
ARRAY_A
);
$this->actionable_table($rows, false);
$f20_willing = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl_si} WHERE will_take_followup_survey = 1");
if ($f20_willing > 0) {
$this->section(__('F20 follow-up willing', 'ai-eshop-optimizer'));
echo '
'
. sprintf(
esc_html__('%d customers explicitly opted into the post-delivery survey on Form 18. When the F20 trigger lands (Phase 5+), this is the seed audience to target — they\'ve given consent.', 'ai-eshop-optimizer'),
$f20_willing
)
. '
'
. sprintf(
esc_html__('Aggregated across %1$d engaged responders (excluded %2$d / %3$s%% who left the GF default rank order untouched — that is disengagement, not a ranking signal). The importance bar shows average rank position (longer = more important on average); the rank-#1 share bar shows how often the priority lands at rank #1 specifically. A priority that is consistently in everyone\'s top 3 dominates the importance bar even when it rarely wins #1 against a polarising favourite.', 'ai-eshop-optimizer'),
$count_engaged,
$count_default,
$default_pct
)
. '
';
if ($count_engaged === 0) {
$this->note(esc_html__('Every responder left the default order — no ranking signal to aggregate.', 'ai-eshop-optimizer'), 'warning');
echo $close;
return;
}
$ranked = array();
$i = 0;
foreach ($aggregate as $r) {
$i++;
$ranked[] = array(
'n' => $i,
'label' => $r['label'],
'avg' => $r['avg'],
'importance_w' => ($worst_avg > 1)
? round(100 * (1 - ($r['avg'] - 1) / max(0.0001, $worst_avg - 1)), 1)
: 100,
'rank1' => $r['rank1'],
'rank1_note' => '(' . $r['rank1_pct'] . '%)',
'rank1_w' => $best_rank1 > 0 ? round(100 * $r['rank1'] / $best_rank1, 1) : 0,
);
}
AIEO_Table::render(array(
'page_id' => $this->get_id(),
'row_key' => 'label',
'rows' => $ranked,
'empty' => __('No ranking signal to aggregate.', 'ai-eshop-optimizer'),
'columns' => array(
array('key' => 'n', 'label' => '#', 'type' => 'integer', 'width' => '30px', 'tone' => 'muted'),
array('key' => 'label', 'label' => __('Priority', 'ai-eshop-optimizer'), 'type' => 'text'),
array('key' => 'avg', 'label' => __('Avg rank', 'ai-eshop-optimizer'), 'type' => 'decimal', 'width' => '90px', 'format' => array('precision' => 2)),
array(
'key' => 'importance_w',
'label' => __('Importance bar (lower avg = more important)', 'ai-eshop-optimizer'),
'type' => 'bar',
'width' => '38%',
'format' => array('max' => 100, 'label' => false),
'sort_key' => 'avg',
),
array(
'key' => 'rank1',
'label' => __('Times #1', 'ai-eshop-optimizer'),
'type' => 'integer',
'width' => '90px',
'suffix' => 'rank1_note',
),
array(
'key' => 'rank1_w',
'label' => __('Rank-#1 share', 'ai-eshop-optimizer'),
'type' => 'bar',
'width' => '25%',
'format' => array('max' => 100, 'label' => false),
'sort_key' => 'rank1',
),
),
));
echo $close;
}
private function render_inferred_priorities_card() {
global $wpdb;
$tbl_ci = $wpdb->prefix . 'aieo_customer_intelligence';
$tbl_si = $wpdb->prefix . 'aieo_survey_intelligence';
$ci_exists = (string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tbl_ci)) === $tbl_ci;
if (!$ci_exists) {
$close = $this->card_open(array(
'anchor' => 'surveys-inferred-priorities-tier-1-heuristic-2',
'icon' => 'list-checks',
'title' => __('④ Estimated customer priorities', 'ai-eshop-optimizer'),
'description' => __('Tier 1 rule that infers each customer\'s top priority from their segment and AOV, with the distribution, average confidence and accuracy against F18 responders.', 'ai-eshop-optimizer'),
'col' => 2,
));
$this->state('not-set-up', array('feature' => __('Customer intelligence', 'ai-eshop-optimizer'), 'text' => __('Cannot run: wp_aieo_customer_intelligence table does not exist. That table is owned by the Web Push module (Phase 1) and is the foundation for every CDP feature in here. Open the Web Push tab, run its recompute once, then come back here and re-press Recompute on this tab.', 'ai-eshop-optimizer')));
echo $close;
return;
}
$dist = $wpdb->get_results(
"SELECT inferred_top_priority slug,
COUNT(*) c,
ROUND(AVG(inferred_priorities_confidence), 2) avg_conf
FROM {$tbl_ci}
WHERE inferred_top_priority <> ''
GROUP BY inferred_top_priority
ORDER BY c DESC",
ARRAY_A
);
$taxonomy = self::priority_taxonomy();
$total_inferred = 0;
foreach ($dist as $r) { $total_inferred += (int) $r['c']; }
$close = $this->card_open(array(
'anchor' => 'surveys-inferred-priorities-tier-1-heuristic-2',
'icon' => 'list-checks',
'title' => __('④ Estimated customer priorities', 'ai-eshop-optimizer'),
'description' => __('Tier 1 rule that infers each customer\'s top priority from their segment and AOV, with the distribution, average confidence and accuracy against F18 responders.', 'ai-eshop-optimizer'),
'col' => 2,
));
$this->guidance(__('Rule-based extrapolation from F18 responders\' priorities to the full customer base via existing CI scalars (discount_share, price_band, top_brand_share, segments). Single SQL UPDATE — runs in seconds for ~33k rows. Top priority drives newsletter content branching + personalized coupon offer type. Confidence (0..1) = top-score / sum-of-scores; low values mean the heuristic had no clear winner and the customer fell to "general".', 'ai-eshop-optimizer'));
if ($total_inferred === 0) {
$this->state('not-run', array('title' => __('No inferred priorities yet — press "Recompute now" to run the heuristic across customer_intelligence.', 'ai-eshop-optimizer')));
echo $close;
return;
}
$this->section(__('Distribution across full base', 'ai-eshop-optimizer'));
$max_c = $dist ? max(array_column($dist, 'c')) : 1;
$buckets = array();
foreach ($dist as $r) {
$slug = (string) $r['slug'];
$buckets[] = array(
'slug' => $slug,
'label' => isset($taxonomy[$slug]) ? $taxonomy[$slug] : $slug,
'customers' => (int) $r['c'],
'bar' => (int) $r['c'],
'max_c' => max(1, (int) $max_c),
'share' => round(100 * (int) $r['c'] / max(1, $total_inferred), 1),
'avg_conf' => (float) $r['avg_conf'],
);
}
AIEO_Table::render(array(
'page_id' => $this->get_id(),
'row_key' => 'slug',
'density' => 'compact',
'rows' => $buckets,
'empty' => __('No inferred priorities yet.', 'ai-eshop-optimizer'),
'columns' => array(
array('key' => 'slug', 'label' => __('Slug', 'ai-eshop-optimizer'), 'type' => 'code'),
array('key' => 'label', 'label' => __('Priority label', 'ai-eshop-optimizer'), 'type' => 'text'),
array('key' => 'customers', 'label' => __('Customers', 'ai-eshop-optimizer'), 'type' => 'integer'),
array(
'key' => 'bar',
'label' => '',
'type' => 'bar',
'width' => '35%',
'format' => array('max' => 'max_c', 'label' => false),
'sortable' => false,
),
array('key' => 'share', 'label' => __('Share', 'ai-eshop-optimizer'), 'type' => 'percent', 'format' => array('precision' => 1)),
array('key' => 'avg_conf', 'label' => __('Avg confidence', 'ai-eshop-optimizer'), 'type' => 'decimal', 'format' => array('precision' => 2)),
),
));
$current_source = (string) $wpdb->get_var(
"SELECT inferred_priorities_source FROM {$tbl_ci}
WHERE inferred_priorities_source <> '' LIMIT 1"
);
if (strpos($current_source, 'knn') === 0) {
$loo = $this->validate_knn_leave_one_out();
if (($loo['total'] ?? 0) > 0) {
$this->section(__('Tier 2 accuracy — leave-one-out on F18 responders', 'ai-eshop-optimizer'));
echo '
'
. sprintf(
esc_html__('For each of %d engaged F18 responders, we predict their priority using the OTHER labelled responders (excluding themselves) and compare against their explicit answer. This is the honest measurement of the k-NN propagation quality on a held-out set — no self-validation cheating.', 'ai-eshop-optimizer'),
(int) $loo['total']
)
. '
';
echo '
';
$this->tile(__('Considered', 'ai-eshop-optimizer'), number_format_i18n((int) $loo['total']),
__('Number of F18 responders included in the leave-one-out evaluation. We predict each one using the OTHER labelled responders, then compare to their explicit answer.', 'ai-eshop-optimizer'));
$this->tile(__('k-NN top = their top', 'ai-eshop-optimizer'),
number_format_i18n((int) $loo['top1']) . ' (' . $loo['top1_pct'] . '%)',
__('How often the k-NN-predicted top priority exactly matched what the responder explicitly chose as #1. Strict accuracy. Random-baseline = 11% (1 of 9 priorities).', 'ai-eshop-optimizer'));
$this->tile(__('k-NN top in their top-3', 'ai-eshop-optimizer'),
number_format_i18n((int) $loo['top3']) . ' (' . $loo['top3_pct'] . '%)',
__('How often the k-NN-predicted top priority appears in the responder\'s own top-3. Soft-match. Random-baseline ≈ 33%; >50% beats chance; >70% is newsletter-worthy quality.', 'ai-eshop-optimizer'));
echo '
';
$this->guidance(__('Top-3 hit rate against random 9-choice baseline (33%): >50% = better than chance; >70% = newsletter-worthy. If well below 50%, the feature vector is missing the signal that drives priority — usually engagement / needs profile.', 'ai-eshop-optimizer'));
}
} else {
$f18_rows = $wpdb->get_results(
"SELECT si.eponymous_id, si.personal_priorities, ci.inferred_top_priority
FROM {$tbl_si} si
JOIN {$tbl_ci} ci ON ci.eponymous_id = si.eponymous_id
WHERE si.f18_entry_id > 0
AND si.personal_priorities IS NOT NULL AND si.personal_priorities <> ''
AND ci.inferred_top_priority <> ''",
ARRAY_A
);
if (!empty($f18_rows)) {
$grank_to_label = $this->get_f18_priority_map();
$default_csv = implode(',', array_keys($grank_to_label));
$label_to_slug = $this->build_label_to_slug_map($grank_to_label);
$matched_top1 = 0; $matched_top3 = 0; $considered = 0;
foreach ($f18_rows as $row) {
$raw = (string) $row['personal_priorities'];
$cleaned = preg_replace('/\s+/', '', $raw);
if ($cleaned === $default_csv) { continue; }
$considered++;
$slugs_in_order = array();
foreach (explode(',', $raw) as $g) {
$g = trim($g);
if (!isset($grank_to_label[$g])) { continue; }
$label = $grank_to_label[$g];
if (isset($label_to_slug[$label])) { $slugs_in_order[] = $label_to_slug[$label]; }
}
if (empty($slugs_in_order)) { continue; }
$top3_slugs = array_slice($slugs_in_order, 0, 3);
$inferred = (string) $row['inferred_top_priority'];
if ($inferred === $slugs_in_order[0]) { $matched_top1++; }
if (in_array($inferred, $top3_slugs, true)) { $matched_top3++; }
}
$top1_pct = $considered > 0 ? round(100 * $matched_top1 / $considered, 1) : 0;
$top3_pct = $considered > 0 ? round(100 * $matched_top3 / $considered, 1) : 0;
$this->section(__('Heuristic accuracy on F18 hold-out', 'ai-eshop-optimizer'));
echo '
';
$this->tile(__('Considered', 'ai-eshop-optimizer'), number_format_i18n($considered),
__('Number of F18 responders included in heuristic self-validation (those whose priority CSV is non-default and decodable into our 9 priority slugs).', 'ai-eshop-optimizer'));
$this->tile(__('Heuristic top = their top', 'ai-eshop-optimizer'), number_format_i18n($matched_top1) . ' (' . $top1_pct . '%)',
__('How often the rule-based heuristic predicted the same #1 priority the responder explicitly chose. Strict accuracy on the heuristic Tier-1 inference path.', 'ai-eshop-optimizer'));
$this->tile(__('Heuristic top in their top-3', 'ai-eshop-optimizer'), number_format_i18n($matched_top3) . ' (' . $top3_pct . '%)',
__('How often the heuristic prediction appears anywhere in the responder\'s top-3. Soft-match — used to gate when k-NN should override the heuristic.', 'ai-eshop-optimizer'));
echo '
';
}
}
echo $close;
}
private function render_position_diagnostics_card() {
global $wpdb;
$tbl_ci = $wpdb->prefix . 'aieo_customer_intelligence';
$ci_exists = (string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tbl_ci)) === $tbl_ci;
if (!$ci_exists) { return; }
$total = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl_ci}");
if ($total === 0) { return; }
$row = $wpdb->get_row("SELECT
-- sole_order_share buckets
SUM(IF(sole_order_share = 0, 1, 0)) AS sos_0,
SUM(IF(sole_order_share > 0 AND sole_order_share < 0.25, 1, 0)) AS sos_low,
SUM(IF(sole_order_share >= 0.25 AND sole_order_share < 0.50, 1, 0)) AS sos_mid,
SUM(IF(sole_order_share >= 0.50 AND sole_order_share < 0.75, 1, 0)) AS sos_high,
SUM(IF(sole_order_share >= 0.75, 1, 0)) AS sos_max,
-- avg_jlw buckets
SUM(IF(avg_jlw > 0 AND avg_jlw < 0.30, 1, 0)) AS jlw_early,
SUM(IF(avg_jlw >= 0.30 AND avg_jlw < 0.50, 1, 0)) AS jlw_lower,
SUM(IF(avg_jlw >= 0.50 AND avg_jlw < 0.70, 1, 0)) AS jlw_upper,
SUM(IF(avg_jlw >= 0.70, 1, 0)) AS jlw_late,
SUM(IF(avg_jlw = 0, 1, 0)) AS jlw_unset,
-- avg_vlw buckets
SUM(IF(avg_vlw > 0 AND avg_vlw < 0.30, 1, 0)) AS vlw_addon,
SUM(IF(avg_vlw >= 0.30 AND avg_vlw < 0.60, 1, 0)) AS vlw_mid,
SUM(IF(avg_vlw >= 0.60 AND avg_vlw < 0.90, 1, 0)) AS vlw_dominant,
SUM(IF(avg_vlw >= 0.90, 1, 0)) AS vlw_solo,
SUM(IF(avg_vlw = 0, 1, 0)) AS vlw_unset,
-- core_needs coverage
SUM(IF(core_needs_json IS NOT NULL AND core_needs_json <> '' AND core_needs_json <> '{}', 1, 0)) AS needs_set
FROM {$tbl_ci}", ARRAY_A);
$close = $this->card_open(array(
'anchor' => 'surveys-position-signal-diagnostics-first-last-sole',
'title' => __('Position-signal diagnostics', 'ai-eshop-optimizer'),
));
$this->guidance(__('The Rooster One core hypothesis: a customer\'s first / last / sole products encode their real expressed needs better than any aggregate scalar. Sole-order customers (single-item orders) know exactly what they want — they paid shipping just for THAT product. First-in-cart products are the magnet. Last-in-cart are the must-not-forgets. Below: how the population distributes on these axes.', 'ai-eshop-optimizer'));
echo '
';
echo '' . esc_html__('core_needs_json coverage:', 'ai-eshop-optimizer') . ' ';
echo number_format_i18n($needs_set) . ' / ' . number_format_i18n($total) . ' customers (' . $needs_pct . '%) ';
if ($needs_pct < 50) {
echo '' . esc_html__('— low coverage means many customers\' top products lack pa_need taxonomy assignments. Tagging more products with pa_need terms would meaningfully boost the k-NN cosine signal.', 'ai-eshop-optimizer') . '';
} elseif ($needs_pct < 80) {
echo '' . esc_html__('— moderate coverage. The k-NN needs-cosine term contributes for these but falls back to scalar matching for the rest.', 'ai-eshop-optimizer') . '';
} else {
echo '' . esc_html__('— strong coverage. Needs-cosine drives priority similarity for most customers.', 'ai-eshop-optimizer') . '';
}
echo '
';
$this->section(__('Catalog role archetypes — most common top product across customers', 'ai-eshop-optimizer'));
echo '
'
. esc_html__('For each role (sole, first, last) — which products show up most often as a customer\'s top product in that position. These are the catalog\'s natural role-occupants: products that Rooster One\'s order-line tracking says are repeatedly chosen as magnets / completers / pure-intent destinations.', 'ai-eshop-optimizer')
. '
';
echo '
';
foreach (array(
array('column' => 'top_sole_product_id', 'label' => __('Top sole-order destinations', 'ai-eshop-optimizer'), 'color' => '#1d4ed8'),
array('column' => 'top_first_product_id', 'label' => __('Top magnet products (first in cart)', 'ai-eshop-optimizer'), 'color' => '#7c3aed'),
array('column' => 'top_last_product_id', 'label' => __('Top completion products (last in cart)', 'ai-eshop-optimizer'), 'color' => '#0d9488'),
) as $role) {
$top = $wpdb->get_results(
"SELECT ci.{$role['column']} AS product_id, COUNT(*) AS hits, p.post_title AS name
FROM {$tbl_ci} ci
LEFT JOIN {$wpdb->posts} p ON p.ID = ci.{$role['column']}
WHERE ci.{$role['column']} > 0
GROUP BY ci.{$role['column']}
ORDER BY hits DESC
LIMIT 10",
ARRAY_A
);
echo '
';
echo $close;
}
private function render_engagement_diagnostics_card() {
global $wpdb;
$tbl_ci = $wpdb->prefix . 'aieo_customer_intelligence';
if ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tbl_ci)) !== $tbl_ci) { return; }
$total = (int) $wpdb->get_var("SELECT COUNT(DISTINCT IF(email <> '', email, NULL)) FROM {$tbl_ci}");
if ($total === 0) { return; }
$row = $wpdb->get_row("SELECT
COUNT(DISTINCT IF(email <> '' AND tnp_subscriber_id > 0, email, NULL)) AS matched,
COUNT(DISTINCT IF(email <> '' AND tnp_status = 'C', email, NULL)) AS confirmed,
COUNT(DISTINCT IF(email <> '' AND tnp_status = 'U', email, NULL)) AS unsubscribed,
COUNT(DISTINCT IF(email <> '' AND emails_sent_lifetime >= 3, email, NULL)) AS sent_3plus,
COUNT(DISTINCT IF(email <> '' AND emails_opened_lifetime > 0, email, NULL)) AS ever_opened,
COUNT(DISTINCT IF(email <> '' AND emails_clicked_lifetime > 0, email, NULL)) AS ever_clicked,
COUNT(DISTINCT IF(email <> '' AND emails_opened_30d > 0, email, NULL)) AS opened_30d,
-- Open-rate buckets (lifetime, restricted to 3+ sent so denominator meaningful)
COUNT(DISTINCT IF(email <> '' AND emails_sent_lifetime >= 3 AND open_rate_lifetime = 0, email, NULL)) AS or_zero,
COUNT(DISTINCT IF(email <> '' AND emails_sent_lifetime >= 3 AND open_rate_lifetime > 0 AND open_rate_lifetime < 0.10, email, NULL)) AS or_low,
COUNT(DISTINCT IF(email <> '' AND emails_sent_lifetime >= 3 AND open_rate_lifetime >= 0.10 AND open_rate_lifetime < 0.30, email, NULL)) AS or_mid,
COUNT(DISTINCT IF(email <> '' AND emails_sent_lifetime >= 3 AND open_rate_lifetime >= 0.30 AND open_rate_lifetime < 0.50, email, NULL)) AS or_high,
COUNT(DISTINCT IF(email <> '' AND emails_sent_lifetime >= 3 AND open_rate_lifetime >= 0.50, email, NULL)) AS or_max,
-- Click rate buckets
COUNT(DISTINCT IF(email <> '' AND emails_sent_lifetime >= 3 AND click_rate_lifetime = 0, email, NULL)) AS cr_zero,
COUNT(DISTINCT IF(email <> '' AND emails_sent_lifetime >= 3 AND click_rate_lifetime > 0 AND click_rate_lifetime < 0.05, email, NULL)) AS cr_low,
COUNT(DISTINCT IF(email <> '' AND emails_sent_lifetime >= 3 AND click_rate_lifetime >= 0.05 AND click_rate_lifetime < 0.15, email, NULL)) AS cr_mid,
COUNT(DISTINCT IF(email <> '' AND emails_sent_lifetime >= 3 AND click_rate_lifetime >= 0.15, email, NULL)) AS cr_high,
-- Engagement recency buckets
COUNT(DISTINCT IF(email <> '' AND days_since_last_engagement <= 30 AND days_since_last_engagement < 65535, email, NULL)) AS eng_30d,
COUNT(DISTINCT IF(email <> '' AND days_since_last_engagement > 30 AND days_since_last_engagement <= 90, email, NULL)) AS eng_90d,
COUNT(DISTINCT IF(email <> '' AND days_since_last_engagement > 90 AND days_since_last_engagement <= 365, email, NULL)) AS eng_year,
COUNT(DISTINCT IF(email <> '' AND days_since_last_engagement > 365 AND days_since_last_engagement < 65535, email, NULL)) AS eng_old,
COUNT(DISTINCT IF(email <> '' AND days_since_last_engagement = 65535, email, NULL)) AS eng_never
FROM {$tbl_ci}", ARRAY_A);
$matched = (int) $row['matched'];
$match_pct = round(100 * $matched / max(1, $total), 1);
$close = $this->card_open(array(
'anchor' => 'surveys-newsletter-engagement-tnp-match-reachability',
'title' => __('Newsletter engagement', 'ai-eshop-optimizer'),
));
$this->guidance(__('Bridges customer email → wp_newsletter subscriber, then aggregates send / open / click history. Engagement signals feed the k-NN priority extrapolation (4 added dims) and inform letter-vs-coupon strategy: high open + low click = letter audience; high click = coupon audience.', 'ai-eshop-optimizer'));
$confirmed_n = max(1, (int) $row['confirmed']);
$opened_pct = round(100 * ((int) $row['ever_opened']) / $confirmed_n, 1);
$clicked_pct = round(100 * ((int) $row['ever_clicked']) / $confirmed_n, 1);
$eng30_pct = round(100 * ((int) $row['opened_30d']) / $confirmed_n, 1);
echo '
';
$tiles = array(
array(
'label' => __('Matched to TNP', 'ai-eshop-optimizer'),
'value' => number_format_i18n($matched),
'sub' => $match_pct . '% of CI',
'help' => __('Customer profiles whose email address also appears as a subscriber in TNP\'s wp_newsletter table. Match is case-insensitive. The "% of CI" is the share of all known customers (including those without an email) that we can reach via TNP — the other half are guests / pure-anonymous sessions.', 'ai-eshop-optimizer'),
),
array(
'label' => __('Confirmed (C)', 'ai-eshop-optimizer'),
'value' => number_format_i18n((int) $row['confirmed']),
'sub' => __('reachable', 'ai-eshop-optimizer'),
'help' => __('Of the matched subscribers, those whose TNP status is "C" (confirmed double-opt-in). Only this cohort is legally and technically reachable for newsletter sends — unconfirmed / unsubscribed / bounced are excluded by TNP\'s own dispatcher.', 'ai-eshop-optimizer'),
),
array(
'label' => __('Ever opened', 'ai-eshop-optimizer'),
'value' => number_format_i18n((int) $row['ever_opened']),
'sub' => $opened_pct . '% ' . __('of confirmed', 'ai-eshop-optimizer'),
'help' => __('Subscribers who have opened at least one newsletter in their entire history (lifetime, no recency cap). Percentage is against the Confirmed cohort — the only meaningful denominator since unconfirmed users never received any sends.', 'ai-eshop-optimizer'),
),
array(
'label' => __('Ever clicked', 'ai-eshop-optimizer'),
'value' => number_format_i18n((int) $row['ever_clicked']),
'sub' => $clicked_pct . '% ' . __('of confirmed', 'ai-eshop-optimizer'),
'help' => __('Subscribers who clicked at least one link in any newsletter (lifetime). High-intent cohort: openers read, but clickers acted. This is the natural seed audience for promo / offer campaigns — they\'ve self-selected as buyers.', 'ai-eshop-optimizer'),
),
array(
'label' => __('Engaged in last 30d', 'ai-eshop-optimizer'),
'value' => number_format_i18n((int) $row['opened_30d']),
'sub' => $eng30_pct . '% ' . __('opened ≥1 in 30d', 'ai-eshop-optimizer'),
'help' => __('Subscribers who opened a newsletter in the last 30 days. Strong reachability signal — these are the audience most likely to read what you send next. Use as the high-confidence cohort for time-sensitive campaigns.', 'ai-eshop-optimizer'),
),
);
foreach ($tiles as $t) {
$this->tile($t['label'], $t['value'], isset($t['help']) ? (string) $t['help'] : '', (string) $t['sub']);
}
echo '
';
echo $close;
}
private function render_push_engagement_diagnostics_card() {
global $wpdb;
$tbl_subs = $wpdb->prefix . 'aieo_push_subscriptions';
$tbl_sess = $wpdb->prefix . 'aieo_sessions_statics';
$has_subs = (string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tbl_subs)) === $tbl_subs;
$has_sess = (string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tbl_sess)) === $tbl_sess;
if (!$has_subs || !$has_sess) { return; }
$has_channel_col = (int) $wpdb->get_var($wpdb->prepare(
"SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s",
DB_NAME, $tbl_sess, 'entry_channel'
));
if ($has_channel_col === 0) { return; }
$known_subs = (int) $wpdb->get_var(
"SELECT COUNT(DISTINCT user_id) FROM {$tbl_subs}
WHERE status = 'active' AND user_id > 0"
);
$anon_subs = (int) $wpdb->get_var(
"SELECT COUNT(*) FROM {$tbl_subs}
WHERE status = 'active' AND user_id = 0"
);
$total_subs = $known_subs + $anon_subs;
$ever_responded = (int) $wpdb->get_var(
"SELECT COUNT(DISTINCT user_id) FROM {$tbl_sess}
WHERE entry_channel = 'web_push' AND user_id > 0"
);
$col_for_recency = (int) $wpdb->get_var($wpdb->prepare(
"SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s",
DB_NAME, $tbl_sess, 'created_at'
)) > 0 ? 'created_at' : 'last_activity';
$responded_30d = (int) $wpdb->get_var($wpdb->prepare(
"SELECT COUNT(DISTINCT user_id) FROM {$tbl_sess}
WHERE entry_channel = 'web_push' AND user_id > 0
AND {$col_for_recency} >= %s",
gmdate('Y-m-d H:i:s', strtotime('-30 days'))
));
$denom = max(1, $known_subs);
$response_rate = round(100 * $ever_responded / $denom, 1);
$recent_rate = round(100 * $responded_30d / $denom, 1);
$close = $this->card_open(array(
'anchor' => 'surveys-web-push-engagement-campaign-reach-response',
'title' => __('Web push engagement', 'ai-eshop-optimizer'),
'description' => __('How many customers are subscribed to web push and how many come back to the site from a notification.', 'ai-eshop-optimizer'),
));
$this->guidance(__('Reads the active-subscriber count from wp_aieo_push_subscriptions and the response cohort from the attribution module (sessions tagged entry_channel = web_push). "Respond" = the customer clicked a push notification and landed on the site. Use the response rate to size campaigns: a 25% historical rate means a 10k push will earn ~2.5k clicks.', 'ai-eshop-optimizer'));
echo '
';
$tiles = array(
array(
'label' => __('Active subscribers', 'ai-eshop-optimizer'),
'value' => number_format_i18n($total_subs),
'sub' => sprintf(
__('%1$s known + %2$s anonymous', 'ai-eshop-optimizer'),
number_format_i18n($known_subs),
number_format_i18n($anon_subs)
),
'help' => __('Push-notification subscriptions in `wp_aieo_push_subscriptions` with status=active. Logged-in users counted by user_id; anonymous devices counted as one subscriber per active endpoint (session_token-scoped). Anonymous subs get push but the response rate below uses only logged-in subs since responses are also tracked by user_id.', 'ai-eshop-optimizer'),
),
array(
'label' => __('Ever responded', 'ai-eshop-optimizer'),
'value' => number_format_i18n($ever_responded),
'sub' => $response_rate . '% ' . __('of known subs', 'ai-eshop-optimizer'),
'help' => __('Distinct logged-in users who have at least one session tagged `entry_channel = web_push` in their entire history. Source: wp_aieo_sessions_statics + the attribution module. The percentage is the historical click-through rate per subscriber — multiply by your next push audience size to estimate clicks.', 'ai-eshop-optimizer'),
),
array(
'label' => __('Responded last 30d', 'ai-eshop-optimizer'),
'value' => number_format_i18n($responded_30d),
'sub' => $recent_rate . '% ' . __('of known subs', 'ai-eshop-optimizer'),
'help' => __('Subscribers who clicked a push in the last 30 days. The freshest reachability signal — use as the high-confidence cohort for time-sensitive campaigns (flash sales, low-stock alerts).', 'ai-eshop-optimizer'),
),
array(
'label' => __('Response rate', 'ai-eshop-optimizer'),
'value' => $response_rate . '%',
'sub' => __('lifetime, per subscriber', 'ai-eshop-optimizer'),
'help' => __('The headline planning number: of all logged-in subscribers, what fraction has ever clicked at least one push. Forecasting rule of thumb: campaign clicks ≈ audience × response_rate × (recency factor). Use the 30-day cohort for time-sensitive campaigns.', 'ai-eshop-optimizer'),
),
);
foreach ($tiles as $t) {
$this->tile($t['label'], $t['value'], isset($t['help']) ? (string) $t['help'] : '', (string) $t['sub']);
}
echo '
';
echo $close;
}
private function render_taste_vectors_card() {
global $wpdb;
$tbl_ci = $wpdb->prefix . 'aieo_customer_intelligence';
$tbl_emb = $wpdb->prefix . 'aieo_chat_embeddings';
if ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tbl_ci)) !== $tbl_ci) { return; }
$total = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl_ci}");
$with_vector = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl_ci} WHERE taste_vector IS NOT NULL");
$stale = (int) $wpdb->get_var(
"SELECT COUNT(*) FROM {$tbl_ci}
WHERE (top_sole_product_id > 0 OR top_first_product_id > 0 OR top_last_product_id > 0)
AND (taste_vector IS NULL
OR (last_order_at IS NOT NULL AND last_order_at > taste_vector_updated_at))"
);
$vector_bytes = (int) $wpdb->get_var("SELECT AVG(LENGTH(taste_vector)) FROM {$tbl_ci} WHERE taste_vector IS NOT NULL");
$cov_pct = round(100 * $with_vector / max(1, $total), 1);
$emb_total = (string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tbl_emb)) === $tbl_emb
? (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl_emb} WHERE LENGTH(embedding) > 100")
: 0;
$engine = get_option('aieo_chat_embedding_model', 'voyage-2');
$close = $this->card_open(array(
'anchor' => 'surveys-tier-3-voyage-taste-vectors',
'title' => __('Tier 3: customer taste vectors', 'ai-eshop-optimizer'),
'description' => __('The taste profile each customer gets from the products they bought, used to find similar customers.', 'ai-eshop-optimizer'),
));
$aieo_sv_t3 = __('Per-customer averaged embedding over their top sole / first / last products. Used in the k-NN distance metric as a third cosine term (weighted 4×). Currently using engine: %s.', 'ai-eshop-optimizer');
echo class_exists('\Rooster\Admin\Guidance')
? \Rooster\Admin\Guidance::text(sprintf($aieo_sv_t3, (string) $engine), 'guidance', array('html' => sprintf(esc_html($aieo_sv_t3), '' . esc_html($engine) . '')))
: '
';
$tiles = array(
array(
'label' => __('Customers vectorised', 'ai-eshop-optimizer'),
'value' => number_format_i18n($with_vector),
'sub' => $cov_pct . '% coverage',
'help' => __('Customers with a per-customer averaged taste-vector blob. Built from their top sole / first / last products via the Voyage embedding cache. Drives the embedding-cosine term in k-NN.', 'ai-eshop-optimizer'),
),
array(
'label' => __('Stale / pending', 'ai-eshop-optimizer'),
'value' => number_format_i18n($stale),
'sub' => __('need (re)compute', 'ai-eshop-optimizer'),
'help' => __('Customers who have signature products but no taste vector yet, OR whose last_order_at moved forward since vectorisation. Hourly cron processes 50 per tick.', 'ai-eshop-optimizer'),
),
array(
'label' => __('Avg vector size', 'ai-eshop-optimizer'),
'value' => number_format_i18n($vector_bytes) . ' B',
'sub' => $vector_bytes > 0 ? sprintf('~%d dims', (int) ($vector_bytes / 4)) : '—',
'help' => __('Average size in bytes of the stored taste-vector blob. Each dim is a float32 (4 bytes), so 6,144 B ≈ 1,536 dimensions — matches the Voyage-3-large model.', 'ai-eshop-optimizer'),
),
array(
'label' => __('Product embeddings', 'ai-eshop-optimizer'),
'value' => number_format_i18n($emb_total),
'sub' => __('available in cache', 'ai-eshop-optimizer'),
'help' => __('Total product embeddings in the Rooster One Chat embedding cache. Coverage of customer taste-vectors caps at "products in cache" — adding more product embeddings unblocks more customer vectorisation.', 'ai-eshop-optimizer'),
),
);
foreach ($tiles as $t) {
$this->tile($t['label'], $t['value'], (string) $t['help'], (string) $t['sub']);
}
echo '
';
echo '
';
echo esc_html__('Backfill runs automatically as part of "Recompute now" (full pass) and hourly via cron (50 stale customers per tick). Coverage rises as the product embedding cache fills.', 'ai-eshop-optimizer');
echo '
';
echo $close;
}
private function verify_price_historical_status() {
global $wpdb;
$tbl_orders = $wpdb->prefix . 'aieo_temp_orders';
$tbl_oi = $wpdb->prefix . 'woocommerce_order_items';
$tbl_oim = $wpdb->prefix . 'woocommerce_order_itemmeta';
if ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tbl_orders)) !== $tbl_orders) {
return array('ok' => false, 'error' => 'wp_aieo_temp_orders not present', 'rows' => array());
}
if ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tbl_oi)) !== $tbl_oi) {
return array('ok' => false, 'error' => 'wp_woocommerce_order_items not present (HPOS-only install?)', 'rows' => array());
}
$rows = $wpdb->get_results(
"SELECT
o.OrderId, o.OrderDate, o.SKU, o.ProductId, o.PrimaryQty,
o.Price AS aieo_price,
o.RegularPrice AS aieo_regular,
o.CurrentPrice AS aieo_current,
o.OnSale AS aieo_onsale,
sub.wc_line_subtotal,
sub.wc_line_total,
sub.wc_qty
FROM {$tbl_orders} o
LEFT JOIN (
SELECT
oi.order_id,
CAST(MAX(IF(oim_pid.meta_key='_product_id', oim_pid.meta_value, NULL)) AS UNSIGNED) AS product_id,
MAX(IF(oim.meta_key='_line_subtotal', oim.meta_value, NULL)) AS wc_line_subtotal,
MAX(IF(oim.meta_key='_line_total', oim.meta_value, NULL)) AS wc_line_total,
MAX(IF(oim.meta_key='_qty', oim.meta_value, NULL)) AS wc_qty
FROM {$tbl_oi} oi
JOIN {$tbl_oim} oim ON oim.order_item_id = oi.order_item_id
JOIN {$tbl_oim} oim_pid ON oim_pid.order_item_id = oi.order_item_id
AND oim_pid.meta_key = '_product_id'
WHERE oi.order_item_type = 'line_item'
GROUP BY oi.order_item_id
) sub ON sub.order_id = o.OrderId AND sub.product_id = o.ProductId
WHERE o.OrderDate IS NOT NULL
AND o.OrderDate < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 6 MONTH)
AND o.Price IS NOT NULL
AND o.PrimaryQty > 0
AND sub.wc_line_subtotal IS NOT NULL
ORDER BY o.OrderDate ASC
LIMIT 10",
ARRAY_A
);
return array('ok' => true, 'rows' => (array) $rows);
}
private function render_priority_diagnostics_card() {
global $wpdb;
$tbl_ci = $wpdb->prefix . 'aieo_customer_intelligence';
$ci_exists = (string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tbl_ci)) === $tbl_ci;
if (!$ci_exists) { return; }
$total = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl_ci}");
if ($total === 0) { return; }
$row = $wpdb->get_row("SELECT
-- discount_share buckets
SUM(IF(IFNULL(discount_share,0) = 0, 1, 0)) AS ds_0,
SUM(IF(discount_share > 0 AND discount_share < 0.10, 1, 0)) AS ds_low,
SUM(IF(discount_share >= 0.10 AND discount_share < 0.30, 1, 0)) AS ds_mid,
SUM(IF(discount_share >= 0.30 AND discount_share < 0.50, 1, 0)) AS ds_high,
SUM(IF(discount_share >= 0.50, 1, 0)) AS ds_max,
-- price_band quintiles
SUM(IF(price_band = 1, 1, 0)) AS pb_1,
SUM(IF(price_band = 2, 1, 0)) AS pb_2,
SUM(IF(price_band = 3, 1, 0)) AS pb_3,
SUM(IF(price_band = 4, 1, 0)) AS pb_4,
SUM(IF(price_band = 5, 1, 0)) AS pb_5,
SUM(IF(price_band = 0 OR price_band IS NULL, 1, 0)) AS pb_unset,
-- orders_count buckets
SUM(IF(orders_count = 1, 1, 0)) AS oc_1,
SUM(IF(orders_count = 2, 1, 0)) AS oc_2,
SUM(IF(orders_count BETWEEN 3 AND 5, 1, 0)) AS oc_3_5,
SUM(IF(orders_count BETWEEN 6 AND 10, 1, 0)) AS oc_6_10,
SUM(IF(orders_count >= 11, 1, 0)) AS oc_11p,
-- top_brand_share buckets
SUM(IF(IFNULL(top_brand_share,0) < 0.30, 1, 0)) AS tb_low,
SUM(IF(top_brand_share >= 0.30 AND top_brand_share < 0.50, 1, 0)) AS tb_mid,
SUM(IF(top_brand_share >= 0.50 AND top_brand_share < 0.70, 1, 0)) AS tb_high,
SUM(IF(top_brand_share >= 0.70, 1, 0)) AS tb_max
FROM {$tbl_ci}", ARRAY_A);
$close = $this->card_open(array(
'anchor' => 'surveys-heuristic-diagnostics-ci-signal-distribution',
'title' => __('Heuristic signal distributions', 'ai-eshop-optimizer'),
));
echo '
'
. sprintf(
esc_html__('Across %s customers in customer_intelligence. Use this to tune the heuristic thresholds — if a rule like "price_band >= 4" matches 78%% of the base, it is too lenient and dominates the distribution. The point of v3 is to set thresholds where they actually carve up the population, not where they accept the majority.', 'ai-eshop-optimizer'),
number_format_i18n($total)
)
. '
'
. esc_html__('k-NN feature: discount_share × 2.0 (weighted to amplify the price-sensitivity axis). 3.8%+ heavy users are now a discoverable audience.', 'ai-eshop-optimizer')
. '
'
. esc_html__('"orders_count <= 2" was a major over-fire signal in v1. v2 only counts when paired with Newbie/Browser segment.', 'ai-eshop-optimizer')
. '
';
echo wp_kses_post(sprintf(
__('Looking for segment membership counts? They live on the %1$sWeb Push → Segments%2$s card, which shows each segment with its definition and a single consistent denominator.', 'ai-eshop-optimizer'),
'',
''
));
echo '
';
$ds_populated = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl_ci} WHERE discount_share > 0 OR avg_item_price > 0");
$ds_label = $ds_populated > 0
? __('Refresh: recompute discount_share + price_band from WC line items', 'ai-eshop-optimizer')
: __('Fix: compute discount_share + price_band from WC line items', 'ai-eshop-optimizer');
$ds_caption = $ds_populated > 0
? sprintf(
esc_html__('Already populated for %d customers via the recompute pipeline. Click to manually refresh — useful when new orders land between scheduled runs.', 'ai-eshop-optimizer'),
$ds_populated
)
: esc_html__('discount_share is currently 100% zero and price_band is 100% unset because the Rooster One data-prep pipeline did not populate temp_orders.Price/OnSale/RegularPrice. Click below to compute both directly from wp_woocommerce_order_itemmeta (authoritative): a coupon-applied line is detected as _line_total < _line_subtotal; per-customer avg unit price is _line_subtotal/_qty; price_band is NTILE(5) over the population of avg unit prices. HPOS-compatible.', 'ai-eshop-optimizer');
$this->section($ds_label);
echo '
'
. esc_html__('Critical for the discount_share computation. Click below to compare temp_orders.Price against WC\'s _line_subtotal/_qty for 10 aged orders. If they match, columns are historical (we can compute discount_share correctly). If temp_orders.Price tracks the current product price instead, the columns are useless for discount-share inference and we\'d need to read directly from WC order item meta.', 'ai-eshop-optimizer')
. '
';
echo $this->verify_prices_inline_js();
echo $close;
}
private function fix_prices_inline_js() {
$nonce = wp_create_nonce(AIEO_DMM::NONCE_ACTION);
$ajax = admin_url('admin-ajax.php');
ob_start();
?>
$cnt) {
$out[] = array(
'label' => (string) $label,
'pct' => round(100 * $cnt / $max, 1),
'count_disp' => number_format_i18n($cnt) . ' (' . round(100 * $cnt / max(1, $total), 1) . '%)',
);
}
$this->micro_bar_table($out, $color);
}
private function build_label_to_slug_map(array $grank_to_label) {
$out = array();
if (!class_exists('AIEO_Survey_Choice_Register')) {
return $out;
}
$allowed = self::priority_taxonomy();
foreach ($grank_to_label as $label) {
$label = (string) $label;
if ('' === trim($label)) {
continue;
}
$code = AIEO_Survey_Choice_Register::code_for($label);
if ('' !== $code && isset($allowed[$code])) {
$out[$label] = $code;
}
}
return $out;
}
private function render_loyalty_journey_card($tbl_si) {
global $wpdb;
$tbl_sr = $wpdb->prefix . 'aieo_survey_responses';
$r = $wpdb->get_row("SELECT
COUNT(*) total,
SUM(CASE WHEN orders_before_survey > 1 THEN 1 ELSE 0 END) returning_at_survey,
SUM(CASE WHEN orders_before_survey <= 1 THEN 1 ELSE 0 END) firsttime_at_survey,
SUM(CASE WHEN orders_after_survey > 0 THEN 1 ELSE 0 END) bought_again,
SUM(CASE WHEN orders_after_survey = 0 THEN 1 ELSE 0 END) silent_after,
SUM(spend_before_survey) total_spend_before,
SUM(spend_after_survey) total_spend_after,
SUM(CASE WHEN orders_before_survey > 0 OR orders_after_survey > 0 THEN 1 ELSE 0 END) any_orders,
SUM(orders_before_survey) total_orders_before,
SUM(orders_after_survey) total_orders_after,
SUM(spend_before_survey) / NULLIF(SUM(orders_before_survey), 0) aov_before,
SUM(spend_after_survey) / NULLIF(SUM(orders_after_survey), 0) aov_after
FROM {$tbl_si} WHERE f18_entry_id > 0", ARRAY_A);
$total = (int) ($r['total'] ?? 0);
$close = $this->card_open(array(
'anchor' => 'surveys-survey-respondent-loyalty-journey',
'title' => __('F18 respondent loyalty journey', 'ai-eshop-optimizer'),
'description' => __('How the customers who answered the post-order survey bought before it and after it.', 'ai-eshop-optimizer'),
));
$this->guidance(__('Cross-references the F18 responder set against Rooster One order history. The first row answers "who were they before the survey?", the second "what did they do after?". The bottom 2×2 splits returners by whether they also filled the survey a second time — the gold cohort gives us explicit drift, the silent cohort gives us implicit feedback through repurchase.', 'ai-eshop-optimizer'));
if ($total === 0) {
echo \Rooster\Admin\Ui::notice(esc_html__('No data yet — press "Recompute now" above to build wp_aieo_survey_intelligence from the ingested responses. Recompute also runs the purchase-correlation step that joins your Rooster One order snapshots to fill orders_before / orders_after / spend_before / spend_after.', 'ai-eshop-optimizer'), 'warning', array('inline' => true));
echo $close;
return;
}
$any_orders = (int) ($r['any_orders'] ?? 0);
if ($any_orders === 0) {
echo \Rooster\Admin\Ui::notice(esc_html__('Survey intelligence has rows but the purchase-correlation columns are all zero. compute_purchase_metrics() likely failed to find any wp_aieo_*_orders snapshots, or none of the responder eponymous_ids matched. Re-run Recompute and check debug.log if it persists.', 'ai-eshop-optimizer'), 'destructive', array('inline' => true));
}
$cohort = $wpdb->get_row("SELECT
SUM(CASE WHEN si.orders_after_survey > 0 AND rep.cnt > 1 THEN 1 ELSE 0 END) bought_refilled,
SUM(CASE WHEN si.orders_after_survey > 0 AND (rep.cnt IS NULL OR rep.cnt = 1) THEN 1 ELSE 0 END) bought_silent,
SUM(CASE WHEN si.orders_after_survey = 0 AND rep.cnt > 1 THEN 1 ELSE 0 END) norebuy_refilled,
SUM(CASE WHEN si.orders_after_survey = 0 AND (rep.cnt IS NULL OR rep.cnt = 1) THEN 1 ELSE 0 END) norebuy_silent
FROM {$tbl_si} si
LEFT JOIN (
SELECT eponymous_id, COUNT(*) cnt
FROM {$tbl_sr} WHERE form_id = 18 AND eponymous_id > 0
GROUP BY eponymous_id
) rep ON rep.eponymous_id = si.eponymous_id
WHERE si.f18_entry_id > 0", ARRAY_A);
$returning = (int) $r['returning_at_survey'];
$firsttime = (int) $r['firsttime_at_survey'];
$bought = (int) $r['bought_again'];
$silent = (int) $r['silent_after'];
$aov_before = (float) $r['aov_before'];
$aov_after = (float) $r['aov_after'];
$aov_lift = $aov_before > 0 ? round(100 * ($aov_after - $aov_before) / $aov_before, 1) : 0;
$this->section(__('At time of survey', 'ai-eshop-optimizer'));
echo '
';
$this->tile(
__('Returning customers', 'ai-eshop-optimizer'),
number_format_i18n($returning) . ' (' . round(100 * $returning / max(1, $total), 1) . '%)',
__('Of all F18 responders, how many had already placed 2+ orders BEFORE submitting the survey. These were already loyal customers when they answered.', 'ai-eshop-optimizer')
);
$this->tile(
__('First-time at survey', 'ai-eshop-optimizer'),
number_format_i18n($firsttime) . ' (' . round(100 * $firsttime / max(1, $total), 1) . '%)',
__('Of all F18 responders, how many had 0 or 1 prior orders when submitting. These were brand-new (or barely past their first order) when they answered.', 'ai-eshop-optimizer')
);
$this->tile(
__('Avg AOV before', 'ai-eshop-optimizer'),
class_exists('AIEO_DMM_Conversions_Registry') ? AIEO_DMM_Conversions_Registry::money($aov_before) : number_format_i18n($aov_before, 2),
__('Average order value across every order any F18 responder placed BEFORE their own f18_submitted_at. Calculated as total revenue ÷ total orders (industry-standard AOV).', 'ai-eshop-optimizer')
);
echo '
';
$this->section(__('After the survey', 'ai-eshop-optimizer'));
echo '
';
$this->tile(
__('Bought again', 'ai-eshop-optimizer'),
number_format_i18n($bought) . ' (' . round(100 * $bought / max(1, $total), 1) . '%)',
__('Of all F18 responders, how many placed at least one order AFTER submitting. The implicit-positive cohort — they returned without leaving a second survey.', 'ai-eshop-optimizer')
);
$this->tile(
__('Silent (no return)', 'ai-eshop-optimizer'),
number_format_i18n($silent) . ' (' . round(100 * $silent / max(1, $total), 1) . '%)',
__('Of all F18 responders, how many placed zero orders AFTER submitting. The win-back target audience — they answered the survey and went quiet.', 'ai-eshop-optimizer')
);
$this->tile(
__('Avg AOV after', 'ai-eshop-optimizer'),
class_exists('AIEO_DMM_Conversions_Registry') ? AIEO_DMM_Conversions_Registry::money($aov_after) : number_format_i18n($aov_after, 2),
__('Average order value across every order placed AFTER each responder\'s f18_submitted_at. Same total-revenue ÷ total-orders formula as Avg AOV before.', 'ai-eshop-optimizer')
);
$this->tile(
__('AOV lift', 'ai-eshop-optimizer'),
($aov_lift >= 0 ? '+' : '') . number_format_i18n($aov_lift, 1) . '%',
__('Percentage change between Avg AOV after and Avg AOV before. Positive = customers spend more per order after taking the survey; negative = they spend less.', 'ai-eshop-optimizer')
);
echo '
';
$br = (int) ($cohort['bought_refilled'] ?? 0);
$bs = (int) ($cohort['bought_silent'] ?? 0);
$nr = (int) ($cohort['norebuy_refilled'] ?? 0);
$ns = (int) ($cohort['norebuy_silent'] ?? 0);
$this->section(__('Returner × refiller cohorts', 'ai-eshop-optimizer'));
$cohorts = array(
array(
'cohort' => __('Bought again', 'ai-eshop-optimizer'),
'refilled' => $br,
'refilled_note' => __('explicit drift cohort', 'ai-eshop-optimizer'),
'silent' => $bs,
'silent_note' => __('implicit positive', 'ai-eshop-optimizer'),
'row_total' => $br + $bs,
),
array(
'cohort' => __('Did not buy again', 'ai-eshop-optimizer'),
'refilled' => $nr,
'refilled_note' => '',
'silent' => $ns,
'silent_note' => __('lost / silent', 'ai-eshop-optimizer'),
'row_total' => $nr + $ns,
),
);
AIEO_Table::render(array(
'page_id' => $this->get_id(),
'row_key' => 'cohort',
'sortable' => false,
'density' => 'compact',
'rows' => $cohorts,
'empty' => __('No F18 responders with order history yet.', 'ai-eshop-optimizer'),
'columns' => array(
array('key' => 'cohort', 'label' => '', 'type' => 'text', 'nowrap' => true),
array(
'key' => 'refilled',
'label' => __('Refilled F18', 'ai-eshop-optimizer'),
'type' => 'integer',
'subline' => 'refilled_note',
),
array(
'key' => 'silent',
'label' => __('No second survey', 'ai-eshop-optimizer'),
'type' => 'integer',
'subline' => 'silent_note',
),
array('key' => 'row_total', 'label' => __('Row total', 'ai-eshop-optimizer'), 'type' => 'integer'),
),
));
echo $close;
}
private function render_repeat_respondents_card($tbl_sr) {
global $wpdb;
$rows = $wpdb->get_results(
"WITH ranked AS (
SELECT eponymous_id, email, response_id, submitted_at,
f18_nps, f18_satisfaction, f18_site_ux_avg,
ROW_NUMBER() OVER (PARTITION BY eponymous_id ORDER BY submitted_at ASC, response_id ASC) rn_first,
ROW_NUMBER() OVER (PARTITION BY eponymous_id ORDER BY submitted_at DESC, response_id DESC) rn_last,
COUNT(*) OVER (PARTITION BY eponymous_id) total_responses
FROM {$tbl_sr}
WHERE form_id = 18 AND eponymous_id > 0
)
SELECT
f.eponymous_id, f.email, f.total_responses,
f.submitted_at first_at, l.submitted_at last_at,
DATEDIFF(l.submitted_at, f.submitted_at) days_between,
f.f18_nps nps_first,
l.f18_nps nps_last,
f.f18_satisfaction sat_first,
l.f18_satisfaction sat_last,
f.f18_site_ux_avg ux_first,
l.f18_site_ux_avg ux_last,
(CAST(l.f18_nps AS SIGNED) - CAST(f.f18_nps AS SIGNED)) nps_delta,
(CAST(l.f18_satisfaction AS SIGNED) - CAST(f.f18_satisfaction AS SIGNED)) sat_delta
FROM ranked f
JOIN ranked l ON l.eponymous_id = f.eponymous_id AND l.rn_last = 1
WHERE f.rn_first = 1 AND f.total_responses > 1",
ARRAY_A
);
$total_repeats = count($rows);
$close = $this->card_open(array(
'anchor' => 'surveys-sentiment-drift-on-repeat-f18',
'title' => __('Sentiment drift on repeat F18', 'ai-eshop-optimizer'),
'description' => __('How the answers of customers who filled the post-order survey twice changed between the two times.', 'ai-eshop-optimizer'),
));
$this->guidance(__('Customers who filled F18 after a second order — same survey, two timepoints. Positive deltas mean they got happier, negative means churn-risk drift. The biggest swings are the most actionable: a -3 NPS swing on a high-LTV customer is rescue priority, a +2 swing is a referral-program seed.', 'ai-eshop-optimizer'));
if ($total_repeats === 0) {
$this->state('first-use', array('title' => __('No customers have filled F18 more than once yet — every responder is a single-shot. As repeat orders flow through, this cohort builds up automatically.', 'ai-eshop-optimizer')));
echo $close;
return;
}
$more_positive = $more_negative = $unchanged = 0;
$sum_nps_delta = 0; $sum_sat_delta = 0; $sum_days = 0;
foreach ($rows as $r) {
$combo = (int) $r['nps_delta'] + (int) $r['sat_delta'];
if ($combo > 0) { $more_positive++; }
elseif ($combo < 0) { $more_negative++; }
else { $unchanged++; }
$sum_nps_delta += (int) $r['nps_delta'];
$sum_sat_delta += (int) $r['sat_delta'];
$sum_days += (int) $r['days_between'];
}
$avg_nps_delta = round($sum_nps_delta / $total_repeats, 2);
$avg_sat_delta = round($sum_sat_delta / $total_repeats, 2);
$avg_days = round($sum_days / $total_repeats, 0);
echo '
';
$this->tile(__('Repeat F18 responders', 'ai-eshop-optimizer'), number_format_i18n($total_repeats),
__('Customers who filled F18 at least twice — same survey, two timepoints. Their first→second swing is the cleanest read on whether the experience improved or degraded.', 'ai-eshop-optimizer'));
$this->tile(__('More positive', 'ai-eshop-optimizer'),
number_format_i18n($more_positive) . ' (' . round(100 * $more_positive / max(1, $total_repeats), 1) . '%)',
__('Repeat responders whose combined NPS+Satisfaction delta is above zero — they got happier between the two surveys. Promote candidates for advocate programs.', 'ai-eshop-optimizer'));
$this->tile(__('More negative', 'ai-eshop-optimizer'),
number_format_i18n($more_negative) . ' (' . round(100 * $more_negative / max(1, $total_repeats), 1) . '%)',
__('Repeat responders whose combined delta is below zero — drift toward churn. The biggest swings on high-LTV customers are the priority rescue list.', 'ai-eshop-optimizer'));
$this->tile(__('Unchanged', 'ai-eshop-optimizer'),
number_format_i18n($unchanged) . ' (' . round(100 * $unchanged / max(1, $total_repeats), 1) . '%)',
__('Repeat responders whose combined delta = 0 — sentiment held steady. Largest cell in most healthy shops; gives a baseline against which positive/negative deltas are meaningful.', 'ai-eshop-optimizer'));
$this->tile(__('Avg NPS change', 'ai-eshop-optimizer'),
($avg_nps_delta >= 0 ? '+' : '') . number_format_i18n($avg_nps_delta, 2),
__('Mean change in NPS score from first F18 to last F18 across all repeat responders. Positive = relationship deepening, negative = trust eroding.', 'ai-eshop-optimizer'));
$this->tile(__('Avg Sat. change', 'ai-eshop-optimizer'),
($avg_sat_delta >= 0 ? '+' : '') . number_format_i18n($avg_sat_delta, 2),
__('Mean change in Overall-Satisfaction score from first to last F18. Tracked separately from NPS because someone can stay loyal (high NPS) while quietly satisfaction-decaying.', 'ai-eshop-optimizer'));
$this->tile(__('Avg gap', 'ai-eshop-optimizer'),
number_format_i18n($avg_days) . ' ' . esc_html__('days', 'ai-eshop-optimizer'),
__('Average days between a repeat responder\'s first F18 submission and their most recent. Gives the natural sentiment-revisit cadence for these customers.', 'ai-eshop-optimizer'));
echo '
';
usort($rows, function($a, $b) {
$ab = abs((int) $a['nps_delta'] + (int) $a['sat_delta']);
$bb = abs((int) $b['nps_delta'] + (int) $b['sat_delta']);
return $bb <=> $ab;
});
$top = array_slice($rows, 0, 20);
$this->section(__('Biggest swings (first → last)', 'ai-eshop-optimizer'));
$swings = array();
foreach ($top as $r) {
$swings[] = array(
'email' => (string) $r['email'],
'responses' => (int) $r['total_responses'],
'days' => (int) $r['days_between'],
'nps_first' => (int) $r['nps_first'],
'nps_last' => (int) $r['nps_last'],
'nps_delta' => (int) $r['nps_delta'],
'sat_first' => (int) $r['sat_first'],
'sat_last' => (int) $r['sat_last'],
'sat_delta' => (int) $r['sat_delta'],
);
}
$delta_bands = array(
array('max' => -1, 'tone' => 'danger'),
array('min' => 0, 'max' => 0, 'tone' => 'muted'),
array('min' => 1, 'tone' => 'success'),
);
AIEO_Table::render(array(
'page_id' => $this->get_id(),
'row_key' => 'email',
'density' => 'compact',
'rows' => $swings,
'cap' => 20,
'total' => count($rows),
'capped' => count($rows) > count($swings),
'empty' => __('No repeat F18 responders yet.', 'ai-eshop-optimizer'),
'columns' => array(
array('key' => 'email', 'label' => __('Email', 'ai-eshop-optimizer'), 'type' => 'text'),
array('key' => 'responses', 'label' => __('Responses', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'days', 'label' => __('Days apart', 'ai-eshop-optimizer'), 'type' => 'integer'),
array(
'key' => 'nps_last',
'label' => __('NPS', 'ai-eshop-optimizer'),
'type' => 'diff',
'format' => array('from' => 'nps_first', 'as' => 'integer'),
),
array(
'key' => 'nps_delta',
'label' => __('NPS change', 'ai-eshop-optimizer'),
'type' => 'integer',
'format' => array('sign' => true),
'bands' => $delta_bands,
),
array(
'key' => 'sat_last',
'label' => __('Sat.', 'ai-eshop-optimizer'),
'type' => 'diff',
'format' => array('from' => 'sat_first', 'as' => 'integer'),
),
array(
'key' => 'sat_delta',
'label' => __('Satisfaction change', 'ai-eshop-optimizer'),
'type' => 'integer',
'format' => array('sign' => true),
'bands' => $delta_bands,
),
),
));
echo $close;
}
private function render_phase5_todo_card() {
$close = $this->card_open(array(
'anchor' => 'surveys-form-20-dispatch-trigger-phase-5-todo',
'title' => __('Form 20 dispatch trigger', 'ai-eshop-optimizer'),
'description' => __('The plan to email the post-delivery survey automatically a week after an order ships.', 'ai-eshop-optimizer'),
));
$this->guidance(__('Nothing runs yet: Phase 5+ will email Form 20 a week after the order ships, via WooCommerce order-status hooks, starting with the 1,400+ who consented on F18 q70.', 'ai-eshop-optimizer'), 'guidance', __('Does it run yet?', 'ai-eshop-optimizer'));
$this->guidance(__('Form 20 (post-delivery satisfaction) is structurally ready — fields cover delivery speed, packaging, per-product expectation match, repurchase intent, gift category preference, and personal traits. We have 11 entries currently. The plan is to email the form a week after dispatch via WC order-status hooks, prioritising customers who answered "Yes" on F18 q70 (1,400+ pre-consented). When that trigger lands in Phase 5+, the personal-profile data + gift_category_preference unlock visual product matching and gift-occasion campaigns we can\'t do today.', 'ai-eshop-optimizer'), 'guidance', __('What Form 20 holds', 'ai-eshop-optimizer'));
echo $close;
}
private function render_competition_signal_card() {
if (!class_exists('AIEO_Competition_Analytics') && defined('AIEO_DIR')) {
foreach (array('class-aieo-competition-cpt.php', 'class-aieo-competition-participants.php', 'class-aieo-competition-analytics.php') as $f) {
$p = AIEO_DIR . 'includes/dmm/competitions/' . $f;
if (file_exists($p)) { require_once $p; }
}
}
if (!class_exists('AIEO_Competition_Analytics')) { return; }
$agg = AIEO_Competition_Analytics::aggregate(10);
if ((int) $agg['total_participants'] === 0) { return; }
$pct = $agg['unique_emails'] > 0 ? round(100 * $agg['identified'] / $agg['unique_emails'], 1) : 0;
$close = $this->card_open(array(
'anchor' => 'surveys-competition-engagement',
'icon' => 'award',
'title' => __('Competition engagement', 'ai-eshop-optimizer'),
));
echo '
'
. esc_html__('Prize-competition entry as a customer signal — how your giveaway entrants overlap with known customers and which are worth most. Rolled up across all competitions (per-competition detail lives on each competition).', 'ai-eshop-optimizer')
. '
';
echo '
';
$this->tile(__('Unique participants', 'ai-eshop-optimizer'), number_format_i18n($agg['unique_emails']),
__('Distinct people (by email) who entered any competition.', 'ai-eshop-optimizer'));
$this->tile(__('Known customers among them', 'ai-eshop-optimizer'), number_format_i18n($agg['identified']) . ' (' . $pct . '%)',
__('Entrants resolved to a customer in the CDP (eponymous_id) — the overlap between competition entrants and your customer base.', 'ai-eshop-optimizer'));
if (!empty($agg['has_order_data'])) {
$this->tile(__('Entrants who ordered', 'ai-eshop-optimizer'), number_format_i18n($agg['converting_entrants']),
__('Entrants whose email matches at least one order — competition entry → purchase.', 'ai-eshop-optimizer'));
$this->tile(__('Revenue from entrants', 'ai-eshop-optimizer'), class_exists('AIEO_DMM_Conversions_Registry') ? AIEO_DMM_Conversions_Registry::money($agg['entrant_revenue']) : number_format_i18n($agg['entrant_revenue'], 2),
__('Total lifetime order revenue from people who entered a competition.', 'ai-eshop-optimizer'));
}
echo '