Laravel自动上传Google Ads客户匹配列表遇404错误求助

问题背景 #

从submissions表获取数据，自动上传至Google Ads Customer Match列表，无需手动上传CSV文件。运行Laravel后台命令googleads:bulk-upload时触发404错误，提示请求URL /v14/customers/{$customerID}/offlineUserDataJobs:create不存在。

错误详情 #

{"message": "<!DOCTYPE html>\n<html lang=en>\n  <meta charset=utf-8>\n  <meta name=viewport content=\"initial-scale=1, minimum-scale=1, width=device-width\">\n  <title>Error 404 (Not Found)!!1<\/title>\n  <style>\n    *{margin:0;padding:0}html,code{font:15px\/22px arial,sans-serif}html{background:#fff;color:#222;padding:15px}body{margin:7% auto 0;max-width:390px;min-height:180px;padding:30px 0 15px}* > body{background-url(\\/\\/www.google.com\\/images\\/errors\\/robot.png) 100% 5px no-repeat;padding-right:205px}p{margin:11px 0 22px;overflow:hidden}ins{color:#777;text-decoration:none}a img{border:0}@media screen and (max-width:772px){body{background:none;margin-top:0;max-width:none;padding-right:0}}#logo{background-url(\\/\\/www.google.com\\/images\\/branding\\/googlelogo\\/1x\\/googlelogo_color_150x54dp.png) no-repeat;margin-left:-5px}@media only screen and (min-resolution:192dpi){#logo{background-url(\\/\\/www.google.com\\/images\\/branding\\/googlelogo\\/2x\\/googlelogo_color_150x54dp.png) no-repeat 0% 0%\\/100% 100%;-moz-border-image:url(\\/\\/www.google.com\\/images\\/branding\\/googlelogo\\/2x\\/googlelogo_color_150x54dp.png) 0}}@media only screen and (-webkit-min-device-pixel-ratio:2){#logo{background-url(\\/\\/www.google.com\\/images\\/branding\\/googlelogo\\/2x\\/googlelogo_color_150x54dp.png) no-repeat;-webkit-background-size:100% 100%}}#logo{display:inline-block;height:54px;width:150px}\n  <\/style>\n  <a href=\\/\\/www.google.com\\/><span id=logo aria-label=Google><\/span><\/a>\n  <p><b>404.<\/b> <ins>That’s an error.<\/ins>\n  <p>The requested URL <code>\/v14\/customers\/{$customerID}\/offlineUserDataJobs:create<\/code> was not found on this server.  <ins>That’s all we know.<\/ins>\n",
"code": 5,
"status": "NOT_FOUND",
"details": []
}

当前Laravel命令代码 #

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;
use Carbon\Carbon;
use Google\Auth\OAuth2;
use Google\Ads\GoogleAds\Lib\V14\GoogleAdsClientBuilder;
use Google\Ads\GoogleAds\Util\V14\ResourceNames;
use Google\Ads\GoogleAds\V14\Services\UserDataOperation;
use Google\Ads\GoogleAds\V14\Enums\OfflineUserDataJobTypeEnum\OfflineUserDataJobType;
use Google\Ads\GoogleAds\V14\Common\UserIdentifier;
use Google\Ads\GoogleAds\V14\Common\OfflineUserAddressInfo;
use Google\Ads\GoogleAds\V14\Common\UserData;
use Google\Ads\GoogleAds\V14\Services\OfflineUserDataJobOperation;

class BulkUploadGoogleAds extends Command
{
    protected $signature = 'googleads:bulk-upload';
    protected $description = 'Fetch today\'s leads, create CSVs, and upload to Google Ads Customer Match lists';

    public function handle()
    {
        $this->info('Fetching today\'s leads and preparing CSVs...');

        // Fetch today's leads from submissions table
        $leads = DB::table('submissions')
            ->select(
                DB::raw("JSON_UNQUOTE(JSON_EXTRACT(applicant_info, '$.email')) as email"),
                DB::raw("JSON_UNQUOTE(JSON_EXTRACT(applicant_info, '$.phone')) as phone"),
                DB::raw("JSON_UNQUOTE(JSON_EXTRACT(applicant_info, '$.first_name')) as first_name"),
                DB::raw("JSON_UNQUOTE(JSON_EXTRACT(applicant_info, '$.last_name')) as last_name"),
                DB::raw("'US' as country"),
                DB::raw("JSON_UNQUOTE(JSON_EXTRACT(applicant_info, '$.zip_code')) as zip"),
                DB::raw("JSON_UNQUOTE(JSON_EXTRACT(form_fields, '$.experience')) as experience")
            )
            ->where('is_qualified', 1)
            ->whereDate('created_at', Carbon::today())
            ->get();

        if ($leads->isEmpty()) {
            $this->info('No leads found for today.');
            return;
        }

        // Customer Match list IDs for driving experience
        $experienceLists = [
            '0'  => '8667734072',
            '3'  => '8667763201',
            '6'  => '8667739115',
            '12' => '8667741140',
            '24' => '8668576641',
        ];

        // Generate and upload CSVs for each list
        foreach ($experienceLists as $experience => $listId) {
            // Filter leads by experience level
            $filteredLeads = $leads->filter(fn($lead) => $this->matchesExperience($lead->experience, $experience));

            if ($filteredLeads->isEmpty()) {
                $this->info("No leads for experience level {$experience}. Skipping...");
                continue;
            }

            // Upload directly to Google Ads
            $this->uploadToGoogleAds($filteredLeads, $listId);
        }

        $this->info('Bulk upload to Google Ads completed successfully.');
    }

    private function matchesExperience($userExp, $criteria)
    {
        $userExp = (int)$userExp;
        switch ($criteria) {
            case '0': return $userExp == 0;
            case '3': return $userExp >= 3 && $userExp < 6;
            case '6': return $userExp >= 6 && $userExp < 12;
            case '12': return $userExp >= 12 && $userExp < 24;
            case '24': return $userExp >= 24;
            default: return false;
        }
    }

    private function uploadToGoogleAds($leads, $listId)
    {
        $this->info("Uploading data to Google Ads list ID: {$listId}");

        $iniConfig = parse_ini_file(base_path('config/google_ads.ini'), true)['GOOGLE_ADS'];

        // Build the OAuth2 credentials and Google Ads client
        $oauth2 = new \Google\Auth\Credentials\UserRefreshCredentials(
            'https://www.googleapis.com/auth/adwords',
            [
                'client_id' => $iniConfig['clientId'],
                'client_secret' => $iniConfig['clientSecret'],
                'refresh_token' => $iniConfig['refreshToken'],
            ]
        );

        $googleAdsClient = (new GoogleAdsClientBuilder())
            ->withDeveloperToken($iniConfig['developerToken'])
            ->withOAuth2Credential($oauth2)
            ->withLoginCustomerId($iniConfig['loginCustomerId'])
            ->build();

        $offlineUserDataJobService = $googleAdsClient->getOfflineUserDataJobServiceClient();

        $userDataList = [];
        foreach ($leads as $lead) {
            $userIdentifiers = [];

            if (!empty($lead->email)) {
                $userIdentifiers[] = new UserIdentifier([
                    'hashed_email' => $this->normalizeAndHash($lead->email)
                ]);
            }

            if (!empty($lead->phone)) {
                $userIdentifiers[] = new UserIdentifier([
                    'hashed_phone_number' => $this->normalizeAndHash($lead->phone)
                ]);
            }

            if (!empty($lead->first_name) && !empty($lead->last_name) && !empty($lead->country) && !empty($lead->zip)) {
                $userIdentifiers[] = new UserIdentifier([
                    'address_info' => new OfflineUserAddressInfo([
                        'hashed_first_name' => $this->normalizeAndHash($lead->first_name),
                        'hashed_last_name' => $this->normalizeAndHash($lead->last_name),
                        'country_code' => $lead->country,
                        'postal_code' => $lead->zip,
                    ])
                ]);
            }

            if (!empty($userIdentifiers)) {
                $userDataList[] = new UserData(['user_identifiers' => $userIdentifiers]);
            }
        }

        if (empty($userDataList)) {
            $this->info("No valid user data for list ID: {$listId}. Skipping...");
            return;
        }

        $operations = array_map(fn(UserData $userData) => new OfflineUserDataJobOperation(['create' => $userData]), $userDataList);

        $offlineUserDataJob = new \Google\Ads\GoogleAds\V14\Resources\OfflineUserDataJob([
            'type' => OfflineUserDataJobType::CUSTOMER_MATCH_USER_LIST,
            'resource_name' => ResourceNames::forUserList(
                $iniConfig['loginCustomerId'],
                $listId
            ),
        ]);

        $jobResponse = $offlineUserDataJobService->createOfflineUserDataJob(
            $iniConfig['loginCustomerId'],
            $offlineUserDataJob
        );

        $jobResourceName = $jobResponse->getResourceName();
        $offlineUserDataJobService->addOfflineUserDataJobOperations($jobResourceName, $operations);
        $offlineUserDataJobService->runOfflineUserDataJob($jobResourceName);

        $this->info("Job successfully submitted for list ID: {$listId}");
    }

    private function normalizeAndHash($value)
    {
        return hash('sha256', strtolower(trim($value)));
    }
}

问题原因与修复方案 #

核心问题 #

代码中创建OfflineUserDataJob时，错误地预先设置了resource_name字段。根据Google Ads API规范，创建离线用户数据任务时，任务的resource_name由API自动生成，不能手动指定。手动设置该字段会导致API请求路径格式错误，触发404。

修复步骤 #

修改uploadToGoogleAds方法中创建OfflineUserDataJob的代码，移除错误的resource_name设置，并通过customer_match_user_list_metadata指定目标用户列表：

$offlineUserDataJob = new \Google\Ads\GoogleAds\V14\Resources\OfflineUserDataJob([
    'type' => OfflineUserDataJobType::CUSTOMER_MATCH_USER_LIST,
    'customer_match_user_list_metadata' => [
        'user_list' => ResourceNames::forUserList(
            $iniConfig['loginCustomerId'],
            $listId
        )
    ]
]);

补充说明 #

• 对于CUSTOMER_MATCH_USER_LIST类型的任务，必须通过customer_match_user_list_metadata.user_list指定目标用户列表的resource_name，而非任务自身的resource_name。
• 确保Google Ads Client Library版本与API版本（v14）完全兼容，避免版本不匹配导致的路径解析错误。
• 检查loginCustomerId为纯数字格式，不要包含连字符等特殊字符，否则会导致请求路径格式异常。

内容的提问来源于stack exchange，提问作者Mageto Denis