← All changes
|
includes/core/rest-api/Controllers/class-funnel-controller.php
+202
-51
3.12.0
→
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. |
| @@ -749,8 +822,39 @@ | ||
| 749 | 822 | * @return array The prepared response data. |
| 750 | 823 | * |
| 751 | 824 | * @since 2.7.17 |
| 752 | 825 | */ |
| 826 | + | |
| 827 | + /** | |
| 828 | + * Restore node_identifier precision from the html field. | |
| 829 | + * | |
| 830 | + * PHP json_encode truncates floats, so node_identifier stored as a number loses digits. | |
| 831 | + * The html field is a string (e.g. "addstep311.7949003047232") and preserves full precision. | |
| 832 | + * This method extracts node_identifier from html so the API response always matches what JS stored. | |
| 833 | + * | |
| 834 | + * @param array $funnel_data | |
| 835 | + * @return array | |
| 836 | + */ | |
| 837 | + private function fix_node_identifier_precision($funnel_data) | |
| 838 | + { | |
| 839 | + if (!isset($funnel_data['drawflow']['Home']['data']) || !is_array($funnel_data['drawflow']['Home']['data'])) { | |
| 840 | + return $funnel_data; | |
| 841 | + } | |
| 842 | + foreach ($funnel_data['drawflow']['Home']['data'] as &$node) { | |
| 843 | + if (!isset($node['html'], $node['data']['node_identifier'])) { | |
| 844 | + continue; | |
| 845 | + } | |
| 846 | + $html = $node['html']; | |
| 847 | + if (strpos($html, 'addstep') === 0) { | |
| 848 | + $node['data']['node_identifier'] = substr($html, 7); | |
| 849 | + } elseif (strpos($html, 'conditional') === 0) { | |
| 850 | + $node['data']['node_identifier'] = substr($html, 11); | |
| 851 | + } | |
| 852 | + } | |
| 853 | + unset($node); | |
| 854 | + return $funnel_data; | |
| 855 | + } | |
| 856 | + | |
| 753 | 857 | private function prepare_funnel_data_response($funnel_id) |
| 754 | 858 | { |
| 755 | 859 | $response = [ |
| 756 | 860 | 'status' => 'error', |
| @@ -810,8 +914,9 @@ | ||
| 810 | 914 | 'pos_y' => 143 |
| 811 | 915 | ]; |
| 812 | 916 | |
| 813 | 917 | $funnel_data = Wpfnl_functions::remove_disconnected_addstep_node($funnel_data, $funnel_id); |
| 918 | + $funnel_data = $this->fix_node_identifier_precision($funnel_data); | |
| 814 | 919 | |
| 815 | 920 | if ($funnel_data) { |
| 816 | 921 | $response = [ |
| 817 | 922 | 'status' => 'success', |
| @@ -863,9 +968,12 @@ | ||
| 863 | 968 | |
| 864 | 969 | |
| 865 | 970 | $response['first_node_id'] = $this->get_first_step_node($first_step_id, $response['funnel_data']); |
| 866 | 971 | $response['conditional_data'] = $this->get_conditional_step($response['funnel_data']); |
| 867 | - $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(). | |
| 868 | 976 | return $response; |
| 869 | 977 | } |
| 870 | 978 | |
| 871 | 979 | |
| @@ -1049,14 +1157,18 @@ | ||
| 1049 | 1157 | { |
| 1050 | 1158 | $is_order_bump = false; |
| 1051 | 1159 | $ob_steps = []; |
| 1052 | 1160 | $formatted_steps_order = []; |
| 1161 | + | |
| 1053 | 1162 | foreach ($steps_order as $step) { |
| 1054 | 1163 | $step_id = isset($step['id']) ? $step['id'] : null; |
| 1055 | 1164 | $_temp_step = $step; |
| 1056 | - $analytics = self::get_step_analytics($step_id); | |
| 1057 | - $_temp_step['visit'] = $analytics['visit']; | |
| 1058 | - $_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; | |
| 1059 | 1171 | $_temp_step['name'] = get_the_title($step_id); |
| 1060 | 1172 | $_step_type = get_post_meta($step_id, '_step_type', true); |
| 1061 | 1173 | $should_assign_product = in_array($_step_type, ['checkout', 'upsell', 'downsell']) && !get_post_meta($step_id, '_wpfnl_' . $_step_type . '_products', true); |
| 1062 | 1174 | |
| @@ -1087,28 +1199,39 @@ | ||
| 1087 | 1199 | ]; |
| 1088 | 1200 | } |
| 1089 | 1201 | |
| 1090 | 1202 | /** |
| 1091 | - * Get visit and conversion analytics data for a step. | |
| 1203 | + * Get visit and conversion analytics data for a set of steps in one batch. | |
| 1092 | 1204 | * |
| 1093 | - * 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. | |
| 1094 | 1212 | * |
| 1095 | - * @param int $step_id The step ID. | |
| 1096 | - * @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]. | |
| 1097 | 1215 | * |
| 1098 | - * @since 3.5.0 | |
| 1216 | + * @since 3.12.12 | |
| 1099 | 1217 | */ |
| 1100 | - private static function get_step_analytics($step_id) | |
| 1218 | + private static function get_steps_analytics_map($step_ids) | |
| 1101 | 1219 | { |
| 1102 | - $total = array( | |
| 1103 | - 'visit' => 0, | |
| 1104 | - 'conversion' => 0, | |
| 1105 | - ); | |
| 1220 | + $step_ids = array_values(array_unique(array_filter($step_ids))); | |
| 1106 | 1221 | |
| 1107 | - if (!$step_id) { | |
| 1108 | - return $total; | |
| 1222 | + $map = []; | |
| 1223 | + foreach ($step_ids as $step_id) { | |
| 1224 | + $map[$step_id] = array( | |
| 1225 | + 'visit' => 0, | |
| 1226 | + 'conversion' => 0, | |
| 1227 | + ); | |
| 1109 | 1228 | } |
| 1110 | 1229 | |
| 1230 | + if (empty($step_ids)) { | |
| 1231 | + return $map; | |
| 1232 | + } | |
| 1233 | + | |
| 1111 | 1234 | global $wpdb; |
| 1112 | 1235 | $analytics_table = $wpdb->prefix . 'wpfnl_analytics'; |
| 1113 | 1236 | $analytics_meta_table = $wpdb->prefix . 'wpfnl_analytics_meta'; |
| 1114 | 1237 | |
| @@ -1114,55 +1237,70 @@ | ||
| 1114 | 1237 | |
| 1115 | 1238 | // Check if the analytics table exists before querying. |
| 1116 | 1239 | $table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $analytics_table)); |
| 1117 | 1240 | if (!$table_exists) { |
| 1118 | - return $total; | |
| 1241 | + return $map; | |
| 1119 | 1242 | } |
| 1120 | 1243 | |
| 1121 | - // Get total visits. | |
| 1122 | - $cache_key_visits = 'wpfnl_pro_visits_' . $step_id; | |
| 1123 | - $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'); | |
| 1124 | 1246 | |
| 1125 | - if (false === $visits_data) { | |
| 1126 | - $query = $wpdb->prepare( | |
| 1127 | - "SELECT wpft1.step_id, COUNT( DISTINCT( wpft1.id ) ) AS total_visits | |
| 1128 | - FROM {$analytics_table} as wpft1 | |
| 1129 | - WHERE wpft1.step_id = %s | |
| 1130 | - ORDER BY NULL", | |
| 1131 | - $step_id | |
| 1132 | - ); // phpcs:ignore | |
| 1133 | - | |
| 1134 | - $visits_data = $wpdb->get_row($query); // phpcs:ignore | |
| 1135 | - wp_cache_set($cache_key_visits, $visits_data, 'wpfnl_pro', 3600); | |
| 1247 | + if (false !== $cached_map) { | |
| 1248 | + return $cached_map; | |
| 1136 | 1249 | } |
| 1137 | 1250 | |
| 1138 | - // Get conversions. | |
| 1139 | - $cache_key_conversion = 'wpfnl_pro_conversion_' . $step_id; | |
| 1140 | - $conversion_data = wp_cache_get($cache_key_conversion, 'wpfnl_pro'); | |
| 1251 | + $placeholders = implode(', ', array_fill(0, count($step_ids), '%s')); | |
| 1141 | 1252 | |
| 1142 | - if (false === $conversion_data) { | |
| 1143 | - $query = $wpdb->prepare( | |
| 1144 | - "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 | |
| 1145 | - FROM {$analytics_table} as wpft1 | |
| 1146 | - INNER JOIN {$analytics_meta_table} as wpft2 ON wpft1.id = wpft2.analytics_id | |
| 1147 | - WHERE wpft1.step_id = %s | |
| 1148 | - ORDER BY NULL", | |
| 1149 | - $step_id | |
| 1150 | - ); // 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 | |
| 1151 | 1263 | |
| 1152 | - $conversion_data = $wpdb->get_row($query); // phpcs:ignore | |
| 1153 | - 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 | + } | |
| 1154 | 1268 | } |
| 1155 | 1269 | |
| 1156 | - if ($visits_data) { | |
| 1157 | - $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 | + } | |
| 1158 | 1298 | } |
| 1159 | 1299 | |
| 1160 | - if ($conversion_data) { | |
| 1161 | - $total['conversion'] = $conversion_data->conversions; | |
| 1162 | - } | |
| 1300 | + wp_cache_set($cache_key, $map, 'wpfnl_pro', 3600); | |
| 1163 | 1301 | |
| 1164 | - return $total; | |
| 1302 | + return $map; | |
| 1165 | 1303 | } |
| 1166 | 1304 | |
| 1167 | 1305 | /** |
| 1168 | 1306 | * Get funnel-level analytics data (total orders, revenue, AOV, orderbump revenue). |
| @@ -1330,8 +1468,21 @@ | ||
| 1330 | 1468 | foreach ($steps as $key => $step) { |
| 1331 | 1469 | if ($step) { |
| 1332 | 1470 | $node_data = $step['data']; |
| 1333 | 1471 | if (isset($node_data["step_name"])) unset($node_data["step_name"]); |
| 1472 | + | |
| 1473 | + // Preserve node_identifier at full precision by extracting it from the html | |
| 1474 | + // field (a string) rather than using the float value decoded by json_decode, | |
| 1475 | + // which PHP truncates before we can read it. | |
| 1476 | + if (isset($step['html'], $node_data['node_identifier'])) { | |
| 1477 | + $html = $step['html']; | |
| 1478 | + if (strpos($html, 'addstep') === 0) { | |
| 1479 | + $node_data['node_identifier'] = substr($html, 7); | |
| 1480 | + } elseif (strpos($html, 'conditional') === 0) { | |
| 1481 | + $node_data['node_identifier'] = substr($html, 11); | |
| 1482 | + } | |
| 1483 | + } | |
| 1484 | + | |
| 1334 | 1485 | $step['data'] = $node_data; |
| 1335 | 1486 | $_steps[$key] = $step; |
| 1336 | 1487 | |
| 1337 | 1488 | } |