Campaign_Statistics.php
3 years ago
Geo_Statistics.php
3 years ago
Page_Statistics.php
3 years ago
Referrer_Statistics.php
3 years ago
Statistic.php
3 years ago
Statistics.php
3 years ago
Statistics.php
234 lines
| 1 | <?php |
| 2 | |
| 3 | namespace IAWP_SCOPED\IAWP\Statistics; |
| 4 | |
| 5 | use DateInterval; |
| 6 | use DatePeriod; |
| 7 | use DateTime; |
| 8 | use Exception; |
| 9 | use IAWP_SCOPED\IAWP\Date_Range\Date_Range; |
| 10 | use IAWP_SCOPED\IAWP\Illuminate_Builder; |
| 11 | use IAWP_SCOPED\IAWP\Query; |
| 12 | use IAWP_SCOPED\Illuminate\Database\Query\Builder; |
| 13 | use IAWP_SCOPED\Illuminate\Database\Query\JoinClause; |
| 14 | use IAWP_SCOPED\Proper\Timezone; |
| 15 | abstract class Statistics |
| 16 | { |
| 17 | protected $date_range; |
| 18 | protected $allowed_ids; |
| 19 | private $views; |
| 20 | private $visitors; |
| 21 | private $sessions; |
| 22 | private $average_session_duration; |
| 23 | private $views_per_session; |
| 24 | private $bounce_rate; |
| 25 | private $woocommerce_orders; |
| 26 | private $woocommerce_net_sales; |
| 27 | private $statistics_by_day; |
| 28 | private $statistics; |
| 29 | private $previous_period_statistics; |
| 30 | // Todo - What would I do next here? |
| 31 | // There are 2 places where a quick stat is created. There's one here, in Statistics.php, and |
| 32 | // there is one in Quick_Stats.php. The Statistic needs to be created here and the stats array' |
| 33 | // item needs to be created there. These should be consildated into a single place (here) and |
| 34 | // not spread all over... |
| 35 | // I should also get the types right. We have floats, and ints, and a lot of == checks to account |
| 36 | // for both. I'd like to lean into floats only up until the point of display... |
| 37 | /** |
| 38 | * @param Date_Range $date_range |
| 39 | * @param int[] $allowed_ids |
| 40 | */ |
| 41 | public function __construct(Date_Range $date_range, array $allowed_ids = null) |
| 42 | { |
| 43 | $this->date_range = $date_range; |
| 44 | $this->allowed_ids = $allowed_ids; |
| 45 | $this->statistics_by_day = $this->query($this->date_range, \true); |
| 46 | $this->statistics = $this->query($this->date_range, \false); |
| 47 | $this->previous_period_statistics = $this->query($this->date_range->previous_period(), \false); |
| 48 | $this->views = $this->get_statistic('views'); |
| 49 | $this->visitors = $this->get_statistic('visitors'); |
| 50 | $this->sessions = $this->get_statistic('sessions'); |
| 51 | $this->woocommerce_orders = $this->get_statistic('wc_orders'); |
| 52 | $this->woocommerce_net_sales = $this->get_statistic('wc_net_sales'); |
| 53 | $this->average_session_duration = $this->get_statistic('average_session_duration'); |
| 54 | $this->bounce_rate = new Statistic($this->calculate_percent($this->statistics->bounces, $this->statistics->sessions), $this->calculate_percent($this->previous_period_statistics->bounces, $this->previous_period_statistics->sessions)); |
| 55 | $this->views_per_session = new Statistic($this->divide($this->statistics->views, $this->statistics->sessions, 1), $this->divide($this->previous_period_statistics->views, $this->previous_period_statistics->sessions, 1)); |
| 56 | } |
| 57 | public function views() : Statistic |
| 58 | { |
| 59 | return $this->views; |
| 60 | } |
| 61 | public function visitors() : Statistic |
| 62 | { |
| 63 | return $this->visitors; |
| 64 | } |
| 65 | public function sessions() : Statistic |
| 66 | { |
| 67 | return $this->sessions; |
| 68 | } |
| 69 | public function average_session_duration() : Statistic |
| 70 | { |
| 71 | return $this->average_session_duration; |
| 72 | } |
| 73 | public function woocommerce_orders() : Statistic |
| 74 | { |
| 75 | return $this->woocommerce_orders; |
| 76 | } |
| 77 | public function woocommerce_net_sales() : Statistic |
| 78 | { |
| 79 | return $this->woocommerce_net_sales; |
| 80 | } |
| 81 | public function bounce_rate() : Statistic |
| 82 | { |
| 83 | return $this->bounce_rate; |
| 84 | } |
| 85 | public function view_per_session() : Statistic |
| 86 | { |
| 87 | return $this->views_per_session; |
| 88 | } |
| 89 | /** |
| 90 | * Statistics could be filtered down to a set of pages, a set of referrers, a set of geos, or |
| 91 | * a set of campaigns. This method allows subclasses to define what identifier column they |
| 92 | * want to use when filtering by id. |
| 93 | * |
| 94 | * @return string |
| 95 | */ |
| 96 | protected abstract function allowed_in_id_column() : string; |
| 97 | /** |
| 98 | * Statistics may wish to keep rows that have a null value in the id column. An example of |
| 99 | * this would be for referrers where a null referrer_id is a special type of referrer, a direct |
| 100 | * referrer. |
| 101 | * |
| 102 | * @return bool |
| 103 | */ |
| 104 | protected function allow_null_in_id_column() : bool |
| 105 | { |
| 106 | return \false; |
| 107 | } |
| 108 | /** |
| 109 | * Statistics can require that a column exists in order to be included. As an example, geos |
| 110 | * requires visitors.country_code and campaigns requires sessions.campaign_id |
| 111 | * |
| 112 | * @return ?string |
| 113 | */ |
| 114 | protected function required_column() : ?string |
| 115 | { |
| 116 | return null; |
| 117 | } |
| 118 | private function get_statistic(string $name) : Statistic |
| 119 | { |
| 120 | return new Statistic($this->statistics->{$name}, $this->previous_period_statistics->{$name}, $this->get_daily_summary($name)); |
| 121 | } |
| 122 | private function get_daily_summary(string $name) : array |
| 123 | { |
| 124 | return $this->fill_in_partial_day_range($this->statistics_by_day, $name); |
| 125 | } |
| 126 | private function query(Date_Range $range, bool $as_daily_statistics) |
| 127 | { |
| 128 | $utc_offset = Timezone::utc_offset(); |
| 129 | $wordpress_site_offset = Timezone::site_offset(); |
| 130 | $sessions_table = Query::get_table_name(Query::SESSIONS); |
| 131 | $views_table = Query::get_table_name(Query::VIEWS); |
| 132 | $visitors_table = Query::get_table_name(Query::VISITORS); |
| 133 | $wc_orders_table = Query::get_table_name(Query::WC_ORDERS); |
| 134 | $session_statistics = Illuminate_Builder::get_builder(); |
| 135 | $session_statistics->select('sessions.session_id')->selectRaw('COUNT(DISTINCT views.id) AS views')->selectRaw('COUNT(DISTINCT wc_orders.order_id) AS orders')->selectRaw('IFNULL(CAST(SUM(wc_orders.total) AS UNSIGNED), 0) AS gross_sales')->selectRaw('IFNULL(CAST(SUM(wc_orders.total_refunded) AS UNSIGNED), 0) AS total_refunded')->selectRaw('IFNULL(CAST(SUM(wc_orders.total_refunds) AS UNSIGNED), 0) AS total_refunds')->selectRaw('IFNULL(CAST(SUM(wc_orders.total - wc_orders.total_refunded) AS UNSIGNED), 0) AS net_sales')->from("{$sessions_table} AS sessions")->join("{$views_table} AS views", function (JoinClause $join) { |
| 136 | $join->on('sessions.session_id', '=', 'views.session_id'); |
| 137 | })->leftJoin("{$wc_orders_table} AS wc_orders", function (JoinClause $join) { |
| 138 | $join->on('views.id', '=', 'wc_orders.view_id')->whereIn('wc_orders.status', ['wc-completed', 'completed', 'wc-processing', 'processing', 'wc-refunded', 'refunded']); |
| 139 | })->whereBetween('sessions.created_at', [$range->iso_start(), $range->iso_end()])->whereBetween('views.viewed_at', [$range->iso_start(), $range->iso_end()])->groupBy('sessions.session_id')->when(!\is_null($this->allowed_ids), function (Builder $query) { |
| 140 | if ($this->allow_null_in_id_column()) { |
| 141 | $query->where(function (Builder $query) { |
| 142 | $query->whereNull($this->allowed_in_id_column())->orWhereIn($this->allowed_in_id_column(), $this->allowed_ids); |
| 143 | }); |
| 144 | } else { |
| 145 | $query->whereIn($this->allowed_in_id_column(), $this->allowed_ids); |
| 146 | } |
| 147 | })->when($this->required_column() === 'visitors.country_code', function (Builder $query) use($visitors_table) { |
| 148 | $query->join("{$visitors_table} as visitors", function (JoinClause $join) { |
| 149 | $join->on('visitors.visitor_id', '=', 'sessions.visitor_id'); |
| 150 | }); |
| 151 | })->when(!\is_null($this->required_column()), function (Builder $query) { |
| 152 | $query->whereNotNull($this->required_column()); |
| 153 | }); |
| 154 | $statistics = Illuminate_Builder::get_builder(); |
| 155 | $statistics->selectRaw('IFNULL(CAST(SUM(session_statistics.views) AS UNSIGNED), 0) AS views')->selectRaw('COUNT(DISTINCT sessions.visitor_id) AS visitors')->selectRaw('COUNT(DISTINCT sessions.session_id) AS sessions')->selectRaw('IFNULL(CAST(AVG(TIMESTAMPDIFF(SECOND, sessions.created_at, sessions.ended_at)) AS UNSIGNED), 0) AS average_session_duration')->selectRaw('COUNT(DISTINCT IF(sessions.final_view_id IS NULL, sessions.session_id, NULL)) AS bounces')->selectRaw('IFNULL(CAST(SUM(session_statistics.orders) AS UNSIGNED), 0) AS wc_orders')->selectRaw('IFNULL(CAST(SUM(session_statistics.gross_sales) AS UNSIGNED), 0) AS wc_gross_sales')->selectRaw('IFNULL(CAST(SUM(session_statistics.total_refunds) AS UNSIGNED), 0) AS wc_refunds')->selectRaw('IFNULL(CAST(SUM(session_statistics.total_refunded) AS UNSIGNED), 0) AS wc_refunded_amount')->selectRaw('IFNULL(CAST(SUM(session_statistics.net_sales) AS UNSIGNED), 0) AS wc_net_sales')->from("{$sessions_table} AS sessions")->joinSub($session_statistics, 'session_statistics', function (JoinClause $join) { |
| 156 | $join->on('sessions.session_id', '=', 'session_statistics.session_id'); |
| 157 | })->whereBetween('sessions.created_at', [$range->iso_start(), $range->iso_end()])->when($as_daily_statistics, function (Builder $query) use($utc_offset, $wordpress_site_offset) { |
| 158 | $query->selectRaw("DATE(CONVERT_TZ(sessions.created_at, '{$utc_offset}', '{$wordpress_site_offset}')) AS date"); |
| 159 | $query->groupByRaw("DATE(CONVERT_TZ(sessions.created_at, '{$utc_offset}', '{$wordpress_site_offset}'))"); |
| 160 | }); |
| 161 | if ($as_daily_statistics) { |
| 162 | return $statistics->get()->all(); |
| 163 | } else { |
| 164 | return $statistics->get()->first(); |
| 165 | } |
| 166 | } |
| 167 | private function calculate_percent(float $top, float $bottom) : float |
| 168 | { |
| 169 | if ($bottom === 0.0 && $top > 0) { |
| 170 | return 100; |
| 171 | } elseif ($bottom === 0.0) { |
| 172 | return 0; |
| 173 | } |
| 174 | return \round($top / $bottom * 100, 0); |
| 175 | } |
| 176 | private function divide(float $top, float $bottom, int $precision = 0) : float |
| 177 | { |
| 178 | if ($bottom === 0.0 && $top > 0) { |
| 179 | return 100; |
| 180 | } elseif ($bottom === 0.0) { |
| 181 | return 0; |
| 182 | } |
| 183 | return \round($top / $bottom, $precision); |
| 184 | } |
| 185 | /** |
| 186 | * @param array $partial_day_range |
| 187 | * @param string $field |
| 188 | * |
| 189 | * @return array |
| 190 | */ |
| 191 | private function fill_in_partial_day_range(array $partial_day_range, string $field) : array |
| 192 | { |
| 193 | $user_timezone = Timezone::site_timezone(); |
| 194 | $start = clone $this->date_range->start(); |
| 195 | $end = clone $this->date_range->end(); |
| 196 | $start->setTimezone($user_timezone); |
| 197 | $end->setTimezone($user_timezone); |
| 198 | $interval = new DateInterval('P1D'); |
| 199 | $date_range = new DatePeriod($start, $interval, $end); |
| 200 | $filled_in_data = []; |
| 201 | foreach ($date_range as $date) { |
| 202 | $stat = $this->get_statistic_for_date($partial_day_range, $date, $field); |
| 203 | $filled_in_data[] = [$date, $stat]; |
| 204 | } |
| 205 | return $filled_in_data; |
| 206 | } |
| 207 | /** |
| 208 | * @param array $partial_day_range |
| 209 | * @param DateTime $datetime_to_match |
| 210 | * @param string $field |
| 211 | * |
| 212 | * @return int Defaults to 0 |
| 213 | */ |
| 214 | private function get_statistic_for_date(array $partial_day_range, DateTime $datetime_to_match, string $field) : ?int |
| 215 | { |
| 216 | $user_timezone = Timezone::site_timezone(); |
| 217 | $default_value = 0; |
| 218 | foreach ($partial_day_range as $day) { |
| 219 | $date = $day->date; |
| 220 | $stat = $day->{$field}; |
| 221 | try { |
| 222 | $datetime = new DateTime($date, $user_timezone); |
| 223 | } catch (Exception $e) { |
| 224 | return $default_value; |
| 225 | } |
| 226 | // Intentionally using non-strict equality to see if two distinct DateTime objects represent the same time |
| 227 | if ($datetime == $datetime_to_match) { |
| 228 | return $stat; |
| 229 | } |
| 230 | } |
| 231 | return $default_value; |
| 232 | } |
| 233 | } |
| 234 |