626 lines
25 KiB
PHP
Executable File
626 lines
25 KiB
PHP
Executable File
#!/usr/bin/env php
|
|
<?php
|
|
/**
|
|
* Brain Creative Performance Engine
|
|
*
|
|
* 1. A/B Testing: Track & score creatives per offer/country/ISP, auto-promote winners
|
|
* 2. Translation: Auto-translate winning creatives to target languages via WEVAL IA
|
|
* 3. Offer Discovery: Auto-evaluate new Everflow offers & recommend approval
|
|
*
|
|
* Usage:
|
|
* php brain-creative-engine.php calculate_performance
|
|
* php brain-creative-engine.php promote_winners
|
|
* php brain-creative-engine.php translate_winners
|
|
* php brain-creative-engine.php discover_offers
|
|
* php brain-creative-engine.php full_cycle
|
|
* php brain-creative-engine.php status
|
|
*/
|
|
|
|
$DB_HOST = 'localhost';
|
|
$DB_NAME = 'adx_system';
|
|
$DB_USER = 'admin';
|
|
$DB_PASS = 'admin123';
|
|
|
|
$EVERFLOW_API = 'https://api.eflow.team';
|
|
$EVERFLOW_KEY = 'b0PPowhR3CI9EtcqtLHA';
|
|
|
|
// Minimum sends before judging a creative
|
|
$MIN_SENDS_FOR_JUDGMENT = 30;
|
|
// Click rate threshold to be considered a winner
|
|
$WINNER_CLICK_RATE = 2.0; // 2%+
|
|
// Click rate below which creative gets paused
|
|
$PAUSE_CLICK_RATE = 0.3; // <0.3% after enough sends = pause
|
|
// Minimum Brain score for auto-approval recommendation
|
|
$AUTO_APPROVE_SCORE = 7.0;
|
|
|
|
$db = pg_connect("host=$DB_HOST dbname=$DB_NAME user=$DB_USER password=$DB_PASS");
|
|
if (!$db) die("DB connection failed\n");
|
|
|
|
$action = $argv[1] ?? 'status';
|
|
|
|
switch ($action) {
|
|
case 'calculate_performance': calculate_performance($db); break;
|
|
case 'promote_winners': promote_winners($db); break;
|
|
case 'translate_winners': translate_winners($db); break;
|
|
case 'discover_offers': discover_offers($db); break;
|
|
case 'full_cycle': full_cycle($db); break;
|
|
case 'status': show_status($db); break;
|
|
default: echo "Unknown action: $action\n";
|
|
}
|
|
|
|
pg_close($db);
|
|
|
|
// ============================================================
|
|
// 1. A/B TESTING - Calculate creative performance
|
|
// ============================================================
|
|
function calculate_performance($db) {
|
|
echo "=== CALCULATING CREATIVE PERFORMANCE ===\n";
|
|
|
|
// Get sends grouped by offer + creative + country + ISP
|
|
$sql = "
|
|
SELECT
|
|
u.offer_id,
|
|
u.creative_id,
|
|
COALESCE(u.country, 'ALL') as country,
|
|
COALESCE(u.isp_target, 'ALL') as isp_target,
|
|
COUNT(*) as total_sent,
|
|
COUNT(DISTINCT CASE WHEN te.event_type = 'open' THEN te.tracking_id END) as total_opens,
|
|
COUNT(DISTINCT CASE WHEN te.event_type = 'click' THEN te.tracking_id END) as total_clicks
|
|
FROM admin.unified_send_log_new u
|
|
LEFT JOIN admin.tracking_events te ON te.tracking_id = u.tracking_id
|
|
WHERE u.status = 'sent'
|
|
AND u.offer_id IS NOT NULL
|
|
AND u.creative_id IS NOT NULL
|
|
GROUP BY u.offer_id, u.creative_id, u.country, u.isp_target
|
|
";
|
|
|
|
$result = pg_query($db, $sql);
|
|
$updated = 0;
|
|
|
|
while ($row = pg_fetch_assoc($result)) {
|
|
$sent = (int)$row['total_sent'];
|
|
$opens = (int)$row['total_opens'];
|
|
$clicks = (int)$row['total_clicks'];
|
|
|
|
$open_rate = $sent > 0 ? round(($opens / $sent) * 100, 2) : 0;
|
|
$click_rate = $sent > 0 ? round(($clicks / $sent) * 100, 2) : 0;
|
|
|
|
// Composite score: weighted combination
|
|
// Click rate is king (60%), open rate matters (30%), volume bonus (10%)
|
|
$volume_bonus = min($sent / 100, 1) * 10; // Max 10 points at 100 sends
|
|
$score = ($click_rate * 6) + ($open_rate * 3) + $volume_bonus;
|
|
|
|
$upsert = "
|
|
INSERT INTO admin.brain_creative_performance
|
|
(offer_id, creative_id, country, isp_target, total_sent, total_opens, total_clicks,
|
|
open_rate, click_rate, score, last_calculated)
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, NOW())
|
|
ON CONFLICT (offer_id, creative_id, country, isp_target)
|
|
DO UPDATE SET
|
|
total_sent = $5, total_opens = $6, total_clicks = $7,
|
|
open_rate = $8, click_rate = $9, score = $10, last_calculated = NOW()
|
|
";
|
|
|
|
pg_query_params($db, $upsert, [
|
|
$row['offer_id'], $row['creative_id'], $row['country'], $row['isp_target'],
|
|
$sent, $opens, $clicks, $open_rate, $click_rate, $score
|
|
]);
|
|
$updated++;
|
|
}
|
|
|
|
// Also calculate subject line performance
|
|
$sql_subj = "
|
|
SELECT
|
|
u.offer_id,
|
|
s.id as subject_id,
|
|
COALESCE(u.country, 'ALL') as country,
|
|
COUNT(*) as total_sent,
|
|
COUNT(DISTINCT CASE WHEN te.event_type = 'open' THEN te.tracking_id END) as total_opens
|
|
FROM admin.unified_send_log_new u
|
|
JOIN affiliate.subjects s ON s.value = u.subject AND s.offer_id = u.offer_id
|
|
LEFT JOIN admin.tracking_events te ON te.tracking_id = u.tracking_id
|
|
WHERE u.status = 'sent' AND u.offer_id IS NOT NULL
|
|
GROUP BY u.offer_id, s.id, u.country
|
|
";
|
|
|
|
$result2 = pg_query($db, $sql_subj);
|
|
$subj_updated = 0;
|
|
|
|
while ($row = pg_fetch_assoc($result2)) {
|
|
$sent = (int)$row['total_sent'];
|
|
$opens = (int)$row['total_opens'];
|
|
$open_rate = $sent > 0 ? round(($opens / $sent) * 100, 2) : 0;
|
|
|
|
$upsert = "
|
|
INSERT INTO admin.brain_subject_performance (offer_id, subject_id, country, total_sent, total_opens, open_rate, last_calculated)
|
|
VALUES ($1, $2, $3, $4, $5, $6, NOW())
|
|
ON CONFLICT (offer_id, subject_id, country)
|
|
DO UPDATE SET total_sent = $4, total_opens = $5, open_rate = $6, last_calculated = NOW()
|
|
";
|
|
pg_query_params($db, $upsert, [$row['offer_id'], $row['subject_id'], $row['country'], $sent, $opens, $open_rate]);
|
|
$subj_updated++;
|
|
}
|
|
|
|
echo " Updated: $updated creative performances, $subj_updated subject performances\n";
|
|
}
|
|
|
|
// ============================================================
|
|
// 2. PROMOTE WINNERS / PAUSE LOSERS
|
|
// ============================================================
|
|
function promote_winners($db) {
|
|
global $MIN_SENDS_FOR_JUDGMENT, $WINNER_CLICK_RATE, $PAUSE_CLICK_RATE;
|
|
|
|
echo "=== PROMOTING WINNERS / PAUSING LOSERS ===\n";
|
|
|
|
// Get all performances with enough data
|
|
$sql = "
|
|
SELECT offer_id, creative_id, country, isp_target,
|
|
total_sent, click_rate, open_rate, score
|
|
FROM admin.brain_creative_performance
|
|
WHERE total_sent >= $MIN_SENDS_FOR_JUDGMENT
|
|
ORDER BY offer_id, score DESC
|
|
";
|
|
|
|
$result = pg_query($db, $sql);
|
|
$winners = 0;
|
|
$paused = 0;
|
|
$by_offer = [];
|
|
|
|
while ($row = pg_fetch_assoc($result)) {
|
|
$key = $row['offer_id'] . '_' . $row['country'];
|
|
if (!isset($by_offer[$key])) $by_offer[$key] = [];
|
|
$by_offer[$key][] = $row;
|
|
}
|
|
|
|
foreach ($by_offer as $key => $creatives) {
|
|
// Sort by score descending
|
|
usort($creatives, fn($a, $b) => $b['score'] <=> $a['score']);
|
|
|
|
$best = $creatives[0];
|
|
|
|
// Mark winner
|
|
if ((float)$best['click_rate'] >= $WINNER_CLICK_RATE) {
|
|
pg_query_params($db,
|
|
"UPDATE admin.brain_creative_performance SET is_winner = true WHERE offer_id = $1 AND creative_id = $2 AND country = $3",
|
|
[$best['offer_id'], $best['creative_id'], $best['country']]
|
|
);
|
|
$winners++;
|
|
echo " WINNER: Offer #{$best['offer_id']} Creative #{$best['creative_id']} [{$best['country']}] - {$best['click_rate']}% CTR ({$best['total_sent']} sends)\n";
|
|
}
|
|
|
|
// Pause losers
|
|
foreach (array_slice($creatives, 1) as $loser) {
|
|
if ((float)$loser['click_rate'] < $PAUSE_CLICK_RATE) {
|
|
pg_query_params($db,
|
|
"UPDATE admin.brain_creative_performance SET is_paused = true WHERE offer_id = $1 AND creative_id = $2 AND country = $3",
|
|
[$loser['offer_id'], $loser['creative_id'], $loser['country']]
|
|
);
|
|
// Also deactivate in creatives table
|
|
pg_query_params($db,
|
|
"UPDATE affiliate.creatives SET status = 'Paused' WHERE id = $1",
|
|
[$loser['creative_id']]
|
|
);
|
|
$paused++;
|
|
echo " PAUSED: Offer #{$loser['offer_id']} Creative #{$loser['creative_id']} [{$loser['country']}] - {$loser['click_rate']}% CTR\n";
|
|
}
|
|
}
|
|
}
|
|
|
|
if ($winners == 0 && $paused == 0) {
|
|
echo " Not enough data yet (need >$MIN_SENDS_FOR_JUDGMENT sends per creative to judge)\n";
|
|
} else {
|
|
echo " Result: $winners winners promoted, $paused losers paused\n";
|
|
}
|
|
}
|
|
|
|
// ============================================================
|
|
// 3. TRANSLATION ENGINE
|
|
// ============================================================
|
|
function translate_winners($db) {
|
|
echo "=== TRANSLATING WINNING CREATIVES ===\n";
|
|
|
|
// Language map by country
|
|
$lang_map = [
|
|
'DE' => 'de', 'AT' => 'de', 'FR' => 'fr', 'IT' => 'it', 'ES' => 'es',
|
|
'UK' => 'en', 'US' => 'en', 'CA' => 'en', 'AU' => 'en',
|
|
'NZ' => 'en', 'SE' => 'sv', 'FI' => 'fi', 'NL' => 'nl'
|
|
];
|
|
|
|
// Find winning creatives that could be translated to new markets
|
|
$sql = "
|
|
SELECT DISTINCT cp.offer_id, cp.creative_id, cp.country, cp.click_rate, cp.score,
|
|
o.countries as offer_countries
|
|
FROM admin.brain_creative_performance cp
|
|
JOIN affiliate.offers o ON o.id = cp.offer_id
|
|
WHERE cp.is_winner = true AND cp.score > 5
|
|
ORDER BY cp.score DESC
|
|
";
|
|
|
|
$result = pg_query($db, $sql);
|
|
$translations = 0;
|
|
|
|
while ($row = pg_fetch_assoc($result)) {
|
|
$source_country = $row['country'];
|
|
$source_lang = $lang_map[$source_country] ?? 'en';
|
|
|
|
// Check which target languages we could translate to
|
|
// For now: translate DE winners to AT, FR winners to CA-FR, etc.
|
|
$translation_targets = get_translation_targets($source_lang, $lang_map);
|
|
|
|
foreach ($translation_targets as $target_lang) {
|
|
if ($target_lang === $source_lang) continue;
|
|
|
|
// Check if translation already exists
|
|
$check = pg_query_params($db,
|
|
"SELECT id FROM admin.brain_creative_translations WHERE source_creative_id = $1 AND target_lang = $2",
|
|
[$row['creative_id'], $target_lang]
|
|
);
|
|
|
|
if (pg_num_rows($check) > 0) continue;
|
|
|
|
// Get source creative HTML
|
|
$cr = pg_fetch_assoc(pg_query_params($db,
|
|
"SELECT value, name FROM affiliate.creatives WHERE id = $1",
|
|
[$row['creative_id']]
|
|
));
|
|
|
|
if (!$cr) continue;
|
|
|
|
$source_html = base64_decode($cr['value']);
|
|
|
|
// Call WEVAL IA for translation
|
|
$translated_html = translate_via_hamid($source_html, $source_lang, $target_lang);
|
|
|
|
if ($translated_html) {
|
|
// Insert translated creative
|
|
$max_id = pg_fetch_result(pg_query($db, "SELECT COALESCE(MAX(id),0)+1 FROM affiliate.creatives"), 0, 0);
|
|
$b64 = base64_encode($translated_html);
|
|
$name = $cr['name'] . "_translated_{$target_lang}";
|
|
|
|
pg_query_params($db,
|
|
"INSERT INTO affiliate.creatives (id, status, affiliate_network_id, offer_id, name, value, created_by, created_date, quality_score, has_tracking_placeholders, last_validated)
|
|
VALUES ($1, 'Activated', 6, $2, $3, $4, 'brain_translator', CURRENT_DATE, 10, true, NOW())",
|
|
[$max_id, $row['offer_id'], $name, $b64]
|
|
);
|
|
|
|
// Log translation
|
|
pg_query_params($db,
|
|
"INSERT INTO admin.brain_creative_translations (source_creative_id, source_lang, target_lang, target_creative_id, translated_by)
|
|
VALUES ($1, $2, $3, $4, 'hamid_ia')",
|
|
[$row['creative_id'], $source_lang, $target_lang, $max_id]
|
|
);
|
|
|
|
$translations++;
|
|
echo " TRANSLATED: Creative #{$row['creative_id']} ({$source_lang} → {$target_lang}) → New creative #{$max_id}\n";
|
|
}
|
|
}
|
|
}
|
|
|
|
echo " Total translations: $translations\n";
|
|
if ($translations == 0) echo " No winners to translate yet (need winning creatives first)\n";
|
|
}
|
|
|
|
function get_translation_targets($source_lang, $lang_map) {
|
|
$all_langs = array_unique(array_values($lang_map));
|
|
return array_filter($all_langs, fn($l) => $l !== $source_lang);
|
|
}
|
|
|
|
function translate_via_hamid($html, $from_lang, $to_lang) {
|
|
// Use WEVAL IA API for translation
|
|
$lang_names = ['de'=>'German','fr'=>'French','en'=>'English','it'=>'Italian','es'=>'Spanish','sv'=>'Swedish','fi'=>'Finnish','nl'=>'Dutch'];
|
|
$from_name = $lang_names[$from_lang] ?? $from_lang;
|
|
$to_name = $lang_names[$to_lang] ?? $to_lang;
|
|
|
|
// Try local HAMID API first
|
|
$prompt = "Translate this email HTML from {$from_name} to {$to_name}. Keep ALL HTML structure, CSS, and placeholders [url], [open], [unsub] EXACTLY as they are. Only translate the visible text content. Return ONLY the translated HTML, no explanation:\n\n" . $html;
|
|
|
|
// Use Cerebras (fastest) via HAMID config
|
|
$providers = [
|
|
['url' => 'https://api.cerebras.ai/v1/chat/completions', 'key' => '', 'model' => 'llama-4-scout-17b-16e-instruct'],
|
|
['url' => 'https://api.groq.com/openai/v1/chat/completions', 'key' => '', 'model' => 'llama-3.3-70b-versatile'],
|
|
];
|
|
|
|
// Load HAMID keys from DB
|
|
global $db;
|
|
foreach (['cerebras', 'groq'] as $i => $provider) {
|
|
$r = pg_query_params($db, "SELECT api_key FROM admin.hamid_providers WHERE name = $1 AND is_active = true", [$provider]);
|
|
if ($r && $row = pg_fetch_assoc($r)) {
|
|
$providers[$i]['key'] = $row['api_key'];
|
|
}
|
|
}
|
|
|
|
foreach ($providers as $p) {
|
|
if (empty($p['key'])) continue;
|
|
|
|
$ch = curl_init($p['url']);
|
|
curl_setopt_array($ch, [
|
|
CURLOPT_POST => true,
|
|
CURLOPT_RETURNTRANSFER => true,
|
|
CURLOPT_TIMEOUT => 30,
|
|
CURLOPT_HTTPHEADER => [
|
|
'Content-Type: application/json',
|
|
'Authorization: Bearer ' . $p['key']
|
|
],
|
|
CURLOPT_POSTFIELDS => json_encode([
|
|
'model' => $p['model'],
|
|
'messages' => [
|
|
['role' => 'system', 'content' => 'You are a professional translator for marketing emails. Preserve ALL HTML and placeholders exactly.'],
|
|
['role' => 'user', 'content' => $prompt]
|
|
],
|
|
'max_tokens' => 4000,
|
|
'temperature' => 0.3
|
|
])
|
|
]);
|
|
|
|
$response = curl_exec($ch);
|
|
$code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
|
|
curl_close($ch);
|
|
|
|
if ($code === 200) {
|
|
$data = json_decode($response, true);
|
|
$translated = $data['choices'][0]['message']['content'] ?? '';
|
|
|
|
// Validate: must contain [url], [open], [unsub]
|
|
if (strpos($translated, '[url]') !== false &&
|
|
strpos($translated, '[open]') !== false &&
|
|
strpos($translated, '<') !== false) {
|
|
return $translated;
|
|
}
|
|
}
|
|
}
|
|
|
|
return null; // Translation failed
|
|
}
|
|
|
|
// ============================================================
|
|
// 4. OFFER AUTO-DISCOVERY
|
|
// ============================================================
|
|
function discover_offers($db) {
|
|
global $EVERFLOW_API, $EVERFLOW_KEY, $AUTO_APPROVE_SCORE;
|
|
|
|
echo "=== OFFER AUTO-DISCOVERY ===\n";
|
|
|
|
// Fetch all Everflow offers
|
|
$ch = curl_init("$EVERFLOW_API/v1/affiliates/alloffers");
|
|
curl_setopt_array($ch, [
|
|
CURLOPT_RETURNTRANSFER => true,
|
|
CURLOPT_TIMEOUT => 30,
|
|
CURLOPT_HTTPHEADER => ['X-Eflow-API-Key: ' . $EVERFLOW_KEY]
|
|
]);
|
|
$response = curl_exec($ch);
|
|
curl_close($ch);
|
|
|
|
$data = json_decode($response, true);
|
|
$offers = $data['offers'] ?? [];
|
|
|
|
// Get existing offers in our DB
|
|
$existing = [];
|
|
$r = pg_query($db, "SELECT production_id FROM affiliate.offers WHERE production_id IS NOT NULL");
|
|
while ($row = pg_fetch_assoc($r)) {
|
|
$existing[] = $row['production_id'];
|
|
}
|
|
|
|
// Get our performance data to understand what works
|
|
$perf = pg_query($db, "
|
|
SELECT country, AVG(click_rate) as avg_ctr, SUM(total_sent) as total_volume
|
|
FROM admin.brain_creative_performance
|
|
WHERE total_sent > 10
|
|
GROUP BY country
|
|
");
|
|
$country_performance = [];
|
|
while ($row = pg_fetch_assoc($perf)) {
|
|
$country_performance[$row['country']] = $row;
|
|
}
|
|
|
|
$new_discovered = 0;
|
|
$auto_approved = 0;
|
|
|
|
foreach ($offers as $offer) {
|
|
$noid = (string)$offer['network_offer_id'];
|
|
|
|
// Skip if already in DB
|
|
if (in_array($noid, $existing)) continue;
|
|
|
|
// Skip if already discovered
|
|
$check = pg_query_params($db,
|
|
"SELECT id FROM admin.brain_offer_discovery WHERE external_offer_id = $1 AND source = 'everflow'",
|
|
[$noid]
|
|
);
|
|
if (pg_num_rows($check) > 0) continue;
|
|
|
|
// Extract country from name
|
|
$name = $offer['name'] ?? '';
|
|
$country = substr($name, 0, 2);
|
|
if (!ctype_alpha($country)) $country = 'WW';
|
|
|
|
// Detect vertical from name
|
|
$vertical = detect_vertical($name);
|
|
|
|
// Brain scoring algorithm
|
|
$brain_score = calculate_brain_score($offer, $country, $vertical, $country_performance);
|
|
|
|
// Build recommendation
|
|
$recommendation = build_recommendation($offer, $country, $vertical, $brain_score, $country_performance);
|
|
|
|
// Auto-approve if score is high enough
|
|
$status = 'pending';
|
|
$reviewed_by = null;
|
|
$reviewed_at = null;
|
|
|
|
if ($brain_score >= $AUTO_APPROVE_SCORE) {
|
|
$status = 'approved';
|
|
$reviewed_by = 'brain_auto';
|
|
$reviewed_at = date('Y-m-d H:i:s');
|
|
$auto_approved++;
|
|
}
|
|
|
|
// Insert discovery
|
|
pg_query_params($db,
|
|
"INSERT INTO admin.brain_offer_discovery
|
|
(source, external_offer_id, offer_name, country, vertical, tracking_url, brain_score, brain_recommendation, status, reviewed_by, reviewed_at)
|
|
VALUES ('everflow', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10)",
|
|
[$noid, $name, $country, $vertical, $offer['tracking_url'] ?? '',
|
|
$brain_score, $recommendation, $status, $reviewed_by, $reviewed_at]
|
|
);
|
|
|
|
$new_discovered++;
|
|
$status_icon = $status === 'approved' ? '✅' : '⏳';
|
|
echo " $status_icon [{$country}] #{$noid}: " . substr($name, 0, 55) . " (score: {$brain_score})\n";
|
|
}
|
|
|
|
echo "\n New discovered: $new_discovered, Auto-approved: $auto_approved\n";
|
|
|
|
// Show pending offers that need manual review
|
|
$pending = pg_query($db, "SELECT external_offer_id, offer_name, country, brain_score, brain_recommendation
|
|
FROM admin.brain_offer_discovery WHERE status = 'pending' ORDER BY brain_score DESC LIMIT 10");
|
|
$pending_count = pg_num_rows($pending);
|
|
if ($pending_count > 0) {
|
|
echo "\n PENDING MANUAL REVIEW ($pending_count offers):\n";
|
|
while ($row = pg_fetch_assoc($pending)) {
|
|
echo " [{$row['country']}] #{$row['external_offer_id']}: " . substr($row['offer_name'], 0, 45) . " (score: {$row['brain_score']})\n";
|
|
echo " → {$row['brain_recommendation']}\n";
|
|
}
|
|
}
|
|
}
|
|
|
|
function detect_vertical($name) {
|
|
$name_lower = strtolower($name);
|
|
if (preg_match('/shein|fashion|mode|moda/', $name_lower)) return 'fashion';
|
|
if (preg_match('/lidl|aldi|walmart|grocery/', $name_lower)) return 'retail';
|
|
if (preg_match('/car|auto|adac|aaa|telepass|vinci/', $name_lower)) return 'automotive';
|
|
if (preg_match('/beauty|lancôme|nocibe|sephora/', $name_lower)) return 'beauty';
|
|
if (preg_match('/health|krankenkasse|medical/', $name_lower)) return 'health';
|
|
if (preg_match('/dating|girls|women|single/', $name_lower)) return 'dating';
|
|
if (preg_match('/loan|credit|finance|insurance/', $name_lower)) return 'finance';
|
|
if (preg_match('/gift.*card|giftcard|voucher/', $name_lower)) return 'giftcard';
|
|
if (preg_match('/tool|diy|bricolage|dexter|parkside/', $name_lower)) return 'diy';
|
|
if (preg_match('/food|coffee|tim horton|restaurant/', $name_lower)) return 'food';
|
|
return 'other';
|
|
}
|
|
|
|
function calculate_brain_score($offer, $country, $vertical, $country_performance) {
|
|
$score = 5.0; // Base score
|
|
|
|
// +2 if we already have good performance in this country
|
|
if (isset($country_performance[$country]) && (float)$country_performance[$country]['avg_ctr'] > 1.0) {
|
|
$score += 2.0;
|
|
}
|
|
|
|
// +1 for EU countries (our primary markets)
|
|
if (in_array($country, ['DE', 'FR', 'UK', 'IT', 'ES', 'AT', 'NL'])) {
|
|
$score += 1.0;
|
|
}
|
|
|
|
// +1 for proven verticals
|
|
if (in_array($vertical, ['retail', 'automotive', 'giftcard', 'diy'])) {
|
|
$score += 1.0;
|
|
}
|
|
|
|
// -2 for dating (compliance risk)
|
|
if ($vertical === 'dating') {
|
|
$score -= 2.0;
|
|
}
|
|
|
|
// +0.5 if tracking URL is rivoweb (known good)
|
|
$track = $offer['tracking_url'] ?? '';
|
|
if (strpos($track, 'rivoweb.com') !== false) {
|
|
$score += 0.5;
|
|
}
|
|
|
|
return min(round($score, 1), 10.0);
|
|
}
|
|
|
|
function build_recommendation($offer, $country, $vertical, $score, $country_performance) {
|
|
$reasons = [];
|
|
|
|
if ($score >= 7) {
|
|
$reasons[] = "HIGH PRIORITY";
|
|
} elseif ($score >= 5) {
|
|
$reasons[] = "MODERATE INTEREST";
|
|
} else {
|
|
$reasons[] = "LOW PRIORITY";
|
|
}
|
|
|
|
if (isset($country_performance[$country])) {
|
|
$ctr = $country_performance[$country]['avg_ctr'];
|
|
$reasons[] = "{$country} avg CTR: {$ctr}%";
|
|
} else {
|
|
$reasons[] = "No performance data for {$country} yet";
|
|
}
|
|
|
|
$reasons[] = "Vertical: {$vertical}";
|
|
|
|
if ($vertical === 'dating') {
|
|
$reasons[] = "⚠️ Dating vertical - compliance review needed";
|
|
}
|
|
|
|
return implode(' | ', $reasons);
|
|
}
|
|
|
|
// ============================================================
|
|
// 5. FULL CYCLE
|
|
// ============================================================
|
|
function full_cycle($db) {
|
|
echo "========================================\n";
|
|
echo " BRAIN CREATIVE ENGINE - FULL CYCLE\n";
|
|
echo " " . date('Y-m-d H:i:s') . "\n";
|
|
echo "========================================\n\n";
|
|
|
|
calculate_performance($db);
|
|
echo "\n";
|
|
promote_winners($db);
|
|
echo "\n";
|
|
translate_winners($db);
|
|
echo "\n";
|
|
discover_offers($db);
|
|
echo "\n";
|
|
|
|
show_status($db);
|
|
}
|
|
|
|
// ============================================================
|
|
// STATUS
|
|
// ============================================================
|
|
function show_status($db) {
|
|
echo "=== BRAIN CREATIVE ENGINE STATUS ===\n";
|
|
|
|
// Creative performance stats
|
|
$r = pg_fetch_assoc(pg_query($db, "SELECT COUNT(*) as total, COUNT(*) FILTER (WHERE is_winner) as winners, COUNT(*) FILTER (WHERE is_paused) as paused FROM admin.brain_creative_performance"));
|
|
echo " Creative performances tracked: {$r['total']} (Winners: {$r['winners']}, Paused: {$r['paused']})\n";
|
|
|
|
// Top performers
|
|
$top = pg_query($db, "
|
|
SELECT cp.offer_id, LEFT(o.name,35) as offer, cp.country, cp.click_rate, cp.open_rate, cp.total_sent, cp.score
|
|
FROM admin.brain_creative_performance cp
|
|
JOIN affiliate.offers o ON o.id = cp.offer_id
|
|
WHERE cp.total_sent >= 10
|
|
ORDER BY cp.score DESC LIMIT 5
|
|
");
|
|
echo "\n TOP 5 CREATIVE PERFORMERS:\n";
|
|
while ($row = pg_fetch_assoc($top)) {
|
|
echo " [{$row['country']}] Offer #{$row['offer_id']} - CTR: {$row['click_rate']}% | Open: {$row['open_rate']}% | Sends: {$row['total_sent']} | Score: {$row['score']}\n";
|
|
}
|
|
|
|
// Subject performance
|
|
$subj = pg_fetch_assoc(pg_query($db, "SELECT COUNT(*) as total FROM admin.brain_subject_performance"));
|
|
echo "\n Subject performances tracked: {$subj['total']}\n";
|
|
|
|
// Translations
|
|
$trans = pg_fetch_assoc(pg_query($db, "SELECT COUNT(*) as total FROM admin.brain_creative_translations"));
|
|
echo " Translations done: {$trans['total']}\n";
|
|
|
|
// Discovery
|
|
$disc = pg_query($db, "SELECT status, COUNT(*) as c FROM admin.brain_offer_discovery GROUP BY status ORDER BY status");
|
|
echo "\n OFFER DISCOVERY:\n";
|
|
while ($row = pg_fetch_assoc($disc)) {
|
|
echo " {$row['status']}: {$row['c']}\n";
|
|
}
|
|
|
|
// Approved offers summary
|
|
$approved = pg_fetch_assoc(pg_query($db, "SELECT COUNT(*) as c FROM admin.brain_offer_config WHERE is_approved = true AND is_active = true AND good_creatives > 0"));
|
|
echo "\n PRODUCTION READY OFFERS: {$approved['c']}\n";
|
|
}
|
|
|
|
?>
|
|
|