Intervals
2 years ago
Campaign_Statistics.php
2 years ago
City_Statistics.php
2 years ago
Country_Statistics.php
2 years ago
Device_Browser_Statistics.php
2 years ago
Device_OS_Statistics.php
2 years ago
Device_Type_Statistics.php
2 years ago
Page_Statistics.php
2 years ago
Referrer_Statistics.php
2 years ago
Statistic.php
2 years ago
Statistics.php
2 years ago
Statistics.php
323 lines
| 1 | <?php |
| 2 | |
| 3 | namespace IAWP\Statistics; |
| 4 | |
| 5 | use DatePeriod; |
| 6 | use DateTime; |
| 7 | use DateTimeZone; |
| 8 | use IAWP\Date_Range\Date_Range; |
| 9 | use IAWP\Form; |
| 10 | use IAWP\Illuminate_Builder; |
| 11 | use IAWP\Query; |
| 12 | use IAWP\Rows\Rows; |
| 13 | use IAWP\Statistics\Intervals\Daily; |
| 14 | use IAWP\Statistics\Intervals\Interval; |
| 15 | use IAWP\WooCommerce_Order; |
| 16 | use IAWPSCOPED\Illuminate\Database\Query\Builder; |
| 17 | use IAWPSCOPED\Illuminate\Database\Query\JoinClause; |
| 18 | use IAWPSCOPED\Illuminate\Support\Str; |
| 19 | use IAWPSCOPED\Proper\Timezone; |
| 20 | /** @internal */ |
| 21 | abstract class Statistics |
| 22 | { |
| 23 | protected $date_range; |
| 24 | protected $rows; |
| 25 | protected $chart_interval; |
| 26 | private $views; |
| 27 | private $visitors; |
| 28 | private $sessions; |
| 29 | private $average_session_duration; |
| 30 | private $views_per_session; |
| 31 | private $bounce_rate; |
| 32 | private $woocommerce_orders; |
| 33 | private $woocommerce_gross_sales; |
| 34 | private $woocommerce_refunds; |
| 35 | private $woocommerce_refunded_amount; |
| 36 | private $woocommerce_net_sales; |
| 37 | private $woocommerce_conversion_rate; |
| 38 | private $woocommerce_earnings_per_visitor; |
| 39 | private $woocommerce_average_order_volume; |
| 40 | private $form_submissions; |
| 41 | private $form_conversion_rate; |
| 42 | private $statistics_by_day; |
| 43 | private $statistics; |
| 44 | private $previous_period_statistics; |
| 45 | private $form_statistics = []; |
| 46 | // The biggest flaw here is that it requires two queries for the stats (current and previous) when |
| 47 | // that could be one. I think it would also be possible to reuse the rows query and not limit |
| 48 | // by 50 and just SUM() up all the stats columns for the quick stats. Maybe that would be faster |
| 49 | // even if two queries were still used. Needs testing. |
| 50 | public function __construct(Date_Range $date_range, ?Rows $rows = null, ?Interval $chart_interval = null) |
| 51 | { |
| 52 | $this->date_range = $date_range; |
| 53 | $this->rows = $rows; |
| 54 | $this->chart_interval = $chart_interval ?? new Daily(); |
| 55 | $this->statistics_by_day = $this->query($this->date_range, \true); |
| 56 | $this->statistics = $this->query($this->date_range, \false); |
| 57 | $this->previous_period_statistics = $this->query($this->date_range->previous_period(), \false); |
| 58 | $this->views = $this->get_statistic('views'); |
| 59 | $this->visitors = $this->get_statistic('visitors'); |
| 60 | $this->sessions = $this->get_statistic('sessions'); |
| 61 | $this->woocommerce_orders = $this->get_statistic('wc_orders'); |
| 62 | $this->woocommerce_gross_sales = $this->get_statistic('wc_gross_sales'); |
| 63 | $this->woocommerce_refunds = $this->get_statistic('wc_refunds'); |
| 64 | $this->woocommerce_refunded_amount = $this->get_statistic('wc_refunded_amount'); |
| 65 | $this->woocommerce_net_sales = $this->get_statistic('wc_net_sales'); |
| 66 | $this->woocommerce_conversion_rate = $this->get_statistic('wc_conversion_rate'); |
| 67 | $this->woocommerce_earnings_per_visitor = $this->get_statistic('wc_earnings_per_visitor'); |
| 68 | $this->woocommerce_average_order_volume = $this->get_statistic('wc_average_order_volume'); |
| 69 | $this->average_session_duration = $this->get_statistic('average_session_duration'); |
| 70 | $this->form_submissions = $this->get_statistic('form_submissions'); |
| 71 | $this->form_conversion_rate = new \IAWP\Statistics\Statistic($this->calculate_percent($this->statistics->form_submissions, $this->statistics->visitors, 2), $this->calculate_percent($this->previous_period_statistics->form_submissions, $this->previous_period_statistics->visitors, 2)); |
| 72 | foreach (Form::get_forms() as $form) { |
| 73 | $this->form_statistics[$form->submissions_column()] = $this->get_statistic($form->submissions_column()); |
| 74 | $this->form_statistics[$form->conversion_rate_column()] = new \IAWP\Statistics\Statistic($this->calculate_percent($this->statistics->{$form->submissions_column()}, $this->statistics->visitors, 2), $this->calculate_percent($this->previous_period_statistics->{$form->submissions_column()}, $this->previous_period_statistics->visitors, 2)); |
| 75 | } |
| 76 | $this->bounce_rate = new \IAWP\Statistics\Statistic($this->calculate_percent($this->statistics->bounces, $this->statistics->sessions), $this->calculate_percent($this->previous_period_statistics->bounces, $this->previous_period_statistics->sessions)); |
| 77 | $this->views_per_session = new \IAWP\Statistics\Statistic($this->divide($this->statistics->total_views, $this->statistics->sessions, 2), $this->divide($this->previous_period_statistics->total_views, $this->previous_period_statistics->sessions, 2)); |
| 78 | } |
| 79 | public function views() : \IAWP\Statistics\Statistic |
| 80 | { |
| 81 | return $this->views; |
| 82 | } |
| 83 | public function visitors() : \IAWP\Statistics\Statistic |
| 84 | { |
| 85 | return $this->visitors; |
| 86 | } |
| 87 | public function sessions() : \IAWP\Statistics\Statistic |
| 88 | { |
| 89 | return $this->sessions; |
| 90 | } |
| 91 | public function average_session_duration() : \IAWP\Statistics\Statistic |
| 92 | { |
| 93 | return $this->average_session_duration; |
| 94 | } |
| 95 | public function form_submissions() : \IAWP\Statistics\Statistic |
| 96 | { |
| 97 | return $this->form_submissions; |
| 98 | } |
| 99 | public function form_conversion_rate() : \IAWP\Statistics\Statistic |
| 100 | { |
| 101 | return $this->form_conversion_rate; |
| 102 | } |
| 103 | public function form_submissions_for(Form $form) : \IAWP\Statistics\Statistic |
| 104 | { |
| 105 | return $this->form_statistics[$form->submissions_column()]; |
| 106 | } |
| 107 | public function form_conversion_rate_for(Form $form) : \IAWP\Statistics\Statistic |
| 108 | { |
| 109 | return $this->form_statistics[$form->conversion_rate_column()]; |
| 110 | } |
| 111 | public function wc_orders() : \IAWP\Statistics\Statistic |
| 112 | { |
| 113 | return $this->woocommerce_orders; |
| 114 | } |
| 115 | public function wc_gross_sales() : \IAWP\Statistics\Statistic |
| 116 | { |
| 117 | return $this->woocommerce_gross_sales; |
| 118 | } |
| 119 | public function wc_refunds() : \IAWP\Statistics\Statistic |
| 120 | { |
| 121 | return $this->woocommerce_refunds; |
| 122 | } |
| 123 | public function wc_refunded_amount() : \IAWP\Statistics\Statistic |
| 124 | { |
| 125 | return $this->woocommerce_refunded_amount; |
| 126 | } |
| 127 | public function wc_net_sales() : \IAWP\Statistics\Statistic |
| 128 | { |
| 129 | return $this->woocommerce_net_sales; |
| 130 | } |
| 131 | public function wc_conversion_rate() : \IAWP\Statistics\Statistic |
| 132 | { |
| 133 | return $this->woocommerce_conversion_rate; |
| 134 | } |
| 135 | public function wc_earnings_per_visitor() : \IAWP\Statistics\Statistic |
| 136 | { |
| 137 | return $this->woocommerce_earnings_per_visitor; |
| 138 | } |
| 139 | public function wc_average_order_volume() : \IAWP\Statistics\Statistic |
| 140 | { |
| 141 | return $this->woocommerce_average_order_volume; |
| 142 | } |
| 143 | public function bounce_rate() : \IAWP\Statistics\Statistic |
| 144 | { |
| 145 | return $this->bounce_rate; |
| 146 | } |
| 147 | public function views_per_session() : \IAWP\Statistics\Statistic |
| 148 | { |
| 149 | return $this->views_per_session; |
| 150 | } |
| 151 | public function chart_interval() : Interval |
| 152 | { |
| 153 | return $this->chart_interval; |
| 154 | } |
| 155 | /** |
| 156 | * I'm sure there's more we could do here. If you get a result back where there isn't a full |
| 157 | * page of results or where you're not paginating, then you can just count up the rows... |
| 158 | * |
| 159 | * @return int|null |
| 160 | */ |
| 161 | public function total_number_of_rows() : ?int |
| 162 | { |
| 163 | $sessions_table = Query::get_table_name(Query::SESSIONS); |
| 164 | $views_table = Query::get_table_name(Query::VIEWS); |
| 165 | $column = $this->total_table_rows_column() ?? $this->required_column(); |
| 166 | $query = Illuminate_Builder::get_builder()->selectRaw("COUNT(DISTINCT {$column}) AS total_table_rows")->from("{$sessions_table} AS sessions")->join("{$views_table} AS views", function (JoinClause $join) { |
| 167 | $join->on('sessions.session_id', '=', 'views.session_id'); |
| 168 | })->when(!\is_null($this->rows), function (Builder $query) { |
| 169 | $this->rows->attach_filters($query); |
| 170 | })->whereBetween('sessions.created_at', [$this->date_range->iso_start(), $this->date_range->iso_end()])->whereBetween('views.viewed_at', [$this->date_range->iso_start(), $this->date_range->iso_end()]); |
| 171 | return $query->value('total_table_rows'); |
| 172 | } |
| 173 | /** |
| 174 | * Define which id column to use to count up the total table rows. This is only required |
| 175 | * for classes that don't have a required column and don't override required_column |
| 176 | * |
| 177 | * @return string|null |
| 178 | */ |
| 179 | protected function total_table_rows_column() : ?string |
| 180 | { |
| 181 | return null; |
| 182 | } |
| 183 | /** |
| 184 | * Statistics can require that a column exists in order to be included. As an example, geos |
| 185 | * requires visitors.country_code and campaigns requires sessions.campaign_id |
| 186 | * |
| 187 | * @return string|null |
| 188 | */ |
| 189 | protected function required_column() : ?string |
| 190 | { |
| 191 | return null; |
| 192 | } |
| 193 | private function get_statistic(string $name) : \IAWP\Statistics\Statistic |
| 194 | { |
| 195 | return new \IAWP\Statistics\Statistic($this->statistics->{$name}, $this->previous_period_statistics->{$name}, $this->fill_in_partial_day_range($this->statistics_by_day, $name)); |
| 196 | } |
| 197 | private function query(Date_Range $range, bool $as_daily_statistics) |
| 198 | { |
| 199 | $utc_offset = Timezone::utc_offset(); |
| 200 | $site_offset = Timezone::site_offset(); |
| 201 | $sessions_table = Query::get_table_name(Query::SESSIONS); |
| 202 | $views_table = Query::get_table_name(Query::VIEWS); |
| 203 | $wc_orders_table = Query::get_table_name(Query::WC_ORDERS); |
| 204 | $form_submissions_table = Query::get_table_name(Query::FORM_SUBMISSIONS); |
| 205 | $form_submissions_query = Illuminate_Builder::get_builder()->select(['form_id', 'session_id'])->selectRaw('COUNT(*) AS form_submissions')->from($form_submissions_table, 'form_submissions')->whereBetween('created_at', [$range->iso_start(), $range->iso_end()])->groupBy(['form_id', 'session_id']); |
| 206 | $session_statistics = Illuminate_Builder::get_builder(); |
| 207 | $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 DECIMAL(10, 2)), 0) AS gross_sales')->selectRaw('IFNULL(CAST(SUM(wc_orders.total_refunded) AS DECIMAL(10, 2)), 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 DECIMAL(10, 2)), 0) AS net_sales')->from("{$sessions_table} AS sessions")->join("{$views_table} AS views", function (JoinClause $join) { |
| 208 | $join->on('sessions.session_id', '=', 'views.session_id'); |
| 209 | })->leftJoin("{$wc_orders_table} AS wc_orders", function (JoinClause $join) { |
| 210 | $join->on('views.id', '=', 'wc_orders.initial_view_id')->whereIn('wc_orders.status', WooCommerce_Order::tracked_order_statuses()); |
| 211 | })->when(!\is_null($this->rows), function (Builder $query) { |
| 212 | $this->rows->attach_filters($query); |
| 213 | })->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->required_column()), function (Builder $query) { |
| 214 | $query->whereNotNull($this->required_column()); |
| 215 | }); |
| 216 | $statistics = Illuminate_Builder::get_builder(); |
| 217 | $statistics->selectRaw('IFNULL(CAST(SUM(sessions.total_views) AS UNSIGNED), 0) AS total_views')->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 DECIMAL(10, 2)), 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 DECIMAL(10, 2)), 0) AS wc_refunded_amount')->selectRaw('IFNULL(CAST(SUM(session_statistics.net_sales) AS DECIMAL(10, 2)), 0) AS wc_net_sales')->selectRaw('IFNULL(SUM(form_submissions.form_submissions), 0) AS form_submissions')->tap(function (Builder $query) { |
| 218 | foreach (Form::get_forms() as $form) { |
| 219 | $query->selectRaw('IFNULL(SUM(IF(form_submissions.form_id = ?, form_submissions.form_submissions, 0)), 0) AS ' . $form->submissions_column(), [$form->id()]); |
| 220 | } |
| 221 | })->from("{$sessions_table} AS sessions")->joinSub($session_statistics, 'session_statistics', function (JoinClause $join) { |
| 222 | $join->on('sessions.session_id', '=', 'session_statistics.session_id'); |
| 223 | })->leftJoinSub($form_submissions_query, 'form_submissions', 'sessions.session_id', '=', 'form_submissions.session_id')->whereBetween('sessions.created_at', [$range->iso_start(), $range->iso_end()])->when($as_daily_statistics, function (Builder $query) use($utc_offset, $site_offset) { |
| 224 | if ($this->chart_interval->id() === 'daily') { |
| 225 | $query->selectRaw("DATE(CONVERT_TZ(sessions.created_at, '{$utc_offset}', '{$site_offset}')) AS date"); |
| 226 | } elseif ($this->chart_interval->id() === 'monthly') { |
| 227 | $query->selectRaw("DATE_FORMAT(CONVERT_TZ(sessions.created_at, '{$utc_offset}', '{$site_offset}'), '%Y-%m-01 00:00:00') AS date"); |
| 228 | } elseif ($this->chart_interval->id() === 'weekly') { |
| 229 | $day_of_week = \IAWPSCOPED\iawp()->get_option('iawp_dow', 0) + 1; |
| 230 | $query->selectRaw("\n IF (\n DAYOFWEEK(CONVERT_TZ(sessions.created_at, '{$utc_offset}', '{$site_offset}')) - {$day_of_week} < 0,\n DATE_FORMAT(SUBDATE(CONVERT_TZ(sessions.created_at, '{$utc_offset}', '{$site_offset}'), DAYOFWEEK(CONVERT_TZ(sessions.created_at, '{$utc_offset}', '{$site_offset}')) - {$day_of_week} + 7), '%Y-%m-%d 00:00:00'),\n DATE_FORMAT(SUBDATE(CONVERT_TZ(sessions.created_at, '{$utc_offset}', '{$site_offset}'), DAYOFWEEK(CONVERT_TZ(sessions.created_at, '{$utc_offset}', '{$site_offset}')) - {$day_of_week}), '%Y-%m-%d 00:00:00')\n ) AS date\n "); |
| 231 | } else { |
| 232 | $query->selectRaw("DATE_FORMAT(CONVERT_TZ(sessions.created_at, '{$utc_offset}', '{$site_offset}'), '%Y-%m-%d %H:00:00') AS date"); |
| 233 | } |
| 234 | $query->groupByRaw("date"); |
| 235 | }); |
| 236 | $outer_query = Illuminate_Builder::get_builder()->selectRaw('statistics.*')->selectRaw('IF(statistics.visitors = 0, 0, (statistics.wc_orders / statistics.visitors) * 100) AS wc_conversion_rate')->selectRaw('IF(statistics.visitors = 0, 0, (statistics.wc_gross_sales - statistics.wc_refunded_amount) / visitors) AS wc_earnings_per_visitor')->selectRaw('IF(statistics.wc_orders = 0, 0, ROUND(CAST(statistics.wc_gross_sales / statistics.wc_orders AS DECIMAL(10, 2)))) AS wc_average_order_volume')->fromSub($statistics, 'statistics'); |
| 237 | $results = \array_map(function (object $statistic) : object { |
| 238 | return $this->parse_statistic($statistic); |
| 239 | }, $outer_query->get()->all()); |
| 240 | if (!$as_daily_statistics) { |
| 241 | return $results[0]; |
| 242 | } |
| 243 | return $results; |
| 244 | } |
| 245 | private function parse_statistic(object $statistic) : object |
| 246 | { |
| 247 | $statistic->wc_gross_sales = \floatval($statistic->wc_gross_sales); |
| 248 | $statistic->wc_refunded_amount = \floatval($statistic->wc_refunded_amount); |
| 249 | $statistic->wc_net_sales = \floatval($statistic->wc_net_sales); |
| 250 | foreach ($statistic as $key => $value) { |
| 251 | if (Str::startsWith($key, 'form_submissions')) { |
| 252 | $statistic->{$key} = \intval($value); |
| 253 | } |
| 254 | } |
| 255 | return $statistic; |
| 256 | } |
| 257 | private function calculate_percent(float $top, float $bottom, int $precision = 0) : float |
| 258 | { |
| 259 | if ($bottom === 0.0 && $top > 0) { |
| 260 | return 100; |
| 261 | } elseif ($bottom === 0.0) { |
| 262 | return 0; |
| 263 | } |
| 264 | return \round($top / $bottom * 100, $precision); |
| 265 | } |
| 266 | private function divide(float $top, float $bottom, int $precision = 0) : float |
| 267 | { |
| 268 | if ($bottom === 0.0 && $top > 0) { |
| 269 | return 100; |
| 270 | } elseif ($bottom === 0.0) { |
| 271 | return 0; |
| 272 | } |
| 273 | return \round($top / $bottom, $precision); |
| 274 | } |
| 275 | /** |
| 276 | * @param array $partial_day_range |
| 277 | * @param string $field |
| 278 | * |
| 279 | * @return array |
| 280 | */ |
| 281 | private function fill_in_partial_day_range(array $partial_day_range, string $field) : array |
| 282 | { |
| 283 | $original_start = (clone $this->date_range->start())->setTimezone(Timezone::site_timezone()); |
| 284 | $start = $this->chart_interval->calculate_start_of_interval_for($original_start); |
| 285 | $original_end = (clone $this->date_range->end())->setTimezone(Timezone::site_timezone()); |
| 286 | $end = $this->chart_interval->calculate_start_of_interval_for($original_end); |
| 287 | $end->add(new \DateInterval('PT1S')); |
| 288 | $date_range = new DatePeriod($start, $this->chart_interval->date_interval(), $end); |
| 289 | $filled_in_data = []; |
| 290 | foreach ($date_range as $date) { |
| 291 | // One day, it would be nice to remove this entire if block. For now, it's necessary to |
| 292 | // fix an issue when a date range spans March 31st 2024. |
| 293 | // https://www.aljazeera.com/news/2023/3/27/lebanon-reverts-to-daylight-saving-time-after-confusion-furor |
| 294 | if ($date->getTimezone() == new DateTimeZone('Asia/Beirut')) { |
| 295 | $formatted_time = $date->format('H:i:s'); |
| 296 | if ($formatted_time === '01:00:00') { |
| 297 | $date->setTime(0, 0, 0); |
| 298 | } |
| 299 | } |
| 300 | $stat = $this->get_statistic_for_date($partial_day_range, $date, $field); |
| 301 | $filled_in_data[] = [$date, $stat]; |
| 302 | } |
| 303 | return $filled_in_data; |
| 304 | } |
| 305 | private function get_statistic_for_date(array $partial_day_range, DateTime $datetime_to_match, string $field) : int |
| 306 | { |
| 307 | foreach ($partial_day_range as $day) { |
| 308 | $date = $day->date; |
| 309 | $stat = $day->{$field}; |
| 310 | try { |
| 311 | $datetime = new DateTime($date, Timezone::site_timezone()); |
| 312 | } catch (\Throwable $e) { |
| 313 | return 0; |
| 314 | } |
| 315 | // Intentionally using non-strict equality to see if two distinct DateTime objects represent the same time |
| 316 | if ($datetime == $datetime_to_match) { |
| 317 | return \intval($stat); |
| 318 | } |
| 319 | } |
| 320 | return 0; |
| 321 | } |
| 322 | } |
| 323 |