5
$\begingroup$

I just found out that my dataset is a lot messier than I expected and I was wondering if anyone here had some advice. I have sales data that is divided into regions (5 big breaks on a national level and then 4-6 breaks within each big national level break, the second break is what I am calling a region. The breakdown after that is all of the stores that sell within a region.), but I just found out that what is included in the regions varries over time.

In the last 3 years, they have removed 3 regions and then moved their sales to other regions (Imagine taking all of the sales for the twin cities and then deciding that on Feb 1 they should be switched from being recored as"Twin Cities" to half of them being recorded "Madison" and the other half being linked to "Chicago").

Also it is very common (not exactly sure how common yet, working on figuring that out) for an indivudal store (the breakdown after region) to move from one region to another (so on Feb 1 my store moved from being recored as selling in the twin cities to selling in Chicago).

Naturally this is information is not contained in my data set (no one things of the statistics guy when they make data deicisions), so I have a cube that currently has no link to this information. In an access database I have access to individual store level data that is a list of their monthly region association (so for my store it would say "twin cities" in each cell until Feb 1 2012 and then switch to "Madison").

I have two ideas to approach this. The first is to go through the access database, identify the stores that have been consistent in their region definition and remove all other stores, then join that with the data I have already (when my IT guy gets me access to the information in SSMS). This amounts to deleting any store that has ever switched region from the data. The problem I see here is it is going to remove what I imagine to be a ton of data and the data is going to be removed more heavily from some regions than others, since some regions have more movement than others (and we still have the 3 complete region removals in the way).

The other thing I am considering (not sure if it is legit) is to go through and add in a dummy variable that is a 1 if there was a region change and include it in my model. This does not address multiple changes, but if I include that information in my model it should deal with some of it, yes?

I would like to come up with a solution that does not involve gutting my data set, which is what I am leaning towards now. It might be that a store level shift can just be ignored, since they are somewhat common and are not that huge of a shift, but deleting a region and moving it seems like it would just be too large to ignore. Thank you for your suggestions.

Update: Looks like our data team already has a solution implemented, see @peter and my comments below for the solution.

$\endgroup$
  • 1
    $\begingroup$ It's a great question and presents a real challenge. You might be heartened to know that many people have had to confront problems like this one. Check out the Modifiable Areal Unit Problem (MAUP) and look into dasymetric mapping (which has two meanings: this is the one concerned with changing geographic regions for the purpose of analyzing underlying data). $\endgroup$ – whuber Feb 29 '12 at 17:17
  • $\begingroup$ Thanks for pointing me in the right direction. I figured it was a problem that has come up before, but I didn't know quite where to start. Do you happen to know of a decent way to implement in R? I am going to look around and see what I can find, but I am always open to suggestions. $\endgroup$ – asjohnson Feb 29 '12 at 17:29
  • $\begingroup$ Are all the changes of region either splitting (like your twin cities example) or merging? If so, could you get around it by using the least granular of the definitions for the whole dataset? (ie convert both "Madison" and "Chicago" back to "Twin Cities" for the purpose of analysis). This would lose some information but definitely be better than deleting all those stores completely. $\endgroup$ – Peter Ellis Feb 29 '12 at 18:53
  • $\begingroup$ Another place to look is at how the US Census Bureau handles this problem. Their administrative units (blocks, block groups, tracts, counties, zip code areas, etc.) change every decade: some split, some merge, and complex combinations of such things occur. To help people compare data over decades, they maintain tables of these relations but they do not modify older databases. See census.gov/geo/www/2010census/tract_rel/tract_rel.html. $\endgroup$ – whuber Feb 29 '12 at 18:59
  • $\begingroup$ @PeterEllis My understanding is that the 3 region changes were removal of 1 region completely and moving the stores in that region into other regions (so merging). The stores are generally just shifting (ie you move from A to B from this point in time on). So your proposal is that I would take both of the regions that the split merged into and turn everything to the region before the split (so madison and chicago all become twin cities), because that just seems like the same problem in reverse (now at a point in time I lose madison and chicago). $\endgroup$ – asjohnson Feb 29 '12 at 19:26
1
$\begingroup$

I may be wrong, but why don't you forecast each store by itself and then aggregate them to the current "major break".

$\endgroup$
  • $\begingroup$ Hmm, so I would go down to a store level, fit a model there (this is ignoring region?) until the first major break. Then fit another mode after the break? Kind of a stepwise regression? Do you include the region information that you have at each step? $\endgroup$ – asjohnson Mar 1 '12 at 4:04
  • 1
    $\begingroup$ @asjohnson What I had in mind was forming a useful model for each store separately. If there had been any structural changes , perhaps do to a re-assignment then deterministic structure like a level shift would be identified and incorporated. If there were potential cause variables like advertising which might be local to a "region" then the cold be incorporated at the store level.If you are referring to a simple indicator reflecting national/regional , you wouldn't need that at the store level.Ultimately store forecasts could be reconciled with national/regional forecasts ala parent to child. $\endgroup$ – IrishStat Mar 1 '12 at 14:07
  • $\begingroup$ Oh that is interesting. I have been looking for a way to incorporate store level analysis. Say I have a very large number of stores (10,000?) how would you go about fitting an individual model for each and keeping track of it? A surprisingly basic question now that I think about it, but I don't think I've ever done anything like that. I usually would just throw a store variable into the full model as a factor. I guess looping through the data in R with where functions? Though that seems like it would make variable selection tricky. How would you approach it? $\endgroup$ – asjohnson Mar 1 '12 at 16:50
  • $\begingroup$ I write and use software called AUTOBOX that does just what you want. It forms a model based upon the statistical fingerprint of each time series incorporating as needed memory, level shifts , local time trends and causals if available. Models are stored and then reused/refreshed as needed. If you wanted you could post the data for 1 store and I would respond publicly with an analysis or if you don't want to do this publicly please contact me privately. $\endgroup$ – IrishStat Mar 1 '12 at 17:24
  • $\begingroup$ Ah fair enough. I am kind of limited on what data I can share and am essentially limited to free software (I mostly use R), but it does seem interesting. I will keep it in mind in the future if I come across problems like this again in a different setting. The idea of aggregating time series after fitting them on an individual level is pretty fascinating. $\endgroup$ – asjohnson Mar 1 '12 at 17:45

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.