SubscriberStatistics.php
3 years ago
SubscriberStatisticsRepository.php
1 month ago
index.php
3 years ago
SubscriberStatisticsRepository.php
253 lines
| 1 | <?php // phpcs:ignore SlevomatCodingStandard.TypeHints.DeclareStrictTypes.DeclareStrictTypesMissing |
| 2 | |
| 3 | namespace MailPoet\Subscribers\Statistics; |
| 4 | |
| 5 | if (!defined('ABSPATH')) exit; |
| 6 | |
| 7 | |
| 8 | use MailPoet\Doctrine\Repository; |
| 9 | use MailPoet\Entities\StatisticsClickEntity; |
| 10 | use MailPoet\Entities\StatisticsNewsletterEntity; |
| 11 | use MailPoet\Entities\StatisticsOpenEntity; |
| 12 | use MailPoet\Entities\StatisticsWooCommercePurchaseEntity; |
| 13 | use MailPoet\Entities\SubscriberEntity; |
| 14 | use MailPoet\Entities\UserAgentEntity; |
| 15 | use MailPoet\Newsletter\Statistics\WooCommerceRevenue; |
| 16 | use MailPoet\Settings\TrackingConfig; |
| 17 | use MailPoet\WooCommerce\Helper as WCHelper; |
| 18 | use MailPoet\WooCommerce\OrderAttributionRevenueReader; |
| 19 | use MailPoetVendor\Doctrine\ORM\EntityManager; |
| 20 | use MailPoetVendor\Doctrine\ORM\QueryBuilder; |
| 21 | |
| 22 | /** |
| 23 | * @extends Repository<SubscriberEntity> |
| 24 | */ |
| 25 | class SubscriberStatisticsRepository extends Repository { |
| 26 | public const ENGAGEMENT_SCORE_UNKNOWN = 'unknown'; |
| 27 | public const ENGAGEMENT_SCORE_DORMANT = 'dormant'; |
| 28 | public const ENGAGEMENT_SCORE_LOW = 'low'; |
| 29 | public const ENGAGEMENT_SCORE_GOOD = 'good'; |
| 30 | public const ENGAGEMENT_SCORE_EXCELLENT = 'excellent'; |
| 31 | |
| 32 | public const MIN_SENT_EMAILS_FOR_ENGAGEMENT_SCORE = 3; |
| 33 | private const ENGAGEMENT_SCORE_LOW_MAX = 20; |
| 34 | private const ENGAGEMENT_SCORE_GOOD_MAX = 50; |
| 35 | |
| 36 | /** @var WCHelper */ |
| 37 | private $wcHelper; |
| 38 | |
| 39 | /** @var TrackingConfig */ |
| 40 | private $trackingConfig; |
| 41 | |
| 42 | /** @var OrderAttributionRevenueReader */ |
| 43 | private $orderAttributionRevenueReader; |
| 44 | |
| 45 | public function __construct( |
| 46 | EntityManager $entityManager, |
| 47 | WCHelper $wcHelper, |
| 48 | TrackingConfig $trackingConfig, |
| 49 | OrderAttributionRevenueReader $orderAttributionRevenueReader |
| 50 | ) { |
| 51 | parent::__construct($entityManager); |
| 52 | $this->wcHelper = $wcHelper; |
| 53 | $this->trackingConfig = $trackingConfig; |
| 54 | $this->orderAttributionRevenueReader = $orderAttributionRevenueReader; |
| 55 | } |
| 56 | |
| 57 | protected function getEntityClassName() { |
| 58 | return SubscriberEntity::class; |
| 59 | } |
| 60 | |
| 61 | public function getStatistics(SubscriberEntity $subscriber, ?\DateTimeInterface $startTime = null) { |
| 62 | return new SubscriberStatistics( |
| 63 | $this->getStatisticsClickCount($subscriber, $startTime), |
| 64 | $this->getStatisticsOpenCount($subscriber, $startTime), |
| 65 | $this->getStatisticsMachineOpenCount($subscriber, $startTime), |
| 66 | $this->getTotalSentCount($subscriber, $startTime), |
| 67 | $this->getWooCommerceRevenue($subscriber, $startTime) |
| 68 | ); |
| 69 | } |
| 70 | |
| 71 | public function getEngagementScoreType(SubscriberEntity $subscriber): string { |
| 72 | $scoreTypes = $this->getEngagementScoreTypes([$subscriber]); |
| 73 | return $scoreTypes[(int)$subscriber->getId()] ?? self::ENGAGEMENT_SCORE_UNKNOWN; |
| 74 | } |
| 75 | |
| 76 | /** |
| 77 | * @param SubscriberEntity[] $subscribers |
| 78 | * @return array<int, string> |
| 79 | */ |
| 80 | public function getEngagementScoreTypes(array $subscribers): array { |
| 81 | if (!$subscribers) { |
| 82 | return []; |
| 83 | } |
| 84 | $yearAgo = new \DateTimeImmutable('-1 year'); |
| 85 | $lifetimeSentCounts = $this->getTotalSentCounts($subscribers); |
| 86 | $recentSentCounts = $this->getTotalSentCounts($subscribers, $yearAgo); |
| 87 | $scoreTypes = []; |
| 88 | foreach ($subscribers as $subscriber) { |
| 89 | $subscriberId = (int)$subscriber->getId(); |
| 90 | $scoreTypes[$subscriberId] = self::getEngagementScoreTypeFromData( |
| 91 | $subscriber->getEngagementScore(), |
| 92 | $lifetimeSentCounts[$subscriberId] ?? 0, |
| 93 | $recentSentCounts[$subscriberId] ?? 0 |
| 94 | ); |
| 95 | } |
| 96 | return $scoreTypes; |
| 97 | } |
| 98 | |
| 99 | public static function getEngagementScoreTypeFromData(?float $score, int $lifetimeSentCount, int $recentSentCount): string { |
| 100 | if ($score === null) { |
| 101 | if ( |
| 102 | $lifetimeSentCount >= self::MIN_SENT_EMAILS_FOR_ENGAGEMENT_SCORE |
| 103 | && $recentSentCount < self::MIN_SENT_EMAILS_FOR_ENGAGEMENT_SCORE |
| 104 | ) { |
| 105 | return self::ENGAGEMENT_SCORE_DORMANT; |
| 106 | } |
| 107 | return self::ENGAGEMENT_SCORE_UNKNOWN; |
| 108 | } |
| 109 | if ($score < self::ENGAGEMENT_SCORE_LOW_MAX) { |
| 110 | return self::ENGAGEMENT_SCORE_LOW; |
| 111 | } |
| 112 | if ($score < self::ENGAGEMENT_SCORE_GOOD_MAX) { |
| 113 | return self::ENGAGEMENT_SCORE_GOOD; |
| 114 | } |
| 115 | return self::ENGAGEMENT_SCORE_EXCELLENT; |
| 116 | } |
| 117 | |
| 118 | public function getStatisticsClickCount(SubscriberEntity $subscriber, ?\DateTimeInterface $startTime = null): int { |
| 119 | $queryBuilder = $this->getStatisticsCountQuery(StatisticsClickEntity::class, $subscriber); |
| 120 | if ($startTime) { |
| 121 | $this->applyDateConstraint($queryBuilder, $startTime); |
| 122 | } |
| 123 | return (int)$queryBuilder |
| 124 | ->getQuery() |
| 125 | ->getSingleScalarResult(); |
| 126 | } |
| 127 | |
| 128 | public function getStatisticsOpenCountQuery(SubscriberEntity $subscriber, ?\DateTimeInterface $startTime = null): QueryBuilder { |
| 129 | $queryBuilder = $this->getStatisticsCountQuery(StatisticsOpenEntity::class, $subscriber); |
| 130 | if ($startTime) { |
| 131 | $this->applyDateConstraint($queryBuilder, $startTime); |
| 132 | } |
| 133 | return $queryBuilder; |
| 134 | } |
| 135 | |
| 136 | public function getStatisticsOpenCount(SubscriberEntity $subscriber, ?\DateTimeInterface $startTime = null): int { |
| 137 | $queryBuilder = $this->getStatisticsOpenCountQuery($subscriber, $startTime); |
| 138 | if ($this->trackingConfig->areOpensSeparated()) { |
| 139 | $queryBuilder |
| 140 | ->andWhere('(stats.userAgentType = :userAgentType)') |
| 141 | ->setParameter('userAgentType', UserAgentEntity::USER_AGENT_TYPE_HUMAN); |
| 142 | } |
| 143 | return (int)$queryBuilder |
| 144 | ->getQuery() |
| 145 | ->getSingleScalarResult(); |
| 146 | } |
| 147 | |
| 148 | public function getStatisticsMachineOpenCount(SubscriberEntity $subscriber, ?\DateTimeInterface $startTime = null): int { |
| 149 | return (int)$this->getStatisticsOpenCountQuery($subscriber, $startTime) |
| 150 | ->andWhere('(stats.userAgentType = :userAgentType)') |
| 151 | ->setParameter('userAgentType', UserAgentEntity::USER_AGENT_TYPE_MACHINE) |
| 152 | ->getQuery() |
| 153 | ->getSingleScalarResult(); |
| 154 | } |
| 155 | |
| 156 | public function getTotalSentCount(SubscriberEntity $subscriber, ?\DateTimeInterface $startTime = null): int { |
| 157 | $queryBuilder = $this->getStatisticsCountQuery(StatisticsNewsletterEntity::class, $subscriber); |
| 158 | if ($startTime) { |
| 159 | $queryBuilder |
| 160 | ->andWhere('stats.sentAt >= :dateTime') |
| 161 | ->setParameter('dateTime', $startTime); |
| 162 | } |
| 163 | return (int)$queryBuilder |
| 164 | ->getQuery() |
| 165 | ->getSingleScalarResult(); |
| 166 | } |
| 167 | |
| 168 | /** |
| 169 | * @param SubscriberEntity[] $subscribers |
| 170 | * @return array<int, int> |
| 171 | */ |
| 172 | private function getTotalSentCounts(array $subscribers, ?\DateTimeInterface $startTime = null): array { |
| 173 | $queryBuilder = $this->entityManager->createQueryBuilder() |
| 174 | ->select('IDENTITY(stats.subscriber) AS subscriber_id, COUNT(DISTINCT stats.newsletter) AS sent_count') |
| 175 | ->from(StatisticsNewsletterEntity::class, 'stats') |
| 176 | ->where('stats.subscriber IN (:subscribers)') |
| 177 | ->groupBy('stats.subscriber') |
| 178 | ->setParameter('subscribers', $subscribers); |
| 179 | if ($startTime) { |
| 180 | $queryBuilder |
| 181 | ->andWhere('stats.sentAt >= :dateTime') |
| 182 | ->setParameter('dateTime', $startTime); |
| 183 | } |
| 184 | $rows = $queryBuilder->getQuery()->getResult(); |
| 185 | $counts = []; |
| 186 | foreach ($rows as $row) { |
| 187 | $counts[(int)$row['subscriber_id']] = (int)$row['sent_count']; |
| 188 | } |
| 189 | return $counts; |
| 190 | } |
| 191 | |
| 192 | public function getStatisticsCountQuery(string $entityName, SubscriberEntity $subscriber): QueryBuilder { |
| 193 | return $this->entityManager->createQueryBuilder() |
| 194 | ->select('COUNT(DISTINCT stats.newsletter) as cnt') |
| 195 | ->from($entityName, 'stats') |
| 196 | ->where('stats.subscriber = :subscriber') |
| 197 | ->setParameter('subscriber', $subscriber); |
| 198 | } |
| 199 | |
| 200 | public function getWooCommerceRevenue(SubscriberEntity $subscriber, ?\DateTimeInterface $startTime = null): ?WooCommerceRevenue { |
| 201 | if (!$this->wcHelper->isWooCommerceActive()) { |
| 202 | return null; |
| 203 | } |
| 204 | |
| 205 | $revenueStatus = $this->wcHelper->getPurchaseStates(); |
| 206 | $currency = $this->wcHelper->getWoocommerceCurrency(); |
| 207 | $wooBackedRevenue = $this->orderAttributionRevenueReader->getSubscriberRevenue((int)$subscriber->getId(), $startTime); |
| 208 | if (is_array($wooBackedRevenue)) { |
| 209 | return new WooCommerceRevenue( |
| 210 | $currency, |
| 211 | (float)$wooBackedRevenue['total'], |
| 212 | (int)$wooBackedRevenue['count'], |
| 213 | $this->wcHelper |
| 214 | ); |
| 215 | } |
| 216 | |
| 217 | $queryBuilder = $this->entityManager->createQueryBuilder() |
| 218 | ->select('stats.orderPriceTotal') |
| 219 | ->from(StatisticsWooCommercePurchaseEntity::class, 'stats') |
| 220 | ->where('stats.subscriber = :subscriber') |
| 221 | ->andWhere('stats.orderCurrency = :currency') |
| 222 | ->setParameter('subscriber', $subscriber) |
| 223 | ->setParameter('currency', $currency) |
| 224 | ->andWhere('stats.status IN (:revenue_status)') |
| 225 | ->setParameter('subscriber', $subscriber) |
| 226 | ->setParameter('currency', $currency) |
| 227 | ->setParameter('revenue_status', $revenueStatus) |
| 228 | ->groupBy('stats.orderId, stats.orderPriceTotal'); |
| 229 | if ($startTime) { |
| 230 | $queryBuilder |
| 231 | ->andWhere('stats.createdAt >= :dateTime') |
| 232 | ->setParameter('dateTime', $startTime); |
| 233 | } |
| 234 | $purchases = |
| 235 | $queryBuilder->getQuery() |
| 236 | ->getResult(); |
| 237 | $sum = array_sum(array_column($purchases, 'orderPriceTotal')); |
| 238 | return new WooCommerceRevenue( |
| 239 | $currency, |
| 240 | (float)$sum, |
| 241 | count($purchases), |
| 242 | $this->wcHelper |
| 243 | ); |
| 244 | } |
| 245 | |
| 246 | private function applyDateConstraint(QueryBuilder $queryBuilder, \DateTimeInterface $startTime): QueryBuilder { |
| 247 | $queryBuilder->join(StatisticsNewsletterEntity::class, 'sent_stats', 'WITH', 'stats.newsletter = sent_stats.newsletter AND stats.subscriber = sent_stats.subscriber AND sent_stats.sentAt >= :dateTime') |
| 248 | ->setParameter('dateTime', $startTime); |
| 249 | |
| 250 | return $queryBuilder; |
| 251 | } |
| 252 | } |
| 253 |