Multiple regression of sales: demand drivers, advertising and promo contribution, multicollinearity, lags and model checks
How to estimate the contribution of price, promo, advertising, weather and traffic to sales with one model without crediting an effect to what merely coincided in time: build a regression in logarithms, allow for the delayed action of advertising, check collinearity and residuals and decompose sales into factor contributions (a simplified marketing mix model). The decision: where to put the budget and which elasticity to build into the plan.
Why this document
The head of marketing and the commercial analyst read this to understand what really moves sales: price, promotion, advertising, weather or traffic. Effects often coincide in time, for instance a promotion runs in season, so a simple comparison credits the wrong cause. Regression separates the contributions, and the multicollinearity check shows where factors cannot be told apart. Lags account for advertising acting with a delay. Without this, budget goes to whatever merely coincided with growth.
At a glance
- What is this document and why is it needed?
- How to estimate the contribution of price, promo, advertising, weather and traffic to sales with one model without crediting an effect to what merely coincided in time: build a regression in logarithms, allow for the delayed action of advertising, check collinearity and residuals and decompose sales into factor contributions (a simplified marketing mix model). The decision: where to put the budget and which elasticity to build into the plan.
- Who owns the document and how often is it rebuilt?
- A sales or marketing analyst; the model is recalculated quarterly and after major changes in prices, advertising or channels; read by the commercial director, marketing and finance.
- What mistakes are the most common?
- A regression of sales on price without season and promo → elasticity biased several-fold → add factors or use log-log with controls. Dozens of factors on a year of data → the model memorizes noise → no more than one factor per 10-15 observations and a hold-out window. Each coefficient is read separately at VIF above 10 → false conclusions that advertising “does not work” → merge factors or check by experiment.
For whom
- Brand
- A brand models D2C sales and partner sell-out against price, advertising, collaborations and season; the advertising budget and price policy are checked on the model before launch.
- Retail
- A retailer models store or category sales against traffic, promotions, weather and advertising; the model cleans elasticity and the promo effect of season and traffic.
What it consists of
Why multiple regression
- Sales depend on many factors at once, and a pairwise regression of sales on price credits price with everything that coincided with it in time. In the example over 104 weeks the naive elasticity estimate is -2.97, the model with promo and weather -1.99, the full model -1.74 against a built-in -1.7: every omitted factor biased the estimate.
- A model in logarithms: ln(sales) is linear in ln(price), ln(traffic), a promo flag, temperature and an advertising effect. The coefficient on ln(price) is the elasticity, on ln(traffic) the traffic elasticity, on the promo flag roughly the percentage gain (e^β − 1).
- Regression shows an association holding other factors fixed, not causation: a causal conclusion comes from an experiment (A/B, control weeks), so the model is calibrated against test results and does not replace them.
Factor set and data preparation
- A weekly grid by store or category of at least 2 years (104 weeks): fewer than 10-15 observations per factor is not acceptable. Sales are cleaned of out-of-stock days, factors are taken for the same week: price (average per receipt), discount share, a promo flag, visitors, temperature, advertising spend.
- Seasonality enters as factors (temperature, holiday weeks), not subtracted beforehand; then elasticity is cleaned of season. Traffic enters as a factor if it is not created by advertising itself; otherwise the advertising contribution is understated (traffic takes the effect).
- Hold-out: the last 13 weeks take no part in fitting. In the example the full model errs by 5.9% WAPE on them, the short one without traffic and advertising by 8.2%.
- Brand: for D2C a weekly grid by channel gives few observations per channel; channel factors are merged into groups (performance, image) or the model moves to daily data.
- Retailer: the model is built as a store panel with store fixed effects: this increases the number of observations and separates the promotion effect from differences between points.
Advertising lags and multicollinearity
- Advertising acts not only in the week of exposure: the effect accumulates as A_t + λ·A_(t−1) + λ²·A_(t−2) + … (adstock). The parameter λ is picked by a grid minimizing the residual sum of squares; in the example the best λ = 0.5 and the advertising coefficient is 1.27·10⁻³ with a standard error of 0.35·10⁻³.
- Multicollinearity: promo, discount and advertising move together (in the example the correlation of promo and advertising spend is 0.95, of promo and ln price -0.96). The variance inflation factor VIF = 1/(1 − R²_j) is 14.8 for promo and 13 for price; values above 10 signal unstable coefficients.
- Consequence: the coefficient on promo is 0.119 with a standard error of 0.062 and t = 1.9, the confidence interval includes zero. Model predictions remain good; what is weakly determined is the contribution of each factor separately. Remedies: merge factors, separate them in time by an experiment or use more informative priors.
Model checks and factor contribution
- R² = 0.962, adjusted R² = 0.96: adding a factor always raises R², while the adjusted one penalizes useless factors. Residuals are checked for autocorrelation (Durbin-Watson 1.99, values near 2 are normal), for constant spread and for the absence of structure in the residual-versus-fit plot.
- Factor contribution (simplified MMM): average sales of 1,053 units a week decompose into a base without price, promo and advertising of 795 units and the contributions of price, promo and advertising. The estimates are approximate: the stronger the collinearity the less determined the split between promo and advertising.
- Budget conclusions are drawn from the marginal return: the advertising effect saturates, so the sales gain from the next rouble is lower than the average; a regression on the historical range does not know what lies beyond it.
Brand and retailer: what is modeled and how it is read
- Brand: the dependent variable is D2C or partner sell-out, not shipments; shipments are distorted by buy-in and order seasonality and give false coefficients.
- Retailer: the dependent variable is store or category sales per week; traffic enters as a factor and elasticity is cleaned of footfall.
- Brand: advertising spend by channel (performance, bloggers, outdoor) enters as separate factors with different λ; price reflects the price policy across channels.
- Retailer: promo enters as a flag of promotion type and depth, weather as temperature and precipitation; the post-promotion dip is modeled by a promo lag on following weeks.
- Brand: the result goes into the marketing budget and the season demand forecast: prices and scenario budgets are put into the model before approval.
- Retailer: the result goes into the promo calendar and purchasing planning: how much stock to allow for a promotion and which discount depth to choose.
Where the data comes from
| Data source | Fields |
|---|---|
| Weekly sales and prices | Sales in units and roubles, average price per receipt, discount share, promo flag and type; by store or category; at least 104 weeks. |
| Traffic and weather | Visitors from counters or receipts, regional temperature and precipitation, holiday weeks. |
| Marketing | Advertising spend by week and channel, exposure period, reach; lags need the exact week of spend. |
How to build it step by step
- Step 1. State the question (elasticity, advertising contribution, promo effect) and choose the dependent variable: weekly unit sales.
- Brand: Step 2. Assemble sell-out by channel and spend by advertising channel; do not take partner shipments into the sales model.
- Retailer: Step 3. Assemble store sales, traffic, promotions and weather for the same week; drop weeks of closure and refit.
- Step 4. Take logarithms of sales, price and traffic; check that the number of weeks is at least 10-15 times the number of factors.
- Step 5. Pick the advertising λ by a grid (0-0.9) on the residual sum of squares; a separate λ for each channel.
- Step 6. Estimate the model, write out coefficients, standard errors, t and 95% intervals; check signs: price negative, traffic positive.
- Step 7. Compute VIF and factor correlations; at VIF above 10 merge factors or do not interpret them separately.
- Step 8. Check residuals (autocorrelation, spread, structure) and the hold-out error; a model that does not beat seasonal naive is not used.
- Brand: Step 9. Decompose sales into contributions and compare channel returns at the marginal rouble; take the budget decision with a check test (a region without advertising).
- Retailer: Step 10. Put the discount depth and promotion traffic into the model, get a sales forecast and compare it with promotion cost; reconcile with control stores.
Formulas
| Measure | Formulas | Note |
|---|---|---|
| OLS estimate | β̂ = (XᵀX)⁻¹·Xᵀ·y, ŷ = X·β̂, e = y − ŷ | X includes a column of ones; with near-linear dependence of columns XᵀX is badly invertible. |
| Errors and intervals | s² = Σe²/(n − k); SE(β̂_j) = √( s²·[(XᵀX)⁻¹]_jj ); β̂_j ± t_(0.975; n−k)·SE | k is the number of coefficients including the intercept; sample estimate, divisor n − k. |
| Adjusted R² | R²_adj = 1 − (1 − R²)·(n − 1)/(n − k) | Compares models with different numbers of factors. |
| Variance inflation factor | VIF_j = 1 / (1 − R²_j), R²_j from regressing factor j on the others | Above 10 is an alarm; the coefficient variance grows by a factor of VIF. |
| Advertising adstock | Ad_t = A_t + λ·Ad_(t−1), half-life = ln 0.5 / ln λ | λ between 0 and 1; close to 1 means long-lasting advertising. |
| Durbin-Watson | DW = Σ_(t≥2)(e_t − e_(t−1))² / Σ e_t² | Near 2 means no residual autocorrelation; below 1.5 or above 2.5 needs checking and a correction of standard errors. |
| Factor contribution | C_j = mean( ŷ(all factors) ) − mean( ŷ(factor j at base level) ) | Base level: price 1, promo 0, advertising 0; contributions are computed sequentially, the order affects the split. |
Formulas in notation
- Log model
ln y_t = β₀ + β₁·ln Traffic_t + β₂·ln P_t + β₃·Promo_t + β₄·Temp_t + β₅·Ad_t + ε_t
β₂ is the price elasticity; β₃ is roughly the percentage promo effect.- OLS
β̂ = (XᵀX)⁻¹ Xᵀ y
Minimizes the sum of squared residuals.- Standard error
SE(β̂_j) = √( s²·[(XᵀX)⁻¹]_{jj} ), s² = Σe² / (n − k)
Divisor n − k.- VIF
VIF_j = 1 / (1 − R²_j)
Above 10 is a collinearity alarm.- Adstock
Ad_t = A_t + λ·Ad_{t−1}
Cumulative advertising effect decaying at λ.
Worked example
Full model coefficients: estimate, standard error, t, 95% interval and the built-in value
| Factor | Estimate | Std. error | t | Lower 95% | Upper 95% | Built-in |
|---|---|---|---|---|---|---|
| Intercept | 4.64 | 0.45 | 10.4 | 3.76 | 5.53 | 4.51 |
| ln traffic | 0.86 | 0.17 | 5.1 | 0.52 | 1.19 | 0.9 |
| ln price | −1.74 | 0.31 | −5.7 | −2.36 | −1.13 | −1.7 |
| Promo | 0.12 | 0.06 | 1.9 | −0 | 0.24 | 0.08 |
| Temperature, °C | −0.01 | 0 | −4.2 | −0.02 | −0.01 | −0.01 |
| Advertising (lagged effect) | 0 | 0 | 3.6 | 0 | 0 | 0 |
104 weeks are generated in code (fixed seed): training 91, hold-out 13. R² = 0.962, advertising λ 0.5. All built-in values fall inside the 95% intervals; the promo interval includes zero because of collinearity with price and advertising. The intercept is shifted by ln 1000 because of the sales scale. The data is illustrative.
Figures
- Actual
- Model
Data as a table
| Measure | Actual | Model |
|---|---|---|
| 1 | 1,025 | 1,132 |
| 2 | 1,241 | 1,147 |
| 3 | 1,103 | 1,070 |
| 4 | 1,198 | 1,082 |
| 5 | 1,166 | 1,124 |
| 6 | 1,009 | 1,127 |
| 7 | 1,130 | 1,077 |
| 8 | 1,125 | 1,058 |
| 9 | 2,292 | 2,112 |
| 10 | 1,173 | 1,023 |
| 11 | 1,023 | 965 |
| 12 | 1,046 | 954 |
| 13 | 689 | 859 |
| 14 | 933 | 901 |
| 15 | 730 | 828 |
| 16 | 1,422 | 1,425 |
| 17 | 1,662 | 1,696 |
| 18 | 812 | 829 |
| 19 | 763 | 751 |
| 20 | 825 | 801 |
| 21 | 744 | 745 |
| 22 | 1,082 | 1,203 |
| 23 | 1,207 | 1,377 |
| 24 | 1,128 | 1,170 |
| 25 | 744 | 731 |
| 26 | 704 | 659 |
| 27 | 1,202 | 1,202 |
| 28 | 635 | 626 |
| 29 | 631 | 652 |
| 30 | 1,344 | 1,404 |
| 31 | 797 | 697 |
| 32 | 616 | 681 |
| 33 | 653 | 688 |
| 34 | 715 | 658 |
| 35 | 700 | 699 |
| 36 | 722 | 708 |
| 37 | 751 | 693 |
| 38 | 1,252 | 1,246 |
| 39 | 864 | 872 |
| 40 | 1,752 | 1,676 |
| 41 | 972 | 912 |
| 42 | 975 | 971 |
| 43 | 1,934 | 1,680 |
| 44 | 990 | 999 |
| 45 | 943 | 975 |
| 46 | 1,049 | 1,094 |
| 47 | 984 | 978 |
| 48 | 1,007 | 1,017 |
| 49 | 2,115 | 2,188 |
| 50 | 1,249 | 1,227 |
| 51 | 1,834 | 1,811 |
| 52 | 1,038 | 1,164 |
| 53 | 1,124 | 1,163 |
| 54 | 2,026 | 2,067 |
| 55 | 1,009 | 1,113 |
| 56 | 1,286 | 1,203 |
| 57 | 2,074 | 1,980 |
| 58 | 1,122 | 1,137 |
| 59 | 1,072 | 1,095 |
| 60 | 1,748 | 1,829 |
| 61 | 1,685 | 1,764 |
| 62 | 1,731 | 1,825 |
| 63 | 1,047 | 1,031 |
| 64 | 1,019 | 995 |
| 65 | 933 | 894 |
| 66 | 867 | 881 |
| 67 | 869 | 884 |
| 68 | 886 | 860 |
| 69 | 698 | 789 |
| 70 | 805 | 818 |
| 71 | 728 | 751 |
| 72 | 690 | 699 |
| 73 | 654 | 687 |
| 74 | 664 | 725 |
| 75 | 631 | 642 |
| 76 | 690 | 695 |
| 77 | 685 | 665 |
| 78 | 563 | 601 |
| 79 | 1,124 | 1,051 |
| 80 | 677 | 639 |
| 81 | 1,146 | 1,187 |
| 82 | 719 | 670 |
| 83 | 773 | 711 |
| 84 | 668 | 681 |
| 85 | 1,368 | 1,211 |
| 86 | 698 | 708 |
| 87 | 720 | 741 |
| 88 | 1,520 | 1,390 |
| 89 | 1,457 | 1,559 |
| 90 | 776 | 778 |
| 91 | 1,418 | 1,373 |
| 92 | 869 | 890 |
| 93 | 1,476 | 1,509 |
| 94 | 1,613 | 1,592 |
| 95 | 1,002 | 1,015 |
| 96 | 1,041 | 1,026 |
| 97 | 914 | 995 |
| 98 | 907 | 981 |
| 99 | 993 | 981 |
| 100 | 2,036 | 1,904 |
| 101 | 1,744 | 2,074 |
| 102 | 1,283 | 1,244 |
| 103 | 1,198 | 1,145 |
| 104 | 2,106 | 2,296 |
Actual and model by week. The model follows the weekly sales pattern; on the hold-out (the last 13 weeks) the error is 5.9% WAPE against 8.2% for the short model. Axis: week number.
Data as a table
| Measure | Coefficient on ln price |
|---|---|
| Price only | −2.97 |
| Price, promo, weather | −1.99 |
| Full model | −1.74 |
| Built-in | −1.7 |
Price elasticity estimate in different models. Omitting factors biases the estimate: naive -2.97, with promo and weather -1.99, full -1.74 against the built-in -1.7.
- VIF
- Alarm threshold
Data as a table
| Measure | VIF | Alarm threshold |
|---|---|---|
| ln traffic | 10.8 | 10 |
| ln price | 13 | 10 |
| Promo | 14.8 | 10 |
| Temperature, °C | 10.3 | 10 |
| Advertising (lagged effect) | 3 | 10 |
Variance inflation factor by regressor. Promo has 14.8 and price 13: they move together, so the coefficients are weakly determined; advertising with accumulation has 3. The threshold of 10 is conventional.
- positive
- negative
- total
Data as a table
| Step | Change | Level |
|---|---|---|
| Base without price, promo and advertising | — | 795 |
| Price (discounts) | +102 | 897 |
| Promo beyond price | +39 | 936 |
| Advertising | +118 | 1,054 |
| Average weekly sales | — | 1,053 |
Decomposition of average sales into factor contributions. The base without price, promo and advertising is 795 units a week; price, promo and advertising add 259 units together. The split between promo and advertising is approximate because of collinearity.
- points
- regression line: y = 0 + 0·x
Data as a table
| Fitted ln sales | Residual | |
|---|---|---|
| 7.03 | −0.1 | |
| 7.05 | 0.08 | |
| 6.98 | 0.03 | |
| 6.99 | 0.1 | |
| 7.03 | 0.04 | |
| 7.03 | −0.11 | |
| 6.98 | 0.05 | |
| 6.97 | 0.06 | |
| 7.66 | 0.08 | |
| 6.93 | 0.14 | |
| 6.87 | 0.06 | |
| 6.86 | 0.09 | |
| 6.76 | −0.22 | |
| 6.8 | 0.04 | |
| 6.72 | −0.13 | |
| 7.26 | −0 | |
| 7.44 | −0.02 | |
| 6.72 | −0.02 | |
| 6.62 | 0.02 | |
| 6.69 | 0.03 | |
| 6.61 | −0 | |
| 7.09 | −0.11 | |
| 7.23 | −0.13 | |
| 7.07 | −0.04 | |
| 6.59 | 0.02 | |
| 6.49 | 0.07 | |
| 7.09 | 0 | |
| 6.44 | 0.01 | |
| 6.48 | −0.03 | |
| 7.25 | −0.04 | |
| 6.55 | 0.13 | |
| 6.52 | −0.1 | |
| 6.53 | −0.05 | |
| 6.49 | 0.08 | |
| 6.55 | 0 | |
| 6.56 | 0.02 | |
| 6.54 | 0.08 | |
| 7.13 | 0 | |
| 6.77 | −0.01 | |
| 7.42 | 0.04 | |
| 6.82 | 0.06 | |
| 6.88 | 0.01 | |
| 7.43 | 0.14 | |
| 6.91 | −0.01 | |
| 6.88 | −0.03 | |
| 7 | −0.04 | |
| 6.89 | 0.01 | |
| 6.93 | −0.01 | |
| 7.69 | −0.03 | |
| 7.11 | 0.02 | |
| 7.5 | 0.01 | |
| 7.06 | −0.12 | |
| 7.06 | −0.03 | |
| 7.63 | −0.02 | |
| 7.02 | −0.1 | |
| 7.09 | 0.07 | |
| 7.59 | 0.05 | |
| 7.04 | −0.01 | |
| 7 | −0.02 | |
| 7.51 | −0.05 | |
| 7.48 | −0.05 | |
| 7.51 | −0.05 | |
| 6.94 | 0.02 | |
| 6.9 | 0.02 | |
| 6.8 | 0.04 | |
| 6.78 | −0.02 | |
| 6.79 | −0.02 | |
| 6.76 | 0.03 | |
| 6.67 | −0.12 | |
| 6.71 | −0.02 | |
| 6.62 | −0.03 | |
| 6.55 | −0.01 | |
| 6.53 | −0.05 | |
| 6.59 | −0.09 | |
| 6.46 | −0.02 | |
| 6.54 | −0.01 | |
| 6.5 | 0.03 | |
| 6.4 | −0.07 | |
| 6.96 | 0.07 | |
| 6.46 | 0.06 | |
| 7.08 | −0.04 | |
| 6.51 | 0.07 | |
| 6.57 | 0.08 | |
| 6.52 | −0.02 | |
| 7.1 | 0.12 | |
| 6.56 | −0.01 | |
| 6.61 | −0.03 | |
| 7.24 | 0.09 | |
| 7.35 | −0.07 | |
| 6.66 | −0 | |
| 7.22 | 0.03 |
Residuals against the fit. Residuals show no obvious structure in the example: a cloud around zero, the Durbin-Watson statistic is 1.99.
Data as a table
| Measure | Model R² |
|---|---|
| 0 | 0.958 |
| 0.1 | 0.959 |
| 0.2 | 0.96 |
| 0.3 | 0.961 |
| 0.4 | 0.962 |
| 0.5 | 0.962 |
| 0.6 | 0.962 |
| 0.7 | 0.961 |
| 0.8 | 0.959 |
| 0.9 | 0.957 |
Choosing the advertising lag parameter. Model R² across λ values: the maximum is at λ = 0.5; at λ = 0 (advertising acts only in the week of exposure) and at λ = 0.9 the model is worse. Axis: decay parameter λ.
Specifics by category
- Apparel
- Weather and season explain most of the variation; temperature is taken with a one-week lag (buying ahead of the season), price elasticity differs for basics and fashion: a model by group, not for the whole category.
- Footwear
- Weather switches are sharp (first cold, rain): precipitation and a temperature threshold flag are needed; sizes do not enter the model, which is built by footwear model.
- Accessories
- Gift weeks and promos give jumps: holiday flags are mandatory; many zero weeks for rare items, so the model is built by group, not by SKU.
- Kids
- The school season and age groups: “before 1 September” and “after school” flags are significant; price is less elastic for school items and more for fashion.
- Men
- Weather and promo contribute less and the base is higher: elasticity is lower, coefficients are steadier; a short model of price, promo and traffic is enough.
- Women
- Color and fashion waves, fast range turnover: price and promo have higher elasticity, coefficients change from season to season; the model is recalculated every season.
Common mistakes
- A regression of sales on price without season and promo → elasticity biased several-fold → add factors or use log-log with controls.
- Dozens of factors on a year of data → the model memorizes noise → no more than one factor per 10-15 observations and a hold-out window.
- Each coefficient is read separately at VIF above 10 → false conclusions that advertising “does not work” → merge factors or check by experiment.
- The advertising lag is ignored → the effect is credited to the next week or promo → add adstock and pick λ.
- Regression is taken for causation and the budget is raised without a test → returns fall outside the historical range → a check experiment before scaling up.
Russian specifics
- Retailer: traffic and receipt data are tied to customers only in anonymized form; a weekly sales model needs no person identifiers, while a loyalty program and cards require consent and a legal basis. Rule: Law 152-FZ of 27.07.2006: Federal Law of 27.07.2006 No. 152-FZ on personal data (rule check not completed).
- Brand: model results are not used to coordinate prices with competitors; price information exchange is limited by competition protection requirements.