Spaces:
Running
Running
File size: 3,916 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 87 88 89 90 91 92 93 94 95 96 97 98 99 | <?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_funnel_progress rows started on a single calendar date
* into analytics_daily_funnels.
*
* Safe to re-run: upsert on (summary_date, module) replaces existing counts.
*
* Schedule: daily at 02:15 UTC (see bootstrap/app.php →withSchedule).
* Manual backfill: php artisan mdn:analytics:rollup-funnels --date=2026-07-10
*/
class RollupAnalyticsFunnels extends Command
{
protected $signature = 'mdn:analytics:rollup-funnels
{--date= : Date to aggregate (YYYY-MM-DD). Defaults to yesterday.}';
protected $description = 'Roll up funnel_progress rows into analytics_daily_funnels.';
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 funnel progress for {$date} …");
$rows = DB::table('analytics_funnel_progress')
->selectRaw("
DATE(started_at) AS summary_date,
module,
COUNT(*) AS starts,
SUM(IF(highest_step >= 0, 1, 0)) AS step_0_reached,
SUM(IF(highest_step >= 1, 1, 0)) AS step_1_reached,
SUM(IF(highest_step >= 2, 1, 0)) AS step_2_reached,
SUM(IF(highest_step >= 3, 1, 0)) AS step_3_reached,
SUM(IF(highest_step >= 4, 1, 0)) AS step_4_reached,
SUM(IF(highest_step >= 5, 1, 0)) AS step_5_reached,
SUM(IF(highest_step >= 6, 1, 0)) AS step_6_reached,
SUM(submitted) AS submissions,
SUM(abandoned) AS abandonments
")
->whereDate('started_at', $date)
->groupByRaw('DATE(started_at), module')
->get();
if ($rows->isEmpty()) {
$this->line(" No funnel_progress rows 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,
'module' => $r->module,
'starts' => (int) $r->starts,
'step_0_reached' => (int) $r->step_0_reached,
'step_1_reached' => (int) $r->step_1_reached,
'step_2_reached' => (int) $r->step_2_reached,
'step_3_reached' => (int) $r->step_3_reached,
'step_4_reached' => (int) $r->step_4_reached,
'step_5_reached' => (int) $r->step_5_reached,
'step_6_reached' => (int) $r->step_6_reached,
'submissions' => (int) $r->submissions,
'abandonments' => (int) $r->abandonments,
'created_at' => $now,
'updated_at' => $now,
])->all();
DB::table('analytics_daily_funnels')->upsert(
$upsertRows,
['summary_date', 'module'],
[
'starts', 'step_0_reached', 'step_1_reached', 'step_2_reached',
'step_3_reached', 'step_4_reached', 'step_5_reached', 'step_6_reached',
'submissions', 'abandonments', 'updated_at',
],
);
$this->info(" Done. {$rows->count()} module(s) written for {$date}.");
return self::SUCCESS;
}
}
|