292 lines
12 KiB
PHP
Executable File
292 lines
12 KiB
PHP
Executable File
|
|
<?php
|
|
/**
|
|
* DATA INTELLIGENCE ENGINE
|
|
* Autonomous Data Collection, Segmentation & Matching
|
|
*/
|
|
header('Content-Type: application/json');
|
|
$pdo = new PDO("pgsql:host=localhost;dbname=adx_system", "admin", "admin123", [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
|
|
|
|
$pdo->exec("
|
|
CREATE TABLE IF NOT EXISTS admin.data_sources (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255),
|
|
type VARCHAR(100),
|
|
connection_config TEXT,
|
|
last_sync TIMESTAMP,
|
|
records_count INTEGER DEFAULT 0,
|
|
is_active BOOLEAN DEFAULT true
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.data_profiles (
|
|
id SERIAL PRIMARY KEY,
|
|
email VARCHAR(255) UNIQUE,
|
|
first_name VARCHAR(100),
|
|
last_name VARCHAR(100),
|
|
country VARCHAR(50),
|
|
language VARCHAR(10),
|
|
interests TEXT,
|
|
engagement_score FLOAT DEFAULT 0,
|
|
last_open TIMESTAMP,
|
|
last_click TIMESTAMP,
|
|
total_sends INTEGER DEFAULT 0,
|
|
total_opens INTEGER DEFAULT 0,
|
|
total_clicks INTEGER DEFAULT 0,
|
|
is_valid BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.data_segments (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255),
|
|
description TEXT,
|
|
criteria TEXT,
|
|
size INTEGER DEFAULT 0,
|
|
best_verticals TEXT,
|
|
avg_engagement FLOAT DEFAULT 0,
|
|
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.offer_registry (
|
|
id SERIAL PRIMARY KEY,
|
|
offer_id VARCHAR(100) UNIQUE,
|
|
name VARCHAR(255),
|
|
vertical VARCHAR(100),
|
|
geo_targets TEXT,
|
|
payout FLOAT,
|
|
epc FLOAT DEFAULT 0,
|
|
conversion_rate FLOAT DEFAULT 0,
|
|
best_segments TEXT,
|
|
creative_urls TEXT,
|
|
tracking_url TEXT,
|
|
is_active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.offer_data_matches (
|
|
id SERIAL PRIMARY KEY,
|
|
offer_id INTEGER,
|
|
segment_id INTEGER,
|
|
match_score FLOAT,
|
|
predicted_conversion FLOAT,
|
|
last_tested TIMESTAMP,
|
|
actual_conversion FLOAT
|
|
);
|
|
");
|
|
|
|
class DataIntelligence {
|
|
private $pdo;
|
|
|
|
public function __construct($pdo) {
|
|
$this->pdo = $pdo;
|
|
}
|
|
|
|
// ============================================
|
|
// DATA SEGMENTATION
|
|
// ============================================
|
|
|
|
public function createSegment($name, $criteria) {
|
|
$this->pdo->prepare("INSERT INTO admin.data_segments (name, criteria) VALUES (?, ?) ON CONFLICT DO NOTHING")
|
|
->execute([$name, json_encode($criteria)]);
|
|
|
|
$segmentId = $this->pdo->lastInsertId();
|
|
$this->updateSegmentSize($segmentId);
|
|
|
|
return ['success' => true, 'segment_id' => $segmentId];
|
|
}
|
|
|
|
public function autoSegment() {
|
|
// Create standard segments automatically
|
|
$segments = [
|
|
['Hot Openers', ['last_open_days' => 7, 'engagement_score_min' => 0.7]],
|
|
['Warm Openers', ['last_open_days' => 30, 'engagement_score_min' => 0.4]],
|
|
['Cold List', ['last_open_days' => 90, 'engagement_score_max' => 0.3]],
|
|
['Clickers', ['total_clicks_min' => 1]],
|
|
['High Value', ['total_clicks_min' => 3, 'engagement_score_min' => 0.8]],
|
|
['US Audience', ['country' => 'US']],
|
|
['EU Audience', ['country' => ['FR', 'DE', 'UK', 'IT', 'ES']]],
|
|
['FR Audience', ['country' => 'FR', 'language' => 'fr']],
|
|
['Never Opened', ['total_opens' => 0, 'total_sends_min' => 3]]
|
|
];
|
|
|
|
foreach ($segments as $seg) {
|
|
$this->createSegment($seg[0], $seg[1]);
|
|
}
|
|
|
|
return ['success' => true, 'segments_created' => count($segments)];
|
|
}
|
|
|
|
private function updateSegmentSize($segmentId) {
|
|
// In real implementation, would count matching profiles
|
|
$this->pdo->exec("UPDATE admin.data_segments SET size = (SELECT COUNT(*) FROM admin.data_profiles WHERE is_valid = true), last_updated = NOW() WHERE id = $segmentId");
|
|
}
|
|
|
|
public function getSegmentData($segmentId, $limit = 1000) {
|
|
$segment = $this->pdo->query("SELECT * FROM admin.data_segments WHERE id = $segmentId")->fetch(PDO::FETCH_ASSOC);
|
|
if (!$segment) return ['error' => 'Segment not found'];
|
|
|
|
// Build query from criteria (simplified)
|
|
$profiles = $this->pdo->query("SELECT email, first_name, country, engagement_score FROM admin.data_profiles WHERE is_valid = true ORDER BY engagement_score DESC LIMIT $limit")->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
return ['segment' => $segment, 'profiles' => $profiles];
|
|
}
|
|
|
|
// ============================================
|
|
// OFFER MATCHING
|
|
// ============================================
|
|
|
|
public function registerOffer($data) {
|
|
$this->pdo->prepare("INSERT INTO admin.offer_registry (offer_id, name, vertical, geo_targets, payout, tracking_url, creative_urls) VALUES (?, ?, ?, ?, ?, ?, ?) ON CONFLICT (offer_id) DO UPDATE SET name = ?, vertical = ?, payout = ?")
|
|
->execute([
|
|
$data['offer_id'], $data['name'], $data['vertical'],
|
|
json_encode($data['geo_targets'] ?? []), $data['payout'] ?? 0,
|
|
$data['tracking_url'] ?? '', json_encode($data['creatives'] ?? []),
|
|
$data['name'], $data['vertical'], $data['payout'] ?? 0
|
|
]);
|
|
|
|
return ['success' => true];
|
|
}
|
|
|
|
public function matchOfferToData($offerId) {
|
|
$offer = $this->pdo->query("SELECT * FROM admin.offer_registry WHERE id = $offerId OR offer_id = '$offerId'")->fetch(PDO::FETCH_ASSOC);
|
|
if (!$offer) return ['error' => 'Offer not found'];
|
|
|
|
$geoTargets = json_decode($offer['geo_targets'] ?? '[]', true);
|
|
$vertical = $offer['vertical'];
|
|
|
|
// Find best matching segments
|
|
$segments = $this->pdo->query("SELECT * FROM admin.data_segments ORDER BY avg_engagement DESC")->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$matches = [];
|
|
foreach ($segments as $seg) {
|
|
$score = $this->calculateMatchScore($offer, $seg);
|
|
$matches[] = [
|
|
'segment_id' => $seg['id'],
|
|
'segment_name' => $seg['name'],
|
|
'match_score' => $score,
|
|
'predicted_conversion' => $score * ($offer['conversion_rate'] ?: 0.01),
|
|
'size' => $seg['size']
|
|
];
|
|
}
|
|
|
|
// Sort by score
|
|
usort($matches, fn($a, $b) => $b['match_score'] <=> $a['match_score']);
|
|
|
|
return ['offer' => $offer, 'matches' => array_slice($matches, 0, 5)];
|
|
}
|
|
|
|
private function calculateMatchScore($offer, $segment) {
|
|
$score = 0.5; // Base score
|
|
|
|
// Engagement boost
|
|
$score += ($segment['avg_engagement'] ?? 0) * 0.3;
|
|
|
|
// Size factor (prefer larger segments)
|
|
if ($segment['size'] > 10000) $score += 0.1;
|
|
if ($segment['size'] > 50000) $score += 0.1;
|
|
|
|
return min(1.0, $score);
|
|
}
|
|
|
|
public function findBestOfferForSegment($segmentId) {
|
|
$segment = $this->pdo->query("SELECT * FROM admin.data_segments WHERE id = $segmentId")->fetch(PDO::FETCH_ASSOC);
|
|
if (!$segment) return ['error' => 'Segment not found'];
|
|
|
|
$offers = $this->pdo->query("SELECT * FROM admin.offer_registry WHERE is_active = true ORDER BY epc DESC")->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$recommendations = [];
|
|
foreach ($offers as $offer) {
|
|
$score = $this->calculateMatchScore($offer, $segment);
|
|
$recommendations[] = [
|
|
'offer_id' => $offer['offer_id'],
|
|
'offer_name' => $offer['name'],
|
|
'vertical' => $offer['vertical'],
|
|
'payout' => $offer['payout'],
|
|
'match_score' => $score,
|
|
'expected_revenue' => $score * $offer['payout'] * $segment['size'] * 0.001
|
|
];
|
|
}
|
|
|
|
usort($recommendations, fn($a, $b) => $b['expected_revenue'] <=> $a['expected_revenue']);
|
|
|
|
return ['segment' => $segment, 'recommendations' => array_slice($recommendations, 0, 5)];
|
|
}
|
|
|
|
// ============================================
|
|
// DATA QUALITY
|
|
// ============================================
|
|
|
|
public function analyzeDataQuality() {
|
|
return [
|
|
'total_profiles' => $this->pdo->query("SELECT COUNT(*) FROM admin.data_profiles")->fetchColumn(),
|
|
'valid_profiles' => $this->pdo->query("SELECT COUNT(*) FROM admin.data_profiles WHERE is_valid = true")->fetchColumn(),
|
|
'with_engagement' => $this->pdo->query("SELECT COUNT(*) FROM admin.data_profiles WHERE total_opens > 0")->fetchColumn(),
|
|
'by_country' => $this->pdo->query("SELECT country, COUNT(*) as count FROM admin.data_profiles GROUP BY country ORDER BY count DESC LIMIT 10")->fetchAll(PDO::FETCH_ASSOC),
|
|
'avg_engagement' => $this->pdo->query("SELECT AVG(engagement_score) FROM admin.data_profiles")->fetchColumn()
|
|
];
|
|
}
|
|
|
|
public function updateEngagementScores() {
|
|
// Calculate engagement score based on opens/clicks
|
|
$this->pdo->exec("UPDATE admin.data_profiles SET engagement_score =
|
|
CASE
|
|
WHEN total_sends = 0 THEN 0.5
|
|
ELSE LEAST(1.0, (total_opens::float / total_sends * 0.5) + (total_clicks::float / GREATEST(total_opens, 1) * 0.5))
|
|
END
|
|
");
|
|
|
|
return ['success' => true];
|
|
}
|
|
|
|
public function getStats() {
|
|
return [
|
|
'profiles' => $this->pdo->query("SELECT COUNT(*) FROM admin.data_profiles")->fetchColumn(),
|
|
'segments' => $this->pdo->query("SELECT COUNT(*) FROM admin.data_segments")->fetchColumn(),
|
|
'offers' => $this->pdo->query("SELECT COUNT(*) FROM admin.offer_registry WHERE is_active = true")->fetchColumn(),
|
|
'sources' => $this->pdo->query("SELECT COUNT(*) FROM admin.data_sources WHERE is_active = true")->fetchColumn()
|
|
];
|
|
}
|
|
}
|
|
|
|
$dataEngine = new DataIntelligence($pdo);
|
|
$action = $_POST['action'] ?? $_GET['action'] ?? '';
|
|
|
|
switch ($action) {
|
|
case 'create_segment':
|
|
echo json_encode($dataEngine->createSegment($_POST['name'], json_decode($_POST['criteria'], true)));
|
|
break;
|
|
case 'auto_segment':
|
|
echo json_encode($dataEngine->autoSegment());
|
|
break;
|
|
case 'get_segment':
|
|
echo json_encode($dataEngine->getSegmentData($_GET['segment_id'], $_GET['limit'] ?? 1000));
|
|
break;
|
|
case 'segments':
|
|
echo json_encode(['segments' => $pdo->query("SELECT * FROM admin.data_segments ORDER BY size DESC")->fetchAll(PDO::FETCH_ASSOC)]);
|
|
break;
|
|
case 'register_offer':
|
|
echo json_encode($dataEngine->registerOffer($_POST));
|
|
break;
|
|
case 'match_offer':
|
|
echo json_encode($dataEngine->matchOfferToData($_GET['offer_id']));
|
|
break;
|
|
case 'recommend_offer':
|
|
echo json_encode($dataEngine->findBestOfferForSegment($_GET['segment_id']));
|
|
break;
|
|
case 'offers':
|
|
echo json_encode(['offers' => $pdo->query("SELECT * FROM admin.offer_registry ORDER BY epc DESC")->fetchAll(PDO::FETCH_ASSOC)]);
|
|
break;
|
|
case 'quality':
|
|
echo json_encode($dataEngine->analyzeDataQuality());
|
|
break;
|
|
case 'update_engagement':
|
|
echo json_encode($dataEngine->updateEngagementScores());
|
|
break;
|
|
case 'stats':
|
|
echo json_encode($dataEngine->getStats());
|
|
break;
|
|
default:
|
|
echo json_encode(['name' => 'Data Intelligence Engine', 'actions' => ['create_segment','auto_segment','get_segment','segments','register_offer','match_offer','recommend_offer','offers','quality','update_engagement','stats']]);
|
|
}
|
|
|