Spaces:
Running
Running
File size: 3,059 Bytes
45dc401 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 | <?php
declare(strict_types=1);
namespace App\Console\Commands;
use App\Infrastructure\Ids\UuidGenerator;
use Illuminate\Console\Command;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;
/**
* Aggregates analytics_events page_view rows for a single calendar date
* into analytics_daily_page_views.
*
* Safe to re-run: upsert on (summary_date, path) replaces existing counts.
* Excludes events with bot_score >= 80 (same threshold as the ingestion discard).
*
* Schedule: daily at 02:00 UTC (see bootstrap/app.php →withSchedule).
* Manual backfill: php artisan mdn:analytics:rollup-page-views --date=2026-07-10
*/
class RollupAnalyticsPageViews extends Command
{
protected $signature = 'mdn:analytics:rollup-page-views
{--date= : Date to aggregate (YYYY-MM-DD). Defaults to yesterday.}';
protected $description = 'Roll up page_view events into analytics_daily_page_views.';
public function __construct(private readonly UuidGenerator $ids)
{
parent::__construct();
}
public function handle(): int
{
$date = $this->option('date')
? Carbon::parse($this->option('date'))->toDateString()
: Carbon::yesterday()->toDateString();
$this->line("Aggregating page views for {$date} …");
$rows = DB::table('analytics_events')
->selectRaw("
DATE(occurred_at) AS summary_date,
COALESCE(path, '/') AS path,
module,
COUNT(*) AS page_views,
COUNT(DISTINCT visitor_id) AS unique_visitors,
COUNT(DISTINCT session_id) AS sessions
")
->where('event_type', 'page_view')
->where('bot_score', '<', 80)
->whereDate('occurred_at', $date)
->groupByRaw("DATE(occurred_at), COALESCE(path, '/'), module")
->get();
if ($rows->isEmpty()) {
$this->line(" No page_view events for {$date} — nothing to write.");
return self::SUCCESS;
}
$now = Carbon::now()->toDateTimeString();
$upsertRows = $rows->map(fn (object $r) => [
'id' => $this->ids->generate(),
'summary_date' => $r->summary_date,
'path' => $r->path,
'module' => $r->module,
'page_views' => (int) $r->page_views,
'unique_visitors' => (int) $r->unique_visitors,
'sessions' => (int) $r->sessions,
'created_at' => $now,
'updated_at' => $now,
])->all();
DB::table('analytics_daily_page_views')->upsert(
$upsertRows,
['summary_date', 'path'],
['page_views', 'unique_visitors', 'sessions', 'updated_at'],
);
$this->info(" Done. {$rows->count()} path/module combinations written for {$date}.");
return self::SUCCESS;
}
}
|