wpfunnels / includes / core / Reporting / ReportGenerator.php

ReportGenerator.php in WPFunnels – Funnel Builder for WooCommerce with Checkout & One Click Upsell 3.12.4, at includes/core/Reporting/ReportGenerator.php

589 lines 18.7 KB

Raw Download Zip

1	<?php
2
3	namespace WPFunnels\Report;
4
5	use WPFunnels\Wpfnl_functions;
6
7	class ReportGenerator {
8
9
10	/**
11	* Format a monetary value using WooCommerce's decimal settings.
12	*
13	* Uses wc_format_decimal() + wc_get_price_decimals() when WooCommerce
14	* is active, otherwise falls back to rounding to 2 decimal places.
15	*
16	* @param mixed $value Raw numeric value.
17	* @return float Formatted decimal value.
18	*
19	* @since 3.6.0
20	*/
21	private static function format_price( $value ) {
22	if ( function_exists( 'wc_format_decimal' ) ) {
23	return (float) wc_format_decimal( $value, wc_get_price_decimals() );
24	}
25	return round( (float) $value, 2 );
26	}
27
28
29	/**
30	* Get overview data of all funnels
31	*
32	* @param $start_date
33	* @param $end_date
34	* @return array
35	*
36	* @since 3.2.0
37	*/
38	public static function get_overview( $start_date, $end_date ) {
39	$total_orders = self::get_total_orders( $start_date, $end_date );
40	$total_customers = self::get_total_customers( $start_date, $end_date );
41	$total_sales = self::get_total_sales( $start_date, $end_date );
42	$total_ob_revenue = self::get_total_ob_sales( $start_date, $end_date );
43	$wc_total = self::get_wc_total_sales( $start_date, $end_date );
44	$wc_total_orders = self::get_wc_total_orders( $start_date, $end_date );
45	// Non-funnel (native WooCommerce) revenue and order count
46	$store_revenue = max( 0, $wc_total - (float) $total_sales );
47	$store_order_count = max( 0, $wc_total_orders - (int) $total_orders );
48	$avg_order_value = (int) $total_orders > 0 ? ( (float) $total_sales / (int) $total_orders ) : 0;
49	// Store baseline AOV = native WC revenue ÷ native WC orders (funnel orders excluded)
50	$store_aov = $store_order_count > 0 ? ( $store_revenue / $store_order_count ) : 0;
51
52	$funnel_checkout_visits = CheckoutTracker::get_funnel_checkout_visits( $start_date, $end_date );
53	$store_checkout_visits = CheckoutTracker::get_store_checkout_visits( $start_date, $end_date );
54	// Cap at 100 — visits may be undercount if tracking started after orders were placed.
55	$checkout_conversion_rate = $funnel_checkout_visits > 0
56	? min( 100, round( ( (int) $total_orders / $funnel_checkout_visits ) * 100, 1 ) )
57	: 0;
58	$store_checkout_conversion_rate = $store_checkout_visits > 0
59	? min( 100, round( ( $store_order_count / $store_checkout_visits ) * 100, 1 ) )
60	: 0;
61
62	$result = array(
63	'total_orders' => (int) $total_orders,
64	'total_customers' => (int) $total_customers,
65	'total_sales' => self::format_price( $total_sales ),
66	'total_ob_revenue' => self::format_price( $total_ob_revenue ),
67	'total_revenue' => self::format_price( $total_sales ),
68	'avg_order_value' => self::format_price( $avg_order_value ),
69	'store_aov' => self::format_price( $store_aov ),
70	'store_total_orders' => (int) $store_order_count,
71	'store_revenue' => self::format_price( $store_revenue ),
72	'conversion_rate' => $checkout_conversion_rate, // backward compat alias
73	'checkout_conversion_rate' => $checkout_conversion_rate,
74	'store_checkout_conversion_rate' => $store_checkout_conversion_rate,
75	'ob_acceptance_rate' => self::get_ob_acceptance_rate( $start_date, $end_date ),
76	'upsell_acceptance_rate' => self::get_upsell_acceptance_rate( $start_date, $end_date ),
77	'downsell_recovery_rate' => self::get_downsell_recovery_rate( $start_date, $end_date ),
78	'checkout_completion_rate' => $checkout_conversion_rate,
79	);
80
81	$response['status'] = true;
82	$response['data'] = apply_filters( 'wpfunnels/funnels-overview-data', $result, $start_date, $end_date );
83	return $response;
84	}
85
86
87	public static function get_stats( $start_date, $end_date, $interval ) {
88	$intervals = self::get_intervals( $start_date, $end_date, $interval );
89	$response = array();
90	foreach ( $intervals as $interval ) {
91	$start_date = isset($interval['start_date']) ? $interval['start_date'] : (new \DateTime('monday last week'))->format('Y-m-d H:i:s');
92	$end_date = isset($interval['end_date']) ? $interval['end_date'] : (new \DateTime('sunday last week'))->format('Y-m-d H:i:s');
93	$total_orders = self::get_total_orders( $start_date, $end_date );
94	$total_customers = self::get_total_customers( $start_date, $end_date );
95	$total_sales = self::get_total_sales( $start_date, $end_date );
96	$total_checkout_sales = self::get_total_checkout_sales( $start_date, $end_date );
97	$total_ob_revenue = self::get_total_ob_sales( $start_date, $end_date );
98	$total_leads = self::get_total_leads( $start_date, $end_date );
99	$wc_interval_total = self::get_wc_total_sales( $start_date, $end_date );
100	$store_sales = max( 0, $wc_interval_total - (float) $total_sales );
101	$response['sales']['interval'][] = apply_filters( 'wpfunnels/stat-interval-data', array(
102	'total_orders' => (int) $total_orders,
103	'total_customers' => (int) $total_customers,
104	'total_sales' => self::format_price( $total_checkout_sales ),
105	'total_ob_revenue' => self::format_price( $total_ob_revenue ),
106	'total_funnel_sales' => self::format_price( $total_sales ),
107	'store_sales' => self::format_price( $store_sales ),
108	), $start_date, $end_date );
109
110	$response['lead']['interval'][] = apply_filters( 'wpfunnels/stat-interval-data-leads', array(
111	'total' => (int) $total_leads,
112	), $start_date, $end_date );
113	}
114	$response['status'] = true;
115	return apply_filters( 'wpfunnels/funnels-stats-data', $response );
116	}
117
118
119	/**
120	* Get the top performing funnels.
121	*
122	* This method retrieves the top three performing funnels based on total revenue,
123	* which is calculated as the sum of total sales, upsell sales, downsell sales, and orderbump sales.
124	*
125	* @global wpdb $wpdb WordPress database abstraction object.
126	* @return array An array of top performing funnels, where each funnel is represented as an associative array
127	* containing 'id', 'link', 'title', 'views', 'conversion', 'revenue', and 'conversion_rate'.
128	* @throws Exception If there is an issue with the database query.
129	* @since 3.5.0
130	*/
131	public static function get_top_funnels( $start_date = null, $end_date = null ) {
132	global $wpdb;
133	$table = $wpdb->prefix . 'wpfnl_stats';
134
135	if ( ! $start_date ) {
136	$start_date = '2000-01-01 00:00:00';
137	}
138	if ( ! $end_date ) {
139	$end_date = current_time( 'Y-m-d H:i:s' );
140	}
141
142	$sql = $wpdb->prepare(
143	"SELECT funnel_id,
144	SUM(CASE WHEN status = 'completed' THEN total_sales ELSE 0 END) AS total_revenue,
145	COUNT(CASE WHEN status = 'completed' THEN 1 END) AS order_count
146	FROM {$table}
147	WHERE paid_date >= %s AND paid_date <= %s
148	GROUP BY funnel_id
149	ORDER BY total_revenue DESC
150	LIMIT 3",
151	$start_date,
152	$end_date
153	);
154
155	$top_funnels = $wpdb->get_results( $sql );
156
157	// Store AOV baseline for lift calculation
158	$wc_total = self::get_wc_total_sales( $start_date, $end_date );
159	$wc_orders = self::get_wc_total_orders( $start_date, $end_date );
160	$store_aov = $wc_orders > 0 ? $wc_total / $wc_orders : 0;
161
162	$funnel_data = array();
163	foreach ( $top_funnels as $top_funnel ) {
164	$funnel_id = $top_funnel->funnel_id;
165	if ( 'publish' !== get_post_status( $funnel_id ) ) {
166	continue;
167	}
168	$revenue = (float) $top_funnel->total_revenue;
169	$orders = (int) $top_funnel->order_count;
170	$aov = $orders > 0 ? $revenue / $orders : 0;
171	$aov_lift = $store_aov > 0 ? round( ( ( $aov - $store_aov ) / $store_aov ) * 100, 1 ) : 0;
172
173	$funnel_data[] = array(
174	'id' => $funnel_id,
175	'link' => admin_url( "/admin.php?page=edit_funnel&id={$funnel_id}&step_id=0" ),
176	'title' => get_the_title( $funnel_id ),
177	'views' => 0,
178	'orders' => $orders,
179	'conversion' => 0,
180	'revenue' => self::format_price( $revenue ),
181	'aov' => self::format_price( $aov ),
182	'aov_lift' => $aov_lift,
183	'conversion_rate' => 0,
184	);
185	}
186	return apply_filters( 'wpfunnels/top-performing-funnels-data', $funnel_data, $start_date, $end_date );
187	}
188
189
190	/**
191	* Get total number of orders
192	*
193	* @param $start_date
194	* @param $end_date
195	* @return array\|object\|null
196	*
197	* @since 3.2.0
198	*/
199	public static function get_total_orders( $start_date, $end_date ) {
200	global $wpdb;
201	$table = $wpdb->prefix. 'wpfnl_stats';
202	$sql = "SELECT count(id) FROM $table";
203	$sql = self::include_where_clause($sql);
204	$result = $wpdb->get_var($wpdb->prepare($sql, $start_date, $end_date ));
205	return $result;
206	}
207
208
209	/**
210	* Get total number of customers
211	*
212	* @param $start_date
213	* @param $end_date
214	* @return string\|null
215	*
216	* @since 3.2.0
217	*/
218	public static function get_total_customers( $start_date, $end_date ) {
219	global $wpdb;
220	$table = $wpdb->prefix. 'wpfnl_stats';
221	$sql = "SELECT count(DISTINCT customer_id) as count FROM $table" ;
222	$sql = self::include_where_clause($sql);
223	$result = $wpdb->get_var( $wpdb->prepare($sql, $start_date, $end_date ) );
224	return $result;
225	}
226
227
228	/**
229	* Get total sales
230	*
231	* @param $start_date
232	* @param $end_date
233	* @return mixed
234	*
235	* @since 3.2.0
236	*/
237	public static function get_total_sales( $start_date, $end_date ) {
238	global $wpdb;
239	$table = $wpdb->prefix. 'wpfnl_stats' ;
240	$sql = "SELECT SUM(total_sales) as total_sales FROM $table";
241	$sql = self::include_where_clause($sql);
242	$result = $wpdb->get_var( $wpdb->prepare( $sql, $start_date, $end_date ) );
243	return $result;
244	}
245
246
247	public static function get_total_checkout_sales( $start_date, $end_date ){
248	global $wpdb;
249	$table = $wpdb->prefix. 'wpfnl_stats' ;
250	$sql = "
251	SELECT
252	SUM(total_sales - ( orderbump_sales + upsell_sales + downsell_sales )) AS checkout_sales
253	FROM
254	{$table}
255	";
256	$sql = self::include_where_clause($sql);
257	$result = $wpdb->get_var( $wpdb->prepare( $sql, $start_date, $end_date ) );
258
259	return $result;
260	}
261
262
263	/**
264	* Get total order bump sales
265	*
266	* @param $start_date
267	* @param $end_date
268	* @return mixed
269	*
270	* @since 3.2.0
271	*/
272	public static function get_total_ob_sales( $start_date, $end_date ) {
273	global $wpdb;
274	$table = $wpdb->prefix. 'wpfnl_stats' ;
275	$sql = "SELECT SUM(orderbump_sales) as orderbump_sales FROM $table";
276	$sql = self::include_where_clause($sql);
277	$result = $wpdb->get_var( $wpdb->prepare( $sql, $start_date, $end_date ) );
278	return $result;
279	}
280
281
282	/**
283	* Get total number of leads
284	*
285	* @param $start_date
286	* @param $end_date
287	* @return string\|null
288	*
289	* @since 3.2.0
290	*/
291	public static function get_total_leads( $start_date, $end_date ) {
292	global $wpdb;
293	$table = $wpdb->prefix. 'wpfnl_optin_entries' ;
294	$sql = "SELECT COUNT(id) as total FROM $table";
295	$sql = self::include_where_clause_leads($sql);
296	$result = $wpdb->get_var( $wpdb->prepare( $sql, $start_date, $end_date ) );
297	return $result;
298	}
299
300
301	/**
302	* Include where clause
303	*
304	* @param $sql
305	* @return string
306	*
307	* @since 3.2.0
308	*/
309	public static function include_where_clause( $sql ) {
310	return $sql." WHERE paid_date >= %s AND paid_date <= %s AND status = 'completed' ";
311	}
312
313
314	/**
315	* Include where clause for leads fetching query
316	*
317	* @param $sql
318	* @return string
319	*
320	* @since 3.2.0
321	*/
322	public static function include_where_clause_leads( $sql ) {
323	return $sql." WHERE date_created >= %s AND date_created <= %s ";
324	}
325
326
327	/**
328	* Get interval data
329	*
330	* This method generates an array of date intervals between the given start and end dates,
331	* based on the specified interval type (day, month, quarter, hour).
332	*
333	* @param string $start_date The start date in 'Y-m-d H:i:s' format.
334	* @param string $end_date The end date in 'Y-m-d H:i:s' format.
335	* @param string $interval_type The type of interval ('day', 'month', 'quarter', 'hour').
336	* @return array An array of intervals, where each interval is an associative array
337	* with 'start_date' and 'end_date' keys in 'Y-m-d H:i:s' format.
338	* @throws \Exception If an invalid interval type is provided.
339	*
340	* @since 3.2.0
341	*/
342	public static function get_intervals( $start_date, $end_date, $interval_type ) {
343	$intervals = array();
344
345	// Convert start and end dates to DateTime objects
346	$start = new \DateTime($start_date);
347	$end = new \DateTime($end_date);
348
349	// Create interval based on interval type
350	switch ($interval_type) {
351	case 'day':
352	$interval = new \DateInterval('P1D');
353	break;
354	case 'month':
355	$interval = new \DateInterval('P1M');
356	$end->modify('last day of this month')->setTime(23, 59, 59);
357	break;
358	case 'quarter':
359	$interval = new \DateInterval('P3M');
360	$end->modify('last day of this quarter')->setTime(23, 59, 59);
361	break;
362	case 'hour':
363	$interval = new \DateInterval('PT1H');
364	break;
365	default:
366	throw new \Exception("Invalid interval type: $interval_type");
367	}
368
369	// Iterate over the date range
370	$period = new \DatePeriod($start, $interval, $end);
371	foreach ($period as $dt) {
372	$interval_end = clone $dt;
373	if ($interval_type === 'day') {
374	$interval_end->setTime(23, 59, 59);
375	} elseif ($interval_type === 'month') {
376	$interval_end->modify('last day of this month')->setTime(23, 59, 59);
377	} elseif ($interval_type === 'hour') {
378	$interval_end->setTime($interval_end->format('H'), 59, 59);
379	}
380	$intervals[] = array(
381	'start_date' => $dt->format('Y-m-d H:i:s'),
382	'end_date' => $interval_end->format('Y-m-d H:i:s')
383	);
384	}
385
386	// Handle the last interval for month type
387	if ($interval_type === 'month' && $end > $interval_end) {
388	$last_month_end = new \DateTime($end->format('Y-m-t').' 23:59:59');
389	$intervals[] = array(
390	'start_date' => $interval_end->format('Y-m-d H:i:s'),
391	'end_date' => $last_month_end->format('Y-m-d H:i:s')
392	);
393	}
394
395	return $intervals;
396	}
397
398
399	/**
400	* Get WooCommerce total sales for all orders (not just funnel orders).
401	* Supports both HPOS and legacy post-based order storage.
402	*
403	* @param string $start_date
404	* @param string $end_date
405	* @return float
406	*
407	* @since 3.9.6
408	*/
409	public static function get_wc_total_sales( $start_date, $end_date ) {
410	if ( ! class_exists( 'WooCommerce' ) ) {
411	return 0;
412	}
413	global $wpdb;
414
415	$hpos_enabled = get_option( 'woocommerce_custom_orders_table_enabled' ) === 'yes';
416
417	if ( $hpos_enabled ) {
418	$table = $wpdb->prefix . 'wc_orders';
419	$start_utc = get_gmt_from_date( $start_date );
420	$end_utc = get_gmt_from_date( $end_date );
421	$total = $wpdb->get_var( $wpdb->prepare(
422	"SELECT COALESCE(SUM(total_amount), 0) FROM {$table}
423	WHERE type = 'shop_order'
424	AND status IN ('wc-completed', 'wc-processing')
425	AND date_created_gmt >= %s AND date_created_gmt <= %s",
426	$start_utc,
427	$end_utc
428	) );
429	} else {
430	$total = $wpdb->get_var( $wpdb->prepare(
431	"SELECT COALESCE(SUM(pm.meta_value), 0)
432	FROM {$wpdb->posts} p
433	INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id AND pm.meta_key = '_order_total'
434	WHERE p.post_type = 'shop_order'
435	AND p.post_status IN ('wc-completed', 'wc-processing')
436	AND p.post_date >= %s AND p.post_date <= %s",
437	$start_date,
438	$end_date
439	) );
440	}
441
442	return self::format_price( (float) $total );
443	}
444
445
446	/**
447	* Get the count of all WooCommerce orders (not just funnel orders) for a date range.
448	* Supports both HPOS and legacy post-based order storage.
449	*
450	* @param string $start_date
451	* @param string $end_date
452	* @return int
453	*
454	* @since 3.9.6
455	*/
456	public static function get_wc_total_orders( $start_date, $end_date ) {
457	if ( ! class_exists( 'WooCommerce' ) ) {
458	return 0;
459	}
460	global $wpdb;
461
462	$hpos_enabled = get_option( 'woocommerce_custom_orders_table_enabled' ) === 'yes';
463
464	if ( $hpos_enabled ) {
465	$table = $wpdb->prefix . 'wc_orders';
466	return (int) $wpdb->get_var( $wpdb->prepare(
467	"SELECT COUNT(id) FROM {$table}
468	WHERE type = 'shop_order'
469	AND status IN ('wc-completed', 'wc-processing')
470	AND date_created_gmt >= %s AND date_created_gmt <= %s",
471	$start_date,
472	$end_date
473	) );
474	}
475
476	return (int) $wpdb->get_var( $wpdb->prepare(
477	"SELECT COUNT(ID) FROM {$wpdb->posts}
478	WHERE post_type = 'shop_order'
479	AND post_status IN ('wc-completed', 'wc-processing')
480	AND post_date >= %s AND post_date <= %s",
481	$start_date,
482	$end_date
483	) );
484	}
485
486
487	/**
488	* Get order bump acceptance rate.
489	* = orders with orderbump_sales > 0 / total completed orders × 100
490	*
491	* @param string $start_date
492	* @param string $end_date
493	* @return float
494	*
495	* @since 3.9.6
496	*/
497	public static function get_ob_acceptance_rate( $start_date, $end_date ) {
498	global $wpdb;
499	$table = $wpdb->prefix . 'wpfnl_stats';
500
501	$total = (int) $wpdb->get_var( $wpdb->prepare(
502	"SELECT COUNT(id) FROM {$table}
503	WHERE paid_date >= %s AND paid_date <= %s AND status = 'completed'",
504	$start_date, $end_date
505	) );
506
507	if ( ! $total ) {
508	return 0;
509	}
510
511	$with_ob = (int) $wpdb->get_var( $wpdb->prepare(
512	"SELECT COUNT(id) FROM {$table}
513	WHERE paid_date >= %s AND paid_date <= %s AND status = 'completed' AND orderbump_sales > 0",
514	$start_date, $end_date
515	) );
516
517	return round( ( $with_ob / $total ) * 100, 1 );
518	}
519
520
521	/**
522	* Get upsell acceptance rate.
523	* = orders with upsell_sales > 0 / total completed orders × 100
524	*
525	* @param string $start_date
526	* @param string $end_date
527	* @return float
528	*
529	* @since 3.9.6
530	*/
531	public static function get_upsell_acceptance_rate( $start_date, $end_date ) {
532	global $wpdb;
533	$table = $wpdb->prefix . 'wpfnl_stats';
534
535	$total = (int) $wpdb->get_var( $wpdb->prepare(
536	"SELECT COUNT(id) FROM {$table}
537	WHERE paid_date >= %s AND paid_date <= %s AND status = 'completed'",
538	$start_date, $end_date
539	) );
540
541	if ( ! $total ) {
542	return 0;
543	}
544
545	$with_upsell = (int) $wpdb->get_var( $wpdb->prepare(
546	"SELECT COUNT(id) FROM {$table}
547	WHERE paid_date >= %s AND paid_date <= %s AND status = 'completed' AND upsell_sales > 0",
548	$start_date, $end_date
549	) );
550
551	return round( ( $with_upsell / $total ) * 100, 1 );
552	}
553
554
555	/**
556	* Get downsell recovery rate.
557	* = orders with downsell_sales > 0 / total completed orders × 100
558	*
559	* @param string $start_date
560	* @param string $end_date
561	* @return float
562	*
563	* @since 3.9.6
564	*/
565	public static function get_downsell_recovery_rate( $start_date, $end_date ) {
566	global $wpdb;
567	$table = $wpdb->prefix . 'wpfnl_stats';
568
569	$total = (int) $wpdb->get_var( $wpdb->prepare(
570	"SELECT COUNT(id) FROM {$table}
571	WHERE paid_date >= %s AND paid_date <= %s AND status = 'completed'",
572	$start_date, $end_date
573	) );
574
575	if ( ! $total ) {
576	return 0;
577	}
578
579	$with_downsell = (int) $wpdb->get_var( $wpdb->prepare(
580	"SELECT COUNT(id) FROM {$table}
581	WHERE paid_date >= %s AND paid_date <= %s AND status = 'completed' AND downsell_sales > 0",
582	$start_date, $end_date
583	) );
584
585	return round( ( $with_downsell / $total ) * 100, 1 );
586	}
587
588	}
589