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