Price Elasticity From Observational Data

In the Real World · Brief · Machine learning · Core · 1 to 2 days

Someone wants to raise prices ten per cent and nobody knows what it does to volume. Build the simulator. Work it yourself before reading the walkthrough.

The situation

Brackwell Home sells homeware. You have 104 weeks of weekly price and volume for 20 products, 2024-07-29 to 2026-07-20. Prices have moved plenty over that period, through promotions, clearance and competitive response.

The commercial director wants a simulator: move the price, see the volume, see the margin.

The data

What the room believes

1. A log-log regression of units on price gives the elasticity.
2. Two years of real price variation is enough to measure it.
3. Adding the right controls will get you to the true answer.
4. An estimate that is close enough for a report is close enough for a pricing decision.

Definition of done

1. A verdict on each of the four beliefs, with the evidence.
2. An elasticity per product, with a defence of the specification you chose.
3. An explicit list of the reasons price moved in this data, and what each does to a naive estimate.
4. A working simulator: price in, volume and contribution out.
5. A clear statement of which decisions your estimate can support and which it cannot.

If you want to go further

• Compare products against each other, then compare a product against itself over time, and explain why the two give different answers.
• Work out which columns in the file you must not control for, and say why.
• Take your elasticity into a contribution calculation and see how much the recommendation moves for a small change in the estimate.
• Decide what evidence would let you recommend a price change with confidence, and whether this file can ever provide it.

In the Real World · Machine learning · Core · 1 to 2 days

Someone wants to raise prices ten per cent and nobody knows what it does to volume. A regression of units on price answers in one line and gets it wrong by 45.0%. Fixing most of that still leaves the wrong pricing decision.

Section 1Problem Definition

No code yet. Elasticity is the most requested and most quietly mis-estimated number in retail analytics, and the reason has nothing to do with the model.

The problem in one sentence

Price is not set at random. It moves for reasons that also move demand, so the relationship between price and volume in a historical file is not the causal effect of changing price.

Business objective

Estimate the own-price elasticity of each product well enough to build a simulator the commercial team can use, and say plainly where the estimate is not good enough to decide on.

Hypotheses

1. H1. A log-log regression of units on price gives the elasticity.
2. H2. Two years of real price variation is enough to measure it.
3. H3. Adding the right controls will get us to the true answer.
4. H4. An estimate that is close enough for a report is close enough for a pricing decision.

Section 2Data Collection

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

d = pd.read_csv('data/brackwell-pricing-panel.csv')
d['lp'] = np.log(d['price'])
d['lq'] = np.log(d['units_sold'].clip(lower=1))

print('rows     :', len(d))
print('products :', d['sku'].nunique(), ' weeks:', d['week_index'].nunique())
print('promo weeks     : %.1f%%' % (100 * d['on_promotion'].mean()))
print('clearance weeks : %.1f%%' % (100 * d['clearance'].mean()))

rows     : 2,080
products : 20  weeks: 104
promo weeks     : 8.8%
clearance weeks : 1.6%

Section 3Data Preprocessing

3aDuplicates and schema checks

print('duplicate rows        :', d.duplicated().sum())
print('duplicate sku weeks   :', d.duplicated(subset=['sku', 'week_index']).sum())
print('missing cells         :', d.isna().sum().sum())
print('zero or negative price:', (d['price'] <= 0).sum())
print('mean price range per product : %.2fx'
      % (d.groupby('sku')['price'].max() / d.groupby('sku')['price'].min()).mean())

duplicate rows        : 0
duplicate sku weeks   : 0
missing cells         : 0
zero or negative price: 0
mean price range per product : 1.57x

Every product’s price moves by a factor of about 1.57 across the window. That is genuine variation, and section 7 shows that having variation is necessary and nowhere near sufficient.

3bHandling categorical mess

Clean. Category is not used as a feature: with 20 products the model uses a dummy per product instead, which absorbs category and everything else that is fixed about a line.

3cDealing with outliers

Nothing extreme, and the deep discounts are the observations that carry most of the information about elasticity. Trimming them would remove the signal and leave the confounding intact, which is the worst of both.

3dHandling missing values

missing cells : 0

3eHandling skewed data

Both price and units are logged, which is not a cosmetic choice. The coefficient on log price in a regression of log units is the elasticity, so the transform is the model rather than a preparation step.

3fData types and normalisation

d['woy'] = pd.to_datetime(d['week_start']).dt.isocalendar().week.astype(int)
for k in (1, 2):
    d['sin' + str(k)] = np.sin(2 * np.pi * k * d['woy'] / 52.0)
    d['cos' + str(k)] = np.cos(2 * np.pi * k * d['woy'] / 52.0)

dummies = pd.get_dummies(d['sku'], prefix='sku', drop_first=True).astype(float)
season  = d[['sin1', 'cos1', 'sin2', 'cos2']]
print('product dummies :', dummies.shape[1])

product dummies : 19

Section 4Exploratory Data Analysis

4aTarget variable analysis

The target is log units and the quantity of interest is one coefficient. Everything below is about whether that coefficient means what it appears to mean.

4bNumerical variables

Start with the cross section, because it is where the first confound lives.

bysku = d.groupby('sku').agg(price=('price', 'mean'), units=('units_sold', 'mean'))
slope = np.polyfit(np.log(bysku['price']), np.log(bysku['units']), 1)[0]
print('across products, log units against log price')
print('  correlation :', round(np.corrcoef(np.log(bysku['price']),
                                           np.log(bysku['units']))[0, 1], 4))
print('  slope       :', round(slope, 3))

across products, log units against log price
  correlation : -0.9595
  slope       : -1.197

A slope of -1.197 across products, which looks like an elasticity and is not one. Dearer products sell in smaller numbers because they are different products, not because anyone changed a price. Any pooled regression is partly fitting this line.

4cCategorical variables

d['discount'] = 1 - d['price'] / d['list_price']
state = np.where(d['clearance'] == 1, 'clearance',
                 np.where(d['on_promotion'] == 1, 'promotion', 'normal'))
print(d.groupby(state)[['units_sold', 'discount']].mean().round(3))

           units_sold  discount
clearance     184.529     0.218
normal        106.106     0.001
promotion     191.033     0.180

Promotions and clearance both cut the price by roughly a fifth and both sell well, so this table cannot separate them. The units column looks the same in a week priced down into strong demand and a week priced down because the line is finished.

# The one thing that does separate them: the direction the line is already travelling.
trend = (d.sort_values('week_index').groupby('sku')
         .apply(lambda g: np.polyfit(g['week_index'],
                                     np.log(g['units_sold'].clip(lower=1)), 1)[0]))
cleared = sorted(d.loc[d['clearance'] == 1, 'sku'].unique())
print('lines ever cleared     :', len(cleared))
print('weekly log-units trend, cleared lines :', round(trend.loc[cleared].mean(), 5))
print('weekly log-units trend, other lines   :', round(trend.drop(index=cleared).mean(), 5))

lines ever cleared     : 6
weekly log-units trend, cleared lines : -0.00356
weekly log-units trend, other lines   : 0.0015

The 6 lines that were ever put into clearance are shrinking at -0.00356 log units a week while the rest are growing. The discount is deep enough to make those weeks look strong anyway. A pooled regression sees a low price beside high units and reads it as weak price sensitivity, when the real story is a dying line being emptied out.

4dRelationships between variables

print('pooled, price and competitor index :',
      round(d['price'].corr(d['competitor_index']), 4))
print('pooled, competitor index and units :',
      round(d['competitor_index'].corr(d['units_sold']), 4))

# The same two correlations computed inside each product, then averaged.
within = d.groupby('sku').apply(
    lambda g: pd.Series({'price': g['price'].corr(g['competitor_index']),
                         'units': g['units_sold'].corr(g['competitor_index'])}))
print('within product, price and competitor index :', round(within['price'].mean(), 4))
print('within product, units and competitor index :', round(within['units'].mean(), 4))

pooled, price and competitor index : 0.0745
pooled, competitor index and units : -0.1956
within product, price and competitor index : 0.6542
within product, units and competitor index : -0.3877

Pooled, the competitor index looks like a variable worth ignoring at 0.0745 against price. That number is small because it is dominated by the gap between a 20 product price list, not because the effect is small. Inside a single product it is 0.6542 against price and -0.3877 against units, which is a textbook omitted variable: it pushes both sides of the regression at once.

The competitor index moves our price and our volume at the same time. It is in the file, so it can be controlled for. The equivalent variables that are not in the file are the reason section 7d stops short of recommending a price change.

4eTesting our hypotheses

4fSubgroups

Elasticity genuinely differs by product, from -3.1 to -1.3. A single number for the range would be wrong for almost every line in it, which is why section 7d estimates each product separately even though each has only 104 observations.

Section 5Feature Engineering

5aThe leakage trap

There is no target leak here in the usual sense. The equivalent failure is a control that sits between price and demand rather than beside them.

5bNew features

Four Fourier terms and a set of product dummies. Nothing clever, because the risk here is not underfitting, it is controlling for the wrong thing.

5cEncoding

Product identity as dummies, which is what turns a pooled regression into a within-product one. That single change does most of the work in section 7b.

5dFeature selection

Fixed in advance from a causal argument rather than from fit. Adding a variable because it improves R squared is exactly how the previous-week-volume mistake gets made: it improves fit and destroys the estimate.

Section 6Model Selection

Section 7Model Training

7aBaselines

The baseline is the one-line answer someone will produce if this project does not: a pooled regression of log units on log price.

7bComparing candidates

def elasticity(X, label):
    m = sm.OLS(d['lq'], sm.add_constant(X)).fit()
    lo, hi = m.conf_int().loc['lp']
    print(f'{label:44s} {m.params["lp"]:+.3f}  [{lo:+.3f}, {hi:+.3f}]  R2 {m.rsquared:.4f}')
    return m.params['lp']


elasticity(d[['lp']], 'Naive pooled')
elasticity(pd.concat([d[['lp']], dummies], axis=1), 'Product fixed effects')
elasticity(pd.concat([d[['lp']], dummies, season], axis=1),
           'Fixed effects plus seasonality')
elasticity(pd.concat([d[['lp']], dummies, season, d[['competitor_index']]], axis=1),
           'Fixed effects, seasonality and competitor')

Naive pooled                                 -1.229  [-1.259, -1.199]  R2 0.7550
Product fixed effects                        -2.880  [-3.070, -2.691]  R2 0.8417
Fixed effects plus seasonality               -2.885  [-3.075, -2.695]  R2 0.8423
Fixed effects, seasonality and competitor    -2.605  [-2.845, -2.366]  R2 0.8433

The naive estimate is too small by 1.006. Product fixed effects overshoot in the other direction. Adding the competitor index leaves 0.37.

7cHyperparameter tuning

There is nothing to tune, which is the point. The gap between the naive and the controlled estimate is entirely a modelling-design decision, taken before any fitting, and no amount of tuning moves it.

7dFinal evaluation

Per product, scored against the truth

This is the step no real pricing project gets to take. The panel is simulated, so the true elasticity of every product is known and every estimate can be marked.

# The ground truth, from the generator. It is not in the exported file, and on a
# real dataset this dictionary does not exist, which is the whole difficulty.
TRUE = {'BW-101': -1.9, 'BW-102': -2.4, 'BW-103': -1.4, 'BW-104': -2.8,
        'BW-105': -1.6, 'BW-106': -2.1, 'BW-107': -2.6, 'BW-108': -3.1,
        'BW-109': -2.9, 'BW-110': -1.5, 'BW-111': -2.7, 'BW-112': -1.3,
        'BW-113': -3.0, 'BW-114': -1.8, 'BW-115': -2.2, 'BW-116': -2.5,
        'BW-117': -2.9, 'BW-118': -2.3, 'BW-119': -1.7, 'BW-120': -2.0}

for sku, g in d.groupby('sku'):
    naive = sm.OLS(g['lq'], sm.add_constant(g[['lp']])).fit().params['lp']
    ctrl  = sm.OLS(g['lq'], sm.add_constant(
        pd.concat([g[['lp']], g[['sin1', 'cos1', 'sin2', 'cos2',
                                 'competitor_index']]], axis=1))).fit().params['lp']
    print(f'{sku}  naive {naive:+.2f}   controlled {ctrl:+.2f}   true {TRUE[sku]:+.2f}')

BW-101  naive -2.30   controlled -1.43   true -1.90
BW-102  naive -3.26   controlled -2.37   true -2.40
BW-103  naive -1.68   controlled -0.78   true -1.40
BW-104  naive -3.36   controlled -2.93   true -2.80
BW-105  naive -3.19   controlled -1.64   true -1.60
BW-106  naive -3.17   controlled -1.95   true -2.10
BW-107  naive -3.56   controlled -2.52   true -2.60
BW-108  naive -3.11   controlled -3.27   true -3.10
BW-109  naive -2.96   controlled -2.26   true -2.90
BW-110  naive -2.69   controlled -1.65   true -1.50
BW-111  naive -3.34   controlled -2.90   true -2.70
BW-112  naive -2.07   controlled -1.49   true -1.30
BW-113  naive -3.55   controlled -2.76   true -3.00
BW-114  naive -2.56   controlled -2.78   true -1.80
BW-115  naive -2.40   controlled -1.59   true -2.20
BW-116  naive -4.33   controlled -2.75   true -2.50
BW-117  naive -3.76   controlled -2.85   true -2.90
BW-118  naive -2.72   controlled -2.33   true -2.30
BW-119  naive -1.43   controlled -1.68   true -1.70
BW-120  naive -2.64   controlled -2.18   true -2.00

Controls help on almost every line. Mean absolute error falls from 0.697 to 0.262.

The simulator, and the decision it gets wrong

SKU, COST_SHARE = 'BW-104', 0.55
g = d[d['sku'] == SKU]
list_price = g['list_price'].iloc[0]
base_units = g.loc[g['price'] >= list_price * 0.99, 'units_sold'].mean()

# the controlled estimate for this one product, which is what a pricing team
# would actually have in front of them
est_elasticity = sm.OLS(g['lq'], sm.add_constant(
    pd.concat([g[['lp']], g[['sin1', 'cos1', 'sin2', 'cos2',
                             'competitor_index']]], axis=1))).fit().params['lp']
print('estimated elasticity', round(est_elasticity, 3),
      '  true', TRUE[SKU])

for change in (-0.20, -0.10, -0.05, 0.0, 0.05, 0.10, 0.20):
    newp = list_price * (1 + change)
    for label, e in (('estimated', est_elasticity), ('true', TRUE[SKU])):
        units = base_units * (newp / list_price) ** e
        contrib = units * (newp - list_price * COST_SHARE)
        print(f'{change:+.0%}  {label:9s} units {units:7.1f}  contribution {contrib:9,.0f}')

estimated elasticity -2.93   true -2.8
-20%  estimated units   359.4  contribution     2,516
-20%  true      units   349.1  contribution     2,444
-10%  estimated units   254.5  contribution     2,494
-10%  true      units   251.0  contribution     2,460
-5%  estimated units   217.2  contribution     2,433
-5%  true      units   215.8  contribution     2,417
+0%  estimated units   186.9  contribution     2,355
+0%  true      units   186.9  contribution     2,355
+5%  estimated units   162.0  contribution     2,268
+5%  true      units   163.0  contribution     2,283
+10%  estimated units   141.4  contribution     2,177
+10%  true      units   143.1  contribution     2,204
+20%  estimated units   109.6  contribution     1,994
+20%  true      units   112.2  contribution     2,042

The two curves peak in different places. The estimate says cut 20%, the truth says 10%.

Section 8Documentation and Handoff

What to do, and who owns it

What not to do

• Do not report the pooled elasticity. It is wrong by 45.0% here and it is the number a one-line regression produces.
• Do not read a narrow confidence interval as accuracy. Every interval in section 7b is tight and most of them exclude the truth. Standard errors measure noise, not bias.
• Do not reach for a machine learning model. It would fit better and identify nothing, because the question is causal.
• Do not price on the simulator alone above five per cent. The worked example shows the best available estimate recommending twice the discount the truth supports.
• Do not use one elasticity for the range. The true values span -3.1 to -1.3.

Reproducibility

What to take from this

• Price is not randomly assigned, so the price and volume relationship in history is not the effect of changing price. Everything else follows from that one sentence.
• Product fixed effects are the cheapest large correction there is. One dummy per line moved the estimate from -1.229 to -2.88.
• Control for causes of price, never for consequences. The tempting controls are usually the second kind.
• A tight confidence interval is not accuracy. It describes sampling noise and is silent about the thing that is actually wrong.
• Good enough to report is not the same as good enough to decide. The controlled estimate here is close and still picks the wrong price.
• When the decision is expensive, test rather than model. A staggered price rollout answers in weeks what no amount of historical data can.

The commercial director asked for a simulator. The simulator exists, it is far better than the one-line answer, and the most valuable thing in the handoff is the sentence saying which decisions it is not accurate enough to make. That sentence is only writable because this dataset knows the truth, and the habit it should leave behind is assuming the same gap exists on data that does not.