Automate Google Ads Customer Match List Population Based on Driving Experience, laravel php
The goal is to get data from my submissions table in the database as shown in my code below and upload it to google ads customer match list automatically without having to upload csv files manually with this data. Currently when I ran the command to do upload the data automatically I get this error :
{ "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\u2019s an error.<\/ins>\n <p>The requested URL <code>\/v14\/customers\/{$customerID}\/offlineUserDataJobs:create<\/code> was not found on this server. <ins>That\u2019s all we know.<\/ins>\n", "code": 5, "status": "NOT_FOUND", "details": [] } My current code in my command, since I want it to run in the background every night looks like:
<?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))); } } 