I have a dataset of sales data (individual sales from multiple shops in an area, date sale was made on given) from multiple shops (denoted by the field shop_number). I calculated a field for profit (units_sold*[retail_price - product_cost]).

To get the profit made by the shops, I grouped the data by shop_number and summed up the profit.

However, some shops have sales within the entire timeframe, while some within a subset of that timeframe.

Should I divide the profit by #sales. If I could assume that the shops with sales only within a subset didn't make any other sales during any other timeframes, then summing the profit would be sufficient.


I assume your goal is to compare the shops in terms of whether one seems to be more profitable than others? If so, doing each comparison for the times when both shops being compared may make sense. I say may, because if some shops cannot ever be open during some time periods (e.g. if shops at railway stations are allowed to be open on bank holidays, but other shops not, then this is simply an inherent advantage of being at a railway station - which presumably also comes with higher costs in terms of renting the space for the shop), then this may not answer the question. If it's rather that some shops did not exist / were being renovated, then it makes more sense to exclude those times from a comparison.

If you don't want to do pairwise comparisons like that, there's also the option of splitting the time periods into some intervals and then fitting, say, a linear mixed effects model for profit that has a fixed effect for shop, a random effect for each time interval (let's say week is a sensible unit) and perhaps some other factors (depends, see below, also time trends, seasonality etc.). E.g. in R this could look like lmer(profit ~ (1|week) + shop) (using the lmer function from the lme4 package, this assumes a data structure with one record per shop per week giving the profit for that week in that shop). This is a bit too simplistic, e.g. it ignores that adjacent weeks are presumably correlated, but may give you a reasonable way to get some kind of impression of what's going on once one adjusts for what times shops were open in.

If it's really about comparing shops, then presumably there's also other considerations: rent for the shop (some locations are presumably more expensive than others), salaries for staff, what product ranges are offered (you need to figure out whether you truly want to compare shops that only sell, say, bread, pastries and coffee to go with full supermarkets or not), other costs (electricity, water, insurance...) and so on. What you wish to do about these things will really depend on exactly what your question is.

  • $\begingroup$ Yes, I want to compare the shops on which is the most profitable. If I only keep the times that exist for all shops, my dataset will be cut my a very large amount. So I don't plant on doing that. Moreover, all the shops do belong to the same category (pharmacies), so no seasonal component. Would assuming profit/sale help? However, the problem remains that what if the shops are not actually making any sales. This is a school project, and the professor is absolutely unhelpful as everything is a 'part of the challenge'. $\endgroup$ May 17 at 18:46
  • $\begingroup$ That's what a model like the one I describe would get around (it does not - on the way I specified it - deal with the other mentioned considerations). $\endgroup$
    – Björn
    May 17 at 18:50
  • $\begingroup$ I edited my comment, sorry. $\endgroup$ May 17 at 18:50
  • $\begingroup$ Presumably for pharmacies there would be at least a slight seasonal component (e.g. in Europe there's typical times where people would tend to buy certain products more e.g. hayfever medications, things to alleviate cold/flu symptoms, flu vaccines, sun cream etc.). It may not be enough to matter much. Also, at least in my country some larger pharmacies would be open every day, while smaller ones would rotate within a city which one stays open on what weekend days/bank holidays (not sure if that applies for your data). $\endgroup$
    – Björn
    May 18 at 14:48
  • $\begingroup$ In any case, as said, if you cut your data into time intervals (e.g. weeks or days) during which each pharmacy would either be open (there is a record with an outcome variable) or closed (no record), then fitting a random effects model as with a random time interval effect may be a reasonable approach (but consider all the other possible complications - you know much more about your own data than anyone else on the internet and will be able to make better decisions by really investigating/looking into the details). $\endgroup$
    – Björn
    May 18 at 14:52

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.