873 lines
40 KiB
PHP
873 lines
40 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace App\Services\Analytics;
|
|
|
|
use Illuminate\Support\Facades\DB;
|
|
use Illuminate\Support\Facades\Schema;
|
|
|
|
final class DiscoveryFeedbackReportService
|
|
{
|
|
public function buildReport(string $from, string $to, int $limit = 20): array
|
|
{
|
|
if (! Schema::hasTable('user_discovery_events')) {
|
|
return [
|
|
'overview' => $this->emptyOverview(),
|
|
'daily_feedback' => [],
|
|
'trend_summary' => $this->emptyTrendSummary(),
|
|
'by_surface' => [],
|
|
'by_algo_surface' => [],
|
|
'top_artworks' => [],
|
|
];
|
|
}
|
|
|
|
$dailyFeedback = $this->dailyFeedback($from, $to);
|
|
$trendSummary = $this->trendSummary($dailyFeedback);
|
|
$surfaceTrendMap = $this->surfaceTrendMap($from, $to);
|
|
$bySurface = $this->attachSurfaceTrendMap($this->bySurface($from, $to), $surfaceTrendMap);
|
|
$algoSurfaceTrendMap = $this->algoSurfaceTrendMap($from, $to);
|
|
$byAlgoSurface = $this->attachAlgoSurfaceTrendMap($this->byAlgoSurface($from, $to), $algoSurfaceTrendMap);
|
|
|
|
return [
|
|
'overview' => $this->overview($from, $to),
|
|
'daily_feedback' => $dailyFeedback,
|
|
'trend_summary' => $trendSummary,
|
|
'by_surface' => $bySurface,
|
|
'by_algo_surface' => $byAlgoSurface,
|
|
'top_artworks' => $this->topArtworks($from, $to, $limit),
|
|
'latest_aggregated_date' => $this->latestAggregatedDate(),
|
|
];
|
|
}
|
|
|
|
private function overview(string $from, string $to): array
|
|
{
|
|
$row = DB::table('user_discovery_events')
|
|
->selectRaw('COUNT(*) AS total_events')
|
|
->selectRaw('COUNT(DISTINCT user_id) AS unique_users')
|
|
->selectRaw('COUNT(DISTINCT artwork_id) AS unique_artworks')
|
|
->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")
|
|
->whereBetween('event_date', [$from, $to])
|
|
->first();
|
|
|
|
$views = (int) ($row->views ?? 0);
|
|
$clicks = (int) ($row->clicks ?? 0);
|
|
$favorites = (int) ($row->favorites ?? 0);
|
|
$downloads = (int) ($row->downloads ?? 0);
|
|
$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);
|
|
$feedbackActions = $favorites + $downloads;
|
|
$negativeFeedbackActions = $hiddenArtworks + $dislikedTags;
|
|
$undoActions = $undoHiddenArtworks + $undoDislikedTags;
|
|
|
|
return [
|
|
'total_events' => (int) ($row->total_events ?? 0),
|
|
'unique_users' => (int) ($row->unique_users ?? 0),
|
|
'unique_artworks' => (int) ($row->unique_artworks ?? 0),
|
|
'views' => $views,
|
|
'clicks' => $clicks,
|
|
'favorites' => $favorites,
|
|
'downloads' => $downloads,
|
|
'feedback_actions' => $feedbackActions,
|
|
'hidden_artworks' => $hiddenArtworks,
|
|
'disliked_tags' => $dislikedTags,
|
|
'negative_feedback_actions' => $negativeFeedbackActions,
|
|
'undo_hidden_artworks' => $undoHiddenArtworks,
|
|
'undo_disliked_tags' => $undoDislikedTags,
|
|
'undo_actions' => $undoActions,
|
|
'ctr' => round($views > 0 ? $clicks / $views : 0.0, 6),
|
|
'favorite_rate_per_click' => round($clicks > 0 ? $favorites / $clicks : 0.0, 6),
|
|
'download_rate_per_click' => round($clicks > 0 ? $downloads / $clicks : 0.0, 6),
|
|
'feedback_rate_per_click' => round($clicks > 0 ? $feedbackActions / $clicks : 0.0, 6),
|
|
'negative_feedback_rate_per_click' => round($clicks > 0 ? $negativeFeedbackActions / $clicks : 0.0, 6),
|
|
'undo_rate_per_negative_feedback' => round($negativeFeedbackActions > 0 ? $undoActions / $negativeFeedbackActions : 0.0, 6),
|
|
];
|
|
}
|
|
|
|
private function bySurface(string $from, string $to): array
|
|
{
|
|
if (Schema::hasTable('discovery_feedback_daily_metrics')) {
|
|
return DB::table('discovery_feedback_daily_metrics')
|
|
->selectRaw('surface')
|
|
->selectRaw('SUM(views) AS views')
|
|
->selectRaw('SUM(clicks) AS clicks')
|
|
->selectRaw('SUM(favorites) AS favorites')
|
|
->selectRaw('SUM(downloads) AS downloads')
|
|
->selectRaw('SUM(feedback_actions) AS feedback_actions')
|
|
->selectRaw('SUM(hidden_artworks) AS hidden_artworks')
|
|
->selectRaw('SUM(disliked_tags) AS disliked_tags')
|
|
->selectRaw('SUM(negative_feedback_actions) AS negative_feedback_actions')
|
|
->selectRaw('SUM(undo_hidden_artworks) AS undo_hidden_artworks')
|
|
->selectRaw('SUM(undo_disliked_tags) AS undo_disliked_tags')
|
|
->selectRaw('SUM(undo_actions) AS undo_actions')
|
|
->selectRaw('SUM(unique_users) AS unique_users')
|
|
->selectRaw('SUM(unique_artworks) AS unique_artworks')
|
|
->whereBetween('metric_date', [$from, $to])
|
|
->groupBy('surface')
|
|
->orderByDesc('clicks')
|
|
->orderByDesc('favorites')
|
|
->get()
|
|
->map(fn ($row): array => $this->formatEventSummaryRow($row, ['surface' => (string) ($row->surface ?? 'unknown')]))
|
|
->all();
|
|
}
|
|
|
|
$surfaceExpression = $this->surfaceExpression();
|
|
|
|
return DB::table('user_discovery_events')
|
|
->selectRaw($surfaceExpression . ' AS surface')
|
|
->selectRaw('COUNT(*) AS total_events')
|
|
->selectRaw('COUNT(DISTINCT user_id) AS unique_users')
|
|
->selectRaw('COUNT(DISTINCT artwork_id) AS unique_artworks')
|
|
->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")
|
|
->whereBetween('event_date', [$from, $to])
|
|
->groupBy(DB::raw($surfaceExpression))
|
|
->orderByDesc('clicks')
|
|
->orderByDesc('favorites')
|
|
->get()
|
|
->map(fn ($row): array => $this->formatEventSummaryRow($row, ['surface' => (string) ($row->surface ?? 'unknown')]))
|
|
->all();
|
|
}
|
|
|
|
private function byAlgoSurface(string $from, string $to): array
|
|
{
|
|
if (Schema::hasTable('discovery_feedback_daily_metrics')) {
|
|
return DB::table('discovery_feedback_daily_metrics')
|
|
->selectRaw('algo_version')
|
|
->selectRaw('surface')
|
|
->selectRaw('SUM(views) AS views')
|
|
->selectRaw('SUM(clicks) AS clicks')
|
|
->selectRaw('SUM(favorites) AS favorites')
|
|
->selectRaw('SUM(downloads) AS downloads')
|
|
->selectRaw('SUM(feedback_actions) AS feedback_actions')
|
|
->selectRaw('SUM(hidden_artworks) AS hidden_artworks')
|
|
->selectRaw('SUM(disliked_tags) AS disliked_tags')
|
|
->selectRaw('SUM(negative_feedback_actions) AS negative_feedback_actions')
|
|
->selectRaw('SUM(undo_hidden_artworks) AS undo_hidden_artworks')
|
|
->selectRaw('SUM(undo_disliked_tags) AS undo_disliked_tags')
|
|
->selectRaw('SUM(undo_actions) AS undo_actions')
|
|
->selectRaw('SUM(unique_users) AS unique_users')
|
|
->selectRaw('SUM(unique_artworks) AS unique_artworks')
|
|
->whereBetween('metric_date', [$from, $to])
|
|
->groupBy('algo_version', 'surface')
|
|
->orderBy('algo_version')
|
|
->orderByDesc('clicks')
|
|
->get()
|
|
->map(fn ($row): array => $this->formatEventSummaryRow($row, [
|
|
'algo_version' => (string) ($row->algo_version ?? ''),
|
|
'surface' => (string) ($row->surface ?? 'unknown'),
|
|
]))
|
|
->all();
|
|
}
|
|
|
|
$surfaceExpression = $this->surfaceExpression();
|
|
|
|
return DB::table('user_discovery_events')
|
|
->selectRaw('algo_version')
|
|
->selectRaw($surfaceExpression . ' AS surface')
|
|
->selectRaw('COUNT(*) AS total_events')
|
|
->selectRaw('COUNT(DISTINCT user_id) AS unique_users')
|
|
->selectRaw('COUNT(DISTINCT artwork_id) AS unique_artworks')
|
|
->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")
|
|
->whereBetween('event_date', [$from, $to])
|
|
->groupBy('algo_version', DB::raw($surfaceExpression))
|
|
->orderBy('algo_version')
|
|
->orderByDesc('clicks')
|
|
->get()
|
|
->map(fn ($row): array => $this->formatEventSummaryRow($row, [
|
|
'algo_version' => (string) ($row->algo_version ?? ''),
|
|
'surface' => (string) ($row->surface ?? 'unknown'),
|
|
]))
|
|
->all();
|
|
}
|
|
|
|
private function topArtworks(string $from, string $to, int $limit): array
|
|
{
|
|
$surfaceExpression = $this->surfaceExpression();
|
|
|
|
return DB::table('user_discovery_events as e')
|
|
->leftJoin('artworks as a', 'a.id', '=', 'e.artwork_id')
|
|
->selectRaw('e.artwork_id')
|
|
->selectRaw('a.title as artwork_title')
|
|
->selectRaw('e.algo_version')
|
|
->selectRaw($surfaceExpression . ' AS surface')
|
|
->selectRaw("SUM(CASE WHEN e.event_type = 'view' THEN 1 ELSE 0 END) AS views")
|
|
->selectRaw("SUM(CASE WHEN e.event_type = 'click' THEN 1 ELSE 0 END) AS clicks")
|
|
->selectRaw("SUM(CASE WHEN e.event_type = 'favorite' THEN 1 ELSE 0 END) AS favorites")
|
|
->selectRaw("SUM(CASE WHEN e.event_type = 'download' THEN 1 ELSE 0 END) AS downloads")
|
|
->selectRaw("SUM(CASE WHEN e.event_type = 'hide_artwork' THEN 1 ELSE 0 END) AS hidden_artworks")
|
|
->selectRaw("SUM(CASE WHEN e.event_type = 'dislike_tag' THEN 1 ELSE 0 END) AS disliked_tags")
|
|
->selectRaw("SUM(CASE WHEN e.event_type = 'unhide_artwork' THEN 1 ELSE 0 END) AS undo_hidden_artworks")
|
|
->selectRaw("SUM(CASE WHEN e.event_type = 'undo_dislike_tag' THEN 1 ELSE 0 END) AS undo_disliked_tags")
|
|
->whereBetween('e.event_date', [$from, $to])
|
|
->groupBy('e.artwork_id', 'a.title', 'e.algo_version', DB::raw($surfaceExpression))
|
|
->get()
|
|
->map(fn ($row): array => $this->formatEventSummaryRow($row, [
|
|
'artwork_id' => (int) ($row->artwork_id ?? 0),
|
|
'artwork_title' => (string) ($row->artwork_title ?? ''),
|
|
'algo_version' => (string) ($row->algo_version ?? ''),
|
|
'surface' => (string) ($row->surface ?? 'unknown'),
|
|
]))
|
|
->sort(static function (array $left, array $right): int {
|
|
$favoriteCompare = $right['favorites'] <=> $left['favorites'];
|
|
if ($favoriteCompare !== 0) {
|
|
return $favoriteCompare;
|
|
}
|
|
|
|
$downloadCompare = $right['downloads'] <=> $left['downloads'];
|
|
if ($downloadCompare !== 0) {
|
|
return $downloadCompare;
|
|
}
|
|
|
|
return $right['clicks'] <=> $left['clicks'];
|
|
})
|
|
->take($limit)
|
|
->values()
|
|
->all();
|
|
}
|
|
|
|
private function dailyFeedback(string $from, string $to): array
|
|
{
|
|
if (Schema::hasTable('discovery_feedback_daily_metrics')) {
|
|
return DB::table('discovery_feedback_daily_metrics')
|
|
->selectRaw('metric_date')
|
|
->selectRaw('SUM(views) AS views')
|
|
->selectRaw('SUM(clicks) AS clicks')
|
|
->selectRaw('SUM(favorites) AS favorites')
|
|
->selectRaw('SUM(downloads) AS downloads')
|
|
->selectRaw('SUM(feedback_actions) AS feedback_actions')
|
|
->selectRaw('SUM(hidden_artworks) AS hidden_artworks')
|
|
->selectRaw('SUM(disliked_tags) AS disliked_tags')
|
|
->selectRaw('SUM(negative_feedback_actions) AS negative_feedback_actions')
|
|
->selectRaw('SUM(undo_hidden_artworks) AS undo_hidden_artworks')
|
|
->selectRaw('SUM(undo_disliked_tags) AS undo_disliked_tags')
|
|
->selectRaw('SUM(undo_actions) AS undo_actions')
|
|
->whereBetween('metric_date', [$from, $to])
|
|
->groupBy('metric_date')
|
|
->orderBy('metric_date')
|
|
->get()
|
|
->map(fn ($row): array => [
|
|
'date' => (string) $row->metric_date,
|
|
'views' => (int) ($row->views ?? 0),
|
|
'clicks' => (int) ($row->clicks ?? 0),
|
|
'favorites' => (int) ($row->favorites ?? 0),
|
|
'downloads' => (int) ($row->downloads ?? 0),
|
|
'feedback_actions' => (int) ($row->feedback_actions ?? 0),
|
|
'hidden_artworks' => (int) ($row->hidden_artworks ?? 0),
|
|
'disliked_tags' => (int) ($row->disliked_tags ?? 0),
|
|
'negative_feedback_actions' => (int) ($row->negative_feedback_actions ?? 0),
|
|
'undo_hidden_artworks' => (int) ($row->undo_hidden_artworks ?? 0),
|
|
'undo_disliked_tags' => (int) ($row->undo_disliked_tags ?? 0),
|
|
'undo_actions' => (int) ($row->undo_actions ?? 0),
|
|
])
|
|
->all();
|
|
}
|
|
|
|
return DB::table('user_discovery_events')
|
|
->selectRaw('event_date')
|
|
->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")
|
|
->whereBetween('event_date', [$from, $to])
|
|
->groupBy('event_date')
|
|
->orderBy('event_date')
|
|
->get()
|
|
->map(fn ($row): array => [
|
|
'date' => (string) $row->event_date,
|
|
'views' => (int) ($row->views ?? 0),
|
|
'clicks' => (int) ($row->clicks ?? 0),
|
|
'favorites' => (int) ($row->favorites ?? 0),
|
|
'downloads' => (int) ($row->downloads ?? 0),
|
|
'feedback_actions' => (int) (($row->favorites ?? 0) + ($row->downloads ?? 0)),
|
|
'hidden_artworks' => (int) ($row->hidden_artworks ?? 0),
|
|
'disliked_tags' => (int) ($row->disliked_tags ?? 0),
|
|
'negative_feedback_actions' => (int) (($row->hidden_artworks ?? 0) + ($row->disliked_tags ?? 0)),
|
|
'undo_hidden_artworks' => (int) ($row->undo_hidden_artworks ?? 0),
|
|
'undo_disliked_tags' => (int) ($row->undo_disliked_tags ?? 0),
|
|
'undo_actions' => (int) (($row->undo_hidden_artworks ?? 0) + ($row->undo_disliked_tags ?? 0)),
|
|
])
|
|
->all();
|
|
}
|
|
|
|
/**
|
|
* @param array<int, array<string, mixed>> $rows
|
|
* @param array<string, array<string, mixed>> $surfaceTrendMap
|
|
* @return array<int, array<string, mixed>>
|
|
*/
|
|
private function attachSurfaceTrendMap(array $rows, array $surfaceTrendMap): array
|
|
{
|
|
$rows = array_map(function (array $row) use ($surfaceTrendMap): array {
|
|
$surface = (string) ($row['surface'] ?? 'unknown');
|
|
|
|
return array_merge($row, [
|
|
'trend' => $surfaceTrendMap[$surface] ?? $this->emptySurfaceTrend(),
|
|
]);
|
|
}, $rows);
|
|
|
|
return $this->sortRowsByTrendRisk($rows);
|
|
}
|
|
|
|
/**
|
|
* @param array<int, array<string, mixed>> $rows
|
|
* @param array<string, array<string, mixed>> $algoSurfaceTrendMap
|
|
* @return array<int, array<string, mixed>>
|
|
*/
|
|
private function attachAlgoSurfaceTrendMap(array $rows, array $algoSurfaceTrendMap): array
|
|
{
|
|
$rows = array_map(function (array $row) use ($algoSurfaceTrendMap): array {
|
|
$algoVersion = (string) ($row['algo_version'] ?? '');
|
|
$surface = (string) ($row['surface'] ?? 'unknown');
|
|
$key = $this->algoSurfaceTrendKey($algoVersion, $surface);
|
|
|
|
return array_merge($row, [
|
|
'trend' => $algoSurfaceTrendMap[$key] ?? $this->emptySurfaceTrend(),
|
|
]);
|
|
}, $rows);
|
|
|
|
return $this->sortRowsByTrendRisk($rows);
|
|
}
|
|
|
|
/**
|
|
* @return array<string, array<string, mixed>>
|
|
*/
|
|
private function surfaceTrendMap(string $from, string $to): array
|
|
{
|
|
$rows = $this->dailySurfaceMetrics($from, $to);
|
|
if ($rows === []) {
|
|
return [];
|
|
}
|
|
|
|
$dates = array_values(array_unique(array_map(
|
|
static fn (array $row): string => (string) $row['date'],
|
|
$rows,
|
|
)));
|
|
sort($dates);
|
|
|
|
$latestDate = $dates[array_key_last($dates)] ?? null;
|
|
$previousDate = count($dates) > 1 ? $dates[count($dates) - 2] : null;
|
|
|
|
$grouped = [];
|
|
foreach ($rows as $row) {
|
|
$date = (string) ($row['date'] ?? '');
|
|
$surface = (string) ($row['surface'] ?? 'unknown');
|
|
$grouped[$surface][$date] = $row;
|
|
}
|
|
|
|
$trendMap = [];
|
|
foreach ($grouped as $surface => $surfaceRows) {
|
|
$latest = $latestDate !== null ? ($surfaceRows[$latestDate] ?? null) : null;
|
|
$previous = $previousDate !== null ? ($surfaceRows[$previousDate] ?? null) : null;
|
|
|
|
$trendMap[$surface] = [
|
|
'latest_day' => $latest,
|
|
'previous_day' => $previous,
|
|
'overall_status' => $this->overallTrendStatus($latest, $previous),
|
|
'deltas' => [
|
|
'feedback_actions' => $this->formatTrendDelta($latest, $previous, 'feedback_actions', 'up'),
|
|
'negative_feedback_actions' => $this->formatTrendDelta($latest, $previous, 'negative_feedback_actions', 'down'),
|
|
'undo_actions' => $this->formatTrendDelta($latest, $previous, 'undo_actions', 'up'),
|
|
],
|
|
];
|
|
}
|
|
|
|
return $trendMap;
|
|
}
|
|
|
|
/**
|
|
* @return array<string, array<string, mixed>>
|
|
*/
|
|
private function algoSurfaceTrendMap(string $from, string $to): array
|
|
{
|
|
$rows = $this->dailyAlgoSurfaceMetrics($from, $to);
|
|
if ($rows === []) {
|
|
return [];
|
|
}
|
|
|
|
$dates = array_values(array_unique(array_map(
|
|
static fn (array $row): string => (string) $row['date'],
|
|
$rows,
|
|
)));
|
|
sort($dates);
|
|
|
|
$latestDate = $dates[array_key_last($dates)] ?? null;
|
|
$previousDate = count($dates) > 1 ? $dates[count($dates) - 2] : null;
|
|
|
|
$grouped = [];
|
|
foreach ($rows as $row) {
|
|
$date = (string) ($row['date'] ?? '');
|
|
$algoVersion = (string) ($row['algo_version'] ?? '');
|
|
$surface = (string) ($row['surface'] ?? 'unknown');
|
|
$grouped[$this->algoSurfaceTrendKey($algoVersion, $surface)][$date] = $row;
|
|
}
|
|
|
|
$trendMap = [];
|
|
foreach ($grouped as $key => $algoSurfaceRows) {
|
|
$latest = $latestDate !== null ? ($algoSurfaceRows[$latestDate] ?? null) : null;
|
|
$previous = $previousDate !== null ? ($algoSurfaceRows[$previousDate] ?? null) : null;
|
|
|
|
$trendMap[$key] = [
|
|
'latest_day' => $latest,
|
|
'previous_day' => $previous,
|
|
'overall_status' => $this->overallTrendStatus($latest, $previous),
|
|
'deltas' => [
|
|
'feedback_actions' => $this->formatTrendDelta($latest, $previous, 'feedback_actions', 'up'),
|
|
'negative_feedback_actions' => $this->formatTrendDelta($latest, $previous, 'negative_feedback_actions', 'down'),
|
|
'undo_actions' => $this->formatTrendDelta($latest, $previous, 'undo_actions', 'up'),
|
|
],
|
|
];
|
|
}
|
|
|
|
return $trendMap;
|
|
}
|
|
|
|
/**
|
|
* @return array<int, array<string, mixed>>
|
|
*/
|
|
private function dailySurfaceMetrics(string $from, string $to): array
|
|
{
|
|
if (Schema::hasTable('discovery_feedback_daily_metrics')) {
|
|
return DB::table('discovery_feedback_daily_metrics')
|
|
->selectRaw('metric_date')
|
|
->selectRaw('surface')
|
|
->selectRaw('SUM(feedback_actions) AS feedback_actions')
|
|
->selectRaw('SUM(negative_feedback_actions) AS negative_feedback_actions')
|
|
->selectRaw('SUM(undo_actions) AS undo_actions')
|
|
->whereBetween('metric_date', [$from, $to])
|
|
->groupBy('metric_date', 'surface')
|
|
->orderBy('metric_date')
|
|
->get()
|
|
->map(fn ($row): array => [
|
|
'date' => (string) $row->metric_date,
|
|
'surface' => (string) ($row->surface ?? 'unknown'),
|
|
'feedback_actions' => (int) ($row->feedback_actions ?? 0),
|
|
'negative_feedback_actions' => (int) ($row->negative_feedback_actions ?? 0),
|
|
'undo_actions' => (int) ($row->undo_actions ?? 0),
|
|
])
|
|
->all();
|
|
}
|
|
|
|
$surfaceExpression = $this->surfaceExpression();
|
|
|
|
return DB::table('user_discovery_events')
|
|
->selectRaw('event_date')
|
|
->selectRaw($surfaceExpression . ' AS surface')
|
|
->selectRaw("SUM(CASE WHEN event_type = 'favorite' THEN 1 ELSE 0 END) + SUM(CASE WHEN event_type = 'download' THEN 1 ELSE 0 END) AS feedback_actions")
|
|
->selectRaw("SUM(CASE WHEN event_type = 'hide_artwork' THEN 1 ELSE 0 END) + SUM(CASE WHEN event_type = 'dislike_tag' THEN 1 ELSE 0 END) AS negative_feedback_actions")
|
|
->selectRaw("SUM(CASE WHEN event_type = 'unhide_artwork' THEN 1 ELSE 0 END) + SUM(CASE WHEN event_type = 'undo_dislike_tag' THEN 1 ELSE 0 END) AS undo_actions")
|
|
->whereBetween('event_date', [$from, $to])
|
|
->groupBy('event_date', DB::raw($surfaceExpression))
|
|
->orderBy('event_date')
|
|
->get()
|
|
->map(fn ($row): array => [
|
|
'date' => (string) $row->event_date,
|
|
'surface' => (string) ($row->surface ?? 'unknown'),
|
|
'feedback_actions' => (int) ($row->feedback_actions ?? 0),
|
|
'negative_feedback_actions' => (int) ($row->negative_feedback_actions ?? 0),
|
|
'undo_actions' => (int) ($row->undo_actions ?? 0),
|
|
])
|
|
->all();
|
|
}
|
|
|
|
/**
|
|
* @return array<int, array<string, mixed>>
|
|
*/
|
|
private function dailyAlgoSurfaceMetrics(string $from, string $to): array
|
|
{
|
|
if (Schema::hasTable('discovery_feedback_daily_metrics')) {
|
|
return DB::table('discovery_feedback_daily_metrics')
|
|
->selectRaw('metric_date')
|
|
->selectRaw('algo_version')
|
|
->selectRaw('surface')
|
|
->selectRaw('SUM(feedback_actions) AS feedback_actions')
|
|
->selectRaw('SUM(negative_feedback_actions) AS negative_feedback_actions')
|
|
->selectRaw('SUM(undo_actions) AS undo_actions')
|
|
->whereBetween('metric_date', [$from, $to])
|
|
->groupBy('metric_date', 'algo_version', 'surface')
|
|
->orderBy('metric_date')
|
|
->get()
|
|
->map(fn ($row): array => [
|
|
'date' => (string) $row->metric_date,
|
|
'algo_version' => (string) ($row->algo_version ?? ''),
|
|
'surface' => (string) ($row->surface ?? 'unknown'),
|
|
'feedback_actions' => (int) ($row->feedback_actions ?? 0),
|
|
'negative_feedback_actions' => (int) ($row->negative_feedback_actions ?? 0),
|
|
'undo_actions' => (int) ($row->undo_actions ?? 0),
|
|
])
|
|
->all();
|
|
}
|
|
|
|
$surfaceExpression = $this->surfaceExpression();
|
|
|
|
return DB::table('user_discovery_events')
|
|
->selectRaw('event_date')
|
|
->selectRaw('algo_version')
|
|
->selectRaw($surfaceExpression . ' AS surface')
|
|
->selectRaw("SUM(CASE WHEN event_type = 'favorite' THEN 1 ELSE 0 END) + SUM(CASE WHEN event_type = 'download' THEN 1 ELSE 0 END) AS feedback_actions")
|
|
->selectRaw("SUM(CASE WHEN event_type = 'hide_artwork' THEN 1 ELSE 0 END) + SUM(CASE WHEN event_type = 'dislike_tag' THEN 1 ELSE 0 END) AS negative_feedback_actions")
|
|
->selectRaw("SUM(CASE WHEN event_type = 'unhide_artwork' THEN 1 ELSE 0 END) + SUM(CASE WHEN event_type = 'undo_dislike_tag' THEN 1 ELSE 0 END) AS undo_actions")
|
|
->whereBetween('event_date', [$from, $to])
|
|
->groupBy('event_date', 'algo_version', DB::raw($surfaceExpression))
|
|
->orderBy('event_date')
|
|
->get()
|
|
->map(fn ($row): array => [
|
|
'date' => (string) $row->event_date,
|
|
'algo_version' => (string) ($row->algo_version ?? ''),
|
|
'surface' => (string) ($row->surface ?? 'unknown'),
|
|
'feedback_actions' => (int) ($row->feedback_actions ?? 0),
|
|
'negative_feedback_actions' => (int) ($row->negative_feedback_actions ?? 0),
|
|
'undo_actions' => (int) ($row->undo_actions ?? 0),
|
|
])
|
|
->all();
|
|
}
|
|
|
|
private function algoSurfaceTrendKey(string $algoVersion, string $surface): string
|
|
{
|
|
return $algoVersion . '|' . $surface;
|
|
}
|
|
|
|
/**
|
|
* @param array<int, array<string, mixed>> $rows
|
|
* @return array<int, array<string, mixed>>
|
|
*/
|
|
private function sortRowsByTrendRisk(array $rows): array
|
|
{
|
|
usort($rows, function (array $left, array $right): int {
|
|
$leftLevel = (string) ($left['trend']['overall_status']['level'] ?? 'neutral');
|
|
$rightLevel = (string) ($right['trend']['overall_status']['level'] ?? 'neutral');
|
|
|
|
$levelCompare = $this->trendLevelRank($leftLevel) <=> $this->trendLevelRank($rightLevel);
|
|
if ($levelCompare !== 0) {
|
|
return $levelCompare;
|
|
}
|
|
|
|
$leftScore = (int) ($left['trend']['overall_status']['score'] ?? 0);
|
|
$rightScore = (int) ($right['trend']['overall_status']['score'] ?? 0);
|
|
$scoreCompare = $leftScore <=> $rightScore;
|
|
if ($scoreCompare !== 0) {
|
|
return $scoreCompare;
|
|
}
|
|
|
|
$clickCompare = ((int) ($right['clicks'] ?? 0)) <=> ((int) ($left['clicks'] ?? 0));
|
|
if ($clickCompare !== 0) {
|
|
return $clickCompare;
|
|
}
|
|
|
|
return ((int) ($right['feedback_actions'] ?? 0)) <=> ((int) ($left['feedback_actions'] ?? 0));
|
|
});
|
|
|
|
return $rows;
|
|
}
|
|
|
|
private function trendLevelRank(string $level): int
|
|
{
|
|
return match ($level) {
|
|
'risk' => 0,
|
|
'watch' => 1,
|
|
'healthy' => 2,
|
|
default => 3,
|
|
};
|
|
}
|
|
|
|
/**
|
|
* @param object $row
|
|
* @param array<string, mixed> $base
|
|
* @return array<string, mixed>
|
|
*/
|
|
private function formatEventSummaryRow(object $row, array $base): array
|
|
{
|
|
$views = (int) ($row->views ?? 0);
|
|
$clicks = (int) ($row->clicks ?? 0);
|
|
$favorites = (int) ($row->favorites ?? 0);
|
|
$downloads = (int) ($row->downloads ?? 0);
|
|
$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);
|
|
$feedbackActions = $favorites + $downloads;
|
|
$negativeFeedbackActions = (int) ($row->negative_feedback_actions ?? ($hiddenArtworks + $dislikedTags));
|
|
$undoActions = (int) ($row->undo_actions ?? ($undoHiddenArtworks + $undoDislikedTags));
|
|
|
|
return array_merge($base, [
|
|
'total_events' => (int) ($row->total_events ?? ($views + $clicks + $favorites + $downloads + $hiddenArtworks + $dislikedTags + $undoHiddenArtworks + $undoDislikedTags)),
|
|
'unique_users' => (int) ($row->unique_users ?? 0),
|
|
'unique_artworks' => (int) ($row->unique_artworks ?? 0),
|
|
'views' => $views,
|
|
'clicks' => $clicks,
|
|
'favorites' => $favorites,
|
|
'downloads' => $downloads,
|
|
'feedback_actions' => $feedbackActions,
|
|
'hidden_artworks' => $hiddenArtworks,
|
|
'disliked_tags' => $dislikedTags,
|
|
'negative_feedback_actions' => $negativeFeedbackActions,
|
|
'undo_hidden_artworks' => $undoHiddenArtworks,
|
|
'undo_disliked_tags' => $undoDislikedTags,
|
|
'undo_actions' => $undoActions,
|
|
'ctr' => round($views > 0 ? $clicks / $views : 0.0, 6),
|
|
'favorite_rate_per_click' => round($clicks > 0 ? $favorites / $clicks : 0.0, 6),
|
|
'download_rate_per_click' => round($clicks > 0 ? $downloads / $clicks : 0.0, 6),
|
|
'feedback_rate_per_click' => round($clicks > 0 ? $feedbackActions / $clicks : 0.0, 6),
|
|
'negative_feedback_rate_per_click' => round($clicks > 0 ? $negativeFeedbackActions / $clicks : 0.0, 6),
|
|
'undo_rate_per_negative_feedback' => round($negativeFeedbackActions > 0 ? $undoActions / $negativeFeedbackActions : 0.0, 6),
|
|
]);
|
|
}
|
|
|
|
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')";
|
|
}
|
|
|
|
private function emptyOverview(): array
|
|
{
|
|
return [
|
|
'total_events' => 0,
|
|
'unique_users' => 0,
|
|
'unique_artworks' => 0,
|
|
'views' => 0,
|
|
'clicks' => 0,
|
|
'favorites' => 0,
|
|
'downloads' => 0,
|
|
'feedback_actions' => 0,
|
|
'hidden_artworks' => 0,
|
|
'disliked_tags' => 0,
|
|
'negative_feedback_actions' => 0,
|
|
'undo_hidden_artworks' => 0,
|
|
'undo_disliked_tags' => 0,
|
|
'undo_actions' => 0,
|
|
'ctr' => 0.0,
|
|
'favorite_rate_per_click' => 0.0,
|
|
'download_rate_per_click' => 0.0,
|
|
'feedback_rate_per_click' => 0.0,
|
|
'negative_feedback_rate_per_click' => 0.0,
|
|
'undo_rate_per_negative_feedback' => 0.0,
|
|
];
|
|
}
|
|
|
|
private function latestAggregatedDate(): ?string
|
|
{
|
|
if (! Schema::hasTable('discovery_feedback_daily_metrics')) {
|
|
return null;
|
|
}
|
|
|
|
$date = DB::table('discovery_feedback_daily_metrics')->max('metric_date');
|
|
|
|
return $date ? (string) $date : null;
|
|
}
|
|
|
|
/**
|
|
* @param array<int, array<string, mixed>> $dailyFeedback
|
|
* @return array<string, mixed>
|
|
*/
|
|
private function trendSummary(array $dailyFeedback): array
|
|
{
|
|
if ($dailyFeedback === []) {
|
|
return $this->emptyTrendSummary();
|
|
}
|
|
|
|
$latest = $dailyFeedback[array_key_last($dailyFeedback)] ?? null;
|
|
$previous = count($dailyFeedback) > 1 ? $dailyFeedback[count($dailyFeedback) - 2] : null;
|
|
$recentSeven = array_slice($dailyFeedback, -7);
|
|
|
|
return [
|
|
'latest_day' => $latest,
|
|
'previous_day' => $previous,
|
|
'rolling_7d_average' => [
|
|
'views' => $this->averageFromRows($recentSeven, 'views'),
|
|
'clicks' => $this->averageFromRows($recentSeven, 'clicks'),
|
|
'feedback_actions' => $this->averageFromRows($recentSeven, 'feedback_actions'),
|
|
'negative_feedback_actions' => $this->averageFromRows($recentSeven, 'negative_feedback_actions'),
|
|
'undo_actions' => $this->averageFromRows($recentSeven, 'undo_actions'),
|
|
],
|
|
'deltas' => [
|
|
'feedback_actions' => $this->formatTrendDelta($latest, $previous, 'feedback_actions', 'up'),
|
|
'negative_feedback_actions' => $this->formatTrendDelta($latest, $previous, 'negative_feedback_actions', 'down'),
|
|
'undo_actions' => $this->formatTrendDelta($latest, $previous, 'undo_actions', 'up'),
|
|
],
|
|
'overall_status' => $this->overallTrendStatus($latest, $previous),
|
|
];
|
|
}
|
|
|
|
/**
|
|
* @param array<string, mixed>|null $latest
|
|
* @param array<string, mixed>|null $previous
|
|
* @return array<string, mixed>
|
|
*/
|
|
private function overallTrendStatus(?array $latest, ?array $previous): array
|
|
{
|
|
if ($previous === null) {
|
|
return [
|
|
'level' => 'neutral',
|
|
'label' => 'No prior day',
|
|
'reason' => 'A second day of data is required to judge trend health.',
|
|
'score' => 0,
|
|
];
|
|
}
|
|
|
|
$feedbackDelta = $this->formatTrendDelta($latest, $previous, 'feedback_actions', 'up');
|
|
$negativeDelta = $this->formatTrendDelta($latest, $previous, 'negative_feedback_actions', 'down');
|
|
$undoDelta = $this->formatTrendDelta($latest, $previous, 'undo_actions', 'up');
|
|
|
|
$score = 0;
|
|
$score += $feedbackDelta['status'] === 'improved' ? 2 : ($feedbackDelta['status'] === 'worse' ? -2 : 0);
|
|
$score += $negativeDelta['status'] === 'improved' ? 2 : ($negativeDelta['status'] === 'worse' ? -2 : 0);
|
|
$score += $undoDelta['status'] === 'improved' ? 1 : ($undoDelta['status'] === 'worse' ? -1 : 0);
|
|
|
|
if ($score >= 3) {
|
|
return [
|
|
'level' => 'healthy',
|
|
'label' => 'Healthy',
|
|
'reason' => 'Positive signals are improving faster than negative feedback.',
|
|
'score' => $score,
|
|
];
|
|
}
|
|
|
|
if ($score <= -2) {
|
|
return [
|
|
'level' => 'risk',
|
|
'label' => 'Risk',
|
|
'reason' => 'Negative feedback is worsening or positive engagement is slipping.',
|
|
'score' => $score,
|
|
];
|
|
}
|
|
|
|
return [
|
|
'level' => 'watch',
|
|
'label' => 'Watch',
|
|
'reason' => 'Signals are mixed and worth monitoring.',
|
|
'score' => $score,
|
|
];
|
|
}
|
|
|
|
/**
|
|
* @param array<int, array<string, mixed>> $rows
|
|
*/
|
|
private function averageFromRows(array $rows, string $key): float
|
|
{
|
|
if ($rows === []) {
|
|
return 0.0;
|
|
}
|
|
|
|
$sum = array_sum(array_map(static fn (array $row): int => (int) ($row[$key] ?? 0), $rows));
|
|
|
|
return round($sum / count($rows), 2);
|
|
}
|
|
|
|
/**
|
|
* @param array<string, mixed>|null $latest
|
|
* @param array<string, mixed>|null $previous
|
|
* @return array<string, mixed>
|
|
*/
|
|
private function formatTrendDelta(?array $latest, ?array $previous, string $key, string $goodDirection): array
|
|
{
|
|
$latestValue = (int) ($latest[$key] ?? 0);
|
|
if ($previous === null) {
|
|
return [
|
|
'value' => 0,
|
|
'direction' => 'flat',
|
|
'status' => 'neutral',
|
|
'label' => 'No prior day',
|
|
];
|
|
}
|
|
|
|
$delta = $latestValue - (int) ($previous[$key] ?? 0);
|
|
if ($delta === 0) {
|
|
return [
|
|
'value' => 0,
|
|
'direction' => 'flat',
|
|
'status' => 'neutral',
|
|
'label' => 'Flat',
|
|
];
|
|
}
|
|
|
|
$improved = $goodDirection === 'down' ? $delta < 0 : $delta > 0;
|
|
|
|
return [
|
|
'value' => $delta,
|
|
'direction' => $delta > 0 ? 'up' : 'down',
|
|
'status' => $improved ? 'improved' : 'worse',
|
|
'label' => sprintf('%s %s%s vs prev day', $improved ? 'Improved' : 'Worse', $delta > 0 ? '+' : '', number_format($delta)),
|
|
];
|
|
}
|
|
|
|
/**
|
|
* @return array<string, mixed>
|
|
*/
|
|
private function emptyTrendSummary(): array
|
|
{
|
|
return [
|
|
'latest_day' => null,
|
|
'previous_day' => null,
|
|
'rolling_7d_average' => [
|
|
'views' => 0.0,
|
|
'clicks' => 0.0,
|
|
'feedback_actions' => 0.0,
|
|
'negative_feedback_actions' => 0.0,
|
|
'undo_actions' => 0.0,
|
|
],
|
|
'deltas' => [
|
|
'feedback_actions' => ['value' => 0, 'direction' => 'flat', 'status' => 'neutral', 'label' => 'No prior day'],
|
|
'negative_feedback_actions' => ['value' => 0, 'direction' => 'flat', 'status' => 'neutral', 'label' => 'No prior day'],
|
|
'undo_actions' => ['value' => 0, 'direction' => 'flat', 'status' => 'neutral', 'label' => 'No prior day'],
|
|
],
|
|
'overall_status' => [
|
|
'level' => 'neutral',
|
|
'label' => 'No prior day',
|
|
'reason' => 'A second day of data is required to judge trend health.',
|
|
'score' => 0,
|
|
],
|
|
];
|
|
}
|
|
|
|
/**
|
|
* @return array<string, mixed>
|
|
*/
|
|
private function emptySurfaceTrend(): array
|
|
{
|
|
return [
|
|
'latest_day' => null,
|
|
'previous_day' => null,
|
|
'overall_status' => [
|
|
'level' => 'neutral',
|
|
'label' => 'No prior day',
|
|
'reason' => 'A second day of data is required to judge trend health.',
|
|
'score' => 0,
|
|
],
|
|
'deltas' => [
|
|
'feedback_actions' => ['value' => 0, 'direction' => 'flat', 'status' => 'neutral', 'label' => 'No prior day'],
|
|
'negative_feedback_actions' => ['value' => 0, 'direction' => 'flat', 'status' => 'neutral', 'label' => 'No prior day'],
|
|
'undo_actions' => ['value' => 0, 'direction' => 'flat', 'status' => 'neutral', 'label' => 'No prior day'],
|
|
],
|
|
];
|
|
}
|
|
}
|