| @@ -91,9 +91,9 @@ | ||
| 91 | 91 | ], |
| 92 | 92 | ], |
| 93 | 93 | ], ['date_basis' => ['type' => 'string'], 'mode' => ['type' => 'string'], 'page' => ['type' => 'object'], 'truncated' => ['type' => 'boolean']]); |
| 94 | 94 | |
| 95 | - $subStatuses = ContextTools::ENUMS['subscription_statuses']; | |
| 95 | + $subStatuses = ContextTools::enums()['subscription_statuses']; | |
| 96 | 96 | |
| 97 | 97 | $defs = [ |
| 98 | 98 | 'fluent-cart/get-sales-report' => [ |
| 99 | 99 | 'label' => __('Get Sales Report', 'fluent-cart'), |
| @@ -238,9 +238,9 @@ | ||
| 238 | 238 | ], |
| 239 | 239 | |
| 240 | 240 | 'fluent-cart/query-orders' => [ |
| 241 | 241 | 'label' => __('Query Orders (flexible aggregate)', 'fluent-cart'), |
| 242 | - 'description' => __('Flexible order analytics: pick metrics, group by dimensions with filters, when a fixed report does not fit — e.g. revenue by payment_status, orders by month, or revenue by order_type (one-time payment vs new subscription vs renewal). product_id or variation_id limits to orders containing that product. One currency; window filters on created_at, echoed as meta.date_basis.', 'fluent-cart'), | |
| 242 | + 'description' => __('Flexible order analytics: pick metrics, group by dimensions with filters, when a fixed report does not fit — e.g. revenue by payment_status, orders by month, revenue by order_type (one-time payment vs new subscription vs renewal), or revenue by country. product_id or variation_id limits to orders containing that product. This is the tool for revenue BY GEOGRAPHY: group by country/state, which read each order\'s own billing address, falling back to the buyer\'s primary billing address. One currency; window filters on created_at, echoed as meta.date_basis.', 'fluent-cart'), | |
| 243 | 243 | 'input_schema' => [ |
| 244 | 244 | 'type' => 'object', |
| 245 | 245 | 'properties' => [ |
| 246 | 246 | 'metrics' => [ |
| @@ -249,11 +249,12 @@ | ||
| 249 | 249 | 'items' => ['type' => 'string', 'enum' => ['order_count', 'gross_revenue', 'paid_revenue', 'refunded_amount', 'aov', 'unique_customers']], |
| 250 | 250 | ], |
| 251 | 251 | 'dimensions' => [ |
| 252 | 252 | 'type' => 'array', |
| 253 | - 'description' => 'Group by these. Empty means a single total row. order_type splits sales by payment (one-time purchase), subscription (first subscription order) and renewal (recurring charge); combine with a time dimension for e.g. order_type x month.', | |
| 254 | - 'items' => ['type' => 'string', 'enum' => ['day', 'week', 'month', 'status', 'payment_status', 'order_type']], | |
| 253 | + 'description' => 'Group by these. Empty means a single total row. order_type splits sales by payment (one-time purchase), subscription (first subscription order) and renewal (recurring charge); combine with a time dimension for e.g. order_type x month. country/state come from each order\'s own billing address, falling back to the buyer\'s primary billing address when the order has no address row of its own (common for digital goods) — this is the right basis for revenue by geography. Orders neither source can place land in an explicit "unknown" bucket so the rows still sum to total revenue; see meta.geo_source.', | |
| 254 | + 'items' => ['type' => 'string', 'enum' => ['day', 'week', 'month', 'status', 'payment_status', 'order_type', 'country', 'state']], | |
| 255 | 255 | ], |
| 256 | + 'country' => ['type' => 'string', 'description' => 'ISO-2 code. Limit to orders billed to this country, resolved the same way as the country dimension (order address, then the buyer\'s primary billing address).'], | |
| 256 | 257 | 'product_id' => ['type' => 'integer', 'description' => 'Limit to orders CONTAINING this product. Order-level metrics (revenue, count) reflect the whole order, not just this product\'s lines — for per-product line revenue use query-products.'], |
| 257 | 258 | 'variation_id' => ['type' => 'integer', 'description' => 'Limit to orders containing this variation.'], |
| 258 | 259 | 'range' => $rangeProp, |
| 259 | 260 | 'start_date' => ['type' => 'string'], |
| @@ -299,16 +300,16 @@ | ||
| 299 | 300 | ], |
| 300 | 301 | |
| 301 | 302 | 'fluent-cart/query-customers' => [ |
| 302 | 303 | 'label' => __('Query Customers (flexible aggregate)', 'fluent-cart'), |
| 303 | - 'description' => __('Flexible customer analytics: pick metrics and group by country, state, status, or first/last purchase month, with optional filters. LTV is in store currency. Returns up to 200 rows.', 'fluent-cart'), | |
| 304 | + 'description' => __('Flexible customer analytics: pick metrics and group by country, state, status, or first/last purchase month, with optional filters. LTV is in store currency. Returns up to 200 rows. country/state resolve from the customer\'s primary billing address, falling back to their profile location, then "unknown" — this is where a customer is REGISTERED, captured at their first purchase and not refreshed since. To attribute revenue to where each order was actually billed, group query-orders by country instead.', 'fluent-cart'), | |
| 304 | 305 | 'input_schema' => [ |
| 305 | 306 | 'type' => 'object', |
| 306 | 307 | 'properties' => [ |
| 307 | 308 | 'metrics' => ['type' => 'array', 'description' => 'Defaults to customer_count and total_ltv.', 'items' => ['type' => 'string', 'enum' => ['customer_count', 'total_ltv', 'avg_ltv', 'avg_purchase_count', 'repeat_customers']]], |
| 308 | 309 | 'dimensions' => ['type' => 'array', 'items' => ['type' => 'string', 'enum' => ['country', 'state', 'status', 'first_purchase_month', 'last_purchase_month']]], |
| 309 | - 'country' => ['type' => 'string'], | |
| 310 | - 'status' => ['type' => 'string', 'enum' => ['active', 'archived']], | |
| 310 | + 'country' => ['type' => 'string', 'description' => 'ISO-2 code, matched against the customer profile country (same caveat as the country dimension).'], | |
| 311 | + 'status' => ['type' => 'string', 'enum' => ['active', 'archived'], 'description' => 'Not every customer has one set — customers with no status appear under the status dimension as "unknown" and are excluded by this filter. Omit it to count all customers.'], | |
| 311 | 312 | 'min_ltv' => ['type' => 'number', 'description' => 'Minimum LTV in store currency.'], |
| 312 | 313 | 'min_purchase_count' => ['type' => 'integer'], |
| 313 | 314 | ], |
| 314 | 315 | ], |
| @@ -839,9 +840,9 @@ | ||
| 839 | 840 | $currency = self::currency($params); |
| 840 | 841 | $range = self::resolveRange($params); |
| 841 | 842 | $mode = self::orderMode($params); |
| 842 | 843 | $metrics = self::pickList($params, 'metrics', ['order_count', 'gross_revenue', 'paid_revenue', 'refunded_amount', 'aov', 'unique_customers'], ['order_count', 'gross_revenue']); |
| 843 | - $dimensions = self::pickList($params, 'dimensions', ['day', 'week', 'month', 'status', 'payment_status', 'order_type'], []); | |
| 844 | + $dimensions = self::pickList($params, 'dimensions', ['day', 'week', 'month', 'status', 'payment_status', 'order_type', 'country', 'state'], []); | |
| 844 | 845 | |
| 845 | 846 | $query = Order::query() |
| 846 | 847 | ->whereIn('payment_status', self::PAID) |
| 847 | 848 | ->where('currency', $currency) |
| @@ -853,13 +854,109 @@ | ||
| 853 | 854 | // whereHas keeps the aggregate order-level (metrics still reflect the whole |
| 854 | 855 | // order); it never fans out rows the way a raw join would. |
| 855 | 856 | self::applyOrderItemFilter($query, $params); |
| 856 | 857 | |
| 858 | + // Geography comes from the order's OWN billing address first, so each | |
| 859 | + // order's revenue is attributed to where that order was actually billed — | |
| 860 | + // not to wherever the customer is registered now. Joined only when asked | |
| 861 | + // for, so every other dimension keeps its current cost. | |
| 862 | + // | |
| 863 | + // Grouped subqueries, not raw joins: neither fct_order_addresses nor | |
| 864 | + // fct_customer_addresses has a UNIQUE constraint on its (parent, type) | |
| 865 | + // pair — fct_order_addresses actually holds 208 duplicate billing groups on | |
| 866 | + // the reference store — and a duplicate would fan out and double-count | |
| 867 | + // that order's revenue in every SUM. | |
| 868 | + // | |
| 869 | + // Within a duplicate group the LATEST row wins (highest id), picked as one | |
| 870 | + // whole row. Taking MAX(country) and MAX(state) independently would let the | |
| 871 | + // two columns come from DIFFERENT rows and synthesize a place that does not | |
| 872 | + // exist: order 259 on the reference store has billing rows (BD, BD-60) and | |
| 873 | + // (AT, BD-60), so independent MAX()es resolve it to Bangladesh even though | |
| 874 | + // its latest address is Austrian. (That order is payment_status=pending and | |
| 875 | + // so outside self::PAID — it demonstrates the mechanism, not a revenue error | |
| 876 | + // this report was making.) Five duplicate groups there disagree on country or | |
| 877 | + // state. latestRowPick() does the one-row pick inside the GROUP BY. | |
| 878 | + // | |
| 879 | + // The customer's primary billing address is the SECOND source, and the | |
| 880 | + // choice between the two is made PER ORDER, not per column — see | |
| 881 | + // dimensionExpr(). Most orders here carry no address row of their own | |
| 882 | + // (digital goods skip the billing step), so order-address-only attribution | |
| 883 | + // left 976 of 4,905 paid orders — 19.9% of orders and 9.0% of revenue — in | |
| 884 | + // the 'unknown' bucket, which makes "revenue by country" unusable for the | |
| 885 | + // question it exists to answer. Falling back to the buyer's own primary | |
| 886 | + // billing address recovers 878 of those 976 and cuts 'unknown' to 2.0%; | |
| 887 | + // 98 orders are then genuinely unattributable. (Figures over all four | |
| 888 | + // self::PAID statuses, which is what this method actually queries.) | |
| 889 | + // geo_source in meta names the precedence so an agent never has to guess. | |
| 890 | + $geoDims = array_values(array_intersect(['country', 'state'], $dimensions)); | |
| 891 | + $wantsGeo = $geoDims || !empty($params['country']); | |
| 892 | + if ($wantsGeo) { | |
| 893 | + // Columns aliased oaddr_*/caddr_* so raw select expressions stay | |
| 894 | + // unambiguous without table prefixes (same note as queryCustomers). | |
| 895 | + $addrSub = App::db()->table('fct_order_addresses') | |
| 896 | + ->select('order_id') | |
| 897 | + ->selectRaw( | |
| 898 | + self::latestRowPick('country') . ' as oaddr_country, ' | |
| 899 | + . self::latestRowPick('state') . ' as oaddr_state' | |
| 900 | + ) | |
| 901 | + ->where('type', 'billing') | |
| 902 | + ->groupBy('order_id'); | |
| 903 | + | |
| 904 | + $query->leftJoinSub($addrSub, 'fc_oaddr', 'fct_orders.id', '=', 'fc_oaddr.order_id'); | |
| 905 | + | |
| 906 | + // LEFT JOIN on customer_id, so a guest order simply has no fallback and | |
| 907 | + // still lands in 'unknown' rather than dropping: fct_orders.customer_id | |
| 908 | + // is nullable and NULL never matches a join predicate. The customer_id > 0 | |
| 909 | + // guard covers the other shape of missing buyer — an order carrying 0 | |
| 910 | + // instead of NULL would otherwise join to an orphaned address row filed | |
| 911 | + // under customer 0 and inherit a stranger's country. | |
| 912 | + // | |
| 913 | + // customer_id is aliased caddr_customer_id for the same reason the value | |
| 914 | + // columns are aliased: fct_orders has a customer_id too, and exposing a | |
| 915 | + // second one made the existing COUNT(DISTINCT customer_id) behind the | |
| 916 | + // unique_customers metric ambiguous — a hard SQL error, not a wrong | |
| 917 | + // number. Nothing joined here may share a name with an orders column. | |
| 918 | + $custAddrSub = App::db()->table('fct_customer_addresses') | |
| 919 | + ->selectRaw('customer_id as caddr_customer_id') | |
| 920 | + ->selectRaw( | |
| 921 | + self::latestRowPick('country') . ' as caddr_country, ' | |
| 922 | + . self::latestRowPick('state') . ' as caddr_state' | |
| 923 | + ) | |
| 924 | + ->where('type', 'billing') | |
| 925 | + ->where('is_primary', 1) | |
| 926 | + ->where('customer_id', '>', 0) | |
| 927 | + ->groupBy('customer_id'); | |
| 928 | + | |
| 929 | + $query->leftJoinSub($custAddrSub, 'fc_caddr', 'fct_orders.customer_id', '=', 'fc_caddr.caddr_customer_id'); | |
| 930 | + | |
| 931 | + if (!empty($params['country'])) { | |
| 932 | + // Built from dimensionExpr() itself, so the filter and the country | |
| 933 | + // dimension resolve a country identically BY CONSTRUCTION — including | |
| 934 | + // the 'unknown' bucket, which a hand-written COALESCE here omitted, | |
| 935 | + // making country=unknown return zero rows while the dimension | |
| 936 | + // reported 98 such orders. | |
| 937 | + $query->whereRaw( | |
| 938 | + self::dimensionExpr('country') . ' = ?', | |
| 939 | + [sanitize_text_field($params['country'])] | |
| 940 | + ); | |
| 941 | + } | |
| 942 | + } | |
| 943 | + | |
| 857 | 944 | $selects = []; |
| 858 | 945 | $groupCols = []; |
| 946 | + $groupRaws = []; | |
| 859 | 947 | foreach ($dimensions as $dim) { |
| 860 | - $selects[] = self::dimensionExpr($dim) . ' as ' . $dim; | |
| 861 | - $groupCols[] = $dim; | |
| 948 | + $expr = self::dimensionExpr($dim); | |
| 949 | + $selects[] = $expr . ' as ' . $dim; | |
| 950 | + if (in_array($dim, ['country', 'state'], true)) { | |
| 951 | + // Group by the EXPRESSION, not the alias: the alias collides with | |
| 952 | + // the real fc_oaddr.country column, and MySQL resolves a GROUP BY | |
| 953 | + // name to the column first — which would split NULL from '' and | |
| 954 | + // scatter the unknowns across two rows instead of one bucket. | |
| 955 | + $groupRaws[] = $expr; | |
| 956 | + } else { | |
| 957 | + $groupCols[] = $dim; | |
| 958 | + } | |
| 862 | 959 | } |
| 863 | 960 | |
| 864 | 961 | $metricSql = [ |
| 865 | 962 | 'order_count' => 'COUNT(*) as order_count', |
| @@ -885,8 +982,11 @@ | ||
| 885 | 982 | $query->selectRaw(implode(', ', $selects)); |
| 886 | 983 | foreach ($groupCols as $g) { |
| 887 | 984 | $query->groupBy($g); |
| 888 | 985 | } |
| 986 | + foreach ($groupRaws as $g) { | |
| 987 | + $query->groupByRaw($g); | |
| 988 | + } | |
| 889 | 989 | |
| 890 | 990 | $sortDesc = !isset($params['sort_desc']) || !empty($params['sort_desc']); |
| 891 | 991 | $firstMetric = isset($metrics[0]) ? $metrics[0] : 'order_count'; |
| 892 | 992 | if ($firstMetric === 'aov') { |
| @@ -902,9 +1002,9 @@ | ||
| 902 | 1002 | } |
| 903 | 1003 | } |
| 904 | 1004 | |
| 905 | 1005 | $paging = self::queryPaging($params); |
| 906 | - if ($groupCols) { | |
| 1006 | + if ($groupCols || $groupRaws) { | |
| 907 | 1007 | if ($timeDim !== null && !isset($params['sort_desc'])) { |
| 908 | 1008 | // A time series reads chronologically by default; ranking a |
| 909 | 1009 | // calendar by metric is rarely what's wanted. An explicit |
| 910 | 1010 | // sort_desc still overrides this. |
| @@ -916,8 +1016,12 @@ | ||
| 916 | 1016 | // reshuffles equal-metric rows across pages. |
| 917 | 1017 | foreach ($groupCols as $g) { |
| 918 | 1018 | $query->orderBy($g, 'ASC'); |
| 919 | 1019 | } |
| 1020 | + foreach ($groupRaws as $g) { | |
| 1021 | + // Raw, for the same alias/column collision reason as the GROUP BY. | |
| 1022 | + $query->orderByRaw($g . ' ASC'); | |
| 1023 | + } | |
| 920 | 1024 | } |
| 921 | 1025 | // One extra row peeks past the page boundary → meta.page.has_more. |
| 922 | 1026 | $query->limit($paging['per_page'] + 1)->offset($paging['offset']); |
| 923 | 1027 | |
| @@ -955,12 +1059,19 @@ | ||
| 955 | 1059 | implode(', ', $metrics), |
| 956 | 1060 | $dimensions ? implode(', ', $dimensions) : __('total', 'fluent-cart') |
| 957 | 1061 | ); |
| 958 | 1062 | |
| 1063 | + $meta = ['currency' => $currency, 'date_basis' => 'created_at', 'mode' => $mode]; | |
| 1064 | + // Only when geography was actually asked for — an agent grouping by month | |
| 1065 | + // should not have to read a note about addresses. | |
| 1066 | + if ($wantsGeo) { | |
| 1067 | + $meta['geo_source'] = 'the order\'s own billing address, falling back to the buyer\'s primary billing address, then "unknown" for orders neither source can place. Rows always sum to the period total, so the "unknown" bucket shows exactly how much revenue is unattributed.'; | |
| 1068 | + } | |
| 1069 | + | |
| 959 | 1070 | return MCPHelper::envelope( |
| 960 | 1071 | $summary, |
| 961 | 1072 | ['metrics' => $metrics, 'dimensions' => $dimensions, 'range' => self::rangeBlock($range, $currency), 'rows' => $out], |
| 962 | - array_merge(['currency' => $currency, 'date_basis' => 'created_at', 'mode' => $mode], self::pageMeta($paging, $fetched)) | |
| 1073 | + array_merge($meta, self::pageMeta($paging, $fetched)) | |
| 963 | 1074 | ); |
| 964 | 1075 | } |
| 965 | 1076 | |
| 966 | 1077 | // ----------------------------------------------------------------- |
| @@ -1147,11 +1258,69 @@ | ||
| 1147 | 1258 | $metrics = self::pickList($params, 'metrics', ['customer_count', 'total_ltv', 'avg_ltv', 'avg_purchase_count', 'repeat_customers'], ['customer_count', 'total_ltv']); |
| 1148 | 1259 | $dimensions = self::pickList($params, 'dimensions', ['country', 'state', 'status', 'first_purchase_month', 'last_purchase_month'], []); |
| 1149 | 1260 | |
| 1150 | 1261 | $query = Customer::query(); |
| 1262 | + | |
| 1263 | + // Geography resolves through the customer's primary BILLING ADDRESS, | |
| 1264 | + // falling back to the fct_customers profile columns. | |
| 1265 | + // | |
| 1266 | + // Neither source is complete on its own, which is the whole reason for the | |
| 1267 | + // coalesce. Measured on the 2,320-customer reference store: 2,176 customers | |
| 1268 | + // have both and the two NEVER disagree (zero rows where both are set and | |
| 1269 | + // differ), 96 have only the profile column, 7 have only an address row, and | |
| 1270 | + // 41 have neither. So reading either source alone silently drops customers | |
| 1271 | + // the other could place — profile-only would lose 7, address-only would lose | |
| 1272 | + // 96 — while the coalesce leaves just the 41 genuinely unplaceable. | |
| 1273 | + // | |
| 1274 | + // Billing address is ordered first because it is the value the buyer actually | |
| 1275 | + // typed, whereas the profile column is written once when the customer row is | |
| 1276 | + // created and never refreshed (and at checkout the seeded value can be a | |
| 1277 | + // country the frontend guessed from the browser timezone). On this store that | |
| 1278 | + // ordering changes no result; it is the correct precedence for the case where | |
| 1279 | + // a profile snapshot has gone stale. | |
| 1280 | + // | |
| 1281 | + // LEFT JOIN, so a customer with no address row is still counted (on the | |
| 1282 | + // profile value, or 'unknown'); an INNER JOIN would silently drop them | |
| 1283 | + // and quietly shrink every total. The join rides the existing | |
| 1284 | + // (customer_id, is_primary) index — measured at no material cost. | |
| 1285 | + // | |
| 1286 | + // Grouped subquery rather than a raw leftJoin: fct_customer_addresses has | |
| 1287 | + // no UNIQUE constraint on (customer_id, type, is_primary), so a second | |
| 1288 | + // primary billing row would fan the join out and double-count that | |
| 1289 | + // customer in COUNT(*) and SUM(ltv). Same guard the source report applies | |
| 1290 | + // to fct_order_operations above. MAX() is ONLY_FULL_GROUP_BY-safe and | |
| 1291 | + // returns the row's own value in the normal one-row case. | |
| 1292 | + // The subquery's columns are aliased addr_* on purpose: raw SQL fragments | |
| 1293 | + // are not table-prefixed by the builder, so a bare `country` in a | |
| 1294 | + // selectRaw would be ambiguous across the two tables and a qualified | |
| 1295 | + // `fct_customers.country` would miss the wp_ prefix. Distinct names keep | |
| 1296 | + // every raw expression unambiguous with no prefix handling at all. | |
| 1297 | + // latestRowPick, not MAX() per column: with more than one primary billing row | |
| 1298 | + // independent MAX()es can take country from one row and state from another and | |
| 1299 | + // report a place that does not exist. See the note in queryOrders. | |
| 1300 | + $addrSub = App::db()->table('fct_customer_addresses') | |
| 1301 | + ->select('customer_id') | |
| 1302 | + ->selectRaw( | |
| 1303 | + self::latestRowPick('country') . ' as addr_country, ' | |
| 1304 | + . self::latestRowPick('state') . ' as addr_state' | |
| 1305 | + ) | |
| 1306 | + ->where('type', 'billing') | |
| 1307 | + ->where('is_primary', 1) | |
| 1308 | + ->groupBy('customer_id'); | |
| 1309 | + | |
| 1310 | + $query->leftJoinSub($addrSub, 'fc_addr', 'fct_customers.id', '=', 'fc_addr.customer_id'); | |
| 1311 | + | |
| 1151 | 1312 | if (!empty($params['country'])) { |
| 1152 | - $query->where('country', sanitize_text_field($params['country'])); | |
| 1313 | + $country = sanitize_text_field($params['country']); | |
| 1314 | + // Match on the resolved value, not the raw column, so the filter and | |
| 1315 | + // the grouping can never disagree about which country a customer is in. | |
| 1316 | + $query->whereRaw( | |
| 1317 | + "COALESCE(NULLIF(fc_addr.addr_country, ''), NULLIF(country, '')) = ?", | |
| 1318 | + [$country] | |
| 1319 | + ); | |
| 1153 | 1320 | } |
| 1321 | + // Unambiguous without qualification: the joined subquery exposes only | |
| 1322 | + // customer_id / addr_country / addr_state. | |
| 1154 | 1323 | if (!empty($params['status'])) { |
| 1155 | 1324 | $query->where('status', sanitize_text_field($params['status'])); |
| 1156 | 1325 | } |
| 1157 | 1326 | if (isset($params['min_ltv'])) { |
| @@ -1164,17 +1333,26 @@ | ||
| 1164 | 1333 | $selects = []; |
| 1165 | 1334 | $groupCols = []; |
| 1166 | 1335 | foreach ($dimensions as $dim) { |
| 1167 | 1336 | if ($dim === 'country' || $dim === 'state') { |
| 1168 | - // Coalesce NULL and '' into a single 'unknown' bucket. Group by the | |
| 1169 | - // expression (not the alias, which would resolve to the raw column | |
| 1170 | - // and keep null/'' split). | |
| 1171 | - $expr = "COALESCE(NULLIF($dim, ''), 'unknown')"; | |
| 1337 | + // Billing address first, profile column second, 'unknown' last. | |
| 1338 | + // Coalesce NULL and '' into the single 'unknown' bucket. Group by | |
| 1339 | + // the expression (not the alias, which would resolve to the raw | |
| 1340 | + // column and keep null/'' split). | |
| 1341 | + $expr = "COALESCE(NULLIF(fc_addr.addr_$dim, ''), NULLIF($dim, ''), 'unknown')"; | |
| 1172 | 1342 | $selects[] = "$expr as $dim"; |
| 1173 | 1343 | $groupCols[] = $expr; |
| 1174 | 1344 | } elseif ($dim === 'status') { |
| 1175 | - $selects[] = $dim; | |
| 1176 | - $groupCols[] = $dim; | |
| 1345 | + // Same 'unknown' coalescing as country/state, for the same reason. | |
| 1346 | + // fct_customers.status is nullable with no default and 77 of the | |
| 1347 | + // 2,320 customers on the reference store have NULL or '' — so a bare | |
| 1348 | + // GROUP BY status answered "how many customers per status" with two | |
| 1349 | + // buckets the agent was never told about (null AND '', split apart), | |
| 1350 | + // neither of them in the status enum. One labelled bucket keeps the | |
| 1351 | + // rows summing to the customer total and makes the gap legible. | |
| 1352 | + $expr = "COALESCE(NULLIF(status, ''), 'unknown')"; | |
| 1353 | + $selects[] = "$expr as status"; | |
| 1354 | + $groupCols[] = $expr; | |
| 1177 | 1355 | } elseif ($dim === 'first_purchase_month') { |
| 1178 | 1356 | $selects[] = "DATE_FORMAT(first_purchase_date, '%Y-%m') as first_purchase_month"; |
| 1179 | 1357 | $groupCols[] = "DATE_FORMAT(first_purchase_date, '%Y-%m')"; |
| 1180 | 1358 | } elseif ($dim === 'last_purchase_month') { |
| @@ -1245,9 +1423,16 @@ | ||
| 1245 | 1423 | count($out), |
| 1246 | 1424 | implode(', ', $metrics) |
| 1247 | 1425 | ), |
| 1248 | 1426 | ['metrics' => $metrics, 'dimensions' => $dimensions, 'rows' => $out], |
| 1249 | - array_merge(['currency' => MCPHelper::currencyCode(), 'note' => 'LTV is in store currency; customers are not currency-scoped.'], self::pageMeta($paging, $fetched)) | |
| 1427 | + array_merge( | |
| 1428 | + [ | |
| 1429 | + 'currency' => MCPHelper::currencyCode(), | |
| 1430 | + 'note' => 'LTV is in store currency; customers are not currency-scoped.', | |
| 1431 | + 'country_source' => 'primary billing address, falling back to the customer profile location, then "unknown". Registration geography, captured at first purchase — for per-order billing geography use query-orders grouped by country.', | |
| 1432 | + ], | |
| 1433 | + self::pageMeta($paging, $fetched) | |
| 1434 | + ) | |
| 1250 | 1435 | ); |
| 1251 | 1436 | } |
| 1252 | 1437 | |
| 1253 | 1438 | // ----------------------------------------------------------------- |
| @@ -1391,8 +1576,39 @@ | ||
| 1391 | 1576 | } |
| 1392 | 1577 | } |
| 1393 | 1578 | } |
| 1394 | 1579 | |
| 1580 | + /** | |
| 1581 | + * Aggregate expression returning $column from the HIGHEST-id row in the group. | |
| 1582 | + * | |
| 1583 | + * Address tables have no UNIQUE constraint on (parent, type), so a group can hold | |
| 1584 | + * several rows and the newest is the one the buyer last entered. Two columns each | |
| 1585 | + * aggregated with a bare MAX() can come from two different rows and describe a | |
| 1586 | + * place that never existed (a real case: country from one row, state from another). | |
| 1587 | + * | |
| 1588 | + * Prefixing each value with its zero-padded id makes lexicographic MAX() agree | |
| 1589 | + * with numeric id order, so every column built this way resolves to the SAME row; | |
| 1590 | + * SUBSTRING then drops the prefix. The width is 20 because that is exactly the | |
| 1591 | + * digit count of the largest BIGINT UNSIGNED (18446744073709551615) — no id can | |
| 1592 | + * overflow the padding, so all prefixes are equal-length and compare numerically. | |
| 1593 | + * Ids are unique within a group (id is the PK), so the prefix alone always decides | |
| 1594 | + * the winner and the collation of the value suffix can never influence it. | |
| 1595 | + * | |
| 1596 | + * COALESCE(...,'') matters — CONCAT with NULL is NULL and MAX() skips NULLs, which | |
| 1597 | + * would let a NULL column fall back to a different row and reintroduce the mixing | |
| 1598 | + * this exists to prevent. | |
| 1599 | + * | |
| 1600 | + * Portable to MySQL 5.6+ (no window functions) and needs no nested join, unlike | |
| 1601 | + * ORDER BY ... LIMIT 1 or ROW_NUMBER(). | |
| 1602 | + * | |
| 1603 | + * @param string $column trusted column name — never interpolate caller input here | |
| 1604 | + * @return string | |
| 1605 | + */ | |
| 1606 | + private static function latestRowPick($column) | |
| 1607 | + { | |
| 1608 | + return "SUBSTRING(MAX(CONCAT(LPAD(id, 20, '0'), COALESCE($column, ''))), 21)"; | |
| 1609 | + } | |
| 1610 | + | |
| 1395 | 1611 | private static function dimensionExpr($dim) |
| 1396 | 1612 | { |
| 1397 | 1613 | if ($dim === 'day') { |
| 1398 | 1614 | return "DATE_FORMAT(created_at, '%Y-%m-%d')"; |
| @@ -1408,8 +1624,33 @@ | ||
| 1408 | 1624 | // fct_orders.type column; expose it under the order_type alias so the |
| 1409 | 1625 | // dimension name and response key read naturally and don't collide |
| 1410 | 1626 | // with the unrelated payment_type on line items. |
| 1411 | 1627 | return 'type'; |
| 1628 | + } | |
| 1629 | + if ($dim === 'country' || $dim === 'state') { | |
| 1630 | + // ONE source per order, decided by whether the order has a billing | |
| 1631 | + // address row at all (fc_oaddr.order_id IS NULL) — never per column. | |
| 1632 | + // | |
| 1633 | + // Coalescing each column independently mixes provenance inside a single | |
| 1634 | + // order and invents places: order 16 on the reference store has its own | |
| 1635 | + // billing row (BG, '') while its buyer's address says (BG, BG-22), so a | |
| 1636 | + // per-column COALESCE reported that order's state as BG-22 — a value | |
| 1637 | + // from a mutable customer record, for an order that carries its own | |
| 1638 | + // address. 38 paid orders were affected. It is the same row-mixing | |
| 1639 | + // latestRowPick() prevents inside a table, reappearing across tables. | |
| 1640 | + // | |
| 1641 | + // Consequence, deliberately: an order whose own address has a country | |
| 1642 | + // but a blank state reports state 'unknown' rather than borrowing one. | |
| 1643 | + // That is the honest answer — that order's address genuinely has no | |
| 1644 | + // state — and it keeps historical attribution stable when a customer | |
| 1645 | + // later edits their address. | |
| 1646 | + // | |
| 1647 | + // Orders neither source can place get the 'unknown' bucket rather than | |
| 1648 | + // being dropped, so rows still sum to the period's total revenue and the | |
| 1649 | + // size of the gap stays visible. | |
| 1650 | + $pick = "CASE WHEN fc_oaddr.order_id IS NULL THEN fc_caddr.caddr_$dim ELSE fc_oaddr.oaddr_$dim END"; | |
| 1651 | + | |
| 1652 | + return "COALESCE(NULLIF($pick, ''), 'unknown')"; | |
| 1412 | 1653 | } |
| 1413 | 1654 | return $dim; |
| 1414 | 1655 | } |
| 1415 | 1656 | |
