Track
3 weeks ago
GATracking.php
7 months ago
StatisticsBouncesRepository.php
3 years ago
StatisticsClicksRepository.php
2 years ago
StatisticsFormsRepository.php
3 years ago
StatisticsNewslettersRepository.php
1 year ago
StatisticsOpensRepository.php
6 days ago
StatisticsUnsubscribesRepository.php
3 months ago
StatisticsWooCommercePurchasesRepository.php
1 year ago
UnsubscribeReasonTracker.php
3 months ago
UserAgentsRepository.php
1 year ago
index.php
3 years ago
StatisticsOpensRepository.php
176 lines
| 1 | <?php declare(strict_types = 1); |
| 2 | |
| 3 | namespace MailPoet\Statistics; |
| 4 | |
| 5 | if (!defined('ABSPATH')) exit; |
| 6 | |
| 7 | |
| 8 | use MailPoet\Doctrine\Repository; |
| 9 | use MailPoet\Doctrine\WPDB\Connection; |
| 10 | use MailPoet\Entities\SegmentEntity; |
| 11 | use MailPoet\Entities\StatisticsNewsletterEntity; |
| 12 | use MailPoet\Entities\StatisticsOpenEntity; |
| 13 | use MailPoet\Entities\SubscriberEntity; |
| 14 | use MailPoet\Entities\UserAgentEntity; |
| 15 | use MailPoet\Settings\TrackingConfig; |
| 16 | use MailPoet\Subscribers\Statistics\SubscriberStatisticsRepository; |
| 17 | use MailPoetVendor\Doctrine\DBAL\ArrayParameterType; |
| 18 | use MailPoetVendor\Doctrine\ORM\EntityManager; |
| 19 | use MailPoetVendor\Doctrine\ORM\QueryBuilder; |
| 20 | |
| 21 | /** |
| 22 | * @extends Repository<StatisticsOpenEntity> |
| 23 | */ |
| 24 | class StatisticsOpensRepository extends Repository { |
| 25 | /** @var TrackingConfig */ |
| 26 | private $trackingConfig; |
| 27 | |
| 28 | public function __construct( |
| 29 | EntityManager $entityManager, |
| 30 | TrackingConfig $trackingConfig |
| 31 | ) { |
| 32 | parent::__construct($entityManager); |
| 33 | $this->entityManager = $entityManager; |
| 34 | $this->trackingConfig = $trackingConfig; |
| 35 | } |
| 36 | |
| 37 | protected function getEntityClassName(): string { |
| 38 | return StatisticsOpenEntity::class; |
| 39 | } |
| 40 | |
| 41 | public function recalculateSubscriberScore(SubscriberEntity $subscriber): void { |
| 42 | $subscriberId = $subscriber->getId(); |
| 43 | if (!$subscriberId) { |
| 44 | return; |
| 45 | } |
| 46 | $this->recalculateSubscribersScore([$subscriberId]); |
| 47 | } |
| 48 | |
| 49 | /** |
| 50 | * Recalculates and persists engagement scores in a single bulk UPDATE. Runs entirely |
| 51 | * in SQL so large batches avoid entity hydration, validation, and per-entity flushes. |
| 52 | * In-memory SubscriberEntity instances are not refreshed and keep stale score fields. |
| 53 | * |
| 54 | * @param int[] $subscriberIds |
| 55 | */ |
| 56 | public function recalculateSubscribersScore(array $subscriberIds): void { |
| 57 | // The UPDATE ... LEFT JOIN below is not supported by the SQLite integration used in |
| 58 | // WordPress Playground. Scores stay unset there and the listing reports them as |
| 59 | // unknown; the sweep worker no-ops for the same reason (see SubscribersEngagementScore). |
| 60 | // Without this guard the tracking endpoint throws, which also breaks the click redirect. |
| 61 | if (Connection::isSQLite()) { |
| 62 | return; |
| 63 | } |
| 64 | |
| 65 | if (!$subscriberIds) { |
| 66 | return; |
| 67 | } |
| 68 | $subscribersTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName(); |
| 69 | $sentStatsTable = $this->entityManager->getClassMetadata(StatisticsNewsletterEntity::class)->getTableName(); |
| 70 | $openStatsTable = $this->entityManager->getClassMetadata(StatisticsOpenEntity::class)->getTableName(); |
| 71 | $humanOpensCondition = $this->trackingConfig->areOpensSeparated() ? ' AND so.user_agent_type = :userAgentType' : ''; |
| 72 | |
| 73 | $sql = " |
| 74 | UPDATE {$subscribersTable} s |
| 75 | LEFT JOIN ( |
| 76 | SELECT subscriber_id, COUNT(DISTINCT newsletter_id) AS sent_count |
| 77 | FROM {$sentStatsTable} |
| 78 | WHERE subscriber_id IN (:ids) AND sent_at >= :yearAgo |
| 79 | GROUP BY subscriber_id |
| 80 | ) sent ON sent.subscriber_id = s.id |
| 81 | LEFT JOIN ( |
| 82 | SELECT so.subscriber_id, COUNT(DISTINCT so.newsletter_id) AS open_count |
| 83 | FROM {$openStatsTable} so |
| 84 | JOIN {$sentStatsTable} sn |
| 85 | ON sn.newsletter_id = so.newsletter_id |
| 86 | AND sn.subscriber_id = so.subscriber_id |
| 87 | AND sn.sent_at >= :yearAgo |
| 88 | WHERE so.subscriber_id IN (:ids){$humanOpensCondition} |
| 89 | GROUP BY so.subscriber_id |
| 90 | ) opens ON opens.subscriber_id = s.id |
| 91 | SET s.engagement_score = CASE |
| 92 | WHEN COALESCE(sent.sent_count, 0) < :minSentCount THEN NULL |
| 93 | ELSE COALESCE(opens.open_count, 0) / sent.sent_count * 100 |
| 94 | END, |
| 95 | s.engagement_score_updated_at = :now |
| 96 | WHERE s.id IN (:ids) |
| 97 | "; |
| 98 | |
| 99 | $parameters = [ |
| 100 | 'ids' => array_map('intval', $subscriberIds), |
| 101 | 'yearAgo' => (new \DateTimeImmutable('-1 year'))->format('Y-m-d H:i:s'), |
| 102 | 'minSentCount' => SubscriberStatisticsRepository::MIN_SENT_EMAILS_FOR_ENGAGEMENT_SCORE, |
| 103 | 'now' => (new \DateTimeImmutable())->format('Y-m-d H:i:s'), |
| 104 | ]; |
| 105 | if ($humanOpensCondition) { |
| 106 | $parameters['userAgentType'] = UserAgentEntity::USER_AGENT_TYPE_HUMAN; |
| 107 | } |
| 108 | $this->entityManager->getConnection()->executeStatement( |
| 109 | $sql, |
| 110 | $parameters, |
| 111 | ['ids' => ArrayParameterType::INTEGER] |
| 112 | ); |
| 113 | } |
| 114 | |
| 115 | public function resetSubscribersScoreCalculation() { |
| 116 | $this->entityManager->createQueryBuilder()->update(SubscriberEntity::class, 's') |
| 117 | ->set('s.engagementScoreUpdatedAt', ':updatedAt') |
| 118 | ->setParameter('updatedAt', null) |
| 119 | ->getQuery()->execute(); |
| 120 | } |
| 121 | |
| 122 | public function recalculateSegmentScore(SegmentEntity $segment): void { |
| 123 | $segment->setAverageEngagementScoreUpdatedAt(new \DateTimeImmutable()); |
| 124 | $avgScore = $this |
| 125 | ->entityManager |
| 126 | ->createQueryBuilder() |
| 127 | ->select('avg(subscriber.engagementScore)') |
| 128 | ->from(SubscriberEntity::class, 'subscriber') |
| 129 | ->join('subscriber.subscriberSegments', 'subscriberSegments') |
| 130 | ->where('subscriberSegments.segment = :segment') |
| 131 | ->andWhere('subscriber.status = :subscribed') |
| 132 | ->andWhere('subscriber.deletedAt IS NULL') |
| 133 | ->andWhere('subscriberSegments.status = :subscribed') |
| 134 | ->setParameter('segment', $segment) |
| 135 | ->setParameter('subscribed', SubscriberEntity::STATUS_SUBSCRIBED) |
| 136 | ->getQuery() |
| 137 | ->getSingleScalarResult(); |
| 138 | $segment->setAverageEngagementScore($avgScore === null ? $avgScore : (float)$avgScore); |
| 139 | $this->entityManager->flush(); |
| 140 | } |
| 141 | |
| 142 | public function resetSegmentsScoreCalculation(): void { |
| 143 | $this->entityManager->createQueryBuilder()->update(SegmentEntity::class, 's') |
| 144 | ->set('s.averageEngagementScoreUpdatedAt', ':updatedAt') |
| 145 | ->setParameter('updatedAt', null) |
| 146 | ->getQuery()->execute(); |
| 147 | } |
| 148 | |
| 149 | public function getAllForSubscriber(SubscriberEntity $subscriber): QueryBuilder { |
| 150 | return $this->entityManager->createQueryBuilder() |
| 151 | ->select('opens.id id, queue.newsletterRenderedSubject, opens.createdAt, userAgent.userAgent') |
| 152 | ->from(StatisticsOpenEntity::class, 'opens') |
| 153 | ->join('opens.queue', 'queue') |
| 154 | ->leftJoin('opens.userAgent', 'userAgent') |
| 155 | ->where('opens.subscriber = :subscriber') |
| 156 | ->orderBy('queue.newsletterRenderedSubject') |
| 157 | ->setParameter('subscriber', $subscriber->getId()); |
| 158 | } |
| 159 | |
| 160 | /** @param int[] $ids */ |
| 161 | public function deleteByNewsletterIds(array $ids): void { |
| 162 | $this->entityManager->createQueryBuilder() |
| 163 | ->delete(StatisticsOpenEntity::class, 's') |
| 164 | ->where('s.newsletter IN (:ids)') |
| 165 | ->setParameter('ids', $ids) |
| 166 | ->getQuery() |
| 167 | ->execute(); |
| 168 | |
| 169 | // delete was done via DQL, make sure the entities are also detached from the entity manager |
| 170 | $this->detachAll(function (StatisticsOpenEntity $entity) use ($ids) { |
| 171 | $newsletter = $entity->getNewsletter(); |
| 172 | return $newsletter && in_array($newsletter->getId(), $ids, true); |
| 173 | }); |
| 174 | } |
| 175 | } |
| 176 |