08 Oct 2015

How Retailers can stop wasting their promotional dollars

Raj Bhatt

As I had described in my previous article, most retailers don’t optimize their promotions. This leads to billions of dollars of wasted promotional spending, that erodes retail bottom-lines.

So how can retailers optimize their retail promotions? Here is a 4-step process for doing so:

Step 1: Collect and understand the data

In the first step, we need to collect the sales data (at a SKU-daily-chainwide level for national promotions; at a SKU-daily-region level for regional promotions) for a few years, and the promotional calendar (see example below).

Often the promotional calendar might reside in multiple spreadsheets with different merchandising managers. Each promotion in  the calendar needs to be tagged to the SKUs/sub-categories/brands that it pertains to. Each promotion should be categorized by type (% discount, Buy X Get Y, $ discount, Freebie, Mail-in-rebate, etc) and depth (e.g., 5-10%, 10-20%, 20-30%, etc).  Any additional information about the promotion should be captured (e.g., Is the promotion applicable only to the loyalty program members? Is the promotion applicable only to specific displays in the store?)

To understand the data, we often resort to visual analysis of sales trends of top-selling SKUs. All promotions of complementary and substitute categories are examined to understand which categories need to be modeled together.

Step 2: Create independent variables

In step 2, we create independent variables to predict sales (or log of sales) of any particular SKU (the dependent variable) using regression techniques. Typical independent variables used are:

1) Promotional flags for different types of promotion for that SKU as well as for complementary/substitute SKUs

2) Day of the week (or Weekday/Weekend Flag)

3) Holidays

4) Weather (Max/Min/Avg temperature; Precipitation)

5) Average Selling Price

6) Lowest Selling price among Substitute products

The bivariate relationship of each of the independent variables with sales is examined to ensure that only relevant variables are picked up for modeling.

Step 3: Develop regression models

Typically we use log-linear regression models to estimate the  impact of each promotion on the sales of the promoted products, complements, and substitutes. The challenge in this exercise is in running regression models for each ofthousands (or tens of thousands) of SKUs. This requires a high degree of programming skill.

Statistical tests are used to determine relevant variables and regression equations.

Step 4: Develop results and simulator for daily use

Once a relevant set of regression equations are available, the sales impact of each promotion can be determined. One needs to remember that each promotion can impact multiple independent variables (ASP, Promotion flag, Lowest selling price) for multiple SKUs. These impacts need to be combined to understand the overall impact of the promotion.

In doing the impact analysis, we keep impacts on promoted SKUs, complementary SKUs and substitute SKUs separate.

The sales impact and the base sales number, along with the discount implied in the promotion, is used to estimate the lift in Gross Margin, for each promotion.

The promotions having negative margin impact can be re-negotiated with the vendor(s) for better funding support to these promotions. For store brands, we can analyze promotions that lead to reduction in sales and/or margin, so that these are restricted.


The regression model coefficients can be embedded into a simple Simulator spreadsheet that the merchandising manager can use to analyze the sales and margin impact of various promotions run together.

If you need more details, please contact me at raj@knowledgefoundry.net, or on my LinkedIn page.

Uncategorized, , ,