Open-source social media scheduler built for AI agents and the teams behind them. Create, schedule and publish posts to every network through MCP or the REST API, or write them yourself in a composer your team will love.
PHP ai-agentsblueskyfacebookinstagramlaravellinkedinmastodonmcpmcp-serveropen-sourcerest-apiself-hostedsocial-mediasocial-media-managementsocial-media-schedulerthreadstiktokvuejsxyoutube
<?php
declare(strict_types=1);
namespace App\Actions\Analytics;
use App\Dto\Analytics\DateRange;
use App\Dto\Analytics\PublicationFilter;
use App\Enums\Analytics\MetricAvailability;
use App\Enums\Analytics\MetricKey;
use App\Enums\Analytics\PublicationAvailability;
use App\Enums\User\WeekStart;
use App\Models\Post;
use App\Models\Workspace;
use App\Support\Analytics\MetricComparison;
use App\Support\Analytics\PeriodBuckets;
use Carbon\CarbonImmutable;
use Illuminate\Database\Query\Builder;
use Illuminate\Support\Collection;
use Illuminate\Support\LazyCollection;
class BuildPublicationAnalyticsReport
{
/**
* Metrics compared per channel in the Performance table, in display order.
*/
public const array PERFORMANCE_METRICS = [
'posts', 'reactions', 'comments', 'engagement_rate', 'reposts', 'impressions', 'clicks', 'views',
'shares', 'saves', 'follows_gained', 'reach', 'watch_time_minutes', 'average_watch_time_seconds',
];
public function __construct(
private readonly PeriodBuckets $buckets,
private readonly QueryLatestPublicationSnapshots $latestSnapshots,
) {}
/**
* @param list<string>|null $accountKeys Analytics account keys to scope to; null means every account.
* @param WeekStart $weekStart The viewer's week start; weekly buckets end on its last day.
* @param PublicationFilter|null $filter Narrows both periods by label and post type.
* @return array<string, mixed>
*/
public function execute(Workspace $workspace, DateRange $previous, DateRange $current, ?array $accountKeys = null, WeekStart $weekStart = WeekStart::DEFAULT, ?PublicationFilter $filter = null): array
{
$currentTotals = $this->emptyTotals();
$previousTotals = $this->emptyTotals();
$currentAccounts = [];
$previousAccounts = [];
$topReactions = [];
$topComments = [];
$buckets = $this->buckets->for($current, $weekStart);
$bucketIndexByDate = [];
$bucketCounts = [];
foreach ($buckets as $index => $bucket) {
for ($day = CarbonImmutable::parse(data_get($bucket, 'start'), 'UTC'); $day->toDateString() <= data_get($bucket, 'end'); $day = $day->addDay()) {
$bucketIndexByDate[$day->toDateString()] = $index;
}
}
foreach ($this->publications($workspace, $previous->startsAt(), $current->endsAt(), $accountKeys, $filter) as $row) {
$key = $row->social_account_key;
if (! $current->contains($row->provider_published_at)) {
$previousTotals = $this->addTotals($previousTotals, $row);
$previousAccounts[$key] = $this->addTotals(
data_get($previousAccounts, $key, $this->emptyTotals()),
$row,
);
continue;
}
$currentTotals = $this->addTotals($currentTotals, $row);
$account = data_get($currentAccounts, $key, ['row' => $row, 'totals' => $this->emptyTotals()]);
$account['totals'] = $this->addTotals(data_get($account, 'totals'), $row);
$currentAccounts[$key] = $account;
$this->retainTopPublication($topReactions, $row, 'reactions_count');
$this->retainTopPublication($topComments, $row, 'comments_count');
$index = data_get($bucketIndexByDate, $current->localDate($row->provider_published_at));
if ($index !== null) {
$bucketCounts[$index][$key] = (int) data_get($bucketCounts, "{$index}.{$key}", 0) + 1;
}
}
$postAccounts = [];
foreach ($currentAccounts as $key => $account) {
$row = data_get($account, 'row');
$postAccounts[] = [
'social_account_key' => $key,
'platform' => $row->platform,
'name' => $row->account_display_name,
'username' => $row->account_username,
'avatar_url' => $row->account_avatar_url,
'count' => data_get($account, 'totals.posts'),
];
}
foreach ($buckets as $index => &$bucket) {
$bucket['accounts'] = array_fill_keys(array_keys($currentAccounts), 0);
foreach (data_get($bucketCounts, $index, []) as $key => $count) {
$bucket['accounts'][$key] = $count;
}
$bucket['total'] = collect(data_get($bucket, 'accounts'))->sum();
}
unset($bucket);
$postMedia = $this->postMedia([...$topReactions, ...$topComments]);
return [
'current_totals' => $this->finalizeTotals($currentTotals),
'previous_totals' => $this->finalizeTotals($previousTotals),
'posts' => [
'resolution' => $this->buckets->resolution($current),
'total' => data_get($currentTotals, 'posts'),
'accounts' => $postAccounts,
'buckets' => $buckets,
],
'top_posts' => [
'reactions' => $this->top($topReactions, $postMedia),
'comments' => $this->top($topComments, $postMedia),
],
'performance' => $this->performance($currentAccounts, $previousAccounts),
];
}
/**
* @param list<string>|null $accountKeys
*/
private function publications(Workspace $workspace, CarbonImmutable $start, CarbonImmutable $end, ?array $accountKeys, ?PublicationFilter $filter): LazyCollection
{
return $this->query($workspace, $start, $end, $accountKeys, $filter)->cursor();
}
/**
* Publications in the window with their latest saved metrics.
*
* @param list<string>|null $accountKeys
*/
public function query(Workspace $workspace, CarbonImmutable $start, CarbonImmutable $end, ?array $accountKeys = null, ?PublicationFilter $filter = null): Builder
{
return $this->latestSnapshots->execute($workspace->id, $accountKeys, $start, $end)
->leftJoin((new Post)->getTable().' as destination', 'destination.id', '=', 'publication.post_id')
->when($filter !== null, fn (Builder $filtered): Builder => $filter->apply($filtered))
->select([
'publication.id', 'publication.social_account_key', 'publication.social_account_id',
'publication.post_id', 'publication.platform', 'publication.network',
'publication.account_display_name', 'publication.account_username',
'publication.account_avatar_url', 'publication.remote_id',
'publication.provider_published_at', 'publication.origin', 'publication.content_type',
'publication.availability',
'publication.permalink', 'publication.excerpt', 'publication.preview_metadata',
'metric.reactions_count', 'metric.comments_count', 'metric.shares_count',
'metric.saves_count', 'metric.views_count', 'metric.impressions_count',
'metric.reach_count', 'metric.engagement_count', 'metric.exposure_count',
'metric.exposure_kind', 'metric.collected_at',
'metric.watch_time_milliseconds', 'metric.average_watch_time_milliseconds', 'metric.metrics',
]);
}
/** @return array{posts: int, reactions: int, comments: int, engagement: int, exposure: int, views: int, reach: int, shares: int, saves: int, impressions: int, clicks: int, reposts: int, quotes: int, watch_time: int, average_watch_time: int, average_watch_time_posts: int, follows: int, has_reactions: bool, has_comments: bool, has_views: bool, has_reach: bool, has_shares: bool, has_saves: bool, has_impressions: bool, has_clicks: bool, has_reposts: bool, has_quotes: bool, has_watch_time: bool, has_follows: bool} */
private function emptyTotals(): array
{
return [
'posts' => 0,
'reactions' => 0,
'comments' => 0,
'engagement' => 0,
'exposure' => 0,
'views' => 0,
'reach' => 0,
'shares' => 0,
'saves' => 0,
'impressions' => 0,
'clicks' => 0,
'reposts' => 0,
'quotes' => 0,
'watch_time' => 0,
'average_watch_time' => 0,
'average_watch_time_posts' => 0,
'follows' => 0,
'has_reactions' => false,
'has_comments' => false,
'has_views' => false,
'has_reach' => false,
'has_shares' => false,
'has_saves' => false,
'has_impressions' => false,
'has_clicks' => false,
'has_reposts' => false,
'has_quotes' => false,
'has_watch_time' => false,
'has_follows' => false,
];
}
/**
* @param array{posts: int, reactions: int, comments: int, engagement: int, exposure: int, views: int, reach: int, shares: int, saves: int, impressions: int, clicks: int, reposts: int, quotes: int, watch_time: int, average_watch_time: int, average_watch_time_posts: int, follows: int, has_reactions: bool, has_comments: bool, has_views: bool, has_reach: bool, has_shares: bool, has_saves: bool, has_impressions: bool, has_clicks: bool, has_reposts: bool, has_quotes: bool, has_watch_time: bool, has_follows: bool} $totals
* @return array{posts: int, reactions: int, comments: int, engagement: int, exposure: int, views: int, reach: int, shares: int, saves: int, impressions: int, clicks: int, reposts: int, quotes: int, watch_time: int, average_watch_time: int, average_watch_time_posts: int, follows: int, has_reactions: bool, has_comments: bool, has_views: bool, has_reach: bool, has_shares: bool, has_saves: bool, has_impressions: bool, has_clicks: bool, has_reposts: bool, has_quotes: bool, has_watch_time: bool, has_follows: bool}
*/
private function addTotals(array $totals, object $row): array
{
$totals['posts'] = data_get($totals, 'posts') + 1;
$measured = $row->metrics === null ? [] : (array) json_decode((string) $row->metrics, true);
foreach ([
'reactions' => $row->reactions_count,
'comments' => $row->comments_count,
'views' => $row->views_count,
'reach' => $row->reach_count,
'shares' => $row->shares_count,
'saves' => $row->saves_count,
'impressions' => $row->impressions_count,
'clicks' => $this->measured($measured, MetricKey::Clicks) ?? $this->measured($measured, MetricKey::LinkClicks),
'reposts' => $this->measured($measured, MetricKey::Reposts),
'quotes' => $this->measured($measured, MetricKey::Quotes),
'watch_time' => $row->watch_time_milliseconds,
'follows' => $this->measured($measured, MetricKey::Follows),
] as $metric => $value) {
if ($value !== null) {
$totals["has_{$metric}"] = true;
$totals[$metric] = data_get($totals, $metric) + (int) $value;
}
}
if ($row->average_watch_time_milliseconds !== null) {
$totals['average_watch_time'] = data_get($totals, 'average_watch_time') + (int) $row->average_watch_time_milliseconds;
$totals['average_watch_time_posts'] = data_get($totals, 'average_watch_time_posts') + 1;
}
if ($row->engagement_count !== null && $row->exposure_count !== null && (int) $row->exposure_count > 0) {
$totals['engagement'] = data_get($totals, 'engagement') + (int) $row->engagement_count;
$totals['exposure'] = data_get($totals, 'exposure') + (int) $row->exposure_count;
}
return $totals;
}
/** @param array<string, mixed> $metrics */
private function measured(array $metrics, MetricKey $key): ?int
{
$metric = data_get($metrics, $key->value);
$value = data_get($metric, 'value');
return data_get($metric, 'availability') === MetricAvailability::Available->value && is_numeric($value)
? (int) $value : null;
}
/**
* @param array{posts: int, reactions: int, comments: int, engagement: int, exposure: int, views: int, reach: int, shares: int, saves: int, impressions: int, clicks: int, reposts: int, quotes: int, watch_time: int, average_watch_time: int, average_watch_time_posts: int, follows: int, has_reactions: bool, has_comments: bool, has_views: bool, has_reach: bool, has_shares: bool, has_saves: bool, has_impressions: bool, has_clicks: bool, has_reposts: bool, has_quotes: bool, has_watch_time: bool, has_follows: bool} $totals
* @return array{posts: int, reactions: ?int, comments: ?int, engagement_rate: ?float, views: ?int, reach: ?int, shares: ?int, saves: ?int, impressions: ?int, clicks: ?int, reposts: ?int, quotes: ?int, watch_time_minutes: ?float, average_watch_time_seconds: ?float, follows_gained: ?int}
*/
private function finalizeTotals(array $totals): array
{
$averagePosts = data_get($totals, 'average_watch_time_posts');
return [
'posts' => data_get($totals, 'posts'),
'reactions' => data_get($totals, 'has_reactions') ? data_get($totals, 'reactions') : null,
'comments' => data_get($totals, 'has_comments') ? data_get($totals, 'comments') : null,
'engagement_rate' => data_get($totals, 'exposure') === 0 ? null : round(data_get($totals, 'engagement') / data_get($totals, 'exposure') * 100, 2),
'views' => data_get($totals, 'has_views') ? data_get($totals, 'views') : null,
'reach' => data_get($totals, 'has_reach') ? data_get($totals, 'reach') : null,
'shares' => data_get($totals, 'has_shares') ? data_get($totals, 'shares') : null,
'saves' => data_get($totals, 'has_saves') ? data_get($totals, 'saves') : null,
'impressions' => data_get($totals, 'has_impressions') ? data_get($totals, 'impressions') : null,
'clicks' => data_get($totals, 'has_clicks') ? data_get($totals, 'clicks') : null,
'reposts' => data_get($totals, 'has_reposts') ? data_get($totals, 'reposts') : null,
'quotes' => data_get($totals, 'has_quotes') ? data_get($totals, 'quotes') : null,
'watch_time_minutes' => data_get($totals, 'has_watch_time') ? round(data_get($totals, 'watch_time') / 60000, 2) : null,
'average_watch_time_seconds' => $averagePosts === 0 ? null : round(data_get($totals, 'average_watch_time') / $averagePosts / 1000, 2),
'follows_gained' => data_get($totals, 'has_follows') ? data_get($totals, 'follows') : null,
];
}
/** @param list<object> $rows */
private function retainTopPublication(array &$rows, object $row, string $metric): void
{
if ($row->{$metric} === null) {
return;
}
$rows[] = $row;
usort($rows, fn (object $a, object $b): int => ((int) $b->{$metric} <=> (int) $a->{$metric})
?: strcmp((string) $b->provider_published_at, (string) $a->provider_published_at)
?: strcmp((string) $a->id, (string) $b->id));
if (count($rows) > 5) {
array_pop($rows);
}
}
/**
* The first image or video of each ranked post, keyed by post id.
*
* @param list<object> $rows
* @return Collection<string, array<string, mixed>>
*/
private function postMedia(array $rows): Collection
{
return Post::query()
->whereKey(collect($rows)->pluck('post_id')->filter())
->get(['id', 'media'])
->mapWithKeys(fn (Post $post): array => [$post->id => $post->coverMedia()?->toArray()])
->filter();
}
/**
* @param list<object> $rows
* @param Collection<string, array<string, mixed>> $postMedia
* @return list<array<string, mixed>>
*/
private function top(array $rows, Collection $postMedia): array
{
return collect($rows)->values()->map(fn (object $row, int $index): array => [
'id' => $row->id,
'rank' => $index + 1,
'post_id' => $row->post_id,
'social_account_key' => $row->social_account_key,
'platform' => $row->platform,
'name' => $row->account_display_name,
'username' => $row->account_username,
'avatar_url' => $row->account_avatar_url,
'origin' => $row->origin,
'content_type' => $row->content_type,
'availability' => $row->availability,
'published_at' => $row->provider_published_at,
'permalink' => PublicationAvailability::from($row->availability)->visiblePermalink($row->permalink),
'excerpt' => $row->excerpt,
'preview_metadata' => $row->preview_metadata ? json_decode((string) $row->preview_metadata, true) : null,
'media' => $postMedia->get((string) $row->post_id),
'reactions' => $row->reactions_count === null ? null : (int) $row->reactions_count,
'comments' => $row->comments_count === null ? null : (int) $row->comments_count,
])->all();
}
/** @return list<array<string, mixed>> */
private function performance(array $current, array $previous): array
{
$rows = [];
foreach ($current as $key => $account) {
$representative = data_get($account, 'row');
$totals = $this->finalizeTotals(data_get($account, 'totals'));
$prior = $this->finalizeTotals(data_get($previous, $key, $this->emptyTotals()));
$rows[] = [
'social_account_key' => $key,
'platform' => $representative->platform,
'name' => $representative->account_display_name,
'username' => $representative->account_username,
'avatar_url' => $representative->account_avatar_url,
...array_combine(self::PERFORMANCE_METRICS, array_map(
fn (string $metric): array => MetricComparison::between(data_get($totals, $metric), data_get($prior, $metric)),
self::PERFORMANCE_METRICS,
)),
];
}
usort($rows, fn (array $a, array $b): int => [data_get($a, 'platform'), data_get($a, 'username'), data_get($a, 'social_account_key')]
<=> [data_get($b, 'platform'), data_get($b, 'username'), data_get($b, 'social_account_key')]);
return $rows;
}
}