} */ public function run(?string $platform = null, ?string $campaign = null, ?int $limit = null): array { $attempted = 0; $dumped = 0; $failed = 0; $errors = []; foreach ($this->candidates($platform, $campaign, $limit) as $candidate) { $attempted++; try { [$since, $until] = $this->windowFor($candidate); $this->dump($candidate, $since, $until); $this->profiles->sync($candidate); $dumped++; } catch (Throwable $exception) { $failed++; $errors[] = $candidate['external_campaign_id'].': '.$exception->getMessage(); Log::warning('Campaign performance dump failed', [ 'platform' => $candidate['platform'], 'external_campaign_id' => $candidate['external_campaign_id'], 'error' => $exception->getMessage(), ]); $this->recordError($candidate, $exception); } } return compact('attempted', 'dumped', 'failed', 'errors'); } /** * Dump hourly Insights/GAQL. First run backfills from campaign start; later runs refresh the last 3 hours. * * @return array{attempted: int, dumped: int, failed: int, errors: list} */ public function runHourly(?string $platform = null, ?string $campaign = null, ?int $limit = null): array { $attempted = 0; $dumped = 0; $failed = 0; $errors = []; foreach ($this->candidates($platform, $campaign, $limit) as $candidate) { $attempted++; try { $this->dumpHours($candidate); $this->profiles->sync($candidate); $dumped++; } catch (Throwable $exception) { $failed++; $errors[] = $candidate['external_campaign_id'].': '.$exception->getMessage(); Log::warning('Campaign performance hourly dump failed', [ 'platform' => $candidate['platform'], 'external_campaign_id' => $candidate['external_campaign_id'], 'error' => $exception->getMessage(), ]); [, $until] = $this->hourlyRefreshWindow(); $this->recordHourError($candidate, $exception, $until); } } return compact('attempted', 'dumped', 'failed', 'errors'); } /** * @param array{ * platform: string, * user_id: int, * external_campaign_id: string, * local_campaign_id: ?int, * local_google_ads_campaign_id: ?int, * created_at: CarbonInterface * } $candidate * @return array{0: string, 1: string} */ private function windowFor(array $candidate): array { $until = now()->toDateString(); $refreshHours = max(1, (int) config('campaign-performance.daily_lookback_hours', 24)); $maxHistory = max(1, (int) config('campaign-performance.max_history_days', 730)); $floor = now()->subDays($maxHistory - 1)->toDateString(); $platformStart = $this->startedOn($candidate); $earliest = CampaignPerformanceStat::query() ->where('user_id', $candidate['user_id']) ->where('platform', $candidate['platform']) ->where('external_campaign_id', $candidate['external_campaign_id']) ->whereNull('last_error') ->min('stat_date'); $earliestDate = is_string($earliest) && $earliest !== '' ? Carbon::parse($earliest)->toDateString() : null; $needsLifetime = $earliestDate === null || ($platformStart !== null && $earliestDate > $platformStart); if ($needsLifetime) { $since = $platformStart ?? $floor; if ($since < $floor) { $since = $floor; } } else { $since = now()->copy()->subHours($refreshHours)->toDateString(); } if ($since > $until) { $since = $until; } return [$since, $until]; } /** * @param array{ * platform: string, * local_campaign_id: ?int, * local_google_ads_campaign_id: ?int, * external_campaign_id: string * } $candidate */ private function startedOn(array $candidate): ?string { if ($this->readerFor($candidate) === 'google') { $campaign = $candidate['local_google_ads_campaign_id'] ? GoogleAdsCampaign::query()->with('connection')->find($candidate['local_google_ads_campaign_id']) : null; return $campaign !== null ? $this->google->startedOn($campaign) : null; } return $this->meta->startedOn($candidate['external_campaign_id']) ?: ($candidate['local_campaign_id'] ? Campaign::query()->find($candidate['local_campaign_id'])?->created_at?->toDateString() : null); } /** * @return list */ private function candidates(?string $platform, ?string $campaign, ?int $limit): array { $platforms = $this->platforms($platform); $allowlist = $this->allowlist($campaign); $cap = $limit ?? (int) config('campaign-performance.limit', 5); $rows = []; if (in_array('meta', $platforms, true)) { $rows = [...$rows, ...$this->metaCandidates($allowlist)]; } if (in_array('google', $platforms, true)) { $rows = [...$rows, ...$this->googleCandidates($allowlist)]; } usort($rows, function (array $left, array $right): int { $byDate = $left['created_at']->timestamp <=> $right['created_at']->timestamp; return $byDate !== 0 ? $byDate : $left['external_campaign_id'] <=> $right['external_campaign_id']; }); if ($campaign !== null && $campaign !== '') { return $rows; } return array_slice($rows, 0, max(0, $cap)); } /** * @return list */ private function platforms(?string $platform): array { if (is_string($platform) && $platform !== '') { return [strtolower($platform)]; } $configured = config('campaign-performance.platforms', ['meta']); return array_values(array_filter(array_map( fn (mixed $value): string => strtolower(trim((string) $value)), is_array($configured) ? $configured : ['meta'], ))); } /** * @return list */ private function allowlist(?string $campaign): array { if (is_string($campaign) && $campaign !== '') { return [$campaign]; } $configured = config('campaign-performance.external_ids', []); return array_values(array_filter(array_map( trim(...), is_array($configured) ? $configured : [], ))); } /** * @param list $allowlist * @return list */ private function metaCandidates(array $allowlist): array { return Campaign::query() ->where('platform', 'meta') ->whereNotNull('external_campaign_id') ->where('external_campaign_id', '!=', '') ->when($allowlist !== [], fn ($query) => $query->whereIn('external_campaign_id', $allowlist)) ->orderBy('created_at') ->orderBy('id') ->get() ->map(fn (Campaign $campaign): array => [ 'platform' => 'meta', 'user_id' => (int) $campaign->user_id, 'external_campaign_id' => (string) $campaign->external_campaign_id, 'local_campaign_id' => $campaign->id, 'local_google_ads_campaign_id' => null, 'created_at' => $campaign->created_at ?? now(), ]) ->all(); } /** * @param list $allowlist * @return list */ private function googleCandidates(array $allowlist): array { return GoogleAdsCampaign::query() ->with('connection') ->whereNotNull('google_campaign_id') ->where('google_campaign_id', '!=', '') ->when($allowlist !== [], fn ($query) => $query->whereIn('google_campaign_id', $allowlist)) ->orderBy('created_at') ->orderBy('id') ->get() ->map(fn (GoogleAdsCampaign $campaign): array => [ 'platform' => 'google', 'user_id' => (int) $campaign->user_id, 'external_campaign_id' => (string) $campaign->google_campaign_id, 'local_campaign_id' => null, 'local_google_ads_campaign_id' => $campaign->id, 'created_at' => $campaign->created_at ?? now(), ]) ->all(); } /** * @param array{ * platform: string, * user_id: int, * external_campaign_id: string, * local_campaign_id: ?int, * local_google_ads_campaign_id: ?int, * created_at: CarbonInterface * } $candidate */ private function dump(array $candidate, string $since, string $until): void { $days = $this->readerFor($candidate) === 'google' ? $this->googleDays($candidate, $since, $until) : $this->metaDays($candidate, $since, $until); foreach ($days as $day) { $this->upsertDay($candidate, $day); } } /** * @param array{ * platform: string, * user_id: int, * external_campaign_id: string, * local_campaign_id: ?int, * local_google_ads_campaign_id: ?int, * created_at: CarbonInterface * } $candidate */ private function dumpHours(array $candidate): void { $plan = $this->hourlyPlan($candidate); if ($plan['backfill']) { $this->dumpHourlyBackfill($candidate, $plan['since'], $plan['until']); } else { $this->dumpHourlyRefresh($candidate, $plan['since'], $plan['until']); } $this->forgetFailedHourRows($candidate); } /** * @param array{ * platform: string, * user_id: int, * external_campaign_id: string, * local_campaign_id: ?int, * local_google_ads_campaign_id: ?int, * created_at: CarbonInterface * } $candidate * @return array{backfill: bool, since: CarbonInterface, until: CarbonInterface} */ private function hourlyPlan(array $candidate): array { [$refreshSince, $refreshUntil] = $this->hourlyRefreshWindow(); $floor = now()->subDays(PerformanceWindows::MAX_HISTORY_DAYS - 1)->startOfDay(); $platformStart = $this->startedOn($candidate); $earliest = CampaignPerformanceHourStat::query() ->where('platform', $candidate['platform']) ->where('external_campaign_id', $candidate['external_campaign_id']) ->whereNull('last_error') ->min('stat_hour'); $earliestHour = is_string($earliest) && $earliest !== '' ? Carbon::parse($earliest)->startOfHour() : null; $earliestDate = $earliestHour?->toDateString(); $needsLifetime = $earliestDate === null || ($platformStart !== null && $earliestDate > Carbon::parse($platformStart)->toDateString()); if ($needsLifetime) { $since = $platformStart !== null ? Carbon::parse($platformStart)->startOfDay() : $floor->copy(); if ($since->lt($floor)) { $since = $floor->copy(); } $until = now()->copy()->startOfHour(); if ($since->gt($until)) { $since = $until->copy(); } return [ 'backfill' => true, 'since' => $since, 'until' => $until, ]; } return [ 'backfill' => false, 'since' => $refreshSince, 'until' => $refreshUntil, ]; } /** * @param array{ * platform: string, * user_id: int, * external_campaign_id: string, * local_campaign_id: ?int, * local_google_ads_campaign_id: ?int, * created_at: CarbonInterface * } $candidate */ private function dumpHourlyBackfill(array $candidate, CarbonInterface $since, CarbonInterface $until): void { $chunkDays = PerformanceWindows::HOURLY_BACKFILL_CHUNK_DAYS; $cursor = $since->copy()->startOfDay(); $end = $until->copy()->startOfHour(); while ($cursor->lte($end)) { $chunkEndDay = $cursor->copy()->addDays($chunkDays - 1)->endOfDay(); if ($chunkEndDay->gt($end)) { $chunkEndDay = $end->copy(); } $chunkSince = $cursor->copy()->startOfDay(); $chunkUntil = $chunkEndDay->copy()->startOfHour(); if ($chunkSince->lt($since)) { $chunkSince = $since->copy()->startOfHour(); } if ($chunkUntil->gt($until)) { $chunkUntil = $until->copy()->startOfHour(); } $hours = $this->readerFor($candidate) === 'google' ? $this->googleHours($candidate, $chunkSince, $chunkUntil) : $this->metaHours($candidate, $chunkSince, $chunkUntil); $this->upsertHoursInWindow($candidate, $hours, $chunkSince, $chunkUntil); $cursor = $chunkEndDay->copy()->addDay()->startOfDay(); } } /** * @param array{ * platform: string, * user_id: int, * external_campaign_id: string, * local_campaign_id: ?int, * local_google_ads_campaign_id: ?int, * created_at: CarbonInterface * } $candidate */ private function dumpHourlyRefresh(array $candidate, CarbonInterface $since, CarbonInterface $until): void { $hours = $this->readerFor($candidate) === 'google' ? $this->googleHours($candidate, $since, $until) : $this->metaHours($candidate, $since, $until); $this->upsertHoursInWindow($candidate, $hours, $since, $until); } /** * @return array{0: CarbonInterface, 1: CarbonInterface} */ private function hourlyRefreshWindow(): array { $hours = PerformanceWindows::HOURLY_REFRESH_HOURS; $until = now()->copy()->startOfHour(); $since = $until->copy()->subHours($hours - 1); return [$since, $until]; } /** * @param array{ * platform: string, * user_id: int, * external_campaign_id: string, * local_campaign_id: ?int, * local_google_ads_campaign_id: ?int, * created_at: CarbonInterface * } $candidate * @param array $day */ private function upsertDay(array $candidate, array $day): void { $statDate = $this->statDate($day['stat_date']); $stat = $this->statRow($candidate, $statDate); $stat->fill([ 'user_id' => $candidate['user_id'], 'local_campaign_id' => $candidate['local_campaign_id'], 'local_google_ads_campaign_id' => $candidate['local_google_ads_campaign_id'], 'spend' => $day['spend'], 'impressions' => $day['impressions'], 'clicks' => $day['clicks'], 'ctr' => $day['ctr'], 'cpc' => $day['cpc'], 'platform_status' => $day['platform_status'], 'delivery_status' => $day['delivery_status'], 'platform_payload' => $day['platform_payload'], 'fetched_at' => now(), 'last_error' => null, ]); $stat->save(); } /** * @param array{ * platform: string, * user_id: int, * external_campaign_id: string, * local_campaign_id: ?int, * local_google_ads_campaign_id: ?int, * created_at: CarbonInterface * } $candidate * @param array $hour */ private function upsertHour(array $candidate, array $hour): void { $statHour = $this->statHour($hour['stat_hour']); $stat = $this->hourRow($candidate, $statHour); $stat->fill([ 'user_id' => $candidate['user_id'], 'local_campaign_id' => $candidate['local_campaign_id'], 'local_google_ads_campaign_id' => $candidate['local_google_ads_campaign_id'], 'spend' => $hour['spend'], 'impressions' => $hour['impressions'], 'clicks' => $hour['clicks'], 'ctr' => $hour['ctr'], 'cpc' => $hour['cpc'], 'platform_status' => $hour['platform_status'], 'delivery_status' => $hour['delivery_status'], 'platform_payload' => $hour['platform_payload'], 'fetched_at' => now(), 'last_error' => null, ]); $stat->save(); } /** * @param array{ * platform: string, * user_id: int, * external_campaign_id: string, * local_campaign_id: ?int, * local_google_ads_campaign_id: ?int, * created_at: CarbonInterface * } $candidate * @param list> $hours */ private function upsertHoursInWindow(array $candidate, array $hours, CarbonInterface $since, CarbonInterface $until): void { $byHour = []; foreach ($hours as $hour) { $byHour[$this->statHour($hour['stat_hour'])] = $hour; } $status = null; foreach ($hours as $hour) { if (is_string($hour['platform_status'] ?? null) && $hour['platform_status'] !== '') { $status = $hour['platform_status']; break; } } $cursor = $since->copy()->startOfHour(); $end = $until->copy()->startOfHour(); if ($cursor->gt($end)) { $cursor = $end->copy(); } while ($cursor->lte($end)) { $key = $cursor->format('Y-m-d H:i:s'); $row = $byHour[$key] ?? [ 'stat_hour' => $key, 'spend' => 0.0, 'impressions' => 0, 'clicks' => 0, 'ctr' => null, 'cpc' => null, 'platform_status' => $status, 'delivery_status' => null, 'platform_payload' => [], ]; $this->upsertHour($candidate, $row); $cursor->addHour(); } } /** * @param array{ * platform: string, * external_campaign_id: string * } $candidate */ private function forgetFailedHourRows(array $candidate): void { CampaignPerformanceHourStat::query() ->where('platform', $candidate['platform']) ->where('external_campaign_id', $candidate['external_campaign_id']) ->whereNotNull('last_error') ->where('spend', 0) ->where('impressions', 0) ->delete(); } /** * @param array{ * platform: string, * user_id: int, * external_campaign_id: string, * local_campaign_id: ?int, * local_google_ads_campaign_id: ?int, * created_at: CarbonInterface * } $candidate * @return list> */ private function metaDays(array $candidate, string $since, string $until): array { return $this->meta->daysFor($candidate['external_campaign_id'], $since, $until); } /** * @param array{ * platform: string, * user_id: int, * external_campaign_id: string, * local_campaign_id: ?int, * local_google_ads_campaign_id: ?int, * created_at: CarbonInterface * } $candidate * @return list> */ private function metaHours(array $candidate, CarbonInterface $since, CarbonInterface $until): array { return $this->meta->hoursFor($candidate['external_campaign_id'], $since, $until); } /** * @param array{ * platform: string, * user_id: int, * external_campaign_id: string, * local_campaign_id: ?int, * local_google_ads_campaign_id: ?int, * created_at: CarbonInterface * } $candidate * @return list> */ private function googleDays(array $candidate, string $since, string $until): array { return $this->google->days($this->googleCampaign($candidate), $since, $until); } /** * @param array{ * platform: string, * user_id: int, * external_campaign_id: string, * local_campaign_id: ?int, * local_google_ads_campaign_id: ?int, * created_at: CarbonInterface * } $candidate * @return list> */ private function googleHours(array $candidate, CarbonInterface $since, CarbonInterface $until): array { return $this->google->hours($this->googleCampaign($candidate), $since, $until); } /** * @param array{ * platform: string, * local_google_ads_campaign_id: ?int * } $candidate */ private function googleCampaign(array $candidate): GoogleAdsCampaign { $campaign = GoogleAdsCampaign::query() ->with('connection') ->find($candidate['local_google_ads_campaign_id']); if ($campaign === null) { throw new RuntimeException('Local Google campaign row is missing.'); } return $campaign; } /** * @param array{ * platform: string, * user_id: int, * external_campaign_id: string, * local_campaign_id: ?int, * local_google_ads_campaign_id: ?int, * created_at: CarbonInterface * } $candidate */ private function recordError(array $candidate, Throwable $exception): void { $stat = $this->statRow($candidate, $this->statDate(now()->toDateString())); $stat->user_id = $candidate['user_id']; $stat->local_campaign_id = $candidate['local_campaign_id']; $stat->local_google_ads_campaign_id = $candidate['local_google_ads_campaign_id']; $stat->last_error = mb_substr($exception->getMessage(), 0, 2000); $stat->fetched_at = now(); if (! $stat->exists) { $stat->spend = 0; $stat->impressions = 0; $stat->clicks = 0; } $stat->save(); } /** * @param array{ * platform: string, * user_id: int, * external_campaign_id: string, * local_campaign_id: ?int, * local_google_ads_campaign_id: ?int, * created_at: CarbonInterface * } $candidate */ private function recordHourError(array $candidate, Throwable $exception, CarbonInterface $hour): void { $stat = $this->hourRow($candidate, $this->statHour($hour)); $stat->user_id = $candidate['user_id']; $stat->local_campaign_id = $candidate['local_campaign_id']; $stat->local_google_ads_campaign_id = $candidate['local_google_ads_campaign_id']; $stat->last_error = mb_substr($exception->getMessage(), 0, 2000); $stat->fetched_at = now(); if (! $stat->exists) { $stat->spend = 0; $stat->impressions = 0; $stat->clicks = 0; } $stat->save(); } /** * @param array{ * platform: string, * user_id: int, * external_campaign_id: string, * local_campaign_id: ?int, * local_google_ads_campaign_id: ?int, * created_at: CarbonInterface * } $candidate */ private function statRow(array $candidate, string $statDate): CampaignPerformanceStat { $stat = CampaignPerformanceStat::query() ->where('platform', $candidate['platform']) ->where('external_campaign_id', $candidate['external_campaign_id']) ->whereDate('stat_date', $statDate) ->first(); if ($stat !== null) { return $stat; } $stat = new CampaignPerformanceStat; $stat->platform = $candidate['platform']; $stat->external_campaign_id = $candidate['external_campaign_id']; $stat->stat_date = $statDate; return $stat; } /** * @param array{ * platform: string, * user_id: int, * external_campaign_id: string, * local_campaign_id: ?int, * local_google_ads_campaign_id: ?int, * created_at: CarbonInterface * } $candidate */ private function hourRow(array $candidate, string $statHour): CampaignPerformanceHourStat { $stat = CampaignPerformanceHourStat::query() ->where('platform', $candidate['platform']) ->where('external_campaign_id', $candidate['external_campaign_id']) ->where('stat_hour', $statHour) ->first(); if ($stat !== null) { return $stat; } $stat = new CampaignPerformanceHourStat; $stat->platform = $candidate['platform']; $stat->external_campaign_id = $candidate['external_campaign_id']; $stat->stat_hour = $statHour; return $stat; } private function statDate(mixed $value): string { return Carbon::parse((string) $value)->toDateString(); } private function statHour(mixed $value): string { return Carbon::parse((string) $value)->startOfHour()->toDateTimeString(); } }