prefix . self::TABLE; } public static function excluded_object_ids() { $ids = array( (int) get_option( 'woocommerce_cart_page_id' ), (int) get_option( 'woocommerce_checkout_page_id' ), (int) get_option( 'woocommerce_myaccount_page_id' ), (int) get_option( 'woocommerce_shop_page_id' ), (int) get_option( 'woocommerce_terms_page_id' ), (int) get_option( 'page_on_front' ), (int) get_option( 'page_for_posts' ), (int) get_option( 'wp_page_for_privacy_policy' ), ); $ids = array_values( array_unique( array_filter( array_map( 'intval', $ids ) ) ) ); return array_values( array_filter( array_map( 'intval', (array) apply_filters( 'aieo_cooccurrence_excluded_ids', $ids ) ) ) ); } public static function noise_gate_sql( $col ) { global $wpdb; $sql = ''; $excluded = self::excluded_object_ids(); $types = self::allowed_post_types(); if ( $excluded ) { $sql .= ' AND ' . $col . ' NOT IN (' . implode( ',', array_map( 'intval', $excluded ) ) . ')'; } if ( $types ) { $quoted = array(); foreach ( $types as $t ) { $quoted[] = "'" . esc_sql( $t ) . "'"; } $sql .= " AND EXISTS (SELECT 1 FROM {$wpdb->posts} np WHERE np.ID = " . $col . ' AND np.post_type IN (' . implode( ',', $quoted ) . '))'; } return $sql; } const TS_FLOOR_MS = 1577836800000; public static function ts_ceil_ms() { return ( time() + DAY_IN_SECONDS ) * 1000; } public static function allowed_post_types() { return array_values( array_unique( array_filter( array_map( 'strval', (array) apply_filters( 'aieo_cooccurrence_post_types', array( 'product', 'post', 'page' ) ) ) ) ) ); } private static function charset() { global $wpdb; $collation = $wpdb->get_var( $wpdb->prepare( 'SELECT TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s', $wpdb->posts ) ); if ( $collation ) { $charset = (string) preg_replace( '/_.*$/', '', $collation ); return "DEFAULT CHARACTER SET {$charset} COLLATE {$collation}"; } return aieo_get_charset_collate(); } public static function ensure_table() { global $wpdb; $table = self::table(); $charset = self::charset(); $wpdb->query( "CREATE TABLE IF NOT EXISTS `{$table}` ( a_id bigint(20) unsigned NOT NULL, b_id bigint(20) unsigned NOT NULL, date date NOT NULL, co_sessions int(10) unsigned NOT NULL DEFAULT 0, downstream_carts int(10) unsigned NOT NULL DEFAULT 0, downstream_conversions int(10) unsigned NOT NULL DEFAULT 0, UNIQUE KEY pair_date (a_id, b_id, date), KEY b_date (b_id, date), KEY date_idx (date) ) {$charset}" ); self::ensure_columns(); } public static function ensure_columns() { global $wpdb; $table = self::table(); $columns = array( 'pair_weight' => '`pair_weight` double NOT NULL DEFAULT 0', 'adjacent_sessions' => '`adjacent_sessions` int(10) unsigned NOT NULL DEFAULT 0', 'forward_sessions' => '`forward_sessions` int(10) unsigned NOT NULL DEFAULT 0', 'carted_sessions' => '`carted_sessions` int(10) unsigned NOT NULL DEFAULT 0', ); $have = $wpdb->get_col( $wpdb->prepare( 'SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s', $table ) ); if ( ! is_array( $have ) || ! $have ) { return 0; } $have = array_map( 'strtolower', $have ); $added = 0; foreach ( $columns as $name => $ddl ) { if ( in_array( strtolower( $name ), $have, true ) ) { continue; } if ( false !== $wpdb->query( "ALTER TABLE `{$table}` ADD COLUMN {$ddl}" ) ) { $added++; } } return $added; } public static function event_weights() { return (array) apply_filters( 'aieo_cooccurrence_event_weights', array( 'page_view' => 1.0, 'product_view' => 1.0, 'product_image_click' => 2.0, 'add_to_cart' => 4.0, ) ); } private static function weight_case_sql( $col = 'event_type' ) { $sql = 'CASE'; foreach ( self::event_weights() as $type => $w ) { $sql .= " WHEN {$col} = '" . esc_sql( (string) $type ) . "' THEN " . (float) $w; } return $sql . ' ELSE 0 END'; } public static function compute_daily( $date = null ) { global $wpdb; $yesterday = gmdate( 'Y-m-d', time() - DAY_IN_SECONDS ); if ( null === $date || ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', (string) $date ) || $date > $yesterday ) { $date = $yesterday; } if ( self::table_exists( $wpdb->prefix . 'aieo_sessions' ) ) { return self::compute_daily_from_events( $date ); } $journeys = $wpdb->prefix . 'aieo_session_journeys'; if ( ! self::table_exists( $journeys ) ) { return 0; } self::ensure_table(); $table = self::table(); $start_ms = strtotime( $date . ' 00:00:00 UTC' ) * 1000; $end_ms = $start_ms + DAY_IN_SECONDS * 1000; $noise_sql = self::noise_gate_sql( 'j.product_id' ); $sql = "INSERT INTO `{$table}` (a_id, b_id, date, co_sessions, downstream_carts, downstream_conversions) WITH sess AS ( SELECT j.session_id, MAX(j.did_add_to_cart) AS sess_carted FROM `{$journeys}` j WHERE j.event_timestamp >= %d AND j.event_timestamp < %d AND j.product_id > 0{$noise_sql} GROUP BY j.session_id HAVING COUNT(DISTINCT j.product_id) BETWEEN 2 AND %d ), sp AS ( SELECT j.session_id, j.product_id FROM `{$journeys}` j JOIN sess ON sess.session_id = j.session_id WHERE j.event_timestamp >= %d AND j.event_timestamp < %d AND j.product_id > 0{$noise_sql} GROUP BY j.session_id, j.product_id ) SELECT a.product_id, b.product_id, %s, COUNT(*) AS co_sessions, SUM(sess.sess_carted) AS downstream_carts, 0 FROM sp a JOIN sp b ON b.session_id = a.session_id AND b.product_id > a.product_id JOIN sess ON sess.session_id = a.session_id GROUP BY a.product_id, b.product_id ON DUPLICATE KEY UPDATE co_sessions = VALUES(co_sessions), downstream_carts = VALUES(downstream_carts)"; $wpdb->query( $wpdb->prepare( $sql, $start_ms, $end_ms, self::MAX_PRODUCTS_PER_SESSION, $start_ms, $end_ms, $date ) ); $written = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM `{$table}` WHERE date = %s", $date ) ); $wpdb->query( $wpdb->prepare( "DELETE FROM `{$table}` WHERE date < DATE_SUB(UTC_DATE(), INTERVAL %d DAY)", self::RETENTION_DAYS ) ); return $written; } public static function compute_daily_from_events( $date ) { global $wpdb; if ( ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', (string) $date ) ) { return 0; } $hot = $wpdb->prefix . 'aieo_sessions'; $archive = $hot . '_archive'; if ( ! self::table_exists( $hot ) ) { return 0; } self::ensure_table(); $start_ms = strtotime( $date . ' 00:00:00 UTC' ) * 1000; $end_ms = $start_ms + DAY_IN_SECONDS * 1000; $sources = array( $hot ); if ( self::table_exists( $archive ) ) { $sources[] = $archive; } $weight_case = self::weight_case_sql( 'event_type' ); $parts = array(); $args = array(); foreach ( $sources as $src ) { $parts[] = "SELECT session_id, CAST(COALESCE(NULLIF(product_id, 0), JSON_EXTRACT(event_data, '$.page_id')) AS UNSIGNED) AS object_id, CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END AS carted, {$weight_case} AS weight, timestamp AS ts, event_type FROM `{$src}` WHERE timestamp >= %d AND timestamp < %d AND timestamp BETWEEN %d AND %d AND session_id <> ''"; array_push( $args, $start_ms, $end_ms, self::TS_FLOOR_MS, self::ts_ceil_ms() ); } $union = implode( ' UNION ALL ', $parts ); $gate = self::noise_gate_sql( 'ev.object_id' ); $table = self::table(); $sql = "INSERT INTO `{$table}` (a_id, b_id, date, co_sessions, downstream_carts, downstream_conversions, pair_weight, adjacent_sessions, forward_sessions, carted_sessions) WITH raw AS ( {$union} ), obj AS ( -- Every event carrying an object id is a touch of that object. Only -- four types ever do (page_view, product_view, product_image_click, -- add_to_cart) — the rest are logged with product_id = 0, so this is -- the whole vocabulary available per object. SELECT ev.session_id, ev.object_id, MAX(ev.weight) AS strength, MAX(ev.carted) AS carted, MIN(ev.ts) AS first_ts FROM raw ev WHERE ev.object_id > 0 {$gate} GROUP BY ev.session_id, ev.object_id ), ranked AS ( SELECT o.*, DENSE_RANK() OVER (PARTITION BY o.session_id ORDER BY o.first_ts) AS pos FROM obj o ), sess AS ( SELECT session_id, MAX(carted) AS any_carted FROM ranked GROUP BY session_id HAVING COUNT(DISTINCT object_id) BETWEEN 2 AND %d ) SELECT a.object_id, b.object_id, %s, COUNT(*) AS co_sessions, SUM(sess.any_carted) AS downstream_carts, 0, SUM( a.strength * b.strength / (1 + ABS(a.pos - b.pos)) ) AS pair_weight, SUM( ABS(a.pos - b.pos) = 1 ) AS adjacent_sessions, SUM( a.first_ts < b.first_ts ) AS forward_sessions, SUM( a.carted > 0 OR b.carted > 0 ) AS carted_sessions FROM ranked a JOIN ranked b ON b.session_id = a.session_id AND b.object_id > a.object_id JOIN sess ON sess.session_id = a.session_id GROUP BY a.object_id, b.object_id ON DUPLICATE KEY UPDATE co_sessions = VALUES(co_sessions), downstream_carts = VALUES(downstream_carts), pair_weight = VALUES(pair_weight), adjacent_sessions = VALUES(adjacent_sessions), forward_sessions = VALUES(forward_sessions), carted_sessions = VALUES(carted_sessions)"; array_push( $args, self::MAX_PRODUCTS_PER_SESSION, $date ); $wpdb->query( $wpdb->prepare( $sql, $args ) ); return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM `{$table}` WHERE date = %s", $date ) ); } public static function rebuild_range_from_events( $from, $to ) { global $wpdb; $out = array( 'days' => 0, 'rows' => 0, 'per_day' => array() ); $start = strtotime( $from . ' UTC' ); $end = strtotime( $to . ' UTC' ); if ( ! $start || ! $end || $start > $end ) { return $out; } $table = self::table(); for ( $ts = $start; $ts <= $end; $ts += DAY_IN_SECONDS ) { $d = gmdate( 'Y-m-d', $ts ); $wpdb->query( $wpdb->prepare( "DELETE FROM `{$table}` WHERE date = %s", $d ) ); $n = self::compute_daily_from_events( $d ); $out['per_day'][ $d ] = $n; $out['rows'] += $n; $out['days']++; } return $out; } public static function compute_window( $date = null ) { $yesterday = gmdate( 'Y-m-d', time() - DAY_IN_SECONDS ); if ( null === $date || ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', (string) $date ) || $date > $yesterday ) { $date = $yesterday; } $anchor_ts = strtotime( $date . ' 12:00:00 UTC' ); $written = 0; for ( $i = self::WINDOW_DAYS - 1; $i >= 0; $i-- ) { $written = self::compute_daily( gmdate( 'Y-m-d', $anchor_ts - $i * DAY_IN_SECONDS ) ); } return $written; } public static function partners_for( $product_id, $days = 56, $limit = 50 ) { global $wpdb; $table = self::table(); if ( ! self::table_exists( $table ) ) { return array(); } $rows = $wpdb->get_results( $wpdb->prepare( "SELECT partner_id, SUM(co_sessions) AS co_sessions, SUM(downstream_carts) AS downstream_carts FROM ( SELECT b_id AS partner_id, co_sessions, downstream_carts FROM `{$table}` WHERE a_id = %d AND date >= DATE_SUB(UTC_DATE(), INTERVAL %d DAY) UNION ALL SELECT a_id AS partner_id, co_sessions, downstream_carts FROM `{$table}` WHERE b_id = %d AND date >= DATE_SUB(UTC_DATE(), INTERVAL %d DAY) ) u GROUP BY partner_id ORDER BY co_sessions DESC LIMIT %d", $product_id, $days, $product_id, $days, $limit ), ARRAY_A ); $out = array(); foreach ( (array) $rows as $r ) { $out[] = array( 'partner_id' => (int) $r['partner_id'], 'co_sessions' => (int) $r['co_sessions'], 'downstream_carts' => (int) $r['downstream_carts'], ); } return $out; } private static function table_exists( $table ) { global $wpdb; return (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) === $table; } }