__( 'Landing pages by campaign', 'ai-eshop-optimizer' ), 'description' => __( 'Traffic-source × landing-page performance from the canonical session stats (free local read): sessions, cart adders/adds, goal conversions per landing_path + utm_source/medium/campaign + referrer_host + channel over the last N days. Covers UNTAGGED referral traffic too: a plain link from another site shows as channel=referral with the referring host. Channels: paid_google (incl. gclid-only Google Ads), paid_facebook, social, organic_search, newsletter, referral, direct. Filter by utm_source, channel, or landing_path substring.', 'ai-eshop-optimizer' ), 'category' => \AIEO\Claude\Bootstrap::CATEGORY, 'input_schema' => array( 'default' => array(), 'type' => 'object', 'properties' => array( 'days' => array( 'type' => 'integer', 'minimum' => 1, 'maximum' => 365, 'default' => 30 ), 'utm_source' => array( 'type' => 'string' ), 'channel' => array( 'type' => 'string', 'description' => 'paid_google | paid_facebook | paid_tiktok | paid_microsoft | paid_other | newsletter | social | campaign_other | organic_search | referral | direct' ), 'landing_path' => array( 'type' => 'string', 'description' => 'Substring match on the canonical landing path.' ), 'limit' => array( 'type' => 'integer', 'minimum' => 1, 'maximum' => 200, 'default' => 30 ), ), ), 'output_schema' => array( 'type' => 'object' ), 'execute_callback' => array( __CLASS__, 'execute' ), 'permission_callback' => array( __CLASS__, 'permission_check' ), 'meta' => array( 'annotations' => array( 'readonly' => true, 'idempotent' => true ), 'show_in_rest' => true, ), ) ); } public static function permission_check( $input = null ) { unset( $input ); return current_user_can( 'manage_woocommerce' ) || current_user_can( 'manage_options' ); } public static function execute( $input ) { global $wpdb; $input = is_array( $input ) ? $input : array(); $days = isset( $input['days'] ) ? max( 1, min( 365, (int) $input['days'] ) ) : 30; $limit = isset( $input['limit'] ) ? max( 1, min( 200, (int) $input['limit'] ) ) : 30; $table = $wpdb->prefix . 'aieo_landing_campaign_daily'; if ( (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) { return array( 'campaigns' => array(), 'hint' => __( 'No landing/campaign rollup yet — it builds with the nightly cron once session tracking is enabled.', 'ai-eshop-optimizer' ) ); } $since = gmdate( 'Y-m-d', time() - $days * DAY_IN_SECONDS ); $where = $wpdb->prepare( '`date` >= %s', $since ); if ( ! empty( $input['utm_source'] ) && is_string( $input['utm_source'] ) ) { $where .= $wpdb->prepare( ' AND utm_source = %s', sanitize_text_field( $input['utm_source'] ) ); } if ( ! empty( $input['channel'] ) && is_string( $input['channel'] ) ) { $where .= $wpdb->prepare( ' AND channel = %s', sanitize_key( $input['channel'] ) ); } if ( ! empty( $input['landing_path'] ) && is_string( $input['landing_path'] ) ) { $where .= $wpdb->prepare( ' AND landing_path LIKE %s', '%' . $wpdb->esc_like( sanitize_text_field( $input['landing_path'] ) ) . '%' ); } $has_ladder = (bool) $wpdb->get_var( "SHOW COLUMNS FROM `{$table}` LIKE 'purchases'" ); $ladder_sel = $has_ladder ? 'SUM(engaged_sessions) AS engaged_sessions, SUM(purchases) AS purchases' : '0 AS engaged_sessions, 0 AS purchases'; $rows = $wpdb->get_results( $wpdb->prepare( "SELECT landing_path, utm_source, utm_medium, utm_campaign, referrer_host, channel, SUM(sessions) AS sessions, {$ladder_sel}, SUM(cart_adders) AS cart_adders, SUM(cart_adds) AS cart_adds, SUM(goals) AS goals, MIN(`date`) AS first_day, MAX(`date`) AS last_day FROM `{$table}` WHERE {$where} GROUP BY landing_path, utm_source, utm_medium, utm_campaign, referrer_host, channel ORDER BY sessions DESC LIMIT %d", $limit ), ARRAY_A ); return array( 'window_days' => $days, 'campaigns' => is_array( $rows ) ? $rows : array(), ); } } class ExitPagesAbility { const ABILITY_NAME = 'aieo/exit-pages'; public static function register() { wp_register_ability( self::ABILITY_NAME, array( 'label' => __( 'Exit pages', 'ai-eshop-optimizer' ), 'description' => __( 'Where sessions END, from the canonical exit-page rollup (free local read): per exit_path over the last N days — exiting sessions, converted_sessions (the session fired a goal ANYWHERE before leaving — converters often keep browsing past the goal page), cart_sessions, average session duration. High exits with zero conversions = bleed points; high exits WITH conversions = natural end-of-journey pages.', 'ai-eshop-optimizer' ), 'category' => \AIEO\Claude\Bootstrap::CATEGORY, 'input_schema' => array( 'default' => array(), 'type' => 'object', 'properties' => array( 'days' => array( 'type' => 'integer', 'minimum' => 1, 'maximum' => 365, 'default' => 30 ), 'exit_path' => array( 'type' => 'string', 'description' => 'Substring match on the canonical exit path.' ), 'limit' => array( 'type' => 'integer', 'minimum' => 1, 'maximum' => 200, 'default' => 30 ), ), ), 'output_schema' => array( 'type' => 'object' ), 'execute_callback' => array( __CLASS__, 'execute' ), 'permission_callback' => array( __CLASS__, 'permission_check' ), 'meta' => array( 'annotations' => array( 'readonly' => true, 'idempotent' => true ), 'show_in_rest' => true, ), ) ); } public static function permission_check( $input = null ) { unset( $input ); return current_user_can( 'manage_woocommerce' ) || current_user_can( 'manage_options' ); } public static function execute( $input ) { global $wpdb; $input = is_array( $input ) ? $input : array(); $days = isset( $input['days'] ) ? max( 1, min( 365, (int) $input['days'] ) ) : 30; $limit = isset( $input['limit'] ) ? max( 1, min( 200, (int) $input['limit'] ) ) : 30; $table = $wpdb->prefix . 'aieo_exit_daily'; if ( (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) { return array( 'exits' => array(), 'hint' => __( 'No exit-page rollup yet — it builds with the nightly cron once session tracking is enabled (exits accrue from deployment forward).', 'ai-eshop-optimizer' ) ); } $since = gmdate( 'Y-m-d', time() - $days * DAY_IN_SECONDS ); $where = $wpdb->prepare( '`date` >= %s', $since ); if ( ! empty( $input['exit_path'] ) && is_string( $input['exit_path'] ) ) { $where .= $wpdb->prepare( ' AND exit_path LIKE %s', '%' . $wpdb->esc_like( sanitize_text_field( $input['exit_path'] ) ) . '%' ); } $rows = $wpdb->get_results( $wpdb->prepare( "SELECT exit_path, SUM(sessions) AS sessions, SUM(converted_sessions) AS converted_sessions, SUM(cart_sessions) AS cart_sessions, ROUND(AVG(avg_duration_seconds)) AS avg_duration_seconds FROM `{$table}` WHERE {$where} GROUP BY exit_path ORDER BY sessions DESC LIMIT %d", $limit ), ARRAY_A ); return array( 'window_days' => $days, 'exits' => is_array( $rows ) ? $rows : array(), ); } }