22

✏️ Editing: xgoogle.php

<?php
/**
 * Google Ads Insights Script (Production Ready)
 * - Batching
 * - Retry logic
 * - Flat JSON output
 */

/* ================= CONFIG ================= */

$CLIENT_ID     = '858812719156-ou2sg114mil1scddjisvik6f0skqbiim.apps.googleusercontent.com';
$CLIENT_SECRET = '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';
$REFRESH_TOKEN = '1//03kMGkkGP1bYaCgYIARAAGAMSNwF-L9IrCixnND5k_BLomX9CNObTF8X1slBl816MmjUWHWU_HfpM-8T3GxvFTa_FvC8qi7JW6Z4';
$DEVELOPER_TOKEN = 'ya29.a0Aa7MYiqe2TrXsg7a8eorcQ5YhSuPE6caY-bSyUmzLVvMv3KhEbaBPxJeF_4d4Dd2zMfNcVxIc0ZvEkXkvRYTSuSWWL0E8GAlqZXFMwSdK0aWNuZA-EWlx0eerSsI2KnSyOTJQ6Oq8vaRup7m58kvmmx0JOGQ-oOSWBGoxbIyzMdbMvzTrMDdy6D5E5kkL5kZm_jplMwaCgYKAXISARUSFQHGX2MixL9103fY8vSMj_5zzGisFg0206';
$CUSTOMER_ID   = '4934124696'; // no dashes
$BRAND_FILTER  = 'honda'; // change if needed

$SINCE = '2026-01-01';
$UNTIL = date('Y-m-d');

$snapDir = __DIR__ . '/snapshots';
if (!is_dir($snapDir)) mkdir($snapDir, 0777, true);

/* ================= HELPERS ================= */

function httpPost($url, $data) {
    $ch = curl_init($url);
    curl_setopt_array($ch, [
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_POST => true,
        CURLOPT_POSTFIELDS => http_build_query($data),
    ]);
    $res = curl_exec($ch);
    if ($res === false) throw new Exception(curl_error($ch));
    return json_decode($res, true);
}

function getAccessToken($clientId, $clientSecret, $refreshToken) {
    $resp = httpPost('https://oauth2.googleapis.com/token', [
        'client_id'     => $clientId,
        'client_secret' => $clientSecret,
        'refresh_token' => $refreshToken,
        'grant_type'    => 'refresh_token',
    ]);

if (empty($resp['access_token'])) {
        throw new Exception("Failed to get access token");
    }

return $resp['access_token'];
}

function googleAdsQuery($accessToken, $query, $developerToken, $customerId) {
    $url = "https://googleads.googleapis.com/v17/customers/{$customerId}/googleAds:searchStream";

$headers = [
        "Authorization: Bearer {$accessToken}",
        "developer-token: {$developerToken}",
        "Content-Type: application/json"
    ];

$body = json_encode(['query' => $query]);

$ch = curl_init($url);
    curl_setopt_array($ch, [
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_POST => true,
        CURLOPT_HTTPHEADER => $headers,
        CURLOPT_POSTFIELDS => $body,
    ]);

$res = curl_exec($ch);
    if ($res === false) throw new Exception(curl_error($ch));

$http = curl_getinfo($ch, CURLINFO_HTTP_CODE);

if ($http >= 400) {
        throw new Exception("Google Ads API HTTP {$http}: {$res}");
    }

return json_decode($res, true);
}

function microsToCurrency($value) {
    return $value !== null ? $value / 1000000 : null;
}

/* ================= AUTH ================= */

$accessToken = getAccessToken($CLIENT_ID, $CLIENT_SECRET, $REFRESH_TOKEN);

/* ================= QUERY TEMPLATE ================= */

$queryTemplate = "
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 campaign.name LIKE '%{$BRAND_FILTER}%'
AND segments.date BETWEEN '%SINCE%' AND '%UNTIL%'
ORDER BY segments.date ASC
";

/* ================= BATCHING ================= */

$rows = [];

$start = new DateTime($SINCE);
$end   = new DateTime($UNTIL);

while ($start <= $end) {

$batchEnd = clone $start;
    $batchEnd->modify('+10 days');

if ($batchEnd > $end) {
        $batchEnd = $end;
    }

$since = $start->format('Y-m-d');
    $until = $batchEnd->format('Y-m-d');

$query = str_replace(
        ['%SINCE%', '%UNTIL%'],
        [$since, $until],
        $queryTemplate
    );

$resp = safeQuery($accessToken, $query, $DEVELOPER_TOKEN, $CUSTOMER_ID);

foreach ($resp as $chunk) {
        if (!empty($chunk['results'])) {
            $rows = array_merge($rows, $chunk['results']);
        }
    }

$start->modify('+11 days');
}

/* ================= PROCESS ================= */

$out = [];

foreach ($rows as $row) {

$metrics = $row['metrics'] ?? [];

$spend = microsToCurrency($metrics['costMicros'] ?? null);
    $clicks = $metrics['clicks'] ?? null;
    $impr   = $metrics['impressions'] ?? null;
    $conv   = $metrics['conversions'] ?? null;

$cpc = ($spend && $clicks) ? $spend / $clicks : null;
    $cpm = ($spend && $impr) ? ($spend / $impr) * 1000 : null;
    $cpl = ($spend && $conv) ? $spend / $conv : null;

$out[] = [
        'source_system'   => 'google_ads',
        'data_type'       => 'insights',
        'data_date'       => $row['segments']['date'] ?? null,
        'account_id'      => $CUSTOMER_ID,

'campaign_id'     => $row['campaign']['id'] ?? null,
        'campaign_name'   => $row['campaign']['name'] ?? null,
        'campaign_status' => $row['campaign']['status'] ?? null,

'adgroup_id'      => $row['adGroup']['id'] ?? null,
        'adgroup_name'    => $row['adGroup']['name'] ?? null,

'ad_id'           => $row['adGroupAd']['ad']['id'] ?? null,
        'ad_name'         => $row['adGroupAd']['ad']['name'] ?? null,

'impressions'     => $impr,
        'clicks'          => $clicks,
        'spend'           => $spend,
        'ctr'             => $metrics['ctr'] ?? null,
        'cpc'             => $cpc,
        'cpm'             => $cpm,
        'conversions'     => $conv,
        'cost_per_conversion' => microsToCurrency($metrics['costPerConversion'] ?? null),
        'cpl'             => $cpl
    ];
}

/* ================= OUTPUT ================= */

file_put_contents(
    $snapDir . "/google_insights.json",
    json_encode(array_values($out), JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE)
);

echo json_encode(array_values($out), JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
exit;

MR SKB