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