'product_upsells', 'aiocartupsell' => 'product_upsells', 'aiocrosssell' => 'product_crosssells', 'aiocartcrosssell' => 'product_crosssells', 'aiowoorandrelated' => 'product_related', 'aiocartwoorandrelated' => 'product_related', 'aiorecentlyviewed' => 'product_recently_viewed', 'aiocartrecentlyviewed' => 'product_recently_viewed', ); } public static function table() { global $wpdb; return $wpdb->prefix . 'aieo_conversions'; } public static function ensure_schema() { global $wpdb; if ( get_option( self::SCHEMA_OPT ) === self::SCHEMA_VER ) { return true; } if ( get_transient( self::SCHEMA_OPT . '_retry' ) ) { return false; } set_transient( self::SCHEMA_OPT . '_retry', 1, HOUR_IN_SECONDS ); if ( ! AIEO_Schema_Lock::acquire( self::SCHEMA_OPT ) ) { return get_option( self::SCHEMA_OPT ) === self::SCHEMA_VER; } $t = self::table(); $ok = true; if ( ! self::table_exists( $t ) ) { $ok = ( false !== $wpdb->query( "CREATE TABLE IF NOT EXISTS `{$t}` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `conversion_type` VARCHAR(10) NOT NULL, `source_key` VARCHAR(64) NOT NULL, `occurred_at` DATETIME NOT NULL, `goal_key` VARCHAR(64) NOT NULL DEFAULT '', `provider` VARCHAR(20) NOT NULL DEFAULT '', `form_id` BIGINT UNSIGNED NULL, `entry_id` BIGINT UNSIGNED NULL, `order_id` BIGINT UNSIGNED NULL, `order_status` VARCHAR(20) NOT NULL DEFAULT '', `session_id` VARCHAR(64) NOT NULL DEFAULT '', `customer_id` BIGINT UNSIGNED NOT NULL DEFAULT 0, -- ===== EXTERNAL PROMOTION PLANE ===== `utm_source` VARCHAR(191) NOT NULL DEFAULT '', `utm_medium` VARCHAR(191) NOT NULL DEFAULT '', `utm_campaign` VARCHAR(191) NOT NULL DEFAULT '', `utm_content` VARCHAR(191) NOT NULL DEFAULT '', `utm_term` VARCHAR(191) NOT NULL DEFAULT '', `canon_source` VARCHAR(191) NOT NULL DEFAULT '', `canon_medium` VARCHAR(191) NOT NULL DEFAULT '', `canon_campaign` VARCHAR(191) NOT NULL DEFAULT '', `canon_content` VARCHAR(191) NOT NULL DEFAULT '', `canon_term` VARCHAR(191) NOT NULL DEFAULT '', `channel` VARCHAR(40) NOT NULL DEFAULT '', `gclid` VARCHAR(191) NOT NULL DEFAULT '', `fbclid` VARCHAR(191) NOT NULL DEFAULT '', `ref_key` VARCHAR(64) NOT NULL DEFAULT '', `attr_origin` VARCHAR(16) NOT NULL DEFAULT '', `landing_path` VARCHAR(500) NOT NULL DEFAULT '', `landing_post_id` BIGINT UNSIGNED NOT NULL DEFAULT 0, `referrer_host` VARCHAR(190) NOT NULL DEFAULT '', -- ===== INTERNAL PROMOTION PLANE ===== -- Orthogonal to the block above. '' = no internal promotion -- was involved, which is a real and common answer, NOT a gap. `int_surface` VARCHAR(40) NOT NULL DEFAULT '', `int_anchor_type` VARCHAR(20) NOT NULL DEFAULT '', `int_anchor_id` BIGINT UNSIGNED NOT NULL DEFAULT 0, `int_target_id` BIGINT UNSIGNED NOT NULL DEFAULT 0, `int_position` SMALLINT UNSIGNED NOT NULL DEFAULT 0, `int_click_at` DATETIME NULL, `int_origin` VARCHAR(16) NOT NULL DEFAULT '', -- ===== VALUE ===== `conversion_value` DECIMAL(12,2) NULL, `value_basis` VARCHAR(20) NOT NULL DEFAULT 'unknown', `value_refunded` DECIMAL(12,2) NOT NULL DEFAULT 0.00, `currency` VARCHAR(3) NOT NULL DEFAULT '', `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, -- The branch the order came in through (offers, Phase 2). Last, as schema step 8 -- (aieo_run_db_migrations()) adds it to a table made before this line: both tables are alike. `entry_branch_id` BIGINT UNSIGNED DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uq_source` (`source_key`), KEY `idx_type_time` (`conversion_type`, `occurred_at`), KEY `idx_camp` (`canon_campaign`(100), `occurred_at`), KEY `idx_channel` (`channel`, `occurred_at`), KEY `idx_int` (`int_surface`, `occurred_at`), KEY `idx_int_anchor` (`int_surface`, `int_anchor_id`), KEY `idx_goal` (`goal_key`, `occurred_at`), KEY `idx_order` (`order_id`), KEY `idx_landing` (`landing_post_id`, `occurred_at`) ) ENGINE=InnoDB " . self::charset() ) ) && $ok; } else { if ( ! function_exists( 'aieo_dmm_ensure_column' ) ) { require_once __DIR__ . '/sql/dmm-schema.php'; } aieo_dmm_ensure_column( $t, 'entry_branch_id', 'bigint unsigned DEFAULT NULL' ); $ok = self::column_exists( $t, 'entry_branch_id' ) && $ok; } if ( $ok ) { update_option( self::SCHEMA_OPT, self::SCHEMA_VER, false ); delete_transient( self::SCHEMA_OPT . '_retry' ); } return $ok; } public static function fold( $v ) { $v = urldecode( (string) $v ); $v = preg_replace( '/\s+/u', ' ', $v ); return trim( (string) $v ); } public static function rejection_reason( $v ) { $v = self::fold( $v ); if ( '' === $v ) { return 'empty'; } if ( preg_match( '/^\d+$/', $v ) ) { return 'bare_numeric_adset_id'; } if ( preg_match( '/^AIO/i', $v ) ) { return 'internal_upsell_tagging'; } if ( 'aieo_marketplace' === strtolower( $v ) ) { return 'marketplace'; } if ( 'undefined' === strtolower( $v ) || 'null' === strtolower( $v ) ) { return 'literal_undefined'; } if ( preg_match( '/\{\{?[a-z_]+\}?\}/i', $v ) ) { return 'unexpanded_macro'; } return ''; } public static function source_key_for_order( $order_id ) { return 'order:' . (int) $order_id; } public static function source_key_for_entry( $form_id, $entry_id ) { return 'gf:' . (int) $form_id . ':' . (int) $entry_id; } public static function source_key_for_goal( $goal_key, $session_id, $ts_ms ) { return 'goal:' . substr( (string) $goal_key, 0, 24 ) . ':' . substr( (string) $session_id, 0, 20 ) . ':' . (int) floor( (int) $ts_ms / 1000 ); } public static function record( array $row ) { global $wpdb; if ( ! self::ensure_schema() ) { return false; } $type = isset( $row['conversion_type'] ) ? (string) $row['conversion_type'] : ''; if ( ! in_array( $type, array( self::TYPE_GOAL, self::TYPE_ORDER ), true ) ) { return false; } $key = isset( $row['source_key'] ) ? substr( (string) $row['source_key'], 0, 64 ) : ''; if ( '' === $key ) { return false; } $occurred = isset( $row['occurred_at'] ) ? (string) $row['occurred_at'] : ''; if ( '' === $occurred ) { return false; } $s = static function ( $v, $len = 191 ) { return substr( (string) $v, 0, $len ); }; $g = static function ( $a, $k, $d = '' ) { return isset( $a[ $k ] ) ? $a[ $k ] : $d; }; $utm = array(); foreach ( array( 'source', 'medium', 'campaign', 'content', 'term' ) as $f ) { $utm[ $f ] = $s( $g( $row, 'utm_' . $f ) ); } $data = array( 'conversion_type' => $s( $type, 10 ), 'source_key' => $key, 'occurred_at' => $occurred, 'goal_key' => $s( $g( $row, 'goal_key' ), 64 ), 'provider' => $s( $g( $row, 'provider' ), 20 ), 'form_id' => isset( $row['form_id'] ) ? (int) $row['form_id'] : null, 'entry_id' => isset( $row['entry_id'] ) ? (int) $row['entry_id'] : null, 'order_id' => isset( $row['order_id'] ) ? (int) $row['order_id'] : null, 'order_status' => $s( $g( $row, 'order_status' ), 20 ), 'session_id' => $s( $g( $row, 'session_id' ), 64 ), 'customer_id' => (int) $g( $row, 'customer_id', 0 ), 'utm_source' => $utm['source'], 'utm_medium' => $utm['medium'], 'utm_campaign' => $utm['campaign'], 'utm_content' => $utm['content'], 'utm_term' => $utm['term'], 'canon_source' => $s( self::fold( $utm['source'] ) ), 'canon_medium' => $s( self::fold( $utm['medium'] ) ), 'canon_campaign' => $s( self::fold( $utm['campaign'] ) ), 'canon_content' => $s( self::fold( $utm['content'] ) ), 'canon_term' => $s( self::fold( $utm['term'] ) ), 'channel' => $s( $g( $row, 'channel' ), 40 ), 'gclid' => $s( $g( $row, 'gclid' ) ), 'fbclid' => $s( $g( $row, 'fbclid' ) ), 'ref_key' => $s( $g( $row, 'ref_key' ), 64 ), 'attr_origin' => $s( $g( $row, 'attr_origin' ), 16 ), 'landing_path' => $s( $g( $row, 'landing_path' ), 500 ), 'landing_post_id' => (int) $g( $row, 'landing_post_id', 0 ), 'referrer_host' => $s( $g( $row, 'referrer_host' ), 190 ), 'int_surface' => $s( $g( $row, 'int_surface' ), 40 ), 'int_anchor_type' => $s( $g( $row, 'int_anchor_type' ), 20 ), 'int_anchor_id' => (int) $g( $row, 'int_anchor_id', 0 ), 'int_target_id' => (int) $g( $row, 'int_target_id', 0 ), 'int_position' => (int) $g( $row, 'int_position', 0 ), 'int_click_at' => isset( $row['int_click_at'] ) && $row['int_click_at'] ? (string) $row['int_click_at'] : null, 'int_origin' => $s( $g( $row, 'int_origin' ), 16 ), 'conversion_value' => isset( $row['conversion_value'] ) && null !== $row['conversion_value'] ? round( (float) $row['conversion_value'], 2 ) : null, 'value_basis' => $s( $g( $row, 'value_basis', 'unknown' ), 20 ), 'value_refunded' => round( (float) $g( $row, 'value_refunded', 0 ), 2 ), 'currency' => $s( $g( $row, 'currency' ), 3 ), 'created_at' => gmdate( 'Y-m-d H:i:s' ), 'updated_at' => gmdate( 'Y-m-d H:i:s' ), ); if ( array_key_exists( 'entry_branch_id', $row ) && self::has_entry_branch() ) { $data['entry_branch_id'] = null !== $row['entry_branch_id'] && (int) $row['entry_branch_id'] > 0 ? (int) $row['entry_branch_id'] : null; } $cols = array_keys( $data ); $ph = array(); $vals = array(); foreach ( $cols as $c ) { if ( null === $data[ $c ] ) { $ph[] = 'NULL'; continue; } $ph[] = is_int( $data[ $c ] ) ? '%d' : ( is_float( $data[ $c ] ) ? '%f' : '%s' ); $vals[] = $data[ $c ]; } $upd = array(); foreach ( $cols as $c ) { if ( in_array( $c, array( 'source_key', 'created_at' ), true ) ) { continue; } $upd[] = "`{$c}` = VALUES(`{$c}`)"; } $t = self::table(); $sql = "INSERT INTO `{$t}` (`" . implode( '`,`', $cols ) . "`) VALUES (" . implode( ',', $ph ) . ") ON DUPLICATE KEY UPDATE " . implode( ', ', $upd ); return $wpdb->query( $vals ? $wpdb->prepare( $sql, $vals ) : $sql ); } public static function money( $v, $currency = '' ) { $v = (float) $v; if ( function_exists( 'wc_price' ) ) { return html_entity_decode( wp_strip_all_tags( wc_price( $v ) ), ENT_QUOTES, 'UTF-8' ); } $dec = '.'; $thou = ','; if ( isset( $GLOBALS['wp_locale'] ) && is_object( $GLOBALS['wp_locale'] ) ) { $d = isset( $GLOBALS['wp_locale']->number_format['decimal_point'] ) ? (string) $GLOBALS['wp_locale']->number_format['decimal_point'] : '.'; $t = isset( $GLOBALS['wp_locale']->number_format['thousands_sep'] ) ? (string) $GLOBALS['wp_locale']->number_format['thousands_sep'] : ','; if ( $d !== $t && '' !== $d ) { $dec = $d; $thou = $t; } else { $dec = ','; $thou = '.'; } } $code = strtoupper( '' !== $currency ? $currency : (string) get_option( 'woocommerce_currency', 'EUR' ) ); $sym = ( 'EUR' === $code ) ? '€' : $code; return number_format( $v, 2, $dec, $thou ) . ' ' . $sym; } private static function table_exists( $table ) { global $wpdb; return (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) === $table; } private static function column_exists( $table, $column ) { global $wpdb; return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s', $table, $column ) ) > 0; } public static function has_entry_branch() { if ( get_option( self::SCHEMA_OPT ) === self::SCHEMA_VER ) { return true; } return self::table_exists( self::table() ) && self::column_exists( self::table(), 'entry_branch_id' ); } public static function facts_source() { $t = self::table(); if ( self::ensure_schema() || self::table_exists( $t ) ) { return array( "`{$t}`", self::has_entry_branch() ); } return array( '(SELECT NULL AS id, NULL AS source_key, NULL AS conversion_value, NULL AS value_refunded, NULL AS entry_branch_id FROM DUAL WHERE 1 = 0)', false ); } public static function ledger_notes( $carried, $no_facts ) { $notes = array(); if ( (int) $carried > 0 ) { $notes[] = __( 'Some rows were carried over from the log kept before the offers\' ledger: a carried free-shipping row\'s reduction stays 0, a carried row\'s cost of goods is today\'s, and a row whose offer no longer exists counts in these totals only, never in an offer\'s own figures.', 'ai-eshop-optimizer' ); } if ( (int) $no_facts > 0 ) { $notes[] = sprintf( _n( '%s order without order facts: its total is not counted.', '%s orders without order facts: their totals are not counted.', (int) $no_facts, 'ai-eshop-optimizer' ), number_format_i18n( (int) $no_facts ) ); } return $notes; } 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 ( ! is_string( $collation ) || '' === $collation ) { return 'DEFAULT CHARACTER SET utf8mb4'; } return 'DEFAULT CHARACTER SET ' . strtok( $collation, '_' ) . ' COLLATE ' . $collation; } }