Location Intelligence Retail Site Selection Automation

Validating Foot Traffic Estimates Against Point-of-Sale Counts

This page solves one exact task: comparing panel-derived visit estimates against transaction counts for the stores where both exist, fitting a calibration factor, and reading the residuals for what they say about formats, markets and the panel itself.

This is the step that gives panel estimates units. Without it, a corrected visit figure is a well-constructed number in a scale nobody can interpret; with it, the same figure becomes an estimate of shopping trips that can be compared against a store’s own performance and used in a forecast.

Prerequisites

  • Corrected panel estimates per store per month, from correcting panel bias in mobility datasets.
  • Transaction counts per store per month — baskets, not revenue, since the panel counts visits rather than spend.
  • Store attributes: format, size, market density, opening hours. The residuals are organised by these.
  • Python packages: pandas, numpy, statsmodels for the regression. Install with pip install pandas numpy statsmodels.

Configuration and execution parameters

Parameter Value for this task Notes
target transactions per month Baskets, excluding online and click-collect
predictor corrected panel visits Never raw panel visits
fit_form log-log regression Multiplicative errors are the norm here
min_months 6 Fewer months and seasonality dominates
group_by format One factor per format, not one globally
outlier_rule 3 robust standard deviations Investigated, not discarded
refresh quarterly The panel changes; the factor drifts

Using baskets rather than revenue is the detail that keeps the comparison honest. A panel visit is a person entering a store; a basket is a person buying something. The two differ by a conversion rate that varies by format and season, and comparing visits against revenue mixes that conversion with basket size, producing a factor that means nothing.

The calibration line and the stores that sit off it Corrected panel visits and monthly transactions line up closely across 180 stores, with a fitted slope near one and a scale factor of 26 transactions per estimated panel visit. Three stores sit well above the line — all inside shopping centres, where the vendor place includes other tenants. A tight line, and three stores that explain themselves corrected panel visits (log scale) transactions (log scale) in-centre stores — the place includes other tenants Slope 0.97, scale factor 26 transactions per estimated visit, R² 0.91 across 180 stores and six months.

Annotated implementation

The regression is deliberately simple, because the value is in the residuals rather than in the model.

python
from __future__ import annotations

import numpy as np
import pandas as pd
import statsmodels.api as sm


def calibrate(panel: pd.DataFrame, pos: pd.DataFrame,
              stores: pd.DataFrame, min_months: int = 6) -> pd.DataFrame:
    """Log-log fit of transactions on corrected panel visits, per format."""
    df = (panel.merge(pos, on=["store_id", "month"])
               .merge(stores[["store_id", "format"]], on="store_id"))

    counts = df.groupby("store_id")["month"].nunique()
    df = df.loc[df["store_id"].isin(counts[counts >= min_months].index)]

    out = []
    for fmt, group in df.groupby("format"):
        # Log-log: errors here are proportional, not additive, so a linear fit
        # on raw counts would be dominated by the largest stores.
        x = np.log(group["panel_visits"].clip(lower=1))
        y = np.log(group["transactions"].clip(lower=1))
        model = sm.OLS(y, sm.add_constant(x)).fit()
        group = group.assign(
            fitted=np.exp(model.predict(sm.add_constant(x))),
            residual_log=y - model.predict(sm.add_constant(x)),
            format_slope=model.params.iloc[1],
            format_scale=float(np.exp(model.params.iloc[0])),
        )
        out.append(group)

    result = pd.concat(out, ignore_index=True)
    robust_sd = 1.4826 * result["residual_log"].abs().median()
    result["outlier"] = result["residual_log"].abs() > 3 * robust_sd
    return result


def factor_table(calibrated: pd.DataFrame) -> pd.DataFrame:
    """One conversion factor per format, with the fit quality beside it."""
    return (calibrated.groupby("format")
            .agg(stores=("store_id", "nunique"),
                 slope=("format_slope", "first"),
                 scale=("format_scale", "first"),
                 mae_pct=("residual_log", lambda r: float(np.abs(np.expm1(r)).mean() * 100)))
            .round(3).reset_index())

A slope near one is the property to check first. It says the panel and the transaction data agree about relative differences between stores, which is the claim the calibration rests on. A slope materially below one means the panel compresses differences — usually a sign that place polygons are picking up neighbouring footfall at the smaller stores — and no scale factor fixes that.

Failure modes and debugging

Comparing against revenue. The single most common error. Revenue mixes visit volume with basket size, so a store with high spend per visit appears to have more traffic than the panel sees, and the residuals then organise by affluence rather than by anything to do with the panel.

Including online orders in the transaction count. Click-and-collect and delivery baskets are transactions with no corresponding footfall, and their share varies hugely between stores. Excluding them is essential; where the data does not permit it, the affected stores should be excluded from the calibration rather than left to distort it.

Fitting one global factor. Formats convert differently, and a single factor splits the difference badly. The signal that this is happening is residuals that cluster tightly by format — which is also the fix.

Reading outliers as noise. In practice almost every large residual has an explanation: a shared vendor place, a store with a car park the polygon includes, a location whose opening hours changed. Investigating them is the most productive part of this exercise, and discarding them silently throws away the findings.

The residuals are a defect list, not noise Of fourteen stores flagged as outliers, six sit inside shopping centres whose vendor place includes other tenants, three have place polygons covering an adjacent car park, two changed opening hours mid-period, two are new stores with less than a full year of data, and one is a genuine panel coverage gap. Thirteen of fourteen outliers had a nameable cause in-centre place includes tenants 6 polygon includes a car park 3 opening hours changed 2 new store, partial history 2 genuine coverage gap 1 — the only one that is about the panel Nine of the fourteen are place-matching problems, which means the calibration is doubling as a quality check on the join — a second return on the same work.

Verification

  • Check the slope, then the scale. A good scale factor on a bad slope is a coincidence that will not hold at other store sizes.
  • Hold out a set of stores from the fit and predict their transactions. Held-out error is the honest measure of what the calibration will do on stores that were not part of it — which includes every candidate site.
  • Re-fit quarterly and track the factor. A drifting factor is a drifting panel, and it means level comparisons across the drift need care.
  • Confirm the residuals have no structure by market density. If they do, the correction stage has under-adjusted and the calibration is absorbing the remainder.
The factor moves, so it has to be refreshed The supermarket conversion factor is 26.1 transactions per estimated panel visit in the first quarter, 25.4 in the second, 27.8 in the third and 31.2 in the fourth — a fifth higher across the year, tracking a known contraction in the vendor's panel rather than any change in the stores. A factor fixed once is a bias that grows all year 26.1 Q1 25.4 Q2 27.8 Q3 31.2 Q4 The stores did not change. The panel shrank, so each observed visit now represents more real ones — which is exactly what a rising factor should say, and exactly what a fixed factor would hide.

Frequently Asked Questions

What if only some stores have usable transaction data?

Calibrate on those and apply the factor to the rest, with the coverage stated. That is the normal situation — franchised locations, concessions and recently acquired stores often lack comparable transaction feeds — and it is perfectly workable provided the calibration sample resembles the estate. A factor fitted entirely on large suburban stores and applied to city-centre convenience is the failure to avoid, which is another reason the fit is per format.

Can door counters be used instead of transactions?

Yes, and they are in some ways better: a door count measures visits directly, which is what the panel measures, without the conversion step. The catch is that door counters count staff, deliveries and re-entries, so they need their own cleaning before they can anchor anything. Where both exist, calibrating against door counts and separately checking the visit-to-transaction conversion is the most informative arrangement.

Should the calibration factor be applied to candidate sites?

Yes — that is its purpose. A candidate site has no transactions, so the panel estimate for whatever operates nearby, multiplied by the format factor, is the analogue-based demand estimate that feeds the suitability score. What must travel with it is the held-out error, since applying a factor fitted on existing stores to a site that does not yet exist is exactly the extrapolation the held-out figure measures.

How large should the calibration sample be?

Enough stores per format that the slope is well determined — typically thirty or more per format, over at least six months. Below that the fit is dominated by a handful of stores and the factor moves with them. Where a format genuinely has fewer stores, borrowing the nearest format’s slope while fitting only the scale is a reasonable compromise, recorded as such.

What does a poor calibration actually tell you?

Usually that something upstream is wrong, not that the panel is unusable. A low R-squared with well-behaved residuals suggests noise — often too few months, or stores too small for the panel to see reliably. A high R-squared with structured residuals points at place matching, since the structure almost always organises by store type or centre membership. And a slope far from one points at the correction stage. Reading which of the three is happening turns a disappointing fit into a specific piece of work.

Should the calibration factor be applied to competitor estimates?

With more caution, and the same factor is usually the best available choice. You have no transactions for a competitor, so their panel-derived visits can only be scaled by a factor fitted on your own stores of a comparable format — which assumes their conversion behaves like yours. That assumption is reasonable within a format and poor across formats, so a competitor estimate should carry a wider band than an own-store one and should be used for relative comparison rather than as a revenue figure.

How does this calibration relate to backtesting a site score?

It is the same idea one stage earlier. Calibration anchors an input — panel visits — to something measured, while backtesting anchors the output, the score itself, to realised sales. A pipeline with both has two independent checks against reality, and when a forecast misses it can tell whether the input estimate or the model turned out to be wrong, which is a question that is otherwise unanswerable.

← Back to Mobility & Foot Traffic Data Integration