1

I have two large lists. One is a list of stores and one is a list of customers. I use a distance formula between Latitude and Longitude to map the store number to the customer list. The issue I have is I need every "store" in the store list to be assigned at least a customer. (Customer list could range between 50k+) I manually do a count on the mapped stores, index the store list and find any zeros. Then I'll filter by that 0 store zipcode and find a mapped store with the largest count and divide that by equal groups. I am looking for a way to do this process across all of my potential 0 stores mapped or even stores with less then "x" number of customer maps. The consolidated list example would be

Customer name, address, Zip, closest store number.

The store list is a full address zip of all stores.

Store number - store Address - zip

The formula I use to map the addresses.

=LOOKUP(1,1/      FREQUENCY(0,SIN((RADIANS(lat1rng-
Lat2))/2)^2+SIN((RADIANS(lng1rng 
Lng2))/2)^2*COS(RADIANS(lat1rng))
*COS(RADIANS(lat2))),storenumber)

Sample Data (The customer list changes a little bit pending on how the list comes to me. That's another reason why Zip will be the best rout to go for this.

Store List (approx 2,500) 

Store # | area | Address | Zip | Zip 4 | Zip Full | Mapped Calls
   1       X        X     00001  0001    00001-0001     100
   2       X        X     00001  0001    00001-0002     0
   3       X        X     00001  0001    00001-0003     2
   4       X        X     22222  0001    22222-0001     20
   5       X        X     22222  0001    22222-0002     0

Customer List (any where from 50k to 1 Mil+ possibly)

Customers| Address | Zip | Zip 4 | Zip Full | Mapped Store
   Id1        X     00001  0001    00001-0001     1
   Id2        X     00001  0001    00001-0002     1
   Id3        X     00001  0001    00001-0003     1
   Id4        X     00001  0001    22222-0001     4
   Id5        X     00001  0001    22222-0002     4

I Need a way to allocate some of "store 1's 100 calls" into stores 2 and 3 (this is a variable group) Not necessarily even distribution. store 4 and 5 only have a group of 2 to split up the mapping.

Community
  • 1
  • 1
Matt McDermott
  • 19
  • 1
  • 6
  • If I understand you correctly, I'm actually working on something similar tonight, reporting on transit to compare actual GPS data of the vehicles route to the scheduled path. I don't know the expected path in advance; I am programmatically "guessing" which route/schedule the vehicle is "mostly likely on" based on time & location of several points. With **your** question, I want to clarify: you want to "assign" customers to the store that they are physically closest to, right? You have customer & store coordinates and you need to also retrieve zip code & street address?? – ashleedawg Dec 03 '17 at 09:04
  • ...also can you expand your explanation of your address-mapping formula? Looks like it's calculating distance between two points based on store number? Are you comparing every customer to every store? – ashleedawg Dec 03 '17 at 09:08
  • and, have you used the [Google Distance Matrix API](https://developers.google.com/maps/documentation/distance-matrix/)? Among other things, it provides estimated travel time/distance based on common routes, speed limits, predicted traffic patterns, transit options,etc, and could provide a more realistic picture of which store a customer will likely choose (compared to calculation "straight line" distance). It's pretty funky and is free for up to 1000 requests per day.) I'm curious to hear more about what you need; I might have some code ready to go for you. – ashleedawg Dec 03 '17 at 09:16
  • So the formula I am using is located here. https://stackoverflow.com/q/28384055/6401570. I have the full store list address and zip and the full customer list address and zip. I used google API to pull in direct lng and lat for all my stores. Due to the API limits I used tableau to map all the zip-4 lng lat for customers. Then I use the formula for a straight line. This all works great I have the closest store they live to. My request is how can I easily check to see if any of my stores have 0 customers to "call". – Matt McDermott Dec 03 '17 at 12:27
  • If I have store a and store b in zipcode 00001 and store a has 50 calls and store b has 0 calls. I need a easier way to allocate some of a calls into b. The formula does take each coordinate into each store. I use a countif to the customer mapped stores to see how many calls each store gets and just do it manually. Its fine if the reallocation isn't their "2nd closest store" store that's why I just split it into other stores on same ZIP code – Matt McDermott Dec 03 '17 at 12:30
  • Sounds simple enough. Are you able to post some sample data? (obviously not real customers) – ashleedawg Dec 03 '17 at 13:03
  • Added it to the post! Thanks a lot for your support! If possible not just calls with "0" but maybe less then 10? Also the calls don't need to be split evenly over the specific zip unless its just easier. I only want this to effect stores that have less then "x" amount of calls mapped so it does not effect entire list. – Matt McDermott Dec 03 '17 at 14:03

0 Answers0