| 1 |
<?php |
| 2 |
|
| 3 |
namespace FluentCart\App\Services\Report; |
| 4 |
|
| 5 |
use FluentCart\App\App; |
| 6 |
use FluentCart\App\Helpers\Status; |
| 7 |
use FluentCart\Framework\Support\Arr; |
| 8 |
use FluentCart\App\Services\Report\Concerns\Subscription\FutureRenewals; |
| 9 |
|
| 10 |
class SubscriptionReportService extends ReportService |
| 11 |
{ |
| 12 |
use FutureRenewals; |
| 13 |
|
| 14 |
public function getRetentionChart($params = []) |
| 15 |
{ |
| 16 |
$customDays = max(0, (int) Arr::get($params, 'customDays', 0)); |
| 17 |
$variationIds = Arr::get($params, 'variationIds', []); |
| 18 |
$startDate = Arr::get($params, 'startDate'); |
| 19 |
$endDate = Arr::get($params, 'endDate'); |
| 20 |
|
| 21 |
$baseQuery = App::db()->query() |
| 22 |
->from('fct_subscriptions as s') |
| 23 |
->whereBetween('s.created_at', [$startDate, $endDate]) |
| 24 |
->when($variationIds, fn ($q) => $q->whereIn('s.variation_id', $variationIds)); |
| 25 |
|
| 26 |
if ($customDays) { |
| 27 |
$query = $baseQuery->selectRaw("COUNT(*) AS day_{$customDays}") |
| 28 |
->whereRaw(' |
| 29 |
DATEDIFF( |
| 30 |
COALESCE(s.canceled_at, NOW()), |
| 31 |
s.created_at |
| 32 |
) <= ? |
| 33 |
', [$customDays]); |
| 34 |
} else { |
| 35 |
$baseQuery->selectRaw('DATEDIFF(COALESCE(s.canceled_at, NOW()), s.created_at) AS lifespan'); |
| 36 |
|
| 37 |
$query = App::db()->query()->selectRaw(' |
| 38 |
SUM(CASE WHEN lifespan <= 7 THEN 1 ELSE 0 END) AS day_7, |
| 39 |
SUM(CASE WHEN lifespan BETWEEN 8 AND 15 THEN 1 ELSE 0 END) AS day_15, |
| 40 |
SUM(CASE WHEN lifespan BETWEEN 16 AND 30 THEN 1 ELSE 0 END) AS day_30, |
| 41 |
SUM(CASE WHEN lifespan BETWEEN 31 AND 90 THEN 1 ELSE 0 END) AS day_90, |
| 42 |
SUM(CASE WHEN lifespan BETWEEN 91 AND 180 THEN 1 ELSE 0 END) AS day_180, |
| 43 |
SUM(CASE WHEN lifespan BETWEEN 181 AND 365 THEN 1 ELSE 0 END) AS day_365, |
| 44 |
SUM(CASE WHEN lifespan > 365 THEN 1 ELSE 0 END) AS more_than_year |
| 45 |
')->fromSub($baseQuery, 'retention_data'); |
| 46 |
} |
| 47 |
|
| 48 |
return $query->first(); |
| 49 |
} |
| 50 |
|
| 51 |
public function getDailySignups($params = []) |
| 52 |
{ |
| 53 |
return App::db()->query() |
| 54 |
->selectRaw(' |
| 55 |
DATE(s.created_at) AS trend_date, |
| 56 |
COUNT(s.id) AS value |
| 57 |
') |
| 58 |
->from('fct_subscriptions as s') |
| 59 |
->whereBetween('s.created_at', [$params['startDate'], $params['endDate']]) |
| 60 |
->when($params['variationIds'], fn ($q) => $q->whereIn('s.variation_id', $params['variationIds'])) |
| 61 |
->groupBy('trend_date') |
| 62 |
->orderBy('trend_date') |
| 63 |
->get(); |
| 64 |
} |
| 65 |
|
| 66 |
public function getChartData(array $params) |
| 67 |
{ |
| 68 |
$startDate = $params['startDate']; |
| 69 |
$endDate = $params['endDate']; |
| 70 |
$subscriptionType = $params['subscriptionType']; |
| 71 |
|
| 72 |
$group = ReportHelper::processGroup($startDate, $endDate, $params['groupKey']); |
| 73 |
|
| 74 |
$query = App::db()->query(); |
| 75 |
|
| 76 |
if (in_array($subscriptionType, [Status::ORDER_TYPE_SUBSCRIPTION, Status::ORDER_TYPE_RENEWAL])) { |
| 77 |
$query->from('fct_orders as o') |
| 78 |
->where('o.type', $subscriptionType) |
| 79 |
->whereIn('o.status', Status::getOrderSuccessStatuses()); |
| 80 |
|
| 81 |
$query = $this->applyFilters($query, $params); |
| 82 |
} else { |
| 83 |
$dateColumn = 'o.expire_at'; |
| 84 |
|
| 85 |
if ($subscriptionType === Status::SUBSCRIPTION_CANCELED) { |
| 86 |
$dateColumn = 'o.canceled_at'; |
| 87 |
} |
| 88 |
|
| 89 |
$query->from('fct_subscriptions as o')->where('o.status', $subscriptionType) |
| 90 |
->whereBetween($dateColumn, [ |
| 91 |
$startDate->format('Y-m-d H:i:s'), |
| 92 |
$endDate->format('Y-m-d H:i:s'), |
| 93 |
]) |
| 94 |
->when($params['variationIds'], fn ($q) => $q->whereIn('o.variation_id', $params['variationIds'])); |
| 95 |
} |
| 96 |
|
| 97 |
$query->selectRaw("{$group['field']}, COUNT(o.id) as count")->groupByRaw($group['by']); |
| 98 |
|
| 99 |
$results = $query->get(); |
| 100 |
|
| 101 |
$keys = ['count']; |
| 102 |
$grouped = $this->getPeriodRange($startDate, $endDate, $group['key'], $keys); |
| 103 |
$totalSubscriptions = 0; |
| 104 |
|
| 105 |
foreach ($results as $row) { |
| 106 |
$grouped[$row->group] = [ |
| 107 |
'year' => (int) $row->year, |
| 108 |
'group' => $row->group, |
| 109 |
'count' => (int) $row->count, |
| 110 |
]; |
| 111 |
|
| 112 |
$totalSubscriptions += (int) $row->count; |
| 113 |
} |
| 114 |
|
| 115 |
return [ |
| 116 |
'grouped' => array_values($grouped), |
| 117 |
'totalSubscriptions' => $totalSubscriptions, |
| 118 |
]; |
| 119 |
} |
| 120 |
|
| 121 |
public function getRetentionData($params) |
| 122 |
{ |
| 123 |
$startDate = $params['startDate']; |
| 124 |
$endDate = $params['endDate']; |
| 125 |
|
| 126 |
// We want to iterate month by month |
| 127 |
$period = new \DatePeriod( |
| 128 |
$startDate, |
| 129 |
new \DateInterval('P1M'), |
| 130 |
$endDate |
| 131 |
); |
| 132 |
|
| 133 |
$data = []; |
| 134 |
|
| 135 |
foreach ($period as $dt) { |
| 136 |
$monthStart = $dt->format('Y-m-01 00:00:00'); |
| 137 |
$monthEnd = $dt->format('Y-m-t 23:59:59'); |
| 138 |
// For labels/keys, we use the end of the month as per requirement |
| 139 |
$labelDate = $dt->format('Y-m-t'); |
| 140 |
|
| 141 |
$stats = App::db()->table('fct_subscriptions') |
| 142 |
->selectRaw(" |
| 143 |
SUM( |
| 144 |
CASE |
| 145 |
WHEN created_at BETWEEN ? AND ? THEN 1 |
| 146 |
ELSE 0 |
| 147 |
END |
| 148 |
) as new_subscriptions, |
| 149 |
SUM( |
| 150 |
CASE |
| 151 |
WHEN created_at BETWEEN ? AND ? THEN |
| 152 |
CASE |
| 153 |
WHEN billing_interval = 'monthly' THEN recurring_amount |
| 154 |
WHEN billing_interval = 'yearly' THEN recurring_amount / 12 |
| 155 |
WHEN billing_interval = 'weekly' THEN (recurring_amount * 52) / 12 |
| 156 |
WHEN billing_interval = 'daily' THEN recurring_amount * 30 |
| 157 |
ELSE 0 |
| 158 |
END |
| 159 |
ELSE 0 |
| 160 |
END |
| 161 |
) / 100 as new_subscriptions_mrr, |
| 162 |
SUM( |
| 163 |
CASE |
| 164 |
WHEN created_at BETWEEN ? AND ? THEN recurring_amount |
| 165 |
ELSE 0 |
| 166 |
END |
| 167 |
) / 100 as period_gross, |
| 168 |
SUM( |
| 169 |
CASE |
| 170 |
WHEN canceled_at BETWEEN ? AND ? THEN 1 |
| 171 |
WHEN canceled_at IS NULL AND expire_at BETWEEN ? AND ? THEN 1 |
| 172 |
ELSE 0 |
| 173 |
END |
| 174 |
) as churned_subscriptions, |
| 175 |
SUM( |
| 176 |
CASE |
| 177 |
WHEN (canceled_at BETWEEN ? AND ?) OR (canceled_at IS NULL AND expire_at BETWEEN ? AND ?) THEN |
| 178 |
CASE |
| 179 |
WHEN billing_interval = 'monthly' THEN recurring_amount |
| 180 |
WHEN billing_interval = 'yearly' THEN recurring_amount / 12 |
| 181 |
WHEN billing_interval = 'weekly' THEN (recurring_amount * 52) / 12 |
| 182 |
WHEN billing_interval = 'daily' THEN recurring_amount * 30 |
| 183 |
ELSE 0 |
| 184 |
END |
| 185 |
ELSE 0 |
| 186 |
END |
| 187 |
) / 100 as churned_subscriptions_mrr, |
| 188 |
SUM( |
| 189 |
CASE |
| 190 |
WHEN created_at <= ? |
| 191 |
AND (canceled_at IS NULL OR canceled_at > ?) |
| 192 |
AND (expire_at IS NULL OR expire_at > ?) |
| 193 |
THEN 1 |
| 194 |
ELSE 0 |
| 195 |
END |
| 196 |
) as active_subscriptions, |
| 197 |
SUM( |
| 198 |
CASE |
| 199 |
WHEN created_at <= ? |
| 200 |
AND (canceled_at IS NULL OR canceled_at > ?) |
| 201 |
AND (expire_at IS NULL OR expire_at > ?) |
| 202 |
AND recurring_amount > 0 |
| 203 |
THEN 1 |
| 204 |
ELSE 0 |
| 205 |
END |
| 206 |
) as active_paid_subscriptions, |
| 207 |
SUM( |
| 208 |
CASE |
| 209 |
WHEN created_at <= ? |
| 210 |
AND (canceled_at IS NULL OR canceled_at > ?) |
| 211 |
AND (expire_at IS NULL OR expire_at > ?) |
| 212 |
AND recurring_amount = 0 |
| 213 |
THEN 1 |
| 214 |
ELSE 0 |
| 215 |
END |
| 216 |
) as active_free_subscriptions, |
| 217 |
SUM( |
| 218 |
CASE |
| 219 |
WHEN created_at <= ? |
| 220 |
AND (canceled_at IS NULL OR canceled_at > ?) |
| 221 |
AND (expire_at IS NULL OR expire_at > ?) |
| 222 |
THEN |
| 223 |
CASE |
| 224 |
WHEN billing_interval = 'monthly' THEN recurring_amount |
| 225 |
WHEN billing_interval = 'yearly' THEN recurring_amount / 12 |
| 226 |
WHEN billing_interval = 'weekly' THEN (recurring_amount * 52) / 12 |
| 227 |
WHEN billing_interval = 'daily' THEN recurring_amount * 30 |
| 228 |
ELSE 0 |
| 229 |
END |
| 230 |
ELSE 0 |
| 231 |
END |
| 232 |
) / 100 as mrr, |
| 233 |
SUM( |
| 234 |
CASE |
| 235 |
WHEN created_at <= ? |
| 236 |
AND (canceled_at IS NULL OR canceled_at > ?) |
| 237 |
AND (expire_at IS NULL OR expire_at > ?) |
| 238 |
THEN 1 |
| 239 |
ELSE 0 |
| 240 |
END |
| 241 |
) as start_subscriptions, |
| 242 |
SUM( |
| 243 |
CASE |
| 244 |
WHEN created_at <= ? |
| 245 |
AND (canceled_at IS NULL OR canceled_at > ?) |
| 246 |
AND (expire_at IS NULL OR expire_at > ?) |
| 247 |
THEN |
| 248 |
CASE |
| 249 |
WHEN billing_interval = 'monthly' THEN recurring_amount |
| 250 |
WHEN billing_interval = 'yearly' THEN recurring_amount / 12 |
| 251 |
WHEN billing_interval = 'weekly' THEN (recurring_amount * 52) / 12 |
| 252 |
WHEN billing_interval = 'daily' THEN recurring_amount * 30 |
| 253 |
ELSE 0 |
| 254 |
END |
| 255 |
ELSE 0 |
| 256 |
END |
| 257 |
) / 100 as start_mrr |
| 258 |
", [ |
| 259 |
$monthStart, $monthEnd, // new_subscriptions |
| 260 |
$monthStart, $monthEnd, // new_subscriptions_mrr |
| 261 |
$monthStart, $monthEnd, // period_gross |
| 262 |
$monthStart, $monthEnd, $monthStart, $monthEnd, // churned_subscriptions (canceled OR expired) |
| 263 |
$monthStart, $monthEnd, $monthStart, $monthEnd, // churned_subscriptions_mrr (canceled OR expired) |
| 264 |
$monthEnd, $monthEnd, $monthEnd, // active_subscriptions |
| 265 |
$monthEnd, $monthEnd, $monthEnd, // active_paid_subscriptions |
| 266 |
$monthEnd, $monthEnd, $monthEnd, // active_free_subscriptions |
| 267 |
$monthEnd, $monthEnd, $monthEnd, // mrr |
| 268 |
$monthStart, $monthStart, $monthStart, // start_subscriptions |
| 269 |
$monthStart, $monthStart, $monthStart // start_mrr |
| 270 |
]) |
| 271 |
->where('created_at', '<=', $monthEnd) // Optimization: Don't scan future rows |
| 272 |
// ->whereIn('status', Status::getValidableSubscriptionStatuses()) |
| 273 |
->whereNotIn('status', [Status::SUBSCRIPTION_PENDING, Status::SUBSCRIPTION_INTENDED]) |
| 274 |
->when(!empty($params['variationIds']), function ($q) use ($params) { |
| 275 |
$q->whereIn('variation_id', $params['variationIds']); |
| 276 |
}) |
| 277 |
->first(); |
| 278 |
|
| 279 |
$newSubscriptions = (int) $stats->new_subscriptions; |
| 280 |
$newSubscriptionsMrr = round((float) $stats->new_subscriptions_mrr, 2); |
| 281 |
$activeSubscriptions = (int) $stats->active_subscriptions; |
| 282 |
$startSubscriptions = (int) $stats->start_subscriptions; |
| 283 |
$mrr = round((float) $stats->mrr, 2); |
| 284 |
$startMrr = round((float) $stats->start_mrr, 2); |
| 285 |
|
| 286 |
// Retention Rate = ((End Count - New Count) / Start Count) * 100 |
| 287 |
$retentionRate = 0; |
| 288 |
if ($startSubscriptions > 0) { |
| 289 |
$retainedCount = $activeSubscriptions - $newSubscriptions; |
| 290 |
$retainedCount = max(0, $retainedCount); |
| 291 |
$retentionRate = ($retainedCount / $startSubscriptions) * 100; |
| 292 |
} |
| 293 |
|
| 294 |
// MRR Retention Rate = ((End MRR - New MRR) / Start MRR) * 100 |
| 295 |
$retentionRateMoney = 0; |
| 296 |
if ($startMrr > 0) { |
| 297 |
$retainedMrr = $mrr - $newSubscriptionsMrr; |
| 298 |
$retainedMrr = max(0, $retainedMrr); |
| 299 |
$retentionRateMoney = ($retainedMrr / $startMrr) * 100; |
| 300 |
} |
| 301 |
|
| 302 |
$data[] = [ |
| 303 |
'day' => $labelDate, |
| 304 |
'week' => date('Y-W', strtotime($labelDate)), |
| 305 |
'group' => $dt->format('Y-m'), |
| 306 |
'year' => $dt->format('Y'), |
| 307 |
'new_subscriptions' => $newSubscriptions, |
| 308 |
'new_subscriptions_mrr' => $newSubscriptionsMrr, |
| 309 |
'churned_subscriptions' => (int) $stats->churned_subscriptions, |
| 310 |
'churned_subscriptions_mrr' => round((float) $stats->churned_subscriptions_mrr, 2), |
| 311 |
'active_subscriptions' => (string) $activeSubscriptions, |
| 312 |
'active_paid_subscriptions' => (string) $stats->active_paid_subscriptions, |
| 313 |
'active_free_subscriptions' => (string) $stats->active_free_subscriptions, |
| 314 |
'mrr' => number_format($mrr, 2, '.', ''), |
| 315 |
'retention_rate' => round($retentionRate, 2), |
| 316 |
'retention_rate_money' => round($retentionRateMoney, 2), |
| 317 |
'period_gross' => round((float) $stats->period_gross, 2), |
| 318 |
'period_subscriptions' => $newSubscriptions, |
| 319 |
]; |
| 320 |
} |
| 321 |
|
| 322 |
return $data; |
| 323 |
} |
| 324 |
|
| 325 |
/** |
| 326 |
* Get cohort retention data from pre-calculated snapshots table |
| 327 |
* |
| 328 |
* Supports both monthly and yearly g * - Monthly: Uses period_offset 1, 2, 3... (each month) |
| 329 |
* - Yearly: Aggregates cohorts by year and uses period_offset 12, 24, 36... (each year) |
| 330 |
* |
| 331 |
* @param array $params |
| 332 |
* @return array |
| 333 |
*/ |
| 334 |
public function getCohortData($params = []) |
| 335 |
{ |
| 336 |
$startDate = $params['startDate'] ?? null; |
| 337 |
$endDate = $params['endDate'] ?? null; |
| 338 |
$productIds = $params['productIds'] ?? []; // Array of product IDs (converted from variation_ids) |
| 339 |
$groupBy = $params['groupBy'] ?? 'month'; // month or year |
| 340 |
$metric = $params['metric'] ?? 'mrr'; |
| 341 |
$maxPeriods = $params['maxPeriods'] ?? 12; |
| 342 |
|
| 343 |
if (!$startDate || !$endDate) { |
| 344 |
return []; |
| 345 |
} |
| 346 |
|
| 347 |
// Convert dates to YYYY-MM format for cohort filtering |
| 348 |
$startCohort = $startDate->format('Y-m'); |
| 349 |
$endCohort = $endDate->format('Y-m'); |
| 350 |
|
| 351 |
if ($groupBy === 'year') { |
| 352 |
return $this->getCohortDataByYear($startCohort, $endCohort, $productIds, $metric, $maxPeriods); |
| 353 |
} |
| 354 |
|
| 355 |
return $this->getCohortDataByMonth($startCohort, $endCohort, $productIds, $metric, $maxPeriods); |
| 356 |
} |
| 357 |
|
| 358 |
/** |
| 359 |
* Get cohort data grouped by month |
| 360 |
* |
| 361 |
* @param string $startCohort |
| 362 |
* @param string $endCohort |
| 363 |
* @param array $productIds Array of product IDs to filter by (empty = all products combined) |
| 364 |
* @param string $metric |
| 365 |
* @param int $maxPeriods |
| 366 |
* @return array |
| 367 |
*/ |
| 368 |
protected function getCohortDataByMonth($startCohort, $endCohort, $productIds, $metric, $maxPeriods) |
| 369 |
{ |
| 370 |
$query = App::db()->table('fct_retention_snapshots') |
| 371 |
->whereBetween('cohort', [$startCohort, $endCohort]) |
| 372 |
->where('period_offset', '<=', $maxPeriods) |
| 373 |
->orderBy('cohort', 'ASC') |
| 374 |
->orderBy('period_offset', 'ASC'); |
| 375 |
|
| 376 |
if (!empty($productIds)) { |
| 377 |
// Filter by specific product IDs and aggregate |
| 378 |
$query->selectRaw(" |
| 379 |
cohort, |
| 380 |
period_offset, |
| 381 |
SUM(cohort_customers) as cohort_customers, |
| 382 |
SUM(cohort_mrr) as cohort_mrr, |
| 383 |
SUM(retained_customers) as retained_customers, |
| 384 |
SUM(retained_mrr) as retained_mrr, |
| 385 |
SUM(churned_customers) as churned_customers |
| 386 |
") |
| 387 |
->whereIn('product_id', $productIds) |
| 388 |
->groupBy('cohort', 'period_offset'); |
| 389 |
} else { |
| 390 |
// Use pre-aggregated "all products" data (product_id IS NULL) |
| 391 |
$query->select([ |
| 392 |
'cohort', |
| 393 |
'period_offset', |
| 394 |
'cohort_customers', |
| 395 |
'cohort_mrr', |
| 396 |
'retained_customers', |
| 397 |
'retained_mrr', |
| 398 |
'churned_customers', |
| 399 |
'retention_rate_customers', |
| 400 |
'retention_rate_mrr', |
| 401 |
]) |
| 402 |
->whereNull('product_id'); |
| 403 |
} |
| 404 |
|
| 405 |
$snapshots = $query->get(); |
| 406 |
|
| 407 |
if ($snapshots->isEmpty()) { |
| 408 |
return $this->emptyResponse('month', $metric, $maxPeriods); |
| 409 |
} |
| 410 |
|
| 411 |
// If we aggregated by product_ids, we need to recalculate retention rates |
| 412 |
if (!empty($productIds)) { |
| 413 |
$snapshots = $snapshots->map(function ($snap) { |
| 414 |
$snap->retention_rate_customers = $snap->cohort_customers > 0 |
| 415 |
? round(($snap->retained_customers / $snap->cohort_customers) * 100, 2) |
| 416 |
: 0; |
| 417 |
$snap->retention_rate_mrr = $snap->cohort_mrr > 0 |
| 418 |
? round(($snap->retained_mrr / $snap->cohort_mrr) * 100, 2) |
| 419 |
: 0; |
| 420 |
return $snap; |
| 421 |
}); |
| 422 |
} |
| 423 |
|
| 424 |
return $this->buildCohortResponse($snapshots, 'month', $metric, $maxPeriods); |
| 425 |
} |
| 426 |
|
| 427 |
/** |
| 428 |
* Get cohort data grouped by year |
| 429 |
* |
| 430 |
* For yearly view, we use period_offset = 12 as the baseline (end of year 1, before first renewal) |
| 431 |
* and show period_offset 24, 36, 48... as Year 1, Year 2, Year 3 (after 1st, 2nd, 3rd renewal) |
| 432 |
* |
| 433 |
* This is because at period_offset = 12, yearly subscribers haven't had a renewal opportunity yet, |
| 434 |
* so they show ~100% retention. The actual churn happens after the first renewal at offset 12+. |
| 435 |
* |
| 436 |
* @param string $startCohort |
| 437 |
* @param string $endCohort |
| 438 |
* @param array $productIds Array of product IDs to filter by (empty = all products combined) |
| 439 |
* @param string $metric |
| 440 |
* @param int $maxPeriods |
| 441 |
* @return array |
| 442 |
*/ |
| 443 |
protected function getCohortDataByYear($startCohort, $endCohort, $productIds, $metric, $maxPeriods) |
| 444 |
{ |
| 445 |
// We need offsets 12, 24, 36... where 12 is baseline, 24 is Year 1, etc. |
| 446 |
// So max offset = (maxPeriods + 1) * 12 to include enough data |
| 447 |
$maxMonthOffset = ($maxPeriods + 1) * 12; |
| 448 |
|
| 449 |
$query = App::db()->table('fct_retention_snapshots') |
| 450 |
->whereBetween('cohort', [$startCohort, $endCohort]) |
| 451 |
->where('period_offset', '<=', $maxMonthOffset) |
| 452 |
->whereRaw('period_offset % 12 = 0') |
| 453 |
->where('period_offset', '>=', 12) // Start from 12 (end of year 1) |
| 454 |
->orderBy('cohort', 'ASC') |
| 455 |
->orderBy('period_offset', 'ASC'); |
| 456 |
|
| 457 |
if (!empty($productIds)) { |
| 458 |
// Filter by specific product IDs |
| 459 |
$query->select([ |
| 460 |
'cohort', |
| 461 |
'period_offset', |
| 462 |
'cohort_customers', |
| 463 |
'cohort_mrr', |
| 464 |
'retained_customers', |
| 465 |
'retained_mrr', |
| 466 |
'churned_customers', |
| 467 |
]) |
| 468 |
->whereIn('product_id', $productIds); |
| 469 |
} else { |
| 470 |
// Use pre-aggregated "all products" data |
| 471 |
$query->select([ |
| 472 |
'cohort', |
| 473 |
'period_offset', |
| 474 |
'cohort_customers', |
| 475 |
'cohort_mrr', |
| 476 |
'retained_customers', |
| 477 |
'retained_mrr', |
| 478 |
'churned_customers', |
| 479 |
'retention_rate_customers', |
| 480 |
'retention_rate_mrr', |
| 481 |
]) |
| 482 |
->whereNull('product_id'); |
| 483 |
} |
| 484 |
|
| 485 |
$snapshots = $query->get(); |
| 486 |
|
| 487 |
if ($snapshots->isEmpty()) { |
| 488 |
return $this->emptyResponse('year', $metric, $maxPeriods); |
| 489 |
} |
| 490 |
|
| 491 |
// Group by year, then by period_offset |
| 492 |
// period_offset 12 -> yearOffset 0 (baseline) |
| 493 |
// period_offset 24 -> yearOffset 1 (Year 1 - after 1st renewal) |
| 494 |
// period_offset 36 -> yearOffset 2 (Year 2 - after 2nd renewal) |
| 495 |
$yearlyData = []; |
| 496 |
|
| 497 |
foreach ($snapshots as $snapshot) { |
| 498 |
$cohortYear = substr($snapshot->cohort, 0, 4); |
| 499 |
// Shift: 12->0, 24->1, 36->2, etc. |
| 500 |
$yearOffset = ((int) $snapshot->period_offset / 12) - 1; |
| 501 |
|
| 502 |
if (!isset($yearlyData[$cohortYear])) { |
| 503 |
$yearlyData[$cohortYear] = []; |
| 504 |
} |
| 505 |
|
| 506 |
if (!isset($yearlyData[$cohortYear][$yearOffset])) { |
| 507 |
$yearlyData[$cohortYear][$yearOffset] = [ |
| 508 |
'cohort_customers' => 0, |
| 509 |
'cohort_mrr' => 0, |
| 510 |
'retained_customers' => 0, |
| 511 |
'retained_mrr' => 0, |
| 512 |
'churned_customers' => 0, |
| 513 |
]; |
| 514 |
} |
| 515 |
|
| 516 |
// Aggregate: sum up all monthly cohorts for this year at this offset |
| 517 |
$yearlyData[$cohortYear][$yearOffset]['cohort_customers'] += (int) $snapshot->cohort_customers; |
| 518 |
$yearlyData[$cohortYear][$yearOffset]['cohort_mrr'] += (int) $snapshot->cohort_mrr; |
| 519 |
$yearlyData[$cohortYear][$yearOffset]['retained_customers'] += (int) $snapshot->retained_customers; |
| 520 |
$yearlyData[$cohortYear][$yearOffset]['retained_mrr'] += (int) $snapshot->retained_mrr; |
| 521 |
$yearlyData[$cohortYear][$yearOffset]['churned_customers'] += (int) $snapshot->churned_customers; |
| 522 |
} |
| 523 |
|
| 524 |
// Convert to cohortGroups format expected by buildCohortResponseFromGroups |
| 525 |
$cohortGroups = []; |
| 526 |
|
| 527 |
foreach ($yearlyData as $cohortYear => $offsets) { |
| 528 |
$cohortGroups[$cohortYear] = []; |
| 529 |
|
| 530 |
foreach ($offsets as $yearOffset => $data) { |
| 531 |
// Calculate retention rates from aggregated data |
| 532 |
$retentionRateCustomers = $data['cohort_customers'] > 0 |
| 533 |
? round(($data['retained_customers'] / $data['cohort_customers']) * 100, 2) |
| 534 |
: 0; |
| 535 |
$retentionRateMrr = $data['cohort_mrr'] > 0 |
| 536 |
? round(($data['retained_mrr'] / $data['cohort_mrr']) * 100, 2) |
| 537 |
: 0; |
| 538 |
|
| 539 |
$cohortGroups[$cohortYear][] = (object) [ |
| 540 |
'cohort' => $cohortYear, |
| 541 |
'period_offset' => $yearOffset, |
| 542 |
'cohort_customers' => $data['cohort_customers'], |
| 543 |
'cohort_mrr' => $data['cohort_mrr'], |
| 544 |
'retained_customers' => $data['retained_customers'], |
| 545 |
'retained_mrr' => $data['retained_mrr'], |
| 546 |
'churned_customers' => $data['churned_customers'], |
| 547 |
'retention_rate_customers' => $retentionRateCustomers, |
| 548 |
'retention_rate_mrr' => $retentionRateMrr, |
| 549 |
]; |
| 550 |
} |
| 551 |
} |
| 552 |
|
| 553 |
return $this->buildCohortResponseFromGroups($cohortGroups, 'year', $metric, $maxPeriods); |
| 554 |
} |
| 555 |
|
| 556 |
/** |
| 557 |
* Build cohort response from raw snapshots |
| 558 |
*/ |
| 559 |
protected function buildCohortResponse($snapshots, $groupBy, $metric, $maxPeriods) |
| 560 |
{ |
| 561 |
$cohortGroups = []; |
| 562 |
foreach ($snapshots as $snapshot) { |
| 563 |
$cohort = $snapshot->cohort; |
| 564 |
if (!isset($cohortGroups[$cohort])) { |
| 565 |
$cohortGroups[$cohort] = []; |
| 566 |
} |
| 567 |
$cohortGroups[$cohort][] = $snapshot; |
| 568 |
} |
| 569 |
|
| 570 |
return $this->buildCohortResponseFromGroups($cohortGroups, $groupBy, $metric, $maxPeriods); |
| 571 |
} |
| 572 |
|
| 573 |
/** |
| 574 |
* Build cohort response from grouped data |
| 575 |
*/ |
| 576 |
protected function buildCohortResponseFromGroups($cohortGroups, $groupBy, $metric, $maxPeriods) |
| 577 |
{ |
| 578 |
$cohortData = []; |
| 579 |
$weightedData = []; |
| 580 |
|
| 581 |
foreach ($cohortGroups as $cohortPeriod => $snapshots) { |
| 582 |
// Get baseline from offset 0 |
| 583 |
$baselineSnapshot = null; |
| 584 |
foreach ($snapshots as $snap) { |
| 585 |
if ((int) $snap->period_offset === 0) { |
| 586 |
$baselineSnapshot = $snap; |
| 587 |
break; |
| 588 |
} |
| 589 |
} |
| 590 |
|
| 591 |
if (!$baselineSnapshot) { |
| 592 |
$baselineSnapshot = $snapshots[0]; |
| 593 |
} |
| 594 |
|
| 595 |
$cohortStartMrr = round((float) $baselineSnapshot->cohort_mrr / 100, 2); |
| 596 |
$cohortStartCount = (int) $baselineSnapshot->cohort_customers; |
| 597 |
|
| 598 |
$periodData = []; |
| 599 |
$snapshotsByOffset = []; |
| 600 |
|
| 601 |
foreach ($snapshots as $snap) { |
| 602 |
$snapshotsByOffset[(int) $snap->period_offset] = $snap; |
| 603 |
} |
| 604 |
|
| 605 |
$prevRetainedMrr = $cohortStartMrr; |
| 606 |
$prevRetainedCount = $cohortStartCount; |
| 607 |
|
| 608 |
for ($offset = 1; $offset <= $maxPeriods; $offset++) { |
| 609 |
if (!isset($snapshotsByOffset[$offset])) { |
| 610 |
$periodData[] = [ |
| 611 |
'offset' => $offset, |
| 612 |
'retained_mrr' => null, |
| 613 |
'retained_count' => null, |
| 614 |
'churned_mrr' => null, |
| 615 |
'churned_count' => null, |
| 616 |
'retention_rate_mrr' => null, |
| 617 |
'retention_rate_count' => null, |
| 618 |
'retention_rate_previous_mrr' => null, |
| 619 |
'retention_rate_previous_count' => null, |
| 620 |
'churn_rate_total_mrr' => null, |
| 621 |
'churn_rate_total_count' => null, |
| 622 |
]; |
| 623 |
continue; |
| 624 |
} |
| 625 |
|
| 626 |
$snap = $snapshotsByOffset[$offset]; |
| 627 |
|
| 628 |
$retainedMrr = round((float) $snap->retained_mrr / 100, 2); |
| 629 |
$retainedCount = (int) $snap->retained_customers; |
| 630 |
$churnedCount = (int) $snap->churned_customers; |
| 631 |
$churnedMrr = round($cohortStartMrr - $retainedMrr, 2); |
| 632 |
|
| 633 |
$retentionRateMrr = (float) $snap->retention_rate_mrr; |
| 634 |
$retentionRateCount = (float) $snap->retention_rate_customers; |
| 635 |
|
| 636 |
$retentionRatePrevMrr = $prevRetainedMrr > 0 |
| 637 |
? round(($retainedMrr / $prevRetainedMrr) * 100, 2) |
| 638 |
: 0; |
| 639 |
$retentionRatePrevCount = $prevRetainedCount > 0 |
| 640 |
? round(($retainedCount / $prevRetainedCount) * 100, 2) |
| 641 |
: 0; |
| 642 |
|
| 643 |
$churnRateTotalMrr = round(100 - $retentionRateMrr, 2); |
| 644 |
$churnRateTotalCount = round(100 - $retentionRateCount, 2); |
| 645 |
|
| 646 |
$periodData[] = [ |
| 647 |
'offset' => $offset, |
| 648 |
'retained_mrr' => $retainedMrr, |
| 649 |
'retained_count' => $retainedCount, |
| 650 |
'churned_mrr' => max(0, $churnedMrr), |
| 651 |
'churned_count' => $churnedCount, |
| 652 |
'retention_rate_mrr' => $retentionRateMrr, |
| 653 |
'retention_rate_count' => $retentionRateCount, |
| 654 |
'retention_rate_previous_mrr' => $retentionRatePrevMrr, |
| 655 |
'retention_rate_previous_count' => $retentionRatePrevCount, |
| 656 |
'churn_rate_total_mrr' => $churnRateTotalMrr, |
| 657 |
'churn_rate_total_count' => $churnRateTotalCount, |
| 658 |
]; |
| 659 |
|
| 660 |
if (!isset($weightedData[$offset])) { |
| 661 |
$weightedData[$offset] = [ |
| 662 |
'total_start_mrr' => 0, |
| 663 |
'total_start_count' => 0, |
| 664 |
'total_retained_mrr' => 0, |
| 665 |
'total_retained_count' => 0, |
| 666 |
'total_prev_retained_mrr' => 0, |
| 667 |
'total_prev_retained_count' => 0, |
| 668 |
]; |
| 669 |
} |
| 670 |
$weightedData[$offset]['total_start_mrr'] += $cohortStartMrr; |
| 671 |
$weightedData[$offset]['total_start_count'] += $cohortStartCount; |
| 672 |
$weightedData[$offset]['total_retained_mrr'] += $retainedMrr; |
| 673 |
$weightedData[$offset]['total_retained_count'] += $retainedCount; |
| 674 |
$weightedData[$offset]['total_prev_retained_mrr'] += $prevRetainedMrr; |
| 675 |
$weightedData[$offset]['total_prev_retained_count'] += $prevRetainedCount; |
| 676 |
|
| 677 |
$prevRetainedMrr = $retainedMrr; |
| 678 |
$prevRetainedCount = $retainedCount; |
| 679 |
} |
| 680 |
|
| 681 |
$cohortData[] = [ |
| 682 |
'cohort' => $cohortPeriod, |
| 683 |
'start_mrr' => $cohortStartMrr, |
| 684 |
'start_count' => $cohortStartCount, |
| 685 |
'periods' => $periodData, |
| 686 |
]; |
| 687 |
} |
| 688 |
|
| 689 |
$weightedAverages = $this->calculateWeightedAverages($weightedData); |
| 690 |
|
| 691 |
return [ |
| 692 |
'cohorts' => $cohortData, |
| 693 |
'weighted_averages' => $weightedAverages, |
| 694 |
'group_by' => $groupBy, |
| 695 |
'metric' => $metric, |
| 696 |
'max_periods' => $maxPeriods, |
| 697 |
]; |
| 698 |
} |
| 699 |
|
| 700 |
/** |
| 701 |
* Calculate weighted averages from accumulated data |
| 702 |
*/ |
| 703 |
protected function calculateWeightedAverages($weightedData) |
| 704 |
{ |
| 705 |
$weightedAverages = []; |
| 706 |
|
| 707 |
foreach ($weightedData as $offset => $data) { |
| 708 |
$avgMrr = $data['total_start_mrr'] > 0 |
| 709 |
? ($data['total_retained_mrr'] / $data['total_start_mrr']) * 100 |
| 710 |
: 0; |
| 711 |
$avgCount = $data['total_start_count'] > 0 |
| 712 |
? ($data['total_retained_count'] / $data['total_start_count']) * 100 |
| 713 |
: 0; |
| 714 |
|
| 715 |
$avgPrevMrr = $data['total_prev_retained_mrr'] > 0 |
| 716 |
? ($data['total_retained_mrr'] / $data['total_prev_retained_mrr']) * 100 |
| 717 |
: 0; |
| 718 |
$avgPrevCount = $data['total_prev_retained_count'] > 0 |
| 719 |
? ($data['total_retained_count'] / $data['total_prev_retained_count']) * 100 |
| 720 |
: 0; |
| 721 |
|
| 722 |
$avgChurnMrr = 100 - $avgMrr; |
| 723 |
$avgChurnCount = 100 - $avgCount; |
| 724 |
|
| 725 |
$weightedAverages[] = [ |
| 726 |
'offset' => $offset, |
| 727 |
'weighted_avg_mrr' => round($avgMrr, 2), |
| 728 |
'weighted_avg_count' => round($avgCount, 2), |
| 729 |
'weighted_avg_prev_mrr' => round($avgPrevMrr, 2), |
| 730 |
'weighted_avg_prev_count' => round($avgPrevCount, 2), |
| 731 |
'weighted_avg_churn_mrr' => round($avgChurnMrr, 2), |
| 732 |
'weighted_avg_churn_count' => round($avgChurnCount, 2), |
| 733 |
]; |
| 734 |
} |
| 735 |
|
| 736 |
return $weightedAverages; |
| 737 |
} |
| 738 |
|
| 739 |
/** |
| 740 |
* Return empty response structure |
| 741 |
*/ |
| 742 |
protected function emptyResponse($groupBy, $metric, $maxPeriods) |
| 743 |
{ |
| 744 |
return [ |
| 745 |
'cohorts' => [], |
| 746 |
'weighted_averages' => [], |
| 747 |
'group_by' => $groupBy, |
| 748 |
'metric' => $metric, |
| 749 |
'max_periods' => $maxPeriods, |
| 750 |
]; |
| 751 |
} |
| 752 |
} |
| 753 |
|
