Campaign_Statistics.php
3 years ago
Geo_Statistics.php
3 years ago
Page_Statistics.php
3 years ago
Referrer_Statistics.php
3 years ago
Statistics.php
3 years ago
Statistics.php
335 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\IAWP\Utils\Timezone; |
| 13 | use IAWP_SCOPED\Illuminate\Database\Query\Builder; |
| 14 | use IAWP_SCOPED\Illuminate\Database\Query\JoinClause; |
| 15 | abstract class Statistics |
| 16 | { |
| 17 | protected $date_range; |
| 18 | protected $allowed_ids; |
| 19 | private $views; |
| 20 | private $prev_period_views; |
| 21 | private $daily_views; |
| 22 | private $visitors; |
| 23 | private $prev_period_visitors; |
| 24 | private $daily_visitors; |
| 25 | private $sessions; |
| 26 | private $prev_period_sessions; |
| 27 | private $daily_sessions; |
| 28 | private $average_session_duration; |
| 29 | private $prev_period_average_session_duration; |
| 30 | private $daily_average_session_durations; |
| 31 | private $bounce_rate; |
| 32 | private $prev_period_bounce_rate; |
| 33 | private $woocommerce_orders; |
| 34 | private $prev_woocommerce_orders; |
| 35 | private $woocommerce_net_sales; |
| 36 | private $prev_woocommerce_net_sales; |
| 37 | private $daily_woocommerce_orders; |
| 38 | private $daily_woocommerce_net_sales; |
| 39 | /** |
| 40 | * @param Date_Range $date_range |
| 41 | * @param int[] $allowed_ids |
| 42 | */ |
| 43 | public function __construct(Date_Range $date_range, array $allowed_ids = null) |
| 44 | { |
| 45 | $this->date_range = $date_range; |
| 46 | $this->allowed_ids = $allowed_ids; |
| 47 | $this->fetch(); |
| 48 | } |
| 49 | /** |
| 50 | * Statistics could be filtered down to a set of pages, a set of referrers, a set of geos, or |
| 51 | * a set of campaigns. This method allows subclasses to define what identifier column they |
| 52 | * want to use when filtering by id. |
| 53 | * |
| 54 | * @return string |
| 55 | */ |
| 56 | protected abstract function allowed_in_id_column() : string; |
| 57 | /** |
| 58 | * Statistics may wish to keep rows that have a null value in the id column. An example of |
| 59 | * this would be for referrers where a null referrer_id is a special type of referrer, a direct |
| 60 | * referrer. |
| 61 | * |
| 62 | * @return bool |
| 63 | */ |
| 64 | protected function allow_null_in_id_column() : bool |
| 65 | { |
| 66 | return \false; |
| 67 | } |
| 68 | /** |
| 69 | * Statistics can require that a column exists in order to be included. As an example, geos |
| 70 | * requires visitors.country_code and campaigns requires sessions.campaign_id |
| 71 | * |
| 72 | * @return ?string |
| 73 | */ |
| 74 | protected function required_column() : ?string |
| 75 | { |
| 76 | return null; |
| 77 | } |
| 78 | public function query(string $start, string $end, bool $as_daily_statistics) |
| 79 | { |
| 80 | $utc_offset = Timezone::utc_offset(); |
| 81 | $wordpress_site_offset = Timezone::wordpress_site_offset(); |
| 82 | $sessions_table = Query::get_table_name(Query::SESSIONS); |
| 83 | $views_table = Query::get_table_name(Query::VIEWS); |
| 84 | $visitors_table = Query::get_table_name(Query::VISITORS); |
| 85 | $wc_orders_table = Query::get_table_name(Query::WC_ORDERS); |
| 86 | $session_statistics = Illuminate_Builder::get_builder(); |
| 87 | $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) { |
| 88 | $join->on('sessions.session_id', '=', 'views.session_id'); |
| 89 | })->leftJoin("{$wc_orders_table} AS wc_orders", function (JoinClause $join) { |
| 90 | $join->on('views.id', '=', 'wc_orders.view_id')->whereIn('wc_orders.status', ['wc-completed', 'completed', 'wc-processing', 'processing', 'wc-refunded', 'refunded']); |
| 91 | })->whereBetween('sessions.created_at', [$start, $end])->whereBetween('views.viewed_at', [$start, $end])->groupBy('sessions.session_id')->when(!\is_null($this->allowed_ids), function (Builder $query) { |
| 92 | if ($this->allow_null_in_id_column()) { |
| 93 | $query->where(function (Builder $query) { |
| 94 | $query->whereNull($this->allowed_in_id_column())->orWhereIn($this->allowed_in_id_column(), $this->allowed_ids); |
| 95 | }); |
| 96 | } else { |
| 97 | $query->whereIn($this->allowed_in_id_column(), $this->allowed_ids); |
| 98 | } |
| 99 | })->when($this->required_column() === 'visitors.country_code', function (Builder $query) use($visitors_table) { |
| 100 | $query->join("{$visitors_table} as visitors", function (JoinClause $join) { |
| 101 | $join->on('visitors.visitor_id', '=', 'sessions.visitor_id'); |
| 102 | }); |
| 103 | })->when(!\is_null($this->required_column()), function (Builder $query) { |
| 104 | $query->whereNotNull($this->required_column()); |
| 105 | }); |
| 106 | $statistics = Illuminate_Builder::get_builder(); |
| 107 | $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) { |
| 108 | $join->on('sessions.session_id', '=', 'session_statistics.session_id'); |
| 109 | })->whereBetween('sessions.created_at', [$start, $end])->when($as_daily_statistics, function (Builder $query) use($utc_offset, $wordpress_site_offset) { |
| 110 | $query->selectRaw("DATE(CONVERT_TZ(sessions.created_at, '{$utc_offset}', '{$wordpress_site_offset}')) AS date"); |
| 111 | $query->groupByRaw("DATE(CONVERT_TZ(sessions.created_at, '{$utc_offset}', '{$wordpress_site_offset}'))"); |
| 112 | }); |
| 113 | if ($as_daily_statistics) { |
| 114 | return $statistics->get()->all(); |
| 115 | } else { |
| 116 | return $statistics->get()->first(); |
| 117 | } |
| 118 | } |
| 119 | public function fetch() |
| 120 | { |
| 121 | $statistics_by_day = $this->query($this->date_range->iso_start(), $this->date_range->iso_end(), \true); |
| 122 | $statistics = $this->query($this->date_range->iso_start(), $this->date_range->iso_end(), \false); |
| 123 | $previous_period_statistics = $this->query($this->date_range->previous_period_iso_start(), $this->date_range->previous_period_iso_end(), \false); |
| 124 | // Views |
| 125 | $this->views = $statistics->views; |
| 126 | $this->prev_period_views = $previous_period_statistics->views; |
| 127 | $this->daily_views = $this->fill_in_partial_day_range($statistics_by_day, 'views'); |
| 128 | // Visitors |
| 129 | $this->visitors = $statistics->visitors; |
| 130 | $this->prev_period_visitors = $previous_period_statistics->visitors; |
| 131 | $this->daily_visitors = $this->fill_in_partial_day_range($statistics_by_day, 'visitors'); |
| 132 | // Sessions |
| 133 | $this->sessions = $statistics->sessions; |
| 134 | $this->prev_period_sessions = $previous_period_statistics->sessions; |
| 135 | $this->daily_sessions = $this->fill_in_partial_day_range($statistics_by_day, 'sessions'); |
| 136 | // Average Session Duration |
| 137 | $this->average_session_duration = $statistics->average_session_duration; |
| 138 | $this->prev_period_average_session_duration = $previous_period_statistics->average_session_duration; |
| 139 | $this->daily_average_session_durations = $this->fill_in_partial_day_range($statistics_by_day, 'average_session_duration'); |
| 140 | if ($this->average_session_duration === null) { |
| 141 | $this->average_session_duration = 0; |
| 142 | } |
| 143 | if ($this->prev_period_average_session_duration === null) { |
| 144 | $this->prev_period_average_session_duration = 0; |
| 145 | } |
| 146 | // Bounce rate (quick stat only) |
| 147 | $this->bounce_rate = $this->calculate_percent($statistics->bounces, $statistics->sessions); |
| 148 | $this->prev_period_bounce_rate = $this->calculate_percent($previous_period_statistics->bounces, $previous_period_statistics->sessions); |
| 149 | // WooCommerce Orders |
| 150 | $this->woocommerce_orders = $statistics->wc_orders; |
| 151 | $this->prev_woocommerce_orders = $previous_period_statistics->wc_orders; |
| 152 | $this->daily_woocommerce_orders = $this->fill_in_partial_day_range($statistics_by_day, 'wc_orders'); |
| 153 | // WooCommerce Net Sales |
| 154 | $this->woocommerce_net_sales = $statistics->wc_gross_sales - $statistics->wc_refunded_amount; |
| 155 | $this->prev_woocommerce_net_sales = $previous_period_statistics->wc_gross_sales - $previous_period_statistics->wc_refunded_amount; |
| 156 | $this->daily_woocommerce_net_sales = $this->fill_in_partial_day_range($statistics_by_day, 'wc_net_sales'); |
| 157 | } |
| 158 | public function views() |
| 159 | { |
| 160 | return $this->views; |
| 161 | } |
| 162 | public function prev_period_views() |
| 163 | { |
| 164 | return $this->prev_period_views; |
| 165 | } |
| 166 | public function daily_views() |
| 167 | { |
| 168 | return $this->daily_views; |
| 169 | } |
| 170 | public function views_percentage_growth() |
| 171 | { |
| 172 | return $this->calculate_percent_growth($this->views(), $this->prev_period_views()); |
| 173 | } |
| 174 | public function visitors() |
| 175 | { |
| 176 | return $this->visitors; |
| 177 | } |
| 178 | public function visitors_percentage_growth() |
| 179 | { |
| 180 | return $this->calculate_percent_growth($this->visitors(), $this->prev_period_visitors()); |
| 181 | } |
| 182 | public function prev_period_visitors() |
| 183 | { |
| 184 | return $this->prev_period_visitors; |
| 185 | } |
| 186 | public function daily_visitors() |
| 187 | { |
| 188 | return $this->daily_visitors; |
| 189 | } |
| 190 | public function sessions() |
| 191 | { |
| 192 | return $this->sessions; |
| 193 | } |
| 194 | public function sessions_percentage_growth() |
| 195 | { |
| 196 | return $this->calculate_percent_growth($this->sessions(), $this->prev_period_sessions()); |
| 197 | } |
| 198 | public function prev_period_sessions() |
| 199 | { |
| 200 | return $this->prev_period_sessions; |
| 201 | } |
| 202 | public function daily_sessions() |
| 203 | { |
| 204 | return $this->daily_sessions; |
| 205 | } |
| 206 | public function average_session_duration() |
| 207 | { |
| 208 | return $this->average_session_duration; |
| 209 | } |
| 210 | public function average_session_duration_percentage_growth() |
| 211 | { |
| 212 | return $this->calculate_percent_growth($this->average_session_duration(), $this->prev_period_average_session_duration()); |
| 213 | } |
| 214 | public function prev_period_average_session_duration() |
| 215 | { |
| 216 | return $this->prev_period_average_session_duration; |
| 217 | } |
| 218 | public function daily_average_session_durations() |
| 219 | { |
| 220 | return $this->daily_average_session_durations; |
| 221 | } |
| 222 | public function bounce_rate() : int |
| 223 | { |
| 224 | return $this->bounce_rate; |
| 225 | } |
| 226 | public function prev_period_bounce_rate() : int |
| 227 | { |
| 228 | return $this->prev_period_bounce_rate; |
| 229 | } |
| 230 | public function bounce_rate_percentage_growth() : float |
| 231 | { |
| 232 | return $this->calculate_percent_growth($this->bounce_rate(), $this->prev_period_bounce_rate()); |
| 233 | } |
| 234 | public function woocommerce_orders() : int |
| 235 | { |
| 236 | return $this->woocommerce_orders; |
| 237 | } |
| 238 | public function prev_woocommerce_orders() : int |
| 239 | { |
| 240 | return $this->prev_woocommerce_orders; |
| 241 | } |
| 242 | public function woocommerce_orders_percentage_growth() : float |
| 243 | { |
| 244 | return $this->calculate_percent_growth($this->woocommerce_orders(), $this->prev_woocommerce_orders()); |
| 245 | } |
| 246 | public function woocommerce_net_sales() : float |
| 247 | { |
| 248 | return $this->woocommerce_net_sales; |
| 249 | } |
| 250 | public function prev_woocommerce_net_sales() : float |
| 251 | { |
| 252 | return $this->prev_woocommerce_net_sales; |
| 253 | } |
| 254 | public function woocommerce_net_sales_percentage_growth() : float |
| 255 | { |
| 256 | return $this->calculate_percent_growth((int) $this->woocommerce_net_sales(), (int) $this->prev_woocommerce_net_sales()); |
| 257 | } |
| 258 | public function daily_woocommerce_orders() : array |
| 259 | { |
| 260 | return $this->daily_woocommerce_orders; |
| 261 | } |
| 262 | public function daily_woocommerce_net_sales() : array |
| 263 | { |
| 264 | return $this->daily_woocommerce_net_sales; |
| 265 | } |
| 266 | private function calculate_percent(int $top, int $bottom) : float |
| 267 | { |
| 268 | if ($bottom === 0 && $top > 0) { |
| 269 | return 100; |
| 270 | } elseif ($bottom === 0) { |
| 271 | return 0; |
| 272 | } |
| 273 | $percent = $top / $bottom * 100; |
| 274 | return \round($percent, 0); |
| 275 | } |
| 276 | private function calculate_percent_growth(int $value, int $previous_period_value) : float |
| 277 | { |
| 278 | if ($value === 0 && $previous_period_value !== 0) { |
| 279 | return -100; |
| 280 | } elseif ($value === 0 || $previous_period_value === 0) { |
| 281 | return 0; |
| 282 | } |
| 283 | $percent_growth = ($value / $previous_period_value - 1) * 100; |
| 284 | return \round($percent_growth, 0); |
| 285 | } |
| 286 | /** |
| 287 | * @param array $partial_day_range |
| 288 | * @param string $field |
| 289 | * |
| 290 | * @return array |
| 291 | */ |
| 292 | private function fill_in_partial_day_range(array $partial_day_range, string $field) : array |
| 293 | { |
| 294 | $user_timezone = Timezone::wordpress_site_timezone(); |
| 295 | $start = clone $this->date_range->start(); |
| 296 | $end = clone $this->date_range->end(); |
| 297 | $start->setTimezone($user_timezone); |
| 298 | $end->setTimezone($user_timezone); |
| 299 | $interval = new DateInterval('P1D'); |
| 300 | $date_range = new DatePeriod($start, $interval, $end); |
| 301 | $filled_in_data = []; |
| 302 | foreach ($date_range as $date) { |
| 303 | $stat = $this->get_statistic_for_date($partial_day_range, $date, $field); |
| 304 | $filled_in_data[] = [$date, $stat]; |
| 305 | } |
| 306 | return $filled_in_data; |
| 307 | } |
| 308 | /** |
| 309 | * @param array $partial_day_range |
| 310 | * @param DateTime $datetime_to_match |
| 311 | * @param string $field |
| 312 | * |
| 313 | * @return int Defaults to 0 |
| 314 | */ |
| 315 | private function get_statistic_for_date(array $partial_day_range, DateTime $datetime_to_match, string $field) : ?int |
| 316 | { |
| 317 | $user_timezone = Timezone::wordpress_site_timezone(); |
| 318 | $default_value = 0; |
| 319 | foreach ($partial_day_range as $day) { |
| 320 | $date = $day->date; |
| 321 | $stat = $day->{$field}; |
| 322 | try { |
| 323 | $datetime = new DateTime($date, $user_timezone); |
| 324 | } catch (Exception $e) { |
| 325 | return $default_value; |
| 326 | } |
| 327 | // Intentionally using non-strict equality to see if two distinct DateTime objects represent the same time |
| 328 | if ($datetime == $datetime_to_match) { |
| 329 | return $stat; |
| 330 | } |
| 331 | } |
| 332 | return $default_value; |
| 333 | } |
| 334 | } |
| 335 |