← All changes | includes/core/rest-api/Controllers/class-funnel-controller.php +157 -51 3.12.8 → 3.13.3 View file →

		@@ -141,8 +141,22 @@
141	141	],
142	142	],
143	143	]);
144	144
	145	+ register_rest_route($this->namespace, '/' . $this->rest_base . '/getFunnelStats/', [
	146	+ [
	147	+ 'methods' => \WP_REST_Server::READABLE,
	148	+ 'callback' => [
	149	+ $this,
	150	+ 'get_funnel_stats'
	151	+ ],
	152	+ 'permission_callback' => [
	153	+ $this,
	154	+ 'update_items_permissions_check'
	155	+ ],
	156	+ ],
	157	+ ]);
	158	+
145	159	register_rest_route($this->namespace, '/' . $this->rest_base . '/getStepType/', [
146	160	[
147	161	'methods' => \WP_REST_Server::READABLE,
148	162	'callback' => [
		@@ -741,8 +755,67 @@
741	755	}
742	756
743	757
744	758	/**
	759	+ * Retrieves visit/conversion figures for a funnel's steps plus funnel totals.
	760	+ *
	761	+ * Deliberately kept out of get_funnel_data() so that opening a funnel on the
	762	+ * canvas never blocks on analytics. On sites with large amounts of tracked
	763	+ * traffic these aggregates can be slow, so the canvas renders its structure
	764	+ * first and fills the numbers in once this responds.
	765	+ *
	766	+ * @param mixed $request The request data.
	767	+ * @return \WP_REST_Response
	768	+ *
	769	+ * @since 3.12.12
	770	+ */
	771	+ public function get_funnel_stats($request)
	772	+ {
	773	+ $funnel_id = isset($request['funnel_id']) ? absint($request['funnel_id']) : 0;
	774	+
	775	+ $response = [
	776	+ 'success' => false,
	777	+ 'steps' => (object) [],
	778	+ 'funnel_analytics' => [
	779	+ 'total_orders' => 0,
	780	+ 'total_revenue' => 0,
	781	+ 'aov' => 0,
	782	+ ],
	783	+ ];
	784	+
	785	+ if (!$funnel_id) {
	786	+ return $this->prepare_item_for_response($response, $request);
	787	+ }
	788	+
	789	+ $cache_key = 'wpfnl_funnel_stats_' . $funnel_id;
	790	+ $cached = get_transient($cache_key);
	791	+
	792	+ if (false !== $cached) {
	793	+ return $this->prepare_item_for_response($cached, $request);
	794	+ }
	795	+
	796	+ $steps = get_post_meta($funnel_id, '_steps', true);
	797	+ $steps = is_array($steps) ? $steps : [];
	798	+ $step_ids = array_map(
	799	+ function ($step) {
	800	+ return isset($step['id']) ? $step['id'] : null;
	801	+ },
	802	+ $steps
	803	+ );
	804	+
	805	+ $response['success'] = true;
	806	+ $response['steps'] = self::get_steps_analytics_map($step_ids);
	807	+ $response['funnel_analytics'] = self::get_funnel_analytics($funnel_id);
	808	+
	809	+ // Cached so repeated canvas opens don't recompute the aggregates. Kept
	810	+ // short enough that the numbers still track recent activity.
	811	+ set_transient($cache_key, $response, 10 * MINUTE_IN_SECONDS);
	812	+
	813	+ return $this->prepare_item_for_response($response, $request);
	814	+ }
	815	+
	816	+
	817	+ /**
745	818	* Prepares the response data for the funnel data based on the provided parameters.
746	819	*
747	820	* @param int $funnel_id The ID of the funnel.
748	821	* @param mixed $request The request data.
		@@ -895,9 +968,12 @@
895	968
896	969
897	970	$response['first_node_id'] = $this->get_first_step_node($first_step_id, $response['funnel_data']);
898	971	$response['conditional_data'] = $this->get_conditional_step($response['funnel_data']);
899		- $response['funnel_analytics'] = self::get_funnel_analytics($funnel_id);
	972	+
	973	+ // Visit/conversion figures and funnel totals are intentionally absent
	974	+ // here — the canvas fetches them separately via getFunnelStats so that
	975	+ // opening a funnel never waits on analytics. See get_funnel_stats().
900	976	return $response;
901	977	}
902	978
903	979
		@@ -1081,14 +1157,18 @@
1081	1157	{
1082	1158	$is_order_bump = false;
1083	1159	$ob_steps = [];
1084	1160	$formatted_steps_order = [];
	1161	+
1085	1162	foreach ($steps_order as $step) {
1086	1163	$step_id = isset($step['id']) ? $step['id'] : null;
1087	1164	$_temp_step = $step;
1088		- $analytics = self::get_step_analytics($step_id);
1089		- $_temp_step['visit'] = $analytics['visit'];
1090		- $_temp_step['conversion'] = $analytics['conversion'];
	1165	+
	1166	+ // Null rather than 0 so the canvas can tell "not loaded yet" apart
	1167	+ // from a genuine zero and show a placeholder until getFunnelStats
	1168	+ // responds.
	1169	+ $_temp_step['visit'] = null;
	1170	+ $_temp_step['conversion'] = null;
1091	1171	$_temp_step['name'] = get_the_title($step_id);
1092	1172	$_step_type = get_post_meta($step_id, '_step_type', true);
1093	1173	$should_assign_product = in_array($_step_type, ['checkout', 'upsell', 'downsell']) && !get_post_meta($step_id, '_wpfnl_' . $_step_type . '_products', true);
1094	1174
		@@ -1119,28 +1199,39 @@
1119	1199	];
1120	1200	}
1121	1201
1122	1202	/**
1123		- * Get visit and conversion analytics data for a step.
	1203	+ * Get visit and conversion analytics data for a set of steps in one batch.
1124	1204	*
1125		- * Queries the analytics tables to get total visits and conversions for a given step.
	1205	+ * Replaces N per-step queries (previously run once per step in
	1206	+ * get_steps_order_data()) with two GROUP BY queries covering the whole
	1207	+ * funnel, avoiding an N+1 query pattern that caused the funnel canvas to
	1208	+ * time out on funnels with several steps. Count semantics are unchanged
	1209	+ * from the previous per-step queries — grouping by step_id and running
	1210	+ * the same WHERE/JOIN conditions together is equivalent to running them
	1211	+ * individually per step.
1126	1212	*
1127		- * @param int $step_id The step ID.
1128		- * @return array Array with 'visit' and 'conversion' counts.
	1213	+ * @param array $step_ids The step IDs to fetch analytics for.
	1214	+ * @return array Map of step_id => ['visit' => int, 'conversion' => int].
1129	1215	*
1130		- * @since 3.5.0
	1216	+ * @since 3.12.12
1131	1217	*/
1132		- private static function get_step_analytics($step_id)
	1218	+ private static function get_steps_analytics_map($step_ids)
1133	1219	{
1134		- $total = array(
1135		- 'visit' => 0,
1136		- 'conversion' => 0,
1137		- );
	1220	+ $step_ids = array_values(array_unique(array_filter($step_ids)));
1138	1221
1139		- if (!$step_id) {
1140		- return $total;
	1222	+ $map = [];
	1223	+ foreach ($step_ids as $step_id) {
	1224	+ $map[$step_id] = array(
	1225	+ 'visit' => 0,
	1226	+ 'conversion' => 0,
	1227	+ );
1141	1228	}
1142	1229
	1230	+ if (empty($step_ids)) {
	1231	+ return $map;
	1232	+ }
	1233	+
1143	1234	global $wpdb;
1144	1235	$analytics_table = $wpdb->prefix . 'wpfnl_analytics';
1145	1236	$analytics_meta_table = $wpdb->prefix . 'wpfnl_analytics_meta';
1146	1237
		@@ -1146,55 +1237,70 @@
1146	1237
1147	1238	// Check if the analytics table exists before querying.
1148	1239	$table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $analytics_table));
1149	1240	if (!$table_exists) {
1150		- return $total;
	1241	+ return $map;
1151	1242	}
1152	1243
1153		- // Get total visits.
1154		- $cache_key_visits = 'wpfnl_pro_visits_' . $step_id;
1155		- $visits_data = wp_cache_get($cache_key_visits, 'wpfnl_pro');
	1244	+ $cache_key = 'wpfnl_pro_steps_analytics_' . md5(implode(',', $step_ids));
	1245	+ $cached_map = wp_cache_get($cache_key, 'wpfnl_pro');
1156	1246
1157		- if (false === $visits_data) {
1158		- $query = $wpdb->prepare(
1159		- "SELECT wpft1.step_id, COUNT( DISTINCT( wpft1.id ) ) AS total_visits
1160		- FROM {$analytics_table} as wpft1
1161		- WHERE wpft1.step_id = %s
1162		- ORDER BY NULL",
1163		- $step_id
1164		- ); // phpcs:ignore
1165		-
1166		- $visits_data = $wpdb->get_row($query); // phpcs:ignore
1167		- wp_cache_set($cache_key_visits, $visits_data, 'wpfnl_pro', 3600);
	1247	+ if (false !== $cached_map) {
	1248	+ return $cached_map;
1168	1249	}
1169	1250
1170		- // Get conversions.
1171		- $cache_key_conversion = 'wpfnl_pro_conversion_' . $step_id;
1172		- $conversion_data = wp_cache_get($cache_key_conversion, 'wpfnl_pro');
	1251	+ $placeholders = implode(', ', array_fill(0, count($step_ids), '%s'));
1173	1252
1174		- if (false === $conversion_data) {
1175		- $query = $wpdb->prepare(
1176		- "SELECT wpft1.step_id, COUNT( CASE WHEN wpft2.meta_key = 'conversion' AND wpft2.meta_value = 'yes' THEN wpft1.step_id ELSE NULL END ) AS conversions
1177		- FROM {$analytics_table} as wpft1
1178		- INNER JOIN {$analytics_meta_table} as wpft2 ON wpft1.id = wpft2.analytics_id
1179		- WHERE wpft1.step_id = %s
1180		- ORDER BY NULL",
1181		- $step_id
1182		- ); // phpcs:ignore
	1253	+ // Get total visits, grouped per step.
	1254	+ $visits_query = $wpdb->prepare(
	1255	+ "SELECT wpft1.step_id, COUNT( DISTINCT( wpft1.id ) ) AS total_visits
	1256	+ FROM {$analytics_table} as wpft1
	1257	+ WHERE wpft1.step_id IN ({$placeholders})
	1258	+ GROUP BY wpft1.step_id
	1259	+ ORDER BY NULL",
	1260	+ $step_ids
	1261	+ ); // phpcs:ignore
	1262	+ $visits_rows = $wpdb->get_results($visits_query); // phpcs:ignore
1183	1263
1184		- $conversion_data = $wpdb->get_row($query); // phpcs:ignore
1185		- wp_cache_set($cache_key_conversion, $conversion_data, 'wpfnl_pro', 3600);
	1264	+ foreach ($visits_rows as $row) {
	1265	+ if (isset($map[$row->step_id])) {
	1266	+ $map[$row->step_id]['visit'] = $row->total_visits;
	1267	+ }
1186	1268	}
1187	1269
1188		- if ($visits_data) {
1189		- $total['visit'] = $visits_data->total_visits;
	1270	+ // Get conversions, grouped per step.
	1271	+ //
	1272	+ // Read the meta table directly on its denormalised step_id rather than
	1273	+ // joining back through the analytics table. The join form had to walk
	1274	+ // every visit for the funnel (tens of thousands of rows) and look up
	1275	+ // each one's meta rows, which dominated the whole request on sites
	1276	+ // with real traffic volume. Filtering on step_id touches only the
	1277	+ // conversion rows themselves — a few thousand instead of ~150k.
	1278	+ //
	1279	+ // Counting meta rows keeps the original numbers intact, including the
	1280	+ // pre-existing behaviour where a visit carrying two 'conversion'='yes'
	1281	+ // meta rows counts twice.
	1282	+ $conversion_query = $wpdb->prepare(
	1283	+ "SELECT step_id, COUNT(*) AS conversions
	1284	+ FROM {$analytics_meta_table}
	1285	+ WHERE step_id IN ({$placeholders})
	1286	+ AND meta_key = 'conversion'
	1287	+ AND meta_value = 'yes'
	1288	+ GROUP BY step_id
	1289	+ ORDER BY NULL",
	1290	+ $step_ids
	1291	+ ); // phpcs:ignore
	1292	+ $conversion_rows = $wpdb->get_results($conversion_query); // phpcs:ignore
	1293	+
	1294	+ foreach ($conversion_rows as $row) {
	1295	+ if (isset($map[$row->step_id])) {
	1296	+ $map[$row->step_id]['conversion'] = $row->conversions;
	1297	+ }
1190	1298	}
1191	1299
1192		- if ($conversion_data) {
1193		- $total['conversion'] = $conversion_data->conversions;
1194		- }
	1300	+ wp_cache_set($cache_key, $map, 'wpfnl_pro', 3600);
1195	1301
1196		- return $total;
	1302	+ return $map;
1197	1303	}
1198	1304
1199	1305	/**
1200	1306	* Get funnel-level analytics data (total orders, revenue, AOV, orderbump revenue).