mb_strlen( (string) $a[ $f ] ) ) { $a[ $f ] = $b[ $f ]; } } $a['consumers'] = self::union_csv( $a['consumers'], $b['consumers'] ); $a['tables'] = array_values( array_unique( array_merge( $a['tables'], $b['tables'] ) ) ); $a['cron_hooks'] = array_values( array_unique( array_merge( $a['cron_hooks'], $b['cron_hooks'] ) ) ); $a['site_profiles'] = array_values( array_unique( array_merge( $a['site_profiles'], $b['site_profiles'] ) ) ); $a['consumed'] = ( $a['consumed'] && $b['consumed'] ); if ( empty( $a['producer_class'] ) && ! empty( $b['producer_class'] ) ) { $a['producer_class'] = $b['producer_class']; } foreach ( array( 'gate_option', 'gate_note' ) as $f ) { if ( empty( $a[ $f ] ) && ! empty( $b[ $f ] ) ) { $a[ $f ] = $b[ $f ]; } } return $a; } private static function union_csv( $a, $b ) { $parts = array_merge( explode( ',', (string) $a ), explode( ',', (string) $b ) ); $seen = array(); foreach ( $parts as $p ) { $p = trim( $p ); if ( '' === $p ) { continue; } $seen[ strtolower( $p ) ] = $p; } return implode( ', ', array_values( $seen ) ); } private static function normalise_entry( array $e ) { $defaults = array( 'id' => '', 'label' => '', 'measures' => '', 'tables' => array(), 'producer' => '', 'producer_class' => '', 'cron_hooks' => array(), 'grain' => '', 'consumers' => '', 'consumed' => true, 'site_profiles' => array( 'all' ), 'notes' => '', 'last_run_option' => '', 'last_run_job' => '', 'gate_option' => '', 'gate_note' => '', 'owned_column' => '', 'min_rows' => 0, 'cron_kind' => 'recurring', ); $e = array_merge( $defaults, $e ); foreach ( array( 'tables', 'cron_hooks', 'site_profiles' ) as $f ) { $e[ $f ] = array_values( array_filter( (array) $e[ $f ] ) ); } foreach ( $e['site_profiles'] as $p ) { if ( ! in_array( $p, self::PROFILES, true ) ) { $e['site_profiles'] = array( 'all' ); break; } } return $e; } public static function status( $force = false ) { if ( ! $force ) { $cached = get_transient( self::TRANSIENT ); if ( is_array( $cached ) && isset( $cached['signals'], $cached['shape'] ) && self::SHAPE === $cached['shape'] ) { return $cached; } } $signals = self::all(); $profile = class_exists( 'AIEO_SEO_Score' ) ? (string) AIEO_SEO_Score::site_profile() : 'shop'; $tables = self::probe_tables( $signals ); foreach ( $signals as $i => $s ) { $ap = self::applicability( $s, $profile ); $signals[ $i ]['applicable'] = $ap['applicable']; $signals[ $i ]['na_reason'] = $ap['reason']; } $owned = self::probe_owned_columns( $signals, $tables ); $crons = self::probe_crons( $signals ); $jobs = get_option( self::JOBS_LAST_RUN_OPT, array() ); $jobs = is_array( $jobs ) ? $jobs : array(); $counts = array_fill_keys( self::STATES, 0 ); foreach ( $signals as $i => $s ) { $rows = null; $present = 0; $table_details = array(); $empty_tables = array(); $missing_tables = array(); $stale_scratch = array(); $uncounted_tables = array(); $transient = isset( $s['transient_tables'] ) ? (array) $s['transient_tables'] : array(); $lazy = isset( $s['lazy_tables'] ) ? (array) $s['lazy_tables'] : array(); foreach ( $s['tables'] as $t ) { $info = isset( $tables[ $t ] ) ? $tables[ $t ] : array( 'exists' => false, 'rows' => null, 'type' => '' ); $is_transient = in_array( $t, $transient, true ); $table_details[] = array_merge( array( 'name' => $t, 'transient' => $is_transient, 'lazy' => in_array( $t, $lazy, true ) ), $info ); if ( $is_transient && empty( $info['exists'] ) ) { continue; } if ( $is_transient && ! empty( $info['exists'] ) ) { $stale_scratch[] = $t; } if ( ! empty( $info['exists'] ) ) { $present++; if ( null !== $info['rows'] ) { $rows = (int) $rows + (int) $info['rows']; if ( 0 === (int) $info['rows'] ) { $empty_tables[] = $t; } } elseif ( 'VIEW' !== $info['type'] ) { $uncounted_tables[] = $t; } } else { $missing_tables[] = $t; } } $scheduled = false; $next = 0; $cron_det = array(); $cron_dead = array(); foreach ( $s['cron_hooks'] as $h ) { $c = isset( $crons[ $h ] ) ? $crons[ $h ] : array( 'scheduled' => false, 'next' => 0, 'via' => '' ); $cron_det[] = array_merge( array( 'hook' => $h ), $c ); if ( ! empty( $c['scheduled'] ) ) { $scheduled = true; if ( ! $next || ( $c['next'] && $c['next'] < $next ) ) { $next = (int) $c['next']; } } else { $cron_dead[] = $h; } } if ( '' !== $s['owned_column'] && isset( $owned[ $i ] ) && null !== $owned[ $i ] ) { $rows = (int) $owned[ $i ]; if ( 0 === $rows ) { $empty_tables = array( $s['tables'][0] . '.' . $s['owned_column'] ); } else { $empty_tables = array(); } $uncounted_tables = array(); } $last_run = self::resolve_last_run( $s, $jobs ); $signals[ $i ]['health'] = array( 'tables' => $table_details, 'tables_present' => $present, 'tables_total' => count( $s['tables'] ), 'all_transient' => ( ! empty( $s['tables'] ) && count( $transient ) >= count( $s['tables'] ) ), 'rows' => $rows, 'empty_tables' => $empty_tables, 'missing_tables' => $missing_tables, 'stale_scratch' => $stale_scratch, 'uncounted_tables' => $uncounted_tables, 'applicable' => ! empty( $signals[ $i ]['applicable'] ), 'na_reason' => (string) $signals[ $i ]['na_reason'], 'crons_unscheduled' => $cron_dead, 'partial' => ( ! empty( $empty_tables ) || ! empty( $missing_tables ) || ! empty( $cron_dead ) ), 'crons' => $cron_det, 'scheduled' => $scheduled, 'next_run' => $next, 'last_run' => $last_run, 'code_present' => self::code_present( $s ), 'unconfigured' => ( '' !== $s['gate_option'] && ! self::option_filled( $s['gate_option'] ) ), 'cron_missing' => ( 'on_demand' !== $s['cron_kind'] && ! empty( $s['cron_hooks'] ) && ! $scheduled ), 'cron_overdue' => self::overdue_hours( $cron_det ), 'refused' => self::refused_today( $s ), 'check' => self::check_figures( $s ), 'underpopulated' => 0, 'floor' => 0, ); $resolved = self::resolve_state( $s, $signals[ $i ]['health'] ); $signals[ $i ]['state'] = $resolved['state']; $signals[ $i ]['state_reason'] = $resolved['reason']; $counts[ $resolved['state'] ] += 1; } $rows_by_id = array(); foreach ( $signals as $s ) { $rows_by_id[ $s['id'] ] = isset( $s['health']['rows'] ) ? $s['health']['rows'] : null; } foreach ( $signals as $i => $s ) { $floor = self::resolve_floor( $s['min_rows'], $rows_by_id ); if ( $floor < 1 ) { continue; } $signals[ $i ]['health']['floor'] = $floor; $rows = $signals[ $i ]['health']['rows']; if ( null === $rows ) { continue; } if ( (int) $rows > 0 && (int) $rows < $floor ) { $signals[ $i ]['health']['underpopulated'] = 1; } } $payload = array( 'shape' => self::SHAPE, 'signals' => $signals, 'counts' => $counts, 'generated' => time(), ); set_transient( self::TRANSIENT, $payload, (int) apply_filters( 'aieo_signal_registry_ttl', self::TTL ) ); return $payload; } private static function resolve_floor( $decl, array $rows_by_id ) { if ( is_numeric( $decl ) ) { return max( 0, (int) $decl ); } if ( ! is_array( $decl ) || empty( $decl['of'] ) ) { return 0; } $of = (string) $decl['of']; if ( ! array_key_exists( $of, $rows_by_id ) || null === $rows_by_id[ $of ] ) { return 0; } $ratio = isset( $decl['ratio'] ) ? (float) $decl['ratio'] : 0.0; if ( $ratio <= 0 ) { return 0; } return (int) floor( (int) $rows_by_id[ $of ] * $ratio ); } public static function flush() { delete_transient( self::TRANSIENT ); } private static function overdue_hours( array $cron_det ) { $now = time(); $worst = 0.0; foreach ( $cron_det as $c ) { if ( empty( $c['scheduled'] ) || empty( $c['next'] ) ) { continue; } $late = ( $now - (int) $c['next'] ) / HOUR_IN_SECONDS; if ( $late > $worst ) { $worst = $late; } } $grace = (float) apply_filters( 'aieo_signal_registry_overdue_grace_hours', 2.0 ); return ( $worst > $grace ) ? round( $worst, 1 ) : 0.0; } private static function probe_owned_columns( array $signals, array $tables ) { global $wpdb; $out = array(); $by_host = array(); foreach ( $signals as $i => $s ) { $col = isset( $s['owned_column'] ) ? (string) $s['owned_column'] : ''; if ( '' === $col || empty( $s['tables'][0] ) ) { continue; } if ( ! preg_match( '/^[A-Za-z0-9_]+$/', $col ) ) { continue; } $host = (string) $s['tables'][0]; $info = isset( $tables[ $host ] ) ? $tables[ $host ] : array(); if ( empty( $info['exists'] ) || 'VIEW' === ( isset( $info['type'] ) ? $info['type'] : '' ) ) { continue; } $by_host[ $host ][ $i ] = $col; } foreach ( $by_host as $host => $map ) { $full = $wpdb->prefix . $host; $have = (array) $wpdb->get_col( $wpdb->prepare( 'SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s', $full ) ); $have = array_map( 'strtolower', $have ); $parts = array(); $idx = array(); foreach ( $map as $i => $col ) { if ( ! in_array( strtolower( $col ), $have, true ) ) { $out[ $i ] = null; continue; } $alias = 'c' . count( $parts ); $parts[] = "SUM(`{$col}` IS NOT NULL AND `{$col}` <> '' AND `{$col}` <> 0) AS {$alias}"; $idx[ $alias ] = $i; } if ( ! $parts ) { continue; } $row = $wpdb->get_row( 'SELECT ' . implode( ', ', $parts ) . " FROM `{$full}`", ARRAY_A ); foreach ( $idx as $alias => $i ) { $out[ $i ] = ( is_array( $row ) && isset( $row[ $alias ] ) ) ? (int) $row[ $alias ] : null; } } return $out; } private static function probe_tables( array $signals ) { global $wpdb; $unprefixed = array(); foreach ( $signals as $s ) { foreach ( $s['tables'] as $t ) { $unprefixed[ $t ] = true; } } $unprefixed = array_keys( $unprefixed ); $out = array(); foreach ( $unprefixed as $t ) { $out[ $t ] = array( 'exists' => false, 'rows' => null, 'type' => '' ); } if ( empty( $unprefixed ) ) { return $out; } $full = array(); foreach ( $unprefixed as $t ) { $full[ $wpdb->prefix . $t ] = $t; } $names = array_keys( $full ); $names[] = $wpdb->prefix . 'actionscheduler_actions'; $ph = implode( ',', array_fill( 0, count( $names ), '%s' ) ); $rows = $wpdb->get_results( $wpdb->prepare( "SELECT TABLE_NAME, TABLE_TYPE, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ({$ph})", $names ), ARRAY_A ); $as_name = $wpdb->prefix . 'actionscheduler_actions'; $zero = array(); foreach ( (array) $rows as $r ) { $name = (string) $r['TABLE_NAME']; if ( $name === $as_name ) { self::$as_table_exists = true; } if ( ! isset( $full[ $name ] ) ) { continue; } $key = $full[ $name ]; $type = (string) $r['TABLE_TYPE']; $est = ( null === $r['TABLE_ROWS'] ) ? null : (int) $r['TABLE_ROWS']; $out[ $key ] = array( 'exists' => true, 'rows' => $est, 'type' => $type, ); if ( 'VIEW' === $type ) { $out[ $key ]['rows'] = null; } elseif ( 0 === $est || null === $est ) { $bytes = (int) $r['DATA_LENGTH'] + (int) $r['INDEX_LENGTH']; if ( $bytes > self::count_byte_cap() ) { $out[ $key ]['rows'] = null; } else { $zero[ $name ] = $key; } } } if ( ! empty( $zero ) ) { $parts = array(); foreach ( $zero as $name => $key ) { $parts[] = $wpdb->prepare( "SELECT %s AS k, COUNT(*) AS c FROM `{$name}`", $key ); } $exact = $wpdb->get_results( implode( ' UNION ALL ', $parts ), ARRAY_A ); foreach ( (array) $exact as $r ) { $k = (string) $r['k']; if ( isset( $out[ $k ] ) ) { $out[ $k ]['rows'] = (int) $r['c']; } } } return $out; } private static $as_table_exists = false; private static function count_byte_cap() { return (int) apply_filters( 'aieo_signal_registry_count_byte_cap', 16 * MB_IN_BYTES ); } private static function probe_crons( array $signals ) { global $wpdb; $hooks = array(); foreach ( $signals as $s ) { foreach ( $s['cron_hooks'] as $h ) { $hooks[ $h ] = array( 'scheduled' => false, 'next' => 0, 'via' => '' ); } } if ( empty( $hooks ) ) { return $hooks; } $cron = _get_cron_array(); if ( is_array( $cron ) ) { foreach ( $cron as $ts => $events ) { if ( ! is_array( $events ) ) { continue; } foreach ( array_keys( $events ) as $hook ) { if ( ! isset( $hooks[ $hook ] ) ) { continue; } if ( ! $hooks[ $hook ]['scheduled'] || $ts < $hooks[ $hook ]['next'] ) { $hooks[ $hook ] = array( 'scheduled' => true, 'next' => (int) $ts, 'via' => 'wp-cron' ); } } } } if ( self::$as_table_exists ) { $t = $wpdb->prefix . 'actionscheduler_actions'; $names = array_keys( $hooks ); $ph = implode( ',', array_fill( 0, count( $names ), '%s' ) ); $rows = $wpdb->get_results( $wpdb->prepare( "SELECT hook, MIN(scheduled_date_gmt) AS next_gmt FROM `{$t}` WHERE status IN ('pending','in-progress') AND hook IN ({$ph}) GROUP BY hook", $names ), ARRAY_A ); foreach ( (array) $rows as $r ) { $hook = (string) $r['hook']; if ( ! isset( $hooks[ $hook ] ) ) { continue; } $next = $r['next_gmt'] ? (int) strtotime( $r['next_gmt'] . ' UTC' ) : 0; if ( ! $hooks[ $hook ]['scheduled'] || ( $next && $next < $hooks[ $hook ]['next'] ) ) { $hooks[ $hook ] = array( 'scheduled' => true, 'next' => $next, 'via' => 'action-scheduler' ); } } } return $hooks; } private static function resolve_last_run( array $s, array $jobs ) { if ( $s['last_run_job'] !== '' && isset( $jobs[ $s['last_run_job'] ] ) ) { $v = $jobs[ $s['last_run_job'] ]; if ( is_array( $v ) ) { if ( isset( $v['finished_at'] ) ) { $v = $v['finished_at']; } elseif ( isset( $v['ts'] ) ) { $v = $v['ts']; } elseif ( isset( $v['time'] ) ) { $v = $v['time']; } else { $v = 0; } } return is_numeric( $v ) ? (int) $v : (int) strtotime( (string) $v . ' UTC' ); } if ( $s['last_run_option'] !== '' ) { $v = get_option( $s['last_run_option'], 0 ); if ( is_array( $v ) ) { return 0; } return is_numeric( $v ) ? (int) $v : (int) strtotime( (string) $v . ' UTC' ); } return 0; } private static function code_present( array $s ) { if ( '' === $s['producer_class'] ) { return true; } return class_exists( $s['producer_class'] ) || function_exists( $s['producer_class'] ); } private static function option_filled( $option ) { $v = get_option( $option, '' ); if ( is_array( $v ) ) { return ! empty( array_filter( $v ) ); } return '' !== trim( (string) $v ); } private static function applicability( array $s, $profile ) { $profiles = $s['site_profiles']; if ( 'content' !== $profile ) { return array( 'applicable' => true, 'reason' => '' ); } if ( ! empty( $profiles ) && ! in_array( 'all', $profiles, true ) && ! in_array( $profile, $profiles, true ) ) { return array( 'applicable' => false, 'reason' => sprintf( __( 'not applicable to a %1$s site — this signal measures %2$s sites', 'ai-eshop-optimizer' ), $profile, implode( ' / ', $profiles ) ), ); } return array( 'applicable' => true, 'reason' => '' ); } private static function resolve_state( array $s, array $h ) { if ( isset( $h['applicable'] ) && ! $h['applicable'] ) { return array( 'state' => 'not_applicable', 'reason' => (string) $h['na_reason'] ); } $has_tables = $h['tables_total'] > 0; if ( ! empty( $h['unconfigured'] ) ) { $why = ( '' !== $s['gate_note'] ) ? $s['gate_note'] : $s['gate_option']; return array( 'state' => 'dormant', 'reason' => sprintf( __( 'never configured — %s, so the producer cannot run', 'ai-eshop-optimizer' ), $why ) . self::partial_note( $h ), ); } if ( ! $has_tables ) { if ( ! $h['code_present'] ) { return array( 'state' => 'absent', 'reason' => __( 'producer class not loaded on this site', 'ai-eshop-optimizer' ) ); } if ( ! $s['consumed'] ) { return array( 'state' => 'unconsumed', 'reason' => __( 'computed on demand, but nothing in the plugin calls it', 'ai-eshop-optimizer' ) ); } return array( 'state' => 'healthy', 'reason' => __( 'computed on demand by its callers', 'ai-eshop-optimizer' ) ); } if ( 0 === $h['tables_present'] && empty( $h['all_transient'] ) ) { if ( $h['code_present'] && ! $h['scheduled'] && ! $h['last_run'] ) { return array( 'state' => 'dormant', 'reason' => __( 'producer ships with the plugin but the table has never been created and no cron is scheduled', 'ai-eshop-optimizer' ) . self::partial_note( $h ) ); } return array( 'state' => 'absent', 'reason' => __( 'no storage on this site', 'ai-eshop-optimizer' ) . self::partial_note( $h ) ); } $rows = $h['rows']; if ( null !== $rows && 0 === (int) $rows ) { if ( ! $h['scheduled'] && ! $h['last_run'] ) { return array( 'state' => 'dormant', 'reason' => __( 'table exists but is empty and nothing schedules the producer', 'ai-eshop-optimizer' ) . self::partial_note( $h ) ); } return array( 'state' => 'empty', 'reason' => __( 'producer is wired; no rows yet on this site', 'ai-eshop-optimizer' ) . self::partial_note( $h ) ); } $uncounted = ! empty( $h['uncounted_tables'] ); $view_only = ( null === $rows && ! $uncounted ); $size_skipped = ( null === $rows && $uncounted ); if ( ! $s['consumed'] ) { if ( $view_only ) { $why = __( 'storage is a VIEW (rows deliberately not counted) and nothing in the plugin reads it', 'ai-eshop-optimizer' ); } elseif ( $size_skipped ) { $why = __( 'rows deliberately not counted (large table with a stale estimate) and nothing in the plugin reads it', 'ai-eshop-optimizer' ); } elseif ( '' !== trim( (string) $s['consumers'] ) ) { $why = sprintf( __( 'rows are being produced but nothing consumes them analytically — the only reader is: %s', 'ai-eshop-optimizer' ), $s['consumers'] ); } else { $why = __( 'rows are being produced but nothing in the plugin reads them', 'ai-eshop-optimizer' ); } return array( 'state' => 'unconsumed', 'reason' => $why . self::partial_note( $h ) ); } if ( $view_only ) { return array( 'state' => 'healthy', 'reason' => __( 'read by its consumers; storage is a VIEW, so rows are deliberately not counted', 'ai-eshop-optimizer' ) . self::partial_note( $h ) ); } if ( $size_skipped ) { return array( 'state' => 'healthy', 'reason' => __( 'read by its consumers; the table is too large to count exactly and its row estimate is stale, so no count is shown', 'ai-eshop-optimizer' ) . self::partial_note( $h ) ); } return array( 'state' => 'healthy', 'reason' => __( 'produced and read', 'ai-eshop-optimizer' ) . self::partial_note( $h ) ); } private static function partial_note( array $h ) { $bits = array(); $multi = ( isset( $h['tables_total'] ) && $h['tables_total'] > 1 ); if ( $multi && ! empty( $h['missing_tables'] ) ) { $bits[] = sprintf( __( 'no such table: %s', 'ai-eshop-optimizer' ), implode( ', ', $h['missing_tables'] ) ); } if ( $multi && ! empty( $h['empty_tables'] ) ) { $bits[] = sprintf( __( 'never produced a row: %s', 'ai-eshop-optimizer' ), implode( ', ', $h['empty_tables'] ) ); } if ( ! empty( $h['stale_scratch'] ) ) { $bits[] = sprintf( __( 'left-over working table from an interrupted run (drop it and re-run the stage): %s', 'ai-eshop-optimizer' ), implode( ', ', $h['stale_scratch'] ) ); } if ( ! empty( $h['crons_unscheduled'] ) ) { $bits[] = sprintf( __( 'hook not scheduled: %s', 'ai-eshop-optimizer' ), implode( ', ', $h['crons_unscheduled'] ) ); } if ( ! empty( $h['refused'] ) && is_array( $h['refused'] ) ) { $turned = array(); foreach ( $h['refused'] as $reason => $n ) { $turned[] = $reason . ' ' . number_format_i18n( (int) $n ); } $bits[] = sprintf( __( 'batches refused or not written today: %s', 'ai-eshop-optimizer' ), implode( ', ', $turned ) ); } if ( ! empty( $h['check']['figures'] ) && is_array( $h['check']['figures'] ) ) { $found = array(); foreach ( $h['check']['figures'] as $figure => $n ) { $found[] = $figure . ' ' . number_format_i18n( (int) $n ); } $bits[] = sprintf( __( 'last day checked %1$s: %2$s', 'ai-eshop-optimizer' ), (string) $h['check']['day'], implode( ', ', $found ) ); } return empty( $bits ) ? '' : ' — ' . implode( '; ', $bits ); } private static function check_figures( array $s ) { if ( 'signal_check' !== ( isset( $s['id'] ) ? (string) $s['id'] : '' ) || ! is_callable( array( 'AIEO_Signal_Check', 'latest' ) ) ) { return array(); } return AIEO_Signal_Check::latest(); } const INTAKE_STREAMS = array( 'raw_session_events' => 'track', 'addcart_events' => 'addcart', 'reco_events' => 'reco', ); private static function refused_today( array $s ) { $id = isset( $s['id'] ) ? (string) $s['id'] : ''; if ( ! isset( self::INTAKE_STREAMS[ $id ] ) || ! is_callable( array( 'AIEO_Session_Tracker', 'refusals' ) ) ) { return array(); } $refused = AIEO_Session_Tracker::refusals( self::INTAKE_STREAMS[ $id ] ); arsort( $refused ); return $refused; } public static function state_label( $state ) { switch ( $state ) { case 'healthy': return __( 'healthy', 'ai-eshop-optimizer' ); case 'empty': return __( 'empty', 'ai-eshop-optimizer' ); case 'dormant': return __( 'dormant', 'ai-eshop-optimizer' ); case 'absent': return __( 'absent', 'ai-eshop-optimizer' ); case 'unconsumed': return __( 'unconsumed', 'ai-eshop-optimizer' ); case 'not_applicable': return __( 'n/a here', 'ai-eshop-optimizer' ); } return (string) $state; } public static function state_is_alarming( $state ) { return in_array( $state, array( 'dormant', 'unconsumed' ), true ); } private static function raw() { return array( array( 'id' => 'raw_session_events', 'label' => __( 'Raw session event firehose', 'ai-eshop-optimizer' ), 'measures' => __( 'Every tracked front-end interaction (page_view, product_view, category_view, add_to_cart, remove_from_cart, scroll_depth, click, tab_hidden/visible, page_exit, goal, checkout_timing) with product_id/category_id, time_on_page, scroll_depth.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_sessions', 'aieo_sessions_archive' ), 'producer' => 'AIEO_Session_Tracker (REST intake from assets/js/aieo-session-tracker.js); retention/archival by AIEO_Session_Tracker::archive_sessions, which also fires aieo_archive_run -> AIEO_Archive_Handlers::handle', 'producer_class' => 'AIEO_Session_Tracker', 'cron_hooks' => array( 'aieo_archive_sessions' ), 'grain' => __( 'session (one row per event)', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Aggregate_Cron, AIEO_Conversion_Probability_Model, AIEO_Interaction_Summary, AIEO_Session_Attribution_Bridge, AIEO_Goal_Tracker, AIEO_Landing_Stats::stamp_exits, session-journey sprocs', 'site_profiles' => array( 'all' ), 'notes' => 'timestamp is epoch-MILLISECONDS — comparing it to a DATETIME literal silently matches everything (fixed twice in AIEO_Aggregate_Cron; the pattern is still a trap). HOT table holds only ~3 days; everything older lives in the archive.', ), array( 'id' => 'sessions_statics', 'label' => __( 'Session statics (one row per session)', 'ai-eshop-optimizer' ), 'measures' => __( 'Per-session metadata and totals: device/browser/os, landing_page, referrer, first/last timestamp, duration, total events/product-views/cart-adds/scroll/click, plus entry_channel / entry_source / entry_campaign / entry_term / entry_content, canonical landing_path + utm_*, exit_path, pages_viewed, value_tier, order_id.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_sessions_statics', 'aieo_sessions_statics_archive' ), 'producer' => 'AIEO_Session_Tracker (intake); channel columns by AIEO_Attribution::backfill_session_channels; canonical landing/exit columns by AIEO_Landing_Stats::backfill / ::stamp_exits; order_id by AIEO_Attribution::reconcile_session_from_order', 'producer_class' => 'AIEO_Session_Tracker', 'cron_hooks' => array( 'aieo_attribution_backfill_channels', 'aieo_aggregate_rollups' ), 'grain' => __( 'session', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Order_Facts (channel fallback), AIEO_Landing_Stats, AIEO_Aggregate_Cron::compute_landing_funnel_daily, AIEO_Session_Attribution_Bridge, order_stats analytics job, CAPI / Google-Ads-EC / TikTok senders, AIEO_Chat_Analytics', 'site_profiles' => array( 'all' ), 'notes' => '30-day retention (archive beyond). NO goal_key column here — goals are event rows in aieo_sessions.', ), array( 'id' => 'addcart_events', 'label' => __( 'Add-to-cart event log with source attribution', 'ai-eshop-optimizer' ), 'measures' => __( 'One row per add-to-cart click: product_id/variation_id, quantity, unit_price, source (single_product / archive / sidecart_* / complementary / cart / home / blog_post ...), source_pid, source_meta JSON (archive_term_id, post_id, badge context), cart_position, ts_added/ts_viewed, order_id (backfilled at checkout), ga_sent.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_addcart_events', 'aieo_addcart_events_archive' ), 'producer' => 'AIEO_Attribution::log_event + AJAX aieo_attribution_log (sendBeacon); order_id backfilled by AIEO_Attribution::on_order_processed', 'producer_class' => 'AIEO_Attribution', 'cron_hooks' => array( 'aieo_archive_sessions' ), 'grain' => __( 'product add-to-cart event (session-keyed)', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Aggregate_Cron, AIEO_Conversion_Probability_Model, AIEO_Interaction_Summary, AIEO_Boost_Candidates (intent_mass), AIEO_Landing_Stats, AIEO_Session_Attribution_Bridge, 11 chat abilities, cart-abandonment push trigger, ROI wizard', 'site_profiles' => array( 'shop' ), 'notes' => 'THE canonical demand-source signal. Retention 365 days hot / 730 archive via AIEO_Archive_Handlers::run_for_addcart_events, reached through the action aieo_archive_run which the SCHEDULED aieo_archive_sessions event fires — aieo_archive_run is never in the cron array itself, so do not read its absence as broken retention.', ), array( 'id' => 'reco_events', 'label' => __( 'Recommendation impression / click log', 'ai-eshop-optimizer' ), 'measures' => __( 'One row per reco impression, reco_click, promo_impression or promo_click: target_pid + target_type, anchor_pid + anchor_type or anchor_term_id+tax, surface, position, ts.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_reco_events', 'aieo_reco_events_archive' ), 'producer' => 'AIEO_Attribution (install_reco + the reco client beacon); AIEO_DMM_Promo_Surfaces writes the promo_* variants, AIEO_DMM_Popups the ei_* ones', 'producer_class' => 'AIEO_Attribution', 'cron_hooks' => array( 'aieo_archive_sessions' ), 'grain' => __( 'recommendation impression event', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Aggregate_Cron::compute_reco_daily, AIEO_Interaction_Summary, aieo/get-reco-impressions + get-reco-conversion + dead-archive-detection + top-converting-pairs abilities, ROI wizard', 'site_profiles' => array( 'content', 'shop' ), 'notes' => 'A row carries ONE of anchor_pid or anchor_term_id, never both. NOT products-only since schema v5: the semantic_related surface renders article / guide / listing cards, and renders them ON articles, so target_type and anchor_type carry the post type. An EMPTY string in either means a pre-v5 row — read those as a product recommended on a product page, which is what every pre-v5 writer produced. Retention 30 days hot / 90 archive via AIEO_Archive_Handlers::run_for_reco_events, driven off the aieo_archive_run action that the scheduled aieo_archive_sessions event fires.', ), array( 'id' => 'survey_answers', 'label' => __( 'User Survey answers', 'ai-eshop-optimizer' ), 'measures' => __( 'Every answer to every survey, keyed by blueprint question code rather than by a form plugin field id — so the same question aggregates across the native surveys, a migrated Gravity history, and any adapter added later.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_form_entry_values' ), 'producer' => 'AIEO_Survey_Source_Native::record (live) + AIEO_Survey_Legacy_Migration (one-off) — both through AIEO_Form_Entries', 'producer_class' => 'AIEO_Form_Entries', 'cron_hooks' => array(), 'cron_kind' => 'on_demand', 'owned_column' => 'field_key', 'grain' => __( 'one answer to one question', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Survey_Health (coverage + mapping health), AIEO_Audience_Rules (survey predicates), AIEO_Form_Insights, the voice-of-customer rollups in AIEO_DMM_Surveys', 'site_profiles' => array( 'shop', 'content' ), 'notes' => 'Identity is (surface, external_id): `aieo_` for a native or migrated answer, `_
` for a raw adapter read. `disposition` separates answered / skipped / not_shown, which is what keeps a denominator honest once conditional logic hides a question — collapse them and every percentage moves in the flattering direction. A migrated Gravity history shares the native surface deliberately, so one distribution covers both.', ), array( 'id' => 'conversion_goals', 'label' => __( 'Configurable content conversion goals', 'ai-eshop-optimizer' ), 'measures' => __( 'Operator-chosen conversions (Gravity Forms submissions, newsletter confirmations, LearnDash enrol/lesson-complete, registrations, custom do_action(\'aieo_goal\')) recorded as goal events with a goal_value.', 'ai-eshop-optimizer' ), 'tables' => array(), 'producer' => 'AIEO_Goal_Tracker::record (+ ::goal_value) — writes goal event rows into aieo_sessions', 'producer_class' => 'AIEO_Goal_Tracker', 'cron_hooks' => array(), 'grain' => __( 'session', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Landing_Stats rollups (landing_campaign_daily.goals, exit_daily.converted_sessions), aieo/landing-exit abilities, AIEO_SEO_Write_Queue (goal context in the AI prompt)', 'site_profiles' => array( 'content', 'catalog' ), 'notes' => 'The conversion signal for sites with no cart. Consent-gated: only recorded when the AIEO session cookie exists. CF7/WPForms are deliberately NOT wired.', ), array( 'id' => 'checkout_timing', 'label' => __( 'Checkout deliberation timing', 'ai-eshop-optimizer' ), 'measures' => __( 'Per successful order: active_seconds vs idle_seconds spent in checkout, device_type, has_coupon / coupon_is_offer flags — the input for learning WHEN to fire an exit-intent offer.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_checkout_timing' ), 'producer' => 'AIEO_Session_Tracker::store_checkout_timing (client checkout_timing event on the order-received page), enriched server-side with coupon flags', 'producer_class' => 'AIEO_Session_Tracker', 'cron_hooks' => array(), 'grain' => __( 'order', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_Checkout_Timing admin, AIEO_Proposal_Engine, aieo/behaviour-VoC abilities', 'site_profiles' => array( 'shop' ), 'notes' => 'Coupon-completed orders are tagged so the per-product baseline can exclude them (a fast returning cohort that deflates genuine deliberation time).', ), array( 'id' => 'server_events_log', 'label' => __( 'Server-side conversion event log', 'ai-eshop-optimizer' ), 'measures' => __( 'One row per server-side measurement event dispatched to Meta CAPI / Google Ads Enhanced Conversions / TikTok: event name, payload hash, consent state, response status.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_server_events_log' ), 'producer' => 'includes/server-events/aieo-server-dispatch.php + AIEO_CAPI_Sender and its siblings', 'producer_class' => '', 'cron_hooks' => array(), 'grain' => __( 'session x conversion event', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_Conversions admin (delivery health / dedup inspection)', 'site_profiles' => array( 'all' ), 'notes' => 'Consent is signalled in-band (Meta dpo in the request body), so this log is also the GDPR audit trail for what left the site.', ), array( 'id' => 'outbox', 'label' => __( 'Outbox of the events sent to the ad platforms', 'ai-eshop-optimizer' ), 'measures' => __( 'One row per purchase and platform on its way out of the shop: pending, sending, sent, failed (tried again after a growing wait) or dead, the attempts made, the last answer, and the consent the purchase was made under.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_outbox' ), 'lazy_tables' => array( 'aieo_outbox' ), 'producer' => 'AIEO_Outbox::put(), from each sender\'s maybe_send() while the part "outbox" of the option aieo_signals is on (wp aieo signals on outbox); drained after the response and by the single event aieo_outbox_drain while rows remain', 'producer_class' => 'AIEO_Outbox', 'cron_hooks' => array(), 'grain' => __( 'purchase x platform', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Outbox::drain() (the sends), the Outbox card of this screen, wp aieo outbox status', 'site_profiles' => array( 'shop' ), 'notes' => 'Off until its part is switched on: the senders then keep their list in the request\'s memory, as before. The payload is not stored (built at the send from the order); aieo_server_events_log stays the record of each attempt.', ), array( 'id' => 'signal_check', 'label' => __( 'Completeness check of the signals', 'ai-eshop-optimizer' ), 'measures' => __( 'For each day of the shop, whether the other signals can be trusted: the page views recorded, the visits that lost events on the way and how many they lost, the paid web orders with no visit, the adds to the cart their order did not take and those of products that were not bought, what the intakes refused, the events the browsers gave up, the anonymous sightings, and the purchase events that stopped or still wait.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_signal_counts' ), 'lazy_tables' => array( 'aieo_signal_counts' ), 'producer' => 'AIEO_Signal_Check::run(), daily at 05:10 shop time for the day before by the event aieo_signal_check while the part "check" of the option aieo_signals is on (wp aieo signals on check), and wp aieo signals check [--day=]: the stream "check" of the counts table, set (never added to) by each run', 'producer_class' => 'AIEO_Signal_Check', 'cron_hooks' => array( 'aieo_signal_check' ), 'cron_kind' => ( class_exists( 'AIEO_Signals', false ) && AIEO_Signals::on( 'check' ) ) ? 'recurring' : 'on_demand', 'last_run_option' => ( class_exists( 'AIEO_Signals', false ) && AIEO_Signals::on( 'check' ) ) ? 'aieo_signal_check_ran' : '', 'grain' => __( 'shop day x figure', 'ai-eshop-optimizer' ), 'consumers' => 'the Completeness card of this screen (AIEO_DMM_Signals), wp aieo signals check, this entry\'s health (the last day checked and its figures)', 'site_profiles' => array( 'shop' ), 'notes' => 'Off until its part is switched on. The table also holds the intakes\' own counts (Offers P5.3 and P5.6: the streams track, reco, addcart, intent, facts and sighting), which the check copies by day; m7_pages_served is the owner\'s number of HTML pages served on a day (the measurement M7), entered on the card, which no run writes. page_views_no_gesture waits for the part "arrival".', ), array( 'id' => 'session_journeys', 'label' => __( 'Session journeys (preprocessed sequences)', 'ai-eshop-optimizer' ), 'measures' => __( 'Per (session, product) event with correct sequencing: event_sequence, is_first_event/is_last_event, journey_position, time_on_page (LEAD gap), max_scroll_depth, click_count, did_add_to_cart, add_to_cart_sequence, time_to_cart.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_session_journeys' ), 'producer' => 'AIEO_Session_Analytics_Processor::cron_process_journeys -> sproc wp_AIEO_Build_Session_Journeys (or _Optimized); cleanup via wp_AIEO_Cleanup_Old_Journeys', 'producer_class' => 'AIEO_Session_Analytics_Processor', 'cron_hooks' => array( 'aieo_process_session_journeys', 'aieo_process_session_journeys_continue', 'aieo_cleanup_old_journeys' ), 'grain' => __( 'session x product event', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Aggregate_Cron::compute_engagement_daily (the ONLY source of engagement_daily), DMM Session Analytics viewer', 'site_profiles' => array( 'all' ), 'notes' => 'Unattended runs are gated on aieo_session_tracking_enabled; the admin "Rebuild now" bypasses the gate. Holds only the <=7-day raw window, so it is NOT a 30-day source — that is why engagement_daily exists.', ), array( 'id' => 'engagement_daily', 'label' => __( 'Per-product per-day engagement rollup', 'ai-eshop-optimizer' ), 'measures' => __( 'date x product: views, product_views (pure product_view count), unique_sessions, avg_time_sec, avg_scroll_pct, avg_clicks, cart_adds, unique_cart_sessions, cart_add_rate, landings, last_page, bounces, avg_time_to_cart, PLUS the split dwell measures (v6): occupancies, avg_engaged_sec, terminated_occupancies, avg_away_sec, unterminated_occupancies, parked_occupancies, avg_hidden_sec.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_engagement_daily' ), 'producer' => 'AIEO_Aggregate_Cron::compute_engagement_daily (and ::compute_engagement_daily_range for backfill, over the days the live events still hold whole: DATABASE-RULES rule 7); AIEO_Engagement_Repair rebuilds, from live and archived events, the days the journeys build rewrote smaller before 11.3.5; the occupancy columns come from AIEO_Page_Occupancy::compute_daily, with ::recompute_from_archive for history', 'producer_class' => 'AIEO_Aggregate_Cron', 'cron_hooks' => array( 'aieo_aggregate_rollups' ), 'grain' => __( 'product x day', 'ai-eshop-optimizer' ), 'consumers' => 'sproc AIEO_Aggregate_Product_Metrics_From_Daily (-> product_session_metrics), AIEO_Session_Attribution_Bridge, AIEO_Merchant_Chat, AIEO_Remote_Export_Manager, AIEO_Content_Vitals', 'site_profiles' => array( 'shop' ), 'notes' => '`views` counts ALL journey events; use `product_views` as a conversion-rate denominator. DWELL: read avg_engaged_sec INSTEAD OF avg_time_sec, but ONLY where occupancies > 0 (see the gate below) — avg_time_sec keeps its ORIGINAL meaning (raw LEAD-gap dwell, contaminated by parked tabs: p99.9 69,502s) and is retained only so the two can be compared. GATE EVERY READ OF THE v6 COLUMNS ON `occupancies > 0`: they are written only by AIEO_Page_Occupancy::compute_daily(), which starts with the first nightly run after the v6 migration, so every pre-existing row reads avg_engaged_sec = 0 until AIEO_Page_Occupancy::recompute_from_archive() is run by hand — that backfill is deliberately NOT scheduled, and an ungated AVG() over a 30-day window silently returns near-zero. avg_away_sec IS NOT HESITATION AND MUST NOT BE READ AS DOUBT — it was called that until measurement killed the idea. On every page_exit-terminated occupancy (25,314 of 25,314, 100%) it is ALGEBRAICALLY IDENTICAL to hidden-tab time: page_exit says exactly when the visitor left, so the only unaccounted time inside the occupancy IS off-screen time. It is a backgrounded-tab measure. Worse for the doubt reading, the sign is backwards: splitting intent_mismatch products at median, the HIGH half converts BETTER (cart 0.140 vs 0.112, scroll 57.2% vs 44.6%, bounce 0.696 vs 0.801). Foreground hesitation is UNOBSERVABLE in this event stream — a visitor reading motionless emits nothing — and the operator has ruled out the visibility heartbeat that would fix it, so do not propose one. Weight by terminated_occupancies, NOT by views, since it is undefined wherever nothing terminates the occupancy; read it as tail exposure, not a typical visitor: p50 is 0s but 99.4% of the mass sits in the 3.4% of occupancies over an hour. avg_hidden_sec shares the terminated_occupancies denominator so avg_away_sec - avg_hidden_sec is the on-screen part. Runs 03:00 UTC — one hour before the 04:00 archive truncation. Kept for good (11.3.5; deleted after 730 days before).', ), array( 'id' => 'product_session_metrics', 'label' => __( 'Product session metrics (30-day aggregate + ranks)', 'ai-eshop-optimizer' ), 'measures' => __( 'Per-product 30-day behaviour: total_views, unique_sessions_viewed, avg_time_on_page, avg_scroll_depth, avg_clicks_per_view, times_first/last/only_page, total_add_to_cart, unique_sessions_added_to_cart, view_to_cart_rate, avg_time_to_cart, engagement_score, bounce_rate, and three ranks (view_to_cart_rank / engagement_rank / bounce_rank).', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_product_session_metrics' ), 'producer' => 'AIEO_Session_Analytics_Processor::aggregate_session_metrics -> sproc wp_AIEO_Aggregate_Product_Metrics_From_Daily(30) (falls back to wp_AIEO_Aggregate_Product_Session_Metrics)', 'producer_class' => 'AIEO_Session_Analytics_Processor', 'cron_hooks' => array( 'aieo_aggregate_session_metrics' ), 'grain' => __( 'product', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_SEO_Opportunity_Scorer (its main behavioural input), AIEO_SEO_Pillars, AIEO_SEO_Score, AIEO_DMM_Session_Analytics viewer + XLSX export, sprocs AIEO_Get_Product_Rankings / AIEO_Get_Analytics_Summary', 'site_profiles' => array( 'shop' ), 'notes' => 'Rolls up engagement_daily, NOT journeys — that is what makes the window a true 30 days after the raw firehose is truncated. Grain is whatever product_id the tracker emitted: PARENT product on a PDP, so do not read it as variation-grain.', ), array( 'id' => 'attribution_daily', 'label' => __( 'Per-source cart-add attribution rollup', 'ai-eshop-optimizer' ), 'measures' => __( 'date x source x archive_term: cart_adds, units, sessions, converted_adds, revenue, plus the converting-view engagement (avg_scroll_pct, avg_time_sec, eng_view_n so a range roll-up reproduces the exact pair-average).', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_attribution_daily' ), 'producer' => 'AIEO_Aggregate_Cron::compute_attribution_daily', 'producer_class' => 'AIEO_Aggregate_Cron', 'cron_hooks' => array( 'aieo_aggregate_rollups' ), 'grain' => __( 'site x traffic source x day (with optional archive-term dimension)', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Session_Attribution_Bridge, aieo/archive-conversion-rates ability, AIEO_Remote_Export_Manager', 'site_profiles' => array( 'shop' ), 'notes' => 'source_meta is JSON so grouping happens in PHP (MariaDB JSON portability). eng_view_n is only populated while aieo_sessions still holds the day — a late/backfilled run gets 0 engagement.', ), array( 'id' => 'conversion_buckets', 'label' => __( 'Conversion probability buckets (engagement -> P(cart-add))', 'ai-eshop-optimizer' ), 'measures' => __( 'product x scroll_quartile x time_quartile: viewers, cart_adders, p_cart_add — the empirical probability an engaged visitor adds to cart.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_conversion_buckets' ), 'producer' => 'AIEO_Aggregate_Cron::compute_conversion_buckets -> AIEO_Conversion_Probability_Model::compute_bucket_table for the top-200 viewed products (90-day window)', 'producer_class' => 'AIEO_Aggregate_Cron', 'cron_hooks' => array( 'aieo_aggregate_rollups' ), 'grain' => __( 'product x engagement bucket', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Remote_Export_Manager (portal export) only', 'consumed' => false, 'site_profiles' => array( 'shop' ), 'notes' => 'MATERIALISED SO THE ABILITIES COULD READ IT — AND THEN NOTHING WAS RE-POINTED. aieo/buy-probability-by-engagement and buy-probability-leaderboard still call AIEO_Conversion_Probability_Model LIVE against the 3-day raw firehose, so they see a far shorter window than the 90-day table sitting right here. Throttled weekly via option aieo_aggregate_buckets_last_run.', 'last_run_option' => 'aieo_aggregate_buckets_last_run', ), array( 'id' => 'blog_engagement_daily', 'label' => __( 'Blog post engagement rollup', 'ai-eshop-optimizer' ), 'measures' => __( 'date x post: views, visitors, avg_time_sec, avg_scroll_pct, downstream_cart_adds (adds attributed to source=\'blog_post\' with that post_id).', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_blog_engagement_daily' ), 'producer' => 'AIEO_Aggregate_Cron::compute_blog_engagement_daily', 'producer_class' => 'AIEO_Aggregate_Cron', 'cron_hooks' => array( 'aieo_aggregate_rollups' ), 'grain' => __( 'content (post) x day', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Session_Attribution_Bridge (blog-engagement-leaderboard + blog-to-product-conversion abilities), AIEO_Content_Vitals, AIEO_Remote_Export_Manager', 'site_profiles' => array( 'content', 'shop' ), 'notes' => 'Resolves URL->post_id with url_to_postid() and filters post_type=\'post\' only — pages and CPTs are dropped here; they are covered by content_engagement_daily / content_vitals.', ), array( 'id' => 'cart_removal_daily', 'label' => __( 'Buyer\'s-remorse (cart removal) rollup', 'ai-eshop-optimizer' ), 'measures' => __( 'date x product: adds, removes (adds FOLLOWED by a same-session same-product remove), remove_rate = removes/adds as a true 0..1 ratio.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_cart_removal_daily' ), 'producer' => 'AIEO_Aggregate_Cron::compute_cart_removal_daily', 'producer_class' => 'AIEO_Aggregate_Cron', 'cron_hooks' => array( 'aieo_aggregate_rollups' ), 'grain' => __( 'product x day', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Session_Attribution_Bridge::get_cart_removal_patterns -> aieo/cart-removal-analysis ability, AIEO_Remote_Export_Manager', 'site_profiles' => array( 'shop' ), 'notes' => 'Pairing is bounded to the GMT day, so a cross-midnight add->remove is missed.', ), array( 'id' => 'reco_daily', 'label' => __( 'Recommendation surface performance rollup', 'ai-eshop-optimizer' ), 'measures' => __( 'date x surface x anchor_pid x target_pid x anchor_term: impressions, reco_clicks, distinct sessions.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_reco_daily' ), 'producer' => 'AIEO_Aggregate_Cron::compute_reco_daily', 'producer_class' => 'AIEO_Aggregate_Cron', 'cron_hooks' => array( 'aieo_aggregate_rollups' ), 'grain' => __( 'product pair x surface x day', 'ai-eshop-optimizer' ), 'consumers' => 'aieo/get-reco-conversion, aieo/dead-archive-detection, aieo/top-converting-pairs abilities', 'site_profiles' => array( 'shop' ), 'notes' => 'impressions/reco_clicks SUM exactly across a date range; `sessions` is a per-grain daily distinct so a range SUM over-counts.', ), array( 'id' => 'reco_surface_daily', 'label' => __( 'Recommendation slot performance rollup', 'ai-eshop-optimizer' ), 'measures' => __( 'stat_date x surface x position: impressions, clicks, assisted_adds (distinct reco events followed by a same-session same-product add within 7 days), assisted_purchases.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_reco_surface_daily' ), 'producer' => 'AIEO_Aggregate_Cron::compute_reco_surface_daily', 'producer_class' => 'AIEO_Aggregate_Cron', 'cron_hooks' => array( 'aieo_aggregate_rollups' ), 'grain' => __( 'surface x slot x day', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_Attribution slot-performance card (falls back to the live reco_events join when the rollup does not span the window)', 'site_profiles' => array( 'shop' ), 'notes' => 'Separate from reco_daily on purpose: adding `position` to that grain would multiply its rows and change its three ability consumers. The trailing 7 days are PROVISIONAL — a day\'s assisted_* keep growing until its 7-day assist window closes, so every run recomputes the last 8 days in place. ALL measures are COUNT(DISTINCT ...) and GRAIN-MATCHED PER QUERY (the slot card pairs on r.id; the products and anchors cards pair on e.id, because their assisted_adds is COUNT(DISTINCT e.id)): the LEFT JOIN emits one row per reco event x matching add, so plain SUMs produced more purchases than adds. Mismatching those grains reintroduces the bug in a subtler form. `position = 0` is a REAL bucket, not "unslotted, ignore" — the old position > 0 filter blacked out the archive surface entirely (6,145 impressions, 873 clicks, all unslotted). Surface labels are only as good as the client: before 2026-07-30 `single_product` was pure noise, 11,158 clicks with zero impressions, because the reco observer\'s click walk-up matched the of every product page; bin/post-deploy-reco-phantom-click-purge.php removes those rows and this rollup must be recomputed after it, since ODKU cannot delete a grain whose source events are gone.', ), array( 'id' => 'landing_funnel_daily', 'label' => __( 'Landing-page funnel rollup (raw URL grain)', 'ai-eshop-optimizer' ), 'measures' => __( 'date x landing_page: visitors -> product_viewers -> cart_adders -> cart_adds.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_landing_funnel_daily' ), 'producer' => 'AIEO_Aggregate_Cron::compute_landing_funnel_daily', 'producer_class' => 'AIEO_Aggregate_Cron', 'cron_hooks' => array( 'aieo_aggregate_rollups' ), 'grain' => __( 'session x landing page x day', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Session_Attribution_Bridge::get_addcart_funnel_by_landing_page -> aieo/addcart-funnel ability', 'site_profiles' => array( 'shop' ), 'notes' => 'Groups by the RAW landing URL, so paid traffic fragments one-row-per-click (fbclid/gclid). landing_campaign_daily is the canonical replacement — this one is kept for the funnel shape.', ), array( 'id' => 'landing_campaign_daily', 'label' => __( 'Canonical landing + campaign rollup', 'ai-eshop-optimizer' ), 'measures' => __( 'date x landing_path x utm_source/medium/campaign/id x referrer_host x channel: sessions, cart_adders, cart_adds, goals, engaged_sessions, purchases, revenue.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_landing_campaign_daily' ), 'producer' => 'AIEO_Landing_Stats::compute_daily (last 3 days recomputed each night) + ::backfill + ::maybe_seed (30-day statics seed)', 'producer_class' => 'AIEO_Landing_Stats', 'cron_hooks' => array( 'aieo_aggregate_rollups' ), 'grain' => __( 'session x landing page x campaign x day', 'ai-eshop-optimizer' ), 'consumers' => 'aieo/landing-exit abilities, AIEO_DMM_Attribution admin, AIEO_DMM_Landing_Income', 'site_profiles' => array( 'all' ), 'notes' => 'SAME-SESSION ONLY, dated by the day the session LANDED. Its cross-session twin is entry_credit_daily and the two will never reconcile; that is by design. The goals column is what makes it work on content sites with no cart. PK = (date, MD5 grain_hash of the FULL dimension tuple) — a prefix key would silently merge distinct groups. Reads sessions from HOT UNION ARCHIVE statics: reading hot alone meant a recompute past the hot window rebuilt the day from ~5% of its sessions (measured 2026-07-30: 37-86 hot vs 871-1,518 archived). purchases and revenue come out of ONE query so they share the (session, order) grain, and both exclude AIEO_Landing_Stats::NON_REVENUE_STATUSES — before 2026-07-30 cancelled money counted as income and purchases had no status filter at all. revenue was added to an existing table, so days rolled up before it existed keep 0.00 until bin/post-deploy-landing-revenue-backfill.php restates them; days it cannot restate are listed in the option aieo_landing_restate_gaps.', ), array( 'id' => 'entry_credit_daily', 'label' => __( 'Cross-session entry credit (WooCommerce attribution)', 'ai-eshop-optimizer' ), 'measures' => __( 'date x entry_path (resolved to object_type/object_id): orders, revenue.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_entry_credit_daily' ), 'producer' => 'AIEO_Landing_Stats::compute_entry_credit_daily (last 7 days recomputed each night) + bin/post-deploy-entry-credit-backfill.php for history', 'producer_class' => 'AIEO_Landing_Stats', 'cron_hooks' => array( 'aieo_aggregate_rollups' ), 'grain' => __( 'entry page x day the ORDER was placed', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_Landing_Income ("Credit that arrives later" card)', 'site_profiles' => array( 'woocommerce' ), 'notes' => 'THE COMPANION TO landing_campaign_daily, NOT A BETTER VERSION OF IT. Reads _wc_order_attribution_session_entry, whose sourcebuster cookie persists for months, so an order still credits the page that first brought the customer weeks later. Dated by the ORDER day, where landing_campaign_daily is dated by the LANDING day and counts same-session only. Blog readers essentially never buy in the same visit, so content that works over days scored zero by construction until this existed. The two measures do not reconcile and must never be summed or merged into one column. 7 trailing days, not 3, because an order status settles after it is placed. HPOS only. Entry values are full live-domain URLs with click junk (gclid, _gl, srsltid) still attached, so AIEO_Path_Resolver::normalise collapses them; resolution goes through AIEO_Path_Resolver in ONE bulk pass. Beware classifying an entry URL by its last slug alone: doing so once reported 159 blog-post orders on femme when 141 of them were /product-category/ URLs whose slug collided with a post slug and only 17 were real.', ), array( 'id' => 'exit_daily', 'label' => __( 'Exit-page rollup', 'ai-eshop-optimizer' ), 'measures' => __( 'date x exit_path: sessions, converted_sessions (had a goal), cart_sessions, avg_duration_seconds.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_exit_daily' ), 'producer' => 'AIEO_Landing_Stats::compute_exit_daily; exit_path stamped by ::stamp_exits on SETTLED sessions (quiet >= 6h)', 'producer_class' => 'AIEO_Landing_Stats', 'cron_hooks' => array( 'aieo_aggregate_rollups' ), 'grain' => __( 'session x exit page x day', 'ai-eshop-optimizer' ), 'consumers' => 'aieo/landing-exit abilities', 'site_profiles' => array( 'all' ), 'notes' => 'Exit is derived nightly, never at goal time — the exit page changes over a session\'s life (form submitted, browsing continues).', ), array( 'id' => 'landing_exit_facts_views', 'label' => __( 'Landing / exit facts SQL views', 'ai-eshop-optimizer' ), 'measures' => __( 'Live per-UTC-day per-landing / per-exit facts UNIONed across aieo_sessions_statics and its archive, for wpDataTables + insights.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_landing_facts', 'aieo_exit_facts' ), 'producer' => 'AIEO_Landing_Stats::ensure_view / ::ensure_exit_view', 'producer_class' => 'AIEO_Landing_Stats', 'cron_hooks' => array(), 'grain' => __( 'session x day', 'ai-eshop-optimizer' ), 'consumers' => 'wpDataTables reports (outside the plugin)', 'consumed' => false, 'site_profiles' => array( 'all' ), 'notes' => 'No PHP reader inside the plugin — these exist purely for report builders. A third view, aieo_content_consumption_facts, belongs to the content-vitals estate.', ), array( 'id' => 'content_engagement_daily', 'label' => __( 'Per-content per-day engagement rollup', 'ai-eshop-optimizer' ), 'measures' => __( 'Permanent daily rollup keyed (object_type, object_id, date): views, visitors, avg time, avg scroll PLUS 8 x 3-hour view buckets in site timezone (day-of-week / day-of-month / payroll bands derive from `date` at query time, mirroring the order-side buyerstime/buyerspayrol segmentation).', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_content_engagement_daily' ), 'producer' => 'AIEO_Content_Vitals (built from raw aieo_sessions page_view events BEFORE they archive out of the 3-day hot window)', 'producer_class' => 'AIEO_Content_Vitals', 'cron_hooks' => array( 'aieo_aggregate_rollups' ), 'grain' => __( 'content object x day', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Content_Vitals (-> core_content_vitals), AIEO_DMM_Session_Analytics content twin', 'site_profiles' => array( 'all' ), 'notes' => 'The superset of blog_engagement_daily (which stays for its existing consumers). URL->object resolution goes through aieo_seo_posts.canonical_url (covers KB / custom-router pages) with url_to_postid() as fallback.', ), array( 'id' => 'core_content_vitals', 'label' => __( 'Content vitals (denormalised per content object)', 'ai-eshop-optimizer' ), 'measures' => __( 'One row per public non-product object: engagement aggregates (7d/30d/lifetime), the 30-day temporal profile, visitors_30d, goals_30d, goal_rate_30d, engagement_score and content_demand 0-100 (reach x engagement temper x traffic-recency decay x freshness).', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_core_content_vitals' ), 'producer' => 'AIEO_Content_Vitals — rebuilt daily via atomic table swap (never a TRUNCATE window)', 'producer_class' => 'AIEO_Content_Vitals', 'cron_hooks' => array( 'aieo_aggregate_rollups' ), 'grain' => __( 'content object', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Boost_Candidates (content_demand), AIEO_SEO_Opportunity_Scorer (content grid), AIEO_SEO_Pillars, aieo/content-vitals-leaderboard + social-candidates abilities, AIEO_DMM_Session_Analytics', 'site_profiles' => array( 'all' ), 'notes' => 'The product-vitals architecture applied to content — this is what makes the whole behavioural estate work on catalog/content sites with no cart. Offline compute only; consumers do one indexed read.', ), array( 'id' => 'content_text', 'label' => __( 'Stripped-text read model (per content object)', 'ai-eshop-optimizer' ), 'measures' => __( 'One row per public object (product parents, posts, pages): HTML-stripped, entity-decoded, whitespace-collapsed plain text of post_content, plus md5 text_hash, word_count and source_modified. The single text source for every downstream text pass — semantic term extraction, transport pseudo-documents.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_content_text' ), 'producer' => 'AIEO_Content_Text — delta by (post_modified, ID) watermark + md5 hash guard; a steady-state run touches only content edited since the previous run. PERSISTENT side table BY DESIGN: InsertProductVitals TRUNCATEs and content vitals swap atomically, so in-table text columns would be wiped nightly and force a full re-strip. Stripping is PHP (wp_strip_all_tags + entity decode); the SQL AIEO_StripHTML path is deliberately NOT used (see the AIEO_UpdateContentIntroPlain postmortem: apostrophes in Greek copy broke spliced statements).', 'producer_class' => 'AIEO_Content_Text', 'cron_hooks' => array( 'aieo_dmm_data_prep_run' ), 'grain' => __( 'content object (product = parent grain)', 'ai-eshop-optimizer' ), 'consumers' => 'Semantic engine indexer (Rooster One Pro), semantic transport pseudo-documents; any future text pass should read this table, never wp_posts', 'site_profiles' => array( 'all' ), 'notes' => 'Consumers JOIN by (object_type, object_id) against the vitals read models — one indexed read, zero HTML parsing at consumption time. GC on batch completion removes rows whose source object vanished or unpublished. Reseed via AIEO_Content_Text::reseed() (hash guard still prevents rewrites of unchanged rows).', ), array( 'id' => 'search_demand_daily', 'label' => __( 'On-site search demand (daily rollup)', 'ai-eshop-optimizer' ), 'measures' => __( 'Per-UTC-day, per-provider, per-normalized-phrase counts of on-site searches and zero-result searches — the highest-value demand signal the docs flagged as captured nowhere. phrase_norm folds case/accents via the semantic Normalizer when Rooster One Pro is present; term_stem_key maps phrases onto the semantic term identity space.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_search_demand_daily' ), 'producer' => 'AIEO_Search_Signal — provider architecture: core ?s= capture (is_search main query + found_posts, consent-aware) + FiboSearch dgwt/wcas/analytics/after_searching hook with a wp-content/fibosearch.php shim for Pro\'s SHORTINIT standalone endpoint + a one-time 30-day backfill mirror of wp_dgwt_wcas_stats before its retention destroys it', 'producer_class' => 'AIEO_Search_Signal', 'cron_hooks' => array(), 'grain' => __( 'day x provider x phrase', 'ai-eshop-optimizer' ), 'consumers' => 'semantic content gaps, fusion demand', 'site_profiles' => array( 'all' ), 'notes' => 'Write-on-event (direct daily upsert, no cron); term_stem_key enrichment + phrase_norm renormalization land with the pro-side data-prep pass. Greeklish unification is the point: FiboSearch Pro stores transliterated autocomplete phrases (papoutsia) while search-page rows keep Greek — our normalizer/stem identity groups what FiboSearch\'s own analytics cannot. Shim conflicts (a foreign wp-content/fibosearch.php) are never overwritten — surfaced on the Semantic Internal Linking Diagnostics tab.', ), array( 'id' => 'order_stats', 'label' => __( 'Per-order statistics (the computed order)', 'ai-eshop-optimizer' ), 'measures' => __( 'One row per order: orderspend (gross), totalshipping, netspendonproducts, shippingtype/freeshipping, numberofitemsperorder, numberoforders, customer identity + birthyear/city/postcode, paymenttype, pickuplocation, phoneorder, orderstatus, ordersource + orderutmsource/medium/campaign/term/content, and the local-time buckets orderdate/orderday/orderdaymonth/buyerspayrol/buyersday/buyerstime/buyersdevice.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_order_stats' ), 'producer' => 'sproc wp_AIEO_GenerateOrderStats (analytics job `order_stats`, body in analytics-jobs-procs.php); run by AIEO_DMM_Product_Orders_Analytics::run_job', 'producer_class' => 'AIEO_DMM_Product_Orders_Analytics', 'cron_hooks' => array( 'aieo_analytics_job_run' ), 'grain' => __( 'order', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Order_Facts (the single read-through accessor), AIEO_Marketing_Stats, AIEO_Workflow_Stats, AIEO_Campaign_Forecasts::reconcile, ROI Wizard, UTM Explorer, Competition Analytics', 'site_profiles' => array( 'shop' ), 'notes' => 'TRUNCATE+INSERT rebuild. Date buckets derive from SITE-LOCAL wp_wc_order_stats.date_created (v19) so AIEO matches WooCommerce Analytics, not UTC. Excludes wc-checkout-draft/auto-draft/trash (v16). Paid traffic is labelled paid_google/paid_fb, so `google` = organic only (v21).', 'last_run_job' => 'order_stats', ), array( 'id' => 'order_products_analytics', 'label' => __( 'Per line-item order analytics', 'ai-eshop-optimizer' ), 'measures' => __( 'One row per order line: product_id/variation_id/parent_product_id, effective_pid (STORED generated column), sku, quantity, price, unit_price, order_sequence, order_items, order_total, customer id/email, date_created_gmt, payment_method, brand, category_id + menu/head/sub category, prof_discount, list_price, soft1_id, stock.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_order_products_analytics' ), 'producer' => 'sproc wp_AIEO_GenerateOrderProductsAnalytics (analytics job `order_products_analytics`)', 'producer_class' => 'AIEO_DMM_Product_Orders_Analytics', 'cron_hooks' => array( 'aieo_analytics_job_run' ), 'grain' => __( 'order line item (variation-aware via effective_pid)', 'ai-eshop-optimizer' ), 'consumers' => 'sales predictor (staged + monolithic), physical recovery, price history module, checkout timing module, competition analytics, Pro account tab', 'site_profiles' => array( 'shop' ), 'notes' => 'effective_pid = COALESCE(NULLIF(variation_id,0),product_id) + index idx_opa_effective_pid — added in sproc v24 to kill the ~4.9B-row full x full BNL joins that drove the nightly pipeline into swap. NEVER join on the raw expression.', 'last_run_job' => 'order_products_analytics', ), array( 'id' => 'order_attribution', 'label' => __( 'Per-order marketing attribution', 'ai-eshop-optimizer' ), 'measures' => __( 'One row per order: channel + utm_source/medium/campaign/term/content, and `origin` recording which feed supplied it (aieo | pys | wc_native).', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_order_attribution' ), 'producer' => 'sproc wp_AIEO_GenerateOrderStats — three INSERT IGNORE passes: (1) AIEO session attribution from aieo_sessions_statics, (2) WooCommerce-native _wc_order_attribution_* meta pivoted from wp_wc_orders_meta, (3) legacy PixelYourSite snapshot', 'producer_class' => 'AIEO_DMM_Product_Orders_Analytics', 'cron_hooks' => array( 'aieo_analytics_job_run' ), 'grain' => __( 'order', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Order_Facts::channel, AIEO_DMM_Offer_Attributor', 'site_profiles' => array( 'shop' ), 'notes' => 'order_id is the PK and every pass is INSERT IGNORE, so the FIRST origin to claim an order wins and later feeds can never correct it. On the reference shop the plugin\'s own session attribution covers under 1% of orders.', 'last_run_job' => 'order_stats', ), array( 'id' => 'cumulative_order_stats', 'label' => __( 'Cumulative (per-customer) order statistics', 'ai-eshop-optimizer' ), 'measures' => __( 'Running per-customer totals across their order history (order sequence, cumulative spend/orders) built on top of order_stats.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_cumulative_order_stats' ), 'producer' => 'sproc wp_AIEO_GenerateCumOrderStats (analytics job `cum_order_stats`, depends on order_stats)', 'producer_class' => 'AIEO_DMM_Product_Orders_Analytics', 'cron_hooks' => array( 'aieo_analytics_job_run' ), 'grain' => __( 'customer x order', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_Product_Orders_Analytics UI / wpDataTables reports', 'site_profiles' => array( 'shop' ), 'notes' => '', 'last_run_job' => 'cum_order_stats', ), array( 'id' => 'order_products_behaviours', 'label' => __( 'Order-line entry / exit / single behaviours', 'ai-eshop-optimizer' ), 'measures' => __( 'Which product OPENED a basket (entry), which CLOSED it (exit), and which was bought ALONE (single-item order) — one row per qualifying order line.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_order_products_entry', 'aieo_order_products_exit', 'aieo_order_products_single_orders' ), 'producer' => 'sproc wp_AIEO_GenerateOrderProductsBehaviours (analytics job `order_products_behaviours`, DROP+CREATE AS SELECT)', 'producer_class' => 'AIEO_DMM_Product_Orders_Analytics', 'cron_hooks' => array( 'aieo_analytics_job_run' ), 'grain' => __( 'order line item', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_Product_Orders_Analytics UI / wpDataTables reports', 'site_profiles' => array( 'shop' ), 'notes' => '', 'last_run_job' => 'order_products_behaviours', ), array( 'id' => 'cart_frequencies', 'label' => __( 'Product cart frequencies (first / last / single choice)', 'ai-eshop-optimizer' ), 'measures' => __( 'Per product: how often it is the FIRST thing added, the LAST thing added, or the ONLY thing in the order — the merchandising "basket opener vs closer" signal.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_order_products_freq_entry', 'aieo_order_products_freq_exit', 'aieo_order_products_freq_single_item_orders' ), 'producer' => 'sproc wp_AIEO_GenerateOrderProductsCartFrequencies (analytics job `cart_frequencies`)', 'producer_class' => 'AIEO_DMM_Product_Orders_Analytics', 'cron_hooks' => array( 'aieo_analytics_job_run' ), 'grain' => __( 'product', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_Product_Orders_Analytics UI / wpDataTables reports', 'site_profiles' => array( 'shop' ), 'notes' => 'link_to_view resolves the product CANONICAL via aieo_seo_posts (v15).', 'last_run_job' => 'cart_frequencies', ), array( 'id' => 'invoice_match_report', 'label' => __( 'Woo <-> ERP invoice reconciliation', 'ai-eshop-optimizer' ), 'measures' => __( 'Per-order match/mismatch between the WooCommerce order total and the ERP (SoftOne) invoice for the previous and current month.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_order_invoice_match_report', 'aieo_order_invoice_match_report_current_month' ), 'producer' => 'sprocs wp_AIEO_GenerateInvoiceMatchPrevMonth / wp_AIEO_GenerateInvoiceMatchCurrentMonth (analytics jobs `invoice_match_prev_month` / `invoice_match_curr_month`)', 'producer_class' => 'AIEO_DMM_Product_Orders_Analytics', 'cron_hooks' => array( 'aieo_analytics_job_run' ), 'grain' => __( 'order', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_Product_Orders_Analytics UI', 'site_profiles' => array( 'shop' ), 'notes' => 'curr_month gained the ANALYZE-trio + latest-import-day dedup only at sproc v25 — before that it double-counted daily-re-imported ERP orders. These two jobs go stale independently of the rest of the family; check their last-run dates against order_stats.', 'last_run_job' => 'invoice_match_curr_month', ), array( 'id' => 'monthly_order_snapshots', 'label' => __( 'Monthly denormalised order snapshots', 'ai-eshop-optimizer' ), 'measures' => __( 'The wide per-line order snapshot the whole customer estate reads: OrderId/OrderStatus/OrderDate, EponymousID, CustomerEmail, ProductId/ParentProductId/SKU, BrandId, MenuId/HeadId/SubcatId, LineValue, Quantity, Price, OrderSize, ItemSequence, OrderItemsTotal, LastTransactionDate, plus the pre-computed customer scalars TotalCustomerOrders / TotalCustomerTurnover / AvgSpend / AvgDBO / CurrentDBO / MaxDBO / MinDBO / SDDBO.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_temp_orders', 'aieo_temp_store_orders' ), 'producer' => 'AIEO::aieo_handle_export_orders (Data Prep -> "Save Settings & Analyze Data") calling wp_AIEO_Export_Orders_HPOS / _non_HPOS, wp_AIEO_Export_Store_Orders, wp_AIEO_Merge_Store_Orders, then the in-place enrichment sprocs wp_AIEO_UpdateProductCentricStats, wp_AIEO_UpdateCustomerCentricStdStats, wp_AIEO_UpdateCustomerCentricAdvStats, wp_AIEO_GenerateGraphDBUUIDs/Freqs, wp_AIEO_UpdateOrdersWithUnsold', 'producer_class' => '', 'cron_hooks' => array(), 'grain' => __( 'order line item (eshop + in-store merged; identity = EponymousID)', 'ai-eshop-optimizer' ), 'consumers' => 'aieo_sp_recompute_customer_intelligence_batch (the CDP), AIEO_Customer_Purchases::rebuild, AIEO_Product_Affinity, AIEO_DMM_Surveys, AI_eShop_Chat_Embedding_Manager, AIEO_DMM_Product_Needs', 'site_profiles' => array( 'shop' ), 'notes' => 'THE OMNICHANNEL SPINE OF THE CUSTOMER ESTATE — AND NOTHING SCHEDULES IT. Operator-triggered admin action only; the monthly archive name is minted from gmdate(\'Y_m\') into aieo_YYYY_MM_orders (not listed here because the name is dynamic). Product-centric stats (TotalItemSales, DistinctOrderSales, TotalTurnover, UniqueItemSales, FirstItemSales, LastItemSales, AvgJLW, AvgVLW) are written as COLUMNS ON THIS TABLE, not to a separate table. Everything downstream — the CDP, audiences, affinity — is only as fresh as the last manual Data-Prep export.', ), array( 'id' => 'customer_centric_stats', 'label' => __( 'Customer-centric standard + advanced statistics', 'ai-eshop-optimizer' ), 'measures' => __( 'Per-order-per-customer cumulative statistics (OrderSpend, OrderSize, NumberOfOrders, inter-order gaps) materialised into a dedicated cumulative table, plus the row-numbered self-join staging the advanced pass uses.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_base_order_stats', 'aieo_adv_stats_orders_rn', 'aieo_adv_stats_orders_rn_copy' ), 'transient_tables' => array( 'aieo_base_order_stats', 'aieo_adv_stats_orders_rn', 'aieo_adv_stats_orders_rn_copy' ), 'gate_option' => 'aieo_include_aggregate_cust_std_stats', 'gate_note' => 'the customer standard/advanced statistics stages are switched off in Data Prep (options aieo_include_aggregate_cust_std_stats / _adv_stats, both default 0)', 'producer' => 'sprocs wp_AIEO_UpdateCustomerCentricStdStats / wp_AIEO_UpdateCustomerCentricAdvStats (includes/sql/sprocs.php), called from AIEO::aieo_handle_export_orders', 'producer_class' => '', 'cron_hooks' => array(), 'grain' => __( 'customer x order', 'ai-eshop-optimizer' ), 'consumers' => 'push-sprocs.php (MAX(AvgDBO) -> wp_aieo_customer_intelligence.replenishment_json), AIEO_DMM_Web_Push (replenisher/overdue segments), AIEO_Push_Trigger_Replenishment, AIEO_Customer_Profile, the hub/portal export', 'consumed' => true, 'site_profiles' => array( 'shop' ), 'notes' => 'CORRECTED 2026-07-26 — the previous note said "the pass has never completed", which was exactly backwards: all three tables are run-scoped scratch that the pass DELETES when it finishes, so absence is the sign of success. Two tables were also removed from this entry entirely: aieo_cumulative_customer_stats and aieo_customer_stats_progress are produced only by the _v2 and legacy StdStats procedure variants, and the installer wires up _Smart alone (ai-eshop-optimizer.php) — _Smart explicitly skips the cumulative table ("Skipped for now to keep it simple"). Progress is tracked in the aieo_customer_std_stats_* transients instead. They were ghosts of an architecture that was replaced. CORRECTED AGAIN, SAME DAY: an earlier version of this note claimed "the pass has NO READER". That was wrong, and it is the third hand-written claim in this file to be falsified in one session. The DBO columns it writes back onto wp_aieo_temp_orders are consumed: push-schema.php:129 maps AvgDBO/CurrentDBO/MaxDBO/MinDBO/SDDBO into wp_aieo_customer_intelligence.replenishment_json, which is what the `replenisher` and `overdue` segments and the replenishment push trigger select on; the hub/portal export reads them too (class-eshop-optimizer.php:6120, :6285). This pass is not unread — it is LOAD-BEARING. 🚨 AND THE NUMBER IS WRONG: the advanced pass computes days-between-orders at LINE grain instead of ORDER grain, so every order is counted once per line. Decisive invariant — a customer with exactly 2 orders has exactly ONE gap, so AvgDBO must equal MaxDBO; measured on the reference bed across 21,306 such customers the ratio is 0.2354, against a mean of 3.52 lines per order (1/3.52 = 0.284). Cadence is therefore roughly a quarter of truth, which is consistent with 1,111 "replenishers" carrying a mean recency of 670 days. FIX = group the rn CTAS by (EponymousId, OrderId) before the ROW_NUMBER, and add the paid-status filter; see the audit note in docs/. Do NOT wire new consumers onto these columns until that lands.', ), array( 'id' => 'order_facts_accessor', 'label' => __( 'Order facts read-through accessor', 'ai-eshop-optimizer' ), 'measures' => __( 'The one API for per-order gross / shipping / net revenue and channel + utm_source/medium/campaign, with per-request L1 memoisation and a sessions_statics channel fallback.', 'ai-eshop-optimizer' ), 'tables' => array(), 'producer' => 'AIEO_Order_Facts::get / ::revenue / ::channel / ::get_batch (read-only; writes nothing)', 'producer_class' => 'AIEO_Order_Facts', 'cron_hooks' => array(), 'grain' => __( 'order', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Order_Origin_Email, AIEO_DMM_Offer_Attributor', 'site_profiles' => array( 'shop' ), 'notes' => 'Documented in documentation/ORDER_DATA_MAP.md as the thing to call instead of re-deriving revenue. Only two callers today — most order-revenue code still hits aieo_order_stats directly. Reads aieo_order_stats + aieo_order_attribution (+ aieo_sessions_statics fallback).', ), array( 'id' => 'order_touchpoints', 'label' => __( 'Multi-touch order path attribution', 'ai-eshop-optimizer' ), 'measures' => __( 'Reconstructs each paid order\'s PATH of engaged owned messages (email/push clicks and opens) in the window before purchase and distributes revenue under last_click / linear / time_decay / position models, returning per-campaign credited revenue.', 'ai-eshop-optimizer' ), 'tables' => array(), 'producer' => 'AIEO_Order_Touchpoints::attribute($window_days, $model, $lookback_days) — computed on demand from aieo_push_dispatch + WC orders (HPOS or classic)', 'producer_class' => 'AIEO_Order_Touchpoints', 'cron_hooks' => array(), 'grain' => __( 'order x touchpoint', 'ai-eshop-optimizer' ), 'consumers' => 'aieo/multi-touch-attribution ability (class-workflow-abilities.php)', 'site_profiles' => array( 'shop' ), 'notes' => 'Hard cap MAX_ROWS=200000 (order,touch) pairs buffered into PHP. Read-only, nothing persisted, so results are recomputed every call.', ), array( 'id' => 'attribution_cost', 'label' => __( 'Ad-spend cost ledger', 'ai-eshop-optimizer' ), 'measures' => __( 'Per-period ad spend by dimension (channel | campaign | creative) from manual entry, CSV import or the Google Ads / Meta / TikTok connectors — the denominator that turns Rooster One revenue into ROAS/ROI.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_attribution_cost' ), 'producer' => 'AIEO_Attribution_Cost::maybe_install + upserts from AIEO_DMM_Ad_Connectors and the ROI Wizard CSV import', 'producer_class' => 'AIEO_Attribution_Cost', 'cron_hooks' => array( 'aieo_ad_connectors_sync' ), 'grain' => __( 'site x channel/campaign/creative x period', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Marketing_Stats (ad_campaign spend), AIEO_DMM_ROI_Wizard, AIEO_DMM_Campaigns_Admin', 'site_profiles' => array( 'shop' ), 'notes' => 'Offer ROI deliberately bypasses this ledger (offers carry their own actual_cost).', ), array( 'id' => 'offer_attributions', 'label' => __( 'Offer / promo surface attribution', 'ai-eshop-optimizer' ), 'measures' => __( 'Which promo surface, exit-intent offer or badge an order can be traced back to, with the offer id, surface and the resolved order value.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_dmm_offer_attributions', 'aieo_dmm_offers' ), 'producer' => 'AIEO_DMM_Offer_Attributor (order hooks) + AIEO_DMM_Promo_Surfaces / AIEO_DMM_Popups impression writers; AIEO_Session_Tracker links the session', 'producer_class' => 'AIEO_DMM_Attribution', 'cron_hooks' => array(), 'grain' => __( 'order x offer', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_Attribution admin, AIEO_DMM_Tools offer-effectiveness report + XLSX export, ROI Wizard', 'site_profiles' => array( 'shop' ), 'notes' => 'Reads revenue through AIEO_Order_Facts rather than re-deriving it — the reference implementation of the accessor contract. Each row measures its campaign twice, as a competition is measured: its own products (target_units, target_revenue: the lines the offer targets) and the whole order (order_total). A free-shipping row is written only for the rule that freed the order (the shipping line\'s _aieo_fs_rule).', ), array( 'id' => 'sales_predictor', 'label' => __( 'Sales / out-of-stock predictor snapshot', 'ai-eshop-optimizer' ), 'measures' => __( '~95 columns per product per day: stock + out_of_stock_days/repetitions, price track, units and turnover for last/next 7/14/28/56/175 days, items-per-day and turnover-per-day for each window, prior-year availability flags, linear + EWMA + optimistic/pessimistic/average estimators, days_until_stock_run_out, estimated_zero_stock_date, reorder_date + reorder_quantity, chosen_method/chosen_ipd (self-tuned), prediction_confidence, reliability_score, variation_share_of_parent, uses_parent_signal.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_sales_predictor' ), 'producer' => 'sproc wp_AIEO_GenerateSalesPredictor (monolithic, cron/CLI path) OR the staged trio wp_AIEO_SP_Prepare -> wp_AIEO_SP_Batch -> wp_AIEO_SP_Finalize (admin "Run now")', 'producer_class' => 'AIEO_DMM_Product_Orders_Analytics', 'cron_hooks' => array( 'aieo_analytics_job_run' ), 'grain' => __( 'product AND variation — both grains coexist, keyed UNIQUE(grain, product_id, prediction_date)', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Boost_Candidates (forecast_units component), aieo/restock-forecast ability, AIEO_SEO_Pillars, AIEO_SEO_Score, AIEO_DMM_Product_Orders_Analytics UI', 'site_profiles' => array( 'shop' ), 'notes' => 'FAILURE MODE TO CHECK FIRST: SP_Prepare TRUNCATEs the snapshot before batching, so an INTERRUPTED staged run leaves the table empty and every downstream consumer with no forecast at all — while aieo_analytics_jobs_last_run still reports the older successful run. Cross-check aieo_sp_run.status; "batching" with an empty snapshot means interrupted, not never-started. Options aieo_sales_predictor_grain/_reference/_staleness default to grain=both when unset.', 'last_run_job' => 'sales_predictor', ), array( 'id' => 'sales_predictor_history', 'label' => __( 'Append-only predictor snapshot log', 'ai-eshop-optimizer' ), 'measures' => __( 'Per (grain, product, snapshot_date): current_stock, price, ipd_linear, ipd_ewma, ipd_average, chosen_ipd, chosen_method, est_zero_stock_date — the backtest log and the "yesterday" source.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_sales_predictor_history' ), 'producer' => 'appended by wp_AIEO_SP_Finalize / wp_AIEO_GenerateSalesPredictor', 'producer_class' => 'AIEO_DMM_Product_Orders_Analytics', 'cron_hooks' => array( 'aieo_analytics_job_run' ), 'grain' => __( 'product/variation x day', 'ai-eshop-optimizer' ), 'consumers' => 'the predictor\'s own backtest pass (feeds sales_predictor_accuracy), and the feature pass that derives price_update_repetitions / out_of_stock_repetitions — wp_AIEO_SP_Prepare + wp_AIEO_GenerateSalesPredictor SELECT this table by name', 'consumed' => true, 'site_profiles' => array( 'shop' ), 'notes' => 'No UI and no ability reads it — its only readers are the predictor sprocs themselves. Its newest snapshot_date is the honest answer to "when did forecasting last actually work": it stops exactly where the predictor stopped succeeding, which the job-runner\'s last_run does not tell you.', 'last_run_job' => 'sales_predictor', ), array( 'id' => 'sales_predictor_accuracy', 'label' => __( 'Predictor self-tuning accuracy scoreboard', 'ai-eshop-optimizer' ), 'measures' => __( 'Per (grain, segment_type, segment_key, method): n_samples, MAPE, bias, ewma_abs_err — the rolling scoreboard that picks chosen_method per product.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_sales_predictor_accuracy' ), 'producer' => 'backtest block inside wp_AIEO_SP_Prepare / wp_AIEO_GenerateSalesPredictor (28-day horizon, acc_alpha 0.30)', 'producer_class' => 'AIEO_DMM_Product_Orders_Analytics', 'cron_hooks' => array( 'aieo_analytics_job_run' ), 'grain' => __( 'product segment (global | category) x method', 'ai-eshop-optimizer' ), 'consumers' => 'the predictor itself — wp_AIEO_SP_Prepare / wp_AIEO_GenerateSalesPredictor SELECT it grouped by segment to pick chosen_method per product', 'consumed' => true, 'site_profiles' => array( 'shop' ), 'notes' => 'No UI and no ability reads it, yet it is the only measurement of whether the forecast is any good. On the reference shop per-CATEGORY linear MAPE (36.0) is far better than global (53-55), which is evidence the segmenting works and is under-used.', 'last_run_job' => 'sales_predictor', ), array( 'id' => 'predictor_staging', 'label' => __( 'Predictor staging + run control', 'ai-eshop-optimizer' ), 'measures' => __( 'Persistent staging the staged predictor needs across HTTP requests: sp_vit (vitals snapshot), sp_pstock (parent stock sums), sp_prev, sp_pw, sp_queue (batched work list), and sp_run (asof / grain / next_batch / total_batches / status).', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_sp_vit', 'aieo_sp_pstock', 'aieo_sp_prev', 'aieo_sp_pw', 'aieo_sp_queue', 'aieo_sp_run' ), 'producer' => 'wp_AIEO_SP_Prepare (creates), wp_AIEO_SP_Batch (consumes), wp_AIEO_SP_Finalize (drops)', 'producer_class' => 'AIEO_DMM_Product_Orders_Analytics', 'cron_hooks' => array(), 'grain' => __( 'product', 'ai-eshop-optimizer' ), 'consumers' => 'the staged predictor only', 'site_profiles' => array( 'shop' ), 'notes' => 'These are the monolithic proc\'s TEMPORARY tables made persistent because connection-scoped temps cannot survive between AJAX requests. Their PRESENCE is the diagnostic: if they exist with sp_run.status=\'batching\', a run was interrupted — not that it never started.', ), array( 'id' => 'customer_intelligence_cdp', 'label' => __( 'Customer Intelligence CDP (eponymous customer profile)', 'ai-eshop-optimizer' ), 'measures' => __( 'One row per EponymousID: first/last_order_at, orders_count, ltv_total, aov, recency_days, RFM bands + rfm_cell, discount_share, avg_item_price, brand_affinity_json + top_brand_id/share, category_affinity_json + top_category_id/share, replenishment_json (AvgDBO/CurrentDBO/Max/Min/SD), price_band, segments_json, dormancy_cohort, reactivation_score + attempt tracking, sole_order_share, top_sole/first/last_product_id, avg_jlw, avg_vlw, core_needs_json, newsletter linkage (tnp_*), lifetime + 30d email engagement (sent/opened/clicked, open_rate, click_rate, days_since_last_engagement), inferred_priorities_*.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_customer_intelligence' ), 'producer' => 'sproc aieo_sp_recompute_customer_intelligence_batch(source_table, offset, batch) looped by AIEO_DMM_Web_Push::run_recompute, then sproc aieo_sp_assign_segments for RFM bands + dormancy + reactivation_score', 'producer_class' => 'AIEO_DMM_Web_Push', 'cron_hooks' => array( 'aieo_push_nightly_recompute' ), 'grain' => __( 'customer (EponymousID)', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Audience_Rules, AIEO_Customer_Profile, AIEO_Subject_Resolver, AIEO_Workflow_Runtime, AIEO_Customer_Intelligence_Query, AIEO_Email_Channel, AIEO_Newsletter_Digest/Registry, AIEO_Push_Dispatcher + win-back / replenishment / loved-brand triggers, Competitions + Events identity bridge, DMM Surveys, aieo/behaviour-VoC + get-customer-profile + recommend-products-for-customer abilities', 'site_profiles' => array( 'shop' ), 'notes' => 'Its INPUT is the OPERATOR-TRIGGERED monthly order snapshot family, so the CDP is only ever as fresh as the last manual Data-Prep export — a nightly recompute over a stale snapshot looks healthy and is not. taste_vector is reserved and unwritten.', ), array( 'id' => 'customer_purchases', 'label' => __( 'Lifetime "ever bought X" rollup', 'ai-eshop-optimizer' ), 'measures' => __( 'Per (email_hash, kind, ref_id): first_at, last_at, times — the full set of PRODUCTS, BRANDS, CATEGORIES, TAGS and NEEDS a customer has ever purchased, so audience rules are an indexed EXISTS instead of an order-history scan.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_customer_purchases' ), 'producer' => 'AIEO_Customer_Purchases::rebuild (TRUNCATE+INSERT) over ::source_tables() = aieo_temp_orders + every aieo_YYYY_MM_orders archive', 'producer_class' => 'AIEO_Customer_Purchases', 'cron_hooks' => array( 'aieo_customer_purchases_rollup' ), 'grain' => __( 'customer x purchased entity', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Audience_Rules (ever_purchased_* fields), AIEO_Product_Affinity (source_tables + PURCHASE_STATUSES)', 'site_profiles' => array( 'shop' ), 'notes' => 'Paid states only: PURCHASE_STATUSES = wc-completed, wc-processing, erp-completed (in-store buyers included; erp-returned excluded). product kind records BOTH parent and variation id so a rule on either matches. tag/need are resolved by taxonomy join (filters aieo_purchases_tag_taxonomy / aieo_purchases_needs_taxonomy), not from snapshot columns. Action Scheduler group \'aieo\' is preferred over WP-cron.', ), array( 'id' => 'identity_substrate', 'min_rows' => array( 'of' => 'customer_intelligence_cdp', 'ratio' => 0.25 ), 'label' => __( 'Subject identity substrate', 'ai-eshop-optimizer' ), 'measures' => __( 'Gives every visitor a stable subject_id and stitches visitor_key (aieo_attr_sid cookie) <-> session <-> wp_user <-> email_hash <-> eponymous_id, with merge tombstones.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_subjects', 'aieo_session_identity' ), 'producer' => 'AIEO_Session_Tracker writes one INSERT IGNORE per new session (resolved_via=\'pending\'); AIEO_Subject_Resolver walks pending rows hourly from a high-water cursor and stamps subject/user/email/eponymous', 'producer_class' => 'AIEO_Subject_Resolver', 'cron_hooks' => array( 'aieo_resolve_subjects_tick' ), 'grain' => __( 'customer (subject) / session', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Interaction_Summary, AIEO_Behavioural_Vector, AIEO_Customer_Profile', 'site_profiles' => array( 'all' ), 'notes' => 'STARVATION MODE: on the reference bed the resolver cron fires happily but the substrate holds a handful of rows against 50k sessions — the tracker\'s per-session INSERT only lands for cookie-bearing sessions AFTER the consent gate. A healthy cron here does not imply a healthy substrate; compare row counts against sessions_statics.', ), array( 'id' => 'interaction_summary', 'min_rows' => array( 'of' => 'customer_intelligence_cdp', 'ratio' => 0.10 ), 'label' => __( 'Person <-> item interaction graph', 'ai-eshop-optimizer' ), 'measures' => __( 'Per (subject, item, item_type): views, carts, removes, wishlists, conversions, sale_ctx, accumulated undecayed `weight`, first_at/last_at. Weights: view = dwell x scroll multiplier [1,3], reco-click 0.5, wishlist +3, cart +5, conversion +12, remove -2.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_interaction_summary' ), 'producer' => 'AIEO_Interaction_Summary::tick — set-based incremental pulls from aieo_sessions, aieo_session_journeys, aieo_reco_events and aieo_addcart_events, joined to the identity substrate', 'producer_class' => 'AIEO_Interaction_Summary', 'cron_hooks' => array( 'aieo_interaction_summary_tick' ), 'grain' => __( 'customer x item (product | post | page)', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Behavioural_Vector (vector build), AIEO_Behavioural_Reco, AIEO_Audience_Rules, AIEO_Customer_Profile', 'site_profiles' => array( 'all' ), 'notes' => 'item_type is generic so it serves content sites too. Per-source high-water cursors only advance when the batch INSERT succeeds; conversions ride a SEPARATE keyset cursor on addcart.backfilled_at because a purchase can land days after the add. Decay is applied at vector-build time, not stored. Downstream of the identity substrate — starves with it.', ), array( 'id' => 'subject_recos', 'label' => __( 'Precomputed per-subject behavioural recommendations', 'ai-eshop-optimizer' ), 'measures' => __( 'Top-N (24) items per subject with reco_rank and cosine score, so digest/cascade serving is an indexed read instead of a per-recipient brute-force cosine.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_subject_recos' ), 'producer' => 'AIEO_Behavioural_Vector::tick — builds the subject vector into aieo_chat_embeddings (post_type=\'user\') from interaction_summary, then one streaming pass over the item-vector pool', 'producer_class' => 'AIEO_Behavioural_Vector', 'cron_hooks' => array( 'aieo_behavioural_vector_tick' ), 'grain' => __( 'customer', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Behavioural_Reco -> AIEO_Workflow_Runtime, AIEO_Merge_Tags, AIEO_Audience_Rules, AIEO_Customer_Profile', 'site_profiles' => array( 'all' ), 'notes' => 'BATCH=50 subjects/tick, ACTIVE_DAYS=90, DECAY_H=30 days half-life, POOL_CAP=10000 item vectors. Downstream of the identity substrate — starves with it.', ), array( 'id' => 'customer_profile_service', 'label' => __( 'Customer profile / persona dossier', 'ai-eshop-optimizer' ), 'measures' => __( 'Resolves a name or email to a customer and assembles every readable per-customer signal (CDP + user_vitals + interaction_summary + subject_recos + lookalikes/instigators) into a confidence-scored persona; optionally narrates it via Claude.', 'ai-eshop-optimizer' ), 'tables' => array(), 'producer' => 'AIEO_Customer_Profile::resolve / ::assemble / ::narrate (read-only)', 'producer_class' => 'AIEO_Customer_Profile', 'cron_hooks' => array(), 'grain' => __( 'customer', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_Customer_Profile admin page', 'site_profiles' => array( 'shop' ), 'notes' => 'PII contract: the operator sees name/email; the Claude narrative receives ONLY the pseudonymised signal bundle. Assembly is free; narrate() is the metered call.', ), array( 'id' => 'customer_intelligence_query', 'label' => __( 'CDP query facade', 'ai-eshop-optimizer' ), 'measures' => __( 'Read API over the CDP for per-customer engagement signals (segments, affinities, recency) used by serving surfaces.', 'ai-eshop-optimizer' ), 'tables' => array(), 'producer' => 'AIEO_Customer_Intelligence_Query (CI_TABLE const) — read-only over aieo_customer_intelligence', 'producer_class' => 'AIEO_Customer_Intelligence_Query', 'cron_hooks' => array(), 'grain' => __( 'customer', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Core_Brand_Vitals, AIEO_DMM_Navigator, brand-related block render, aieo/get-customer-profile + recommend-products-for-customer abilities', 'site_profiles' => array( 'shop' ), 'notes' => '', ), array( 'id' => 'survey_intelligence', 'label' => __( 'Voice-of-Customer survey intelligence', 'ai-eshop-optimizer' ), 'measures' => __( 'Per eponymous customer: the latest F18 + F20 survey responses distilled into 7 metrics plus a composite VoC score 0-100.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_survey_intelligence', 'aieo_survey_responses' ), 'producer' => 'sprocs aieo_sp_ingest_survey_responses (GF -> aieo_survey_responses) then aieo_sp_extract_survey_intelligence; driven by AIEO_DMM_Surveys', 'producer_class' => 'AIEO_DMM_Surveys', 'cron_hooks' => array( 'aieo_surveys_nightly_ingest', 'aieo_surveys_taste_vectors_tick' ), 'grain' => __( 'customer', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Audience_Rules, AIEO_Proposal_Engine, aieo/behaviour-VoC abilities, AIEO_DMM_Surveys admin', 'site_profiles' => array( 'shop' ), 'notes' => 'The only place Gravity Forms tables are touched is ingest. AIEO_Boost_Candidates DELIBERATELY excludes VoC as a component and exposes it shop-level instead (see its get_voc_pulse note).', ), array( 'id' => 'user_vitals', 'label' => __( 'ERP/SOFT1 user vitals', 'ai-eshop-optimizer' ), 'measures' => __( 'Per WP user: soft1_customer_name/id/ids, soft1_loyalty_id, soft1_turnover, soft1_loyalty_points and the rest of the ERP customer extension, typed and indexed so user data can be correlated with order/product analytics.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_user_vitals' ), 'producer' => 'aieo_install_user_vitals_schema + AIEO_DMM_User_Vitals (import from the ERP feed; lifted out of the legacy Pods wp_pods_user)', 'producer_class' => 'AIEO_DMM_User_Vitals', 'cron_hooks' => array(), 'grain' => __( 'customer (wp_user_id)', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Customer_Profile, AIEO_Loyalty_Connection, AIEO_DMM_User_Vitals admin', 'site_profiles' => array( 'shop' ), 'notes' => 'NO SCHEDULED REFRESH EXISTS — the table is only as current as the last manual ERP import.', ), array( 'id' => 'user_behavior_metrics', 'label' => __( 'Per-user cancellation / refund risk', 'ai-eshop-optimizer' ), 'measures' => __( 'Per user: total_orders, total_cancellations, total_refund_lines, cancellation_rate, refund_rate, last_cancelled_at, and the derived risk_band (0 normal / 1 elevated / 2 high) from rate + recency.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_dmm_user_behavior_metrics' ), 'producer' => 'AIEO_DMM_User_Behavior_Metrics::recompute (synchronous on AIEO_DMM_Order_Cancellations capture hooks) + ::recompute_stale from cron', 'producer_class' => 'AIEO_DMM_User_Behavior_Metrics', 'cron_hooks' => array( 'aieo_dmm_metrics_daily_recompute' ), 'grain' => __( 'customer', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_Cancellations admin; theme/checkout surfaces via the public getter (COD restriction, warning prompts)', 'site_profiles' => array( 'shop' ), 'notes' => 'Object-cache fronted (group aieo_dmm, TTL 1h).', ), array( 'id' => 'product_behavior_metrics', 'label' => __( 'Per-product cancellation / refund penalty', 'ai-eshop-optimizer' ), 'measures' => __( 'Per product: total_orders, total_cancellations, total_refund_lines, cancellation_rate, refund_rate, last_cancelled_at, top_reason_slug — the reco engine\'s penalty signal.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_dmm_product_behavior_metrics', 'aieo_dmm_order_cancellations', 'aieo_dmm_order_line_refunds' ), 'producer' => 'AIEO_DMM_Product_Behavior_Metrics::recompute / ::recompute_stale from AIEO_DMM_Order_Cancellations capture hooks', 'producer_class' => 'AIEO_DMM_Product_Behavior_Metrics', 'cron_hooks' => array( 'aieo_dmm_metrics_daily_recompute' ), 'grain' => __( 'product', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_Reco_Penalty / the recommendation engine (hot path, O(1) PK lookup with object cache), AIEO_DMM_Cancellations admin', 'site_profiles' => array( 'shop' ), 'notes' => 'Reads JSON product_ids via JSON_TABLE (MariaDB 10.6+). If aieo_dmm_order_line_refunds is empty, refund_rate is uniformly 0 and only the cancellation half of the penalty is live.', ), array( 'id' => 'core_product_vitals', 'label' => __( 'Product vitals (denormalised per product)', 'ai-eshop-optimizer' ), 'measures' => __( 'The plugin\'s single denormalised product row: price/stock/availability, category + brand ids, attribute and need linkage, sales aggregates and demand_score — the read-model every serving surface uses instead of touching postmeta.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_core_product_vitals', 'aieo_core_variation_vitals' ), 'producer' => 'sprocs in includes/sql/sprocs.php driven by AIEO_Recompute_Queue (dirty-object queue, minute tick) and the Data-Prep full rebuild', 'producer_class' => 'AIEO_Recompute_Queue', 'cron_hooks' => array( 'aieo_recompute_queue_tick' ), 'grain' => __( 'product AND variation', 'ai-eshop-optimizer' ), 'consumers' => 'chat retrieval + embeddings, AIEO_Boost_Candidates, AIEO_Ratings, AIEO_Core_Brand_Vitals, the sales predictor (sp_vit snapshot), archive/PDP blocks, most DMM display modules', 'site_profiles' => array( 'shop', 'catalog' ), 'notes' => 'The most widely read table in the plugin. Schema/columns are probed once per request and cached — never per row (this codebase had a production swap incident from exactly that pattern).', ), array( 'id' => 'core_catalog_read_models', 'label' => __( 'Catalog read-models: attributes, category hierarchy, reciprocal products', 'ai-eshop-optimizer' ), 'measures' => __( 'Three compiled catalogue projections rebuilt by Data Prep: per-product attribute rows (attribute -> value, flattened out of postmeta/terms), the full product-category hierarchy with level/parent plus the google_product_category and facebook_product_category feed mappings, and the reciprocal (this-product <-> that-product) pairing used by the recommendation join.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_core_product_attributes', 'aieo_core_product_category_hierarchy', 'aieo_core_reciprocal_products' ), 'producer' => 'Data-Prep sprocs installed from includes/sql/sprocs.php — wp_AIEO_InsertCoreProductAttributes and wp_AIEO_InsertProductCategoryHierarchy (both TRUNCATE + INSERT, so a failed run leaves an EMPTY table, not a stale one) — driven by AIEO_DMM_Data_Prep_Scheduler. Tables are created by aieo_create_core_*_table() (DDL only).', 'producer_class' => 'AIEO_DMM_Data_Prep_Scheduler', 'cron_hooks' => array( 'aieo_dmm_data_prep_run' ), 'grain' => __( 'product x attribute / product category / product pair', 'ai-eshop-optimizer' ), 'consumers' => 'AI_eShop_Chat_Embedding_Manager and the chat REST retrieval (attribute + hierarchy context in the embedded document), the feed/exchanger category mapping, and the reciprocal LEFT JOIN inside the product-vitals sprocs', 'site_profiles' => array( 'shop', 'catalog' ), 'notes' => 'TRUNCATE+INSERT means "empty" here is indistinguishable from "the rebuild died halfway", which is why these are catalogued per table rather than as one summed row count — check empty_tables, not rows. On the reference shop aieo_core_reciprocal_products has never held a row while its two siblings are populated, so any recommendation logic that LEFT JOINs it is silently running with the reciprocal leg switched off.', ), array( 'id' => 'core_brand_vitals', 'label' => __( 'Brand vitals + category-brand popularity', 'ai-eshop-optimizer' ), 'measures' => __( 'Per brand: catalogue size, price band, stock coverage, sales aggregates and popularity; plus per (category, brand) popularity for navigator/menu ordering.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_core_brand_vitals', 'aieo_core_category_brand_popularity' ), 'producer' => 'AIEO_Core_Brand_Vitals (dirty-brand queue in option aieo_brand_vitals_dirty_brands) + sprocs in includes/sql/brand-vitals-schema.php', 'producer_class' => 'AIEO_Core_Brand_Vitals', 'cron_hooks' => array(), 'grain' => __( 'brand / category x brand', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_Navigator, brand archive hero + brand blocks, aieo/brand-performance-tiers + list-brands + brand-gift-coverage abilities', 'site_profiles' => array( 'shop', 'catalog' ), 'notes' => 'Object-cache fronted with a version key (group aieo_core_brand_vitals, TTL 1h) so a brand edit invalidates without a full flush.', ), array( 'id' => 'recompute_queue', 'label' => __( 'Dirty-object recompute queue (the Phase-C driver)', 'ai-eshop-optimizer' ), 'measures' => __( 'The work list of (surface, entity_id, audience_key) tuples invalidated by a product save, a stock-status flip, a pricing/offer rule change or a cache-version bump — and therefore what the compiled read-models still owe.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_recompute_queue' ), 'producer' => 'AIEO_Recompute_Queue::enqueue (hooked to product save, stock status, post status, delete, and the aieo_dmm_cache_version_bumped action); drained by ::tick', 'producer_class' => 'AIEO_Recompute_Queue', 'cron_hooks' => array( 'aieo_recompute_queue_tick' ), 'grain' => __( 'product x surface x audience (work item)', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Effective_Recos, AIEO_Effective_Prices, AIEO_Effective_Eligibility, the core product/variation vitals sprocs', 'site_profiles' => array( 'shop', 'catalog' ), 'notes' => 'DEPTH IS THE SIGNAL: a queue that never drains means every "effective" table below is serving stale answers, and a queue at zero with empty outputs means the enqueue side never fired. Tick interval is the custom schedule aieo_recompute_minute (option aieo_recompute_tick_seconds, default 60). It self-heals duplicate scheduling via pre_schedule_event filters and a lock, so a doubled cron array is expected rather than alarming.', ), array( 'id' => 'product_need_suggestions', 'label' => __( 'Need-matched product suggestions', 'ai-eshop-optimizer' ), 'measures' => __( 'Per source product, a JSON array of up to 6 product ids that serve the SAME customer need (matched on primary_need_id / secondary_need_id only, deliberately not parsing other_needs_ids), ranked by TotalItemSales.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_product_need_suggestions' ), 'producer' => 'AI_eShop_Need_Suggestions::generate_all_suggestions / ::generate_suggestions_batch (batch 100-300), run from the Data-Prep pass when option aieo_create_need_suggestions = 1. Table created by aieo_create_product_need_suggestions_table() (DDL only).', 'producer_class' => 'AI_eShop_Need_Suggestions', 'cron_hooks' => array(), 'grain' => __( 'product (source) -> product list', 'ai-eshop-optimizer' ), 'consumers' => 'the need-suggestions render in class-eshop-optimizer.php (reads suggested_products by product_id; parents only — a variation is resolved to its parent first), ::get_suggestions / ::get_stats', 'site_profiles' => array( 'shop', 'catalog' ), 'notes' => 'HAS A HARD UPSTREAM: it ranks off wp_aieo_temp_product_catalogue, which Data Prep must have populated first, and matches off the operator-authored needs in aieo_dmm_product_needs. Empty here almost always means one of those two, not a broken generator. Distinct from effective_recos (behavioural/affinity) and reco_cache (LLM bundles): this is the STATED-NEED axis, and rebuilding it as "similar products" loses that.', ), array( 'id' => 'effective_recos', 'label' => __( 'Compiled per-audience recommendations', 'ai-eshop-optimizer' ), 'measures' => __( 'The materialised top-N recommendation set per (source product, surface, audience): target product, rank, score and payload — so a PDP/sidecart render is an indexed read instead of a live affinity computation.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_effective_recos' ), 'producer' => 'AIEO_Effective_Recos::recompute / ::recompute_all_audiences, driven by AIEO_Recompute_Queue::tick; schema self-installs on option aieo_effective_recos_schema_version', 'producer_class' => 'AIEO_Effective_Recos', 'cron_hooks' => array( 'aieo_recompute_queue_tick' ), 'grain' => __( 'source product x surface x audience x target product', 'ai-eshop-optimizer' ), 'consumers' => 'front-end reco rendering (AIEO_DMM_Badge_Renderer, class-eshop-optimizer.php), AIEO_Effective_Recos::get_top / ::get_top_full', 'site_profiles' => array( 'shop', 'catalog' ), 'notes' => 'Typically the biggest table the plugin owns — it multiplies products x surfaces x audiences. Gated by ::is_enabled(); when the feature is off the table can be large and STALE rather than absent, which is the failure mode to watch. Invalidation is bidirectional: ::invalidate_target lets a change to a TARGET product dirty every source row that recommends it.', ), array( 'id' => 'effective_prices', 'label' => __( 'Compiled per-audience effective prices', 'ai-eshop-optimizer' ), 'measures' => __( 'The resolved price a given audience actually sees per product — after role pricing, offers and rules — with the display HTML, so price rendering does not re-evaluate the whole rule stack per request.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_effective_prices' ), 'producer' => 'AIEO_Effective_Prices::recompute / ::recompute_all_audiences, driven by AIEO_Recompute_Queue::tick; schema on option aieo_effective_prices_schema_version', 'producer_class' => 'AIEO_Effective_Prices', 'cron_hooks' => array( 'aieo_recompute_queue_tick' ), 'grain' => __( 'product x audience', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Effective_Prices::maybe_filter_price_html / ::maybe_filter_get_price on the front end, ::get_for_batch for archive listings', 'site_profiles' => array( 'shop' ), 'notes' => 'This is the read-model behind the role-pricing hot path — the same area that produced a documented N+1 incident, so DO NOT add a live price computation next to it. TTL defaults to 0 (never time-expire, invalidate only via the queue); option aieo_effective_prices_ttl_seconds can force staleness expiry if the queue is ever untrustworthy. Audiences are represented by a representative user (::representative_user_for_audience), so a wrong audience_key silently yields a plausible-but-wrong price.', ), array( 'id' => 'effective_eligibility', 'label' => __( 'Compiled per-audience surface eligibility', 'ai-eshop-optimizer' ), 'measures' => __( 'Whether a given (surface, audience, product) is allowed to show at all, plus the offer id and payload that justified it — the gate the badge/promo surfaces consult before rendering.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_effective_eligibility' ), 'producer' => 'AIEO_Effective_Eligibility::recompute / ::set / ::recompute_all_audiences, driven by AIEO_Recompute_Queue::tick; schema on option aieo_effective_eligibility_schema_version', 'producer_class' => 'AIEO_Effective_Eligibility', 'cron_hooks' => array( 'aieo_recompute_queue_tick' ), 'grain' => __( 'surface x audience x product', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_Badge_Renderer and the promo/offer surfaces via ::is_eligible / ::get_for. On a shop whose offers store mode is unified, the badges and the add-to-cart snapshot read the gift and free shipping from wp_aieo_dmm_offer_facts (AIEO_DMM_Offer_Facts) instead, and no brand_gift or free_shipping_bar row is a badge\'s', 'site_profiles' => array( 'shop', 'catalog' ), 'notes' => 'A row can legitimately record NOT-eligible, so row count alone does not tell you the surface is live — read the eligible flag, not COUNT(*). Sibling of effective_prices/effective_recos and invalidated by the same queue; if one of the three is empty while the others are full, the enqueue hook for that surface is the thing that is broken.', ), array( 'id' => 'reco_cache', 'label' => __( 'Recommendation blob cache + reverse-reference index', 'ai-eshop-optimizer' ), 'measures' => __( 'The persisted per-anchor recommendation payload and the reverse index mapping every REFERENCED product back to the cache rows that mention it, so one product edit can invalidate precisely the entries affected.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_product_recommendations', 'aieo_reco_reverse_refs' ), 'producer' => 'AIEO_Recommendations_Cache (write on generate); reverse index backfilled by aieo_reco_reverse_refs_backfill_step on the resumable event aieo_reco_reverse_refs_backfill; TTL cleanup on aieo_cleanup_expired_recommendations', 'producer_class' => 'AIEO_Recommendations_Cache', 'cron_hooks' => array( 'aieo_cleanup_expired_recommendations', 'aieo_reco_reverse_refs_backfill' ), 'grain' => __( 'anchor product (cache) / referenced product -> cache row (index)', 'ai-eshop-optimizer' ), 'consumers' => 'front-end reco rendering in class-eshop-optimizer.php, aieo/recommend-products-for-customer ability', 'site_profiles' => array( 'shop' ), 'notes' => 'Readiness is the autoloaded option aieo_reco_reverse_refs_ready — until it is 1 the invalidation path is incomplete and edits can leave stale recos. Distinct from BOTH aieo_effective_recos (audience-compiled) and aieo_subject_recos (behavioural, per subject); three different reco stores coexist by design and merging them is not a cleanup, it is a regression.', ), array( 'id' => 'boost_candidates', 'label' => __( 'Boost fusion scorer (what to promote next)', 'ai-eshop-optimizer' ), 'measures' => __( 'One row per candidate object with boost_score 0-100 = weighted sum of percent-ranked components — seo_opportunity (priority + quadrant), search_demand, commerce_demand, forecast_units (sales_predictor next-28d x confidence), content_demand, intent_mass (wishlist + addcart), push_appetite (dispatch CTR), waitlist_demand, rating_quality (Bayesian-shrunk), repeat_affinity (category-relative repurchase rate) — plus components_json, weights_version, eligible_vectors and veto_reasons.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_boost_candidates', 'aieo_boost_weights' ), 'producer' => 'AIEO_Boost_Candidates::rebuild (TRUNCATE + rebuild under a DB lock)', 'producer_class' => 'AIEO_Boost_Candidates', 'cron_hooks' => array( 'aieo_boost_candidates_rebuild' ), 'grain' => __( 'product AND content (products plus every public post/page; also store_location where present)', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Ads_Composer, aieo/boost-slate abilities, AIEO_DMM_Social, AIEO_DMM_Ratings', 'site_profiles' => array( 'all' ), 'notes' => 'Modelled on AIEO_SEO_Opportunity_Scorer — READ THAT ONE FIRST before building any new scorer. Weights are RENORMALISED per object over the components that actually have data, so a catalog or content site is scored fairly on the signals it has. Every source table AND column is probed (cached per request) before use; a missing signal just drops that component — which means a dead upstream (e.g. an empty sales_predictor) silently removes forecast_units rather than failing loudly. veto_reasons splits transient (out_of_stock, stock_signout) from permanent (low_rating) via ::veto_is_transient / ::split_vetoes. Voice-of-Customer is deliberately NOT a component.', ), array( 'id' => 'product_affinity', 'label' => __( 'Parent repurchase / variation leader / variation breadth', 'ai-eshop-optimizer' ), 'measures' => __( 'variation_leaders() — per parent, the top-selling VARIATIONS by units, each with its own featured image: which shade the ad creative should show. The other two methods were deleted 2026-07-26 (see notes).', 'ai-eshop-optimizer' ), 'tables' => array(), 'producer' => 'AIEO_Product_Affinity::variation_leaders — reads the newest cumulative order snapshot via AIEO_Customer_Purchases::source_tables() + newest_only(); result cached in a 6h transient', 'producer_class' => 'AIEO_Product_Affinity', 'cron_hooks' => array(), 'grain' => __( 'variation, keyed by parent', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Ads_Composer::variation_hints (includes/ads/class-aieo-ads-composer.php) calls ::variation_leaders($ids, 1) and names the leading variation in the COPYWRITER prompt when it clears an even-split test. Sole method, sole consumer.', 'consumed' => true, 'site_profiles' => array( 'shop' ), 'notes' => 'TRIMMED 2026-07-26 to the one question nothing else answers. parent_repurchase() was DELETED: it duplicated the wired boost component repeat_affinity, which answers the same question with empirical-Bayes shrinkage against a leave-one-out category mean, where this published a raw unshrunk rate with 29.5% of parents at exactly 0.0. Its one real advantage was identity — EponymousID rather than wc_order_product_lookup.customer_id, which splits a guest from their later registration and understates repurchase by ~13.8% by the repeat_affinity docblock itself — and that is worth fixing IN the survivor, not worth keeping a weaker second answer alive to remember it by. variation_breadth() was DELETED as self-falsifying: its own docblock recorded spearman 0.929 against the number of shades a product comes in (so ~93% a restatement of catalogue width, not preference depth) and 0.999 between its two output columns, with 72.5% of parents pinned at 1.0000. The image_id variation_leaders returns is still unused by the composer, which names the shade in COPY but does not yet pick the picture — that is the remaining half of the operator model.' ), array( 'id' => 'conversion_probability_model', 'label' => __( 'Engagement -> purchase probability model', 'ai-eshop-optimizer' ), 'measures' => __( 'compute_bucket_table(product_id) returns the 16-cell scroll-quartile x time-quartile grid of empirical P(cart-add); find_steepest_gradient_products(period) finds products where engaged visitors convert far above disengaged ones.', 'ai-eshop-optimizer' ), 'tables' => array(), 'producer' => 'AIEO_Conversion_Probability_Model::compute_bucket_table / ::find_steepest_gradient_products — materialised weekly into aieo_conversion_buckets', 'producer_class' => 'AIEO_Conversion_Probability_Model', 'cron_hooks' => array(), 'grain' => __( 'product x engagement bucket', 'ai-eshop-optimizer' ), 'consumers' => 'aieo/buy-probability-by-engagement + buy-probability-leaderboard abilities (LIVE, from raw aieo_sessions), AIEO_Aggregate_Cron::compute_conversion_buckets', 'site_profiles' => array( 'shop' ), 'notes' => 'Heuristic aggregate, not ML — deliberately explainable so Claude can quote it. Reads the RAW 3-day firehose, so the live abilities have a much shorter effective window than the 90-day materialised buckets they could be reading (see the conversion_buckets signal).', ), array( 'id' => 'price_history', 'label' => __( 'EU-Omnibus price history', 'ai-eshop-optimizer' ), 'measures' => __( 'Append-only, change-only observations of the effective price per purchasable unit (simple product or individual variation): effective, regular, on_sale, currency, observed_at — the substrate for the 30-day lowest-price reference.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_price_history' ), 'producer' => 'AIEO_Price_History::record / ::record_product (price-change hooks) + AIEO_DMM_Price_History daily sweep', 'producer_class' => 'AIEO_Price_History', 'cron_hooks' => array( 'aieo_price_history_daily' ), 'grain' => __( 'product AND variation (purchasable unit, not parent)', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Price_History::lowest_30d / ::reference / ::is_reduction, the lowest-price-message block, AIEO_DMM_Price_History admin', 'site_profiles' => array( 'shop' ), 'notes' => 'Never pruned by design — a row only lands when the price actually changes, so ~100-300k rows/year at 10k products.', ), array( 'id' => 'stock_recovery', 'label' => __( 'Physical stock recovery candidates', 'ai-eshop-optimizer' ), 'measures' => __( 'Products the ERP says exist but Woo shows as out of stock (false-OOS), plus the recovery events raised and resolved for each.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_dmm_stock_recovery_candidates', 'aieo_dmm_stock_recovery_events' ), 'producer' => 'AIEO_DMM_Physical_Recovery + the analytics-job sprocs in includes/sql/analytics-jobs-procs.php', 'producer_class' => 'AIEO_DMM_Physical_Recovery', 'cron_hooks' => array( 'aieo_analytics_job_run' ), 'grain' => __( 'product', 'ai-eshop-optimizer' ), 'consumers' => 'aieo/stock-recovery ability, AIEO_DMM_Physical_Recovery admin', 'site_profiles' => array( 'shop' ), 'notes' => 'Restock excludes the shop\'s OWN sales via the ERP InvoiceType exclusion — otherwise every sale reads as a replenishment. Two analytics jobs feed it: `stock_recovery` (candidates) and `stock_recovery_outcomes` (events); the last-run shown is the candidates pass.', 'last_run_job' => 'stock_recovery', ), array( 'id' => 'analytics_jobs_runner', 'label' => __( 'Analytics job scheduler + run ledger', 'ai-eshop-optimizer' ), 'measures' => __( 'Not a metric — the DAG, per-job schedule (frequency + Athens-local time) and the last-run ledger (finished_at, source, duration, rows, ok, error) for all 15 SQL analytics jobs.', 'ai-eshop-optimizer' ), 'tables' => array(), 'producer' => 'AIEO_DMM_Product_Orders_Analytics::run_job / ::run_all / ::maybe_schedule / ::install_sprocs, over aieo_analytics_jobs_definitions() in includes/sql/analytics-jobs-sprocs.php', 'producer_class' => 'AIEO_DMM_Product_Orders_Analytics', 'cron_hooks' => array( 'aieo_analytics_job_run' ), 'grain' => __( 'site (orchestrator)', 'ai-eshop-optimizer' ), 'consumers' => 'the Product & Orders Analytics admin subtab (row counts, Run now, Run all, reinstall sprocs); every job-backed signal in this registry resolves its last-run through the ledger option', 'site_profiles' => array( 'shop' ), 'notes' => 'START HERE when adding or debugging an SQL analytics job — the definitions array carries file/proc/deps/label/table/default_time and is the single place a new job is declared. State is options, not tables: aieo_analytics_jobs_schedule / _last_run / _sproc_version. Each run takes a named lock and registers a shutdown release. A GREEN LEDGER DOES NOT MEAN A POPULATED TABLE: several ERP-fed jobs report ok=true with rows=0 when their source feed is empty.', ), array( 'id' => 'physical_store_sales', 'label' => __( 'Physical-store sales stats (per branch)', 'ai-eshop-optimizer' ), 'measures' => __( 'Per (product, branch): 30-day and 60-day in-store units and turnover from the ERP feed — the offline demand signal that makes false-out-of-stock detection possible.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_dmm_store_sales' ), 'producer' => 'sproc wp_AIEO_GenerateStoreSalesStats (analytics job `store_sales_stats`)', 'producer_class' => 'AIEO_DMM_Product_Orders_Analytics', 'cron_hooks' => array( 'aieo_analytics_job_run' ), 'grain' => __( 'product x store branch', 'ai-eshop-optimizer' ), 'consumers' => 'the preorder_vitals job, AIEO_DMM_Product_Orders_Analytics, the physical-recovery estate', 'site_profiles' => array( 'shop' ), 'notes' => 'Sproc v25 added ANALYZE-after-TRUNCATE+rebuild to stop a stale-statistics plan flip. Has NO computed_at column, so freshness is only knowable through the job ledger — which is exactly why last_run_job is set here.', 'last_run_job' => 'store_sales_stats', ), array( 'id' => 'preorder_vitals', 'label' => __( 'Preorder vitals + per-branch store boards', 'ai-eshop-optimizer' ), 'measures' => __( 'Per product/branch preorder demand and the replenishment board rows derived from store sales plus the supplier map.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_dmm_preorder_vitals_stats', 'aieo_dmm_preorder_vitals', 'aieo_dmm_supplier_preorders' ), 'producer' => 'sproc wp_AIEO_GeneratePreorderVitals (analytics job `preorder_vitals`, deps store_sales_stats + supplier_sku_sync)', 'producer_class' => 'AIEO_DMM_Product_Orders_Analytics', 'cron_hooks' => array( 'aieo_analytics_job_run' ), 'grain' => __( 'product x branch', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_Product_Orders_Analytics / the per-branch store boards', 'site_profiles' => array( 'shop' ), 'notes' => 'A TEXTBOOK "green run that produced nothing": the job reports ok=true with rows=0 whenever its dependency aieo_dmm_supplier_sku_map is empty, which happens whenever the ERP catalogue feed is empty. Diagnose upstream (supplier_sku_map, then the ERP feed table) before touching this proc.', 'last_run_job' => 'preorder_vitals', ), array( 'id' => 'supplier_sku_map', 'label' => __( 'Supplier <-> SKU map', 'ai-eshop-optimizer' ), 'measures' => __( 'Per-product supplier resolved from the ERP catalogue feed — the join that lets preorder and replenishment boards group by who is actually shipping the goods.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_dmm_supplier_sku_map' ), 'producer' => 'sproc wp_AIEO_SyncSupplierSkuMap (analytics job `supplier_sku_sync`)', 'producer_class' => 'AIEO_DMM_Product_Orders_Analytics', 'cron_hooks' => array( 'aieo_analytics_job_run' ), 'grain' => __( 'product', 'ai-eshop-optimizer' ), 'consumers' => 'the preorder_vitals job, supplier boards', 'site_profiles' => array( 'shop' ), 'notes' => 'Empty whenever the ERP source table is empty. This is the first thing to check when preorder_vitals reports a successful run with zero rows.', 'last_run_job' => 'supplier_sku_sync', ), array( 'id' => 'erp_reciprocal_ids', 'label' => __( 'ERP store-only products + reciprocal id map', 'ai-eshop-optimizer' ), 'measures' => __( 'SKU <-> SOFT1 id <-> eshop product/parent id reciprocity, and the set of products that exist in the physical store but not the eshop (with per-role prices, stock, barcode, supplier).', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_reciprocal_ids', 'aieo_store_only_products' ), 'producer' => 'sproc wp_AIEO_PrepareStoreOnlyProductTables (analytics job `store_only_products`), TRUNCATE+rebuild from the ERP unique-product-catalogue feed', 'producer_class' => 'AIEO_DMM_Product_Orders_Analytics', 'cron_hooks' => array( 'aieo_analytics_job_run' ), 'grain' => __( 'product', 'ai-eshop-optimizer' ), 'consumers' => 'the physical-recovery and catalog-gap sprocs', 'site_profiles' => array( 'shop' ), 'notes' => 'On the reference shop these AIEO-owned tables are empty while the LEGACY wp_s1erp_reciprocal_ids_table still holds the data — so a reader who greps for the legacy name finds rows and wrongly concludes the migration completed. The AIEO tables are the intended source going forward.', 'last_run_job' => 'store_only_products', ), array( 'id' => 'catalog_gap_candidates', 'label' => __( 'Catalog gaps — hot sellers missing from the eshop', 'ai-eshop-optimizer' ), 'measures' => __( 'SKUs selling well in the PHYSICAL stores that are not live online: physical units and net turnover over the most recent valid period, the Woo post_status where a draft exists, which attributes (image/description/category/brand) are missing, and whether the product needs creating outright. Plus the outcome ledger for gaps that were closed.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_dmm_catalog_gap_candidates', 'aieo_dmm_catalog_gap_events' ), 'producer' => 'sprocs wp_AIEO_GeneratePhysicalRecovery / wp_AIEO_GeneratePhysicalRecoveryOutcomes (analytics jobs `stock_recovery` / `stock_recovery_outcomes`) — the same pass that writes the stock-recovery tables', 'producer_class' => 'AIEO_DMM_Physical_Recovery', 'cron_hooks' => array( 'aieo_analytics_job_run' ), 'grain' => __( 'product (or SKU with no product yet)', 'ai-eshop-optimizer' ), 'consumers' => 'aieo/missing-hot-sellers ability, AIEO_DMM_Missing_Hot_Sellers admin, AIEO_DMM_Physical_Recovery admin', 'site_profiles' => array( 'shop' ), 'notes' => 'Catalogued SEPARATELY from stock_recovery even though one sproc writes both: that entry answers "what is falsely out of stock", this one answers "what do we not sell online at all". They have different owners, different admin pages and different abilities, and folding them together is what hid this one. Ranked by physical net turnover so the highest-impact gap is published first.', 'last_run_job' => 'stock_recovery', ), array( 'id' => 'category_brand_popularity', 'label' => __( 'Category x brand popularity ranks', 'ai-eshop-optimizer' ), 'measures' => __( 'Per category (with level + parent) and per brand-in-category: sales-based popularity ranks — subcategory_rank_in_parent_sales and brand_rank_in_category_sales — the ordering behind navigator and menu placement.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_core_category_brand_popularity' ), 'producer' => 'AIEO::aieo_process_export_batch, Data-Prep step `aggregate_category_brand_stats` (includes/class-eshop-optimizer.php, progress_step 4) — RANK() OVER (PARTITION BY parent_category_id / category_id) on MySQL 8+ / MariaDB 10.2+, with a correlated-subquery fallback for older servers. DDL only (no rows) in aieo_create_core_category_brand_popularity_table().', 'producer_class' => 'AIEO', 'cron_hooks' => array( 'aieo_dmm_data_prep_run' ), 'grain' => __( 'product category x brand', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Core_Brand_Vitals (brand rollups), AIEO_DMM_Navigator, class AIEO (archive + menu ordering)', 'site_profiles' => array( 'shop', 'catalog' ), 'notes' => 'Shares a table with the core_brand_vitals entry, which is why the dedup key is table+producer rather than table alone: the hourly data-prep refreshes brand vitals but has been observed NOT to move this table, so the two need separate health readings. GATED: the step only runs when option aieo_include_aggregate_category_brand_stats = 1, so a stale rank set is usually that checkbox, not a broken sproc.', ), array( 'id' => 'wishlist', 'label' => __( 'Wishlist / favourites', 'ai-eshop-optimizer' ), 'measures' => __( 'One row per (user-or-cookie, product) favourite with date_added — the purest stated-intent signal in the plugin.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_wishlist' ), 'producer' => 'AIEO_Wishlist (AJAX aieo_wishlist_toggle / aieo_wishlist_get); one-shot migration from wp_om_ppe_wishlist', 'producer_class' => 'AIEO_Wishlist', 'cron_hooks' => array(), 'grain' => __( 'customer x product', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Boost_Candidates (intent_mass), AIEO_Customer_Intelligence_Query, AIEO_DMM_Product_Display, brand-archive-hero block, price-drop-wishlist + brand-wishlist-new-arrival push triggers, AIEO_Google_Tag', 'site_profiles' => array( 'shop', 'catalog' ), 'notes' => 'Cookie name is intentionally the legacy PPE one so hearts stay synchronised across both plugins.', ), array( 'id' => 'waitlist', 'label' => __( 'Back-in-stock waitlist demand', 'ai-eshop-optimizer' ), 'measures' => __( 'Per (product, subscriber) queue rows with status (subscribed | notified) — queue depth is a pure desirability signal separable from stock.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_waitlist' ), 'producer' => 'AIEO_DMM_Waitlist (AJAX aieo_waitlist_subscribe + the legacy om_ppe_waitlist_subscribe shim); notified on woocommerce_product_set_stock_status', 'producer_class' => 'AIEO_DMM_Waitlist', 'cron_hooks' => array( 'aieo/waitlist_notify' ), 'grain' => __( 'product x customer', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Boost_Candidates (waitlist_demand — desirability with a TRANSIENT stock veto), aieo/boost-slate abilities, AIEO_DMM_Social, back-in-stock push trigger, AIEO_Workflow_Runtime, AIEO_AW_Decommission, AIEO_DMM_Goal_Conversions (waitlist_signup lead, per campaign)', 'site_profiles' => array( 'shop' ), 'notes' => 'Since 2026-08-30 a successful subscribe also fires aieo_waitlist_subscribed and books a valued `waitlist_signup` conversion in wp_aieo_conversions, so a campaign that sent buyers to an out-of-stock product stops scoring EUR 0.00. NO SCHEMA CHANGE was made to this table for that: the conversion row references the waitlist row id through provider + entry_id.', ), array( 'id' => 'product_requests', 'label' => __( 'Quote and contact requests', 'ai-eshop-optimizer' ), 'measures' => __( 'ONLY wp_aieo_product_request_items is demand: it is what a visitor explicitly asked to be quoted. wp_aieo_product_request_views is CONTEXT ONLY (products passively looked at while their quote was open) and must never be counted as intent, summed with the items, or fed to a ranking. Ten viewed and three requested is a different fact from ten viewed and one requested; that distinction is the whole reason the two live in separate tables.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_product_requests', 'aieo_product_request_items', 'aieo_product_request_views' ), 'producer' => 'AIEO_Product_Request_Store (AJAX aieo_product_request_submit / _add_item / _remove_item / _view), shared byte-identically with the runtime plugin so it writes with core deactivated', 'producer_class' => 'AIEO_Product_Request_Store', 'cron_hooks' => array( 'aieo/product_request_notify' ), 'grain' => __( 'request x product', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_Product_Requests admin, AIEO_Product_Request_Trail, AIEO_DMM_Goal_Conversions (product_request lead, per campaign)', 'site_profiles' => array( 'shop', 'catalog' ), 'notes' => 'Built for CATALOGUE sites, where nothing is purchasable and the only conversion a product page can produce is a conversation. Attribution is captured live on the uncached submit and persisted on the request row, and the conversion ledger is written from that row rather than from aieo_goal_recorded — which is consent-gated and returns early with no session tables, exactly the state of the sites this exists for.', ), array( 'id' => 'ratings', 'label' => __( 'Star ratings + aggregate stats', 'ai-eshop-optimizer' ), 'measures' => __( 'Event log of 1-5 star votes (anonymous votes keyed by the aieo_rating_session cookie) and the aggregate cache per object: count_total, sum_total, the n1..n5 histogram, last_voted_at.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_ratings', 'aieo_rating_stats' ), 'producer' => 'AIEO_Ratings (AJAX aieo_rating_save / aieo_rating_get) updating aieo_rating_stats via ON DUPLICATE KEY UPDATE deltas; AIEO_Ratings_WC_Bridge reconciles against WooCommerce reviews', 'producer_class' => 'AIEO_Ratings', 'cron_hooks' => array( 'aieo_ratings_wc_reconcile' ), 'grain' => __( 'product (object_type column allows other objects)', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Boost_Candidates (rating_quality, Bayesian-shrunk mean; low ratings are a PERMANENT veto), AIEO_Core_Brand_Vitals, AIEO_Session_Attribution_Bridge::get_rating_vs_conversion, four chat abilities (leaderboard, coverage-by-category, products-needing-ratings, rating-conversion-correlation), AIEO_DMM_Ratings admin', 'site_profiles' => array( 'shop', 'catalog' ), 'notes' => '', ), array( 'id' => 'saved_abandoned_carts', 'label' => __( 'Saved & abandoned carts', 'ai-eshop-optimizer' ), 'measures' => __( 'Per cart: status (active | abandoned), value, owner (cookieless share token or user), created/updated, plus normalised line items for analysis.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_carts', 'aieo_cart_items' ), 'producer' => 'AIEO_Carts_Capture::run_capture (+ AIEO_Carts_Store for save/share/resume)', 'producer_class' => 'AIEO_Carts_Capture', 'cron_hooks' => array( 'aieo_carts_capture_abandoned' ), 'grain' => __( 'session/customer cart', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_Saved_Abandoned_Carts admin, AIEO_Workflow_Runtime / AIEO_Workflows / AIEO_Order_Rules, aieo/abandoned-product-offers + abandoned-cart-triage abilities, AIEO_AW_Decommission', 'site_profiles' => array( 'shop' ), 'notes' => '', ), array( 'id' => 'google_reviews', 'label' => __( 'Google place reviews', 'ai-eshop-optimizer' ), 'measures' => __( 'Full review history per tracked Google place (never deleted), with matched_user_id overlaying the customer tie; the locations table carries per-location API keys for quota sharding.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_google_reviews', 'aieo_google_locations' ), 'producer' => 'AIEO_Google_Reviews_Fetcher via AIEO_Google_Reviews_Cron', 'producer_class' => 'AIEO_Google_Reviews_Fetcher', 'cron_hooks' => array( 'aieo_grv_refresh' ), 'grain' => __( 'site / store location', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Google_Reviews_Block, helpers, settings, AIEO_DMM_Google_Reviews', 'site_profiles' => array( 'all' ), 'notes' => 'Local / multi-location sites. Action Scheduler preferred.', ), array( 'id' => 'push_dispatch', 'label' => __( 'Owned-message dispatch ledger', 'ai-eshop-optimizer' ), 'measures' => __( 'One row per push/email send: trigger_id, template_id, subject/campaign, accepted, opened, clicked, converted, revenue and the timestamps that make multi-touch attribution and the bandit possible.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_push_dispatch', 'aieo_push_subscriptions', 'aieo_push_templates', 'aieo_push_campaigns', 'aieo_push_caps' ), 'producer' => 'AIEO_Push_Dispatcher / AIEO_Email_Channel via AIEO_DMM_Web_Push', 'producer_class' => 'AIEO_DMM_Web_Push', 'cron_hooks' => array( 'aieo_push_dispatch_tick', 'aieo_push_campaign_batch' ), 'grain' => __( 'customer x message', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Bandit (msg_stats + template weights), AIEO_Workflow_Stats, AIEO_Marketing_Stats, AIEO_Order_Touchpoints, AIEO_Boost_Candidates (push_appetite)', 'site_profiles' => array( 'all' ), 'notes' => 'THE upstream of the entire marketing-measurement chain: workflow_stats, msg_stats and marketing_stats all produce nothing when this is thin, and they look "empty" rather than "starved". Check dispatch row count first.', ), array( 'id' => 'coupon_grants', 'label' => __( 'Coupon grant ledger', 'ai-eshop-optimizer' ), 'measures' => __( 'Per minted coupon: code, source (\'wf_\', \'wf__o\', \'wf__g\' ...), recipient, minted/sent/clicked/redeemed timestamps and value.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_coupon_grants' ), 'producer' => 'AIEO_Coupon_Minter', 'producer_class' => 'AIEO_Coupon_Minter', 'cron_hooks' => array(), 'grain' => __( 'customer x coupon', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Workflow_Stats (parses the BASE workflow id out of scoped sources), AIEO_Bandit, AIEO_AW_Decommission', 'site_profiles' => array( 'shop' ), 'notes' => 'The scoped source format is why AIEO_Workflow_Stats parses rather than exact-matches — the bandit\'s exact-match join cannot see scoped grants.', ), array( 'id' => 'marketing_stats', 'label' => __( 'Unified all-marketing performance rollup', 'ai-eshop-optimizer' ), 'measures' => __( 'One row per marketing INITIATIVE reconciled like-for-like across four types: workflow (sent -> clicks -> coupons redeemed -> attributed revenue), push_trigger (from aieo_msg_stats), broadcast (reach -> clicks -> revenue), ad_campaign (spend -> orders -> ROAS), plus an ORIGIN dimension separating AI-driven from operator-built initiatives.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_marketing_stats' ), 'producer' => 'AIEO_Marketing_Stats::rebuild', 'producer_class' => 'AIEO_Marketing_Stats', 'cron_hooks' => array( 'aieo_marketing_stats_rebuild' ), 'grain' => __( 'site x marketing initiative', 'ai-eshop-optimizer' ), 'consumers' => 'Campaigns tab "All marketing" card, aieo/marketing-performance ability, AIEO_DMM_ROI_Wizard, AIEO_Campaign_Forecasts::reconcile', 'site_profiles' => array( 'shop' ), 'notes' => 'Also pulls aieo_social_posts / aieo_social_post_metrics / aieo_social_boosts when present. If every row is initiative_type=\'ad_campaign\', the workflow / push_trigger / broadcast legs are starved upstream (empty workflow_stats + msg_stats), not broken here. Twicedaily via Action Scheduler + throttled on-read.', ), array( 'id' => 'workflow_stats', 'label' => __( 'Per-workflow outcome funnel', 'ai-eshop-optimizer' ), 'measures' => __( 'Per workflow: sent -> clicked -> coupon minted/sent/clicked/redeemed -> revenue, reconciling three previously uncoordinated ledgers (aieo_push_dispatch by trigger_id=\'wf_\', aieo_coupon_grants by source, aieo_order_stats last-click orderutmcampaign).', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_workflow_stats' ), 'producer' => 'AIEO_Workflow_Stats::rebuild', 'producer_class' => 'AIEO_Workflow_Stats', 'cron_hooks' => array( 'aieo_workflow_stats_rebuild' ), 'grain' => __( 'site x workflow (campaign)', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Marketing_Stats, AIEO_Proposal_Engine, aieo/workflow-performance ability, Campaigns builder funnel', 'site_profiles' => array( 'shop' ), 'notes' => 'Parses the BASE workflow id out of scoped coupon sources — the exact-match join the bandit uses cannot. Zero rows despite many completed rebuilds means no workflow_runs and a thin dispatch ledger, not a broken rebuild.', ), array( 'id' => 'msg_stats', 'label' => __( 'Push message stats + Thompson-sampling bandit', 'ai-eshop-optimizer' ), 'measures' => __( 'Per (stat_date, template_id): accepted / clicked / converted_strict / revenue rolled up from aieo_push_dispatch, and the derived Beta(1+clicked, 1+accepted-clicked) Monte-Carlo P(best) written back as aieo_push_templates.weight.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_msg_stats' ), 'producer' => 'AIEO_Bandit::run_conversion_join -> ::run_rollup -> ::run_bandit', 'producer_class' => 'AIEO_Bandit', 'cron_hooks' => array( 'aieo_bandit_nightly' ), 'grain' => __( 'site x message template x day', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Marketing_Stats (push_trigger leg); the dispatcher consumes the derived weights, not this table', 'site_profiles' => array( 'shop' ), 'notes' => 'REWARD = clicked; converted_strict is a VETO only. The bandit reweights but never sends and never deactivates a variant. aieo_bandit_nightly runs purely via Action Scheduler and is deliberately absent from the WP-cron array — do not read that absence as "not scheduled".', ), array( 'id' => 'campaign_forecasts', 'label' => __( 'Forecast -> reconcile accountability loop', 'ai-eshop-optimizer' ), 'measures' => __( 'At accept-time, an immutable snapshot of WHAT WE PREDICTED (component signals, weights_version, model_version, predicted_* outcomes, confidence); days later, the ACTUALS from workflow_stats + order_stats plus error_ratio and reconciled_at — the residual a later weight-fitting pass would consume.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_campaign_forecasts' ), 'producer' => 'AIEO_Campaign_Forecasts::snapshot (write) and ::reconcile (grade)', 'producer_class' => 'AIEO_Campaign_Forecasts', 'cron_hooks' => array( 'aieo_social_forecast_reconcile', 'aieo_marketing_stats_rebuild' ), 'grain' => __( 'site x campaign/vector proposal', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_Proposals, AIEO_Proposal_Engine (weights feedback), Campaigns admin', 'site_profiles' => array( 'shop' ), 'notes' => 'Binds RECONCILE_HOOK always, and additionally piggybacks the marketing-stats rebuild so a reconcile happens even if its own schedule is lost. Prefers Action Scheduler (group \'aieo\'), falling back to WP-cron.', ), array( 'id' => 'social_performance', 'label' => __( 'Social publish ledger', 'ai-eshop-optimizer' ), 'measures' => __( 'One row per (workflow, platform, account, object) publish attempt: platform_post_id, permalink, media, status, error, published_at, variant_label, step_idx, launch_batch.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_social_posts' ), 'producer' => 'AIEO_Social_Jobs::run_publish on the one-shot action aieo_social_publish_batch; drafts by AIEO_Social_Broadcast / AIEO_Social_Metabox; schema in includes/dmm/sql/social-schema.php', 'producer_class' => 'AIEO_Social_Jobs', 'cron_hooks' => array( 'aieo_social_publish_batch' ), 'cron_kind' => 'on_demand', 'grain' => __( 'marketing initiative (publish attempt)', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Marketing_Stats, aieo/social-performance ability, AIEO_DMM_Social, AIEO_Social_Boost (object_story_id for a Meta boost)', 'site_profiles' => array( 'all' ), 'notes' => 'The UNIQUE key makes a workflow re-run idempotent (upsert, never a duplicate post). Fully site-owned: each shop registers its own platform app and tokens live ENCRYPTED in options via AIEO_Social_Store, never in these tables. Empty until a platform account is connected.', ), array( 'id' => 'social_post_metrics', 'label' => __( 'Per-day social post engagement snapshot', 'ai-eshop-optimizer' ), 'measures' => __( 'Impressions / reach / likes / comments / shares / link clicks per (social post, day), fetched back from the platform.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_social_post_metrics' ), 'producer' => 'NONE — table created by aieo_social_install_schema(); every driver (Facebook, Instagram, LinkedIn, TikTok, X) implements fetch_metrics($post_ref), but fetch_metrics() has ZERO callers plugin-wide. The daily refresh its schema docblock describes does not exist.', 'producer_class' => '', 'cron_hooks' => array(), 'grain' => __( 'social post x day', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Marketing_Stats (reach/clicks), aieo/social-performance ability — both read a table nothing writes', 'site_profiles' => array( 'all' ), 'notes' => 'A WRITE-NEVER TABLE WITH TWO LIVE READERS. This is the highest-value dormancy in the social estate: the platform-side collection code is fully implemented per driver and only lacks a scheduler plus an upsert. DO NOT REBUILD THE DRIVERS — wire fetch_metrics() into a daily job and write the rows.', ), array( 'id' => 'social_boosts', 'label' => __( 'Paid boost journal', 'ai-eshop-optimizer' ), 'measures' => __( 'Every boost attempt — sandbox or live — with platform, campaign/adset/ad resource names, budget, idempotency key and outcome.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_social_boosts' ), 'lazy_tables' => array( 'aieo_social_boosts' ), 'producer' => 'AIEO_Social_Boost::boost -> ::journal; table lazily created by ::ensure_table on first use', 'producer_class' => 'AIEO_Social_Boost', 'cron_hooks' => array(), 'grain' => __( 'marketing initiative (boost attempt)', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Social_Boost::spent_today (daily cap enforcement), ::find_by_idem (double-spend prevention), AIEO_DMM_Social admin', 'site_profiles' => array( 'shop' ), 'notes' => 'The table is ABSENT until the first boost is attempted — ensure_table() is lazy, so "absent" here means "never used", not "broken install". Boost spend is a real-money actuator and fails SAFE: three independent gates (aieo_social_boost_enabled, aieo_social_boost_sandbox, aieo_social_boost_kill) all default inert, and anything but an explicit sandbox=0 counts as sandbox ON. Platform campaigns are NAMED wf_ so the existing spend-sync and ROI Wizard close ROAS with no new measurement code.', ), array( 'id' => 'social_accounts_health', 'label' => __( 'Connected-account mirror + token health', 'ai-eshop-optimizer' ), 'measures' => __( 'Non-secret account metadata (name, type, follower count) plus token_status flipped to valid / expiring (within 7 days) / needs_reauth (past expiry).', 'ai-eshop-optimizer' ), 'tables' => array(), 'producer' => 'AIEO_Social_Jobs::run_account_health on the recurring action aieo_social_account_health, over AIEO_Social_Store (the option aieo_social_accounts_store, tokens encrypted)', 'producer_class' => 'AIEO_Social_Jobs', 'cron_hooks' => array( 'aieo_social_account_health' ), 'grain' => __( 'site (connected platform account)', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_Social account list, AIEO_Boost_Candidates::connected_platforms (which platforms make an object eligible for a vector), AIEO_Social_Broadcast target resolution', 'site_profiles' => array( 'all' ), 'notes' => 'THE UPSTREAM GATE FOR THE WHOLE SOCIAL ESTATE: with zero connected accounts, eligible_vectors is necessarily empty on every boost candidate and the publish ledger can never fill. Check this before investigating any empty social table. Tokens themselves NEVER land in SQL (openssl aes-256-cbc, wp_salt-derived key).', ), array( 'id' => 'social_outcome_report', 'label' => __( 'Site -> portal social outcome meter', 'ai-eshop-optimizer' ), 'measures' => __( 'Server-verified outcomes (goal completions and social-attributed orders) that our social posts drove, queued locally and reported once a day in a signed idempotent batch.', 'ai-eshop-optimizer' ), 'tables' => array(), 'producer' => 'AIEO_Social_Outcome_Reporter::on_goal (on the aieo_goal_recorded action) enqueues into the non-autoloaded option aieo_social_outcome_queue; ::report drains it', 'producer_class' => 'AIEO_Social_Outcome_Reporter', 'cron_hooks' => array( 'aieo_social_outcome_report' ), 'grain' => __( 'site (outcome event attributed to a social campaign)', 'ai-eshop-optimizer' ), 'consumers' => 'the portal /social/outcomes endpoint. Nothing site-side reads the queue.', 'site_profiles' => array( 'all' ), 'notes' => 'State lives in an OPTION, not a table, so this signal has no row count by design — judge it by the queue length and the cron. Only server-verified events are ever reported: goals fire from server hooks with idempotent event_ids and orders come from the attribution spine; client-JS "visits" are never billable and never reported. QUEUE_CAP 1000; an unreachable portal leaves the batch queued and retries.', ), array( 'id' => 'social_video_association', 'owned_column' => 'social_video_id', 'label' => __( 'Per-object social video association', 'ai-eshop-optimizer' ), 'measures' => __( 'Which video (attachment or URL) an object can be promoted with — the eligibility gate for vertical-video platforms (TikTok, Reels, Shorts).', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_seo_posts' ), 'producer' => 'AIEO_Social_Video (single read/write authority) writing social_video_id / social_video_url, from the Social Video metabox', 'producer_class' => 'AIEO_Social_Video', 'cron_hooks' => array(), 'grain' => __( 'content object', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Boost_Candidates eligible_vectors media rule (image/video platforms need an asset), AIEO_Social_Broadcast and the drivers at publish time', 'site_profiles' => array( 'all' ), 'notes' => 'Deliberately lives on the EXISTING per-object overrides table (already UNIQUE on object_type+object_id) so it costs no new table and no new join — which also means its row count is that of aieo_seo_posts and says nothing about coverage. Resolution order: social_video_id -> social_video_url -> products only: _merchant_product_video_url post meta -> nothing. Both columns are probed once per request and every accessor degrades to "no video" rather than fataling on a site that has not run the social schema self-heal.', ), array( 'id' => 'competition_analytics', 'label' => __( 'Competition participation -> revenue', 'ai-eshop-optimizer' ), 'measures' => __( 'Per competition: entrants, identified vs guest, entrant->order conversion, orders and revenue from entrants, plus the "pull" (units + revenue) of the products the competition is associated with. Across all competitions: participation totals, converting entrants, and the top participants ranked by lifetime value.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_competition_participants' ), 'producer' => 'AIEO_Competition_Analytics::refresh_stats_cache -> ::revenue_by_competition + ::aggregate, precomputed OFF the request path into the non-autoloaded option aieo_competitions_stats_cache. Participant rows themselves are written by AIEO_Competition_Participants from Gravity Forms entries.', 'producer_class' => 'AIEO_Competition_Analytics', 'cron_hooks' => array( 'aieo_competitions_stats_refresh' ), 'grain' => __( 'marketing initiative (competition) x entrant', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_Competitions Insights tab (renders from the cached payload only, never live), the competitions identity bridge into aieo_customer_intelligence', 'site_profiles' => array( 'shop' ), 'notes' => 'THE METRIC IS IN AN OPTION, NOT THE TABLE — the row count here is entrants, not freshness; freshness is generated_at inside aieo_competitions_stats_cache. Both heavy methods join ~1.7k participant emails against aieo_order_stats, which made the Competitions tab take ~10s inline; do not call them from a render path. Every email join needs an explicit COLLATE cast: this table uses the DB-default collation while the analytics tables are pinned to utf8mb4_uca1400_ai_ci (MariaDB 1267 otherwise). All analytics tables are existence-guarded, so figures degrade to 0 before the analytics pipeline has run.', ), array( 'id' => 'ads_plans', 'label' => __( 'Google Ads campaign plan record', 'ai-eshop-optimizer' ), 'measures' => __( 'The durable draft -> created -> activated record of a proposed Google Ads campaign: objective, budget, RSA assets, keywords, and the external resource names once pushed.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_ads_plans' ), 'producer' => 'AIEO_Ads_Plan::save_draft from AIEO_Ads_Composer::compose; ::mark_created after AIEO_Ads_Google_Driver::push creates everything PAUSED; ::mark_activated only RECORDS that a human enabled it. Schema self-installs on option aieo_ads_plans_schema_version.', 'producer_class' => 'AIEO_Ads_Plan', 'cron_hooks' => array(), 'grain' => __( 'marketing initiative (campaign plan)', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_Ads admin, aieo/ads-plans ability, AIEO_Ads_Google_Driver::rollback / ::manage_url', 'site_profiles' => array( 'shop' ), 'notes' => 'THE ONE INVARIANT: the driver hard-codes campaign status PAUSED on every write path and contains no code that can set ENABLED — a human must activate in Google. Composition sources: product_set, boost_slate (the top of the nightly fusion minus vetoes — the interesting one), competition, event, lead_form. RSA asset limits (headline 30, description 90) are measured and trimmed LOCALLY because Google rejects the whole mutate, not just the offending asset.', ), array( 'id' => 'campaign_proposals', 'label' => __( 'Nightly opportunity proposals (suggest-only)', 'ai-eshop-optimizer' ), 'measures' => __( 'Deterministic pattern-mined campaign proposals — audience rule + offer + timing + channel — each with a predicted EV computed from real cohort economics, landing in the operator inbox.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_proposals' ), 'producer' => 'AIEO_Proposal_Engine::scan (MIN_AUDIENCE 20, MAX_NARRATE 8 LLM calls per scan)', 'producer_class' => 'AIEO_Proposal_Engine', 'cron_hooks' => array( 'aieo_proposal_scan' ), 'grain' => __( 'marketing initiative (proposal)', 'ai-eshop-optimizer' ), 'consumers' => 'the proposal inbox admin; ::accept creates a saved audience + a DRAFT workflow and stamps trigger_config.proposal_kind — which is what AIEO_Marketing_Stats reads to set origin=rooster_ai', 'site_profiles' => array( 'shop' ), 'notes' => 'The first survey named this class three times as a CONSUMER and never as a PRODUCER, so its own table was invisible. Scheduled daily at 04:20 via Action Scheduler with a WP-cron fallback (::ensure_scheduled), so it appears in BOTH schedulers — expected, not duplication. HARD RULE: membership, counts, EV and timing are deterministic SQL + vector math; Claude only NAMES and NARRATES, it never computes who gets contacted. Proposals sitting at status=new mean nothing was accepted, which is also why marketing_stats can show origin=manual on every row.', ), array( 'id' => 'bandit_template_weight', 'label' => __( 'Thompson-sampling variant weights', 'ai-eshop-optimizer' ), 'measures' => __( 'Per (trigger x channel x segment) pool of active push templates with >=2 variants: Beta(1+clicked, 1+accepted-clicked) Thompson sampling, 10,000-draw Monte-Carlo P(best), written back as weight = MAX(10, ROUND(100*p)).', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_push_templates' ), 'producer' => 'AIEO_Bandit::run_bandit (after ::run_conversion_join and ::run_rollup)', 'producer_class' => 'AIEO_Bandit', 'cron_hooks' => array( 'aieo_bandit_nightly' ), 'grain' => __( 'marketing initiative (push template variant)', 'ai-eshop-optimizer' ), 'consumers' => 'the dispatcher\'s existing weighted-random pick_template reads `weight` directly — zero dispatcher changes', 'site_profiles' => array( 'all' ), 'notes' => 'Shares aieo_push_templates with the push-dispatch entry, which is why producer is part of the dedup key: the ROWS there are operator-authored content (excluded as config), but the `weight` COLUMN is computed and therefore a signal. REWARD = clicked; converted_strict acts only as a VETO. The bandit reweights but never sends and never deactivates a variant — deactivation is only ever RECOMMENDED. WEIGHT_FLOOR 10 keeps exploration alive; cold arms self-explore via Beta(1,1). Note aieo_bandit_nightly runs purely via Action Scheduler and is deliberately absent from the WP-cron array.', ), array( 'id' => 'vector_priors', 'label' => __( 'Social/campaign vector priors', 'ai-eshop-optimizer' ), 'measures' => __( 'RETIRED 2026-07-26. Was intended as the per-vector prior distribution feeding forecast confidence. Never written, never read; the table is no longer created.', 'ai-eshop-optimizer' ), 'tables' => array(), 'producer' => 'RETIRED — the CREATE TABLE was removed from aieo_social_install_schema() (includes/dmm/sql/social-schema.php) on 2026-07-26. It had no writer and no reader for its entire life.', 'producer_class' => '', 'cron_hooks' => array(), 'grain' => __( 'marketing initiative (campaign vector)', 'ai-eshop-optimizer' ), 'consumers' => '', 'consumed' => false, 'site_profiles' => array( 'all' ), 'notes' => 'TOMBSTONE — kept deliberately so nobody rebuilds this under a new name. THE SUCCESSOR ALREADY SHIPS, off-site: om-optimizer-hub\'s `social_fleet_priors` (includes/db/class-hub-db.php, served by includes/api/class-hub-iq-api.php). That is the right home and the reason this one never got a writer: a prior is only worth having if it is pooled ACROSS the fleet, and one shop cannot accumulate enough evidence per (platform, object_class, audience_band, metric) cell to beat the heuristic it was meant to replace. Existing sites keep an empty table — harmless, and not worth a migration to drop. If forecast confidence needs priors, call the hub; do not resurrect a per-site table.', ), array( 'id' => 'remote_export_state', 'label' => __( 'Portal analytics export + purge gate', 'ai-eshop-optimizer' ), 'measures' => __( 'Which analytics rows have been confirmed shipped to the operator\'s remote analytics DB, and the high-water timestamp that gates retention deletes.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_remote_export_state' ), 'producer' => 'AIEO_Remote_Export_Manager + AIEO_Remote_Export_Cron', 'producer_class' => 'AIEO_Remote_Export_Manager', 'cron_hooks' => array( 'aieo_remote_export_run' ), 'grain' => __( 'site x source table', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Archive_Handlers::archive_then_delete_by_ts — asks ::can_purge_through() BEFORE any hot-row delete, holding the delete when the remote is behind', 'site_profiles' => array( 'all' ), 'notes' => 'SAFETY-CRITICAL DESPITE BEING EMPTY ON MOST SITES: zero rows means no remote is configured, and retention purges then proceed UNGUARDED. Exports engagement_daily, attribution_daily, conversion_buckets, blog_engagement_daily, cart_removal_daily, addcart_events, reco_events, sessions_statics. Do not "clean up" this table — an empty one is a working default, but a stale one silently blocks all archival.', ), array( 'id' => 'seo_opportunities', 'label' => __( 'SEO opportunity grid (traffic x on-site conversion)', 'ai-eshop-optimizer' ), 'measures' => __( 'One advisory row per object: quadrant (hidden_gem / intent_mismatch / defend / prune / insufficient), traffic_score, conversion_score, behavioural cornerstone_score (entry strength + onward routing + engagement), priority 0-100, a plain-language recommendation and the signal_mix that produced it.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_seo_opportunities' ), 'producer' => 'AIEO_SEO_Opportunity_Scorer::maybe_rebuild / ::rebuild — set-based, offline, no front-end cost', 'producer_class' => 'AIEO_SEO_Opportunity_Scorer', 'cron_hooks' => array( 'aieo_seo_opportunities_rebuild' ), 'grain' => __( 'product AND content object', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_SEO_Opportunities admin, AIEO_Boost_Candidates (seo_opportunity component); AIEO_SEO_Pillars consumes AIEO_SEO_Opportunity_Scorer::config() only, not this table', 'site_profiles' => array( 'all' ), 'notes' => 'THE SIGNAL THIS REGISTRY WAS BUILT FOR. It already implements hidden-winner detection, poor-converter detection, operator-tunable thresholds (SEO -> Opportunities) and a shop/catalog/content profile that redistributes the view->cart weight into engagement/retention on sites with no cart. It sat dormant for a long time because nothing scheduled it — DO NOT BUILD A SECOND ONE. Self-throttled to ~20h (option aieo_seo_opportunities_built_at); gated on seo.enabled, so a disabled SEO engine reports skipped rather than running. Note the admin "Rebuild now" calls ::rebuild() directly and therefore does NOT stamp the built-at option — a hand-run site shows rows with no last-run, which is why the cron_missing flag matters more than last_run here.', 'last_run_option' => 'aieo_seo_opportunities_built_at', ), array( 'id' => 'seo_scores', 'owned_column' => 'demand_score', 'label' => __( 'Per-object SEO score + fix backlog', 'ai-eshop-optimizer' ), 'measures' => __( 'Per post/product: the computed SEO score, per-pillar breakdown and the list of open deterministic fixes (canonical, keyphrase, title/description, image alt) the auto-fix assistant can act on.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_seo_scores', 'aieo_seo_posts' ), 'producer' => 'AIEO_SEO_Score / AIEO_SEO_Pillars (scores + pillar breakdown); canonical index maintained by AIEO_SEO_Store; the search-demand columns (search_volume_total, best_position, striking_count, search_demand_score) are written by AIEO_DFS_Rankings::materialise_aggregates from the DataForSEO pull; schema in includes/dmm/sql/seo-schema.php', 'producer_class' => 'AIEO_SEO_Score', 'cron_hooks' => array( 'aieo_seo_async_drain' ), 'cron_kind' => 'on_demand', 'grain' => __( 'content object', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_SEO_Health, AIEO_SEO_Admin_Columns, aieo/seo-list-fixes + seo-fix-* abilities, AIEO_Content_Vitals, AIEO_Boost_Candidates (search_demand + commerce_demand components), AIEO_SEO_Write_Queue (prioritises by striking_count), aieo/social-candidates ability, cart_frequencies link_to_view', 'site_profiles' => array( 'all' ), 'notes' => 'aieo_seo_posts.canonical_url is the plugin\'s canonical URL->object index and is what makes content rollups resolve KB / custom-router pages that url_to_postid() cannot. WATCH THE SEARCH-DEMAND COLUMNS SEPARATELY: they default to 0 and are only ever non-zero after AIEO_DFS_Rankings has pulled DataForSEO into aieo_dfs_rankings. Where that table does not exist (the reference shop) search_demand_score is a column of literal zeros — which the boost scorer cannot distinguish from "measured, and low", so it does not drop the component, it dilutes every score by its 20% weight. aieo_dfs_rankings is NOT yet a catalogued signal of its own.', ), array( 'id' => 'seo_404_log', 'label' => __( '404 log + redirect map', 'ai-eshop-optimizer' ), 'measures' => __( 'Every unresolved request (path, hits, first/last seen, referrer) and the operator/AI-authored redirect that resolves it.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_seo_404_log', 'aieo_seo_redirects' ), 'producer' => 'AIEO_SEO_Redirects (template_redirect capture + admin/AI authoring)', 'producer_class' => 'AIEO_SEO_Redirects', 'cron_hooks' => array(), 'grain' => __( 'URL', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_SEO_Redirects admin; redirect serving runs from an indexed lookup in the mu-plugin', 'site_profiles' => array( 'all' ), 'notes' => 'TWO SIGNALS SHARE THIS ENTRY BY DESIGN: the 404 log is a demand signal (what people ask for and do not get) and aieo_seo_redirects.hits is a usage counter. Redirect hit counts are SAMPLED 1-in-10 (bump() adds 10) so a hot redirect does not write on every request — treat them as approximate, never as exact traffic. Rules are also auto-created by AIEO_SEO_Slug_Monitor on slug/parent change on every save path (the block editor REST save, the classic editor, Quick Edit, WP-CLI, cron, the WooCommerce REST API, a WPML translation save; pre_post_update captures the old permalink in the language of the post, post_updated writes the 301), with no cron of its own. A 404 log that fills while SUM(redirected) stays 0 means the operator loop is not being worked.', ), array( 'id' => 'gsc_history', 'label' => __( 'Search Console history (rotating annual)', 'ai-eshop-optimizer' ), 'measures' => __( 'date x query and date x page: clicks, impressions, CTR, average position — the daily series behind the Search Queries report, with pages resolved to the post or product behind them.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_gsc_daily' ), 'producer' => 'AIEO_GSC_History::maybe_sync from AIEO_Aggregate_Cron::run (self-throttled ~20h, re-pulls the trailing 7 days because Google revises them), backfilled by bin/post-deploy-gsc-history-backfill.php', 'producer_class' => 'AIEO_GSC_History', 'cron_hooks' => array( 'aieo_aggregate_rollups' ), 'grain' => __( 'day x search term / page', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_GSC_History::series + ::year_on_year, the Search Queries report, aieo/get-search-trend ability', 'site_profiles' => array( 'all' ), 'gate_option' => 'aieo_gsc_tokens', 'gate_note' => 'Search Console is not connected (no OAuth token in aieo_gsc_tokens)', 'notes' => 'WHY IT EXISTS: Search Console serves roughly 16 months and then the data is GONE, so a year-on-year comparison can never be reconstructed after the fact — it only exists if each day is written down as it passes. That matters here because most of this catalogue\'s terms are SEASONAL: "down 40% on last month" is noise, "down 40% on the same weeks last year" is a decision. ROTATING at 400 days (filter aieo_gsc_history_retention_days), not 365: a 30-day window compared against the same window a year ago needs 365 + the window width, and a hard 365 truncates the older half of the comparison at the moment it is drawn. UPSERTS rather than INSERT IGNORE — unlike the Google Reviews store it is modelled on, a metric is revised after publication and the newer figure must win. Asked for as dimensions [date, query] and [date, page]: putting `date` first is what makes one call a per-day series instead of one aggregate. Google WITHHOLDS rare queries for privacy, so the query rows will never sum to the property total; that is Google, not a gap here.', ), array( 'id' => 'dfs_ranked_keywords', 'label' => __( 'DataForSEO ranked-keywords snapshot', 'ai-eshop-optimizer' ), 'measures' => __( 'Every keyword the domain ranks for — keyword, position, URL, search volume, ETV, intent — pulled weekly and swapped in atomically.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_dfs_rankings' ), 'producer' => 'AIEO_DFS_Rankings::pull (PAGE_SIZE 1000, MAX_PAGES 20 = 20k keyword cap), entered via ::maybe_pull from AIEO_Aggregate_Cron::run', 'producer_class' => 'AIEO_DFS_Rankings', 'cron_hooks' => array( 'aieo_aggregate_rollups' ), 'grain' => __( 'site (keyword x resolved object)', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DFS_Rankings::materialise_aggregates (writes the four seo_posts columns), ::keywords_for, ::striking_distance, ::serp_context; AIEO_SEO_Write_Queue; AIEO_SEO_Fixer::top_keyword; aieo/dfs-rankings + dataforseo-status abilities', 'site_profiles' => array( 'all' ), 'gate_option' => 'aieo_dataforseo_auth', 'gate_note' => 'no DataForSEO credential has been entered (option aieo_dataforseo_auth)', 'notes' => 'THE SINGLE UPSTREAM THAT MAKES SIX DOWNSTREAM SIGNALS EMPTY. pull() self-gates on a DataForSEO credential, so on a site that never entered one the TABLE IS NEVER CREATED — and "absent" here is the root cause of: a zeroed search_demand fusion component, an empty striking-distance queue, a keyphrase autofix with nothing to apply, and an AI write queue that never drafts. Weekly interval 561600s (6.5 days); spend capped by seo.dfs_budget_usd (default $5/month) and recorded via ::record_spend. Check ::table_exists() before reading anything downstream.', 'last_run_option' => 'aieo_dfs_rankings_pulled_at', ), array( 'id' => 'seo_search_demand', 'owned_column' => 'search_demand_score', 'label' => __( 'Google search demand per page (volume / position / striking distance)', 'ai-eshop-optimizer' ), 'measures' => __( 'Per object: search_volume_total (sum of ranked keyword volumes), best_position (MIN), striking_count (keywords at positions 8-20 — the one-push-from-page-1 queue) and search_demand_score (0-100 percentile of volume-weighted coverage).', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_seo_posts' ), 'producer' => 'AIEO_DFS_Rankings::materialise_aggregates, fed by ::pull from aieo_dfs_rankings', 'producer_class' => 'AIEO_DFS_Rankings', 'cron_hooks' => array( 'aieo_aggregate_rollups' ), 'grain' => __( 'content object', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Boost_Candidates::collect_seo_posts (search_demand component, ~20% weight), AIEO_SEO_Write_Queue::candidates (striking-distance first), AIEO_DFS_Rankings::striking_distance in SEO -> Opportunities, AIEO_SEO_Fixer::top_keyword', 'site_profiles' => array( 'all' ), 'gate_option' => 'aieo_dataforseo_auth', 'gate_note' => 'no DataForSEO credential has been entered, so these four columns have never been written (shared with the rankings pull)', 'notes' => 'Catalogued separately from seo_scores even though both write aieo_seo_posts, because the OWNER differs and so does the failure mode. These four columns DEFAULT TO 0 and are only non-zero after a DataForSEO pull. A consumer cannot distinguish "0 = never measured" from "0 = measured, no demand" — so on a site with no DFS credential the boost scorer does not drop the component, it DILUTES every score by its weight. If you add a consumer, gate it on AIEO_DFS_Rankings::table_exists().', ), array( 'id' => 'backlink_profile', 'label' => __( 'Referring-domain backlink snapshot + disavow list', 'ai-eshop-optimizer' ), 'measures' => __( 'Per referring domain: backlink count, spam_score 0-100, domain rank, first_seen (replaced wholesale on each refresh); plus the operator\'s persistent disavow set (domain, reason, source, spam_score, added_at).', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_backlinks', 'aieo_disavow' ), 'producer' => 'AIEO_DFS_Backlinks::refresh($limit=300) over the DataForSEO Backlinks API (summary cached in option aieo_backlinks_summary); disavow rows by ::disavow_add / ::disavow_remove / ::disavow_all_toxic', 'producer_class' => 'AIEO_DFS_Backlinks', 'cron_hooks' => array(), 'grain' => __( 'site (referring domain)', 'ai-eshop-optimizer' ), 'consumers' => 'aieo/seo-backlinks abilities (::referring_domains, ::toxic, ::cached_summary), AIEO_DMM_SEO_Backlinks admin, ::disavow_file for the generated .txt', 'site_profiles' => array( 'all' ), 'gate_option' => 'aieo_dataforseo_auth', 'gate_note' => 'no DataForSEO credential has been entered (shared with the rankings pull)', 'notes' => 'REFRESH-ON-BUTTON-PRESS BY DESIGN — there is no cron for it at all, so an unscheduled state here is correct rather than a bug. Shares the DataForSEO credential and the same monthly budget as the rankings pull, so it is empty wherever that credential was never entered. The disavow list persists deliberately ACROSS backlink snapshots because Google will not tell us what is already disavowed, and there is NO API submission path anywhere: file generation plus manual handoff to Google\'s UI-only tool is the whole design.', 'last_run_option' => 'aieo_backlinks_refreshed_at', ), array( 'id' => 'gsc_search_analytics', 'label' => __( 'Google Search Console queries / pages / clicks / impressions', 'ai-eshop-optimizer' ), 'measures' => __( 'Ground-truth Google organic performance: which queries and pages actually earn clicks and impressions, plus URL Inspection (why a page is not indexed).', 'ai-eshop-optimizer' ), 'tables' => array(), 'producer' => 'AIEO_GSC::query / ::top_queries / ::inspect_url — 3-legged OAuth2, read-only scope webmasters.readonly; credentials in options aieo_gsc_settings / aieo_gsc_tokens', 'producer_class' => 'AIEO_GSC', 'cron_hooks' => array(), 'grain' => __( 'site (query x page)', 'ai-eshop-optimizer' ), 'consumers' => 'aieo/seo-search-queries ability, AIEO_DMM_SEO_Search_Queries admin', 'site_profiles' => array( 'all' ), 'gate_option' => 'aieo_gsc_tokens', 'gate_note' => 'Search Console OAuth has never been completed (option aieo_gsc_tokens)', 'notes' => 'NOTHING IS CACHED TO A TABLE — every consumer read is a live API call, so this signal has no row count and no cron by design; judge it purely by whether the OAuth options are set. Deliberately no write scope and no disavow here (Google exposes no disavow API). This is the only source of TRUE Google click data in the plugin; the DataForSEO estate is third-party estimation, so do not treat the two as interchangeable.', ), array( 'id' => 'seo_head_cache', 'label' => __( 'Materialised rendered fragment', 'ai-eshop-optimizer' ), 'measures' => __( 'The fully-rendered head HTML (title, canonical, robots, OG, Twitter, JSON-LD) per (object_type, object_id, lang), with a content_hash, so the runtime does one PK read and echo instead of computing at render.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_seo_head_cache' ), 'producer' => 'AIEO_SEO_Head_Builder::build on save_post / edited_term / product update / set_object_terms, drained by ::drain_dirty + ::run_backfill in the data-prep seo_head_cache stage', 'producer_class' => 'AIEO_SEO_Head_Builder', 'cron_hooks' => array( 'aieo_dmm_data_prep_run' ), 'grain' => __( 'content object x language', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_SEO_Head::output on the front end (the whole point), SEO -> General diagnostics, the ratings/schema bridge', 'site_profiles' => array( 'all' ), 'notes' => 'In scope despite being a rendered cache (see the exclusion rules) because its COVERAGE is a real diagnostic: an object with no head-cache row is invisible to the sitemap. It is NEVER built on a read path by contract — a cache miss serves the fallback, it does not build. There is no built_at column; freshness is the `updated` column plus content_hash.', ), array( 'id' => 'seo_canonical_indexable', 'owned_column' => 'canonical_url', 'label' => __( 'Canonical URL + indexable flag (the sitemap/serve gate)', 'ai-eshop-optimizer' ), 'measures' => __( 'The materialised canonical path per object and a boolean indexable that decouples sitemap inclusion from the robots noindex directive.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_seo_posts' ), 'producer' => 'AIEO_SEO_Head_Builder::materialise_canonical + ::compute_robots during the head build; also settable by AIEO_SEO_Fixer (canonical / indexable fixes) and AIEO_SEO_Store::save', 'producer_class' => 'AIEO_SEO_Head_Builder', 'cron_hooks' => array( 'aieo_dmm_data_prep_run' ), 'grain' => __( 'content object', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_SEO_Sitemap::build (WHERE canonical_url <> \'\' AND indexable = 1 AND sitemap > 0 AND sitemap_cut = 0, the sitemap rules\' decision and a content type\'s limit: AIEO_SEO_Sitemap_Rules, AIEO_SEO_Sitemap_Priority; ordered by priority_score), AIEO_Content_Vitals URL->object resolution, AIEO_DFS_Rankings::resolve_path, AIEO_SEO_Redirects target validation', 'site_profiles' => array( 'all' ), 'notes' => 'THE HIGHEST-LEVERAGE COLUMN IN THE SEO ESTATE and the one whose row count lies loudest: aieo_seo_posts holds a row per object, but canonical_url is set only on those actually head-built. Attachment rows in particular can carry indexable=1 with NO canonical — present in the store, invisible to the sitemap, while occupying its largest slice. Content rollups resolve URLs THROUGH this column, so a page without a canonical is silently unresolvable to content_engagement_daily as well. Always measure coverage (canonical_url <> \'\'), never COUNT(*).', ), array( 'id' => 'seo_sitemap', 'label' => __( 'Static XML sitemap shards + robots.txt', 'ai-eshop-optimizer' ), 'measures' => __( 'Which URLs are advertised to crawlers, in 2000-URL shards written atomically to uploads/aieo-sitemaps/, plus a locations shard for physical stores.', 'ai-eshop-optimizer' ), 'tables' => array(), 'producer' => 'AIEO_SEO_Sitemap::build — data-prep stage seo_sitemap, ordered AFTER seo_head_cache so canonicals exist', 'producer_class' => 'AIEO_SEO_Sitemap', 'cron_hooks' => array( 'aieo_dmm_data_prep_run' ), 'grain' => __( 'site', 'ai-eshop-optimizer' ), 'consumers' => 'nginx static serving / the PHP fallback in ::maybe_serve; robots.txt advertisement via ::robots_txt', 'site_profiles' => array( 'all' ), 'notes' => 'STATIC FILES, NO TABLE — row count is meaningless here; the marker option is the only freshness evidence. Known gap worth not re-deriving: sitemap_priority is 0 on every seo_posts row on the reference shop, so the demand-ranked priority the design calls for is not actually emitted and every URL falls back to the default. Files are REMOVED when AIEO is not the active SEO provider, so a competitor plugin can take back over cleanly. Writes can also fail silently if the uploads dir is root-owned.', 'last_run_option' => 'aieo_seo_sitemap_built_at', ), array( 'id' => 'seo_ai_write_queue', 'owned_column' => 'ai_state', 'label' => __( 'SERP-informed AI title/description/body drafts', 'ai-eshop-optimizer' ), 'measures' => __( 'Draft suggested_title / suggested_description / suggested_keyphrase / suggested_body written against the LIVE SERP for the page\'s target keyword, held at ai_state=suggested for operator approval (or auto-applied under seo.autopilot).', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_seo_posts' ), 'producer' => 'AIEO_SEO_Write_Queue::run called from AIEO_Aggregate_Cron::run; capped by seo.ai_write_daily (default 5/night) and the DataForSEO monthly budget', 'producer_class' => 'AIEO_SEO_Write_Queue', 'cron_hooks' => array( 'aieo_aggregate_rollups' ), 'grain' => __( 'content object', 'ai-eshop-optimizer' ), 'consumers' => 'SEO -> Health approve/dismiss UI (::pending, ::approve, ::dismiss); AIEO_SEO_Store::save on approve', 'site_profiles' => array( 'all' ), 'notes' => 'DORMANCY IS UPSTREAM, NOT HERE: candidates() picks striking-distance pages first, and those come from the possibly-absent aieo_dfs_rankings; with no SERP context available() gates the run off entirely, so ai_state stays "none" on every row and the queue looks broken when it is merely starved. The prompt is enriched with AIEO_Goal_Tracker::goal_context_for so lead-gen sites get conversion-aware copy. Because state lives in COLUMNS on a table shared with several other signals, row count here says nothing — measure ai_state <> \'none\'.', ), array( 'id' => 'seo_autofix_deterministic', 'owned_column' => 'suggested_title', 'label' => __( 'Free deterministic SEO autopilot (canonical + keyphrase)', 'ai-eshop-optimizer' ), 'measures' => __( 'Applies the no-AI, no-credit fixes computable from the site\'s own data: materialise canonical, and set focus_keyphrase from the page\'s own top-ranked Google keyword (stamped focus_keyphrase_source=aieo_autofix).', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_seo_posts' ), 'producer' => 'AIEO_SEO_Fixer::run_autopilot(200) called from AIEO_Aggregate_Cron::run; gated by DMM setting seo.autofix_deterministic (default OFF)', 'producer_class' => 'AIEO_SEO_Fixer', 'cron_hooks' => array( 'aieo_aggregate_rollups' ), 'grain' => __( 'content object', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_SEO_Score::build (fix_count drops immediately), SEO Health fix list, aieo/seo-fix abilities, ::undo', 'site_profiles' => array( 'all' ), 'notes' => 'DEFAULT OFF, which is a decision and not an oversight — it writes to live content. The keyphrase half additionally depends on the DataForSEO rankings, so on a site with no credential only the canonical half can ever act. SOURCE-STAMPING IS WHAT MAKES UNDO SAFE: ::undo only reverts values still stamped aieo_autofix, so a manual edit afterwards is never clobbered. "indexable" is explicit-only and deliberately never autopiloted.', ), array( 'id' => 'chat_content_gaps', 'label' => __( 'Chat-derived content gaps', 'ai-eshop-optimizer' ), 'measures' => __( 'Questions customers ask that the site answers poorly, rolled up by a normalised canonical key: occurrences, zero_product_turns (the answer surfaced no products), unhappy_turns (rating < 40), avg_rating and a demand_weight.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_chat_content_gaps' ), 'producer' => 'AIEO_SEO_Content_Gaps::rebuild($max_rows=40000), called from AIEO_Chat_Analytics::refresh_stats_cache; reads aieo_chat_history OFFLINE', 'producer_class' => 'AIEO_SEO_Content_Gaps', 'cron_hooks' => array( 'aieo_chat_analytics_stats_refresh' ), 'grain' => __( 'site (normalised question key), optionally mapped to one content object', 'ai-eshop-optimizer' ), 'consumers' => 'aieo/seo-content-gaps ability (::top), the admin gap list; operator-mapped gaps may advisorily nudge the mapped page via the aieo_seo_demand_score filter', 'site_profiles' => array( 'all' ), 'notes' => 'Its own docblock calls it "a SIGNAL store", and the first survey still omitted it. This is the demand loop that turns unanswered chat questions into ranked SEO content candidates — the non-obvious payoff of logging chat. ZERO PER-MESSAGE COST: the rollup is offline over stored history, never an extra model call. It NEVER writes to posts/products/terms/meta by explicit design — signal only, the operator decides what to write. Requires the AI chat to be live and logging.', ), array( 'id' => 'form_insights', 'label' => __( 'Form choice-field audience distributions', 'ai-eshop-optimizer' ), 'measures' => __( 'Turns every checkbox / radio / select / multiselect on a Gravity Form into a self-declared audience distribution: option label, count, and share OF RESPONDENTS to that field.', 'ai-eshop-optimizer' ), 'tables' => array(), 'producer' => 'AIEO_Form_Insights::distributions -> ::tally, computed on demand from Gravity Forms\' own entry/meta tables and cached in a transient (CACHE_TTL 900s); flushed by ::flush on entry save', 'producer_class' => 'AIEO_Form_Insights', 'cron_hooks' => array(), 'grain' => __( 'site (form field option)', 'ai-eshop-optimizer' ), 'consumers' => 'aieo/form-segments ability, AIEO_DMM_Conversion_Goals admin, AIEO_Social_AI_Writer (audience brief for copy), AIEO_Ads_Composer', 'site_profiles' => array( 'content', 'catalog' ), 'notes' => 'THE PRIMARY AUDIENCE SIGNAL ON SITES WITH NO ORDER HISTORY — which is why a shop-centric reader misses it. No table: it is a live read with a transient, so it has no row count and no cron. COUNTING RULE THAT MUST NOT BE "FIXED": on a multi-select one person choosing three options adds three counts but ONE respondent, so shares deliberately sum past 100%. The honest reading is "38% of the people who answered grow olives"; dividing by total selections would understate every option. Goes straight at the GF entry/meta tables rather than paging GFAPI::get_entries(), because a 50,000-entry form would otherwise cost a hydrate-and-discard of every row. MAX_OPTIONS 40 before the tail folds into "other". ::available() is false without Gravity Forms.', ), array( 'id' => 'session_attribution_bridge', 'label' => __( 'Session analytics read API', 'ai-eshop-optimizer' ), 'measures' => __( 'Not a store — the single query facade behind the behavioural chat abilities: addcart funnel by landing page, attribution with engagement, cart-removal patterns, blog engagement leaderboard, blog-to-product conversion, need vs conversion, rating vs conversion, abandoned carts, session journey for an add-to-cart.', 'ai-eshop-optimizer' ), 'tables' => array(), 'producer' => 'AIEO_Session_Attribution_Bridge (read-only over engagement_daily, attribution_daily, blog_engagement_daily, cart_removal_daily, landing_funnel_daily, sessions_statics, addcart_events, rating_stats)', 'producer_class' => 'AIEO_Session_Attribution_Bridge', 'cron_hooks' => array(), 'grain' => __( 'site (mixed: product / session / content)', 'ai-eshop-optimizer' ), 'consumers' => '9 Claude abilities: addcart-funnel, attribution-with-engagement, cart-removal-analysis, blog-engagement-leaderboard, blog-to-product-conversion, need-conversion-correlation, rating-conversion-correlation, abandoned-cart-triage, session-journey-for-addcart', 'site_profiles' => array( 'all' ), 'notes' => 'READ THIS BEFORE WRITING A NEW BEHAVIOURAL AGGREGATE — it is the intended entry point for new behaviour questions, and the place a duplicate rollup would most easily be built by accident. Writes nothing and schedules nothing, so it has no rows and no cron by construction: its health is the health of the rollups underneath it.', ), array( 'id' => 'content_consumption_facts', 'label' => __( 'Content consumption reporting VIEW', 'ai-eshop-optimizer' ), 'measures' => __( 'A wpDataTables-ready flattening of per-content daily consumption (the 3-hour view buckets and day bands) for report builders.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_content_consumption_facts' ), 'producer' => 'AIEO_Content_Vitals (ensures the VIEW alongside the content-vitals build)', 'producer_class' => 'AIEO_Content_Vitals', 'cron_hooks' => array( 'aieo_aggregate_rollups' ), 'grain' => __( 'content object x day', 'ai-eshop-optimizer' ), 'consumers' => 'wpDataTables reports (outside the plugin). No PHP reader inside it.', 'consumed' => false, 'site_profiles' => array( 'all' ), 'notes' => 'A VIEW, so it is deliberately NEVER row-counted by probe_tables() — counting it would run the underlying aggregation, which is the expensive thing this registry avoids. The landing/exit facts entry already name-checked it; it is catalogued here in its own right so it is discoverable. Its sibling views are aieo_landing_facts and aieo_exit_facts.', ), array( 'id' => 'ab_impressions', 'label' => __( 'A/B test impressions + variant funnel', 'ai-eshop-optimizer' ), 'measures' => __( 'One row per (test_id, session) exposure: surface, variant, session_hash, user_id, device, meta (geo / traffic source / custom dimensions) and created_at — the impression side of the checkout-layout experiment funnel.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_ab_impressions' ), 'lazy_tables' => array( 'aieo_ab_impressions' ), 'producer' => 'AIEO_DMM_Checkout_Layout (lazy ensure_impressions_table on first use, schema gated on option aieo_ab_impressions_db_version)', 'producer_class' => 'AIEO_DMM_Checkout_Layout', 'cron_hooks' => array(), 'grain' => __( 'session x experiment variant', 'ai-eshop-optimizer' ), 'consumers' => 'the checkout-layout admin funnel queries', 'site_profiles' => array( 'shop' ), 'notes' => 'THE PLUGIN ALREADY HAS AN A/B IMPRESSION STORE — check here before building an experimentation framework. UNIQUE on (test_id, session_hash) prevents double-counting the same browser within a test, so impressions are UNIQUE EXPOSURES, not page views; do not sum them against a pageview denominator. Indexed on (test_id, variant, created_at) for the funnel. Lazily created, so an absent table means "no experiment has run", not a broken install. Currently wired only to the checkout surface even though test_id/surface are generic columns.', ), array( 'id' => 'workflow_runs', 'label' => __( 'Workflow execution ledger', 'ai-eshop-optimizer' ), 'measures' => __( 'One row per workflow execution: which workflow ran, for which subject, when, and how it resolved — the per-run substrate the workflow funnel aggregates.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_workflow_runs' ), 'producer' => 'AIEO_Workflow_Runtime; schema in includes/dmm/sql/subjects-schema.php', 'producer_class' => 'AIEO_Workflow_Runtime', 'cron_hooks' => array(), 'grain' => __( 'marketing initiative (workflow run x subject)', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Workflow_Stats::rebuild, the Campaigns builder funnel', 'site_profiles' => array( 'shop' ), 'notes' => 'THE MISSING INPUT BEHIND AN EMPTY aieo_workflow_stats: that rollup can complete successfully every night and still write zero rows when nothing has executed here. Diagnose an empty workflow funnel in this order — workflows defined, then runs here, then dispatch legs, then stats. Catalogued because "0 rows" at this level is a different and more actionable finding than "0 rows" at the rollup.', ), array( 'id' => 'term_relations', 'label' => __( 'Internal link / term relation graph', 'ai-eshop-optimizer' ), 'measures' => __( 'Generic edges between any (taxonomy, term) source and any (taxonomy, term) OR (post_type, post) target — the internal-link graph behind link centrality and related-content surfaces.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_dmm_term_relations' ), 'producer' => 'AIEO_DMM_Term_Relations::set_targets / ::add_edge (operator + programmatic authoring); schema in includes/dmm/sql/dmm-schema.php', 'producer_class' => 'AIEO_DMM_Term_Relations', 'cron_hooks' => array(), 'grain' => __( 'content object (graph edge)', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_Related_Tab + AIEO_DMM_Post_Relations_Metabox (authoring), the brand-related / post-related-brands / taxonomy-related-articles block renders, AIEO_Post_Relations_Shortcodes, AIEO_DMM_Navigator, the *_extras modules (brand / category / need / supplier / tag)', 'site_profiles' => array( 'all' ), 'notes' => 'IN SCOPE EVEN THOUGH THE EDGES ARE AUTHORED, because the related-content and navigator surfaces that rank off them are catalogued signals. NOT the pillar-score link graph: AIEO_SEO_Pillars deliberately derives centrality from core term_relationships (product_cat / product_brand / product_tag memberships), so an empty table here does NOT zero any pillar score. BIDIRECTIONAL INVARIANT: term-to-term edges are stored as TWO rows (one per direction) and ::set_targets maintains the inverse automatically, so row count is roughly double the conceptual edge count. Term-to-post edges store ONE direction; the reverse is served from the aieo_dmm_idx_tr_tgt index instead of duplicated rows.', ), array( 'id' => 'loyalty_points', 'label' => __( 'Loyalty points ledger', 'ai-eshop-optimizer' ), 'measures' => __( 'Accrued and redeemed loyalty points per customer, with the class assignment that determined the earn rate.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_dmm_loyalty_points' ), 'producer' => 'AIEO_DMM_Loyalty_Classes; schema in includes/dmm/sql/dmm-schema.php', 'producer_class' => 'AIEO_DMM_Loyalty_Classes', 'cron_hooks' => array(), 'grain' => __( 'customer', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_DMM_Loyalty_Classes admin, AIEO_Loyalty_Connection', 'site_profiles' => array( 'shop' ), 'notes' => 'A LEDGER, not a setting — the class definitions are operator config and excluded, but the accrued balance is computed from order history and is therefore a signal. Distinct from the ERP-sourced soft1_loyalty_points on aieo_user_vitals: one is this plugin\'s own accrual, the other is what the ERP believes. They can legitimately disagree; do not reconcile them without deciding which is authoritative.', ), array( 'id' => 'consent_log', 'label' => __( 'Consent decisions log', 'ai-eshop-optimizer' ), 'measures' => __( 'Per visitor: the consent decision recorded (categories granted/denied) and when — the GDPR evidence record, and the gate that determines whether anything else in this registry is allowed to observe a session.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_consent_log', 'aieo_consent_cookies' ), 'producer' => 'AIEO_Consent (AJAX aieo_consent_log, logged-in and nopriv)', 'producer_class' => 'AIEO_Consent', 'cron_hooks' => array(), 'grain' => __( 'session', 'ai-eshop-optimizer' ), 'consumers' => 'the consent admin / GDPR evidence export; Consent-Mode signalling to the measurement layer', 'site_profiles' => array( 'all' ), 'notes' => 'READ THIS BEFORE CONCLUDING A BEHAVIOURAL SIGNAL IS BROKEN. Consent is the upstream gate for the entire session estate: the tracker only sets its cookie after the gate passes, goals are only recorded for cookie-bearing sessions, and the identity substrate is starved when consent is sparse. A low consent rate looks exactly like a broken tracker from downstream. Catalogued as a signal because the decision RATE is a measurement, not because the config is.', ), array( 'id' => 'chat_intelligence', 'label' => __( 'Customer chat history + embeddings', 'ai-eshop-optimizer' ), 'measures' => __( 'Every chat turn (question, answer, retrieved products, tokens, cost, session tie) plus the vector store the retrieval runs on — including the behavioural SUBJECT vectors written as post_type=\'user\'.', 'ai-eshop-optimizer' ), 'tables' => array( 'aieo_chat_history', 'aieo_chat_embeddings' ), 'producer' => 'AIEO chat REST API + AIEO_Database_Manager; embeddings by AI_eShop_Chat_Embedding_Manager (incremental — only missing vectors per model), backfill by AIEO_Embedding_Backfill', 'producer_class' => 'AI_eShop_Chat_Embedding_Manager', 'cron_hooks' => array( 'aieo_chat_cleanup_cron', 'aieo_chat_generate_single_embedding', 'aieo_monthly_model_refresh' ), 'grain' => __( 'session x chat turn / object x model', 'ai-eshop-optimizer' ), 'consumers' => 'AIEO_Chat_Analytics + AIEO_DMM_AI_Chat_Analytics, AIEO_SEO_Content_Gaps (mines unanswered questions into content candidates), AIEO_Behavioural_Vector (subject vectors), chat retrieval', 'site_profiles' => array( 'all' ), 'notes' => 'The content-gap loop is the non-obvious consumer: chat questions the catalogue could not answer become demand-ranked SEO content candidates.', ), ); } }