wpfunnels / includes / core / Reporting / ReportGenerator.php

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

625 lines 19.8 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 $bucket ) {
91	// Bucket boundaries, not the whole requested range.
92	$bucket_start = isset($bucket['start_date']) ? $bucket['start_date'] : (new \DateTime('monday last week'))->format('Y-m-d H:i:s');
93	$bucket_end = isset($bucket['end_date']) ? $bucket['end_date'] : (new \DateTime('sunday last week'))->format('Y-m-d H:i:s');
94	$total_orders = self::get_total_orders( $bucket_start, $bucket_end );
95	$total_customers = self::get_total_customers( $bucket_start, $bucket_end );
96	$total_sales = self::get_total_sales( $bucket_start, $bucket_end );
97	$total_checkout_sales = self::get_total_checkout_sales( $bucket_start, $bucket_end );
98	$total_ob_revenue = self::get_total_ob_sales( $bucket_start, $bucket_end );
99	$total_leads = self::get_total_leads( $bucket_start, $bucket_end );
100	$wc_interval_total = self::get_wc_total_sales( $bucket_start, $bucket_end );
101	$store_sales = max( 0, $wc_interval_total - (float) $total_sales );
102	$response['sales']['interval'][] = apply_filters( 'wpfunnels/stat-interval-data', array(
103	// Exposed so consumers can label buckets from the real
104	// boundaries instead of re-deriving them from the range start.
105	'start_date' => $bucket_start,
106	'end_date' => $bucket_end,
107	'total_orders' => (int) $total_orders,
108	'total_customers' => (int) $total_customers,
109	'total_sales' => self::format_price( $total_checkout_sales ),
110	'total_ob_revenue' => self::format_price( $total_ob_revenue ),
111	'total_funnel_sales' => self::format_price( $total_sales ),
112	'store_sales' => self::format_price( $store_sales ),
113	), $bucket_start, $bucket_end );
114
115	$response['lead']['interval'][] = apply_filters( 'wpfunnels/stat-interval-data-leads', array(
116	'start_date' => $bucket_start,
117	'end_date' => $bucket_end,
118	'total' => (int) $total_leads,
119	), $bucket_start, $bucket_end );
120	}
121	$response['status'] = true;
122	return apply_filters( 'wpfunnels/funnels-stats-data', $response );
123	}
124
125
126	/**
127	* Get the top performing funnels.
128	*
129	* This method retrieves the top three performing funnels based on total revenue,
130	* which is calculated as the sum of total sales, upsell sales, downsell sales, and orderbump sales.
131	*
132	* @global wpdb $wpdb WordPress database abstraction object.
133	* @return array An array of top performing funnels, where each funnel is represented as an associative array
134	* containing 'id', 'link', 'title', 'views', 'conversion', 'revenue', and 'conversion_rate'.
135	* @throws Exception If there is an issue with the database query.
136	* @since 3.5.0
137	*/
138	public static function get_top_funnels( $start_date = null, $end_date = null ) {
139	global $wpdb;
140	$table = $wpdb->prefix . 'wpfnl_stats';
141
142	if ( ! $start_date ) {
143	$start_date = '2000-01-01 00:00:00';
144	}
145	if ( ! $end_date ) {
146	$end_date = current_time( 'Y-m-d H:i:s' );
147	}
148
149	// Restricting to published funnels here — rather than skipping them after
150	// the fact — keeps LIMIT 3 from being eaten by trashed or draft funnels.
151	$sql = $wpdb->prepare(
152	"SELECT s.funnel_id,
153	SUM(CASE WHEN s.status = 'completed' THEN s.total_sales ELSE 0 END) AS total_revenue,
154	COUNT(CASE WHEN s.status = 'completed' THEN 1 END) AS order_count
155	FROM {$table} s
156	INNER JOIN {$wpdb->posts} p
157	ON p.ID = s.funnel_id AND p.post_status = 'publish'
158	WHERE s.paid_date >= %s AND s.paid_date <= %s
159	GROUP BY s.funnel_id
160	ORDER BY total_revenue DESC
161	LIMIT 3",
162	$start_date,
163	$end_date
164	);
165
166	$top_funnels = $wpdb->get_results( $sql );
167
168	// Store AOV baseline for lift calculation
169	$wc_total = self::get_wc_total_sales( $start_date, $end_date );
170	$wc_orders = self::get_wc_total_orders( $start_date, $end_date );
171	$store_aov = $wc_orders > 0 ? $wc_total / $wc_orders : 0;
172
173	$funnel_data = array();
174	foreach ( $top_funnels as $top_funnel ) {
175	$funnel_id = $top_funnel->funnel_id;
176	$revenue = (float) $top_funnel->total_revenue;
177	$orders = (int) $top_funnel->order_count;
178	$aov = $orders > 0 ? $revenue / $orders : 0;
179
180	// With no orders there is no average order value to compare, so the
181	// lift is undefined. Reporting it as a number would always read
182	// -100% and look like a real result.
183	$aov_lift = ( $orders > 0 && $store_aov > 0 )
184	? round( ( ( $aov - $store_aov ) / $store_aov ) * 100, 1 )
185	: null;
186
187	$funnel_data[] = array(
188	'id' => $funnel_id,
189	'link' => admin_url( "/admin.php?page=edit_funnel&id={$funnel_id}&step_id=0" ),
190	'title' => get_the_title( $funnel_id ),
191	'views' => 0,
192	'orders' => $orders,
193	'conversion' => 0,
194	'revenue' => self::format_price( $revenue ),
195	'aov' => self::format_price( $aov ),
196	'aov_lift' => $aov_lift,
197	'conversion_rate' => 0,
198	);
199	}
200	return apply_filters( 'wpfunnels/top-performing-funnels-data', $funnel_data, $start_date, $end_date );
201	}
202
203
204	/**
205	* Get total number of orders
206	*
207	* @param $start_date
208	* @param $end_date
209	* @return array\|object\|null
210	*
211	* @since 3.2.0
212	*/
213	public static function get_total_orders( $start_date, $end_date ) {
214	global $wpdb;
215	$table = $wpdb->prefix. 'wpfnl_stats';
216	$sql = "SELECT count(id) FROM $table";
217	$sql = self::include_where_clause($sql);
218	$result = $wpdb->get_var($wpdb->prepare($sql, $start_date, $end_date ));
219	return $result;
220	}
221
222
223	/**
224	* Get total number of customers
225	*
226	* @param $start_date
227	* @param $end_date
228	* @return string\|null
229	*
230	* @since 3.2.0
231	*/
232	public static function get_total_customers( $start_date, $end_date ) {
233	global $wpdb;
234	$table = $wpdb->prefix. 'wpfnl_stats';
235	$sql = "SELECT count(DISTINCT customer_id) as count FROM $table" ;
236	$sql = self::include_where_clause($sql);
237	$result = $wpdb->get_var( $wpdb->prepare($sql, $start_date, $end_date ) );
238	return $result;
239	}
240
241
242	/**
243	* Get total sales
244	*
245	* @param $start_date
246	* @param $end_date
247	* @return mixed
248	*
249	* @since 3.2.0
250	*/
251	public static function get_total_sales( $start_date, $end_date ) {
252	global $wpdb;
253	$table = $wpdb->prefix. 'wpfnl_stats' ;
254	$sql = "SELECT SUM(total_sales) as total_sales FROM $table";
255	$sql = self::include_where_clause($sql);
256	$result = $wpdb->get_var( $wpdb->prepare( $sql, $start_date, $end_date ) );
257	return $result;
258	}
259
260
261	public static function get_total_checkout_sales( $start_date, $end_date ){
262	global $wpdb;
263	$table = $wpdb->prefix. 'wpfnl_stats' ;
264	$sql = "
265	SELECT
266	SUM(total_sales - ( orderbump_sales + upsell_sales + downsell_sales )) AS checkout_sales
267	FROM
268	{$table}
269	";
270	$sql = self::include_where_clause($sql);
271	$result = $wpdb->get_var( $wpdb->prepare( $sql, $start_date, $end_date ) );
272
273	return $result;
274	}
275
276
277	/**
278	* Get total order bump sales
279	*
280	* @param $start_date
281	* @param $end_date
282	* @return mixed
283	*
284	* @since 3.2.0
285	*/
286	public static function get_total_ob_sales( $start_date, $end_date ) {
287	global $wpdb;
288	$table = $wpdb->prefix. 'wpfnl_stats' ;
289	$sql = "SELECT SUM(orderbump_sales) as orderbump_sales FROM $table";
290	$sql = self::include_where_clause($sql);
291	$result = $wpdb->get_var( $wpdb->prepare( $sql, $start_date, $end_date ) );
292	return $result;
293	}
294
295
296	/**
297	* Get total number of leads
298	*
299	* @param $start_date
300	* @param $end_date
301	* @return string\|null
302	*
303	* @since 3.2.0
304	*/
305	public static function get_total_leads( $start_date, $end_date ) {
306	global $wpdb;
307	$table = $wpdb->prefix. 'wpfnl_optin_entries' ;
308	$sql = "SELECT COUNT(id) as total FROM $table";
309	$sql = self::include_where_clause_leads($sql);
310	$result = $wpdb->get_var( $wpdb->prepare( $sql, $start_date, $end_date ) );
311	return $result;
312	}
313
314
315	/**
316	* Include where clause
317	*
318	* @param $sql
319	* @return string
320	*
321	* @since 3.2.0
322	*/
323	public static function include_where_clause( $sql ) {
324	return $sql." WHERE paid_date >= %s AND paid_date <= %s AND status = 'completed' ";
325	}
326
327
328	/**
329	* Include where clause for leads fetching query
330	*
331	* @param $sql
332	* @return string
333	*
334	* @since 3.2.0
335	*/
336	public static function include_where_clause_leads( $sql ) {
337	return $sql." WHERE date_created >= %s AND date_created <= %s ";
338	}
339
340
341	/**
342	* Get interval data
343	*
344	* Splits the given range into buckets aligned to calendar boundaries. The
345	* first bucket starts exactly at $start_date and the last ends exactly at
346	* $end_date, so the buckets are contiguous, cover the whole range and never
347	* reach outside it — even when the range starts or ends mid-unit.
348	*
349	* @param string $start_date The start date in 'Y-m-d H:i:s' format.
350	* @param string $end_date The end date in 'Y-m-d H:i:s' format.
351	* @param string $interval_type One of 'hour', 'day', 'week', 'month', 'quarter', 'year'.
352	* @return array An array of intervals, where each interval is an associative array
353	* with 'start_date' and 'end_date' keys in 'Y-m-d H:i:s' format.
354	* @throws \Exception If an invalid interval type is provided.
355	*
356	* @since 3.2.0
357	*/
358	public static function get_intervals( $start_date, $end_date, $interval_type ) {
359	$valid_types = array( 'hour', 'day', 'week', 'month', 'quarter', 'year' );
360	if ( ! in_array( $interval_type, $valid_types, true ) ) {
361	throw new \Exception( "Invalid interval type: $interval_type" );
362	}
363
364	$start = new \DateTime( $start_date );
365	$end = new \DateTime( $end_date );
366
367	if ( $end < $start ) {
368	return array();
369	}
370
371	$intervals = array();
372	$cursor = clone $start;
373
374	while ( $cursor <= $end ) {
375	$bucket_end = self::get_bucket_end( $cursor, $interval_type );
376
377	// Never report past the requested range.
378	if ( $bucket_end > $end ) {
379	$bucket_end = clone $end;
380	}
381
382	$intervals[] = array(
383	'start_date' => $cursor->format( 'Y-m-d H:i:s' ),
384	'end_date' => $bucket_end->format( 'Y-m-d H:i:s' ),
385	);
386
387	// Resume the instant after this bucket, so buckets never overlap
388	// and never leave a gap.
389	$cursor = ( clone $bucket_end )->modify( '+1 second' );
390	}
391
392	return $intervals;
393	}
394
395
396	/**
397	* End of the calendar unit containing the given moment.
398	*
399	* @param \DateTime $date
400	* @param string $interval_type
401	* @return \DateTime
402	*
403	* @since 3.13.0
404	*/
405	protected static function get_bucket_end( \DateTime $date, $interval_type ) {
406	$end = clone $date;
407
408	switch ( $interval_type ) {
409	case 'hour':
410	$end->setTime( (int) $end->format( 'H' ), 59, 59 );
411	break;
412	case 'day':
413	$end->setTime( 23, 59, 59 );
414	break;
415	case 'week':
416	$end->modify( 'sunday this week' )->setTime( 23, 59, 59 );
417	break;
418	case 'month':
419	$end->modify( 'last day of this month' )->setTime( 23, 59, 59 );
420	break;
421	case 'quarter':
422	$quarter_end_month = ( (int) ceil( ( (int) $end->format( 'n' ) ) / 3 ) ) * 3;
423	$end->setDate( (int) $end->format( 'Y' ), $quarter_end_month, 1 )
424	->modify( 'last day of this month' )->setTime( 23, 59, 59 );
425	break;
426	case 'year':
427	$end->setDate( (int) $end->format( 'Y' ), 12, 31 )->setTime( 23, 59, 59 );
428	break;
429	}
430
431	return $end;
432	}
433
434
435	/**
436	* Get WooCommerce total sales for all orders (not just funnel orders).
437	* Supports both HPOS and legacy post-based order storage.
438	*
439	* @param string $start_date
440	* @param string $end_date
441	* @return float
442	*
443	* @since 3.9.6
444	*/
445	public static function get_wc_total_sales( $start_date, $end_date ) {
446	if ( ! class_exists( 'WooCommerce' ) ) {
447	return 0;
448	}
449	global $wpdb;
450
451	$hpos_enabled = get_option( 'woocommerce_custom_orders_table_enabled' ) === 'yes';
452
453	if ( $hpos_enabled ) {
454	$table = $wpdb->prefix . 'wc_orders';
455	$start_utc = get_gmt_from_date( $start_date );
456	$end_utc = get_gmt_from_date( $end_date );
457	$total = $wpdb->get_var( $wpdb->prepare(
458	"SELECT COALESCE(SUM(total_amount), 0) FROM {$table}
459	WHERE type = 'shop_order'
460	AND status IN ('wc-completed', 'wc-processing')
461	AND date_created_gmt >= %s AND date_created_gmt <= %s",
462	$start_utc,
463	$end_utc
464	) );
465	} else {
466	$total = $wpdb->get_var( $wpdb->prepare(
467	"SELECT COALESCE(SUM(pm.meta_value), 0)
468	FROM {$wpdb->posts} p
469	INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id AND pm.meta_key = '_order_total'
470	WHERE p.post_type = 'shop_order'
471	AND p.post_status IN ('wc-completed', 'wc-processing')
472	AND p.post_date >= %s AND p.post_date <= %s",
473	$start_date,
474	$end_date
475	) );
476	}
477
478	return self::format_price( (float) $total );
479	}
480
481
482	/**
483	* Get the count of all WooCommerce orders (not just funnel orders) for a date range.
484	* Supports both HPOS and legacy post-based order storage.
485	*
486	* @param string $start_date
487	* @param string $end_date
488	* @return int
489	*
490	* @since 3.9.6
491	*/
492	public static function get_wc_total_orders( $start_date, $end_date ) {
493	if ( ! class_exists( 'WooCommerce' ) ) {
494	return 0;
495	}
496	global $wpdb;
497
498	$hpos_enabled = get_option( 'woocommerce_custom_orders_table_enabled' ) === 'yes';
499
500	if ( $hpos_enabled ) {
501	$table = $wpdb->prefix . 'wc_orders';
502	return (int) $wpdb->get_var( $wpdb->prepare(
503	"SELECT COUNT(id) FROM {$table}
504	WHERE type = 'shop_order'
505	AND status IN ('wc-completed', 'wc-processing')
506	AND date_created_gmt >= %s AND date_created_gmt <= %s",
507	$start_date,
508	$end_date
509	) );
510	}
511
512	return (int) $wpdb->get_var( $wpdb->prepare(
513	"SELECT COUNT(ID) FROM {$wpdb->posts}
514	WHERE post_type = 'shop_order'
515	AND post_status IN ('wc-completed', 'wc-processing')
516	AND post_date >= %s AND post_date <= %s",
517	$start_date,
518	$end_date
519	) );
520	}
521
522
523	/**
524	* Get order bump acceptance rate.
525	* = orders with orderbump_sales > 0 / total completed orders × 100
526	*
527	* @param string $start_date
528	* @param string $end_date
529	* @return float
530	*
531	* @since 3.9.6
532	*/
533	public static function get_ob_acceptance_rate( $start_date, $end_date ) {
534	global $wpdb;
535	$table = $wpdb->prefix . 'wpfnl_stats';
536
537	$total = (int) $wpdb->get_var( $wpdb->prepare(
538	"SELECT COUNT(id) FROM {$table}
539	WHERE paid_date >= %s AND paid_date <= %s AND status = 'completed'",
540	$start_date, $end_date
541	) );
542
543	if ( ! $total ) {
544	return 0;
545	}
546
547	$with_ob = (int) $wpdb->get_var( $wpdb->prepare(
548	"SELECT COUNT(id) FROM {$table}
549	WHERE paid_date >= %s AND paid_date <= %s AND status = 'completed' AND orderbump_sales > 0",
550	$start_date, $end_date
551	) );
552
553	return round( ( $with_ob / $total ) * 100, 1 );
554	}
555
556
557	/**
558	* Get upsell acceptance rate.
559	* = orders with upsell_sales > 0 / total completed orders × 100
560	*
561	* @param string $start_date
562	* @param string $end_date
563	* @return float
564	*
565	* @since 3.9.6
566	*/
567	public static function get_upsell_acceptance_rate( $start_date, $end_date ) {
568	global $wpdb;
569	$table = $wpdb->prefix . 'wpfnl_stats';
570
571	$total = (int) $wpdb->get_var( $wpdb->prepare(
572	"SELECT COUNT(id) FROM {$table}
573	WHERE paid_date >= %s AND paid_date <= %s AND status = 'completed'",
574	$start_date, $end_date
575	) );
576
577	if ( ! $total ) {
578	return 0;
579	}
580
581	$with_upsell = (int) $wpdb->get_var( $wpdb->prepare(
582	"SELECT COUNT(id) FROM {$table}
583	WHERE paid_date >= %s AND paid_date <= %s AND status = 'completed' AND upsell_sales > 0",
584	$start_date, $end_date
585	) );
586
587	return round( ( $with_upsell / $total ) * 100, 1 );
588	}
589
590
591	/**
592	* Get downsell recovery rate.
593	* = orders with downsell_sales > 0 / total completed orders × 100
594	*
595	* @param string $start_date
596	* @param string $end_date
597	* @return float
598	*
599	* @since 3.9.6
600	*/
601	public static function get_downsell_recovery_rate( $start_date, $end_date ) {
602	global $wpdb;
603	$table = $wpdb->prefix . 'wpfnl_stats';
604
605	$total = (int) $wpdb->get_var( $wpdb->prepare(
606	"SELECT COUNT(id) FROM {$table}
607	WHERE paid_date >= %s AND paid_date <= %s AND status = 'completed'",
608	$start_date, $end_date
609	) );
610
611	if ( ! $total ) {
612	return 0;
613	}
614
615	$with_downsell = (int) $wpdb->get_var( $wpdb->prepare(
616	"SELECT COUNT(id) FROM {$table}
617	WHERE paid_date >= %s AND paid_date <= %s AND status = 'completed' AND downsell_sales > 0",
618	$start_date, $end_date
619	) );
620
621	return round( ( $with_downsell / $total ) * 100, 1 );
622	}
623
624	}
625