I've been trying to come up with a method to evaluate how certain stores sales may or may not be affected once an identical store is introduced within a given proximity. With the help of some of you, I've been introduced to some pretty clever ideas to get it going.
However, in order to make this process more replicable, I think I need to be more specific. So, my end goal is to be able to evaluate average store sales for the time period each new store is introduced.
To begin:
I have a data set filled with store information (an identifier, when it opened, latitude, longitude). With a combination of macro variables, SQL, and the GEODIST function, I am able to run a program that produces which stores opened within a given year (e.g., 2006), and which stores already existed (within a selected distance) when that new store opened.
So, my results would look like the following:
Each unique value of N_Store represents a store that opened during 2006 and into a 1 mile radius of an existing store. Each value of E_Store represents those existing stores which were within a 1-mile radius of the newly opened store.
Now, this is where I'm having trouble.
I want to use this information in conjunction with a sales data set to see if, all else constant, this range affected sales of existing stores.
An abbreviated version of that sales data would like like the following:
So, when store 570 opened on June 6, 2006, stores 156, 531, 406, 499, and 385 already existed within a 1-mile radius. Then, later when store 752 opened on June 20, 2006, store 492 already existed within a 1-mile radius.
My goal:
I'd like to calculate the average of all stores, which already existed, for the time period before the new store was introduced. For example, for stores 156 through 385, the period would be from May 30, 2006 and before. For store 492, the period would be June 13, 2006 and before.
Then I'd also like to know what they did 1 year, 2 years, 3 years after that store was introduced. So, for stores 156 through 385, I'd be looking at a 52/104/156 week average after June 6, 2006. Then, for store 492, a 52/104/156 week average from June 20, 2006.
If anyone has any ideas on how this could be done, I'd love to hear them! If it can't be done, I'd love to hear that too!
Thanks very much.
... View more