104 lines
4.9 KiB
PHP
104 lines
4.9 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace App\Console\Commands;
|
|
|
|
use Illuminate\Console\Command;
|
|
use Illuminate\Support\Facades\DB;
|
|
use Illuminate\Support\Facades\Schema;
|
|
|
|
final class AggregateDiscoveryFeedbackCommand extends Command
|
|
{
|
|
protected $signature = 'analytics:aggregate-discovery-feedback {--date= : Date (Y-m-d), defaults to yesterday}';
|
|
|
|
protected $description = 'Aggregate discovery feedback events into daily metrics by algorithm and surface';
|
|
|
|
public function handle(): int
|
|
{
|
|
if (! Schema::hasTable('user_discovery_events') || ! Schema::hasTable('discovery_feedback_daily_metrics')) {
|
|
$this->warn('Required discovery feedback tables are missing.');
|
|
return self::SUCCESS;
|
|
}
|
|
|
|
$date = $this->option('date')
|
|
? (string) $this->option('date')
|
|
: now()->subDay()->toDateString();
|
|
|
|
$surfaceExpression = $this->surfaceExpression();
|
|
|
|
$rows = DB::table('user_discovery_events')
|
|
->selectRaw('algo_version')
|
|
->selectRaw($surfaceExpression . ' AS surface')
|
|
->selectRaw("SUM(CASE WHEN event_type = 'view' THEN 1 ELSE 0 END) AS views")
|
|
->selectRaw("SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END) AS clicks")
|
|
->selectRaw("SUM(CASE WHEN event_type = 'favorite' THEN 1 ELSE 0 END) AS favorites")
|
|
->selectRaw("SUM(CASE WHEN event_type = 'download' THEN 1 ELSE 0 END) AS downloads")
|
|
->selectRaw("SUM(CASE WHEN event_type = 'hide_artwork' THEN 1 ELSE 0 END) AS hidden_artworks")
|
|
->selectRaw("SUM(CASE WHEN event_type = 'dislike_tag' THEN 1 ELSE 0 END) AS disliked_tags")
|
|
->selectRaw("SUM(CASE WHEN event_type = 'unhide_artwork' THEN 1 ELSE 0 END) AS undo_hidden_artworks")
|
|
->selectRaw("SUM(CASE WHEN event_type = 'undo_dislike_tag' THEN 1 ELSE 0 END) AS undo_disliked_tags")
|
|
->selectRaw('COUNT(DISTINCT user_id) AS unique_users')
|
|
->selectRaw('COUNT(DISTINCT artwork_id) AS unique_artworks')
|
|
->whereDate('event_date', $date)
|
|
->groupBy('algo_version', DB::raw($surfaceExpression))
|
|
->get();
|
|
|
|
foreach ($rows as $row) {
|
|
$views = (int) ($row->views ?? 0);
|
|
$clicks = (int) ($row->clicks ?? 0);
|
|
$favorites = (int) ($row->favorites ?? 0);
|
|
$downloads = (int) ($row->downloads ?? 0);
|
|
$feedbackActions = $favorites + $downloads;
|
|
$hiddenArtworks = (int) ($row->hidden_artworks ?? 0);
|
|
$dislikedTags = (int) ($row->disliked_tags ?? 0);
|
|
$undoHiddenArtworks = (int) ($row->undo_hidden_artworks ?? 0);
|
|
$undoDislikedTags = (int) ($row->undo_disliked_tags ?? 0);
|
|
$negativeFeedbackActions = $hiddenArtworks + $dislikedTags;
|
|
$undoActions = $undoHiddenArtworks + $undoDislikedTags;
|
|
|
|
DB::table('discovery_feedback_daily_metrics')->updateOrInsert(
|
|
[
|
|
'metric_date' => $date,
|
|
'algo_version' => (string) ($row->algo_version ?? ''),
|
|
'surface' => (string) ($row->surface ?? 'unknown'),
|
|
],
|
|
[
|
|
'views' => $views,
|
|
'clicks' => $clicks,
|
|
'favorites' => $favorites,
|
|
'downloads' => $downloads,
|
|
'hidden_artworks' => $hiddenArtworks,
|
|
'disliked_tags' => $dislikedTags,
|
|
'undo_hidden_artworks' => $undoHiddenArtworks,
|
|
'undo_disliked_tags' => $undoDislikedTags,
|
|
'feedback_actions' => $feedbackActions,
|
|
'negative_feedback_actions' => $negativeFeedbackActions,
|
|
'undo_actions' => $undoActions,
|
|
'unique_users' => (int) ($row->unique_users ?? 0),
|
|
'unique_artworks' => (int) ($row->unique_artworks ?? 0),
|
|
'ctr' => $views > 0 ? $clicks / $views : 0.0,
|
|
'favorite_rate_per_click' => $clicks > 0 ? $favorites / $clicks : 0.0,
|
|
'download_rate_per_click' => $clicks > 0 ? $downloads / $clicks : 0.0,
|
|
'feedback_rate_per_click' => $clicks > 0 ? $feedbackActions / $clicks : 0.0,
|
|
'updated_at' => now(),
|
|
'created_at' => now(),
|
|
]
|
|
);
|
|
}
|
|
|
|
$this->info("Aggregated discovery feedback for {$date}.");
|
|
|
|
return self::SUCCESS;
|
|
}
|
|
|
|
private function surfaceExpression(): string
|
|
{
|
|
if (DB::connection()->getDriverName() === 'sqlite') {
|
|
return "COALESCE(NULLIF(JSON_EXTRACT(meta, '$.gallery_type'), ''), NULLIF(JSON_EXTRACT(meta, '$.surface'), ''), 'unknown')";
|
|
}
|
|
|
|
return "COALESCE(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(meta, '$.gallery_type')), ''), NULLIF(JSON_UNQUOTE(JSON_EXTRACT(meta, '$.surface')), ''), 'unknown')";
|
|
}
|
|
}
|