MODELING SAME STORE SALES USING SIMULTANEOUS EQUATIONS

published in DMA MARKETING ANALYTICS JOURNAL June 2016

By Mike Grigsby, PhD

* SORRY THE GRAPHIC FIGURES DID NOT COME THROUGH. *

ABSTRACT

A specialty retailer wanted to develop a model to ascertain revenue performance by stores. They wanted to differentiate first time buyers from repeat buyers, in order to exploit those different sensitivities. They believed there were regional differences and needed the model to account for those differences.

Some of their stores were in attractive areas, having little competition and / or good demographics (income, lifestyle, etc.) whereas other stores were in less attractive areas. The question was around these uncontrollable dimensions vis a vis controllable ones like pricing, staffing, store appearance, customer service, satisfaction, etc. That is, they wanted to develop a “scorecard” for each store taking into account their controllable operations given their uncontrollable circumstances.

Because this framework clearly had at least one independent variable to be used as a dependent variable (satisfaction) and because there was staging involved (customer service => satisfaction => sales), simultaneous equations was the econometric technique of choice.

The resulting analysis allowed development of a scorecard for each store.     This meant that for one store a particular variable, say, net price could be very powerful in terms of driving sales, but for another store (perhaps in another region) net price was less impactful. It also meant that two stores with similar uncontrollable situations that varied in their same store sales could be analyzed in terms of better operations, etc.

BUSINESS PROBLEM

A specialty retailer had about 450 stores nationwide. They wanted to develop a model to ascertain revenue performance. What explained same store sales? The goal of this was to both predict sales and to account for sales. That is, assess accountability for store managers in terms of performance.

It was hypothesized there were two general classes of performance drivers, some in the store’s control and some not in the store’s control. Examples of variables not in the store’s control include number of competitors, demographics around each store’s trade area, etc. Examples of variables within the store’s control (and hence things they could do to increase their performance) included net price, marketing spend, staffing (both the number and type), customer service training, culture, employee engagement, etc.

These differences in performance likely varied by, say, region. Some of the regions may have a large employer move in (or out), differences in unemployment, income, household size, etc., and might make a difference in how effective a store’s operations were. That is, senior management wanted to know if a store is performing aright, taking into account their regional circumstances. It may be possible for a store to do no better than it did, maximizing their pricing and marketing and staffing, and it might be possible for a store to do far better, given their very attractive circumstances.

They wanted to differentiate first time buyers from repeat buyers, in order to exploit and target those different behaviors. First time buyers may be motivated by lead generation, cooperative partnerships, a social media reputation score, whereas none of these would have much of a bearing on repeat buyers.

DATA COLLECTED

This business objective required data from three major sources. First the transaction database would supply same store sales, net price, etc. The second source was primary marketing research in terms of employee engagement, satisfaction, loyalty, customer service and store culture. The last source was overlay data to detail number of competitors, demographics, interests and lifestyle.

The transactional database supplied same store revenue, units, average net price and number and type of staffing. There were also data including certification of industry excellence standards, external and internal store appearance, distance each customer was from each store, etc.

There was a heavy investment in marketing research, primarily focusing on these areas: satisfaction, customer service, employee engagement, quality of assortment and store culture. These responses came from the database of customers so were easy to merge together.

Lastly, several overlay data sources were used. One gave demographics (income, age, size of household), another gave interests, lifestyle and a third gave number of competitors in trade area, etc.

MODELING FRAMEWORK

The stores were grouped by geography, typical in retail. (Another possibility–often preferred, depending on operational tactics—would be to do a behavioral segmentation and then do simultaneous equations by segment.) Each Group VP had from 30 – 70 stores to manage. Most of their annual bonuses are based on same store sales so understanding drivers to increase unit sales is critical.

There would have to be a separate model for first time purchases as differentiated from repeat purchasers. (About 30% of total sales are from first time purchasers.)

Likewise, because of the differences by region, there would have to be a different model for each region. This would amplify the key insight: what kinds of sales performance can be expected given differences by region? Obviously a national KPI could not be standardized across all regions, it needed to be distinct at least at the region level.

Simultaneous Equations

The dependent variable for first time and repeat customers would be units, typical in retail. It was hypothesized there would be some variables unique to first units and some variables unique to repeat units and some variables shared by both. (This was one of the reasons a systems approach was needed.) Causality also suggested a staged approached in that satisfaction was caused by some variables and repeat units were caused by satisfaction. See figure 1 for a graphic representation.

The below are the simplified hypothesized equations.

First Units = f(net price, # competitors, store appearance, marketing

spend, age, income, lifestyle, partnerships, reputation, lead generation, seasonality)

Repeat Units = f(net price, # competitors, store appearance, marketing

spend, age, income, lifestyle, SATISFACTION, customer service, staffing, employee engagement, seasonality)

Satisfaction = f(net price, customer service,   store culture, employee

Engagement, staffing)

 

FIGURE 1 CONCEPTUAL MODELING FRAMEWORK

 

 

The above meant that three stage least squares (3SLS) was one of the key econometric techniques of choice.

(A quick note about another popular (simultaneous equation) choice, Vector Auto Regression (VAR), is that because different independent variables are in each of the equations a vector would be inappropriate. That is, the ability to have different variables by equation (rather than a vector) is more accurate and more insightful.)

Thus, in this case, 3SLS is preferred and instrumental variables had to be found. These would have to be correlated with the endogenous variables and uncorrelated with the error terms. Often large scale macro variables (consumer confidence, industry growth, etc.) can be used as they are correlated with many dependent / endogenous variables (units, revenue, etc.) and (hopefully) less correlated with error terms.

The endogenous variables are those estimated by the system of equations, in this case the dependent variables and those shared by all equations, e.g., first units, repeat units, satisfaction, net price. The exogenous variables are those given and thus outside the system, in this case marketing spend, store appearance, demographics, etc.

In order to be solved, each equation must be at least identified. That is, the number of exogenous variables excluded from each equation has to be greater than the number of endogenous variables included, less one.

As typical, in terms of generating the model results, the data file was split into two random samples. The model was estimated using the “training” sample and verified using the “testing” sample. There was no attempt to “simulate” via some Monte Carlo, etc., process. That is, the point of the model was not to asses risk or range of outputs.

The cost of doing simultaneous equations is that the only desirable property remaining for estimators is consistency. Because variables depend on values from other equations, they cannot be assumed to be fixed. (That is, the assumption of non-stochastic X is violated.) The benefit is that simultaneous equations more accurately model the behavior sought to be understood. Added complexity means added insights.

RESULTS

 

The model showed differences between first time and repeat units and satisfaction by region. As hypothesized, different regions are sensitive to different independent variables.

 

That is, repeat visitors have different sensitivities varying by region. In one region net price may dominate and in another region staffing may dominate and in yet another region satisfaction may dominate. Likewise, first time visitors have different sensitivities varying by region. In one region partnerships may dominate and in another region lead generation may dominate and in yet another region their online reputation score may dominate. All of the above are controllable (within the firm’s ability to change) variables.

 

The model showed differences between controllable and uncontrollable variables by region. In one region unemployment may dominate and in another region the number of competitors may dominate and in yet another region demographics (income, size of household, education, etc.) may dominate.

 

 

Model Output

 

To show the power of this kind of analysis, two regions are detailed below. These are the (final) results of the 3SLS model applied by each region. The key thing to notice is that different independent variables (controllable as well as uncontrollable) are significant by different regions. This is as expected. Note also a different elasticity, even for the same variables, is different by regions.

 

In table 1, first timers are very sensitive to net price, in that a 10% decrease in net price causes a 22.5% decrease in units. The number of sales associates is significant in this region and a 10% increase in sales associates causes a 6.5% increase in units, so while it’s impactful it would be classified as insensitive. Each of these variables gives lucrative strategic insights and provides a business case. The cost of changing price and the cost of hiring more associates can be weighed again the benefits of additional units (and ultimately addition revenue). That is, this analysis pinpoints not only which “levers” a regional VP can pull but by how much in order to maximize total revenue.

 

Their reputation score (a calculation similar to Net Promoter Score) is (barely) elastic and obviously as the firm closes more leads this drives more units. There could be a business case made here as well, in that perhaps hiring more call center reps could increase more closed leads.

 

The number of competitors has a negative impact on first time units and this is an uncontrollable variable. The value in this is that it provides quantification to number of new units as more (or less) competitors move into the trade area.

 

TABLE 1

 

REGION X
ELAST
ESTIMATE MEAN
FIRST UNITS 3.77
Controllable
net price -0.78 10.87 -2.25
# of sales assoc 0.22 11.22 0.65
reputation score 0.06 77.66 1.13
# leads closed 0.45 27.11 3.24
Uncontrollable 0.00
# competitors -0.45 24.77 -2.92
ESTIMATE MEAN
REPEAT UNITS 44.66
Controllable
net price -3.55 18.55 -1.47
# of emails sent -0.91 112.55 -2.30
# of direct mails sent 4.55 14.22 1.45
# sales assoc 5.09 11.22 1.28
internal appear 11.24 0.11 0.03
Uncontrollable
med income 0.001 $61,244 1.37
# competitors -0.60 24.77 -0.33
satisfaction 5.78 7.80 1.01
ESTIMATE MEAN
SATISFACTION 7.80
Controllable
customer service 1.55 6.07 1.21
systems 0.87 5.22 0.58
product assort 1.55 8.97 1.78
Uncontrollable
# competitors -0.25 24.77 -0.79
distance from store -0.08 9.87 -0.10

 

 

 

Repeat visitors are also sensitive to net price. If a 10% decrease were applied there would be a corresponding increase in units by 14.7% and a resulting increase in net revenue. Note that the number of emails sent is negative in terms of responding units. This is rationalized as email fatigue. The number of direct mail sent is positive and impactful. Hiring more associates (which will drive both first and repeat units) in the repeat model is more impactful (as expected) than the first time model. Here, increasing 10% more associates drives 12.8% more units and resulting more total revenue. And the internal appearance of the store is important and positive but rather minor impact. Thus this region has several ways to affect their performance, that is, there are a few variables directly in their control to impact units.

 

Number of competitors (as with first time units) is also impactful and negative. Distance from store is negative but these are uncontrollable. That is, these should be watched but cannot really be acted upon.

 

Lastly it is important to understand the impact of satisfaction. This is why simultaneous equations was an appropriate choice. As satisfaction increases by 10% repeat units increase by 10.1%. The question is, how will the regions increase satisfaction? The answer lies in the below satisfaction model.

 

There are several things that compose satisfaction, and these may differ somewhat by region. Customer service and systems improvement and product assortment all have a positive impact on satisfaction. Product assortment is greatest and a system is actually insensitive to an impact on satisfaction. But again the issue is in the ability to calculate a business case. What is the cost of increasing customer service, or improving systems or (if even possible) explaining product assortment? Whatever the cost, the return it gives generates an ROI. This model details a way to optimizing which projects best improve satisfaction, which will in turn improve repeat units which will drive repeat net revenue.

 

As expected number of competitors and distance from store decreases satisfaction. Both of these have a minor impact on satisfaction.

 

 

TABLE 2

 

 

 

REGION Y
ELAST
ESTIMATE MEAN
FIRST UNITS 4.08
Controllable
net price -0.36 10.09 -0.89
# of partners 1.05 2.59 0.67
reputation score 1.98 3.55 1.72
# leads closed 0.27 31.99 2.12
Uncontrollable
lost major employer -25.77 0.09 -0.56
# competitors -0.22 9.55 -0.51
ESTIMATE MEAN
REPEAT UNITS 67.08
Controllable
net price -2.44 19.08 -0.69
# of emails sent -0.72 121.50 -1.31
# of direct mails sent 4.99 15.01 1.12
# sales assoc 6.55 12.97 1.27
remodel amount 0.0002 $115,208 0.34
Uncontrollable
med income 0.001 $68,055 1.01
size of household 2.28 2.09 0.07
satisfaction 6.88 6.70 0.69
ESTIMATE MEAN
SATISFACTION 6.70
Controllable
customer service 2.22 5.55 1.84
assoc engage 1.55 8.99 2.08
product assort 2.08 6.88 2.14
Uncontrollable
distance from store -0.04 5.55 -0.03

 

 

Table 2 presents a very different region. Again the idea is to note these regions vary and those variances give managers ways to improve their regions and the stores performance.

 

First visitors in region Y are insensitive to price, which is a very different finding than in region X. This finding means that instead of lowering price to increase revenue, this region should raise price to increase revenue. While reputation score and closed leads are again significant, the amount is very different then regions X. Lastly, the number of associates does not show up in the region’s model but instead partnerships are a significant variable.

 

In terms of uncontrollable variables number of competitors is significant as well as the loss of a major employer. While the firm can do nothing about these variables just noting their occurrence gives them items to be careful of and pay attention to.

 

The repeat visitors are very different in this region as well. While net price is significant, repeat (as with first time visitors) visitors are insensitive to price. There are similar findings in both regions in terms of number of emails and direct mail sent ands number of associates. However, this region is sensitive to the remodel amount rather than internal appearance, perhaps only a subtle difference but interesting in its own way.

 

Uncontrollable variables show up as median income and size of household. This region does not have the competitive pressures of region X. This has implications for enterprise optimization / subsidies, etc. Note also that while satisfaction is significate, it actually has an insensitive elasticity.

 

This regions satisfaction model has customer service, number of associates and product assortment under the firm’s control. Associate engagement was not found to be significant in region X.

 

For uncontrollable variables, distance from store shows up again as significant.

 

TABLE 3

 

REGION Y
REPEAT UNITS
STORE YOY% % PRICE CHANGE % CHANGE # EM SENT % CHANGE # DM SENT % CHANGE # ASSOC % CHANGE REMODEL $ MED INCOME INDXD SIZE HH INDXD % CHANGE SAT
11 +11% +2% -3% +0% +1% +4% 1.12 1.01 +0%
113 +9% +2% +1% +1% -2% +6% 1.24 0.98 +1%
209 +8% +1% -2% +3% +3% -1% 0.98 1.03 +2%
7 -2% +0% -2% +1% +2% +2% 0.77 0.89 -3%
18 -4% -3% +4% +4% -4% -3% 1.02 1.03 +0%
90 -5% -1% +4% +4% -2% +0% 0.68 0.99 +2%

 

 

 

Scorecard

 

Table 3 shows part of region Y’s scorecard. These are the top and bottom three store performers, measured by year over year percent growth. The whole point of the modeling was to find which variables are significant, believing that these would differ by region, and then look at how each store operated in terms of those variables.

 

That is, if price is important and say the region tends to operate on the inelastic side of demand, the appropriate strategy would be to increase price which should help increase revenue. Given that, each store’s operations can be ascertained (and ultimately guided) in terms of correct strategy by particular variables or metrics.

 

Thus, the top three performing stores all did tend to increase price. Notice that the bottom three performers moved price in the wrong way. In terms of number of emails, the more sent the more negative pressure is put on revenue and the bottom three performers tended to send more emails then the top three performers. Also the bottom three stores decreased their number of associates which tended to decrease units. While minor, increasing the amount spent on store upkeep, modernizing, etc. has a positive impact on revenue.

 

In terms of store operations, if the store is struggling during the year a common tactic would be to decrease price, and without the model management would not know what the appropriate action is. Also it may seem that sending out more emails would help counteract a sub-par year but in this case those are exactly the wrong actions. Likewise decreasing the dollars spent on associates and decreasing the amount spent on remodeling may appear to be a cost cutting measure but again those are exactly the wrong decisions. The bottom performers also sent out more direct mail and that is the correct response.

 

Thus the scorecard is intended to find which levers are impactful in each region and give store managers a tool to help optimize revenue. This can be in the form of a test and learn plan, or managing KPIs, etc.

 

The above referenced controllable variables, those levers that store management can change. Looking at the uncontrollable variables note that the top three performance tend to be over indexed on income and size of household whereas the bottom three tend to be less than average.

 

Taking a quick look at overall satisfaction shows the same trend: the top performers tended to increase their satisfaction score while the bottom performers tended to decrease their satisfaction score. Since the managers know form the model that customer services, associate engagement and product assort drives satisfaction in this regions, these metrics can be examined as a scorecard as well and a focus can help drive satisfaction.

 

 

 

Using the Store planning matrix

 

Note Figure 2 below, which shows the store planning matrix (SPM). Only the six stores mentioned in the scorecard are shown.

 

 

FIGURE 2 STORE PLANNING MATRIX

 

 

 

The SPM plots stores on two dimensions, economic area and revenue performance. Economic area can be defined as some combination of number of competitors, the changing of a large employer, income, household size, unemployment, etc. The idea is to create some dimension of how attractive is a particular trade area. The other dimension is YOY change in revenue.

 

The issue is how a store performs given the economic environment they find themselves in. As an obvious example look at store number 11 versus store number 18. They each, as shown, have similar economic operating areas but drastically different revenue performance. The SPM is a tool that gives managers an immediate way to understand which store is delivering given their operations. That is, store number 18 cannot claim that they can do no better because store number 11 is in the same environment and did perform much better. Then, using the store scorecard above, as mentioned, particular recovery plans can be put into place.

 

In terms of a strategic approach, the four quadrants of the SPM each have a specific goal. The top left might be to gain share. The top right might be to maximize and defend. The bottom left might be to manage for profit. The bottom right might be to manage for revenue. Plotting where a specific store lands in terms of its peers gives management a quickly relevant POV.

 

 

CONCLUSION

 

Same store sales modeling can be used to operationally predict future sales but the real power is to provide tools to understand what drives revenue. If, as is usually the case, these drivers are different in terms of a region (or a segment) then it becomes more critical to find how they differ. And more importantly, the ability to drill down to the store level, and find which stores are performing optimally, is critical for YOY success.

 

 

BIOGRAPHY

 

Mike Grigsby has been in marketing analytics for nearly three decades. He worked in CRM / database marketing at Dell, HP, Sprint, the Gap and is now a marketing science consultant at Targetbase. His PhD is in marketing science and he has taught marketing analytics at UTD, UD, and St. Edwards. He has published in both academic and trade journals and led seminars at DMA, NCDM, etc. He is the author of MARKETING ANALYTICS and his second book, ADVANCED CUSTOMER ANALYTICS, comes out October, 2016. Link to him on LinkedIn, follow on Twitter, or read the blog at marketingscience.biz.

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>