Set-Up File

 

Evaluating Promotions is a complex and difficult task.  In the real world, anything and everything can occur and does.  A robust model will handle all these conditions but an analyst needs to direct the model as to what is important.  The following is the control/set up file SPAR uses.  There is nothing required to be used other than basic information such as which product is to be evaluated.  But by reviewing the setup file you will be able to learn about what factors are occurring in the real world and how they can be dealt with.  It is then up to the analyst to determine how much they want to consider.  One of the most important considerations in what should be in the setup file is the robustness of the model itself.    For simplistic models such as exponential smoothing the ability of an analyst to have any flexibility is very limited so most of these factors are not required.  My recommendation is that if a company is spending hundreds of millions of dollars that the model be robust and all the conditions considered.  This is the file SPAR uses after having evaluated millions of promotions.

 

  1. The name of the Company running the promotion.
  2. Type of run the analyst wants.  The options are. 
    1. Standard analysis using the database with all the data.  This would be the typical analysis and for most situations this is what is run 
    2. An automated run when good information on historical promotions is not available.  This would not be used for a sophisticated CPG company spending hundreds of millions of dollars on trade promotions but can be used for a company without a large budget.  It is also very effective with scanner data as the promotions are short, discreet and the lift is usually significant.  But this option requires a whole additional set of AI that allows the identification of promotions based on the data.  This will also not generate profitability but only an approximation of profitability based on some averages.  For someone starting out this option is not important but when setting up a control file it can be included. 
  3. SOURCE of lift analysis.  This is unique to SPAR and not something anyone starting to build a Trade Promotion Analysis system will need.  But eventually after the incremental volume from promotions is calculated, questions will arise about the source of the incremental volume.  Is it brand switching, forward buying, cannibalization, incremental consumption and so forth.  SPAR uses three options. 1-Don’t do this type of analysis (which is what almost anyone starting to build a system should show). 2-Run it for ex-factory shipments and 3-run for scanner data.  The characteristics of ex-factory shipments and scanner data are materially different so the analysis will be materially different. 
  4. Location of data.  With a relational database vs. other types of databases the exact information needed here may vary from database to database.  For the SPAR system we identify the exact location of the various data files (e.g. sales, promotion, market variables or weights, summary files, product master files, dates file and various parameter files).  To the extent the system is used with limited companies or products this is less important.  But some type of pointers are necessary to allow the system to access the correct data files. 
  5. Type of units.  (e.g. cases, dozens, packages, units, pounds, tons).  This is best done by a number (e.g. 1=cases) to avoid misspelling or other abbreviations. 
  6. System version.  Most systems will at some point have versions to be used.  A simple example is when a system operates Internationally.  Then the version has to be identified as dates are input and displayed differently in most countries of the world from what is done in the US. 
  7. The following are all items in the SPAR control file.  Some of these may not be necessary for someone building a new model or they can be built in and always set to 0 or 1. 
    1. default starting week.  Usually this is 1 but there are time when an analysis will want to run the model using starting at a different week number.  This usually occurs when something has caused a material shift in the market and the early data is no longer relevant and may be distorting the analysis. 
    2. Generate forecast or not.  If the analysis is only for historical analysis it is not necessary to generate forecasts and this allows for less processing. If a forecast is required than it can be turned on.  SPAR has found that 19 months is the appropriate amount to forecast as other than short term projections most companies start their annual planning after the first half of the current year is over so a 19-month forecast will allow for a full subsequent fiscal year to be projected. 
    3. Type of profit margin to be used.  The options are usually the profit margin found in the setup file (this means it will be consistent over time), profit margins by individual promotions which means it will come from the promotion file, calculate the profit margin based on some type of built in logic and customized margins. 
    4. Use of promotion extensions.  If promotion extensions are not used then the dates of the promotion will determine the period of the promotion and analysis.  But it is often the case that promotion dates are extended.  Sometimes for all promotions and sometimes on a promotion by promotion basis.  One reasons to have promotion extensions are if the company always takes orders on the last day of a promotion (e.g. Friday) which are shipped the following week.  So while the promotion technically ended on Friday (as shown by the promotion dates) the sales in the week following the promotion are really part of the promotion and need to be considered as promoted sales.  Another reason is that occasionally special situations will result in promotions being extended or the sales from the promotion goes past the promotion dates.  This only happens in a small percentage of the promotions but if you want a robust system the system needs to be able to handle these types of situations. 
    5. Maximum number of promotions per product.  This is a database/software/processing issue and needs to be worked out with the software team building the models.  At SPAR we use two options.  A maximum of 41 promotions or a maximum of 146 promotions.  It is helpful to know the maximum number of promotions for building arrays and databases.  41 promotions is adequate for an analysis.  But for a few products where promotions are run weekly (generally DSD products sold by companies such as Pepsi and Coke) it is possible to have up to 146 promotions over the history and forecasted period. 
    6. Number of weeks to shift for a price increase.  Price increases are picked up by the model from the database showing when a price has increased using certain rules (these rules insure it is a real price increase and not just an error in the data or normal variance from week to week price variations).  Whenever there is a price increase there is a buy in at the lower price and a reduction in future sales as the extra purchases are used up.  Different companies have different policies on how they handle price increases.  Some allow for purchases the week of the price increase, some the week before and some the week after it is announced.  In order to be sure the model turns on the price increase variable in the correct week (regardless of what the official price list shows) it is sometimes necessary to shift the week the price increase variable is turned on by a week.  This will not apply for a scanner data analysis. 
    7. Compliance factor.  Most promotions are a straight discount on all purchases during the promotion period.  But a number of promotions require compliance with certain criteria (how accurate the measurement of the compliance is a separate issue).  The choice is to not use any compliance factor or use the compliance factors from the promotion file so they can be entered promotion by promotion. 
    8. Use extra costs from the promotion file.  Management may want to factor in these costs.  While an analyst may think it should be one way or other management will often ask for things that seem different.  A good system needs to be prepared to handle those requests routinely and including or not including these extra costs are a request that occasional gets asked.  Note there are two buckets for extra costs in the promotion file so an input is needed to control which one of the extra costs are to be used. 
    9. Skip the first x weeks of data.  This is helpful for new items or other situations where the early history distorts the analysis.9.  Use of the sales file.  In the sales file there are 4 types of promotion volume, a manual override, a sales adjustment column and a sparline override for each week.  All will be used unless indicated otherwise.  The model needs the flexibility to occasionally skip one of the columns for various reasons.  Until you have done a number of evaluations this may not seem like something that is needed.  But from time to time it will be important and if built in up front it makes it easier when it is needed. 
  8. Use of the sales file.  In the sales file there are 4 types of promotion volume, a manual override, a sales adjustment column and a sparline override for each week.  All will be used unless indicated otherwise.  The model needs the flexibility to occasionally skip one of the columns for various reasons.  Until you have done a number of evaluations this may not seem like something that is needed.  But from time to time it will be important and if built in up front it makes it easier when it is needed.
  9. Instructions to the system. 
    1. output location.  Depending on the purpose of the run the output may go to a printer, a database, or a screen.  This is a code for telling the model where to send the results.  For looking at one promotion it may be better on a screen.  If you need to work with the analysis then a hard copy.  If you are running 100’s or 1,000’s of promotions then to a database. 
    2. Suppress various error messages.  Within any good system there are many warnings that print alerting the analyst to possible errors.  For example, it may say that a discount is unusually high or low based on historical averages.  When running a single promotion this is helpful.  When running a thousand promotions it is too much data and it is better to not keep printing the message. 
    3. Do you want to set the post promoted period to 0?  This is often the case with scanner data as the actual drop in sales is negligible compared to actual pantry loading and not helpful. 
    4. Should short term trend logic be used.  Within the SPAR system are special conditions for dealing with sales variations that are unexplained by any known variables but are occurring in the real world.  For example, normal sales are 100 units per week for years and sales during promoted weeks are 200 units showing a 100% increase during a promotion.  Suddenly sales during promotions jump to 300 units/week indicating better promotions.  But when we look at normal sales just before and after the promotion we see it is 150 units/week and not 100.  Based on using all the historical data it appears normal sales (the sparline) is 100.  But just before and after the promotion sales are 150.  Of course if this is not explained by a known variable,  extra weight needs to be given to these periods of normal sales before and after the promotion.  The setup file will tell the model if this special logic should be used or not. 
    5. The type of date format being used.  6 digits (mmddyy) or 8 digits (mmddyyyy). 
    6. US or international dates. US is mmddyy and international is ddmmyy.


Not all these factors are necessary all the time.  They may never be needed for some models.  But it has been our experience that situations arise where they all have been needed.  In building a database and a model it is far easier to build something in up front than to try to add it later.  I would recommend the model be built to allow all these factors be handled as inputs and it not needed enter them as zero.  But when the time comes they are needed the model will be ready to accept them.