Export
1 month ago
NewsletterStatistics.php
3 years ago
NewsletterStatisticsRepository.php
3 days ago
WooCommerceRevenue.php
3 years ago
index.php
3 years ago
NewsletterStatisticsRepository.php
420 lines
| 1 | <?php // phpcs:ignore SlevomatCodingStandard.TypeHints.DeclareStrictTypes.DeclareStrictTypesMissing |
| 2 | |
| 3 | namespace MailPoet\Newsletter\Statistics; |
| 4 | |
| 5 | if (!defined('ABSPATH')) exit; |
| 6 | |
| 7 | |
| 8 | use MailPoet\Doctrine\Repository; |
| 9 | use MailPoet\Entities\NewsletterEntity; |
| 10 | use MailPoet\Entities\ScheduledTaskEntity; |
| 11 | use MailPoet\Entities\SendingQueueEntity; |
| 12 | use MailPoet\Entities\StatisticsBounceEntity; |
| 13 | use MailPoet\Entities\StatisticsClickEntity; |
| 14 | use MailPoet\Entities\StatisticsNewsletterEntity; |
| 15 | use MailPoet\Entities\StatisticsOpenEntity; |
| 16 | use MailPoet\Entities\StatisticsUnsubscribeEntity; |
| 17 | use MailPoet\Entities\StatisticsWooCommercePurchaseEntity; |
| 18 | use MailPoet\Entities\SubscriberEntity; |
| 19 | use MailPoet\Entities\UserAgentEntity; |
| 20 | use MailPoet\Settings\TrackingConfig; |
| 21 | use MailPoet\WooCommerce\Helper as WCHelper; |
| 22 | use MailPoet\WooCommerce\OrderAttributionRevenueReader; |
| 23 | use MailPoetVendor\Doctrine\ORM\EntityManager; |
| 24 | use MailPoetVendor\Doctrine\ORM\Query\Expr\Join; |
| 25 | use MailPoetVendor\Doctrine\ORM\QueryBuilder; |
| 26 | use MailPoetVendor\Doctrine\ORM\UnexpectedResultException; |
| 27 | |
| 28 | /** |
| 29 | * @extends Repository<NewsletterEntity> |
| 30 | */ |
| 31 | class NewsletterStatisticsRepository extends Repository { |
| 32 | /** |
| 33 | * Emails that are sent again for every trigger instead of once as a campaign, so they |
| 34 | * keep adding sending queues and scheduled tasks for as long as they stay active. |
| 35 | */ |
| 36 | private const TYPES_SENT_REPEATEDLY = [ |
| 37 | NewsletterEntity::TYPE_WELCOME, |
| 38 | NewsletterEntity::TYPE_AUTOMATIC, |
| 39 | NewsletterEntity::TYPE_AUTOMATION, |
| 40 | NewsletterEntity::TYPE_AUTOMATION_TRANSACTIONAL, |
| 41 | NewsletterEntity::TYPE_AUTOMATION_NOTIFICATION, |
| 42 | NewsletterEntity::TYPE_RE_ENGAGEMENT, |
| 43 | ]; |
| 44 | |
| 45 | /** @var WCHelper */ |
| 46 | private $wcHelper; |
| 47 | |
| 48 | /** @var TrackingConfig */ |
| 49 | private $trackingConfig; |
| 50 | |
| 51 | /** @var OrderAttributionRevenueReader */ |
| 52 | private $orderAttributionRevenueReader; |
| 53 | |
| 54 | public function __construct( |
| 55 | EntityManager $entityManager, |
| 56 | WCHelper $wcHelper, |
| 57 | TrackingConfig $trackingConfig, |
| 58 | OrderAttributionRevenueReader $orderAttributionRevenueReader |
| 59 | ) { |
| 60 | parent::__construct($entityManager); |
| 61 | $this->wcHelper = $wcHelper; |
| 62 | $this->trackingConfig = $trackingConfig; |
| 63 | $this->orderAttributionRevenueReader = $orderAttributionRevenueReader; |
| 64 | } |
| 65 | |
| 66 | protected function getEntityClassName() { |
| 67 | return NewsletterEntity::class; |
| 68 | } |
| 69 | |
| 70 | public function getStatistics(NewsletterEntity $newsletter): NewsletterStatistics { |
| 71 | $stats = new NewsletterStatistics( |
| 72 | $this->getStatisticsClickCount($newsletter), |
| 73 | $this->getStatisticsOpenCount($newsletter), |
| 74 | $this->getStatisticsUnsubscribeCount($newsletter), |
| 75 | $this->getStatisticsBounceCount($newsletter), |
| 76 | $this->getTotalSentCount($newsletter), |
| 77 | $this->getWooCommerceRevenue($newsletter) |
| 78 | ); |
| 79 | $stats->setMachineOpenCount($this->getStatisticsMachineOpenCount($newsletter)); |
| 80 | return $stats; |
| 81 | } |
| 82 | |
| 83 | /** |
| 84 | * @param NewsletterEntity[] $newsletters |
| 85 | * @return NewsletterStatistics[] |
| 86 | */ |
| 87 | public function getBatchStatistics( |
| 88 | array $newsletters, |
| 89 | ?\DateTimeImmutable $from = null, |
| 90 | ?\DateTimeImmutable $to = null, |
| 91 | array $include = [ |
| 92 | 'totals', |
| 93 | StatisticsClickEntity::class, |
| 94 | StatisticsOpenEntity::class, |
| 95 | StatisticsUnsubscribeEntity::class, |
| 96 | StatisticsBounceEntity::class, |
| 97 | WooCommerceRevenue::class, |
| 98 | ] |
| 99 | ): array { |
| 100 | |
| 101 | $totalSentCounts = in_array('totals', $include, true) ? $this->getTotalSentCounts($newsletters, $from, $to) : []; |
| 102 | $clickCounts = in_array(StatisticsClickEntity::class, $include, true) ? $this->getStatisticCounts(StatisticsClickEntity::class, $newsletters, $from, $to) : []; |
| 103 | $openCounts = in_array(StatisticsOpenEntity::class, $include, true) ? $this->getStatisticCounts(StatisticsOpenEntity::class, $newsletters, $from, $to) : []; |
| 104 | $unsubscribeCounts = in_array(StatisticsUnsubscribeEntity::class, $include, true) ? $this->getStatisticCounts(StatisticsUnsubscribeEntity::class, $newsletters, $from, $to) : []; |
| 105 | $bounceCounts = in_array(StatisticsBounceEntity::class, $include, true) ? $this->getStatisticCounts(StatisticsBounceEntity::class, $newsletters, $from, $to) : []; |
| 106 | $wooCommerceRevenues = in_array(WooCommerceRevenue::class, $include, true) ? $this->getWooCommerceRevenues($newsletters, $from, $to) : []; |
| 107 | |
| 108 | $statistics = []; |
| 109 | foreach ($newsletters as $newsletter) { |
| 110 | $id = $newsletter->getId(); |
| 111 | $statistics[$id] = new NewsletterStatistics( |
| 112 | $clickCounts[$id] ?? 0, |
| 113 | $openCounts[$id] ?? 0, |
| 114 | $unsubscribeCounts[$id] ?? 0, |
| 115 | $bounceCounts[$id] ?? 0, |
| 116 | $totalSentCounts[$id] ?? 0, |
| 117 | $wooCommerceRevenues[$id] ?? null |
| 118 | ); |
| 119 | } |
| 120 | return $statistics; |
| 121 | } |
| 122 | |
| 123 | public function getTotalSentCount(NewsletterEntity $newsletter): int { |
| 124 | $counts = $this->getTotalSentCounts([$newsletter]); |
| 125 | return $counts[$newsletter->getId()] ?? 0; |
| 126 | } |
| 127 | |
| 128 | public function getStatisticsClickCount(NewsletterEntity $newsletter): int { |
| 129 | $counts = $this->getStatisticCounts(StatisticsClickEntity::class, [$newsletter]); |
| 130 | return $counts[$newsletter->getId()] ?? 0; |
| 131 | } |
| 132 | |
| 133 | public function getStatisticsOpenCount(NewsletterEntity $newsletter): int { |
| 134 | $counts = $this->getStatisticCounts(StatisticsOpenEntity::class, [$newsletter]); |
| 135 | return $counts[$newsletter->getId()] ?? 0; |
| 136 | } |
| 137 | |
| 138 | public function getStatisticsMachineOpenCount(NewsletterEntity $newsletter): int { |
| 139 | $qb = $this->getStatisticsQuery(StatisticsOpenEntity::class, [$newsletter]); |
| 140 | $result = $qb->andWhere('(stats.userAgentType = :userAgentType)') |
| 141 | ->setParameter('userAgentType', UserAgentEntity::USER_AGENT_TYPE_MACHINE) |
| 142 | ->getQuery() |
| 143 | ->getOneOrNullResult(); |
| 144 | |
| 145 | if (empty($result)) return 0; |
| 146 | return $result['cnt'] ?? 0; |
| 147 | } |
| 148 | |
| 149 | /** |
| 150 | * @param SubscriberEntity $subscriber |
| 151 | * @param int|null $limit |
| 152 | * @param int|null $offset |
| 153 | * @return list<array{newsletter_id: mixed, newsletter_rendered_subject: string|null, opened_at: \DateTimeInterface|null, sent_at: \DateTimeInterface}> |
| 154 | */ |
| 155 | public function getAllForSubscriber( |
| 156 | SubscriberEntity $subscriber, |
| 157 | ?int $limit = null, |
| 158 | ?int $offset = null |
| 159 | ): array { |
| 160 | return $this->entityManager->createQueryBuilder() |
| 161 | ->select('IDENTITY(statistics.newsletter) AS newsletter_id') |
| 162 | ->addSelect('opens.createdAt AS opened_at') |
| 163 | ->addSelect('queue.newsletterRenderedSubject AS newsletter_rendered_subject') |
| 164 | ->addSelect('statistics.sentAt AS sent_at') |
| 165 | ->from(StatisticsNewsletterEntity::class, 'statistics') |
| 166 | ->join(SendingQueueEntity::class, 'queue', Join::WITH, 'statistics.queue = queue') |
| 167 | ->leftJoin( |
| 168 | StatisticsOpenEntity::class, |
| 169 | 'opens', |
| 170 | Join::WITH, |
| 171 | 'statistics.newsletter = opens.newsletter AND statistics.subscriber = opens.subscriber' |
| 172 | ) |
| 173 | ->where('statistics.subscriber = :subscriber') |
| 174 | ->setParameter('subscriber', $subscriber) |
| 175 | ->addOrderBy('newsletter_id') |
| 176 | ->setMaxResults($limit) |
| 177 | ->setFirstResult($offset) |
| 178 | ->getQuery() |
| 179 | ->getResult(); |
| 180 | } |
| 181 | |
| 182 | public function getStatisticsUnsubscribeCount(NewsletterEntity $newsletter): int { |
| 183 | $counts = $this->getStatisticCounts(StatisticsUnsubscribeEntity::class, [$newsletter]); |
| 184 | return $counts[$newsletter->getId()] ?? 0; |
| 185 | } |
| 186 | |
| 187 | public function getStatisticsBounceCount(NewsletterEntity $newsletter): int { |
| 188 | $counts = $this->getStatisticCounts(StatisticsBounceEntity::class, [$newsletter]); |
| 189 | return $counts[$newsletter->getId()] ?? 0; |
| 190 | } |
| 191 | |
| 192 | public function getWooCommerceRevenue(NewsletterEntity $newsletter) { |
| 193 | $revenues = $this->getWooCommerceRevenues([$newsletter]); |
| 194 | return $revenues[$newsletter->getId()] ?? null; |
| 195 | } |
| 196 | |
| 197 | /** |
| 198 | * @param NewsletterEntity $newsletter |
| 199 | * @return int |
| 200 | */ |
| 201 | public function getChildrenCount(NewsletterEntity $newsletter) { |
| 202 | try { |
| 203 | return (int)$this->entityManager |
| 204 | ->createQueryBuilder() |
| 205 | ->select('COUNT(n.id) as cnt') |
| 206 | ->from(NewsletterEntity::class, 'n') |
| 207 | ->where('n.parent = :newsletter') |
| 208 | ->setParameter('newsletter', $newsletter) |
| 209 | ->getQuery() |
| 210 | ->getSingleScalarResult(); |
| 211 | } catch (UnexpectedResultException $e) { |
| 212 | return 0; |
| 213 | } |
| 214 | } |
| 215 | |
| 216 | private function getTotalSentCounts(array $newsletters, ?\DateTimeImmutable $from = null, ?\DateTimeImmutable $to = null): array { |
| 217 | $sentRepeatedly = []; |
| 218 | $sentAsCampaign = []; |
| 219 | foreach ($newsletters as $newsletter) { |
| 220 | if (in_array($newsletter->getType(), self::TYPES_SENT_REPEATEDLY, true)) { |
| 221 | $sentRepeatedly[] = $newsletter; |
| 222 | } else { |
| 223 | $sentAsCampaign[] = $newsletter; |
| 224 | } |
| 225 | } |
| 226 | |
| 227 | // no key collisions, a newsletter belongs to exactly one group |
| 228 | return $this->getQueuedSentCounts($sentAsCampaign, $from, $to) |
| 229 | + $this->getRecordedSentCounts($sentRepeatedly, $from, $to); |
| 230 | } |
| 231 | |
| 232 | /** |
| 233 | * Counts sends from the sending queues, which hold one row per sending run. |
| 234 | */ |
| 235 | private function getQueuedSentCounts(array $newsletters, ?\DateTimeImmutable $from, ?\DateTimeImmutable $to): array { |
| 236 | if (!$newsletters) { |
| 237 | return []; |
| 238 | } |
| 239 | |
| 240 | $query = $this->doctrineRepository |
| 241 | ->createQueryBuilder('n') |
| 242 | ->select('n.id, SUM(q.countProcessed) AS cnt') |
| 243 | ->join('n.queues', 'q') |
| 244 | ->join('q.task', 't') |
| 245 | ->where('t.status = :status') |
| 246 | ->setParameter('status', ScheduledTaskEntity::STATUS_COMPLETED) |
| 247 | ->andWhere('q.newsletter IN (:newsletters)') |
| 248 | ->setParameter('newsletters', $newsletters) |
| 249 | ->groupBy('n.id'); |
| 250 | |
| 251 | if ($from && $to) { |
| 252 | $query->andWhere('q.createdAt BETWEEN :from AND :to') |
| 253 | ->setParameter('from', $from) |
| 254 | ->setParameter('to', $to); |
| 255 | } elseif ($from && $to === null) { |
| 256 | $query->andWhere('q.createdAt >= :from') |
| 257 | ->setParameter('from', $from); |
| 258 | } elseif ($from === null && $to) { |
| 259 | $query->andWhere('q.createdAt <= :to') |
| 260 | ->setParameter('to', $to); |
| 261 | } |
| 262 | |
| 263 | $results = $query->getQuery() |
| 264 | ->getResult(); |
| 265 | |
| 266 | $counts = []; |
| 267 | foreach ($results ?: [] as $result) { |
| 268 | $counts[(int)$result['id']] = (int)$result['cnt']; |
| 269 | } |
| 270 | return $counts; |
| 271 | } |
| 272 | |
| 273 | /** |
| 274 | * Counts sends from the sending statistics, which hold one row per email actually sent. |
| 275 | * |
| 276 | * Counting a repeatedly sent email through its queues instead would make the total depend |
| 277 | * on a chain of rows that grows for the lifetime of the email, while the opens and clicks |
| 278 | * measured against that total are only ever removed along with the newsletter itself. The |
| 279 | * sending statistics share that same lifecycle, so both sides of a rate stay consistent |
| 280 | * even where the queue chain has lost rows. |
| 281 | */ |
| 282 | private function getRecordedSentCounts(array $newsletters, ?\DateTimeImmutable $from, ?\DateTimeImmutable $to): array { |
| 283 | if (!$newsletters) { |
| 284 | return []; |
| 285 | } |
| 286 | |
| 287 | $query = $this->entityManager->createQueryBuilder() |
| 288 | ->select('IDENTITY(stats.newsletter) AS id, COUNT(stats.id) AS cnt') |
| 289 | ->from(StatisticsNewsletterEntity::class, 'stats') |
| 290 | ->where('stats.newsletter IN (:newsletters)') |
| 291 | ->setParameter('newsletters', $newsletters) |
| 292 | ->groupBy('stats.newsletter'); |
| 293 | |
| 294 | if ($from && $to) { |
| 295 | $query->andWhere('stats.sentAt BETWEEN :from AND :to') |
| 296 | ->setParameter('from', $from) |
| 297 | ->setParameter('to', $to); |
| 298 | } elseif ($from && $to === null) { |
| 299 | $query->andWhere('stats.sentAt >= :from') |
| 300 | ->setParameter('from', $from); |
| 301 | } elseif ($from === null && $to) { |
| 302 | $query->andWhere('stats.sentAt <= :to') |
| 303 | ->setParameter('to', $to); |
| 304 | } |
| 305 | |
| 306 | $results = $query->getQuery() |
| 307 | ->getResult(); |
| 308 | |
| 309 | $counts = []; |
| 310 | foreach ($results ?: [] as $result) { |
| 311 | $counts[(int)$result['id']] = (int)$result['cnt']; |
| 312 | } |
| 313 | return $counts; |
| 314 | } |
| 315 | |
| 316 | private function getStatisticCounts(string $statisticsEntityName, array $newsletters, ?\DateTimeImmutable $from = null, ?\DateTimeImmutable $to = null): array { |
| 317 | $qb = $this->getStatisticsQuery($statisticsEntityName, $newsletters); |
| 318 | if ( |
| 319 | $statisticsEntityName === StatisticsClickEntity::class |
| 320 | || ($statisticsEntityName === StatisticsOpenEntity::class && $this->trackingConfig->areOpensSeparated()) |
| 321 | ) { |
| 322 | $qb->andWhere('(stats.userAgentType = :userAgentType) OR (stats.userAgentType IS NULL)') |
| 323 | ->setParameter('userAgentType', UserAgentEntity::USER_AGENT_TYPE_HUMAN); |
| 324 | } |
| 325 | if ($from && $to) { |
| 326 | $qb->andWhere('stats.createdAt BETWEEN :from AND :to') |
| 327 | ->setParameter('from', $from) |
| 328 | ->setParameter('to', $to); |
| 329 | } elseif ($from && $to === null) { |
| 330 | $qb->andWhere('stats.createdAt >= :from') |
| 331 | ->setParameter('from', $from); |
| 332 | } elseif ($from === null && $to) { |
| 333 | $qb->andWhere('stats.createdAt <= :to') |
| 334 | ->setParameter('to', $to); |
| 335 | } |
| 336 | |
| 337 | $results = $qb |
| 338 | ->getQuery() |
| 339 | ->getResult(); |
| 340 | |
| 341 | $counts = []; |
| 342 | foreach ($results ?: [] as $result) { |
| 343 | $counts[(int)$result['id']] = (int)$result['cnt']; |
| 344 | } |
| 345 | return $counts; |
| 346 | } |
| 347 | |
| 348 | private function getStatisticsQuery(string $statisticsEntityName, array $newsletters): QueryBuilder { |
| 349 | return $this->entityManager->createQueryBuilder() |
| 350 | ->select('IDENTITY(stats.newsletter) AS id, COUNT(DISTINCT stats.subscriber) as cnt') |
| 351 | ->from($statisticsEntityName, 'stats') |
| 352 | ->where('stats.newsletter IN (:newsletters)') |
| 353 | ->groupBy('stats.newsletter') |
| 354 | ->setParameter('newsletters', $newsletters); |
| 355 | } |
| 356 | |
| 357 | private function getWooCommerceRevenues(array $newsletters, ?\DateTimeImmutable $from = null, ?\DateTimeImmutable $to = null) { |
| 358 | if (!$this->wcHelper->isWooCommerceActive()) { |
| 359 | return null; |
| 360 | } |
| 361 | |
| 362 | $newsletterIds = array_map(function(NewsletterEntity $newsletter): int { |
| 363 | return (int)$newsletter->getId(); |
| 364 | }, $newsletters); |
| 365 | $revenueStatus = $this->wcHelper->getPurchaseStates(); |
| 366 | $currency = $this->wcHelper->getWoocommerceCurrency(); |
| 367 | $wooBackedRevenues = $this->orderAttributionRevenueReader->getNewsletterRevenues($newsletterIds, $from, $to); |
| 368 | if (is_array($wooBackedRevenues)) { |
| 369 | $revenues = []; |
| 370 | foreach ($wooBackedRevenues as $newsletterId => $result) { |
| 371 | $revenues[(int)$newsletterId] = new WooCommerceRevenue( |
| 372 | $currency, |
| 373 | (float)$result['total'], |
| 374 | (int)$result['count'], |
| 375 | $this->wcHelper |
| 376 | ); |
| 377 | } |
| 378 | return $revenues; |
| 379 | } |
| 380 | |
| 381 | $query = $this->entityManager |
| 382 | ->createQueryBuilder() |
| 383 | ->select('IDENTITY(stats.newsletter) AS id, SUM(stats.orderPriceTotal) AS total, COUNT(stats.id) AS cnt') |
| 384 | ->from(StatisticsWooCommercePurchaseEntity::class, 'stats') |
| 385 | ->where('stats.newsletter IN (:newsletters)') |
| 386 | ->andWhere('stats.orderCurrency = :currency') |
| 387 | ->andWhere('stats.status IN (:revenue_status)') |
| 388 | ->setParameter('newsletters', $newsletters) |
| 389 | ->setParameter('currency', $currency) |
| 390 | ->setParameter('revenue_status', $revenueStatus) |
| 391 | ->groupBy('stats.newsletter'); |
| 392 | |
| 393 | if ($from && $to) { |
| 394 | $query->andWhere('stats.createdAt BETWEEN :from AND :to') |
| 395 | ->setParameter('from', $from) |
| 396 | ->setParameter('to', $to); |
| 397 | } elseif ($from && $to === null) { |
| 398 | $query->andWhere('stats.createdAt >= :from') |
| 399 | ->setParameter('from', $from); |
| 400 | } elseif ($from === null && $to) { |
| 401 | $query->andWhere('stats.createdAt <= :to') |
| 402 | ->setParameter('to', $to); |
| 403 | } |
| 404 | |
| 405 | $results = $query->getQuery() |
| 406 | ->getResult(); |
| 407 | |
| 408 | $revenues = []; |
| 409 | foreach ($results ?: [] as $result) { |
| 410 | $revenues[(int)$result['id']] = new WooCommerceRevenue( |
| 411 | $currency, |
| 412 | (float)$result['total'], |
| 413 | (int)$result['cnt'], |
| 414 | $this->wcHelper |
| 415 | ); |
| 416 | } |
| 417 | return $revenues; |
| 418 | } |
| 419 | } |
| 420 |