class-merchant-analytics-data-provider.php in Product Labels, Quick View, Buy Now, Pre-Orders, Frequently Bought Together & More for WooCommerce – Merchant 2.3.1, at inc/analytics/class-merchant-analytics-data-provider.php

772 lines 21.0 KB Raw Download Zip

1	<?php
2
3	// Exit if accessed directly.
4	if ( ! defined( 'ABSPATH' ) ) {
5	exit;
6	}
7
8	/**
9	* Class Merchant_Analytics_Data
10	*
11	* This class is responsible for providing data for analytics.
12	*/
13	class Merchant_Analytics_Data_Provider {
14
15	/**
16	* @var string The start date in 'm/d/y H:i:s' format.
17	*/
18	private $start_date;
19
20	/**
21	* @var string The end date in 'm/d/y H:i:s' format.
22	*/
23	private $end_date;
24
25	/**
26	* @var Merchant_Analytics_DB_ORM The analytics Database ORM instance.
27	*/
28	private $analytics;
29
30	public function __construct( $analytics = null ) {
31	if ( ! $analytics ) {
32	$this->analytics = new Merchant_Analytics_DB_ORM();
33	} else {
34	$this->analytics = $analytics;
35	}
36	}
37
38	/**
39	* Set the start date for filtering data.
40	*
41	* @param string $start_date The start date in 'm/d/y H:i:s' format.
42	*/
43	public function set_start_date( $start_date ) {
44	$this->start_date = $this->validate_date( $start_date );
45	}
46
47	/**
48	* Set the end date for filtering data.
49	*
50	* @param string $end_date The end date in 'm/d/y H:i:s' format.
51	*/
52	public function set_end_date( $end_date ) {
53	$this->end_date = $this->validate_date( $end_date );
54	}
55
56	/**
57	* Get the start date. If not set, initialize with the default value.
58	*
59	* @return string The start date in 'm/d/y H:i:s' format.
60	*/
61	public function get_start_date() {
62	if ( ! $this->start_date ) {
63	// Default to the beginning of today in GMT
64	$this->start_date = gmdate( 'Y-m-d 00:00:00', strtotime( '-30 days' ) );
65	}
66
67	return $this->convert_date_format( $this->start_date );
68	}
69
70	/**
71	* Get the end date. If not set, initialize with the default value.
72	*
73	* @return string The end date in 'm/d/y H:i:s' format.
74	*/
75	public function get_end_date() {
76	if ( ! $this->end_date ) {
77	// Default to the current time in GMT
78	$this->end_date = gmdate( 'Y-m-d H:i:s', time() );
79	}
80
81	return $this->convert_date_format( $this->end_date );
82	}
83
84	/**
85	* Get the total revenue.
86	*
87	* @return float The total revenue.
88	*/
89	public function get_revenue() {
90	$orders_data = $this->get_dated_orders_with_revenue( - 1 );
91	if ( ! empty( $orders_data ) ) {
92	return array_sum( array_column( $orders_data, 'revenue' ) );
93	}
94
95	return 0;
96	}
97
98	/**
99	* Get orders records with in date range.
100	*
101	* @param int $limit The limit of the query.
102	*
103	* @return array\|null
104	*/
105	public function get_orders_in_period( $limit = 10000 ) {
106	$db_orders_records = $this->analytics
107	->select()
108	->where( 'order_id > %d', 0 )
109	->where( 'event_type = %s', 'order' )
110	->where_between_dates( $this->get_start_date(), $this->get_end_date() )
111	->limit( $limit )
112	->get();
113
114	// Reset the query to avoid conflicts
115	$this->analytics->reset_query();
116
117	return $db_orders_records;
118	}
119
120	/**
121	* Get the dated revenue.
122	*
123	* @param int $limit The limit of the query.
124	*
125	* @return array The dated revenue.
126	*/
127	public function get_dated_orders_with_revenue( $limit = 10000 ) {
128	$db_orders_records = $this->get_orders_in_period( $limit );
129
130	// Group campaigns by order_id and source_product_id
131	$grouped_orders = array();
132	foreach ( $db_orders_records as $order ) {
133	$order_id = $order['order_id'];
134	$product_id = $order['source_product_id'];
135	$campaign_cost = (float) $order['campaign_cost'];
136
137	// Initialize the order if it doesn't exist
138	if ( ! isset( $grouped_orders[ $order_id ] ) ) {
139	$grouped_orders[ $order_id ] = array(
140	'order_id' => $order_id,
141	'order_subtotal' => (float) $order['order_subtotal'],
142	'order_total' => (float) $order['order_total'],
143	'customer_id' => $order['customer_id'],
144	'timestamp' => $order['timestamp'],
145	'products' => array(),
146	'revenue' => 0,
147	);
148	}
149
150	// Update the product with the biggest campaign cost
151	if (
152	! isset( $grouped_orders[ $order_id ]['products'][ $product_id ] )
153	\|\| $campaign_cost > $grouped_orders[ $order_id ]['products'][ $product_id ]
154	) {
155	$grouped_orders[ $order_id ]['products'][ $product_id ] = $campaign_cost;
156	}
157	}
158
159	// Calculate revenue for each order
160	foreach ( $grouped_orders as &$order ) {
161	$order['revenue'] = array_sum( $order['products'] );
162	$order['products'] = array_map( static function ( $product_id, $campaign_cost ) {
163	return array(
164	'product_id' => $product_id,
165	'campaign_cost' => $campaign_cost,
166	);
167	}, array_keys( $order['products'] ), $order['products'] );
168	}
169	unset( $order );
170
171	// Return the grouped orders as an array
172	return array_values( $grouped_orders );
173	}
174
175	/**
176	* Get the total number of reviews collected.
177	*
178	* @param int $limit The limit of the query.
179	*
180	* @return int The total number of reviews collected.
181	*/
182	public function get_collected_reviews_count( $limit = 10000 ) {
183	$result = $this->analytics
184	->where( 'event_type = %s', 'submit_product_review' )
185	->where_between_dates( $this->get_start_date(), $this->get_end_date() )
186	->count( 'id' )
187	->limit( $limit )
188	->first();
189
190	$this->analytics->reset_query(); // Reset the query to avoid conflicts with other queries.
191
192	if ( ! empty( $result ) ) {
193	return $result['count_id'];
194	}
195
196	return 0;
197	}
198
199	/**
200	* Get the total number of sent emails.
201	*
202	* @param int $limit The limit of the query.
203	*
204	* @return int The total number of sent emails.
205	*/
206	public function get_sent_emails_count( $limit = 10000 ) {
207	$result = $this->analytics
208	->where( array(
209	'event_type' => array(
210	'in' => array(
211	'send_review_request_email',
212	'send_review_discount_code_email',
213	'send_review_request_reminder_email',
214	'send_review_discount_code_reminder_email',
215	),
216	),
217	) )
218	->where_between_dates( $this->get_start_date(), $this->get_end_date() )
219	->count( 'id' )
220	->limit( $limit )
221	->first();
222
223	$this->analytics->reset_query(); // Reset the query to avoid conflicts with other queries.
224
225	if ( ! empty( $result ) ) {
226	return $result['count_id'];
227	}
228
229	return 0;
230	}
231
232	/**
233	* Get the total number of scheduled emails.
234	*
235	* @param int $limit The limit of the query.
236	*
237	* @return int The total number of scheduled emails.
238	*/
239	public function get_scheduled_emails_count( $limit = 10000 ) {
240	$result = $this->analytics
241	->where( array(
242	'event_type' => array(
243	'in' => array(
244	'schedule_review_request',
245	'schedule_review_request_reminder',
246	'schedule_discount_code_review_email',
247	),
248	),
249	) )
250	->where_between_dates( $this->get_start_date(), $this->get_end_date() )
251	->count( 'id' )
252	->limit( $limit )
253	->first();
254
255	$this->analytics->reset_query(); // Reset the query to avoid conflicts with other queries.
256
257	if ( ! empty( $result ) ) {
258	return $result['count_id'];
259	}
260
261	return 0;
262	}
263
264	/**
265	* Get the total number of opened emails.
266	*
267	* @param int $limit The limit of the query.
268	*
269	* @return int The total number of opened emails.
270	*/
271	public function get_opened_emails_count( $limit = 10000 ) {
272	$result = $this->analytics
273	->where( 'event_type LIKE %s', 'email_open_%' )
274	->where_between_dates( $this->get_start_date(), $this->get_end_date() )
275	->count( 'id' )
276	->limit( $limit )
277	->first();
278
279	$this->analytics->reset_query(); // Reset the query to avoid conflicts with other queries.
280
281	if ( ! empty( $result ) ) {
282	return $result['count_id'];
283	}
284
285	return 0;
286	}
287
288	/**
289	* Get the dated revenue.
290	*
291	* @param $limit int The limit of the query.
292	*
293	* @return array\|null
294	*/
295	public function get_dated_impressions( $limit = 10000 ) {
296	$impressions = $this->analytics
297	->select( array( 'timestamp', 'count(id) as impressions_count' ) )
298	->where( 'event_type = %s', 'impression' )
299	->where_between_dates( $this->get_start_date(), $this->get_end_date() )
300	->group_by( 'DATE(timestamp)' )
301	->order_by( 'timestamp', 'ASC' )
302	->limit( $limit )
303	->get();
304
305	$this->analytics->reset_query(); // Reset the query to avoid conflicts with other queries.
306
307	return $impressions;
308	}
309
310	/**
311	* Get the average order value.
312	*
313	* @return float The average order value.
314	*/
315	public function get_average_order_value() {
316	$orders_data = $this->get_dated_orders_with_revenue( - 1 );
317	if ( ! empty( $orders_data ) ) {
318	$order_subtotals = array_column( $orders_data, 'order_subtotal' );
319	$orders_count = count( $order_subtotals );
320	if ( $orders_count > 0 ) {
321	return array_sum( $order_subtotals ) / $orders_count;
322	}
323	}
324
325	return 0;
326	}
327
328	/**
329	* Get the total number of orders.
330	*
331	* @return int The total number of orders.
332	*/
333	public function get_orders_count() {
334	$orders_data = $this->get_dated_orders_with_revenue( - 1 );
335	if ( ! empty( $orders_data ) ) {
336	return count( $orders_data );
337	}
338
339	return 0;
340	}
341
342	/**
343	* Get the total number of impressions.
344	*
345	* @return int The total number of impressions.
346	*/
347	public function get_total_impressions() {
348	$total_impressions = $this->analytics
349	->where( 'event_type = %s', 'impression' )
350	->count( 'id' )
351	->where_between_dates( $this->get_start_date(), $this->get_end_date() )
352	->first();
353
354	$this->analytics->reset_query(); // Reset the query to avoid conflicts with other queries.
355
356	if ( ! empty( $total_impressions ) ) {
357	return $total_impressions['count_id'];
358	}
359
360	return 0;
361	}
362
363	/**
364	* Get the conversion rate percentage.
365	*
366	* @return float The conversion rate percentage.
367	*/
368	public function get_conversion_rate_percentage() {
369	$orders_count = $this->get_orders_count();
370	$total_impressions = $this->get_total_impressions();
371	if ( $total_impressions > 0 && $orders_count > 0 ) {
372	return ( $orders_count / $total_impressions ) * 100;
373	}
374
375	return 0;
376	}
377
378	/**
379	* Get total campaign impressions.
380	*
381	* @param int\|string $campaign_id The campaign ID.
382	* @param string $module_id The module ID.
383	*
384	* @return int The total campaign impressions or 0 if not found.
385	*/
386	public function get_campaign_impressions( $campaign_id, $module_id ) {
387	$campaign_impressions = $this->analytics
388	->where( 'event_type = %s', 'impression' )
389	->where( 'campaign_id = %s', $campaign_id )
390	->where( 'module_id = %s', $module_id )
391	->count( 'id' )
392	->where_between_dates( $this->get_start_date(), $this->get_end_date() )
393	->first();
394
395	$this->analytics->reset_query(); // Reset the query to avoid conflicts with other queries.
396
397	if ( ! empty( $campaign_impressions ) ) {
398	return (int) $campaign_impressions['count_id'];
399	}
400
401	return 0;
402	}
403
404	/**
405	* Get total campaign clicks.
406	*
407	* @param int\|string $campaign_id The campaign ID.
408	* @param string $module_id The module ID.
409	*
410	* @return int The total campaign clicks or 0 if not found.
411	*/
412	public function get_campaign_clicks( $campaign_id, $module_id ) {
413	$campaign_clicks = $this->analytics
414	->where( 'event_type = %s', 'add_to_cart' )
415	->where( 'campaign_id = %s', $campaign_id )
416	->where( 'module_id = %s', $module_id )
417	->count( 'id' )
418	->where_between_dates( $this->get_start_date(), $this->get_end_date() )
419	->first();
420
421	$this->analytics->reset_query(); // Reset the query to avoid conflicts with other queries.
422
423	if ( ! empty( $campaign_clicks ) ) {
424	return $campaign_clicks['count_id'];
425	}
426
427	return 0;
428	}
429
430	/**
431	* Get total campaign orders count.
432	*
433	* @param int\|string $campaign_id The campaign ID.
434	* @param string $module_id The module ID.
435	*
436	* @return int The total campaign orders count or 0 if not found.
437	*/
438	public function get_campaign_orders_count( $campaign_id, $module_id ) {
439	$campaign_orders = $this->analytics
440	->distinct( 'order_id' )
441	//->where( 'order_id > %d', 0 )
442	->where( 'event_type = %s', 'order' )
443	->where( 'campaign_id = %s', $campaign_id )
444	->where( 'module_id = %s', $module_id )
445	->count( 'order_id' )
446	->where_between_dates( $this->get_start_date(), $this->get_end_date() )
447	->first();
448
449	$this->analytics->reset_query(); // Reset the query to avoid conflicts with other queries.
450
451	if ( ! empty( $campaign_orders ) ) {
452	return $campaign_orders['count_order_id'];
453	}
454
455	return 0;
456	}
457
458	/**
459	* Get campaign revenue.
460	*
461	* @param int\|string $campaign_id The campaign ID.
462	* @param string $module_id The module ID.
463	*
464	* @return float The campaign revenue or 0 if not found.
465	*/
466	public function get_campaign_revenue( $campaign_id, $module_id ) {
467	$revenue = 0;
468	$db_orders_records = $this->analytics
469	->where( 'order_id > %d', 0 )
470	->where( 'event_type = %s', 'order' )
471	->where( 'campaign_id = %s', $campaign_id )
472	->where( 'module_id = %s', $module_id )
473	->where_between_dates( $this->get_start_date(), $this->get_end_date() )
474	->get();
475
476	$this->analytics->reset_query();
477
478	$grouped_orders = array();
479	foreach ( $db_orders_records as $order ) {
480	$order_id = $order['order_id'];
481	$product_id = $order['source_product_id'];
482	$campaign_cost = (float) $order['campaign_cost'];
483
484	if ( ! isset( $grouped_orders[ $order_id ] ) ) {
485	$grouped_orders[ $order_id ] = array( 'products' => array() );
486	}
487
488	if ( ! isset( $grouped_orders[ $order_id ]['products'][ $product_id ] ) \|\| $campaign_cost > $grouped_orders[ $order_id ]['products'][ $product_id ] ) {
489	$grouped_orders[ $order_id ]['products'][ $product_id ] = $campaign_cost;
490	}
491	}
492
493	foreach ( $grouped_orders as $order ) {
494	$revenue += array_sum( $order['products'] );
495	}
496
497	return $revenue;
498	}
499
500	/**
501	* Get campaign average order value.
502	*
503	* @param int\|string $campaign_id The campaign ID.
504	* @param string $module_id The module ID.
505	*
506	* @return float The campaign average order value or 0 if not found.
507	*/
508	public function get_campaign_average_order_value( $campaign_id, $module_id ) {
509	$campaign_average_order_value = $this->analytics
510	->distinct( 'order_id' )
511	->where( 'order_id > %d', 0 )
512	->where( 'event_type = %s', 'order' )
513	->where( 'campaign_id = %s', $campaign_id )
514	->where( 'module_id = %s', $module_id )
515	->avg( 'order_subtotal' )
516	->where_between_dates( $this->get_start_date(), $this->get_end_date() )
517	->first();
518
519	$this->analytics->reset_query(); // Reset the query to avoid conflicts with other queries.
520
521	if ( ! empty( $campaign_average_order_value ) ) {
522	return $campaign_average_order_value['avg_order_subtotal'];
523	}
524
525	return 0;
526	}
527
528	/**
529	* Get campaign CTR percentage.
530	*
531	* @param int\|string $campaign_id The campaign ID.
532	* @param string $module_id The module ID.
533	*
534	* @return float The campaign CTR percentage or 0 if not found.
535	*/
536	public function get_campaign_ctr_percentage( $campaign_id, $module_id ) {
537	$campaign_orders_count = $this->get_campaign_clicks( $campaign_id, $module_id );
538	$campaign_impressions = $this->get_campaign_impressions( $campaign_id, $module_id );
539	if ( $campaign_impressions > 0 && $campaign_orders_count > 0 ) {
540	return ( $campaign_orders_count / $campaign_impressions ) * 100;
541	}
542
543	return 0;
544	}
545
546	/**
547	* Get module CTR percentage.
548	*
549	* @return float The module CTR percentage or 0 if not found.
550	*/
551	public function get_module_ctr_percentage( $module_id ) {
552	$module_orders_count = $this->get_module_clicks( $module_id );
553	$module_impressions = $this->get_module_impressions( $module_id );
554	if ( $module_impressions > 0 && $module_orders_count > 0 ) {
555	return ( $module_orders_count / $module_impressions ) * 100;
556	}
557
558	return 0;
559	}
560
561	/**
562	* Get the top performing campaigns.
563	*
564	* @return array The top performing campaigns.
565	*/
566	public function get_top_performing_campaigns( $limit = 10 ) {
567	$top_performing_campaigns = $this->analytics
568	->select( array( '*', 'COUNT(id) as orders_count' ) )
569	->where( 'event_type = %s', 'order' )
570	->where( 'campaign_id != %s', '' )
571	->where( 'campaign_id != %s', '0' )
572	->where_between_dates( $this->get_start_date(), $this->get_end_date() )
573	->order_by( 'orders_count', 'DESC' )
574	->group_by( 'campaign_id' )
575	->limit( $limit )
576	->get();
577
578	$this->analytics->reset_query(); // Reset the query to avoid conflicts with other queries.
579
580	return $top_performing_campaigns;
581	}
582
583	/**
584	* Get module impressions count.
585	*
586	* @param $module_id string The module ID.
587	*
588	* @return int The module impressions count.
589	*/
590	public function get_module_impressions( $module_id ) {
591	$module_impressions = $this->analytics
592	->where( 'event_type = %s', 'impression' )
593	->where( 'module_id = %s', $module_id )
594	->count( 'id' )
595	->where_between_dates( $this->get_start_date(), $this->get_end_date() )
596	->first();
597
598	$this->analytics->reset_query(); // Reset the query to avoid conflicts with other queries.
599
600	if ( ! empty( $module_impressions ) ) {
601	return $module_impressions['count_id'];
602	}
603
604	return 0;
605	}
606
607	/**
608	* Get module clicks count.
609	*
610	* @param $module_id string The module ID.
611	*
612	* @return int The module clicks count.
613	*/
614	public function get_module_clicks( $module_id ) {
615	$module_clicks = $this->analytics
616	->where( 'event_type = %s', 'add_to_cart' )
617	->where( 'module_id = %s', $module_id )
618	->count( 'id' )
619	->where_between_dates( $this->get_start_date(), $this->get_end_date() )
620	->first();
621
622	$this->analytics->reset_query(); // Reset the query to avoid conflicts with other queries.
623
624	if ( ! empty( $module_clicks ) ) {
625	return $module_clicks['count_id'];
626	}
627
628	return 0;
629	}
630
631	/**
632	* Get module orders count.
633	*
634	* @param $module_id string The module ID.
635	*
636	* @return int The module orders count.
637	*/
638	public function get_module_orders_count( $module_id ) {
639	$module_orders = $this->analytics
640	->distinct( 'order_id' )
641	->where( 'order_id > %d', 0 )
642	->where( 'event_type = %s', 'order' )
643	->where( 'module_id = %s', $module_id )
644	->count( 'order_id' )
645	->where_between_dates( $this->get_start_date(), $this->get_end_date() )
646	->first();
647
648	$this->analytics->reset_query(); // Reset the query to avoid conflicts with other queries.
649
650	if ( ! empty( $module_orders ) ) {
651	return $module_orders['count_order_id'];
652	}
653
654	return 0;
655	}
656
657	/**
658	* Get module revenue.
659	*
660	* @param $module_id string The module ID.
661	*
662	* @return float The module revenue.
663	*/
664	public function get_module_revenue( $module_id ) {
665	$revenue = 0;
666	$db_orders_records = $this->analytics
667	->where( 'order_id > %d', 0 )
668	->where( 'event_type = %s', 'order' )
669	->where( 'module_id = %s', $module_id )
670	->where_between_dates( $this->get_start_date(), $this->get_end_date() )
671	->get();
672
673	$this->analytics->reset_query();
674
675	$grouped_orders = array();
676	foreach ( $db_orders_records as $order ) {
677	$order_id = $order['order_id'];
678	$product_id = $order['source_product_id'];
679	$campaign_cost = (float) $order['campaign_cost'];
680
681	if ( ! isset( $grouped_orders[ $order_id ] ) ) {
682	$grouped_orders[ $order_id ] = array( 'products' => array() );
683	}
684
685	if ( ! isset( $grouped_orders[ $order_id ]['products'][ $product_id ] ) \|\| $campaign_cost > $grouped_orders[ $order_id ]['products'][ $product_id ] ) {
686	$grouped_orders[ $order_id ]['products'][ $product_id ] = $campaign_cost;
687	}
688	}
689
690	foreach ( $grouped_orders as $order ) {
691	$revenue += array_sum( $order['products'] );
692	}
693
694	return $revenue;
695	}
696
697	/**
698	* Get module average order value.
699	*
700	* @param $module_id string The module ID.
701	*
702	* @return float The module average order value.
703	*/
704	public function get_module_average_order_value( $module_id ) {
705	$module_average_order_value = $this->analytics
706	->distinct( 'order_id' )
707	->where( 'order_id > %d', 0 )
708	->where( 'event_type = %s', 'order' )
709	->where( 'module_id = %s', $module_id )
710	->avg( 'order_subtotal' )
711	->where_between_dates( $this->get_start_date(), $this->get_end_date() )
712	->first();
713
714	$this->analytics->reset_query(); // Reset the query to avoid conflicts with other queries.
715
716	if ( ! empty( $module_average_order_value ) ) {
717	return $module_average_order_value['avg_order_subtotal'];
718	}
719
720	return 0;
721	}
722
723	/**
724	* Get module conversion rate percentage.
725	*
726	* @param $module_id string The module ID.
727	*
728	* @return float The module conversion rate percentage.
729	*/
730	public function get_module_conversion_rate_percentage( $module_id ) {
731	$module_orders_count = $this->get_module_orders_count( $module_id );
732	$module_impressions = $this->get_module_impressions( $module_id );
733	if ( $module_impressions > 0 && $module_orders_count > 0 ) {
734	return ( $module_orders_count / $module_impressions ) * 100;
735	}
736
737	return 0;
738	}
739
740	/**
741	* Validate the date format.
742	*
743	* @param string $date The date string to validate.
744	*
745	* @return string Validated date string.
746	* @throws InvalidArgumentException If the date format is invalid.
747	*/
748	private function validate_date( $date ) {
749	$date .= ' 23:59:59'; // make the date compatible with the format without forcing the user to select the time.
750	$d = DateTime::createFromFormat( 'm/d/y H:i:s', $date );
751	if ( $d && $d->format( 'm/d/y H:i:s' ) === $date ) {
752	return $date;
753	}
754	throw new InvalidArgumentException( 'Invalid date format. Expected m/d/y H:i:s given ' . esc_html( $date ) );
755	}
756
757	/**
758	* Convert the date from m/d/y H:i:s format to 'Y-m-d H:i:s' to make it compatible with the database.
759	*
760	* @param string $date The date string to convert.
761	*
762	* @return string The date string in 'Y-m-d H:i:s' format.
763	*/
764	protected function convert_date_format( $date ) {
765	$d = DateTime::createFromFormat( 'm/d/y H:i:s', $date );
766	if ( $d ) {
767	return $d->format( 'Y-m-d H:i:s' );
768	}
769
770	return '';
771	}
772	}