fluent-cart / app / Services / Report / Concerns / Subscription / CanCalculateChurnRateTrend.php

CanCalculateChurnRateTrend.php in FluentCart A New Era of eCommerce – Faster, Lighter, and Simpler trunk, at app/Services/Report/Concerns/Subscription/CanCalculateChurnRateTrend.php

263 lines 11.7 KB

Raw Download Zip

1	<?php
2
3	namespace FluentCart\App\Services\Report\Concerns\Subscription;
4
5	use FluentCart\App\App;
6	use FluentCart\App\Services\DateTime\DateTime;
7
8	trait CanCalculateChurnRateTrend
9	{
10	/**
11	* Retrieves the Subscriber Churn Rate trend over a specified period,
12	* aggregated by the given interval (daily, monthly, yearly).
13	*
14	* Churn Rate = (Number of Churned Subscribers / Number of Subscribers at Beginning of Period) * 100%
15	*
16	* @param string\|null $period_start_date_str Optional. The start date of the period in 'Y-m-d H:i:s' format.
17	* If null, uses the earliest available date from the database.
18	* @param string\|null $period_end_date_str Optional. The end date of the period in 'Y-m-d H:i:s' format.
19	* If null, uses the latest available date from the database.
20	* @param string $interval_type The aggregation interval: 'daily', 'monthly', or 'yearly'.
21	* @return array An array of associative arrays, each containing 'trend_date' and 'value' (churn rate percentage).
22	*/
23	public function get_churn_rate_trend($period_start_date_str = null, $period_end_date_str = null, $interval_type = 'monthly', $currency = null)
24	{
25	$start_date_obj = DateTime::anyTimeToGmt($period_start_date_str);
26	$end_date_obj = DateTime::anyTimeToGmt($period_end_date_str);
27
28	$start_sql = DateTime::anyTimeToGmt($period_start_date_str)->format('Y-m-d H:i:s');
29	$end_sql = DateTime::anyTimeToGmt($period_end_date_str)->format('Y-m-d H:i:s');
30	$originalTimeZone = DateTime::extractTimezone($period_start_date_str);
31	$offsetMinutes = DateTime::getTimezoneOffsetMinutes($originalTimeZone->getName());
32
33
34	$data = [];
35	$period_intervals = []; // Stores start/end dates for each interval
36
37	$temp_date_iterator = clone $start_date_obj;
38
39	$date_format = 'Y-m-d';
40	switch ($interval_type) {
41	case 'daily':
42	$temp_date_iterator->startOfDay();
43	$end_date_obj->endOfDay();
44	$date_format = 'Y-m-d';
45	break;
46	case 'monthly':
47	$temp_date_iterator->startOfMonth();
48	$end_date_obj->endOfMonth(); // End of period for churn events
49	$date_format = 'Y-m';
50	break;
51	case 'yearly':
52	$temp_date_iterator->startOfYear();
53	$end_date_obj->endOfYear(); // End of period for churn events
54	$date_format = 'Y';
55	break;
56	default:
57	$temp_date_iterator->startOfMonth();
58	$end_date_obj->endOfMonth();
59	$date_format = 'Y-m';
60	$interval_type = 'monthly';
61	break;
62	}
63
64	// First pass: Collect all interval start/end dates for churn rate calculation
65	$all_start_dates_of_intervals_to_fetch = [];
66	$all_end_dates_of_intervals_to_fetch = [];
67
68	while ($temp_date_iterator <= $end_date_obj) {
69	$interval_start_date_obj = clone $temp_date_iterator;
70	$interval_end_date_obj = clone $temp_date_iterator;
71
72	switch ($interval_type) {
73	case 'daily':
74	$interval_start_date_obj->startOfDay();
75	$interval_end_date_obj->endOfDay();
76	break;
77	case 'monthly':
78	$interval_start_date_obj->startOfMonth();
79	$interval_end_date_obj->endOfMonth();
80	break;
81	case 'yearly':
82	$interval_start_date_obj->startOfYear();
83	$interval_end_date_obj->endOfYear();
84	break;
85	}
86
87	$trend_key = $temp_date_iterator->format($date_format);
88	$period_intervals[$trend_key] = [
89	'start_of_period' => $interval_start_date_obj->format('Y-m-d H:i:s'),
90	'end_of_period' => $interval_end_date_obj->format('Y-m-d H:i:s')
91	];
92
93	$all_start_dates_of_intervals_to_fetch[] = $interval_start_date_obj->format('Y-m-d H:i:s');
94	$all_end_dates_of_intervals_to_fetch[] = $interval_end_date_obj->format('Y-m-d H:i:s');
95
96
97	// Move to the next period
98	switch ($interval_type) {
99	case 'daily':
100	$temp_date_iterator->addDays(1);
101	break;
102	case 'monthly':
103	$temp_date_iterator->addMonth();
104	break;
105	case 'yearly':
106	$temp_date_iterator->addYear();
107	break;
108	}
109	}
110
111	// Efficiently fetch all relevant subscriptions in one go
112	$overall_min_date = min($all_start_dates_of_intervals_to_fetch);
113	$overall_max_date = max($all_end_dates_of_intervals_to_fetch);
114
115	$all_relevant_subscriptions = $this->get_all_relevant_subscriptions_for_churn_rate(
116	$overall_min_date, $overall_max_date, $offsetMinutes, $currency
117	);
118
119	// Initialize results array for each trend date key
120	$trend_results = array_fill_keys(array_keys($period_intervals), 0.00);
121
122	// Calculate churn rate for each interval
123	foreach ($period_intervals as $trend_date_key => $interval_dates) {
124	$start_of_period_obj = DateTime::parse($interval_dates['start_of_period']);
125	$end_of_period_obj = DateTime::parse($interval_dates['end_of_period']);
126
127	$subscribers_at_beginning = 0;
128	$churned_subscribers_in_period = 0;
129
130	foreach ($all_relevant_subscriptions as $subscription) {
131	$sub_created_at_obj = DateTime::parse($subscription['created_at']);
132	$sub_expire_at_obj = null;
133	if (!empty($subscription['expire_at'])) {
134	$sub_expire_at_obj = DateTime::parse($subscription['expire_at']);
135	}
136	$sub_canceled_at_obj = null;
137	if (!empty($subscription['canceled_at'])) {
138	$sub_canceled_at_obj = DateTime::parse($subscription['canceled_at']);
139	}
140
141	// Determine if active at the START of the period
142	$is_active_at_beginning = false;
143	if (in_array($subscription['status'], ['active', 'trialling', 'pending'])) { // Check original status types
144	if ($sub_created_at_obj <= $start_of_period_obj) {
145	$not_expired_at_beginning = ($sub_expire_at_obj === null \|\| $sub_expire_at_obj >= $start_of_period_obj);
146	$not_cancelled_at_beginning = ($sub_canceled_at_obj === null \|\| $sub_canceled_at_obj > $start_of_period_obj); // Use > for active on the exact start date
147
148	if ($not_expired_at_beginning && $not_cancelled_at_beginning) {
149	$is_active_at_beginning = true;
150	}
151	}
152	}
153
154	if ($is_active_at_beginning) {
155	$subscribers_at_beginning++;
156	}
157
158	// Determine if churned WITHIN this period
159	$is_churned_in_period = false;
160	if (in_array($subscription['status'], ['cancelled', 'expired', 'failed'])) { // Check churn status types
161	$churn_date_obj = null;
162	if (!empty($subscription['canceled_at'])) {
163	$churn_date_obj = $sub_canceled_at_obj;
164	} elseif (!empty($subscription['expire_at']) && $subscription['status'] === 'expired') {
165	$churn_date_obj = $sub_expire_at_obj;
166	}
167	// Add other conditions for 'failed' if they map to a specific date column
168
169	if ($churn_date_obj && $churn_date_obj >= $start_of_period_obj && $churn_date_obj <= $end_of_period_obj) {
170	$churned_subscribers_in_period++;
171	}
172	}
173	}
174
175	$churn_rate = 0.00;
176	if ($subscribers_at_beginning > 0) {
177	$churn_rate = ($churned_subscribers_in_period / $subscribers_at_beginning) * 100;
178	}
179	$trend_results[$trend_date_key] = round($churn_rate, 2); // Round to 2 decimal places
180	}
181
182
183	// Populate the final data array
184	foreach ($period_intervals as $trend_date_key => $dates) {
185	$data[] = [
186	'trend_date' => $trend_date_key,
187	'value' => $trend_results[$trend_date_key]
188	];
189	}
190
191	return $data;
192	}
193
194	public function get_daily_churn_rate_trend($period_start_date_str = null, $period_end_date_str = null)
195	{
196	return $this->get_churn_rate_trend($period_start_date_str, $period_end_date_str, 'daily');
197	}
198
199	public function get_monthly_churn_rate_trend($period_start_date_str = null, $period_end_date_str = null, $currency = null)
200	{
201	return $this->get_churn_rate_trend($period_start_date_str, $period_end_date_str, $currency);
202	}
203
204	public function get_yearly_churn_rate_trend($period_start_date_str = null, $period_end_date_str = null)
205	{
206	return $this->get_churn_rate_trend($period_start_date_str, $period_end_date_str, 'yearly');
207	}
208
209	/**
210	* Fetches all relevant subscriptions that could potentially be active or churned
211	* within the overall date range for churn rate calculation.
212	* Assumes $this->wpdb and $this->table_subscriptions are available.
213	*
214	* @param string $overall_min_date The earliest date in the entire reporting range.
215	* @param string $overall_max_date The latest date in the entire reporting range.
216	* @return array An array of subscription records.
217	*/
218	protected function get_all_relevant_subscriptions_for_churn_rate(string $overall_min_date, string $overall_max_date, $offsetMinutes = 0, $currency = null): array
219	{
220	global $wpdb;
221	$table_subscriptions = 'fct_subscriptions';
222	$table_orders = 'fct_orders';
223
224
225	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Query is prepared with placeholders
226	$subscriptions_query = App::db()->table($table_subscriptions . ' as s')
227	->select('s.id')
228	->selectRaw('s.created_at + INTERVAL ? MINUTE as created_at', [$offsetMinutes])
229	->selectRaw('s.expire_at + INTERVAL ? MINUTE as expire_at', [$offsetMinutes])
230	->selectRaw('s.canceled_at + INTERVAL ? MINUTE as canceled_at', [$offsetMinutes])
231	->select('s.status')
232	->join($table_orders . ' as o', 's.parent_order_id', '=', 'o.id')
233	->where(function($query) use ($overall_min_date, $overall_max_date) {
234	$query->where(function($q) use ($overall_min_date, $overall_max_date) {
235	$q->where('s.created_at', '<=', $overall_max_date)
236	->where(function($sub) use ($overall_min_date) {
237	$sub->whereNull('s.expire_at')
238	->orWhere('s.expire_at', '>=', $overall_min_date);
239	})
240	->where(function($sub) use ($overall_min_date) {
241	$sub->whereNull('s.canceled_at')
242	->orWhere('s.canceled_at', '>', $overall_min_date);
243	});
244	})
245	->orWhere(function($q) use ($overall_min_date, $overall_max_date) {
246	$q->where('s.created_at', '>=', $overall_min_date)
247	->where('s.created_at', '<=', $overall_max_date);
248	});
249	});
250
251	// Optional currency filter
252	if (!empty($currency)) {
253	$subscriptions_query->where('o.currency', $currency);
254	}
255
256	// Execute query
257	$subscriptions = $subscriptions_query->get()->toArray();
258
259
260	return $subscriptions;
261	}
262	}
263