­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ Campaign DETAILS export * - ?endpoint=adgroup => Ad Group DETAILS export * - ?endpoint=ad => Ad DETAILS export * - ?endpoint=creative => Creative-like DETAILS export * - ?endpoint=insights => Ad-level INSIGHTS export * * REQUIRED POST: * AUTH, from=YYYY-MM-DD, to=YYYY-MM-DD * * OUTPUT FORMAT: * { * "extraction_time": "...", * "run_date": "YYYY-MM-DD", * "data_date": "YYYY-MM-DD", * "source_system": "google_ads", * "data_type": "campaigns|adgroups|ads|creatives|insights", * "record_count": N, * "data": [...] * } */ declare(strict_types=1); date_default_timezone_set('Asia/Riyadh'); $yesterday = new DateTime('yesterday'); $dttt= $yesterday->format('Y-m-d'); //$dttt = '2026-05-16'; $_POST['from'] = $dttt; $_POST['to'] = $dttt; header('Content-Type: application/json; charset=utf-8'); /** ------------------- BASIC CONFIG ------------------- **/ const MULTIPLIER = 2.5; // apply only to insights monetary values if you still want same behavior if (($_POST['AUTH'] ?? '') !== 'a2h1cnJhbS5kaGVkaGlAY29yZTNjb25zdWx0YW5jeS5jb206QkdDamVkZGFoQDY5') { http_response_code(401); echo json_encode([ 'ok' => false, 'error' => 'Authentication Error' ], JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE); exit; } $ENDPOINT_MODE = strtolower(trim((string)($_GET['endpoint'] ?? 'insights'))); if (!in_array($ENDPOINT_MODE, ['campaign', 'adgroup', 'ad', 'creative', 'insights'], true)) { http_response_code(400); echo json_encode([ 'ok' => false, 'error' => 'Invalid endpoint. Use campaign|adgroup|ad|creative|insights' ], JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE); exit; } $SINCE = trim((string)($_POST['from'] ?? '')); $UNTIL = trim((string)($_POST['to'] ?? '')); if ($SINCE === '' || $UNTIL === '') { http_response_code(400); echo json_encode([ 'ok' => false, 'error' => 'Missing from/to date. Send POST: from=YYYY-MM-DD&to=YYYY-MM-DD' ], JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE); exit; } /** ------------------- ENV CONFIG ------------------- **/ $GOOGLE_ADS_VERSION = 'v23'; /** * IMPORTANT: * Use real env vars on server. * Do NOT keep secrets in code in production. */ $DEVELOPER_TOKEN = getenv('GOOGLE_ADS_DEVELOPER_TOKEN') ?: 'y8lF6kuiuSDv--Gjx7V2DQ'; $CLIENT_ID = getenv('GOOGLE_ADS_CLIENT_ID') ?: '858812719156-ou2sg114mil1scddjisvik6f0skqbiim.apps.googleusercontent.com'; $CLIENT_SECRET = getenv('GOOGLE_ADS_CLIENT_SECRET') ?: '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'; $REFRESH_TOKEN = getenv('GOOGLE_ADS_REFRESH_TOKEN') ?: '1//03wThUss62VrpCgYIARAAGAMSNwF-L9Ir7xUDs9RmF-QTAKR5XA8MSGoq9rlpQw2PxvP5UoAggU2PYcVtO8mSsbijdKr6MycacfM'; $CUSTOMER_ID = preg_replace('/\D+/', '', (string)(getenv('GOOGLE_ADS_CUSTOMER_ID') ?: '493-412-4696')); $LOGIN_CUSTOMER_ID = preg_replace('/\D+/', '', (string)(getenv('GOOGLE_ADS_LOGIN_CUSTOMER_ID') ?: '762-429-5415')); $DB_HOST = getenv('DB_HOST') ?: 'localhost'; $DB_NAME = getenv('DB_NAME') ?: 'coreagen_marketing_insights'; $DB_USER = getenv('DB_USER') ?: 'coreagen_marketing_insights'; $DB_PASS = getenv('DB_PASS') ?: 'Vision@2050'; $DB_PORT = getenv('DB_PORT') ?: '3306'; $RUN_ID = date('Ymd_His') . '_' . bin2hex(random_bytes(4)); /** ------------------- HELPERS ------------------- **/ function to_float($v): ?float { if ($v === null || $v === '') { return null; } if (is_numeric($v)) { return (float)$v; } return null; } function micros_to_unit($v): ?float { $f = to_float($v); if ($f === null) { return null; } return $f / 1000000; } function multiply_if_numeric(array &$arr, string $key, float $mult): void { if (!array_key_exists($key, $arr)) { return; } $f = to_float($arr[$key]); if ($f === null) { return; } $arr[$key] = $f * $mult; } function today_date(): string { return date('Y-m-d'); } function iso_now_micro(): string { $micro = microtime(true); $dt = DateTime::createFromFormat('U.u', sprintf('%.6f', $micro)); return $dt ? $dt->format('Y-m-d\TH:i:s.u') : date('Y-m-d\TH:i:s'); } function export_payload(string $dataType, string $dataDate, array $data): array { /* return [ 'extraction_time' => iso_now_micro(), 'run_date' => today_date(), 'data_date' => $dataDate, 'source_system' => 'google_ads', 'data_type' => $dataType, 'record_count' => count($data), 'data' => array_values($data), ]; */ return array_values($data); } function compute_cpl_cpa_cpv(array $row): array { $spend = to_float($row['spend'] ?? null) ?? 0.0; $conversions = to_float($row['conversions'] ?? null) ?? 0.0; $videoViews = to_float($row['video_views'] ?? null) ?? 0.0; return [ 'cpl' => ($conversions > 0 ? $spend / $conversions : null), 'cpa' => ($conversions > 0 ? $spend / $conversions : null), 'cpv' => ($videoViews > 0 ? $spend / $videoViews : null), ]; } function apply_multiplier_and_kpis(array &$row, float $mult): void { multiply_if_numeric($row, 'spend', $mult); multiply_if_numeric($row, 'cpc', $mult); multiply_if_numeric($row, 'cpm', $mult); multiply_if_numeric($row, 'cpl', $mult); multiply_if_numeric($row, 'cpa', $mult); multiply_if_numeric($row, 'cpv', $mult); multiply_if_numeric($row, 'campaign_budget', $mult); multiply_if_numeric($row, 'cost_per_conversion', $mult); multiply_if_numeric($row, 'average_cpv', $mult); } function arr_get(array $arr, array $path, $default = null) { $cur = $arr; foreach ($path as $key) { if (!is_array($cur) || !array_key_exists($key, $cur)) { return $default; } $cur = $cur[$key]; } return $cur; } /** ------------------- LOGGER ------------------- **/ class Logger { private string $logDir; private string $runId; public function __construct(string $logDir, string $runId) { $this->logDir = rtrim($logDir, '/'); $this->runId = $runId; if (!is_dir($this->logDir)) { mkdir($this->logDir, 0755, true); } } public function info(string $message, array $context = []): void { $this->write('INFO', $message, $context); } public function error(string $message, array $context = []): void { $this->write('ERROR', $message, $context); } private function write(string $level, string $message, array $context): void { $file = $this->logDir . '/google_ads_' . date('Ymd') . '.log'; $line = [ 'ts' => date('Y-m-d H:i:s'), 'level' => $level, 'run_id' => $this->runId, 'msg' => $message, 'ctx' => $context ]; file_put_contents($file, json_encode($line, JSON_UNESCAPED_UNICODE) . PHP_EOL, FILE_APPEND); } } /** ------------------- GOOGLE ADS CLIENT ------------------- **/ class GoogleAdsClient { private string $version; private string $developerToken; private string $clientId; private string $clientSecret; private string $refreshToken; private string $customerId; private ?string $loginCustomerId; private int $timeoutSec; private ?Logger $logger; public function __construct( string $version, string $developerToken, string $clientId, string $clientSecret, string $refreshToken, string $customerId, ?string $loginCustomerId = null, int $timeoutSec = 90, ?Logger $logger = null ) { $this->version = $version; $this->developerToken = $developerToken; $this->clientId = $clientId; $this->clientSecret = $clientSecret; $this->refreshToken = $refreshToken; $this->customerId = $customerId; $this->loginCustomerId = $loginCustomerId ?: null; $this->timeoutSec = $timeoutSec; $this->logger = $logger; } private function httpPostForm(string $url, array $postFields): array { $ch = curl_init($url); curl_setopt_array($ch, [ CURLOPT_RETURNTRANSFER => true, CURLOPT_POST => true, CURLOPT_TIMEOUT => $this->timeoutSec, CURLOPT_CONNECTTIMEOUT => 15, CURLOPT_SSL_VERIFYPEER => true, CURLOPT_HTTPHEADER => ['Content-Type: application/x-www-form-urlencoded'], CURLOPT_POSTFIELDS => http_build_query($postFields), ]); $body = curl_exec($ch); $errno = curl_errno($ch); $err = curl_error($ch); $http = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE); curl_close($ch); if ($errno) { throw new RuntimeException("OAuth cURL error ({$errno}): {$err}"); } if ($body === false || $body === '') { throw new RuntimeException("Empty OAuth response (HTTP {$http})"); } $json = json_decode($body, true); if (!is_array($json)) { throw new RuntimeException("Invalid OAuth response (HTTP {$http}): " . substr($body, 0, 1000)); } if ($http >= 400 || isset($json['error'])) { throw new RuntimeException("OAuth error: " . json_encode($json, JSON_UNESCAPED_UNICODE)); } return $json; } private function httpPostJson(string $url, array $headers, array $payload): array { $ch = curl_init($url); curl_setopt_array($ch, [ CURLOPT_RETURNTRANSFER => true, CURLOPT_POST => true, CURLOPT_TIMEOUT => $this->timeoutSec, CURLOPT_CONNECTTIMEOUT => 15, CURLOPT_SSL_VERIFYPEER => true, CURLOPT_HTTPHEADER => $headers, CURLOPT_POSTFIELDS => json_encode($payload, JSON_UNESCAPED_UNICODE), ]); $body = curl_exec($ch); $errno = curl_errno($ch); $err = curl_error($ch); $http = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE); $headerSize = (int)curl_getinfo($ch, CURLINFO_HEADER_SIZE); curl_close($ch); if ($errno) { throw new RuntimeException("Google Ads cURL error ({$errno}): {$err}"); } if ($body === false || $body === '') { throw new RuntimeException("Empty Google Ads response (HTTP {$http})"); } $json = json_decode($body, true); if ($json === null && json_last_error() !== JSON_ERROR_NONE) { throw new RuntimeException("Invalid Google Ads JSON response (HTTP {$http}): " . substr($body, 0, 1000)); } if ($http >= 400) { $msg = is_array($json) ? json_encode($json, JSON_UNESCAPED_UNICODE) : substr($body, 0, 1000); throw new RuntimeException("Google Ads API HTTP {$http}: {$msg}"); } return is_array($json) ? $json : []; } public function getAccessToken(): string { $resp = $this->httpPostForm('https://oauth2.googleapis.com/token', [ 'client_id' => $this->clientId, 'client_secret' => $this->clientSecret, 'refresh_token' => $this->refreshToken, 'grant_type' => 'refresh_token', ]); $token = (string)($resp['access_token'] ?? ''); if ($token === '') { throw new RuntimeException('Unable to obtain Google OAuth access token'); } return $token; } public function searchStream(string $query, int $maxRetries = 2): array { $attempt = 0; do { $attempt++; try { $accessToken = $this->getAccessToken(); $url = "https://googleads.googleapis.com/{$this->version}/customers/{$this->customerId}/googleAds:searchStream"; $headers = [ 'Authorization: Bearer ' . $accessToken, 'developer-token: ' . $this->developerToken, 'Content-Type: application/json', ]; if ($this->loginCustomerId) { $headers[] = 'login-customer-id: ' . $this->loginCustomerId; } $resp = $this->httpPostJson($url, $headers, ['query' => $query]); $rows = []; foreach ($resp as $chunk) { if (!isset($chunk['results']) || !is_array($chunk['results'])) { continue; } foreach ($chunk['results'] as $row) { $rows[] = $row; } } return $rows; } catch (Throwable $e) { $msg = $e->getMessage(); if ($this->logger) { $this->logger->error('Google Ads searchStream failed', [ 'attempt' => $attempt, 'error' => $msg, 'query' => $query ]); } $isInternal = stripos($msg, 'HTTP 500') !== false || stripos($msg, '"status":"INTERNAL"') !== false; if ($isInternal && $attempt <= $maxRetries) { usleep(800000 * $attempt); continue; } throw $e; } } while ($attempt <= $maxRetries); return []; } public function getCustomerMeta(): array { $query = " SELECT customer.id, customer.descriptive_name, customer.currency_code FROM customer LIMIT 1 "; $rows = $this->searchStream($query); return $rows[0]['customer'] ?? []; } } /** ------------------- REPO ------------------- **/ class Repo { private PDO $pdo; public function __construct(PDO $pdo) { $this->pdo = $pdo; $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } public function ensureTables(): void { $this->pdo->exec(" CREATE TABLE IF NOT EXISTS hondav2_google_campaign_details ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, run_id VARCHAR(64) NOT NULL, customer_id VARCHAR(32) NOT NULL, campaign_id VARCHAR(64) NOT NULL, campaign_name VARCHAR(255) NULL, status VARCHAR(64) NULL, advertising_channel_type VARCHAR(64) NULL, start_date DATE NULL, end_date DATE NULL, campaign_budget DECIMAL(18,6) NULL, optimization_score DECIMAL(18,6) NULL, raw_json JSON NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_campaign (customer_id, campaign_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; "); $this->pdo->exec(" CREATE TABLE IF NOT EXISTS hondav2_google_adgroup_details ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, run_id VARCHAR(64) NOT NULL, customer_id VARCHAR(32) NOT NULL, campaign_id VARCHAR(64) NULL, campaign_name VARCHAR(255) NULL, adgroup_id VARCHAR(64) NOT NULL, adgroup_name VARCHAR(255) NULL, status VARCHAR(64) NULL, type VARCHAR(64) NULL, cpc_bid_micros BIGINT NULL, raw_json JSON NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_adgroup (customer_id, adgroup_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; "); $this->pdo->exec(" CREATE TABLE IF NOT EXISTS hondav2_google_ad_details ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, run_id VARCHAR(64) NOT NULL, customer_id VARCHAR(32) NOT NULL, campaign_id VARCHAR(64) NULL, campaign_name VARCHAR(255) NULL, adgroup_id VARCHAR(64) NULL, ad_id VARCHAR(64) NOT NULL, ad_name VARCHAR(255) NULL, ad_type VARCHAR(64) NULL, ad_status VARCHAR(64) NULL, final_urls_json JSON NULL, raw_json JSON NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_ad (customer_id, ad_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; "); $this->pdo->exec(" CREATE TABLE IF NOT EXISTS hondav2_google_creative_details ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, run_id VARCHAR(64) NOT NULL, customer_id VARCHAR(32) NOT NULL, campaign_id VARCHAR(64) NULL, campaign_name VARCHAR(255) NULL, adgroup_id VARCHAR(64) NULL, ad_id VARCHAR(64) NOT NULL, ad_name VARCHAR(255) NULL, ad_type VARCHAR(64) NULL, display_url TEXT NULL, final_urls_json JSON NULL, final_mobile_urls_json JSON NULL, tracking_url_template TEXT NULL, raw_json JSON NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_creative (customer_id, ad_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; "); $this->pdo->exec(" CREATE TABLE IF NOT EXISTS hondav2_google_ad_insights ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, run_id VARCHAR(64) NOT NULL, customer_id VARCHAR(32) NOT NULL, date_start DATE NOT NULL, date_stop DATE NOT NULL, campaign_id VARCHAR(64) NULL, campaign_name VARCHAR(255) NULL, adgroup_id VARCHAR(64) NULL, adgroup_name VARCHAR(255) NULL, ad_id VARCHAR(64) NOT NULL, ad_name VARCHAR(255) NULL, currency VARCHAR(16) NULL, campaign_status VARCHAR(64) NULL, adgroup_status VARCHAR(64) NULL, ad_status VARCHAR(64) NULL, campaign_budget DECIMAL(18,6) NULL, impressions BIGINT NULL, clicks BIGINT NULL, ctr DECIMAL(18,6) NULL, cpc DECIMAL(18,6) NULL, cpm DECIMAL(18,6) NULL, spend DECIMAL(18,6) NULL, conversions DECIMAL(18,6) NULL, cost_per_conversion DECIMAL(18,6) NULL, video_views BIGINT NULL, average_cpv DECIMAL(18,6) NULL, cpl DECIMAL(18,6) NULL, cpa DECIMAL(18,6) NULL, cpv DECIMAL(18,6) NULL, raw_json JSON NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_row (customer_id, date_start, ad_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; "); } public function upsertCampaignDetails(string $runId, string $customerId, array $rows): int { $sql = " INSERT INTO hondav2_google_campaign_details (run_id, customer_id, campaign_id, campaign_name, status, advertising_channel_type, start_date, end_date, campaign_budget, optimization_score, raw_json) VALUES (:run_id, :customer_id, :campaign_id, :campaign_name, :status, :advertising_channel_type, :start_date, :end_date, :campaign_budget, :optimization_score, :raw_json) ON DUPLICATE KEY UPDATE run_id=VALUES(run_id), campaign_name=VALUES(campaign_name), status=VALUES(status), advertising_channel_type=VALUES(advertising_channel_type), start_date=VALUES(start_date), end_date=VALUES(end_date), campaign_budget=VALUES(campaign_budget), optimization_score=VALUES(optimization_score), raw_json=VALUES(raw_json), updated_at=CURRENT_TIMESTAMP "; $stmt = $this->pdo->prepare($sql); $count = 0; $this->pdo->beginTransaction(); try { foreach ($rows as $r) { $stmt->execute([ ':run_id' => $runId, ':customer_id' => $customerId, ':campaign_id' => $r['campaign_id'], ':campaign_name' => $r['campaign_name'] ?? null, ':status' => $r['status'] ?? null, ':advertising_channel_type' => $r['advertising_channel_type'] ?? null, ':start_date' => $r['start_date'] ?? null, ':end_date' => $r['end_date'] ?? null, ':campaign_budget' => $r['campaign_budget'] ?? null, ':optimization_score' => $r['optimization_score'] ?? null, ':raw_json' => json_encode($r, JSON_UNESCAPED_UNICODE), ]); $count++; } $this->pdo->commit(); } catch (Throwable $e) { $this->pdo->rollBack(); throw $e; } return $count; } public function upsertAdgroupDetails(string $runId, string $customerId, array $rows): int { $sql = " INSERT INTO hondav2_google_adgroup_details (run_id, customer_id, campaign_id, campaign_name, adgroup_id, adgroup_name, status, type, cpc_bid_micros, raw_json) VALUES (:run_id, :customer_id, :campaign_id, :campaign_name, :adgroup_id, :adgroup_name, :status, :type, :cpc_bid_micros, :raw_json) ON DUPLICATE KEY UPDATE run_id=VALUES(run_id), campaign_id=VALUES(campaign_id), campaign_name=VALUES(campaign_name), adgroup_name=VALUES(adgroup_name), status=VALUES(status), type=VALUES(type), cpc_bid_micros=VALUES(cpc_bid_micros), raw_json=VALUES(raw_json), updated_at=CURRENT_TIMESTAMP "; $stmt = $this->pdo->prepare($sql); $count = 0; $this->pdo->beginTransaction(); try { foreach ($rows as $r) { $stmt->execute([ ':run_id' => $runId, ':customer_id' => $customerId, ':campaign_id' => $r['campaign_id'] ?? null, ':campaign_name' => $r['campaign_name'] ?? null, ':adgroup_id' => $r['adgroup_id'], ':adgroup_name' => $r['adgroup_name'] ?? null, ':status' => $r['status'] ?? null, ':type' => $r['type'] ?? null, ':cpc_bid_micros' => $r['cpc_bid_micros'] ?? null, ':raw_json' => json_encode($r, JSON_UNESCAPED_UNICODE), ]); $count++; } $this->pdo->commit(); } catch (Throwable $e) { $this->pdo->rollBack(); throw $e; } return $count; } public function upsertAdDetails(string $runId, string $customerId, array $rows): int { $sql = " INSERT INTO hondav2_google_ad_details (run_id, customer_id, campaign_id, campaign_name, adgroup_id, ad_id, ad_name, ad_type, ad_status, final_urls_json, raw_json) VALUES (:run_id, :customer_id, :campaign_id, :campaign_name, :adgroup_id, :ad_id, :ad_name, :ad_type, :ad_status, :final_urls_json, :raw_json) ON DUPLICATE KEY UPDATE run_id=VALUES(run_id), campaign_id=VALUES(campaign_id), campaign_name=VALUES(campaign_name), adgroup_id=VALUES(adgroup_id), ad_name=VALUES(ad_name), ad_type=VALUES(ad_type), ad_status=VALUES(ad_status), final_urls_json=VALUES(final_urls_json), raw_json=VALUES(raw_json), updated_at=CURRENT_TIMESTAMP "; $stmt = $this->pdo->prepare($sql); $count = 0; $this->pdo->beginTransaction(); try { foreach ($rows as $r) { $stmt->execute([ ':run_id' => $runId, ':customer_id' => $customerId, ':campaign_id' => $r['campaign_id'] ?? null, ':campaign_name' => $r['campaign_name'] ?? null, ':adgroup_id' => $r['adgroup_id'] ?? null, ':ad_id' => $r['ad_id'], ':ad_name' => $r['ad_name'] ?? null, ':ad_type' => $r['ad_type'] ?? null, ':ad_status' => $r['ad_status'] ?? null, ':final_urls_json' => isset($r['final_urls']) ? json_encode($r['final_urls'], JSON_UNESCAPED_UNICODE) : null, ':raw_json' => json_encode($r, JSON_UNESCAPED_UNICODE), ]); $count++; } $this->pdo->commit(); } catch (Throwable $e) { $this->pdo->rollBack(); throw $e; } return $count; } public function upsertCreativeDetails(string $runId, string $customerId, array $rows): int { $sql = " INSERT INTO hondav2_google_creative_details (run_id, customer_id, campaign_id, campaign_name, adgroup_id, ad_id, ad_name, ad_type, display_url, final_urls_json, final_mobile_urls_json, tracking_url_template, raw_json) VALUES (:run_id, :customer_id, :campaign_id, :campaign_name, :adgroup_id, :ad_id, :ad_name, :ad_type, :display_url, :final_urls_json, :final_mobile_urls_json, :tracking_url_template, :raw_json) ON DUPLICATE KEY UPDATE run_id=VALUES(run_id), campaign_id=VALUES(campaign_id), campaign_name=VALUES(campaign_name), adgroup_id=VALUES(adgroup_id), ad_name=VALUES(ad_name), ad_type=VALUES(ad_type), display_url=VALUES(display_url), final_urls_json=VALUES(final_urls_json), final_mobile_urls_json=VALUES(final_mobile_urls_json), tracking_url_template=VALUES(tracking_url_template), raw_json=VALUES(raw_json), updated_at=CURRENT_TIMESTAMP "; $stmt = $this->pdo->prepare($sql); $count = 0; $this->pdo->beginTransaction(); try { foreach ($rows as $r) { $stmt->execute([ ':run_id' => $runId, ':customer_id' => $customerId, ':campaign_id' => $r['campaign_id'] ?? null, ':campaign_name' => $r['campaign_name'] ?? null, ':adgroup_id' => $r['adgroup_id'] ?? null, ':ad_id' => $r['ad_id'], ':ad_name' => $r['ad_name'] ?? null, ':ad_type' => $r['ad_type'] ?? null, ':display_url' => $r['display_url'] ?? null, ':final_urls_json' => isset($r['final_urls']) ? json_encode($r['final_urls'], JSON_UNESCAPED_UNICODE) : null, ':final_mobile_urls_json' => isset($r['final_mobile_urls']) ? json_encode($r['final_mobile_urls'], JSON_UNESCAPED_UNICODE) : null, ':tracking_url_template' => $r['tracking_url_template'] ?? null, ':raw_json' => json_encode($r, JSON_UNESCAPED_UNICODE), ]); $count++; } $this->pdo->commit(); } catch (Throwable $e) { $this->pdo->rollBack(); throw $e; } return $count; } public function upsertAdInsightsRows(string $runId, string $customerId, array $rows): int { $sql = " INSERT INTO hondav2_google_ad_insights (run_id, customer_id, date_start, date_stop, campaign_id, campaign_name, adgroup_id, adgroup_name, ad_id, ad_name, currency, campaign_status, adgroup_status, ad_status, campaign_budget, impressions, clicks, ctr, cpc, cpm, spend, conversions, cost_per_conversion, video_views, average_cpv, cpl, cpa, cpv, raw_json) VALUES (:run_id, :customer_id, :date_start, :date_stop, :campaign_id, :campaign_name, :adgroup_id, :adgroup_name, :ad_id, :ad_name, :currency, :campaign_status, :adgroup_status, :ad_status, :campaign_budget, :impressions, :clicks, :ctr, :cpc, :cpm, :spend, :conversions, :cost_per_conversion, :video_views, :average_cpv, :cpl, :cpa, :cpv, :raw_json) ON DUPLICATE KEY UPDATE run_id=VALUES(run_id), campaign_id=VALUES(campaign_id), campaign_name=VALUES(campaign_name), adgroup_id=VALUES(adgroup_id), adgroup_name=VALUES(adgroup_name), ad_name=VALUES(ad_name), currency=VALUES(currency), campaign_status=VALUES(campaign_status), adgroup_status=VALUES(adgroup_status), ad_status=VALUES(ad_status), campaign_budget=VALUES(campaign_budget), impressions=VALUES(impressions), clicks=VALUES(clicks), ctr=VALUES(ctr), cpc=VALUES(cpc), cpm=VALUES(cpm), spend=VALUES(spend), conversions=VALUES(conversions), cost_per_conversion=VALUES(cost_per_conversion), video_views=VALUES(video_views), average_cpv=VALUES(average_cpv), cpl=VALUES(cpl), cpa=VALUES(cpa), cpv=VALUES(cpv), raw_json=VALUES(raw_json), updated_at=CURRENT_TIMESTAMP "; $stmt = $this->pdo->prepare($sql); $count = 0; $this->pdo->beginTransaction(); try { foreach ($rows as $r) { $stmt->execute([ ':run_id' => $runId, ':customer_id' => $customerId, ':date_start' => $r['date_start'], ':date_stop' => $r['date_stop'], ':campaign_id' => $r['campaign_id'] ?? null, ':campaign_name' => $r['campaign_name'] ?? null, ':adgroup_id' => $r['adgroup_id'] ?? null, ':adgroup_name' => $r['adgroup_name'] ?? null, ':ad_id' => $r['ad_id'], ':ad_name' => $r['ad_name'] ?? null, ':currency' => $r['currency'] ?? null, ':campaign_status' => $r['campaign_status'] ?? null, ':adgroup_status' => $r['adgroup_status'] ?? null, ':ad_status' => $r['ad_status'] ?? null, ':campaign_budget' => $r['campaign_budget'] ?? null, ':impressions' => $r['impressions'] ?? null, ':clicks' => $r['clicks'] ?? null, ':ctr' => $r['ctr'] ?? null, ':cpc' => $r['cpc'] ?? null, ':cpm' => $r['cpm'] ?? null, ':spend' => $r['spend'] ?? null, ':conversions' => $r['conversions'] ?? null, ':cost_per_conversion' => $r['cost_per_conversion'] ?? null, ':video_views' => $r['video_views'] ?? null, ':average_cpv' => $r['average_cpv'] ?? null, ':cpl' => $r['cpl'] ?? null, ':cpa' => $r['cpa'] ?? null, ':cpv' => $r['cpv'] ?? null, ':raw_json' => json_encode($r, JSON_UNESCAPED_UNICODE), ]); $count++; } $this->pdo->commit(); } catch (Throwable $e) { $this->pdo->rollBack(); throw $e; } return $count; } } /** ------------------- MAIN ------------------- **/ $logger = new Logger(__DIR__ . '/logs', $RUN_ID); try { if ( $DEVELOPER_TOKEN === '' || $CLIENT_ID === '' || $CLIENT_SECRET === '' || $REFRESH_TOKEN === '' || $CUSTOMER_ID === '' ) { throw new RuntimeException('Missing Google Ads credentials or customer ID in environment variables'); } $dsn = "mysql:host={$DB_HOST};port={$DB_PORT};dbname={$DB_NAME};charset=utf8mb4"; $pdo = new PDO($dsn, $DB_USER, $DB_PASS, [ PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC ]); $repo = new Repo($pdo); $repo->ensureTables(); $client = new GoogleAdsClient( $GOOGLE_ADS_VERSION, $DEVELOPER_TOKEN, $CLIENT_ID, $CLIENT_SECRET, $REFRESH_TOKEN, $CUSTOMER_ID, $LOGIN_CUSTOMER_ID !== '' ? $LOGIN_CUSTOMER_ID : null, 90, $logger ); $customerMeta = $client->getCustomerMeta(); $currency = $customerMeta['currencyCode'] ?? null; $accountName = $customerMeta['descriptiveName'] ?? null; $outputDir = __DIR__ . '/output'; if (!is_dir($outputDir)) { mkdir($outputDir, 0755, true); } /** * 1) CAMPAIGN DETAILS */ if ($ENDPOINT_MODE === 'campaign') { $query = " SELECT campaign.id, campaign.name, campaign.status, campaign.advertising_channel_type, campaign.start_date, campaign.end_date, campaign.optimization_score, campaign_budget.amount_micros FROM campaign "; $rows = $client->searchStream($query); $out = []; foreach ($rows as $row) { $out[] = [ 'campaign_id' => arr_get($row, ['campaign', 'id']), 'campaign_name' => arr_get($row, ['campaign', 'name']), 'status' => arr_get($row, ['campaign', 'status']), 'advertising_channel_type' => arr_get($row, ['campaign', 'advertisingChannelType']), 'start_date' => arr_get($row, ['campaign', 'startDate']), 'end_date' => arr_get($row, ['campaign', 'endDate']), 'optimization_score' => to_float(arr_get($row, ['campaign', 'optimizationScore'])), 'campaign_budget' => micros_to_unit(arr_get($row, ['campaignBudget', 'amountMicros'])), 'account_id' => $CUSTOMER_ID, 'account_name' => $accountName, ]; } $repo->upsertCampaignDetails($RUN_ID, $CUSTOMER_ID, $out); $payload = export_payload('campaigns', $UNTIL, $out); file_put_contents($outputDir . "/google_campaigns_{$RUN_ID}.json", json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE)); echo json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE); exit; } /** * 2) AD GROUP DETAILS */ if ($ENDPOINT_MODE === 'adgroup') { $query = " SELECT campaign.id, campaign.name, ad_group.id, ad_group.name, ad_group.status, ad_group.type, ad_group.cpc_bid_micros FROM ad_group "; $rows = $client->searchStream($query); $out = []; foreach ($rows as $row) { $out[] = [ 'campaign_id' => arr_get($row, ['campaign', 'id']), 'campaign_name' => arr_get($row, ['campaign', 'name']), 'adgroup_id' => arr_get($row, ['adGroup', 'id']), 'adgroup_name' => arr_get($row, ['adGroup', 'name']), 'status' => arr_get($row, ['adGroup', 'status']), 'type' => arr_get($row, ['adGroup', 'type']), 'cpc_bid_micros' => arr_get($row, ['adGroup', 'cpcBidMicros']), 'account_id' => $CUSTOMER_ID, 'account_name' => $accountName, ]; } $repo->upsertAdgroupDetails($RUN_ID, $CUSTOMER_ID, $out); $payload = export_payload('adgroups', $UNTIL, $out); file_put_contents($outputDir . "/google_adgroups_{$RUN_ID}.json", json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE)); echo json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE); exit; } /** * 3) AD DETAILS */ if ($ENDPOINT_MODE === 'ad') { $query = " SELECT campaign.id, campaign.name, ad_group.id, ad_group.name, ad_group_ad.status, ad_group_ad.ad.id, ad_group_ad.ad.name, ad_group_ad.ad.type, ad_group_ad.ad.final_urls FROM ad_group_ad "; $rows = $client->searchStream($query); $out = []; foreach ($rows as $row) { $out[] = [ 'campaign_id' => arr_get($row, ['campaign', 'id']), 'campaign_name' => arr_get($row, ['campaign', 'name']), 'adgroup_id' => arr_get($row, ['adGroup', 'id']), 'adgroup_name' => arr_get($row, ['adGroup', 'name']), 'ad_id' => arr_get($row, ['adGroupAd', 'ad', 'id']), 'ad_name' => arr_get($row, ['adGroupAd', 'ad', 'name']), 'ad_type' => arr_get($row, ['adGroupAd', 'ad', 'type']), 'ad_status' => arr_get($row, ['adGroupAd', 'status']), 'final_urls' => arr_get($row, ['adGroupAd', 'ad', 'finalUrls'], []), 'account_id' => $CUSTOMER_ID, 'account_name' => $accountName, ]; } $repo->upsertAdDetails($RUN_ID, $CUSTOMER_ID, $out); $payload = export_payload('ads', $UNTIL, $out); file_put_contents($outputDir . "/google_ads_{$RUN_ID}.json", json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE)); echo json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE); exit; } /** * 4) CREATIVE DETAILS */ if ($ENDPOINT_MODE === 'creative') { $query = " SELECT campaign.id, campaign.name, ad_group.id, ad_group.name, ad_group_ad.ad.id, ad_group_ad.ad.name, ad_group_ad.ad.type, ad_group_ad.ad.display_url, ad_group_ad.ad.final_urls, ad_group_ad.ad.final_mobile_urls, ad_group_ad.ad.tracking_url_template FROM ad_group_ad "; $rows = $client->searchStream($query); $out = []; foreach ($rows as $row) { $out[] = [ 'campaign_id' => arr_get($row, ['campaign', 'id']), 'campaign_name' => arr_get($row, ['campaign', 'name']), 'adgroup_id' => arr_get($row, ['adGroup', 'id']), 'adgroup_name' => arr_get($row, ['adGroup', 'name']), 'ad_id' => arr_get($row, ['adGroupAd', 'ad', 'id']), 'ad_name' => arr_get($row, ['adGroupAd', 'ad', 'name']), 'ad_type' => arr_get($row, ['adGroupAd', 'ad', 'type']), 'display_url' => arr_get($row, ['adGroupAd', 'ad', 'displayUrl']), 'final_urls' => arr_get($row, ['adGroupAd', 'ad', 'finalUrls'], []), 'final_mobile_urls' => arr_get($row, ['adGroupAd', 'ad', 'finalMobileUrls'], []), 'tracking_url_template' => arr_get($row, ['adGroupAd', 'ad', 'trackingUrlTemplate']), 'account_id' => $CUSTOMER_ID, 'account_name' => $accountName, ]; } $repo->upsertCreativeDetails($RUN_ID, $CUSTOMER_ID, $out); $payload = export_payload('creatives', $UNTIL, $out); file_put_contents($outputDir . "/google_creatives_{$RUN_ID}.json", json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE)); echo json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE); exit; } /** * 5) INSIGHTS * * Keeping same architecture, but query is cleaned. * No brand filter. */ $query = " SELECT segments.date, campaign.id, campaign.name, campaign.status, ad_group.id, ad_group.name, ad_group.status, ad_group_ad.status, ad_group_ad.ad.id, ad_group_ad.ad.name, metrics.impressions, metrics.clicks, metrics.ctr, metrics.average_cpc, metrics.average_cpm, metrics.cost_micros, metrics.conversions, metrics.cost_per_conversion FROM ad_group_ad WHERE segments.date BETWEEN '{$SINCE}' AND '{$UNTIL}' ORDER BY segments.date ASC "; $rows = $client->searchStream($query); $out = []; foreach ($rows as $row) { $r = [ 'date_start' => arr_get($row, ['segments', 'date']), 'date_stop' => arr_get($row, ['segments', 'date']), 'campaign_id' => arr_get($row, ['campaign', 'id']), 'campaign_name' => arr_get($row, ['campaign', 'name']), 'campaign_status' => arr_get($row, ['campaign', 'status']), 'adgroup_id' => arr_get($row, ['adGroup', 'id']), 'adgroup_name' => arr_get($row, ['adGroup', 'name']), 'adgroup_status' => arr_get($row, ['adGroup', 'status']), 'ad_status' => arr_get($row, ['adGroupAd', 'status']), 'ad_id' => arr_get($row, ['adGroupAd', 'ad', 'id']), 'ad_name' => arr_get($row, ['adGroupAd', 'ad', 'name']), 'currency' => $currency, 'impressions' => to_float(arr_get($row, ['metrics', 'impressions'])), 'clicks' => to_float(arr_get($row, ['metrics', 'clicks'])), 'ctr' => to_float(arr_get($row, ['metrics', 'ctr'])), 'cpc' => micros_to_unit(arr_get($row, ['metrics', 'averageCpc'])), 'cpm' => micros_to_unit(arr_get($row, ['metrics', 'averageCpm'])), 'spend' => micros_to_unit(arr_get($row, ['metrics', 'costMicros'])), 'conversions' => to_float(arr_get($row, ['metrics', 'conversions'])), 'cost_per_conversion' => micros_to_unit(arr_get($row, ['metrics', 'costPerConversion'])), 'video_views' => null, 'average_cpv' => null, 'account_id' => $CUSTOMER_ID, 'account_name' => $accountName, ]; $k = compute_cpl_cpa_cpv($r); $r['cpl'] = $k['cpl']; $r['cpa'] = $k['cpa']; $r['cpv'] = $k['cpv']; apply_multiplier_and_kpis($r, MULTIPLIER); $out[] = $r; } $repo->upsertAdInsightsRows($RUN_ID, $CUSTOMER_ID, $out); $final = []; foreach ($out as $r) { $final[] = [ 'campaign_id' => $r['campaign_id'] ?? null, 'campaign_name' => $r['campaign_name'] ?? null, 'adgroup_id' => $r['adgroup_id'] ?? null, 'adgroup_name' => $r['adgroup_name'] ?? null, 'ad_id' => $r['ad_id'] ?? null, 'ad_name' => $r['ad_name'] ?? null, 'date_start' => $r['date_start'] ?? null, 'date_stop' => $r['date_stop'] ?? null, 'impressions' => $r['impressions'] ?? null, 'clicks' => $r['clicks'] ?? null, 'spend' => $r['spend'] ?? null, 'cpc' => $r['cpc'] ?? null, 'cpm' => $r['cpm'] ?? null, 'ctr' => $r['ctr'] ?? null, 'conversions' => $r['conversions'] ?? null, 'cost_per_conversion' => $r['cost_per_conversion'] ?? null, 'cpl' => $r['cpl'] ?? null, 'cpa' => $r['cpa'] ?? null, ]; } $payload = export_payload('insights', $UNTIL, $final); file_put_contents($outputDir . "/google_insights_{$RUN_ID}.json", json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE)); echo json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE); exit; } catch (Throwable $e) { try { $logger->error('FAILED', [ 'error' => $e->getMessage() ]); } catch (Throwable $ignore) { } http_response_code(500); echo json_encode([ 'ok' => false, 'run_id' => $RUN_ID ?? null, 'error' => $e->getMessage() ], JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE); }