Hiprup

How do you choose a baseline or benchmark for evaluating performance?

A number on its own carries no verdict. £420k in November is only good or bad relative to something, and the baseline you pick decides the story — which is precisely why it must be chosen deliberately and stated out loud.

  • Prior period — simple and current, good for momentum, but badly misleading in any seasonal business where a December-to-January fall is normal rather than a problem.

  • Same period last year — controls for seasonality and is the default for retail and anything with an annual cycle; the weakness is that it silently assumes last year was itself a normal comparison.

  • Plan, budget or target — the right baseline for accountability because it measures against a commitment, though it says nothing about whether the plan was realistic.

  • Trailing average or moving window — smooths one-off spikes and short-term noise, useful for volatile daily series where any single prior period is unstable.

  • Control group or external benchmark — the only baselines that support a causal claim; without a holdout or a market rate, a rise after a launch may just be the whole market rising.

Key terms: baseline, benchmark, year-over-year, period-over-period, seasonality, moving average, control group, counterfactual, like-for-like

-- Three baselines for the SAME monthly number, side by side.
WITH monthly AS (
    SELECT DATE_TRUNC('month', order_date) AS mth,
           SUM(net_amount)                 AS revenue
    FROM   fact_orders
    WHERE  order_status = 'completed'
    GROUP  BY 1
)
SELECT
    mth,
    revenue,

    -- 1. PRIOR PERIOD: momentum, but contaminated by seasonality
    LAG(revenue, 1) OVER (ORDER BY mth)                          AS prior_month,
    ROUND(100.0 * (revenue - LAG(revenue, 1) OVER (ORDER BY mth))
          / NULLIF(LAG(revenue, 1) OVER (ORDER BY mth), 0), 1)   AS pct_vs_prior_month,

    -- 2. SAME PERIOD LAST YEAR: controls for seasonality
    LAG(revenue, 12) OVER (ORDER BY mth)                         AS same_month_last_year,
    ROUND(100.0 * (revenue - LAG(revenue, 12) OVER (ORDER BY mth))
          / NULLIF(LAG(revenue, 12) OVER (ORDER BY mth), 0), 1)  AS pct_vs_last_year,

    -- 3. TRAILING 12-MONTH AVERAGE: smooths one-off spikes
    ROUND(AVG(revenue) OVER (ORDER BY mth
              ROWS BETWEEN 12 PRECEDING AND 1 PRECEDING), 0)     AS trailing_12m_avg
FROM   monthly
ORDER  BY mth DESC;

-- Typical January row:  pct_vs_prior_month = -22%   (looks alarming)
--                       pct_vs_last_year   =  +6%   (actually a good month)
-- Same revenue, opposite conclusions -- which is why the baseline must be stated.


-- Like-for-like guard: strip out stores that did not trade in BOTH periods,
-- otherwise a new store opening is reported as organic growth.
SELECT s.store_id, SUM(f.net_amount) AS revenue
FROM   fact_orders f
JOIN   dim_store   s ON s.store_id = f.store_id
WHERE  s.opened_date <= DATEADD(year, -1, CURRENT_DATE)   -- open for the whole comparison
  AND  s.closed_date IS NULL
GROUP  BY s.store_id;

The single query computes three different verdicts on the same monthly revenue so the choice of baseline becomes visible rather than implicit. LAG with an offset of 1 gives period-over-period momentum, LAG with an offset of 12 gives the same month a year earlier and therefore neutralises seasonality, and the windowed AVG over the preceding twelve months smooths out one-off spikes that would distort either single comparison. The commented January row is the whole point: the identical revenue figure reads as a 22% collapse against December and a 6% improvement against last January, so an analyst who reports a percentage without naming its baseline has not finished the analysis.

NULLIF guards every denominator so a zero or missing prior period yields NULL rather than a division error. The final query handles like-for-like comparability by restricting to stores that traded across the entire comparison window, which stops a newly opened store from being counted as organic growth.

Do not name one baseline as correct — the answer interviewers want is that it depends on the question, followed by the specific mapping (momentum, seasonality, accountability, causation). The strongest single point is the seasonality trap, ideally with a concrete example such as a January drop that is entirely normal, because it shows you have been in a real reporting cycle.

Mention like-for-like adjustments (trading days, acquisitions, new stores) since that is a genuinely senior consideration. Expect the follow-up "how do you know the improvement came from your change?" — that is asking for a control group or holdout, and saying so cleanly separates you from candidates who only think in period comparisons.