Statistics.php — Independent Analytics – WordPress Analytics Plugin 2.2.0

independent-analytics / IAWP / Statistics / Statistics.php

independent-analytics / IAWP / Statistics Last commit date

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

246 lines

1	<?php
2
3	namespace IAWP_SCOPED\IAWP\Statistics;
4
5	use DatePeriod;
6	use DateTime;
7	use IAWP_SCOPED\IAWP\Date_Range\Date_Range;
8	use IAWP_SCOPED\IAWP\Illuminate_Builder;
9	use IAWP_SCOPED\IAWP\Query;
10	use IAWP_SCOPED\IAWP\Rows\Rows;
11	use IAWP_SCOPED\IAWP\Statistics\Intervals\Daily;
12	use IAWP_SCOPED\IAWP\Statistics\Intervals\Interval;
13	use IAWP_SCOPED\Illuminate\Database\Query\Builder;
14	use IAWP_SCOPED\Illuminate\Database\Query\JoinClause;
15	use IAWP_SCOPED\Proper\Timezone;
16	/** @internal */
17	abstract class Statistics
18	{
19	protected $date_range;
20	protected $rows;
21	protected $chart_interval;
22	private $views;
23	private $visitors;
24	private $sessions;
25	private $average_session_duration;
26	private $views_per_session;
27	private $bounce_rate;
28	private $woocommerce_orders;
29	private $woocommerce_net_sales;
30	private $statistics_by_day;
31	private $statistics;
32	private $previous_period_statistics;
33	// The biggest flaw here is that it requires two queries for the stats (current and previous) when
34	// that could be one. I think it would also be possible to reuse the rows query and not limit
35	// by 50 and just SUM() up all the stats columns for the quick stats. Maybe that would be faster
36	// even if two queries were still used. Needs testing.
37	public function __construct(Date_Range $date_range, ?Rows $rows = null, ?Interval $chart_interval = null)
38	{
39	$this->date_range = $date_range;
40	$this->rows = $rows;
41	$this->chart_interval = $chart_interval ?? new Daily();
42	$this->statistics_by_day = $this->query($this->date_range, \true);
43	$this->statistics = $this->query($this->date_range, \false);
44	$this->previous_period_statistics = $this->query($this->date_range->previous_period(), \false);
45	$this->views = $this->get_statistic('views');
46	$this->visitors = $this->get_statistic('visitors');
47	$this->sessions = $this->get_statistic('sessions');
48	$this->woocommerce_orders = $this->get_statistic('wc_orders');
49	$this->woocommerce_net_sales = $this->get_statistic('wc_net_sales');
50	$this->average_session_duration = $this->get_statistic('average_session_duration');
51	$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));
52	$this->views_per_session = new 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));
53	}
54	public function views() : Statistic
55	{
56	return $this->views;
57	}
58	public function visitors() : Statistic
59	{
60	return $this->visitors;
61	}
62	public function sessions() : Statistic
63	{
64	return $this->sessions;
65	}
66	public function average_session_duration() : Statistic
67	{
68	return $this->average_session_duration;
69	}
70	public function woocommerce_orders() : Statistic
71	{
72	return $this->woocommerce_orders;
73	}
74	public function woocommerce_net_sales() : Statistic
75	{
76	return $this->woocommerce_net_sales;
77	}
78	public function bounce_rate() : Statistic
79	{
80	return $this->bounce_rate;
81	}
82	public function view_per_session() : Statistic
83	{
84	return $this->views_per_session;
85	}
86	public function chart_interval() : Interval
87	{
88	return $this->chart_interval;
89	}
90	/**
91	* I'm sure there's more we could do here. If you get a result back where there isn't a full
92	* page of results or where you're not paginating, then you can just count up the rows...
93	*
94	* @return int\|null
95	*/
96	public function total_number_of_rows() : ?int
97	{
98	$sessions_table = Query::get_table_name(Query::SESSIONS);
99	$views_table = Query::get_table_name(Query::VIEWS);
100	$column = $this->total_table_rows_column() ?? $this->required_column();
101	$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) {
102	$join->on('sessions.session_id', '=', 'views.session_id');
103	})->when(!\is_null($this->rows), function (Builder $query) {
104	$this->rows->attach_filters($query);
105	})->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()]);
106	return $query->value('total_table_rows');
107	}
108	/**
109	* Define which id column to use to count up the total table rows. This is only required
110	* for classes that don't have a required column and don't override required_column
111	*
112	* @return string\|null
113	*/
114	protected function total_table_rows_column() : ?string
115	{
116	return null;
117	}
118	/**
119	* Statistics can require that a column exists in order to be included. As an example, geos
120	* requires visitors.country_code and campaigns requires sessions.campaign_id
121	*
122	* @return string\|null
123	*/
124	protected function required_column() : ?string
125	{
126	return null;
127	}
128	private function get_statistic(string $name) : Statistic
129	{
130	return new Statistic($this->statistics->{$name}, $this->previous_period_statistics->{$name}, $this->fill_in_partial_day_range($this->statistics_by_day, $name));
131	}
132	private function query(Date_Range $range, bool $as_daily_statistics)
133	{
134	$utc_offset = Timezone::utc_offset();
135	$site_offset = Timezone::site_offset();
136	$sessions_table = Query::get_table_name(Query::SESSIONS);
137	$views_table = Query::get_table_name(Query::VIEWS);
138	$wc_orders_table = Query::get_table_name(Query::WC_ORDERS);
139	$session_statistics = Illuminate_Builder::get_builder();
140	$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) {
141	$join->on('sessions.session_id', '=', 'views.session_id');
142	})->leftJoin("{$wc_orders_table} AS wc_orders", function (JoinClause $join) {
143	$join->on('views.id', '=', 'wc_orders.view_id')->whereIn('wc_orders.status', ['wc-completed', 'completed', 'wc-processing', 'processing', 'wc-refunded', 'refunded']);
144	})->when(!\is_null($this->rows), function (Builder $query) {
145	$this->rows->attach_filters($query);
146	})->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) {
147	$query->whereNotNull($this->required_column());
148	});
149	$statistics = Illuminate_Builder::get_builder();
150	$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')->from("{$sessions_table} AS sessions")->joinSub($session_statistics, 'session_statistics', function (JoinClause $join) {
151	$join->on('sessions.session_id', '=', 'session_statistics.session_id');
152	})->whereBetween('sessions.created_at', [$range->iso_start(), $range->iso_end()])->when($as_daily_statistics, function (Builder $query) use($utc_offset, $site_offset) {
153	if ($this->chart_interval->id() === 'daily') {
154	$query->selectRaw("DATE(CONVERT_TZ(sessions.created_at, '{$utc_offset}', '{$site_offset}')) AS date");
155	} elseif ($this->chart_interval->id() === 'monthly') {
156	$query->selectRaw("DATE_FORMAT(CONVERT_TZ(sessions.created_at, '{$utc_offset}', '{$site_offset}'), '%Y-%m-01 00:00:00') AS date");
157	} elseif ($this->chart_interval->id() === 'weekly') {
158	$day_of_week = \IAWP_SCOPED\iawp()->get_option('iawp_dow', 0) + 1;
159	$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 ");
160	} else {
161	$query->selectRaw("DATE_FORMAT(CONVERT_TZ(sessions.created_at, '{$utc_offset}', '{$site_offset}'), '%Y-%m-%d %H:00:00') AS date");
162	}
163	$query->groupByRaw("date");
164	});
165	$results = \array_map(function (object $statistic) : object {
166	return $this->parse_statistic($statistic);
167	}, $statistics->get()->all());
168	if (!$as_daily_statistics) {
169	return $results[0];
170	}
171	return $results;
172	}
173	private function parse_statistic(object $statistic) : object
174	{
175	$statistic->wc_gross_sales = \floatval($statistic->wc_gross_sales);
176	$statistic->wc_refunded_amount = \floatval($statistic->wc_refunded_amount);
177	$statistic->wc_net_sales = \floatval($statistic->wc_net_sales);
178	return $statistic;
179	}
180	private function calculate_percent(float $top, float $bottom) : float
181	{
182	if ($bottom === 0.0 && $top > 0) {
183	return 100;
184	} elseif ($bottom === 0.0) {
185	return 0;
186	}
187	return \round($top / $bottom * 100, 0);
188	}
189	private function divide(float $top, float $bottom, int $precision = 0) : float
190	{
191	if ($bottom === 0.0 && $top > 0) {
192	return 100;
193	} elseif ($bottom === 0.0) {
194	return 0;
195	}
196	return \round($top / $bottom, $precision);
197	}
198	/**
199	* @param array $partial_day_range
200	* @param string $field
201	*
202	* @return array
203	*/
204	private function fill_in_partial_day_range(array $partial_day_range, string $field) : array
205	{
206	$original_start = (clone $this->date_range->start())->setTimezone(Timezone::site_timezone());
207	$start = $this->chart_interval->calculate_start_of_interval_for($original_start);
208	$original_end = (clone $this->date_range->end())->setTimezone(Timezone::site_timezone());
209	$end = $this->chart_interval->calculate_start_of_interval_for($original_end);
210	$end->add(new \DateInterval('PT1S'));
211	$date_range = new DatePeriod($start, $this->chart_interval->date_interval(), $end);
212	$filled_in_data = [];
213	foreach ($date_range as $date) {
214	$stat = $this->get_statistic_for_date($partial_day_range, $date, $field);
215	$filled_in_data[] = [$date, $stat];
216	}
217	return $filled_in_data;
218	}
219	/**
220	* @param array $partial_day_range
221	* @param DateTime $datetime_to_match
222	* @param string $field
223	*
224	* @return int Defaults to 0
225	*/
226	private function get_statistic_for_date(array $partial_day_range, DateTime $datetime_to_match, string $field) : int
227	{
228	$user_timezone = Timezone::site_timezone();
229	$default_value = 0;
230	foreach ($partial_day_range as $day) {
231	$date = $day->date;
232	$stat = $day->{$field};
233	try {
234	$datetime = new DateTime($date, $user_timezone);
235	} catch (\Throwable $e) {
236	return $default_value;
237	}
238	// Intentionally using non-strict equality to see if two distinct DateTime objects represent the same time
239	if ($datetime == $datetime_to_match) {
240	return \intval($stat);
241	}
242	}
243	return $default_value;
244	}
245	}
246