Catchment Mission Fit

At k = 3 the inner-city apartment catchments vanish into a broader urban group. That group is only 15 areas and 15,156 households, but it is the most behaviourally distinct segment in Auckland — 45% living alone, 52% without a car, five times the household density of anywhere else. Collapsing it produces a tidier model that cannot see the finding.

An analyst who reports only the silhouette-optimal k has let the metric make a business decision.

Suppression coded as −999. Caught because the household total increased after dropping small areas, which is arithmetically impossible. The ≥100-household filter happened to remove every suppressed row. The analysis was never contaminated — but that was luck, not design, and a filter on any other column would have corrupted every derived percentage.

Projected, not geographic, coordinates. The export ships NZTM2000 eastings and northings in metres. An initial pass assuming degrees returned zero rows.

Unstable cluster labels. K-means label numbers are arbitrary and move when the input changes. Names hardcoded to label numbers silently attached to the wrong clusters — Eden Terrace came out as "South Auckland multi-family". Names are now derived from each cluster's own profile and verified against four known areas.

Bounding-box region filter. An initial coordinate box leaked into Waikato, putting Thames and Coromandel in the results at 56 km from a store. Recalibrated against known centroids: Waiheke East marks the eastern edge at x = 1,791,870.

The clusters are defined by these features, so the significance tests are close to tautological. They are reported for effect size, not for the p-values, and anyone reading them as vindication has misread them.

The two informative results are the ones not guaranteed by construction: the gap statistic, which is an independent criterion for k — and which never satisfies the 1-SE rule, rising all the way to k = 10, so it does not support my choice of five — and the bootstrap Jaccard scores, which test whether the segments survive resampling. At a mean of 0.862, with every segment above Hennig's 0.75 threshold, they do.

Reporting the gap statistic result is the honest half of this section. It disagrees with me.

Query 4 did not bind. It computed a banner benchmark as AVG(SUM(band_sales)) OVER (PARTITION BY banner) — an aggregate nested inside a window function, in a query with no GROUP BY to support it. Invalid SQL that reads perfectly well on the page. Rewritten to take the average of per-store shares, which also fixes the statistic: the original was a ratio of averages, so a single high-turnover store could set the benchmark for its whole banner. Now every store counts once.

Query 4 referenced a column the schema block did not declare. ref_catchment.avg_residents_proxy was used but never documented. Building the tables straight from the documented block is what surfaced it. The block now declares it, and the correction is dated in the file rather than quietly patched.

Both were found the first time the file was executed, and neither would have been found by reading it.

I do not know FSNI's warehouse schema. The table and column names are a documented assumption, defined in a block at the top of the file so the logic can be reviewed independently of the naming. Adapting these to the real schema is a rename, not a rewrite.

The file is written for Snowflake, BigQuery or SQL Server. Running it on DuckDB needed three scalar-function substitutions, listed in the log above — which is what "minimal change between dialects" should mean in practice, stated as a count rather than as a claim.

Zero rows is the correct result, not a disappointing one. No synthetic transactions were generated. Filling these tables with invented baskets would produce output that looks like a finding, proves nothing, and to a reader skimming quickly would imply access I do not have.

$171.6m of annual grocery spend sits in the 22 flagged catchments. Against FSNI's published FY24 accounts — $9.2bn revenue, $292m EBIT, a 3.17% margin and a $3.2m statutory loss — recapturing 10% of it is worth roughly $0.46m of EBIT. About 0.16%.

That is modest, and the model says so rather than hiding it. The same workbook shows a 5% improvement in the allocation of FSNI's $254m annual capex is worth around $12.7m — twenty-seven times the leverage. The catchment gaps are not the prize. They are the evidence the method works.

South Auckland's 129 multi-family catchments — the segment with the largest baskets and the tightest budgets — are well served, at a median 2.22 km to a Pak'nSave. That is closer than any other segment sits to its matching value format.

I expected to find a gap there. There isn't one, and it appears on this page at the same size as the findings that support the argument. A model that only ever confirms expectations is not being read honestly.

Distances are straight-line, not walking or driving. In the CBD, with motorways and a harbour edge, real walking distance is longer — which widens these gaps rather than narrowing them.

Store positions come from OpenStreetMap. Querying supermarkets alone returned 7 Four Squares for the whole region, an obvious undercount; including convenience-tagged stores gives 24, against 25 reported for the Auckland region as at April 2025. Coverage is effectively complete — but the eight inner-city flags should still be confirmed against the official store finder before any decision rests on them.