__('Attribution Dashboard', 'ai-eshop-optimizer'),
'description' => __('Know exactly which traffic source, recommendation rail, or archive page drove every add-to-cart and order. Replace gut feel with hard data on what is working — and stop spending on what is not.', 'ai-eshop-optimizer'),
'bullets' => array(
__('Per-channel adds, orders, and conversion rates (paid, organic, push, social, referral, direct).', 'ai-eshop-optimizer'),
__('Reco-assist split — see which sources benefit most from on-site recommendations.', 'ai-eshop-optimizer'),
__('Branch / surface / slot breakdowns with searchable detail rows + CSV export.', 'ai-eshop-optimizer'),
),
);
}
public function register_hooks() {
add_action('admin_post_aieo_attribution_clear', array($this, 'handle_clear'));
add_action('admin_post_aieo_attribution_export', array($this, 'handle_export'));
add_action('wp_ajax_aieo_attribution_rows', array($this, 'ajax_rows'));
}
public function enqueue_admin_assets() {
$rel = 'assets/js/aieo-attribution-admin.js';
$url = plugins_url($rel, AIEO_FILE);
$path = dirname(AIEO_FILE) . '/' . $rel;
$ver = file_exists($path) ? filemtime($path) : (defined('AIEO_VERSION') ? AIEO_VERSION : false);
wp_enqueue_script('aieo-attribution-admin', $url, array(), $ver, true);
wp_localize_script('aieo-attribution-admin', 'aieoAttrAdmin', array(
'i18n' => array(
'badgeLabel' => __('Badge', 'ai-eshop-optimizer'),
'adds' => array(
_n('%s assisted add', '%s assisted adds', 1, 'ai-eshop-optimizer'),
_n('%s assisted add', '%s assisted adds', 2, 'ai-eshop-optimizer'),
),
'search' => __('Search', 'ai-eshop-optimizer'),
),
));
}
public function render_subtab() {
global $wpdb;
if (isset($_GET['attr_lead']) && is_string($_GET['attr_lead'])) {
$this->render_lead_view(substr(sanitize_text_field(rawurldecode(wp_unslash($_GET['attr_lead']))), 0, 64));
return;
}
$range_days = isset($_GET['attr_days']) ? max(1, (int) $_GET['attr_days']) : 30;
$since = gmdate('Y-m-d H:i:s', time() - ($range_days * DAY_IN_SECONDS));
$this->slot_cache_bypass = isset($_GET['attr_refresh'], $_GET['_wpnonce'])
&& wp_verify_nonce(sanitize_text_field(wp_unslash($_GET['_wpnonce'])), 'aieo_attr_refresh');
$addcart = AIEO_Attribution::get_table_name();
$reco = AIEO_Attribution::get_reco_table_name();
$this->in_bands = function_exists('rooster_admin_band_open');
$cleared = isset($_GET['cleared']);
$figures = $this->headline_figures($addcart, $reco, $since);
if ($this->in_bands) {
rooster_admin_band_open(array('cols' => '1:2'));
}
$this->render_toolbar_card($range_days, $cleared, $figures['assist_all']);
$this->render_stats_card($figures);
if ($this->in_bands) {
rooster_admin_band_close();
rooster_admin_band_open(array(
'title' => __('Traffic', 'ai-eshop-optimizer'),
'key' => 'traffic',
'cols' => 1,
));
}
$this->render_traffic_source_card($addcart, $reco, $since);
$this->render_goals_channel_card($range_days);
$this->render_campaign_leads_card($since);
$this->render_leads_card($since, $range_days);
$this->render_branch_breakdown_card($since);
if ($this->in_bands) {
rooster_admin_band_close();
rooster_admin_band_open(array(
'title' => __('Recommendation engine', 'ai-eshop-optimizer'),
'description' => __('Products bought after a recommendation impression or click, grouped by surface, slot, and anchor product.', 'ai-eshop-optimizer'),
'key' => 'recommendations',
'cols' => 2,
));
}
$this->render_surface_card($addcart, $since);
$this->render_slot_card($addcart, $reco, $since, $range_days);
$this->render_top_products_card($addcart, $reco, $since);
$this->render_top_anchors_card($addcart, $reco, $since);
$this->render_detail_card($range_days);
if ($this->in_bands) {
rooster_admin_band_close();
}
}
private function join_collate( $table_a, $col_a, $table_b, $col_b ) {
static $memo = array();
$key = $table_a . '.' . $col_a . '|' . $table_b . '.' . $col_b;
if ( isset( $memo[ $key ] ) ) {
return $memo[ $key ];
}
global $wpdb;
$get = static function ( $t, $c ) use ( $wpdb ) {
return (string) $wpdb->get_var( $wpdb->prepare(
"SELECT COLLATION_NAME FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s",
$t,
$c
) );
};
$a = $get( $table_a, $col_a );
$b = $get( $table_b, $col_b );
$memo[ $key ] = ( '' !== $a && '' !== $b && $a !== $b ) ? ' COLLATE ' . $a : '';
return $memo[ $key ];
}
private function render_campaign_leads_card($since) {
global $wpdb;
if (!class_exists('AIEO_DMM_Conversions_Registry')) { return; }
$conv = AIEO_DMM_Conversions_Registry::table();
if ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $conv)) !== $conv) { return; }
$orders = $wpdb->get_results($wpdb->prepare(
"SELECT canon_campaign AS campaign,
COUNT(*) AS orders,
SUM(COALESCE(conversion_value,0) - COALESCE(value_refunded,0)) AS revenue
FROM `{$conv}`
WHERE conversion_type = 'order' AND occurred_at >= %s
GROUP BY canon_campaign",
$since
), ARRAY_A);
$goals = $wpdb->get_results($wpdb->prepare(
"SELECT canon_campaign AS campaign, goal_key, COALESCE(form_id, 0) AS form_id, COUNT(*) AS n
FROM `{$conv}`
WHERE conversion_type = 'goal' AND occurred_at >= %s
GROUP BY canon_campaign, goal_key, COALESCE(form_id, 0)",
$since
), ARRAY_A);
if (!$orders && !$goals) { return; }
$by = array();
$seed = static function (&$by, $campaign) {
if (!isset($by[$campaign])) {
$by[$campaign] = array(
'campaign' => $campaign,
'orders' => 0, 'revenue' => 0.0,
'leads' => 0, 'lead_value' => 0.0, 'goals' => 0,
'quotes' => 0, 'restock' => 0,
);
}
return $campaign;
};
foreach ((array) $orders as $r) {
$k = $seed($by, (string) $r['campaign']);
$by[$k]['orders'] = (int) $r['orders'];
$by[$k]['revenue'] = (float) $r['revenue'];
}
foreach ((array) $goals as $r) {
$k = $seed($by, (string) $r['campaign']);
$key = (string) $r['goal_key'];
$n = (int) $r['n'];
$by[$k]['leads'] += $n;
if (!class_exists('AIEO_DMM_Goal_Conversions') || AIEO_DMM_Goal_Conversions::counts_as_goal($key, (int) $r['form_id'])) {
$by[$k]['goals'] += $n;
}
if (class_exists('AIEO_Goal_Tracker')) {
$by[$k]['lead_value'] += $n * (float) AIEO_Goal_Tracker::goal_value($key);
}
if ('product_request' === $key) { $by[$k]['quotes'] += $n; }
if ('waitlist_signup' === $key) { $by[$k]['restock'] += $n; }
}
uasort($by, static function ($a, $b) {
return ($b['orders'] + $b['leads']) <=> ($a['orders'] + $a['leads']);
});
$by = array_slice($by, 0, 30, true);
$money = static function ($v) {
return class_exists('AIEO_DMM_Conversions_Registry')
? AIEO_DMM_Conversions_Registry::money($v)
: number_format((float) $v, 2);
};
$grid = array();
foreach ($by as $r) {
$grid[] = array(
'campaign' => '' !== $r['campaign'] ? $r['campaign'] : __('(no campaign: direct or untagged)', 'ai-eshop-optimizer'),
'orders' => (int) $r['orders'],
'revenue' => $money($r['revenue']),
'leads' => (int) $r['leads'],
'goals' => (int) $r['goals'],
'quotes' => (int) $r['quotes'],
'restock' => (int) $r['restock'],
'lead_value' => $money($r['lead_value']),
);
}
AIEO_Table::card(array(
'anchor' => 'attribution-orders-and-leads-per-campaign',
'icon' => 'megaphone',
'heading' => 'h2',
'title' => __('Orders and leads per campaign', 'ai-eshop-optimizer'),
'description' => __('From the durable conversion ledger, written when the order is placed or the request is sent, so unlike the card above it does NOT need session tracking, and the two will differ on a site where tracking is off. A lead is worth whatever you set it to on the Conversion Goals screen; left at zero it still counts but contributes nothing.', 'ai-eshop-optimizer'),
'page_id' => $this->get_id(),
'row_key' => 'campaign',
'density' => 'compact',
'rows' => $grid,
'caps' => array('search' => true),
'empty' => __('No conversions recorded in this window.', 'ai-eshop-optimizer'),
'columns' => array(
array('key' => 'campaign', 'label' => __('Campaign', 'ai-eshop-optimizer'), 'type' => 'text'),
array('key' => 'orders', 'label' => __('Orders', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'revenue', 'label' => __('Revenue', 'ai-eshop-optimizer'), 'type' => 'text'),
array('key' => 'leads', 'label' => __('Leads', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'goals', 'label' => __('Goals', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'quotes', 'label' => __('Quote requests', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'restock', 'label' => __('Back-in-stock', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'lead_value', 'label' => __('Lead value', 'ai-eshop-optimizer'), 'type' => 'text'),
),
));
$this->render_unsold_demand_card($conv, $since);
}
private function render_leads_card($since, $range_days) {
global $wpdb;
if (!class_exists('AIEO_DMM_Conversions_Registry') || !class_exists('AIEO_Submission_Context')) { return; }
$this->load_submission_panel();
if (!class_exists('AIEO_Submission_Panel')) { return; }
$conv = AIEO_DMM_Conversions_Registry::table();
$ctx = AIEO_Submission_Context::table();
if ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $conv)) !== $conv || !AIEO_Submission_Context::ensure_schema()) { return; }
$campaign = isset($_GET['attr_lead_campaign']) ? sanitize_text_field(wp_unslash((string) $_GET['attr_lead_campaign'])) : '';
$form = isset($_GET['attr_lead_form']) ? sanitize_key(wp_unslash((string) $_GET['attr_lead_form'])) : '';
$campaigns = (array) $wpdb->get_col($wpdb->prepare("SELECT DISTINCT canon_campaign FROM `{$conv}` WHERE conversion_type = 'goal' AND occurred_at >= %s AND canon_campaign <> '' ORDER BY canon_campaign LIMIT 200", $since));
$surfaces = (array) $wpdb->get_results($wpdb->prepare("SELECT DISTINCT x.surface, c.provider FROM `{$conv}` c JOIN `{$ctx}` x ON x.source_key = c.source_key WHERE c.conversion_type = 'goal' AND c.occurred_at >= %s AND x.surface <> '' LIMIT 200", $since), ARRAY_A);
$where = array("c.conversion_type = 'goal'", 'c.occurred_at >= %s');
$args = array($since);
if ('' !== $campaign) { $where[] = 'c.canon_campaign = %s'; $args[] = $campaign; }
if ('' !== $form) { $where[] = 'x.surface = %s'; $args[] = $form; }
$rows = (array) $wpdb->get_results($wpdb->prepare(
"SELECT c.source_key, c.occurred_at, c.goal_key, c.provider, c.canon_campaign, c.canon_source, c.canon_medium, x.surface, x.tracked
FROM `{$conv}` c LEFT JOIN `{$ctx}` x ON x.source_key = c.source_key
WHERE " . implode(' AND ', $where) . "
ORDER BY c.occurred_at DESC, c.id DESC LIMIT 200",
$args
), ARRAY_A);
$grid = array();
foreach ($rows as $r) {
$ts = strtotime((string) $r['occurred_at'] . ' UTC');
$grid[] = array(
'when' => $ts ? wp_date('Y-m-d H:i', $ts) : '',
'form' => AIEO_Submission_Panel::surface_label((string) $r['surface'], (string) $r['provider']),
'campaign' => '' !== (string) $r['canon_campaign'] ? (string) $r['canon_campaign'] : __('(no campaign: direct or untagged)', 'ai-eshop-optimizer'),
'source' => trim((string) $r['canon_source'] . ' / ' . (string) $r['canon_medium'], ' /'),
'journey' => null === $r['tracked'] ? __('Not kept', 'ai-eshop-optimizer') : ((int) $r['tracked'] ? __('Kept', 'ai-eshop-optimizer') : __('Not tracked', 'ai-eshop-optimizer')),
'open' => __('Campaign and journey', 'ai-eshop-optimizer'),
'open_url' => AIEO_Submission_Panel::url((string) $r['source_key']),
);
}
$base = add_query_arg(array('page' => AIEO_DMM::MENU_SLUG, 'dmm_tab' => 'attribution', 'attr_days' => (int) $range_days), admin_url('admin.php'));
$c_opts = array('' => __('All campaigns', 'ai-eshop-optimizer'));
foreach ($campaigns as $c) { $c_opts[(string) $c] = (string) $c; }
$f_opts = array('' => __('All forms', 'ai-eshop-optimizer'));
foreach ($surfaces as $sf) { $f_opts[(string) $sf['surface']] = AIEO_Submission_Panel::surface_label((string) $sf['surface'], (string) $sf['provider']); }
$filters = '';
AIEO_Table::card(array(
'anchor' => 'attribution-leads',
'icon' => 'inbox',
'heading' => 'h2',
'title' => __('Leads', 'ai-eshop-optimizer'),
'description' => __('Every form, contact message, newsletter sign-up, survey response, competition entry, event registration, quote request and waitlist signup of the period, newest first, with its campaign. Open one to see its campaign in full and the pages the visitor read before and after.', 'ai-eshop-optimizer'),
'page_id' => $this->get_id(),
'row_key' => 'when',
'density' => 'compact',
'toolbar' => $filters,
'rows' => $grid,
'empty' => '' !== $campaign . $form ? __('No leads match these filters in this period.', 'ai-eshop-optimizer') : __('No leads recorded in this period.', 'ai-eshop-optimizer'),
'columns' => array(
array('key' => 'when', 'label' => __('When', 'ai-eshop-optimizer'), 'type' => 'text'),
array('key' => 'form', 'label' => __('Form', 'ai-eshop-optimizer'), 'type' => 'text'),
array('key' => 'campaign', 'label' => __('Campaign', 'ai-eshop-optimizer'), 'type' => 'text'),
array('key' => 'source', 'label' => __('Source / medium', 'ai-eshop-optimizer'), 'type' => 'text'),
array('key' => 'journey', 'label' => __('Journey', 'ai-eshop-optimizer'), 'type' => 'text'),
array('key' => 'open', 'label' => __('Open', 'ai-eshop-optimizer'), 'type' => 'link', 'format' => array('href' => 'open_url')),
),
));
}
private function render_lead_view($source_key) {
$this->load_submission_panel();
$back = add_query_arg(array('page' => AIEO_DMM::MENU_SLUG, 'dmm_tab' => 'attribution'), admin_url('admin.php')) . '#attribution-leads';
$lead = class_exists('AIEO_Submission_Panel') ? AIEO_Submission_Panel::lead($source_key) : null;
if (function_exists('rooster_admin_band_open')) { rooster_admin_band_open(array('cols' => 1)); }
echo '← ' . esc_html__('All leads', 'ai-eshop-optimizer') . '
';
if (!$lead) {
echo \Rooster\Admin\Ui::notice(esc_html__('This lead is not in the ledger.', 'ai-eshop-optimizer'), 'warning', array('inline' => true));
} else {
echo AIEO_Submission_Panel::render($lead);
}
if (function_exists('rooster_admin_band_close')) { rooster_admin_band_close(); }
}
private function load_submission_panel() {
if (!class_exists('AIEO_Submission_Panel', false) && is_readable(dirname(__DIR__) . '/class-aieo-submission-panel.php')) {
require_once dirname(__DIR__) . '/class-aieo-submission-panel.php';
}
}
private function render_unsold_demand_card($conv, $since) {
global $wpdb;
$rows = array();
$items = $wpdb->prefix . 'aieo_product_request_items';
if ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $items)) === $items) {
$rows = array_merge($rows, (array) $wpdb->get_results($wpdb->prepare(
"SELECT c.canon_campaign AS campaign, i.product_id, COUNT(*) AS n, 'quote' AS kind
FROM `{$conv}` c
JOIN `{$items}` i ON i.request_id = c.entry_id
WHERE c.conversion_type = 'goal' AND c.goal_key = 'product_request'
AND c.provider = 'product_request' AND c.occurred_at >= %s
GROUP BY c.canon_campaign, i.product_id
ORDER BY n DESC
LIMIT 50",
$since
), ARRAY_A));
}
$wl = $wpdb->prefix . 'aieo_waitlist';
if ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $wl)) === $wl) {
$rows = array_merge($rows, (array) $wpdb->get_results($wpdb->prepare(
"SELECT c.canon_campaign AS campaign, w.product_id, COUNT(*) AS n, 'restock' AS kind
FROM `{$conv}` c
JOIN `{$wl}` w ON w.id = c.entry_id
WHERE c.conversion_type = 'goal' AND c.goal_key = 'waitlist_signup'
AND c.provider = 'waitlist' AND c.occurred_at >= %s
GROUP BY c.canon_campaign, w.product_id
ORDER BY n DESC
LIMIT 50",
$since
), ARRAY_A));
}
if (!$rows) { return; }
usort($rows, static function ($a, $b) { return (int) $b['n'] <=> (int) $a['n']; });
$rows = array_slice($rows, 0, 30);
$grid = array();
foreach ($rows as $r) {
$pid = (int) $r['product_id'];
$prod = function_exists('wc_get_product') ? wc_get_product($pid) : null;
$grid[] = array(
'campaign' => '' !== (string) $r['campaign'] ? (string) $r['campaign'] : __('(no campaign: direct or untagged)', 'ai-eshop-optimizer'),
'product' => $prod ? $prod->get_name() : ('#' . $pid),
'edit_url' => admin_url('post.php?post=' . $pid . '&action=edit'),
'why' => 'restock' === $r['kind']
? __('Out of stock', 'ai-eshop-optimizer')
: __('No price listed', 'ai-eshop-optimizer'),
'n' => (int) $r['n'],
);
}
AIEO_Table::card(array(
'per_page' => 0,
'anchor' => 'attribution-demand-created-but-not-sold',
'icon' => 'calendar-x',
'heading' => 'h2',
'title' => __('Demand these campaigns created but the catalogue could not sell', 'ai-eshop-optimizer'),
'description' => __('Each row is a real person who arrived through a campaign, wanted this product, and could not buy it, because it was out of stock or because it carries no price. These campaigns earn no revenue and read everywhere else as failures. They are not: they worked, and the catalogue did not.', 'ai-eshop-optimizer'),
'page_id' => $this->get_id(),
'row_key' => array('campaign', 'product'),
'density' => 'compact',
'rows' => $grid,
'empty' => __('Nothing recorded in this window.', 'ai-eshop-optimizer'),
'columns' => array(
array('key' => 'campaign', 'label' => __('Campaign', 'ai-eshop-optimizer'), 'type' => 'text'),
array('key' => 'product', 'label' => __('Product', 'ai-eshop-optimizer'), 'type' => 'link', 'format' => array('href' => 'edit_url')),
array('key' => 'why', 'label' => __('Why they could not buy', 'ai-eshop-optimizer'), 'type' => 'text'),
array('key' => 'n', 'label' => __('People', 'ai-eshop-optimizer'), 'type' => 'integer'),
),
));
}
private function render_goals_channel_card($range_days) {
global $wpdb;
$t = $wpdb->prefix . 'aieo_landing_campaign_daily';
if ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $t)) !== $t) {
return;
}
$since = gmdate('Y-m-d', time() - $range_days * DAY_IN_SECONDS);
$has_ladder = (bool) $wpdb->get_var("SHOW COLUMNS FROM `{$t}` LIKE 'purchases'");
$ladder_sel = $has_ladder ? ', SUM(engaged_sessions) AS engaged, SUM(purchases) AS purchases' : ', 0 AS engaged, 0 AS purchases';
$rows = $wpdb->get_results($wpdb->prepare(
"SELECT channel, referrer_host,
SUM(sessions) AS sessions, SUM(cart_adds) AS cart_adds, SUM(goals) AS goals{$ladder_sel}
FROM `{$t}`
WHERE `date` >= %s
GROUP BY channel, referrer_host
ORDER BY purchases DESC, goals DESC, sessions DESC
LIMIT 25",
$since
), ARRAY_A);
$grid = array();
foreach ($rows as $r) {
$s = (int) $r['sessions'];
$g = (int) $r['goals'];
$p = (int) $r['purchases'];
$grid[] = array(
'channel' => class_exists('AIEO_Attribution_Block') ? AIEO_Attribution_Block::channel_label($r['channel']) : $r['channel'],
'host' => (string) $r['referrer_host'],
'sessions' => $s,
'engaged' => (int) $r['engaged'],
'goals' => $g,
'adds' => (int) $r['cart_adds'],
'buys' => $p,
'conv_pct' => $s > 0 ? round(100 * ($p > 0 ? $p : $g) / $s, 2) : 0,
);
}
$head = array(
'anchor' => 'attribution-goal-conversions-by-traffic-source',
'icon' => 'target',
'heading' => 'h2',
'title' => __('Goal conversions by source', 'ai-eshop-optimizer'),
'description' => __('Sessions → goal conversions per channel and referring site, from the canonical landing/campaign rollup. Goals are the conversions of content sites and portals (form submissions, newsletter signups). Pick them on Settings → Conversion goals.', 'ai-eshop-optimizer'),
);
if (!$grid) {
$this->empty_card($head, $this->has_rows($t), __('No data yet — the rollup builds nightly once session tracking is enabled.', 'ai-eshop-optimizer'));
return;
}
AIEO_Table::card($head + array(
'per_page' => 0,
'page_id' => $this->get_id(),
'row_key' => array('channel', 'host'),
'density' => 'compact',
'rows' => $grid,
'empty' => __('No data yet — the rollup builds nightly once session tracking is enabled.', 'ai-eshop-optimizer'),
'columns' => array(
array('key' => 'channel', 'label' => __('Channel', 'ai-eshop-optimizer'), 'type' => 'text'),
array('key' => 'host', 'label' => __('Referring site', 'ai-eshop-optimizer'), 'type' => 'text'),
array('key' => 'sessions', 'label' => __('Sessions', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'engaged', 'label' => __('Engaged', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'goals', 'label' => __('Goals', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'adds', 'label' => __('Cart adds', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'buys', 'label' => __('Purchases', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'conv_pct', 'label' => __('Conv. %', 'ai-eshop-optimizer'), 'type' => 'decimal', 'format' => array('precision' => 2)),
),
));
}
private function render_traffic_source_card($addcart, $reco, $since) {
global $wpdb;
$sessions = $wpdb->prefix . 'aieo_sessions_statics';
$coll = $this->join_collate( $addcart, 'session_id', $sessions, 'session_id' );
$exists = (string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $sessions)) === $sessions;
if (!$exists) { return; }
$rows = $wpdb->get_results($wpdb->prepare(
"SELECT
COALESCE(NULLIF(s.entry_channel, ''), 'unattributed') AS channel,
COUNT(*) AS adds,
SUM(e.order_id > 0) AS converted,
COUNT(DISTINCT IF(e.order_id > 0, e.order_id, NULL)) AS orders,
SUM(EXISTS (
SELECT 1 FROM {$reco} r
WHERE r.session_id = e.session_id
AND r.target_pid = e.product_id
AND r.ts < e.ts_added
AND e.ts_added < DATE_ADD(r.ts, INTERVAL 7 DAY)
)) AS with_assist,
ROUND(100 * SUM(e.order_id > 0) / COUNT(*), 1) AS conv_pct,
COUNT(DISTINCT s.entry_campaign) AS campaigns_seen
FROM {$addcart} e
LEFT JOIN {$sessions} s
ON s.session_id{$coll} = e.session_id{$coll}
WHERE e.ts_added >= %s
GROUP BY channel
ORDER BY adds DESC",
$since
));
$head = array(
'anchor' => 'attribution-traffic-source-channel-breakdown',
'icon' => 'shopping-cart',
'title' => __('Cart adds by channel', 'ai-eshop-optimizer'),
'description' => __('Add-to-cart events grouped by how the visitor arrived: web push (our own dispatches), email newsletter, paid Google / Facebook campaigns (gclid / fbclid auto-detected), organic search referrers, social referrers, direct traffic. Any URL with a UTM tag captures the campaign name in the right-most column. Push notifications get auto-tagged with aieo_src=push + dispatch_id.', 'ai-eshop-optimizer'),
);
if (!$rows) {
$this->empty_card($head, $this->has_rows($addcart), __('No add-to-cart events yet in this range — channel breakdown will populate as traffic flows.', 'ai-eshop-optimizer'));
return;
}
list($open, $close) = AIEO_DMM_UI::card($head + array('flush' => true));
echo $open;
$chan = array();
foreach ($rows as $r) {
$chan[] = array(
'channel' => (string) $r->channel,
'adds' => (int) $r->adds,
'assist' => (int) $r->with_assist,
'converted' => (int) $r->converted,
'orders' => (int) $r->orders,
'conv_pct' => (float) $r->conv_pct,
'campaigns' => (int) $r->campaigns_seen,
);
}
AIEO_Table::render(array(
'per_page' => 0,
'page_id' => $this->get_id(),
'row_key' => 'channel',
'density' => 'compact',
'rows' => $chan,
'empty' => __('No add-to-cart events yet in this range — channel breakdown will populate as traffic flows.', 'ai-eshop-optimizer'),
'columns' => array(
array('key' => 'channel', 'label' => __('Channel', 'ai-eshop-optimizer'), 'type' => 'badge'),
array('key' => 'adds', 'label' => __('Adds', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'assist', 'label' => __('w/ Reco assist', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'converted', 'label' => __('Converted lines', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'orders', 'label' => __('Orders', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'conv_pct', 'label' => __('Conv %', 'ai-eshop-optimizer'), 'type' => 'percent', 'format' => array('precision' => 2)),
array('key' => 'campaigns', 'label' => __('Campaigns', 'ai-eshop-optimizer'), 'type' => 'integer'),
),
));
$top_campaigns = $wpdb->get_results($wpdb->prepare(
"SELECT
COALESCE(NULLIF(s.entry_channel, ''), 'unattributed') AS channel,
s.entry_campaign AS campaign,
COUNT(*) AS adds,
COUNT(DISTINCT IF(e.order_id > 0, e.order_id, NULL)) AS orders
FROM {$addcart} e
JOIN {$sessions} s
ON s.session_id{$coll} = e.session_id{$coll}
WHERE e.ts_added >= %s
AND s.entry_campaign <> ''
GROUP BY channel, campaign
ORDER BY orders DESC, adds DESC
LIMIT 15",
$since
));
if (!empty($top_campaigns)) {
if ($this->card_sections()) {
echo rooster_admin_card_section_html(__('Top campaigns (by orders)', 'ai-eshop-optimizer'));
} else {
echo '' . esc_html__('Top campaigns (by orders)', 'ai-eshop-optimizer') . '
';
}
$camps = array();
foreach ($top_campaigns as $r) {
$camps[] = array(
'channel' => (string) $r->channel,
'campaign' => (string) $r->campaign,
'adds' => (int) $r->adds,
'orders' => (int) $r->orders,
);
}
AIEO_Table::render(array(
'per_page' => 0,
'page_id' => $this->get_id(),
'row_key' => array('channel', 'campaign'),
'density' => 'compact',
'rows' => $camps,
'caps' => array('search' => true),
'filters' => array(array('key' => 'channel', 'label' => __('Channel', 'ai-eshop-optimizer'), 'kind' => 'values', 'control' => 'select')),
'cap' => 15,
'capped' => count($camps) >= 15,
'empty' => __('No campaign-tagged traffic in this range.', 'ai-eshop-optimizer'),
'columns' => array(
array('key' => 'channel', 'label' => __('Channel', 'ai-eshop-optimizer'), 'type' => 'code'),
array('key' => 'campaign', 'label' => __('Campaign', 'ai-eshop-optimizer'), 'type' => 'text'),
array('key' => 'adds', 'label' => __('Adds', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'orders', 'label' => __('Orders', 'ai-eshop-optimizer'), 'type' => 'integer'),
),
));
}
echo $close;
}
private function card_sections() {
return function_exists('rooster_admin_card_section_html') && class_exists('AIEO_Chrome') && AIEO_Chrome::on();
}
private function has_rows($table) {
static $memo = array();
if (!isset($memo[$table])) {
global $wpdb;
$memo[$table] = (bool) $wpdb->get_var("SELECT 1 FROM `{$table}` LIMIT 1");
}
return $memo[$table];
}
private function period_state($ever, $title) {
return \Rooster\Admin\States::from(
array('total' => $ever ? 1 : 0, 'shown' => 0, 'filtered' => true),
array('first-use' => array('title' => $title))
);
}
private function empty_card(array $head, $ever, $title) {
list($open, $close) = AIEO_DMM_UI::card(array_intersect_key($head, array_flip(array('anchor', 'icon', 'title', 'description', 'footnote', 'tools', 'col', 'span', 'min', 'movable'))));
echo $open;
echo $this->period_state($ever, $title);
echo $close;
}
private function render_branch_breakdown_card($since) {
global $wpdb;
$attr_tbl = $wpdb->prefix . 'aieo_dmm_offer_attributions';
$branch_tbl = $wpdb->prefix . 'aieo_dmm_branches';
if (!aieo_dmm_table_exists($attr_tbl) || !aieo_dmm_table_exists($branch_tbl)) { return; }
$any_branch = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$branch_tbl}");
if ($any_branch === 0) { return; }
$rows = $wpdb->get_results($wpdb->prepare(
"SELECT
COALESCE(b.label, 'eshop / unmapped') AS branch_label,
a.entry_branch_id AS branch_id,
COUNT(*) AS attribution_rows,
COUNT(DISTINCT a.order_id) AS orders,
ROUND(SUM(a.applied_value), 2) AS applied_value_total,
ROUND(SUM(IFNULL(a.actual_cost, 0)), 2) AS actual_cost_total,
ROUND(SUM(a.order_total) / NULLIF(COUNT(DISTINCT a.order_id), 0), 2) AS avg_order_value
FROM {$attr_tbl} a
LEFT JOIN {$branch_tbl} b ON b.id = a.entry_branch_id
WHERE a.fired_at >= %s
GROUP BY a.entry_branch_id
ORDER BY orders DESC",
$since
));
$branches = array();
foreach ((array) $rows as $r) {
$branches[] = array(
'branch_id' => (null === $r->branch_id) ? 0 : (int) $r->branch_id,
'branch' => (string) $r->branch_label,
'attr_rows' => (int) $r->attribution_rows,
'orders' => (int) $r->orders,
'applied' => (float) $r->applied_value_total,
'cost' => (float) $r->actual_cost_total,
'aov' => (float) $r->avg_order_value,
);
}
$head = array(
'anchor' => 'attribution-per-branch-attribution-breakdown',
'icon' => 'building-2',
'heading' => 'h2',
'title' => __('Per-branch attribution breakdown', 'ai-eshop-optimizer'),
'description' => __('Offer attributions grouped by the physical branch the order came from. Resolved at fire-time from the order\'s `soft1_branches` post-meta against the SoftOne ID column on Branch Scanner. NULL = eshop-only orders or branches without a SoftOne mapping yet.', 'ai-eshop-optimizer'),
);
if (!$branches) {
$this->empty_card($head, $this->has_rows($attr_tbl), __('No per-branch attribution data in this range. Set the SoftOne ID for each branch in Branch Scanner, then trigger a few in-store orders to populate.', 'ai-eshop-optimizer'));
return;
}
AIEO_Table::card($head + array(
'per_page' => 0,
'page_id' => $this->get_id(),
'row_key' => 'branch_id',
'density' => 'compact',
'rows' => $branches,
'empty' => __('No per-branch attribution data in this range. Set the SoftOne ID for each branch in Branch Scanner, then trigger a few in-store orders to populate.', 'ai-eshop-optimizer'),
'row_class' => static function ($row) {
return empty($row['branch_id']) ? 'aieo-t-tone--muted' : '';
},
'columns' => array(
array('key' => 'branch', 'label' => __('Branch', 'ai-eshop-optimizer'), 'type' => 'text'),
array('key' => 'attr_rows', 'label' => __('Attribution rows', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'orders', 'label' => __('Orders', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'applied', 'label' => __('Applied value', 'ai-eshop-optimizer'), 'type' => 'money'),
array('key' => 'cost', 'label' => __('Actual cost', 'ai-eshop-optimizer'), 'type' => 'money'),
array('key' => 'aov', 'label' => __('Avg order value', 'ai-eshop-optimizer'), 'type' => 'money'),
),
));
}
private function render_toolbar_card($current, $cleared = false, $exportable = 1) {
$post_url = admin_url('admin-post.php');
$clear_msg = __('Clear ALL attribution data (add-cart events + reco events)? This cannot be undone.', 'ai-eshop-optimizer');
$export = array(
'type' => 'submit',
'icon' => 'download',
'attrs' => array('form' => 'aieo-attr-export-form', 'id' => 'aieo-attr-export'),
);
if ((int) $exportable < 1) {
$export['reason'] = __('Nothing to export yet.', 'ai-eshop-optimizer');
}
list($open, $close) = AIEO_DMM_UI::card(array(
'anchor' => 'attribution-period',
'icon' => 'calendar-range',
'title' => __('Period', 'ai-eshop-optimizer'),
'description' => __('Choose how many days the report covers, download its recommendation-assisted adds to cart as a spreadsheet, or clear the recorded data.', 'ai-eshop-optimizer'),
'col' => 1,
'footer' => \Rooster\Admin\Ui::button(__('Export XLSX', 'ai-eshop-optimizer'), $export)
. \Rooster\Admin\Ui::button(__('Clear tables', 'ai-eshop-optimizer'), array(
'type' => 'submit',
'variant' => 'destructive',
'icon' => 'trash-2',
'attrs' => array('form' => 'aieo-attr-clear-form') + \Rooster\Admin\States::confirm_attrs('aieo-attr-clear-ask'),
)),
));
echo $open;
if ($cleared) {
echo \Rooster\Admin\Ui::notice(esc_html__('Attribution tables cleared.', 'ai-eshop-optimizer'), 'success', array('inline' => true));
}
echo '';
echo '';
echo '';
$ask = \Rooster\Admin\States::confirm(
'aieo-attr-clear-ask',
'loss',
__('Clear the attribution tables?', 'ai-eshop-optimizer'),
esc_html($clear_msg),
array('confirm' => __('Clear tables', 'ai-eshop-optimizer'))
);
echo $ask;
echo $close;
}
private function headline_figures($addcart, $reco, $since) {
global $wpdb;
$total_adds = (int) $wpdb->get_var($wpdb->prepare(
"SELECT COUNT(*) FROM {$addcart} WHERE ts_added >= %s", $since
));
$orders = (int) $wpdb->get_var($wpdb->prepare(
"SELECT COUNT(DISTINCT order_id) FROM {$addcart} WHERE order_id > 0 AND ts_added >= %s", $since
));
$adds_converted = (int) $wpdb->get_var($wpdb->prepare(
"SELECT COUNT(*) FROM {$addcart} WHERE order_id > 0 AND ts_added >= %s", $since
));
$impressions = (int) $wpdb->get_var($wpdb->prepare(
"SELECT COUNT(*) FROM {$reco} WHERE event_type = 'impression' AND ts >= %s", $since
));
$reco_clicks = (int) $wpdb->get_var($wpdb->prepare(
"SELECT COUNT(*) FROM {$reco} WHERE event_type = 'reco_click' AND ts >= %s", $since
));
$adds_with_assist_all = (int) $wpdb->get_var($wpdb->prepare(
"SELECT COUNT(DISTINCT e.id)
FROM {$addcart} e
WHERE e.ts_added >= %s
AND EXISTS (
SELECT 1 FROM {$reco} r
WHERE r.session_id = e.session_id
AND r.target_pid = e.product_id
AND r.ts < e.ts_added
AND e.ts_added < DATE_ADD(r.ts, INTERVAL 7 DAY)
)",
$since
));
$adds_with_assist_converted = (int) $wpdb->get_var($wpdb->prepare(
"SELECT COUNT(DISTINCT e.id)
FROM {$addcart} e
WHERE e.order_id > 0 AND e.ts_added >= %s
AND EXISTS (
SELECT 1 FROM {$reco} r
WHERE r.session_id = e.session_id
AND r.target_pid = e.product_id
AND r.ts < e.ts_added
AND e.ts_added < DATE_ADD(r.ts, INTERVAL 7 DAY)
)",
$since
));
return array(
'total_adds' => $total_adds,
'orders' => $orders,
'adds_converted' => $adds_converted,
'impressions' => $impressions,
'reco_clicks' => $reco_clicks,
'assist_all' => $adds_with_assist_all,
'assist_converted' => $adds_with_assist_converted,
'assist_pct' => $total_adds > 0 ? round(100 * $adds_with_assist_all / $total_adds, 1) : 0,
);
}
private function render_stats_card(array $f) {
$total_adds = (int) $f['total_adds'];
$orders = (int) $f['orders'];
$adds_converted = (int) $f['adds_converted'];
$impressions = (int) $f['impressions'];
$reco_clicks = (int) $f['reco_clicks'];
$adds_with_assist_all = (int) $f['assist_all'];
$adds_with_assist_converted = (int) $f['assist_converted'];
$assist_pct = $f['assist_pct'];
list($open, $close) = AIEO_DMM_UI::card(array(
'anchor' => 'attribution-headline-numbers',
'icon' => 'hash',
'title' => __('Headline numbers', 'ai-eshop-optimizer'),
'description' => __('See at a glance the period\'s adds to cart, orders and purchased lines, and how often shoppers saw, clicked and bought from recommendations.', 'ai-eshop-optimizer'),
'col' => 2,
));
echo $open;
echo '';
$tiles = array(
array(__('Add-to-cart events', 'ai-eshop-optimizer'), number_format_i18n($total_adds)),
array(__('… with reco assist', 'ai-eshop-optimizer'), number_format_i18n($adds_with_assist_all) . ' (' . $assist_pct . '%)'),
array(__('Orders attributed', 'ai-eshop-optimizer'), number_format_i18n($orders)),
array(__('Purchased lines', 'ai-eshop-optimizer'), number_format_i18n($adds_converted)),
array(__('… purchased w/ assist', 'ai-eshop-optimizer'), number_format_i18n($adds_with_assist_converted)),
array(__('Reco impressions', 'ai-eshop-optimizer'), number_format_i18n($impressions)),
array(__('Reco click-throughs', 'ai-eshop-optimizer'), number_format_i18n($reco_clicks)),
);
foreach ($tiles as $tile) {
echo \Rooster\Admin\Ui::stat($tile[0], $tile[1]);
}
echo '
';
echo $close;
}
private function render_surface_card($addcart, $since) {
global $wpdb;
$rows = $wpdb->get_results($wpdb->prepare(
"SELECT
source,
COUNT(*) AS adds,
SUM(order_id > 0) AS converted,
ROUND(100 * SUM(order_id > 0) / COUNT(*), 1) AS conv_pct
FROM {$addcart}
WHERE ts_added >= %s
GROUP BY source
ORDER BY adds DESC",
$since
));
$surfaces = array();
foreach ((array) $rows as $r) {
$surfaces[] = array(
'surface' => (string) $r->source,
'label' => $this->surface_label($r->source),
'adds' => (int) $r->adds,
'converted' => (int) $r->converted,
'conv_pct' => (float) $r->conv_pct,
);
}
$lead = __('Which surface closes the most sales — add-to-cart → order conversion rate per surface.', 'ai-eshop-optimizer');
if (!$this->in_bands) {
$lead = __('Products bought after a recommendation impression or click, grouped by surface, slot, and anchor product.', 'ai-eshop-optimizer') . ' ' . $lead;
}
$head = array(
'anchor' => 'attribution-conversion-by-surface',
'icon' => 'layout-template',
'heading' => 'h2',
'title' => __('Conversion by surface', 'ai-eshop-optimizer'),
'description' => $lead,
'span' => 'all',
);
if (!$surfaces) {
$this->empty_card($head, $this->has_rows($addcart), __('No events in this range yet.', 'ai-eshop-optimizer'));
return;
}
AIEO_Table::card($head + array(
'per_page' => 0,
'page_id' => $this->get_id(),
'row_key' => 'surface',
'density' => 'compact',
'rows' => $surfaces,
'empty' => __('No events in this range yet.', 'ai-eshop-optimizer'),
'columns' => array(
array('key' => 'surface', 'label' => __('Surface', 'ai-eshop-optimizer'), 'type' => 'code', 'subline' => 'label'),
array('key' => 'adds', 'label' => __('Adds', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'converted', 'label' => __('Converted', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'conv_pct', 'label' => __('Conv. rate', 'ai-eshop-optimizer'), 'type' => 'percent', 'format' => array('precision' => 2)),
),
));
}
private function render_slot_card($addcart, $reco, $since, $range_days) {
$payload = $this->slot_payload($addcart, $reco, $since, $range_days);
$rows = $payload['rows'];
$slots = array();
foreach ($rows as $r) {
$imp = (int) $r['impressions'];
$slots[] = array(
'surface' => (string) $r['surface'],
'position' => (int) $r['position'],
'slot' => '#' . (int) $r['position'],
'imps' => $imp,
'clicks' => (int) $r['clicks'],
'adds' => (int) $r['assisted_adds'],
'purchases' => (int) $r['assisted_purchases'],
'add_rate' => $imp > 0 ? round(100 * ((int) $r['assisted_adds']) / $imp, 1) : 0,
);
}
list($open, $close) = AIEO_DMM_UI::card(array(
'anchor' => 'attribution-slot-performance',
'icon' => 'gauge',
'title' => __('Slot performance', 'ai-eshop-optimizer'),
'description' => __('Which slot within each recommendation block converts best. Reads: of shoppers who saw a card in slot #N, what fraction ended up adding it to cart.', 'ai-eshop-optimizer'),
'tools' => $this->slot_refresh_button($range_days),
'span' => 'all',
'flush' => (bool) $slots,
));
echo $open;
$this->render_slot_provenance($payload, count($slots));
if (!$slots) {
echo $this->period_state($this->has_rows($reco), __('No slot-level data yet.', 'ai-eshop-optimizer'));
echo $close;
return;
}
AIEO_Table::render(array(
'per_page' => 0,
'page_id' => $this->get_id(),
'row_key' => array('surface', 'position'),
'density' => 'compact',
'rows' => $slots,
'empty' => __('No slot-level data yet.', 'ai-eshop-optimizer'),
'columns' => array(
array('key' => 'surface', 'label' => __('Surface', 'ai-eshop-optimizer'), 'type' => 'code'),
array('key' => 'slot', 'label' => __('Slot', 'ai-eshop-optimizer'), 'type' => 'text', 'align' => 'center', 'sort_key' => 'position'),
array('key' => 'imps', 'label' => __('Impressions', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'clicks', 'label' => __('Clicks', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'adds', 'label' => __('Assisted adds', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'purchases', 'label' => __('Assisted purchases', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'add_rate', 'label' => __('Add rate', 'ai-eshop-optimizer'), 'type' => 'percent', 'format' => array('precision' => 1)),
),
));
echo $close;
}
private function slot_payload($addcart, $reco, $since, $range_days) {
$signature = array(
'v' => 1,
'days' => (int) $range_days,
'from' => gmdate('Y-m-d', strtotime($since . ' UTC')),
'through' => gmdate('Y-m-d'),
'gen' => (string) get_option(self::SLOT_CACHE_GEN_OPTION, '0'),
);
$key = self::SLOT_CACHE_PREFIX . md5(wp_json_encode($signature));
if (!$this->slot_cache_bypass) {
$hit = get_transient($key);
if (is_array($hit) && isset($hit['rows'], $hit['source'], $hit['computed_at']) && is_array($hit['rows'])) {
$hit['cached'] = true;
$hit['stored'] = true;
return $hit;
}
}
$this->slot_query_failed = false;
$computed = $this->fetch_slot_rows($addcart, $reco, $since);
$errored = $this->slot_query_failed;
$payload = array(
'rows' => $computed['rows'],
'source' => $computed['source'],
'computed_at' => time(),
);
if (!$errored) {
set_transient($key, $payload, self::SLOT_CACHE_TTL);
}
$payload['cached'] = false;
$payload['stored'] = !$errored;
return $payload;
}
private function render_slot_provenance($payload, $count = 0) {
$age = max(0, time() - (int) $payload['computed_at']);
if (!empty($payload['cached'])) {
$when = $age < 60
? __('less than a minute ago', 'ai-eshop-optimizer')
: sprintf(
_n('%d minute ago', '%d minutes ago', (int) floor($age / 60), 'ai-eshop-optimizer'),
(int) floor($age / 60)
);
$state = sprintf(
__('Cached — computed %1$s, refreshes automatically every %2$d min.', 'ai-eshop-optimizer'),
$when,
(int) (self::SLOT_CACHE_TTL / MINUTE_IN_SECONDS)
);
} elseif (empty($payload['stored'])) {
$state = __('Computed just now — not cached (the query reported an error).', 'ai-eshop-optimizer');
} else {
$state = __('Computed just now.', 'ai-eshop-optimizer');
}
$source = ('rollup' === $payload['source'])
? __('Source: nightly rollup + today live.', 'ai-eshop-optimizer')
: __('Source: live scan of the raw event tables (rollup does not cover this window).', 'ai-eshop-optimizer');
if (empty($payload['cached']) && empty($payload['stored'])) {
echo \Rooster\Admin\Ui::notice(esc_html($state . ' ' . $source), 'warning', array('inline' => true));
return;
}
\Rooster\Admin\States::enqueue();
$head = \Rooster\Admin\States::result_head(
$count > 0
? sprintf(_n('%s slot', '%s slots', (int) $count, 'ai-eshop-optimizer'), number_format_i18n((int) $count))
: '',
array('facts' => array(rtrim($state, '.'), rtrim($source, '.')))
);
echo $head;
}
private function slot_refresh_button($range_days) {
$refresh = wp_nonce_url(
add_query_arg(
array(
'page' => 'ai-eshop-dmm',
'dmm_tab' => $this->get_id(),
'attr_days' => (int) $range_days,
'attr_refresh' => 1,
),
admin_url('admin.php')
),
'aieo_attr_refresh'
);
return \Rooster\Admin\Ui::button(__('Refresh now', 'ai-eshop-optimizer'), array(
'href' => $refresh . '#aieo-card-attribution-slot-performance',
'icon' => 'refresh-cw',
'size' => 'sm',
));
}
private function fetch_slot_rows($addcart, $reco, $since) {
global $wpdb;
$rollup = $wpdb->prefix . 'aieo_reco_surface_daily';
$win_start = gmdate('Y-m-d', strtotime($since . ' UTC'));
$today = gmdate('Y-m-d');
$last_full = gmdate('Y-m-d', strtotime($today . ' 00:00:00 UTC') - DAY_IN_SECONDS);
if ($last_full < $win_start || !$this->slot_rollup_covers($rollup, $win_start, $last_full)) {
return array(
'rows' => $this->merge_slot_rows($this->fetch_slot_rows_live($addcart, $reco, $since), array()),
'source' => 'live',
);
}
$rows = $wpdb->get_results($wpdb->prepare(
"SELECT surface,
position,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks,
SUM(assisted_adds) AS assisted_adds,
SUM(assisted_purchases) AS assisted_purchases
FROM {$rollup}
WHERE stat_date BETWEEN %s AND %s
GROUP BY surface, position",
$win_start,
$last_full
));
$this->note_slot_query_error();
$tail = $this->fetch_slot_rows_live($addcart, $reco, $today . ' 00:00:00');
return array(
'rows' => $this->merge_slot_rows($rows, $tail),
'source' => 'rollup',
);
}
private function slot_rollup_covers($rollup, $win_start, $last_full) {
global $wpdb;
if ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $rollup)) !== $rollup) {
return false;
}
$bounds = $wpdb->get_row("SELECT MIN(stat_date) AS min_d, MAX(stat_date) AS max_d FROM {$rollup}");
if (!$bounds || empty($bounds->min_d) || empty($bounds->max_d)) {
return false;
}
return ((string) $bounds->min_d <= $win_start && (string) $bounds->max_d >= $last_full);
}
private function fetch_slot_rows_live($addcart, $reco, $from) {
global $wpdb;
$rows = $wpdb->get_results($wpdb->prepare(
"SELECT
r.surface,
r.position,
COUNT(DISTINCT CASE WHEN r.event_type = 'impression' THEN r.id END) AS impressions,
COUNT(DISTINCT CASE WHEN r.event_type = 'reco_click' THEN r.id END) AS clicks,
COUNT(DISTINCT CASE WHEN e.id IS NOT NULL THEN r.id END) AS assisted_adds,
COUNT(DISTINCT CASE WHEN e.order_id > 0 THEN r.id END) AS assisted_purchases
FROM {$reco} r
LEFT JOIN {$addcart} e
ON e.session_id = r.session_id
AND e.product_id = r.target_pid
AND e.ts_added > r.ts
AND e.ts_added < DATE_ADD(r.ts, INTERVAL 7 DAY)
WHERE r.ts >= %s
GROUP BY r.surface, r.position
ORDER BY r.surface, r.position",
$from
));
$this->note_slot_query_error();
return $rows;
}
private function note_slot_query_error() {
global $wpdb;
if ('' !== (string) $wpdb->last_error) {
$this->slot_query_failed = true;
}
}
private function merge_slot_rows($rollup_rows, $tail_rows) {
$merged = array();
foreach (array((array) $rollup_rows, (array) $tail_rows) as $set) {
foreach ($set as $r) {
$key = $r->surface . '|' . (int) $r->position;
if (!isset($merged[$key])) {
$merged[$key] = array(
'surface' => (string) $r->surface,
'position' => (int) $r->position,
'impressions' => 0,
'clicks' => 0,
'assisted_adds' => 0,
'assisted_purchases' => 0,
);
}
$merged[$key]['impressions'] += (int) $r->impressions;
$merged[$key]['clicks'] += (int) $r->clicks;
$merged[$key]['assisted_adds'] += (int) $r->assisted_adds;
$merged[$key]['assisted_purchases'] += (int) $r->assisted_purchases;
}
}
uasort($merged, static function ($a, $b) {
$c = strcmp($a['surface'], $b['surface']);
return $c !== 0 ? $c : ($a['position'] <=> $b['position']);
});
return array_values($merged);
}
private function render_top_products_card($addcart, $reco, $since) {
global $wpdb;
$rows = $wpdb->get_results($wpdb->prepare(
"SELECT
e.product_id AS pid,
COUNT(DISTINCT e.id) AS assisted_adds,
COUNT(DISTINCT CASE WHEN e.order_id > 0 THEN e.id END) AS assisted_purchases,
COUNT(DISTINCT r.surface) AS distinct_surfaces,
GROUP_CONCAT(DISTINCT r.surface ORDER BY r.surface SEPARATOR ', ') AS surfaces
FROM {$addcart} e
INNER JOIN {$reco} r
ON r.session_id = e.session_id
AND r.target_pid = e.product_id
AND r.ts < e.ts_added
AND e.ts_added < DATE_ADD(r.ts, INTERVAL 7 DAY)
WHERE e.ts_added >= %s
GROUP BY e.product_id
ORDER BY assisted_adds DESC
LIMIT 20",
$since
));
$top = array();
foreach ((array) $rows as $r) {
$top[] = array(
'pid' => (int) $r->pid,
'adds' => (int) $r->assisted_adds,
'purchases' => (int) $r->assisted_purchases,
'surfaces' => (string) $r->surfaces,
);
}
$head = array(
'anchor' => 'attribution-top-products-discovered-via-recommendations',
'icon' => 'package',
'heading' => 'h2',
'title' => __('Top products via recommendations', 'ai-eshop-optimizer'),
'description' => __('Products most often added to cart after appearing as a recommendation — ranked by number of reco-assisted adds.', 'ai-eshop-optimizer'),
'col' => 1,
);
if (!$top) {
$this->empty_card($head, $this->has_rows($addcart) && $this->has_rows($reco), __('No assisted adds yet.', 'ai-eshop-optimizer'));
return;
}
AIEO_Table::card($head + array(
'per_page' => 0,
'narrow' => true,
'page_id' => $this->get_id(),
'row_key' => 'pid',
'density' => 'compact',
'rows' => $top,
'caps' => array('search' => true),
'cap' => 20,
'capped' => count($top) >= 20,
'empty' => __('No assisted adds yet.', 'ai-eshop-optimizer'),
'columns' => array(
array('key' => 'pid', 'label' => __('Product', 'ai-eshop-optimizer'), 'type' => 'object', 'format' => array('show_id' => true)),
array('key' => 'adds', 'label' => __('Assisted adds', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'purchases', 'label' => __('Purchases', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'surfaces', 'label' => __('Shown on surfaces', 'ai-eshop-optimizer'), 'type' => 'text', 'width' => 'lg', 'tone' => 'secondary'),
),
));
}
private function render_top_anchors_card($addcart, $reco, $since) {
global $wpdb;
$rows = $wpdb->get_results($wpdb->prepare(
"SELECT
r.anchor_pid AS anchor,
COUNT(DISTINCT e.id) AS assisted_adds,
COUNT(DISTINCT CASE WHEN e.order_id > 0 THEN e.id END) AS assisted_purchases,
COUNT(DISTINCT r.target_pid) AS unique_targets
FROM {$addcart} e
INNER JOIN {$reco} r
ON r.session_id = e.session_id
AND r.target_pid = e.product_id
AND r.ts < e.ts_added
AND e.ts_added < DATE_ADD(r.ts, INTERVAL 7 DAY)
WHERE e.ts_added >= %s
AND r.anchor_pid > 0
GROUP BY r.anchor_pid
ORDER BY assisted_adds DESC
LIMIT 20",
$since
));
$anchors = array();
foreach ((array) $rows as $r) {
$anchors[] = array(
'anchor' => (int) $r->anchor,
'adds' => (int) $r->assisted_adds,
'purchases' => (int) $r->assisted_purchases,
'targets' => (int) $r->unique_targets,
);
}
$head = array(
'anchor' => 'attribution-top-anchor-products',
'icon' => 'pin',
'heading' => 'h2',
'title' => __('Top anchor products', 'ai-eshop-optimizer'),
'description' => __('Product pages whose recommendation blocks drive the most cross-discovery — the "traffic hubs" of your catalog.', 'ai-eshop-optimizer'),
'col' => 2,
);
if (!$anchors) {
$this->empty_card($head, $this->has_rows($addcart) && $this->has_rows($reco), __('No anchor-attributed adds yet.', 'ai-eshop-optimizer'));
return;
}
AIEO_Table::card($head + array(
'per_page' => 0,
'narrow' => true,
'page_id' => $this->get_id(),
'row_key' => 'anchor',
'density' => 'compact',
'rows' => $anchors,
'caps' => array('search' => true),
'cap' => 20,
'capped' => count($anchors) >= 20,
'empty' => __('No anchor-attributed adds yet.', 'ai-eshop-optimizer'),
'columns' => array(
array('key' => 'anchor', 'label' => __('Anchor product', 'ai-eshop-optimizer'), 'type' => 'object', 'format' => array('show_id' => true)),
array('key' => 'adds', 'label' => __('Assisted adds', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'purchases', 'label' => __('Purchases', 'ai-eshop-optimizer'), 'type' => 'integer'),
array('key' => 'targets', 'label' => __('Distinct targets', 'ai-eshop-optimizer'), 'type' => 'integer'),
),
));
}
private function render_detail_card($range_days) {
list($open, $close) = AIEO_DMM_UI::card(array(
'anchor' => 'attribution-products-added-bought-after-seeing-them-as-r',
'icon' => 'sparkles',
'title' => __('Recommendation adds and purchases', 'ai-eshop-optimizer'),
'description' => __('One row per (cart-add, reco touch) pair. Sort headers, paginate, or search by product name / order.', 'ai-eshop-optimizer'),
'span' => 'all',
));
echo $open;
\Rooster\Admin\States::enqueue();
$filters = $this->detail_filters();
$values = \Rooster\Admin\Filters::values($filters, 'ad_');
$view = $this->detail_rows(array(
'days' => $range_days,
'page' => isset($_GET['ad_paged']) ? (int) $_GET['ad_paged'] : 1,
'per_page' => isset($_GET['ad_per_page']) ? (int) $_GET['ad_per_page'] : 25,
'search' => isset($_GET['ad_s']) ? sanitize_text_field(wp_unslash((string) $_GET['ad_s'])) : '',
'badge' => (string) ($values['badge'] ?? ''),
'orderby' => isset($_GET['ad_orderby']) ? sanitize_key(wp_unslash((string) $_GET['ad_orderby'])) : 'bought_ts',
'order' => isset($_GET['ad_order']) ? sanitize_key(wp_unslash((string) $_GET['ad_order'])) : 'desc',
));
$period = __('Range', 'ai-eshop-optimizer') . ': ' . sprintf(
__('Last %d days', 'ai-eshop-optimizer'),
(int) $range_days
);
$n = (int) $view['meta']['filtered'];
$facts = array($period);
if ('' !== $view['badge']) {
$facts[] = __('Badge', 'ai-eshop-optimizer') . ': ' . $this->detail_badges()[$view['badge']];
}
if ('' !== $view['search']) {
$facts[] = __('Search', 'ai-eshop-optimizer') . ': ' . $view['search'];
}
$count = $n ? sprintf(_n('%s assisted add', '%s assisted adds', $n, 'ai-eshop-optimizer'), number_format_i18n($n)) : '';
echo \Rooster\Admin\States::result_head($count, array('id' => 'aieo-attr-dt-result', 'facts' => $n ? $facts : array(), 'attrs' => array('data-period' => $period) + ($n ? array() : array('hidden' => true))));
$ever = $this->has_rows(AIEO_Attribution::get_table_name()) && $this->has_rows(AIEO_Attribution::get_reco_table_name());
$empty = $ever
? \Rooster\Admin\States::state('no-results')
: \Rooster\Admin\States::state('first-use', array('title' => __('No assisted adds yet.', 'ai-eshop-optimizer')));
$columns = $this->detail_columns($view);
echo \Rooster\Admin\Ui::table($columns, $view['rows'], array(
'id' => 'aieo-attr-detail-table',
'caption' => __('Recommendation adds and purchases', 'ai-eshop-optimizer'),
'empty' => $empty,
'filters' => $filters,
'filter_values' => $values,
'search' => array(
'label' => __('Search', 'ai-eshop-optimizer'),
'placeholder' => __('Search product or order…', 'ai-eshop-optimizer'),
),
'remote' => array(
'url' => admin_url('admin-ajax.php?action=aieo_attribution_rows'),
'nonce' => wp_create_nonce('aieo_attr_admin'),
'scope' => array('days' => (int) $range_days),
'context' => 'admin',
'size' => (int) $view['meta']['per_page'],
'sizes' => array(10, 25, 50, 100, 200),
'total' => $n,
'paged' => (int) $view['meta']['page'],
'sort' => array('key' => 'bought_ts', 'dir' => 'desc'),
'prefix' => 'ad_',
),
));
echo $close;
}
private function detail_badges() {
return array(
'sale' => _x('SALE', 'sale badge', 'ai-eshop-optimizer'),
'new' => _x('NEW', 'new badge', 'ai-eshop-optimizer'),
'gift' => __('Free gift', 'ai-eshop-optimizer'),
'monthly' => __('Monthly offer', 'ai-eshop-optimizer'),
);
}
private function detail_filters() {
return array(
array(
'key' => 'badge',
'label' => __('Badge', 'ai-eshop-optimizer'),
'options' => $this->detail_badges(),
'any' => __('Any badge', 'ai-eshop-optimizer'),
),
);
}
private function detail_columns(array $view) {
$cols = array(
array('key' => 'status', 'label' => __('Status', 'ai-eshop-optimizer'), 'type' => 'html', 'sorts' => true),
array('key' => 'bought', 'label' => __('Bought / added', 'ai-eshop-optimizer')),
array('key' => 'closing_source', 'label' => __('Closed via', 'ai-eshop-optimizer'), 'type' => 'code', 'sorts' => true),
array('key' => 'assist_type', 'label' => __('Reco type', 'ai-eshop-optimizer'), 'sorts' => true),
array('key' => 'assist_surface', 'label' => __('Reco surface', 'ai-eshop-optimizer'), 'sorts' => true),
array('key' => 'assist_position', 'label' => __('Slot', 'ai-eshop-optimizer'), 'type' => 'num', 'sorts' => true),
array('key' => 'anchor', 'label' => __('Shown on / under', 'ai-eshop-optimizer')),
array('key' => 'gap_minutes', 'label' => __('Gap', 'ai-eshop-optimizer'), 'type' => 'num', 'sorts' => true),
array('key' => 'bought_ts', 'label' => __('Added at', 'ai-eshop-optimizer'), 'sorts' => true),
);
foreach ($cols as $i => $col) {
if (!empty($col['sorts'])) {
$on = $view['orderby'] === $col['key'];
$cols[$i]['sort'] = $on ? $view['order'] : 'none';
$cols[$i]['sort_url'] = add_query_arg(array('ad_orderby' => $col['key'], 'ad_order' => $on && 'asc' === $view['order'] ? 'desc' : 'asc'), wp_doing_ajax() ? (string) wp_get_referer() : false);
$cols[$i]['sort_type'] = in_array($col['key'], array('assist_position', 'gap_minutes'), true) ? 'number' : ('bought_ts' === $col['key'] ? 'date' : 'text');
}
unset($cols[$i]['sorts']);
}
return $cols;
}
private function detail_rows(array $args) {
global $wpdb;
$addcart = AIEO_Attribution::get_table_name();
$reco = AIEO_Attribution::get_reco_table_name();
$days = max(1, (int) ($args['days'] ?? 30));
$since = gmdate('Y-m-d H:i:s', time() - ($days * DAY_IN_SECONDS));
$page = max(1, (int) ($args['page'] ?? 1));
$per_page = max(10, min(200, (int) ($args['per_page'] ?? 25)));
$search = sanitize_text_field((string) ($args['search'] ?? ''));
$badge_in = sanitize_key((string) ($args['badge'] ?? ''));
$badge_clauses = array(
'sale' => 'e.bcs_was_on_sale = 1',
'new' => 'e.bcs_is_new = 1',
'gift' => 'e.bcs_gift_offer_id IS NOT NULL AND e.bcs_gift_offer_id > 0',
'monthly' => 'e.bcs_monthly_offer_id IS NOT NULL AND e.bcs_monthly_offer_id > 0',
);
$badge_in = isset($badge_clauses[$badge_in]) ? $badge_in : '';
$badge_where = '' !== $badge_in ? ' AND ' . $badge_clauses[$badge_in] : '';
$valid_sort = array(
'status' => 'order_id',
'bought_pid' => 'bought_pid',
'closing_source' => 'closing_source',
'assist_type' => 'assist_type',
'assist_surface' => 'assist_surface',
'assist_position'=> 'assist_position',
'anchor_pid' => 'anchor_pid',
'bought_ts' => 'bought_ts',
'gap_minutes' => 'gap_minutes',
);
$sort_key = sanitize_key((string) ($args['orderby'] ?? 'bought_ts'));
if (!isset($valid_sort[$sort_key])) { $sort_key = 'bought_ts'; }
$sort_col = $valid_sort[$sort_key];
$dir = 'asc' === strtolower((string) ($args['order'] ?? '')) ? 'ASC' : 'DESC';
$search_join = '';
$search_where = '';
$search_args = array();
if ($search !== '') {
$like = '%' . $wpdb->esc_like($search) . '%';
$search_join = "
LEFT JOIN {$wpdb->posts} p_bought ON p_bought.ID = e.product_id
LEFT JOIN {$wpdb->posts} p_anchor ON p_anchor.ID = r.anchor_pid";
$pid_guess = (int) preg_replace('/\D/', '', $search);
if ($pid_guess > 0) {
$search_where = "AND (
p_bought.post_title LIKE %s
OR p_anchor.post_title LIKE %s
OR e.product_id = %d
OR r.anchor_pid = %d
OR e.order_id = %d
)";
$search_args = array($like, $like, $pid_guess, $pid_guess, $pid_guess);
} else {
$search_where = 'AND (p_bought.post_title LIKE %s OR p_anchor.post_title LIKE %s)';
$search_args = array($like, $like);
}
}
$base_select = "SELECT
e.id AS addcart_id,
e.order_id,
e.product_id AS bought_pid,
e.source AS closing_source,
e.ts_added AS bought_ts,
e.bcs_was_on_sale AS bcs_sale,
e.bcs_is_new AS bcs_new,
e.bcs_gift_offer_id AS bcs_gift,
e.bcs_monthly_offer_id AS bcs_monthly,
r.event_type AS assist_type,
r.surface AS assist_surface,
r.position AS assist_position,
r.anchor_pid AS anchor_pid,
r.anchor_term_id AS anchor_term_id,
r.anchor_term_tax AS anchor_term_tax,
r.ts AS assist_ts,
TIMESTAMPDIFF(MINUTE, r.ts, e.ts_added) AS gap_minutes,
ROW_NUMBER() OVER (PARTITION BY e.id ORDER BY r.ts DESC, r.id DESC) AS rn
FROM {$addcart} e
INNER JOIN {$reco} r
ON r.session_id = e.session_id
AND r.target_pid = e.product_id
AND r.ts < e.ts_added
AND e.ts_added < DATE_ADD(r.ts, INTERVAL 7 DAY)";
$count_sql = "SELECT COUNT(*) FROM ({$base_select}
{$search_join}
WHERE e.ts_added >= %s {$search_where} {$badge_where}
) sub WHERE sub.rn = 1";
$count_args = array_merge(array($since), $search_args);
$filtered = (int) $wpdb->get_var($wpdb->prepare($count_sql, $count_args));
$total = (int) $wpdb->get_var($wpdb->prepare(
"SELECT COUNT(*) FROM ({$base_select}
WHERE e.ts_added >= %s
) sub WHERE sub.rn = 1", $since
));
$page = min($page, max(1, (int) ceil($filtered / $per_page)));
$offset = ($page - 1) * $per_page;
$rows_sql = "SELECT * FROM ({$base_select}
{$search_join}
WHERE e.ts_added >= %s {$search_where} {$badge_where}
) sub
WHERE sub.rn = 1
ORDER BY {$sort_col} {$dir}, addcart_id DESC
LIMIT %d OFFSET %d";
$rows_args = array_merge(array($since), $search_args, array($per_page, $offset));
$rows = $wpdb->get_results($wpdb->prepare($rows_sql, $rows_args));
$out = array();
foreach ((array) $rows as $r) {
$order = (int) $r->order_id;
$pid = (int) $r->bought_pid;
$anchor = (int) $r->anchor_pid;
$gap = (int) $r->gap_minutes;
$status = $order > 0
? \Rooster\Admin\Ui::badge(__('Purchased', 'ai-eshop-optimizer'), 'success', array('sm' => true)) . ' #' . esc_html((string) $order) . ''
: \Rooster\Admin\Ui::badge(__('In cart', 'ai-eshop-optimizer'), 'warning', array('sm' => true));
if ($anchor > 0 && '' !== (string) get_the_title($anchor)) {
$shown = array('title' => (string) get_the_title($anchor), 'url' => admin_url('post.php?post=' . $anchor . '&action=edit'), 'sub' => '#' . $anchor);
} else {
$term = $this->resolve_term_info((int) $r->anchor_term_id, (string) $r->anchor_term_tax);
$kinds = array(
'product_cat' => __('Category', 'ai-eshop-optimizer'),
'product_brand' => __('Brand', 'ai-eshop-optimizer'),
'product_tag' => __('Tag', 'ai-eshop-optimizer'),
);
$shown = (int) $r->anchor_term_id > 0 && '' !== (string) $term['name']
? array('title' => (string) $term['name'], 'url' => (string) $term['url'], 'sub' => $kinds[(string) $r->anchor_term_tax] ?? '')
: $this->anchor_fallback_label((string) $r->assist_surface);
}
$out[] = array(
'_id' => (string) $r->addcart_id,
'status' => array('html' => $status),
'bought' => $pid > 0 ? array('title' => '' !== (string) get_the_title($pid) ? (string) get_the_title($pid) : '#' . $pid, 'url' => admin_url('post.php?post=' . $pid . '&action=edit'), 'sub' => '#' . $pid) : '—',
'closing_source' => (string) $r->closing_source,
'assist_type' => (string) $r->assist_type,
'assist_surface' => array('title' => (string) $r->assist_surface, 'sub' => $this->surface_label((string) $r->assist_surface)),
'assist_position' => (int) $r->assist_position > 0 ? '#' . (int) $r->assist_position : '—',
'anchor' => '' !== $shown ? $shown : '—',
'gap_minutes' => $gap < 60 ? sprintf(__('%s min', 'ai-eshop-optimizer'), number_format_i18n($gap)) : sprintf( __('%s h', 'ai-eshop-optimizer'), number_format_i18n($gap / 60, 1)),
'bought_ts' => (string) $r->bought_ts,
);
}
return array(
'rows' => $out,
'meta' => array(
'total' => $total,
'filtered' => $filtered,
'page' => $page,
'per_page' => $per_page,
'orderby' => $sort_key,
'order' => strtolower($dir),
),
'orderby' => $sort_key,
'order' => strtolower($dir),
'search' => $search,
'badge' => $badge_in,
);
}
public function ajax_rows() {
check_ajax_referer('aieo_attr_admin');
if (!current_user_can(AIEO_CAPABILITY)) { wp_send_json(array('message' => __('Permission denied.', 'ai-eshop-optimizer')), 403); }
if (!class_exists('\Rooster\Admin\Ui') && (!function_exists('rooster_admin_builders') || !rooster_admin_builders())) {
wp_send_json(array('message' => __('Failed to load rows.', 'ai-eshop-optimizer')), 500);
}
$params = isset($_GET['params']) && is_array($_GET['params']) ? wp_unslash($_GET['params']) : array();
$values = \Rooster\Admin\Filters::values($this->detail_filters(), '', $params);
$view = $this->detail_rows(array(
'days' => isset($_GET['days']) ? (int) $_GET['days'] : 30,
'page' => isset($_GET['page']) ? (int) $_GET['page'] : 1,
'per_page' => isset($_GET['per_page']) ? (int) $_GET['per_page'] : 25,
'search' => isset($_GET['search']) ? sanitize_text_field(wp_unslash((string) $_GET['search'])) : '',
'badge' => (string) ($values['badge'] ?? ''),
'orderby' => isset($_GET['orderby']) ? sanitize_key(wp_unslash((string) $_GET['orderby'])) : 'bought_ts',
'order' => isset($_GET['order']) ? sanitize_key(wp_unslash((string) $_GET['order'])) : 'desc',
));
wp_send_json(\Rooster\Admin\Ui::answer($this->detail_columns($view), $view['rows'], $view['meta']));
}
public function handle_clear() {
if (!current_user_can(AIEO_CAPABILITY)) {
wp_die(esc_html__('Permission denied.', 'ai-eshop-optimizer'), 403);
}
check_admin_referer('aieo_attribution_clear');
global $wpdb;
$wpdb->query('TRUNCATE TABLE ' . AIEO_Attribution::get_table_name());
$wpdb->query('TRUNCATE TABLE ' . AIEO_Attribution::get_reco_table_name());
wp_safe_redirect(add_query_arg(
array('page' => 'ai-eshop-dmm', 'dmm_tab' => $this->get_id(), 'cleared' => 1),
admin_url('admin.php')
));
exit;
}
public function handle_export() {
if (!current_user_can(AIEO_CAPABILITY)) {
wp_die(esc_html__('Permission denied.', 'ai-eshop-optimizer'), 403);
}
check_admin_referer('aieo_attribution_export');
global $wpdb;
$addcart = AIEO_Attribution::get_table_name();
$reco = AIEO_Attribution::get_reco_table_name();
$days = isset($_POST['attr_days']) ? max(1, (int) $_POST['attr_days']) : 30;
$since = gmdate('Y-m-d H:i:s', time() - ($days * DAY_IN_SECONDS));
$assist_rows = $wpdb->get_results($wpdb->prepare(
"SELECT * FROM (
SELECT
e.order_id,
e.product_id AS bought_pid,
e.source AS closing_source,
e.ts_added AS bought_ts,
r.event_type AS assist_type,
r.surface AS assist_surface,
r.position AS assist_position,
r.anchor_pid AS anchor_pid,
r.ts AS assist_ts,
TIMESTAMPDIFF(MINUTE, r.ts, e.ts_added) AS gap_minutes,
ROW_NUMBER() OVER (PARTITION BY e.id ORDER BY r.ts DESC, r.id DESC) AS rn
FROM {$addcart} e
INNER JOIN {$reco} r
ON r.session_id = e.session_id
AND r.target_pid = e.product_id
AND r.ts < e.ts_added
AND e.ts_added < DATE_ADD(r.ts, INTERVAL 7 DAY)
WHERE e.ts_added >= %s
) sub
WHERE sub.rn = 1
ORDER BY bought_ts DESC, assist_ts DESC",
$since
), ARRAY_A);
$sheet = array();
foreach ($assist_rows as $r) {
$sheet[] = array(
'Status' => (int) $r['order_id'] > 0 ? 'Purchased' : 'In cart',
'Order ID' => (int) $r['order_id'] > 0 ? (int) $r['order_id'] : '',
'Bought PID' => (int) $r['bought_pid'],
'Bought name' => (string) get_the_title((int) $r['bought_pid']),
'Closing source' => (string) $r['closing_source'],
'Reco type' => (string) $r['assist_type'],
'Reco surface' => (string) $r['assist_surface'],
'Slot #' => (int) $r['assist_position'],
'Anchor PID' => (int) $r['anchor_pid'],
'Anchor name' => (int) $r['anchor_pid'] > 0 ? (string) get_the_title((int) $r['anchor_pid']) : '',
'Reco shown at' => (string) $r['assist_ts'],
'Added at' => (string) $r['bought_ts'],
'Gap (min)' => (int) $r['gap_minutes'],
);
}
if (!class_exists('\Rooster\Admin\Export') && function_exists('rooster_admin_builders')) {
rooster_admin_builders();
}
if (!class_exists('\Rooster\Admin\Export')) { wp_die(esc_html__('XLSX writer unavailable.', 'ai-eshop-optimizer')); }
$id = array('type' => 'number');
$columns = array(
'Status' => 'Status',
'Order ID' => array('label' => 'Order ID') + $id,
'Bought PID' => array('label' => 'Bought PID') + $id,
'Bought name' => 'Bought name',
'Closing source' => 'Closing source',
'Reco type' => 'Reco type',
'Reco surface' => 'Reco surface',
'Slot #' => array('label' => 'Slot #') + $id,
'Anchor PID' => array('label' => 'Anchor PID') + $id,
'Anchor name' => 'Anchor name',
'Reco shown at' => array('label' => 'Reco shown at', 'type' => 'date'),
'Added at' => array('label' => 'Added at', 'type' => 'date'),
'Gap (min)' => array('label' => 'Gap (min)', 'format' => array('decimals' => 0)),
);
\Rooster\Admin\Export::xlsx('aieo-attribution-' . gmdate('Y-m-d-His') . '.xlsx', array(array('name' => 'Reco-assisted adds', 'columns' => $columns, 'rows' => $sheet)));
exit;
}
private function surface_label($source) {
$map = array(
'archive' => __('Shop / category listing', 'ai-eshop-optimizer'),
'home' => __('Home page', 'ai-eshop-optimizer'),
'single_product' => __('Product page — main', 'ai-eshop-optimizer'),
'cart' => __('Cart page', 'ai-eshop-optimizer'),
'checkout' => __('Checkout page', 'ai-eshop-optimizer'),
'product_upsells' => __('Product page — upsells', 'ai-eshop-optimizer'),
'product_crosssells' => __('Product page — cross-sells', 'ai-eshop-optimizer'),
'product_needs' => __('Product page — product needs', 'ai-eshop-optimizer'),
'product_related' => __('Product page — related', 'ai-eshop-optimizer'),
'product_recently_viewed' => __('Product page — recently viewed', 'ai-eshop-optimizer'),
'semantic_related' => __('Semantic related content', 'ai-eshop-optimizer'),
'sidecart_recently_viewed' => __('Side cart — recently viewed', 'ai-eshop-optimizer'),
'sidecart_favorites' => __('Side cart — favorites', 'ai-eshop-optimizer'),
'sidecart_may_also_like' => __('Side cart — you may also like', 'ai-eshop-optimizer'),
'sidecart_product_needs' => __('Side cart — product needs', 'ai-eshop-optimizer'),
'sidecart_might_like' => __('Side cart — you might like', 'ai-eshop-optimizer'),
'sidecart_quickview' => __('Side cart — quick view', 'ai-eshop-optimizer'),
'unknown' => __('Unknown', 'ai-eshop-optimizer'),
);
return isset($map[$source]) ? $map[$source] : $source;
}
private function anchor_fallback_label($surface) {
if (strpos($surface, 'sidecart_') === 0) { return __('side cart', 'ai-eshop-optimizer'); }
if ($surface === 'archive') { return __('archive listing', 'ai-eshop-optimizer'); }
if ($surface === 'home') { return __('home page', 'ai-eshop-optimizer'); }
return __('no anchor', 'ai-eshop-optimizer');
}
private function resolve_term_info($term_id, $taxonomy) {
if ($term_id <= 0 || $taxonomy === '') {
return array('name' => '', 'url' => '');
}
$term = get_term($term_id, $taxonomy);
if (!$term || is_wp_error($term)) {
return array('name' => '#' . $term_id, 'url' => '');
}
$url = get_edit_term_link($term_id, $taxonomy);
return array(
'name' => (string) $term->name,
'url' => $url ? (string) $url : '',
);
}
private function taxonomy_label($taxonomy) {
$map = array(
'product_cat' => __('Category', 'ai-eshop-optimizer'),
'product_tag' => __('Tag', 'ai-eshop-optimizer'),
'product_brand' => __('Brand', 'ai-eshop-optimizer'),
);
return isset($map[$taxonomy]) ? $map[$taxonomy] : $taxonomy;
}
}