I am developing a system where the management of the supermarket can make decisions on past sales data. There I mainly want to focus on how to predict sales of an item changes based on a discount given to another item (e.g Substitute goods). for that management can decide how much sales of other items may vary based on a discount given to a particular item.

Could you please suggest me how the data set should be to approach to this requirement? I.e., what are the tables and columns of the database?

  • $\begingroup$ FYI, what you're trying to estimate in economics is referred to as the cross-price elasticity of demand. $\endgroup$ – Matthew Gunn Jun 4 '16 at 15:29

What you are trying to estimate in economics is referred to as the cross-price elasticity of demand, how demand for a good $Y$ changes in response to a change in the price $p_x$ of good $X$.

Several points to keep in mind:

  • To estimate a demand curve, you need to use shifts in the supply curve. For example, if we were trying to estimate how demand for apple juice changes as a function of the orange juice price we could use:
    • Purposeful experiments: (eg. we randomly raise price of orange juice and see what happens to purchase of apple juice.)
    • Natural experiments: (eg. hit to Florida orange crop reduced orange juice supply
  • You cannot estimate a demand curve just looking at movements in price and quantity in the abstract because you don't know whether the changes are driven by changes in supply or demand! Changes in supply allow you to estimate a demand curve and changes in demand allow you to estimate a supply curve.

Example of horribly wrong inference:

Imagine you're trying to estimate demand for snow shovels as a function of the price of mechanized snow blowers. And imagine in the data, you see sales of snow shovels goes DOWN when the price of snow blowers is LOWER!?!? How could they be complements? Shouldn't they be substitutes?!

The answer is that the price of snow equipment is lower in the summer (when demand is low) and higher in the winter (when demand is high). You can't estimate a demand curve using demand curve shifts!


The most convenient data layout for analysis will likely depend on the model you're using. Presumably you haven't already chosen a model, so for the meantime, collect the data into whatever format is convenient for collection, keeping in mind principles of good database design such as fourth normal form. I'm imagining something like

Discount ID | Item ID | Discount | Start      | End
4710        | 31231   | .25      | 2016-06-04 | 2016-06-11

Item ID | Date        | Units sold
31231   | 2016-06-05  | 41

All due respect to Kodiologist, I wouldn't structure your data matrix the way he's laid it out. Too much useful information is being lost with a weekly summary or isn't accounted for. Daily information would be much more sensitive and helpful for estimating elasticities and cross-elasticities, particularly for panel data modeling which is one of the best ways to elicit information of this type.

That said, you haven't told us some important things about your data. One can infer that it is at the "item" level. Is that synonymous with a UPC (sku) or are items aggregates of multiple UPCs? Do you have features or factors that describe those items, e.g., category, size, quantity, etc. Is it information from Nielsen or IRI and at the market level? Or is it actual transaction level data from the store's sales registers? How many stores? You would want your data matrix to reflect relevant structural factors related to cross-sections such as stores and markets. How far back does the information go? To Matthew Gunn's point, you would also want to include factors related to seasonality -- by year, quarter, month, week and day, as available and appropriate.

Next, any discount occurs in a "landscape" of marketing factors such as price, advertising, promotion, displays, etc., which are going on simultaneously for every product and brand across the stores, all of which can impact household purchase decisions. Given that, widening your theoretical frame to include product complements (negative cross-elasticities) as well as substitutes (positive cross-elasticities) would be a big strategic win. Related to that are the concepts of competitive frames or consideration sets where a set of items cluster together as being possible substitutes, etc. This effect would be captured with dummy variables to indicate membership in a product cluster.

So, your matrix of panel data would have qualitative factors (columns, features or variables) that capture the relevant structural factors, e.g., a column for store id, a column for the date of the sales, columns for the seasonality factors (as appropriate), a column for the items, columns for the relevant descriptive features, and so on. Dates, in particular, are useful for trend analysis. It would be a lot of columns, potentially, and some of it would be redundant.

The matrix should also contain time-sensitive information related to pricing and other marketing activities by store, product and date, each with a separate column, as appropriate.

The unit of observation (rows) of the matrix would be the store, date, item combination of this information at whatever level of granularity you have available. You will have to make a determination if the data is to be rolled up or aggregated to some higher level to facilitate processing and modeling. Just bear in mind that the more you summarize the information up, the more sensitivity you lose. If you go too high, you can erase the impact of the things you are trying to understand.

There are lots of ways to model data like this -- deriving the relevant interaction terms to estimate the elasticities, etc., is the key thing to understand. These interactions will potentially be complex. One of the best introductions to thinking about elasticities and cross-elasticities from a marketing point of view is Lee Cooper's book Market Share Analysis which is available free on his UCLA website. He discusses in considerable detail the various methods for their estimation using interaction terms. Cooper's recommended approach is to use OLS regression in a panel data format. His book also contains really nitty-gritty examples of what the data matrix could look like and how to structure it for modeling.

Economists will tell you to pick up Wooldridge's book Econometric Analysis of Cross Section and Panel Data. It is indeed an excellent resource but it is focused on econometric analysis based on panel data models with balanced, fixed N (sample size) and t (time periods). Your data is marketing data and will be neither balanced nor fixed. Wooldridge's prescriptions, however well intended and appropriate for economics, will only confuse you.

  • 1
    $\begingroup$ "Too much useful information is being lost with a weekly summary or isn't accounted for." — My suggestion is not to show only a summary of discounts per week but to show the duration of every discount the shopkeeper created, at a daily level of granualrity. In the example, the discount lasts 1 week. $\endgroup$ – Kodiologist Jun 4 '16 at 18:51
  • $\begingroup$ @Kodiologist Evidently, our OP isn't happy with any of our responses as he's asked the same question again. stats.stackexchange.com/questions/216306/… $\endgroup$ – Mike Hunter Jun 4 '16 at 18:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.