Location Intelligence Retail Site Selection Automation

Backtesting Site Scores Against Realized Store Sales

This page solves one exact task: joining archived site scores to the sales those stores went on to achieve, adjusting for maturity and market effects that have nothing to do with site quality, and reporting whether the score predicted anything.

The exercise is simple arithmetic wrapped around one hard requirement: the score must have been recorded before the outcome was known. Everything else here is about removing the effects that would otherwise be credited to or blamed on the site.

Prerequisites

  • Archived scored runs with the site identifier, the composite score, the criterion values and the weight version — as described in validating and backtesting site selection models.
  • Realized sales per store per period, ideally with a like-for-like measure and store size for normalisation.
  • Market context: a market-level sales index over the same period, so a good store in a declining market is not scored as a modelling failure.
  • Python packages: pandas, numpy, scipy. Install with pip install pandas numpy scipy.

Configuration and execution parameters

Parameter Value for this task Notes
maturity_months 18 Sales before this are opening effects
outcome_measure sales per square metre, year two Normalises for store size
market_adjustment divide by the market index Removes market movement
min_stores 40 Below this the metrics are noise
primary_metric Spearman rank correlation The ranking is the deliverable
report_also decile lift, hit rate at the cut What a committee reads
include_closed yes The most informative rows in the dataset

include_closed is the row that changes the answer most and is most often set the other way. A backtest run against currently-trading stores has quietly removed every failure, which is exactly the outcome the model is meant to help avoid.

Four adjustments between raw sales and a comparable outcome Raw first-year sales are adjusted to year two to remove the opening surge, divided by floor area to remove size, divided by the market index to remove market movement, and adjusted for opening date to remove the effect of trading through a different economy — leaving a figure attributable to the site. Each adjustment removes something the site did not cause raw sales year one year two no opening surge per m² size removed ÷ market index attributable to the site Skipping the market adjustment is the subtle one: a model that opened good sites during a downturn will look as though it picked badly, and its weights will be "corrected" toward whatever traded through it. Skipping the maturity adjustment favours dense urban sites, whose opening surge is largest — so a model validated on first-year sales learns to prefer them for a reason that evaporates in year two. Every adjustment is a judgement, so each belongs in the model card with the figure it produced rather than applied silently inside a query.

Annotated implementation

python
from __future__ import annotations

import numpy as np
import pandas as pd
from scipy.stats import spearmanr

MATURITY_MONTHS = 18


def prepare_outcomes(sales: pd.DataFrame, stores: pd.DataFrame,
                     market_index: pd.DataFrame) -> pd.DataFrame:
    """Year-two sales per square metre, adjusted for market movement."""
    df = sales.merge(stores[["store_id", "opened_on", "floor_area_m2",
                             "market", "closed_on"]], on="store_id")
    df["months_open"] = ((df["period"] - df["opened_on"])
                         / np.timedelta64(1, "M")).round()
    mature = df.loc[(df["months_open"] > MATURITY_MONTHS)
                    & (df["months_open"] <= MATURITY_MONTHS + 12)]

    out = (mature.groupby(["store_id", "market"], as_index=False)
           .agg(sales=("sales", "sum"), area=("floor_area_m2", "first")))
    out["sales_per_m2"] = out["sales"] / out["area"]

    idx = market_index.set_index("market")["index"]
    out["adjusted"] = out["sales_per_m2"] / out["market"].map(idx)

    # Closed stores are outcomes too — the worst ones. Keep them, with a floor
    # of zero rather than an absence, or the backtest never sees a failure.
    closed = stores.loc[stores["closed_on"].notna(), ["store_id", "market"]]
    missing = closed.loc[~closed["store_id"].isin(out["store_id"])].copy()
    missing["adjusted"] = 0.0
    return pd.concat([out, missing], ignore_index=True)


def backtest(scores: pd.DataFrame, outcomes: pd.DataFrame) -> dict:
    """Rank correlation plus the decile table a committee will actually read."""
    df = scores.merge(outcomes, on="store_id", how="inner")
    assert len(df) >= 40, "too few matured stores for a meaningful backtest"

    rho, p = spearmanr(df["score"], df["adjusted"])
    df["decile"] = pd.qcut(df["score"], 10, labels=False, duplicates="drop")
    mean_all = df["adjusted"].mean()
    deciles = (df.groupby("decile")["adjusted"].mean() / mean_all).round(3)

    top = df.loc[df["decile"] == df["decile"].max(), "adjusted"]
    bottom = df.loc[df["decile"] == df["decile"].min(), "adjusted"]
    return {
        "n": len(df),
        "spearman": round(float(rho), 3),
        "p_value": round(float(p), 5),
        "decile_lift": deciles.to_dict(),
        "top_vs_bottom": round(float(top.mean() / bottom.mean()), 2),
    }

Assigning closed stores an adjusted outcome of zero rather than dropping them is a deliberate simplification, and it is the right direction of error: a store that closed did not merely underperform, and treating it as the worst observation keeps the model’s most expensive mistakes in the sample.

Failure modes and debugging

Scores reconstructed after the fact. Re-running today’s model on a site that opened three years ago tests nothing, because the model has since been shaped by that store’s performance. If archived scores do not exist, say so and start archiving; a reconstructed backtest is worse than none because it produces a number people trust.

Sales measured at different maturities. A store open twenty months and one open forty are not comparable, and mixing them puts recently-opened sites at the bottom of the outcome distribution regardless of quality. Windowing on months since opening, as above, is the fix.

Format mixed into one correlation. Convenience and supermarket sales per square metre live on different scales, so a pooled correlation is partly measuring format. Backtest within format, and report the pooled figure only alongside the segments.

Ignoring transfers. A store that met its forecast by taking sales from a sister store has not created value, and a backtest against gross sales rewards it. Where a transfer estimate exists, backtesting against net new sales measures what the business actually got.

The same model, backtested four ways Pooled across formats the rank correlation is 0.58; within supermarkets it is 0.64, within convenience 0.49, and against net new sales rather than gross it falls to 0.41 — which is the figure that matches the business question and the one least often reported. Four defensible numbers, one business question pooled, gross sales 0.58 supermarkets only 0.64 convenience only 0.49 net new sales 0.41 — the honest one The model is better at supermarkets than convenience, which is a finding rather than a defect — and it says the convenience weights need work rather than that the approach is wrong. Spearman rank correlation between archived score and market-adjusted year-two outcome, 214 stores.

Verification

  • Confirm the scores predate the outcomes. Check the archive timestamp against the opening date for every row in the backtest; any row that fails belongs out of the sample.
  • Check the closed stores are present. Count them and compare against the estate’s closure history; a backtest with no closures has silently filtered them.
  • Run the metrics on a shuffled outcome column. A correlation near zero confirms the pipeline is not leaking the answer into the score; anything else means it is.
  • Compare against a naive baseline — population within fifteen minutes, alone. A model that does not beat one criterion is not earning its complexity.
Does the model beat one number? Reachable population alone achieves a rank correlation of 0.44. Adding demographic fit takes it to 0.52, adding competition to 0.58, and the full weighted model reaches 0.58 as well — so the last two criteria are contributing nothing measurable and are candidates for removal. Where the predictive power actually comes from reachable population alone 0.44 + demographic fit 0.52 + competition 0.58 full model (5 criteria) 0.58 — no gain Rent and co-tenancy add nothing here. That does not make them unimportant to the business — it means they are not predicting sales, and their weight in the score should be defended on other grounds.

Frequently Asked Questions

What correlation is good enough?

Enough to beat the baseline and the alternative, which for site selection is usually a rank correlation in the range of 0.4 to 0.6 on a prospective test. Anything much higher on a prospective design should be checked for leakage rather than celebrated. What matters more than the level is the decile pattern: a model with a modest correlation and a clean monotonic lift is more useful for a top-decile decision than one with a higher correlation driven by the middle of the distribution.

Should the backtest drive a re-fit of the weights?

It should inform one, on a schedule, with the results of the re-fit validated on a fresh holdout. What it should not do is trigger an immediate adjustment each time a store underperforms, which is how a model acquires a weight for whatever happened most recently. Separating the measurement cadence from the fitting cadence — validate quarterly, re-fit annually — keeps both honest.

How are stores that never opened handled?

As a separate and valuable dataset. A high-scoring site that was rejected for a reason unrelated to the model — a lease that fell through, a planning refusal — is a missing observation rather than a failure, and where a competitor later opened there, its observable performance is a genuine test of the model’s judgement. Recording rejection reasons at the time is what makes this possible later.

What if sales data is not available at store level?

Use the best available proxy and state it: transaction counts, footfall, or a category-level revenue allocation. All are weaker than sales and all are usable for a rank correlation, which only needs the ordering to be roughly right. What is not acceptable is quietly substituting a proxy and describing the result as validated against sales.

How should the backtest handle acquired stores?

As a separate, valuable stratum. Stores a chain acquired rather than chose are the closest thing to an unselected sample most retailers ever hold, so backtesting against them tests the model without the survivorship problem that afflicts the rest of the estate. They come with their own complication — an acquired store’s performance reflects its previous operator as much as its site — which argues for using them to check the ranking rather than to fit weights, and for reporting them separately rather than pooling them in.

What does a backtest cost to run once it exists?

Minutes of compute and about a day of analyst time per quarter, almost all of it spent reading the outliers rather than computing the metrics. That asymmetry is worth designing for: automate the join, the adjustments and the metrics so the recurring cost is the judgement, and keep the outlier list short enough that reading it is realistic. A backtest that takes a week to produce will be run annually and then not at all.

Can the backtest be run on candidate sites that were never approved?

Not against sales, since there are none — but a partial version is available and worth running. Comparing the score distribution of approved sites against rejected ones tells you whether the committee’s decisions and the model’s ranking agree, and where they systematically diverge. That is a check on the decision process rather than on the model, and it is frequently more interesting: a committee that routinely approves sites in the model’s third decile is telling the modelling team something about criteria the model does not contain.

← Back to Validating & Backtesting Site Selection Models