Spar InfoTech, Inc.
Sales Database for Trade Promotion Analysis
To determine what is in a sales data file I will discuss the minimum requirements and then expand to the sales file for building a SPARLINE. The reader can determine what is necessary for them to build the type of database needed based on the sophistication of the user, the availability of the data, and the value of a continually more accurate analysis.
Essential data. The minimum level of data is simply weekly unit sales. It can be scanner or shipment data (and even other types of data such as warehouse withdrawals). It is recommended sales be physical units and not be in a local currency since price increases and inflation will then distort the analysis. Weekly data is usually available. Sometimes only monthly data exists but it is my experience that it is a waste of time analyzing trade promotions with monthly data. I recommend spending time getting weekly data and not trying to analyze Trade Promotions using monthly data. This is the minimum that is needed for the most elementary analysis and all that is required for some models (e.g. exponential smoothing).
Let’s expand the type of sales data that can be collected and the reasons. I will review the Sales Data file that is used for the SPARLINE and the reader can determine which types of data make sense. In building a data file of any type it is usually better to build the data file that may eventually be needed rather than a less sophisticated data file and have to add to it in the future.
Dates/week number. Sales data does require a week number or dates to be associated with it.
Units sold on Promotion. The next level is how many units are sold each week at list price and how many on Promotion. This is necessary for a number of reasons. Without knowing how many units are sold at full price and how many on promotion it is not possible to accurately calculate the cost of the promotion. In some situations, weekly sales will have product sold at full price and on promotion. If 100% of the sales during the promotion period was assumed to be on promotion it would inflate the cost of the promotion. This spilt is also necessary if a more sophisticated model is used which verifies promotion dates by the sales data since the percentage of sales on promotion in any week is an indication of when a promotion is occurring. My experience is that a model builder should allow for 4 different types of discounts. While four may seem like a lot of promotion options, it does happen on occasion and the extra work to deal with exceptions is worth the cost of maintaining four buckets for promotions. Many readers may think that a promotion is simple and has a single discount. But consider a situation where a product is heavily promoted and a free goods (e.g. 1 free with 11) or an on-pack is offered with a discount. In that case, sales could be just the on-pack promotion (e.g. excess inventory sold after the promotion ends) and if an off invoice (OI) discount is offered the database should indicate how many units were sold with the on-pack, with the on-pack and the OI, or just with the OI. If another promotion was run just prior to this promotion it is possible that a different OI or other type of promotion could overlap this promotion. This complexity is particularly true if not using scanner data as shipments can lag the official ending date of a promotion and without having the units sold at different discounts accurate costs are hard to quantify. While the reader can decide how many alternatives to allow for, four promotion alternatives are in the SPARLINE system. If limited resources are available to build the database it is possible to just show promoted units in total but I recommend against this unless it cannot be avoided. Even if starting with just a spilt between promoted and non-promoted sales I would recommend building the database to be able to handle 4 different types of promotions.
List Price. The list price for the week is necessary in order to quantify the costs of the promotion. Actual average selling price can be used and while it makes sense to have it, it has less value than may appear on the surface. While the actual average price is a measure of what the product is sold at, it doesn’t reflect the true cost of many promotions. If a promotion is run at a 10% discount and all units during the promotion are sold at the 10% discount then the actual average price is useful. But if any of the units are sold at full price then a difference will exist from the 10%. If the sales database is built showing full price sales and sales at a 10% discount the average price can be calculated and doesn’t have to be independently maintained. There are also many costs that are not OI, so having the actual average price is not helpful in determining the cost of a promotion. Costs such as on-pack, extra product promotion, IRC’s, coupons, in store promotional material and other such non-OI costs need to be dealt with to accurately quantify the profitability of a promotion.
Adjustment to sales. In the real world things happen to distort sales. Some of these are strikes, special shipments (e.g. a tractor trailer of product is shipped on a special opening), material out-of-stock and lost, or extra sales for different reasons). To not adjust for these items would mean that the historical data is materially compromised which negatively impacts the accuracy of such measures as seasonality and trend. Consider a case where every January sales are 1,000 units but something happens that makes sales 0 for one year. Without an adjustment converting actual sales to normalized sales it would appear that January seasonality was 800 instead of 1,000.
SPARLINE (or baseline model override). There will be situations where the model will want to set a baseline so it is locked in. This will only be done in the more sophisticated models so most new users need not be concerned with this item. But once a sophisticated model has been operating for a number of years situations with an automatic baseline override will appear and it is better to design the database now to handle that eventuality rather than wait.
Manual SPARLINE override. This will be more common and can be used by the inexperienced and new user of trade promotion analysis. In many cases this is more important to the more unsophisticated user having a simplistic model. The more simplistic the model the more likely obvious baseline errors will occur. When that happens the analyst can either use the wrong estimates, try to modify the input, or model to have an automatic adjustment or put in a manual baseline override. Using the incorrect numbers defeats the purpose of the analysis (but that is often what will happen when large numbers of baselines are run automatically without oversight) and modifying the model or input will be hard since these situations most likely occur with poor models or unsophisticated users so corrections are difficult. But it is easy for a user to put a baseline estimate into the database and have it locked into place for use in future analysis.
There is no absolute right and wrong way to build a Sales Database. I have outlined what is used in the SPARLINE system and why and my recommendations for what is needed in different situations. There may be other appropriate information that can be included. Some of the more sophisticated and complex options may not be needed for a simpler analysis but designing the sales database so data can be added later is valuable. The sales database is the foundation on which everything is built. It needs to tie into the other data such as the promotion database and in the promotion database section we will see how and why. The sales database is also dependent on the model being used. A model without the ability to automatically override a baseline means that attribute is not needed. The final design of the sales database will take into consideration the type of model being used, the other databases, what data is available, the sophistication of the users, and the purpose of the trade promotion analysis.